实验4 使用T-SQL语言创建和修改表
一、实验目的
1.掌握使用查询分析器利用T-SQL语句创建数据库表的方法。
2.掌握使用查询分析器利用T-SQL语句修改数据库表结构、删除数据库表的方法。
3.掌握如何使用查询分析器利用T-SQL语句创建和使用用户自定义数据类型。
二、实验内容
新建人事管理系统数据库，包括三个表：员工基本信息表、部门信息表、员工工资信息表。三个表的结构如表3-7、表3-8、表3-9。完成以下各题。
表 3-7 员工基本信息表
表 3-8部门信息表
表 3-9员工工资信息表
1.使用T-SQL语句自定义一个数据类型TELEPHONE_COD，为字符型CHAR(15)，允许空
值。
SQL代码如下：
USE 人事管理系统
EXEC SP_ADDTYPE TELEPHONE_CODE,'CHAR(15)','NULL'
2.使用查询分析器利用T-SQL语句创建员工基本信息表。
SQL代码如下：
USE 人事管理系统
CREATE TABLE 员工基本信息表
(
员工编号INT NOT NULL CONSTRAINT PK_YGID PRIMARY KEY,
员工姓名NVARCHAR(50) NOT NULL,
员工登录名NVARCHAR(20) NOT NULL,
员工登录密码BINARY(20) NULL,
员工电子邮件NVARCHAR(50) NOT NULL,
员工所属部门编号INT NULL,
员工基本工资INT NULL,
员工职位名称NVARCHAR(50) NULL,
员工电话TELEPHONE_CODE NULL,
员工报到日期DATETIME NOT NULL,
员工自我介绍NVARCHAR(200) NULL,
员工剩余假期INT NULL,
员工的级别INT NULL,
员工照片IMAGE NULL
)
3.使用查询分析器利用T-SQL语句创建部门信息表。
SQL代码如下：
USE 人事管理系统
CREATE TABLE 部门信息表
( 部门编号INT NOT NULL CONSTRAINT PK_BMID PRIMARY KEY,部门名称CHAR(10) NULL,
部门描述CHAR(50) NULL,
部门经理编号INT NULL
)
4.使用查询分析器利用T-SQL语句创建员工工资信息表。
SQL代码如下：
USE 人事管理系统
CREATE TABLE 员工工资信息表
( 工资编号INT NOT NULL,
员工编号INT NOT NULL,
工资发放时间DATETIME NOT NULL,
员工基本工资INT NULL,
加班工资INT NULL,
缺勤扣除INT NULL,
其他工资INT NULL,
CONSTRAINT PK_GZID_YGID PRIMARY KEY(工资编号,员工编号)
)
5.使用T-SQL语句重命名员工工资信息表为员工工资表。
SQL代码如下：
USE 人事管理系统
EXEC SP_RENAME 员工工资信息表,员工工资表
6.使用查询分析器在员工基本信息表中进行如下修改：
（1）添加一个“联系地址”字段，数据类型为VARCHAR(100),允许为空值。
SQL代码如下：
USE 人事管理系统
ALTER TABLE 员工基本信息表
ADD 联系地址 VARCHAR(100) NULL
（2）将“员工自我介绍”字段删除。
SQL代码如下：
USE 人事管理系统
ALTER TABLE 员工基本信息表
DROP COLUMN 员工自我介绍
（3）将“员工所属部门编号”字段重命名为“部门编号”,数据类型修改为INT,不允许为空。
SQL代码如下：
USE 人事管理系统
EXEC SP_RENAME '员工基本信息表.[员工所属部门编号]','部门编号
','COLUMN'
ALTER TABLE 员工基本信息表
ALTER COLUMN 部门编号 INT NOT NULL
（4）将“部门编号”字段添加外键约束，约束标识为FK_BMID,与部门信息表中
的“部门编号”字段关联。
SQL代码如下：
USE 人事管理系统
ALTER TABLE 员工基本信息表
ADD CONSTRAINT FK_BMID FOREIGN KEY (部门编号) REFERENCES 部门信
息表(部门编号)
（5）添加一个“性别”字段，数据类型为CHAR(2),默认约束，约束名为DF_XB,其值为“男”。
SQL代码如下：
USE 人事管理系统
ALTER TABLE 员工基本信息表
ADD 性别 CHAR(2) CONSTRAINT DF_XB DEFAULT '男'
（6）在“性别”列上增加CHECK约束，约束名为CK_XB，要求性别字段只能是“男”或“女”。
SQL代码如下：
USE 人事管理系统
ALTER TABLE 员工基本信息表
ADD CONSTRAINT CK_XB CHECK (性别 IN('男','女'))
7.修改部门信息表，在“部门名称”列增加唯一约束UK_BMNAME。
SQL代码如下：
USE 人事管理系统
ALTER TABLE 部门信息表
ADD CONSTRAINT UN_BMNAME UNIQUE(部门名称)
8.修改员工工资表，删除主键约束PK_GZID_YGID。
SQL代码如下：
USE 人事管理系统
ALTER TABLE 员工工资表
DROP CONSTRAINT PK_GZID_YGID
9.使用SQL语句将如表3-10所示部门信息记录表插入部门信息表中。
表 3-10部门信息记录表
SQL代码如下：
USE 人事管理系统
INSERT INTO 部门信息表(部门编号,部门名称,部门描述,部门经理编号) VALUES(000,'人力资源部','负责招聘等工作',200)
INSERT INTO 部门信息表(部门编号,部门名称,部门描述,部门经理编号) VALUES(001,'财务部','负责工资管理',202)
INSERT INTO 部门信息表(部门编号,部门名称,部门描述,部门经理编号) VALUES(002,'市场部','负责销售',203)
INSERT INTO 部门信息表(部门编号,部门名称,部门描述,部门经理编号) VALUES(003,'开发部','负责产品开发',204)
10.使用SQL语句将部门信息表中的人力资源部部门经理编号改为201。
SQL代码如下：
USE 人事管理系统
UPDATE 部门信息表
SET 部门经理编号 = 201
WHERE 部门名称 = '人力资源部'
11.使用SQL语句将部门信息表中的部门编号增加1。
SQL代码如下：
USE 人事管理系统
UPDATE 部门信息表
SET 部门编号 = 部门编号 + 1
12.使用SQL语句将部门信息表中的开发部删除。SQL代码如下：
USE 人事管理系统
DELETE FROM 部门信息表
WHERE 部门名称 = '开发部'