实验4 使用T-SQL语言创建和修改表答案.doc

实验4 使用T-SQL语言创建和修改表一、实验目的1. 掌握使用查询分析器利用T-SQL语句创建数据库表的方法。2. 掌握使用查询分析器利用T-SQL语句修改数据库表结构、删除数据库表的方法。3. 掌握如何使用查询分析器利用T-SQL语句创建和使用用户自定义数据类型。二、实验内容新建人事管理系统数据库，包括三个表：员工基本信息表、部门信息表、员工工资信息表。三个表的结构如表3-7、表3-8、表3-9。完成以下各题。列名数据类型约束说明备注员工编号INT NOT NULL主键，约束名为PK_YGID员工姓名NVARCHAR( 50 )NOT NULL员工登录名NVARCHAR( 20 )NOT NULL建议为英文字符，且与姓名不同员工登录密码BINARY (20)NULL员工电子邮件NVARCHAR( 50)NOT NULL员工所属部门编号INT NULL 员工基本工资INTNULL员工职位名称NVARCHAR (50)NULL员工电话用户自定义类型TELEPHONE_CODENULL员工报到日期DATETIME NOT NULL员工自我介绍NVARCHAR (200)NULL初始为空，由员工自行输入员工剩余假期INT NULL小时数员工的级别INT NULL员工照片IMAGE NULL表 3-7 员工基本信息表列名类型约束说明备注部门编号INT NOT NULL主键, 约束名为PK_BMID部门名称CHAR (10)NULL部门描述CHAR (50)NULL部门经理编号INT NULL表 3-8部门信息表列名类型约束说明备注工资编号INT NOT NULL主键, 约束名为PK_GZID_YGID员工编号INT NOT NULL主键，约束名为PK_GZID_YGID工资发放时间DATETIME NOT NULL员工基本工资INT NULL加班工资INT NULL缺勤扣除INT NULL其他工资INT NULL表 3-9员工工资信息表1. 使用T-SQL语句自定义一个数据类型TELEPHONE_COD，为字符型CHAR(15)，允许空值。SQL代码如下：USE 人事管理系统EXEC SP_ADDTYPE TELEPHONE_CODE,CHAR(15),NULL2. 使用查询分析器利用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,员工基本工资INTNULL,员工职位名称NVARCHAR(50)NULL,员工电话TELEPHONE_CODENULL,员工报到日期DATETIMENOT 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 员工基本信息表.员工所属部门编号,部门编号,COLUMNALTER 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_YGID9. 使用SQL语句将如表3-10所示部门信息记录表插入部门信息表中。部门编号部门名称部门描述部门经理编号000人力资源部负责招聘等工作200001财务部负责工资管理202002市场部负责销售203003开发部负责产品开发204表 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