创建性别表，可以将性别编号作为主码。CREATE TABLE 性别表 （SexNo char（1) PRIMARY KEY, SexName nchar（4) NULL, )

如下所示，创建表s，并完成随后的操作 Create table s(sno char(2) primary key,sname char(10)); 1.向表中添加属性列status,数据类型为int 2.向表中添加属性列city,数据类型为varchar(20),并限定其取值范围为“上海”，“北京”，“天津”之一 3.修改属性列status的数据类型为smallint 4.删除2.题中取值范围的约束 5.删除属性列status 6.如教材85页所示，建立student, sc, course等表,并插入若干数据，完成如下操作： （1）列出没有成绩的学生的学号和课程号 （2）列出2号课程成绩在70分到80分学生的学号 （3）查询所有2005级的学生的姓名，性别和所在系 （4）查询计科系2004级全体学生的所有信息 （5）查询计科系2006级3班和4班学生的姓名和性别 （6）查询所有以“数”打头的课程的名称和学分 （7）查询数学系所有学生的姓名，性别和出生年份 （8）将course表中名为PASCAL语言的课程更名为“C语言” （9）将所有课程的学分增加1分 （10）删除没有选课成绩的选课记录 （11）删除“IS”系的所有学生信息 （12）删除所有的课程信息

实验十二：完整性约束 实验名称：完整性约束 一、实验目的 要求学生在熟练掌握使用T—SQL语句CREATE TABLE、ALTER TABLE及DROP TABLE语句对数据表进行管理的基础上，掌握主键约束、外键约束、UNIQUE约束和CHECK约束的创建和应用。 二、实验原理 使用T—SQL语句实现主键约束、外键约束、UNIQUE约束和CHECK约束的创建。 三、实验设备 安装有SQL SERVER 2012的计算机。 四、实验示例 示例是某公司的产品销售数据库company，company数据库中存在人事表employee、客户表customer、销售表sales、销售明细表sale_item、产品表product。各表的结构如下： 员工人事表employee emp_no char(5) Not null primary key 员工编号 emp_name varchar(10) Not null 员工姓名 sex char(2) Not null 性别 dept varchar(10) Not null 所属部门 title varchar(10) Not null 职称 date_hired datetime Not null 雇佣日 birthday datetime Null 生日 salary int Not null 薪水 telephone varchar(20) Null 电话 addr varchar(50) null 住址 客户表customer cust_id char(5) Not null primary key 客户号 cust_name varchar(20) Not null 客户名称 addr varchar(40) Not null 客户住址 tel_no varchar(20) Not null 客户电话 zip char(6) null 邮政编码 销售主表sales order_no int Not null primary key 订单编号 cust_id char(5) Not null 客户号 sale_id char(5) Not null 业务员编号 tot_amt numeric(9,2) Not null 订单金额 order_date datetime Not null 订货日期 销货明细表sale_item order_no int Not null primary key 订单编号 prod_id char(5) Not null primary key 产品编号 qty int Not null 销售数量 unit_price numeric(7,2) Not null 单价 order_date datetime null 订单日期 产品名称表product prod_id char(5) Not null primary key 产品编号 prod_name varchar(20) Not null 产品名称 1．创建表及相应的约束 create table employee1( emp_no char(5) not null check(emp_no like '[E-F][0-9][0-9][0-9][0-9]'), emp_name char(10) not null, emp_sex char(1) not null check(emp_sex in ('m','f')), emp_phone char (13) not null, emp_add varchar(60) not null, emp_salary char(5) not null check (emp_salary between 60000 and 80000) ) go 2．修改表 alter table employee add constraint ck_phone check (phone like ’[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9] [0-9][0-9][0-9][0-9][0-9]’) 五、实验内容 使用T—SQL语句完成以下操作： 1．在数据库company中创建以上五张表，并设置各表的主键。 2．在销售主表sales中添加字段“发票号码” invoice_no，char(10)，NOT NULL。 3．添加外键约束： a) 在销售主表sales的业务员编号字段sale_id上添加外键约束，参照字段为员工表employee中的字段员工编号emp_no，约束名为FK_sale_id。 b) 在销售主表sales的客户号字段cust_id上添加外键约束，参照字段为客户表customer中的字段客户号cust_id，约束名为FK_cust_id。 c) 在销售明细表sale_item的订单编号字段order_no上添加外键约束，参照字段为销售主表sales中的字段订单编号order_no，约束名为FK_order_no。 d) 在销售明细表sale_item的产品编号字段prod_id上添加外键约束，参照字段为产品名称表product中的产品编号字段prod_id，约束名为FK_prod_id。 4．添加核查约束： a) 将员工表employee中的薪水字段salary的值限定在1000至10000间，约束名为CK_salary。 b) 将员工表employee中的员工编号字段emp_no设定为以“E”字母开头, 后面跟5位数的编号，约束名为CK_emp_no。 c) 将员工表employee中的性别字段设定这取值只能是“男”和“女”。约束名为CK_sex。 d) 将销售主表sales中的发票号码字段invoice_no设定为以“I”字母开头，后面跟9位数的编号，约束名为CK_inno。 5．为销售主表sales中的字段发票号码invoice_no设置为唯一约束，约束名为UN_inno。

