sql数据库——综合练习.docx

建立一个单位员工管理数据库 数据库名：Employee_manage use [master] go create database Employe_manage on primary ( name=Employe_manage, filename=c:\Employe_manage.mdf, size=3MB, maxsize=50MB, filegrowth=10% ), ( name=Employe_2_manage, filename=c:\Employe_2_manage.ndf, size=3MB, maxsize=30MB, filegrowth=10% ) log on ( name=Employe_manage_log, filename=c:\Employe_manage_log.ldf, size=3MB, maxsize=40MB, filegrowth=10% ) Go sp_helpdb Employe_manage 查看数据库 建立表如下： 红色标注的字段，在建立表时不要加约束。 Department部门表 字段数据类型约束注Department_idBigintPkIdentityDepartment_nameNvarchar(20)nuiqueAffiliation_idbigintfk该部门从属于哪个部门，当没有从属于的部门时为NULL。Manager_idbigintfk部门主管的员工IDcreate table Department ( Department_id int identity , Department_name nvarchar(20) not null, Affiliation_id int , manager_id int , constraint pk_Department_id primary key(Department_id), constraint uni_Department_name unique(Department_name), constraint fk_affiliation_id foreign key(affiliation_id)references Department(Department_id) ) employee员工表 字段数据类型约束注employee_idBigintPkidentityemployee_nameNvarchar(20)manager_idbigintFk该员工的领导IDDepartment_idbigintFk所属部门IDsexbitCheck：’1 OR 0’，默认“1”1:男,0:女salarymoney合法的工资2000 - 10000之间birtydaydateNOT NULL没错就是birtydayWork_datedateNot null工作日期Job_idbigintFK职位IDcreate table employee ( employee_id int identity , employee_name nvarchar(20) not null, manager_id int , Department_id int, sex bit constraint def_sex default(1), salary money, birtyday date , Work_date date not null, Job_id bigint , constraint pk_employee_id primary key(employee_id), constraint fk_manager_id foreign key(manager_id) references employee(employee_id), constraint fk_Department_id foreign key(Department_id) references Department(Department_id) , constraint ck_sex check(sex in(0,1)), constraint ck_salary check(salary between 200 and 10000) ) Job职位表 字段数据类型约束注Job_idBigintPkidentityJob_nameNvarchar(20)Nuiquecreate ta