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

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

一、实验目的

1. 2. 法。

3.

掌握如何使用查询分析器利用T-SQL语句创建和使用用户自定义数据类型。 掌握使用查询分析器利用T-SQL语句创建数据库表的方法。

掌握使用查询分析器利用T-SQL语句修改数据库表结构、删除数据库表的方

二、实验内容

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

列名 员工编号 员工姓名 ) 员工登录名 ) 员工登录密码 员工电子邮件 员工所属部门编号 员工基本工资 员工职位名称 员工电话 型 TELEPHONE_CODE 员工报到日期 员工自我介绍 DATETIME NVARCHAR (200) NOT NULL NULL 初始为空，由员工自行输入 INT NVARCHAR (50) 用户自定义类NULL NULL NULL BINARY (20) NVARCHAR( 50) INT NULL NOT NULL NULL NVARCHAR( 20NOT NULL 建议为英文字符，且与姓名不同 数据类型 INT NVARCHAR( 50约束说明 NOT NULL NOT NULL 备注 主键，约束名为PK_YGID

员工剩余假期 员工的级别 员工照片 INT INT IMAGE NULL NULL NULL 小时数

表 3-7 员工基本信息表 列名 部门编号 部门名称 部门描述 部门经理编号

表 3-8部门信息表

列名 工资编号 类型 INT 约束说明 NOT NULL 备注 主键, 约束名为PK_GZID_YGID 主键，约束名为PK_GZID_YGID 类型 INT CHAR (10) CHAR (50) INT 约束说明 NOT NULL NULL NULL NULL 备注 主键, 约束名为PK_BMID 员工编号 INT NOT NULL 工资发放时间 员工基本工资 加班工资 缺勤扣除 其他工资 DATETIME INT INT INT INT 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 员工基本信息表 ( 员工编号 PRIMARY KEY,

员工姓名

NVARCHAR(50) NVARCHAR(20)

NOT NULL, NOT NULL,

INT

NOT NULL CONSTRAINT PK_YGID

员工登录名 员工登录密码

BINARY(20)

NULL,

NOT NULL, NULL,

员工电子邮件 NVARCHAR(50) INT INT

员工所属部门编号 员工基本工资 员工职位名称 员工电话

NULL, NULL,

NVARCHAR(50)

TELEPHONE_CODE NULL, DATETIME

NOT NULL, NULL, NULL,

NULL,

员工报到日期 员工自我介绍 员工剩余假期 员工的级别 员工照片 )

NVARCHAR(200) INT

INT

IMAGE NULL

3. 使用查询分析器利用T-SQL语句创建部门信息表。 SQL代码如下： USE 人事管理系统 CREATE TABLE 部门信息表 ( KEY,

部门名称 部门描述

CHAR(10) CHAR(50) INT

NULL, NULL, NULL

部门编号

INT

NOT NULL CONSTRAINT PK_BMID PRIMARY

部门经理编号

)

4. 使用查询分析器利用T-SQL语句创建员工工资信息表。 SQL代码如下： USE 人事管理系统

CREATE TABLE 员工工资信息表 (

工资编号

INT

NOT NULL,

员工编号 INT NOT NULL,

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

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 人事管理系统