如果学生表student是使用下面的SQL谙句创建的： CREATE TABLE student(学号C(6)PRIMARY KEY NOT NULL，姓名C(8)，性别C(2)，年龄N(2) CHECK(年龄>15 AND年龄<30，)) 则下面的SQL语句中可以正确执行的是（ ）。

A. INSERT INTO student(学号，性别，年龄)VALUES(”0542”，”李雷”，”男”，17)

B. INSERT INTO student(学号，性别，年龄)VALUES(”李雷”，”男”，20)

C. INSERT INTO student(学号，性别，年龄)VALUES(”女”，25)

D. INSERT INTO student(学号，性别，年龄)VALUES(”0897”，”安宁”，”女”，30)

如果学生表student是使用下面的SQL谙句创建的： CREATE TABLE student(学号C(6)PRIMARY KEY NOT NULL，姓名C(8)，性别C(2)，年龄N(2) CHECK(年龄>15 AND年龄<30，)) 则下面的SQL语句中可以正确执行的是（ ）。

A.INSERT INTO student(学号，性别，年龄)VALUES(”0542”，”李雷”，”男”，17)

B.INSERT INTO student(学号，性别，年龄)VALUES(”李雷”，”男”，20)

C.INSERT INTO student(学号，性别，年龄)VALUES(”女”，25)

D.INSERT INTO student(学号，性别，年龄)VALUES(”0897”，”安宁”，”女”，30)

创建"姓名拼音_student"的数据库，设置可以采用默认。使用T-SQL在数据库内完成以下内容： 1. 依据数据表的结构用create table创建相对应的数据表，表结构如下所示： 学生信息表（student） 字段名称 字段类型及长度 说明 备注 Sno char（9） 学生学号 主关键字 Sname nvarchar（6） 学生姓名 非空 Ssex char（2） 学生性别 可为空 Sage int 学生年龄 可为空 Sdept nvarchar（20） 学生所在院系 可为空 课程信息表（course） 字段名称 字段类型及长度 说明 备注 Cno char（4） 课程编号 主关键字 Cname nvarchar（10） 课程名称 非空 Cpno char（4） 先行课 可为空 Ccredit int 学分 可为空 选课信息表（sc） 字段名称 字段类型及长度 说明 备注 Sno char（9） 学生学号 主关键字，外码 Cno char（4） 课程编号 主关键字，外码 Grade numeric(4,1) 成绩 可为空，取值范围0-100 学生信息表2（student2） 字段名称 字段类型及长度 说明 备注 Sno char（9） 学生学号 主关键字 Sname nvarchar（6） 学生姓名 非空 Ssex nchar（1） 学生性别 可为空 Sage int 学生年龄 可为空 Sdept nvarchar（10） 学生所在院系 可为空 2. 在表student2中增加新字段"班级名称（Sclass）"，数据类型为nvarchar（10）； 3. 在表student2中删除字段"班级名称（Sclass）"； 4. 修改表student2中字段名为"Sname"的字段长度由原来的6改为8； 5. 修改表student2中字段"Sdept"名称为"dept"，长度为20； 6. 修改表student2中Sage字段名称为Sbirth，类型为smalldatetime； 7. 修改表student2新名称为stu_info； 8. 删除数据表stu_info。

