常用查询与表结构修改约束等.docxVIP

use master go if exists(select name from sys.databases where name=EmployeeMIS) drop database EmployeeMIS go create database EmployeeMIS go use EmployeeMIS go --部门表 create table Dept ( deptId int identity(1,1) primary key, deptName nvarchar(100) not null, descn nvarchar(1000), createTime datetime default getdate() ) go insert into Dept(deptName,descn) values(财务部,这是财务部门) insert into Dept(deptName,descn) values(学术部,这是学术部门) set identity_insert Dept on insert into Dept(deptId,deptName,descn) values(50,市场部,这是市场部门) set identity_insert Dept off insert into Dept(deptName,descn) values(就业部,这是就业部门) insert into Dept(deptName,descn,createTime) values(就业部,这是就业部门,default) insert into Dept(deptName,descn,createTime) values(就业部,这是就业部门,2010-1-1 12:30:30) go select * from Dept --*：代表所有的字段 --角色表 create table Roles ( roleID int identity(1,1) primary key, roleName nvarchar(100) not null, descn nvarchar(1000), createTime datetime default getdate() ) go insert into Roles(roleName,descn) values(管理员,这是管理员角色) insert into Roles(roleName,descn) values(普通职员,这是普通职员角色) go select * from Roles create table Employee2 ( empId nchar(5) not null, empName nvarchar(100) not null, gender nchar(1), birthday datetime, hireDate datetime, title nvarchar(100), address nvarchar(100), phone nvarchar(20), photo image, deptId int, roleId int, createTime datetime, primary key(empId), check(gender in (男,女)), default 男 for gender, foreign key(deptId) references Dept(deptId), foreign key(roleId) references Roles(rolieId) ) --创建职员基本信息表 create table Employee ( empId nchar(5) primary key, empName nvarchar(100) not null, gender nchar(1) check(gender in(男,女)) default 男, birthday datetime check(datediff(yyyy,birthday,getdate())=18), hireDate datetime default getdate(), title nvarchar(100), address nvarchar(100), phone nvarchar(20), photo image, deptId int foreign key references Dept(deptId) on delete cascade on update cas