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

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

一、实验目的

1. 掌握使用查询分析器利用T-SQL语句创建数据库表的方法。

2. 掌握使用查询分析器利用T-SQL语句修改数据库表结构、删除数据库表的方法。 3. 掌握如何使用查询分析器利用T-SQL语句创建和使用用户自定义数据类型。

二、实验内容

新建人事管理系统数据库，包括三个表：员工基本信息表、部门信息表、员工工资信息表。三个表的结构如表3-7、表3-8、表3-9。完成以下各题。

列名 员工编号 员工姓名 员工登录名 员工登录密码 员工电子邮件 员工所属部门编号 员工基本工资 员工职位名称 员工电话 数据类型 INT NVARCHAR( 50 ) NVARCHAR( 20 ) BINARY (20) NVARCHAR( 50) INT INT NVARCHAR (50) 用户自定义类型 TELEPHONE_CODE DATETIME NVARCHAR (200) INT INT IMAGE

约束说明 NOT NULL NOT NULL NOT NULL NULL NOT NULL NULL NULL NULL NULL 备注 主键，约束名为PK_YGID 建议为英文字符，且与姓名不同 员工报到日期 员工自我介绍 员工剩余假期 员工的级别 员工照片 NOT NULL NULL NULL NULL NULL 初始为空，由员工自行输入 小时数 表 3-7 员工基本信息表

列名 部门编号 部门名称 部门描述 部门经理编号 类型 INT CHAR (10) CHAR (50) INT 约束说明 NOT NULL NULL NULL NULL 表 3-8部门信息表

列名 工资编号 员工编号 工资发放时间 员工基本工资 加班工资 缺勤扣除 其他工资 类型 INT INT DATETIME INT INT INT INT 约束说明 NOT NULL NOT NULL NOT NULL NULL NULL NULL NULL 表 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

备注 主键, 约束名为PK_GZID_YGID 主键，约束名为PK_GZID_YGID 备注 主键, 约束名为PK_BMID 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 PRIMARY KEY, 部门名称 CHAR(10) NULL, 部门描述

CHAR(50) NULL, 部门经理编号 INT

NULL

)

4. 使用查询分析器利用T-SQL语句创建员工工资信息表。

SQL代码如下： USE 人事管理系统

CREATE TABLE 员工工资信息表 (

工资编号

INT

NOT NULL,

员工编号

INT NOT NULL,

PK_BMID

工资发放时间 员工基本工资 加班工资 缺勤扣除 其他工资

DATETIME NOT NULL, INT INT INT INT

NULL, NULL, NULL, 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