设数据库中有三个关系： 职工表 EMP（E#，ENAME，AGE，SEX，ECITY）， 其属性分别表示职工工号、姓名、年龄、性别和籍贯。 工作表 WORKS（E#，C#，SALARY）， 其属性分别表示职工工号、工作的公司编号和工资。 公司表 COMP（C#，CNAME，CITY）， 其属性分别表示公司编号、公司名称和公司所在城市。 用CREATE TABLE语句创建上述三个表，需指出主键和外键。

A、CREATE TABLE EMP ( E# CHAR(4) NOT NULL, ENAME CHAR(8) NOT NULL, AGE SMALLINT, SEX CHAR(1), ECITY CHAR(20), PRIMARY KEY(E#))； CREATE TABLE COMP ( C# CHAR(4) NOT NULL, CNAME CHAR(20) NOT NULL, CITY CHAR(20), PRIMARY KEY(C#))； CREATE TABLE WORKS ( E# CHAR(4) NOT NULL, C# CHAR(4) NOT NULL, SALARY SMALLINT, PRIMARY KEY(E#, C#), FOREIGN KEY(E#，C#) REFERENCE EMP(E#)，COMP(C#))；

B、CREATE TABLE EMP ( E# CHAR(4) NOT NULL, ENAME CHAR(8) NOT NULL, AGE SMALLINT, SEX CHAR(1), ECITY CHAR(20), PRIMARY KEY(E#))； CREATE TABLE COMP ( C# CHAR(4) NOT NULL, CNAME CHAR(20) NOT NULL, CITY CHAR(20), PRIMARY KEY(C#))； CREATE TABLE WORKS ( E# CHAR(4) NOT NULL, C# CHAR(4) NOT NULL, SALARY SMALLINT, PRIMARY KEY(E#, C#), FOREIGN KEY(E#) REFERENCE EMP(E#), FOREIGN KEY(C#) REFERENCES COMP(C#))；

C、CREATE TABLE EMP ( E# CHAR(4) NOT NULL, ENAME CHAR(8) NOT NULL, AGE SMALLINT, SEX CHAR(1), ECITY CHAR(20), )； CREATE TABLE COMP ( C# CHAR(4) NOT NULL, CNAME CHAR(20) NOT NULL, CITY CHAR(20), PRIMARY KEY(C#))； CREATE TABLE WORKS ( E# CHAR(4) NOT NULL, C# CHAR(4) NOT NULL, SALARY SMALLINT, PRIMARY KEY(E#, C#), FOREIGN KEY(E#) REFERENCES EMP(E#), FOREIGN KEY(C#) REFERENCES COMP(C#))；

D、CREATE TABLE EMP ( E# CHAR(4) NOT NULL, ENAME CHAR(8) NOT NULL, AGE SMALLINT, SEX CHAR(1), ECITY CHAR(20), PRIMARY KEY(E#))； CREATE TABLE COMP ( C# CHAR(4) NOT NULL, CNAME CHAR(20) NOT NULL, CITY CHAR(20), PRIMARY KEY(C#))； CREATE TABLE WORKS ( E# CHAR(4) NOT NULL, C# CHAR(4) NOT NULL, SALARY SMALLINT, PRIMARY KEY(E#, C#), FOREIGN KEY(E#) REFERENCES EMP(E#), FOREIGN KEY(C#) REFERENCES COMP(C#))；

(1)创建aaa数据库，并在该数据库中建立学生表student、课程表course、成绩表score、教师表teacher。表中各列的内容和要求如表5-4～表5-7所示： 表5-4学生表Student结构： 列名称 列类型 长度 是否为主键 约束条件 缺省值 列说明 sno char 8 是 学号 sname char 8 不为空 学生名 sex char 2 取值范围男或女 男 性别 brithday datetime 出生年月 表5-4学生表Student结构： 列名称 列类型 长度 是否为主键 约束条件 缺省值 列说明 sno char 8 是 学号 sname char 8 不为空 学生名 sex char 2 取值范围男或女 男 性别 brithday datetime 出生年月 Sdept char 8 所在系 表5-5课程表Course结构： 列名称 列类型 长度 是否为主键 约束条件 缺省值 列说明 Cno char 8 是 课程号 Cname char 8 课程名 Tno char 8 外键 教师号 Ccredit int >0 学分 表5-6成绩表score结构： 列名称 列类型 长度 是否为主键 约束条件 缺省值 列说明 sno char 8 外键 学号 cno char 8 外键 课程 Score int 在0到100之间 分数 表5-7教师表teacher结构： 列名称 列类型 长度 是否为主键 约束条件 缺省值 列说明 tno char 8 主键 教师号 Tname char 8 不为空 教师名 Tsex Char 2 取值范围男或女 男 性别 Tbirthday datetime 出生年月 Dept Char 8 所在系

实验九：使用SQL Server管理数据表 一、实验目的 1. 熟悉数据表的特点； 2. 熟悉在Management Studio中创建、编辑及删除数据表； 3. 熟悉使用T-SQL创建、编辑及删除数据表； 二、实验环境 已安装SQL Server 2012企业版的计算机； 三、实验学时 2学时 四、实验要求 1. 了解表的结构特点； 2. 了解SQL Server的基本数据类型； 3. 熟悉使用T-SQL语法； 4. 完成实验报告。 五、实验内容 请T-SQL完成以下内容： 1、 请在指定数据库内完成以下内容： 1） 依据数据表的结构创建相对应的数据表，表结构如下所示； 学生信息表（student） 字段名称 字段类型及长度 说明 备注 Sno Char（9） 学生学号 主关键字 Sname Char（6） 学生姓名 非空 Ssex Char（2） 学生性别 可为空 Sage Int 学生年龄 可为空 Sdept VarChar（8） 学生所在院系 可为空 课程信息表（course） 字段名称 字段类型及长度 说明 备注 Cno Char（4） 课程编号 主关键字 Cname VarChar（20） 课程名称 非空 Cpno Char（4） 先行课 可为空 Ccredit Int 学分 可为空 选课信息表（sc） 字段名称 字段类型及长度 说明 备注 Sno Char（9） 学生学号 主关键字 Cno Char（4） 课程编号 主关键字 Grade Int 成绩 可为空 2） 在表student中增加新字段 “班级名称（sclass）“； 3） 在表student中删除字段“班级名称（sclass）”； 4） 修改表student中字段名为“sname”的字段长度由原来的6改为8； 5） 修改表student中字段“sdept”名称为“dept”，长度为20； 6） 修改表student中sage字段名称为sbirth，类型为smalldatetime； 7） 修改表student新名称为stu_info； 8） 删除数据表student； 2、创建教师授课管理数据库JSSK，并完成以下内容； 1） 在数据库JSSK中创建下列三张表； 表名：teachers 列名 数据类型 说明 描述 Tno 字符型，长度7 主键 教师号 Tname 字符型，长度10 非空 姓名 Tsex 字符型，长度2 默认取值为“男” 性别 Birthday 小日期时间型 允许空 出生日期 Dept 字符型，长度20 允许空 所在部门 Sid 字符型，长度18 身份证号 表名： lessons 列名 数据类型 说明 描述 Cno 字符型，长度10 主键 课程号 Cname 字符型，长度20 非空 课程名 Credit 短整型 学分 property 字符型，长度为10 课程性质 表名： shouke 列名 数据类型 说明 描述 Tno 字符型，长度7 主键 教师号 Cno 字符型，长度10 主键 课程名 Hours 整数 课时 2） 在shouke表里添加一个授课类别字段，列名为Type，类型为Char，长度为4； 3） 将shouke表的Hours的类型改为smallint； 4） 删除lessons表中的property列； 3、(选做)创建产品销售数据库CPXS 在数据库CPXS中创建下列三张表； 表名：产品表（cp）表结构 列名 数据类型 说明 描述 Cpbh 字符型，长度6 主键 产品编号 Cpmc 字符型，长度30 非空 产品名称 Jg 浮点型，长度8 允许空 价格 Kcl 整型，长度4 允许空 库存量 表名：销售商（xss）表结构 列名 数据类型 说明 描述 Xsbh 字符型，长度6 主键 销售商编号 Xsmc 字符型，长度30 非空 销售商名称 Dq 字符型，长度10 允许空 地区 Fzr 字符型，长度8 允许空 负责人 Dh 字符型，长度12 允许空 电话 Bz 文本，长度16 允许空 备注 表名： 产品销售（xss）表结构 列名 数据类型 说明 描述 Cpbh 字符型，长度6 主键 产品编号 Xsbh 字符型，长度6 主键 销售商编号 Xssj Datetime，长度8 非空 销售时间 Sl 整型，长度4 非空 数量 Je 浮点型，长度8 非空 金额 六、出现问题及解决方法 如数据表无法创建或删除等问题；

● 设有员工关系 Emp（员工号，姓名，性别，部门，家庭住址），其中，属性“性别”的取值只能为 M 或 F；属性“部门”是关系 Dept 的主键。要求可访问“家庭住址”的某个成分，如邮编、省、市、街道以及门牌号。关系Emp的主键和外键分别是 （15） 。“家庭住址”是一个 （16） 属性。创建Emp关系的SQL语句如下：

CREATE TABLE Emp(

员工号 CHAR(4),

姓名CHAR(10),

性别CHAR(1) （17） ,

部门CHAR(4) （18） ,

家庭住址CHAR(30),

PRIMARY KEY (员工号) );

（15）

A. 员工号、部门 B. 姓名、部门

C.员工号、家庭住址 D. 姓名、家庭住址

（16）

A. 简单 B. 复合 C. 多值 D. 派生

（17）

A. IN (M,F) B. LIKE ('M', 'F')

C.CHECK ('M', 'F') D. CHECK(性别 IN ('M', 'F'))

（18）

A. NOT NULL B. REFERENCES Dept(部门)

C.NOT NULL UNIQUE D. REFERENCES Dept('部门')

● 设有员工关系Emp（员工号，姓名，性别，部门，家庭住址），其中，属性“性别”的取值只能为 M 或 F；属性“部门”是关系 Dept 的主键。要求可访问“家庭住址”的某个成分，如邮编、省、市、街道以及门牌号。关系Emp的主键和外键分别是 （15） 。“家庭住址”是一个 （16） 属性。创建Emp关系的SQL语句如下：

CREATE TABLE Emp(

员工号 CHAR(4),

姓名CHAR(10),

性别CHAR(1) （17） ,

部门CHAR(4) （18） ,

家庭住址CHAR(30),

PRIMARY KEY (员工号) );

（15）

A. 员工号、部门

B. 姓名、部门

C. 员工号、家庭住址

D. 姓名、家庭住址

（16）

A. 简单

B. 复合

C. 多值

D. 派生

（17）

A. IN (M,F)

B. LIKE ('M', 'F')

C. CHECK ('M', 'F')

D. CHECK(性别 IN ('M', 'F'))

（18）

A. NOT NULL

B. REFERENCES Dept(部门)

C. NOT NULL UNIQUE

D. REFERENCES Dept('部门')