数据库原及其应用实验（作业）

实验1、数据定义

1.1 实验目的

熟悉SQL的数据定义语言，能够熟练地使用SQL语句来创建和更改基本表，创建和取消索引。 1.2 实验内容

? 用企业管理器创建数据库University_Mis ? 使用CREATE语句创建基本表。

? 更改基本表的定义: 增加列，删除列，修改列的数据类型。 ? 创建表的升、降序索引。

? 删除基本表的约束、基本表的索引或基本表。

1.3 实验步骤

(1) 用企业管理器创建数据库University_Mis

(2) 在查询分析器中用SQL语句创建关系数据库基本表：

学生表Students(Sno,Sname, Semail,Scredit,Sroom)； 教师表Teachers(Tno,Tname,Temail,Tsalary)； 课程表Courses(Cno,Cname,Ccredit); 成绩表Reports(Sno,Tno,Cno, Score);

其中：Sno、Tno、Cno分别是表Students、表Teachers、表Courses的主键，具有唯一性约束，Scredit具有约束“大于等于0”； Reports中的Sno,Tno,Cno是外键，它们共同组成Reports的主键。

(3) 更改表Students：增加属性Ssex(类型是CHAR，长度为2)，取消Scredit“大于等于0”约束。把表Courses中的属性Cname的数据类型改成长度为30。

(4) 删除表Students的一个属性Sroom。 (5) 删除表Reports。

(6) 为Courses表创建按Cno降序排列的索引。 (7) 为Students表创建按Sno升序排列的索引。

(8) 创建表Students的按Sname升序排列的唯一性索引。 (9) 删除Students表Sno的升序索引。

1

实验2、SQL的数据查询

2.1 实验目的

熟悉SQL语句的数据查询语言，能够SQL语句对数据库进行单表查询、连接查询、嵌套查询、集合查询和统计查询。

2.2 实验内容

实验内容主要是对数据库进行查询操作，包括如下四类查询方式： （1） 单表查询

? 查询的目标表达式为所有列、指定的列或指定的列的运算三种不同。 ? 使用DISTINCT保留字消除重复行。 ? 对查询结果排序和分组。

? 集合分组使用集函数进行各项统计。 （2） 连接查询

? 笛卡儿连接和等值连接。 ? 自连接。 ? 外连接

? 复合条件连接。 ? 多表连接。 （3） 嵌套查询

? 通过实验验证对子查询的两个限制条件。 ? 体会相关子查询和不相关子查询的不同。 ? 考察四类谓词的用法，包括：

第一类，IN、NOT IN；

第二类，带有比较运算符的子查询；

第三类，SOME、ANY或ALL谓词的子查询，查询最大值和最小值； 第四类，带有EXISTS谓词的子查询，实现“所有”等情况（如王宏的“所有”课程，“所有”女生选修的课程）

（4） 集合运算

? 使用保留字UNION进行集合或运算。

? 采用逻辑运算符AND或OR来实现集合交和减运算。 2.3 实验步骤

以University_Mis数据库为例，该数据库中有四张如实验1，其中Score是每门课的考试成绩，Scredit是学生所有考试合格课程所获得的积分总数，Ccredit每门课程的学分数。

在数据库中，存在这样的联系：学生可以选择课程，一个课程对应一个教师。在表Reports中保存学生的选课记录和考试成绩。

请先输入如下符合条件的元组后，再对数据库进行有关的查询操作：

2

图1.1、Students表

图1.2、Teachers表

图1.3、Courses表

图1.4、Reports表

1） 查询性别为“男”的所有学生的名称并按学号升序排列。

SELECT Sname FROM Students WHERE Ssex = '男'

3

（ ORDER BY Sno ASC;

（2） 查询学生的选课成绩合格的课程成绩，并把成绩换算为积分。积分的

计算公式为：[1+(考试成绩-60)*0.1]*Ccredit。考试成绩>=60 否则=0

SELECT Sno,Score,Credits, (1+(Score-60)*0.1)*Credits score

FROM Reports,Courses WHERE Score >= 60

（3） 查询学分是3或4的课程的名称。 SELECT Cname

FROM Courses

WHERE Credits = 3 OR Credits = 4;

（4） 查询所有课程名称中含有“算法”的课程编号。

SELECT Cno FROM Courses

WHERE Cname LIKE '%算法%';

（5） 查询所有选课记录的课程号（不重复显示）。 SELECT Cno

FROM Reports WHERE Score != 0 GROUP BY Cno

（6） 统计所有老师的平均工资。

SELECT AVG(Tsalary) FROM Teachers

（7） 查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降

序排列。

SELECT Tno,AVG(Score)

FROM Reports GROUP BY Tno

ORDER BY AVG(Score) DESC

（8） 统计各个课程的选课人数和平均成绩。 SELECT Cno,COUNT(Sno) number,AVG(Score)

FROM Reports GROUP BY Cno

（9） 查询至少选修了三门课程的学生编号和姓名。 SELECT Sno,Sname

FROM Students Where Sno IN (

SELECT Sno FROM Reports

4

GROUP BY Sno HAVING COUNT(*)>=3 )

（10）查询编号S26的学生所选的全部课程的课程名和成绩。 SELECT Sno,Score,Cname

FROM Reports,Courses

（11）查询所有选了“数据库原理及其应用”课程的学生编号和姓名。 SELECT Students.Sno,Sname

FROM Courses,Students,Reports

Where Cname LIKE '数据库原理与其应用%' AND Courses.Cno = Reports.Cno AND Reports.Sno = Students.Sno

Where Sno LIKE 'S26' AND Courses.Cno = Reports.Cno

（12）求出选择了同一个课程的学生对。 SELECT A.Cno,A.Sno,B.Sno

FROM Reports A , Reports B

Where A.Cno = B.Cno AND A.Sno != B.Sno ORDER BY Cno

（13）求出至少被两名学生选修的课程编号。

（14）查询选修了编号S26的学生所选的某个课程的学生编号。 （15）查询学生的基本信息及选修课程编号和成绩。

（16）查询学号S52的学生的姓名和选修的课程名称及成绩。 （17）查询和学号S52的学生同性别的所有学生资料。 （18）查询所有选课的学生的详细信息。

（19）查询没有学生选的课程的编号和名称。

（20）查询选修了课程名为C++的学生学号和姓名。

（21）找出选修课程UML或者课程C++的学生学号和姓名。 （22）找出和课程UML或课程C++的学分一样课程名称。 （23）查询所有选修编号C01的课程的学生的姓名。 （24）查询选修了所有课程的学生姓名。

（25）利用集合查询方式，查询选修课程C++或选择课程JAVA的学生的编

号、姓名和积分。

（26）实现集合交运算，查询既选修课程C++又选修课程JAVA的学生的编

号、姓名和积分。

（27）实现集合减运算，查询选修课程C++而没有选修课程JAVA的学生的

编号。

5

实验3、数据更新

3.1 实验目的

熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、更新、删除操作。

3.2 实验内容

? 在本实验中，主要内容是如何用SQL语句对数据进行更新。 ? 使用INSERT INTO语句插入数据，包括插入一个元组或将子查询的结果插

入到数据库中两种方式。

? 使用SELECT INTO语句，产生一个新表并插入数据。

? 使用UPDATE语句可以修改指定表中满足WHERE子句条件的元组，有三

种修改的方式：修改某一个元组的值；修改多个元组的值；带子查询地修改语句。

? 使用DELETE语句删除数据：删除某一个元组的值；删除多个元组的值；

带子查询地删除语句。 3.3 实验步骤

在数据库University_Mis上按下列要求进行数据更新。

(1) 使用SQL语句向Students表中插入元组(Sno：S78; Sname：李迪; Semail:LD@zjut.edu.cn; Scredit：0；Ssex：男)。

(2) 对每个课程，求学生的选课人数和学生的平均成绩，并把结果存入数据库。使用SELECT INTO 和INSERT INTO 两种方法实现。

(3) 在Students表中使用SQL语句将姓名为李迪的学生的学号改为S70。 (4) 在Teachers表中使用SQL语句将所有教师的工资加500元。

(5) 将姓名为刘华的学生的课程“数据库原理及其应用”的成绩加上6分。 (6) 在Students表中使用SQL语句删除姓名为李迪的学生信息。 (7) 删除所有选修课程JAVA的选修课记录。

(8) 对Courses表做删去学分<=4的元组操作，讨论该操作所受到的约束。

6

实验4、SQL的视图

4.1 实验目的

熟悉SQL支持的有关视图的操作，能够熟练使用SQL语句来创建需要的视图，对视图进行查询和取消视图。 4.2 实验内容

（1） 定义常见的视图形式，包括：

? 行列子集视图

? WITH CHECK OPTION的视图 ? 基于多个基表的视图 ? 基于视图的视图 ? 带表达式的视图 ? 分组视图

（2） 通过实验考察WITH CHECK OPTION这一语句在视图定义后产生的影

响，包括对修改操作、删除操作、插入操作的影响。 （3） 讨论视图的数据更新情况，对子行列视图进行数据更新。 （4） 使用DROP语句删除一个视图，由该视图导出的其他视图定义仍在数据

字典中，但已不能使用，必须显式删除。同样的原因，删除基表时，由该基表导出的所有视图定义都必须显式删除。 4.3 实验步骤

（1） 创建一个行列子集视图CS_View，给出选课成绩合格的学生的编号、教

师编号、所选课程号和该课程成绩。

（2） 创建基于多个基本表的视图SCT_View，这个视图由学生姓名和他所选

修的课程名及讲授该课程的教师姓名构成。

（3） 创建带表达式的视图EXP_View，由学生姓名及所选课程名和所有课程

成绩都比原来多5分这几个属性组成。

（4） 创建分组视图Group_View，将学生的学号及他的平均成绩定义为一个

视图。

（5） 创建一个基于视图的视图，基于（1）中建立的视图，定义一个包括学

生编号、学生所选课程数目和平均成绩的视图VV_View。 （6） 查询所有选修课程“数据库原理及其应用”的学生姓名。

（7） 插入元组（S52,T02,C02,59）到视图CS_View中。若是在视图的定义中

存在WITH CHECK OPTION字句对插入操作由什么影响。

（8） 将视图CS_View（包括定义WITH CHECK OPTION）中，所有课程编

号为C01的课程的成绩都减去5分。这个操作数据库是否会正确执行，为什么？如果加上5分（原来95分以上的不变）呢？

（9） 在视图CS_View（包括定义WITH CHECK OPTION）删除编号S03学

生的记录，会产生什么结果？

（10） 取消视图SCT_View和视图CS_View

7

实验5、数据控制

5.1 实验目的

熟悉SQL的数据控制功能，能够使用SQL语句来向用户授予和收回权限。 5.2 实验内容

(1) 使用GRANT语句来对用户授权，对单个用户或多个用户授权，或使用保留字PUBLIC对所有用户授权。对不同的操作对象包括数据库、视图、基本表等进行不同权限的授权。

(2) 使用WITH GRANT OPTION字句授予用户传播该权限的权利。

(3) 当在授权时发生循环授权，考察DBS能否发现这个错误。如果不能，结合取消权限操作，查看DBS对循环授权的控制。 (4) 使用REVOKE子句收回授权，取消授权的级联反应。 5.3 实验步骤

用企业管理器在数据库University_Mis中建立三个用户USER1、USER2和USER3，他们在数据库中的角色是PUBLIC。请按以下要求，分别以管理员身份或这三个用户的身份登陆到数据库中，进行操作，并记录操作结果。

配置管理器——服务——第一个重新启动

(1) 授予所有用户对表Courses的查询权限。 这是以windows登录SQL: GRANT SELECT

ON Courses TO PUBLIC

以USER1的身份登陆查询分析器，用SQL语言查询Courses和Students表，查询结果如何？

SELECT * FROM Courses

查询Courses正常，查询表Stuents出错，由于为将表Students的查询权限授予用户USER1.

(2) 授予用户USER1对表Students插入和更新的权限，但不授予删除权限，并且授予用户USER1传播这两个权限的权利。

GRANT SELECT,INSERT,UPDATE ON Students TO USER1

WITH GRANT OPTION

(若不授予用户USER1 SELECT 以USER？的身。。。

INSERT

INTO Students

权限登录时将无法UPDATE)

VALUES('S05','葛晓凡','GXF@zjut.edu.cn',18.6,'女') UPDATE Students

8

SET Scredit = 19.2 WHERE Sno = 'S05'

(3) 允许用户USER2在表Reports中插入元组，更新Score列，可以查询除了Sno以外的所有列。

GRANT INSERT,UPDATE(Score),SELECT(Tno,Cno,Score) ON Reports TO USER2

以USER？的身。。。

INSERT INTO Reports

VALUES('S05','T02','C01',85)

UPDATE Reports SET Score = 90 WHERE Tno = 'T01'

SELECT Tno,Cno,Score FROM Reports

(4) 用户USER1授予用户USER2对表Students插入和更新的权限，并且授予用户USER2传播插入操作的权利。

GRANT SELECT,INSERT,UPDATE ON Students TO USER2

(传播插入操作不会) 以USER？的身。。。

INSERT

INTO Students

VALUES('S06','陈慧','CH@zjut.edu.cn',19.6,'女')

UPDATE Students SET Scredit = 20.1 WHERE Sno = 'S06'

(5) 收回对用户USER1对表Courses查询权限的授权。

9

REVOKE SELECT ON Courses FROM USER1

以USER？的身。。。

明明收回权限为什么还可以查询？ (6) 由上面（2）和（4）的授权，再由用户USER2对用户USER3授予表Students插入和更新的权限，并且授予用户USER3传播插入操作的权力。这时候，如果由USER3对USER1授予表Students的插入和更新权限是否能得到成功？如果能够成功，那么如果有用户USER2取消USER3的权限，对USER1会有什么影响？如果再由DBA取消USER1的权限，对USER2有什么影响？

10

实验6、SQL的空值和空集处理

6.1 实验目的

认识NULL值在数据库中的特殊含义，了解空值和空集对于数据库的数据查询操作，特别是空值在条件表达式中与其他的算术运算符或者逻辑运算符的运算中，空集作为嵌套查询的子查询的返回结果时候的特殊性，能够熟练使用SQL语句来进行与空值，空集相关的操作。

6.2 实验内容

通过实验验证在原理解析中分析过的SQL Server对NULL的处理，包括： ? 在查询的目标表达式中包含空值的运算。 ? 在查询条件中空值与比较运算符的运算结果。

? 使用IS NULL或IS NOT NULL 来判断元组该列是否为空值。 ? 对存在取空值的列按值进行ORDER BY排序。

? 使用保留字DISTINCT对空值的处理，区分数据库的多中取值与现实中的

多种取值的不同。

? 使用 GROUP BY对存在取空值的属性值进行分组。

? 结合分组考察空值对各个集合函数的影响，特别注意对COUNT（*）和

COUNT（列名）的不同影响。

? 考察结果集是空集时，各个集函数的处理情况。

? 验证嵌套查询中返回空集的情况下与各个谓词的运算结果。 ? 进行与空值有关的等值连接运算。 6.3 实验步骤

(1) 查询所有选课记录的成绩并将它换算为五分制（满分为5分，合格为3分），

注意,创建表时允许Score取NULL值。

(2) 通过查询选修编号C07的课程的学生的人数，其中成绩合格的学生人数，

不合格的人数，讨论NULL值的特殊含义。

(3) 通过实验检验在使用ORDER BY进行排序时，取NULL的项是否出现在

结果中？如果有，在什么位置？

(4) 在上面的查询的过程中如果加上保留字DISTINCT会有什么效果呢？ (5) 通过实验说明使用分组GROUP BY对取值为NULL的项的处理。

(6) 结合分组，使用集合函数求每个同学的平均分、总的选课记录、最高成绩、

最低成绩和总成绩。

(7) 查询成绩小于0的选课记录，统计总数、平均分、最大值和最小值。 (8) 采用嵌套查询的方式，利用比较运算符和谓词ALL的结合来查询表Courses

中最少的学分。假设数据库中只有一个记录的时候，使用前面的方法会得到什么结果，为什么？

(9) 创建一个学生表S（No，Sno，Sname），教师表T（No，Tno，Tname）

作为实验用的表。其中，No分别是这两个表的主键，其他键允许为空。 (10) 向S插入元组(n1，S01，李迪)、(n2，S02，李岚)、(n3，S05，NULL)、

(n4，S04，关红)；

11

(11) 向T插入元组(n1，T09，李迪)、(n2，T08，李兰)、(n3，T01，NULL)、

(n4，T02，NULL)。

(12) 对这两个表作对姓名的等值连接运算，找出既是老师又是学生的人员的学

生编号和教师编号。

12

实验7、实体完整性

7.1 试验目的

学习实体完整性的建立，以及实践违反实体完整性的结果。

7.2 试验内容

（1） 在数据库University_Mis中建立表Stu_Union，进行主键约束，在没有违反

实体完整性的前提下插入并更新一条记录。 （2） 演示违反实体完整性的插入操作。 （3） 演示违反实体完整性的更新操作。

（4） 演示事务的处理，包括事务的建立、处理以及出错时的事务回滚。

（5） 通过建立University_Misarship表，插入数据，演示当与现有的数据环境不

等时，无法建立实体完整性以及参照完整性。 7.3 实验步骤

以系统管理员或sa用户登录进入查询分析器，在查询分析器窗口中输入如下命令，运行并观察和记录结果。

（1） 在查询分析器中输入如下SQL语句：

USE University_Mis

CREATE TABLE Stu_Union(Sno CHAR(8) NOT NULL UNIQUE ,

Sname CHAR(8), Ssex CHAR(1), Sage INT, Sdept CHAR(20),

CONSTRAINT PK_Stu_Union PRIMARY KEY(Sno));

INSERT Stu_Union VALUES('S01','王兵','M',23,'CS'); UPDATE Stu_Union SET Sno='' WHERE Sdept='CS'; UPDATE Stu_Union SET Sno='S02' WHERE Sname='王兵'; SELECT * FROM Stu_union;

（2） 在查询分析器中输入如下SQL语句：

USE University_Mis

INSERT Stu_Union VALUES ('S02','黄山','M',23,'CS');

消息2627，级别14，状态1，第2 行

违反了PRIMARY KEY 约束'PK_Stu_Union'。不能在对象'dbo.Stu_Union' 中插入重复键。

（3） 在查询分析器中输入如下SQL语句：

USE University_Mis

UPDATE Stu_Union SET Sno =NULL WHERE Sno='S02';

不能将值NULL 插入列'Sno'，表'University_Mis.dbo.Stu_Union'；列不允许有Null 值。UPDATE 失败。

13

（4）

① 在查询分析器中输入如下SQL语句：

USE University_Mis SET XACT_ABORT ON BEGIN TRANSACTION T1

INSERT INTO Stu_union VALUES('S09','李永','M',25,'EE'); INSERT INTO Stu_union VALUES ('S03','黄浩','F',25,'EE'); INSERT INTO Stu_union VALUES ('S05','黄浩','F',25,'EE'); SELECT * FROM Stu_union; COMMIT TRANSACTION T1

② 在查询分析器中输入如下SQL语句：

USE University_Mis SET XACT_ABORT ON BEGIN TRANSACTION T2

INSERT INTO Stu_union VALUES ('S07','李宁','M',25,'EE'); SELECT * FROM Stu_union;

INSERT INTO Stu_union VALUES ('S09','李靖','F',22,'CS'); COMMIT TRANSACTION T2

消息2627，级别14，状态1，第6 行

违反了PRIMARY KEY 约束'PK_Stu_Union'。不能在对象'dbo.Stu_Union' 中插入重复键。

③ 在查询分析器中输入如下SQL语句：

USE University_Mis SELECT * FROM Stu_union;

（5）

① 在查询分析器中输入如下SQL语句：

USE University_Mis CREATE TABLE Scholarship (

M_ID VARCHAR(10), Stu_id CHAR(8),R_Money INT )

INSERT INTO Scholarship VALUES('M01','S07',5000) INSERT INTO Scholarship VALUES ('M01','S08',8000) SELECT * FROM Scholarship

② 在查询分析器中输入如下SQL语句：

USE University_Mis

ALTER TABLE Scholarship ADD

CONSTRAINT PK_Scholarship PRIMARY KEY(M_ID)

消息8111，级别16，状态1，第2 行

14

无法在表'Scholarship' 中可为Null 的列上定义PRIMARY KEY 约束。 消息1750，级别16，状态0，第2 行 无法创建约束。请参阅前面的错误消息。

③ 在查询分析器中输入如下SQL语句：

USE University_Mis

ALTER TABLE Scholarship ADD

CONSTRAINT FK_Scholarship FOREIGN KEY(Stu_id) REFERENCES Students(Sno)

消息1753，级别16，状态0，第2 行

列'Students.Sno' 的长度或小数位数与外键'FK_Scholarship' 中的引用列'Scholarship.Stu_id' 的长度或小数位数不同。参与构造外键关系的列必须定义为具有同一长度和小数位数。 消息1750，级别16，状态0，第2 行 无法创建约束。请参阅前面的错误消息。

15

实验8、参照完整性

8.1 实验目的

学习建立外键，以及利用FOREIGN KEY…REFERENCES子句以及各种约束保证参照完整性。

8.2 实验内容

（1） 为演示参照完整性，建立表Course，令Cno为其主键，并在Stu_Union

中插入数据。为下面的实验步骤做预先准备。

（2） 建立表SC，令Sno和Cno分别为参照Stu_Union表以及Course表的外

键，设定为级联删除，并令(Sno,Cno)为其主键。在不违反参照完整性的前提下，插入数据。

（3） 演示违反参照完整性的插入数据。

（4） 在Stu_Union中删除数据，演示级联删除。 （5） 在Course中删除数据，演示级联删除。

（6） 为了演示多重级联删除，建立Stu_Card表，令Stu_id为参数Stu_Union

表的外键，令Card_id为其主键，并插入数据。

（7） 为了演示多重级联删除，建立ICBC_Card表，令Stu_card_id为参数

Stu_Union表的外键，令Card_id为其主键，并插入数据。

（8） 通过删除Students表中的一条记录，演示三个表的多重级联删除。 （9） 演示事务中进行多重级联删除失败的处理。修改ICBC_Card表的外键

属性，使其变为On delete No action，演示事务中通过删除Students表中的一条记录，多重级联删除失败，整个事务回滚到事务的初始状态。 （10）演示互参考问题及其解决方法。要建立教师授课和课程指定教师听课关

系的两张表，规定一个教师可以授多门课，但是每个课程只能指定一个教师去听课，所以要为两张表建立相互之间的参照关系。 8.3 实验步骤

以系统管理员或sa账号登录查询分析器，在查询分析器窗体中输入如下命令，运行并观察和记录结果。

(1) 在查询分析器中输入如下SQL语句：

USE University_Mis

INSERT Stu_Union Values('S01','李用','0',24,'FF') SELECT * FROM Stu_Union; CREATE TABLE Course(

Cno CHAR(4) NOT NULL UNIQUE, Cname VARCHAR(50) NOT NULL, Cpoints INT,

CONSTRAINT PK PRIMARY KEY(Cno));

INSERT Course VALUES('C01','ComputerNetworks',2); INSERT Course VALUES('C02','ArtificialIntelligence',3);

16

(2) 在查询分析器中输入如下SQL语句：

USE University_Mis CREATE Table SC(

Sno CHAR(8),// 列'Stu_Union.Sno' 的长度或小数位数与外键'FK_SC_Sno' 中的引用列'SC.Sno' 的长度

或小数位数不同。参与构造外键关系的列必须定义为具有同一长度和小数位数。

Cno CHAR(4), Scredit INT,

CONSTRAINT PK_SC PRIMARY KEY(Sno,Cno),

CONSTRAINT FK_SC_Sno FOREIGN KEY(Sno) REFERENCES Stu_Union (Sno) ON DELETE CASCADE,

CONSTRAINT FK_SC_Cno FOREIGN KEY(Cno) REFERENCES Course (Cno) ON DELETE CASCADE );

INSERT INTO SC VALUES('S02','C01',2); INSERT INTO SC VALUES ('S02','C02',2); INSERT INTO SC VALUES ('S01','C01',2); INSERT INTO SC VALUES ('S01','C02',2); SELECT * FROM SC;

(3) 在查询分析器中输入如下SQL语句：

USE University_Mis

INSERT INTO SC VALUES('S99','C99',2);

消息547，级别16，状态0，第2 行

INSERT 语句与FOREIGN KEY 约束\冲突。该冲突发生于数据库\，表\。

(4) 在查询分析器中输入如下SQL语句：

USE University_Mis

DELETE FROM Stu_Union WHERE Sno='S01'; SELECT * FROM SC;

(5) 在查询分析器中输入如下SQL语句：

USE University_Mis

DELETE FROM Course WHERE Cno='C02'; SELECT * FROM SC;

(6) 在查询分析器中输入如下SQL语句：

USE University_Mis

CREATE TABLE Stu_Card( Card_id CHAR(14), Sno CHAR(5),

Remained_money DECIMAL(10,2),

17

Constraint PK_Stu_Card PRIMARY KEY(Card_id),

Constraint FK_Stu_Card_Sno FOREIGN KEY(Sno) Students(Sno) ON DELETE CASCADE )

INSERT INTO Stu_Card VALUES('05212567','S03',400.25); INSERT INTO Stu_Card VALUES('05212222','S02',600.50); SELECT * FROM Stu_card;

(7) 在查询分析器中输入如下SQL语句：

USE University_Mis CREATE TABLE ICBC_Card(

Bank_id CHAR(20), Stu_card_id CHAR(14),

Restored_money DECIMAL(10,2),

constraint PK_ICBC_Card PRIMARY KEY(Bank_id),

REFERENCES

constraint FK_ICBC_Card_Stu_id FOREIGN KEY(Stu_card_id) REFERENCES Stu_card(card_id) ON DELETE CASCADE )

INSERT INTO ICBC_Card VALUES('9558844022312','05212567',15000.1); INSERT INTO ICBC_Card VALUES('9558844023645','05212222',50000.3); SELECT * FROM ICBC_Card;

(8) 在查询分析器中输入如下SQL语句：

USE University_Mis ALTER TABLE Reports ADD

CONSTRAINT [FK_Reports_Students] FOREIGN KEY

(

[Sno] [Sno]

) REFERENCES [dbo].[Students] ( ) ON DELETE CASCADE;

DELETE FROM Students WHERE Sno='S03'; SELECT * FROM Stu_card; SELECT * FROM ICBC_Card;

(9) 在查询分析器中输入如下SQL语句：

ALTER TABLE ICBC_Card

DROP CONSTRAINT FK_ICBC_Card_Stu_id; ALTER TABLE ICBC_Card

ADD CONSTRAINT FK_ICBC_Card_Stu_id FOREIGN KEY (Stu_card_id) REFERENCES Stu_card(Card_id) ON DELETE NO ACTION;

在查询分析器中输入如下SQL语句：

Begin Transaction Del

18

DELETE FROM Stu_Card WHERE Card_id ='05212222'; SELECT * FROM Stu_card; SELECT * FROM ICBC_card; Commit Transaction Del

消息547，级别16，状态0，第2 行

DELETE 语句与REFERENCE 约束\冲突。该冲突发生于数据库\，表\。

在查询分析器中输入如下SQL语句：

USE University_Mis SELECT * FROM Stu_card; SELECT * FROM ICBC_card;

(10) 在查询分析器中输入如下SQL语句：

USE University_Mis

CREATE TABLE Listen_course( Tno CHAR(6),Tname VARCHAR(20),Cno CHAR(4) CONSTRAINT PK_listen_course PRIMARY KEY(Tno) CONSTRAINT FK_listen_course FOREIGN KEY(Cno) REFERENCES Teach_course(Cno)

)

CREATE TABLE Teach_course( Cno CHAR(4),Cname VARCHAR(30),Tno CHAR(6) CONSTRAINT PK_Teach_course PRIMARY KEY(Cno) CONSTRAINT FK_Teach_course FOREIGN KEY(Tno) REFERENCES Listen_course(Tno)

)

消息1767，级别16，状态0，第2 行

外键'FK_listen_course' 引用了无效的表'Teach_course'。 消息1750，级别16，状态0，第2 行 无法创建约束。请参阅前面的错误消息。

在查询分析器中输入如下SQL语句：

USE University_Mis

CREATE TABLE Listen_course( Tno CHAR(6),Tname VARCHAR(20),Cno CHAR(4) CONSTRAINT PK_listen_Course PRIMARY KEY(Tno)

)

在查询分析器中输入如下SQL语句：

USE University_Mis

CREATE TABLE Teach_course(

19

)

Cno CHAR(4),Cname VARCHAR(30),Tno CHAR(6) CONSTRAINT PK_teach_course PRIMARY KEY(Cno) CONSTRAINT FK_teach_course FOREIGN KEY(Tno) REFERENCES Listen_course(Tno)

ALTER TABLE Listen_course

ADD CONSTRAINT FK_listen_course FOREIGN KEY(Cno) REFERENCES Teach_course(Cno);

实验9. 用户自定义完整性

9.1 实验目的

学习用户自定义约束，并实践用户自定义完整性，利用SQL查询分析器用短语NOT NULL、UNIQUE、CHECK保证用户定义完整性。 9.2 实验内容（建议将表改成Teachers,相应属性以T开始命名，如Tname）建立Teacher表。 建立表Teacher,注意跟前面所建立的Teacher区分开来，

(1) 创建Worker，Teacher表，并自定义2个约束U1以及U2，其中U1规定

Wname Tname字段唯一，U2规定Wage, Tage (级别)字段的上限是28。 (2) 在Worker, Teacher表中插入一条合法记录。

(3) 演示插入违反U2约束的例子，U2规定元组的Wage,Tage属性的值必须

<=28。

(4) 去除U2约束。

(5) 重新插入(3)中想要插入的数据，由于去除了U2约束，所以插入成功。 (6) 创建规则Rule_sex，规定插入或更新的值只能是M或F，并绑定到Worker

Teacher的Wsex Tsex字段。

(7) 演示违反规则Rule_sex的插入操作。 9.3 实验步骤

以系统管理员或sa帐号登录查询分析器，在查询分析器窗体下输入如下命令，运行并观察结果。

(1) 在查询分析器中输入如下SQL语句:

USE University_Mis

CREATE TABLE Teacher( Tno CHAR(5),

Tname CHAR(8) CONSTRAINT U1 UNIQUE, Tsex CHAR(1),

Tage INT CONSTRAINT U2 CHECK (Tage<=28), Tdept CHAR(20),

CONSTRAINT PK_Teacher PRIMARY KEY(Tno))

20

(2) 在查询分析器中输入如下SQL语句

USE University_Mis

INSERT INTO Teacher (Tno, Tname,Tsex, Tage,Tdept) VALUES(‘T01’,’李用’,’M’,14,’后勤部’) SELECT * FROM Teacher

(3) 在查询分析器中输入如下SQL语句

USE University_Mis

INSERT INTO Teacher (Tno, Tname,Tsex, Tage,Tdept) Values(‘T02’,’王勇’,’M’,38,’ 后勤部’) SELECT * FROM Teacher

(4) 在查询分析器中输入如下SQL语句

USE University_Mis

ALTER TABLE Worker DROP U2

(5) 在查询分析器中输入如下SQL语句

USE University_Mis

INSERT INTO Teacher (Tno, Tname,Tsex, Tage,Tdept) VALUES(‘T02’,’王勇’,’M’,38,’ 后勤部’) SELECT * FROM Teacher

(6) 在查询分析器输入如下SQL语句

USE University_Mis Go

CREATE RULE Rule_sex AS @Value IN (‘F’,’M’) Go

EXEC SP_bindrule Rule_sex, ‘Teacher.[Tsex]’;

(7) 在查询分析器中输入如下SQL语句

USE University_Mis

INSERT INTO Teacher VALUES(‘T03’,’黄号’,’1’,’25’,’ 后勤部’)

21

实验10、触发器

10.1 实验目的

通过实验使学生加深对数据完整性的理解，学会创建和使用触发器。 10.2 实验内容 （用实验9的Teacher表）

(1) 为Teacher表建立触发器T1，当插入或使更新表中的数据时，保证所操作的纪录的Tage值大于0。

(2) 为Teacher表建立触发器T2，禁止删除编号为00001的CEO。

(3) Teacher表中的人员的编号是唯一且不可更变的，创建触发器T3实现更新中编号的不可改变性。

(4) 演示违反T1触发器的约束的插入操作。 (5) 演示违反T1触发器的约束的更新操作。 (6) 演示违反T2触发器的约束的插入操作。 (7) 演示违反T2触发器的约束的更新操作。 10.3 实验步骤

(1) 仍然使用自定义完整性实验中的Teacher表。为此表建立触发器T1，当插入或使更新表中的数据时，保证所操作的纪录的Tage值大于0。 在查询分析器中输入如下SQL语句

USE University_Mis GO

CREATE TRIGGER T1 ON Teacher FOR INSERT,UPDATE AS

IF(SELECT Tage FROM INSERTED)<1 BEGIN

PRINT ‘职工年龄必须是大于0的整数! 操作失败！’ ROLLBACK TRANSACTION END

(2) 为Teacher表建立触发器T2，禁止删除编号为S01的CEO。

在查询分析器中输入如下SQL语句 USE University_Mis GO

CREATE TRIGGER T2 ON Teacher FOR DELETE AS

IF(SELECT Tno FROM DELETED)=’T01’ BEGIN

PRINT ‘此人是CEO! 删除操作失败!’ ROLLBACK TRANSACTION END

22

(3) Teacher表中的人员的编号是唯一且不可更变的，创建触发器T3实现更新中编号的不可改变性。

在查询分析器中输入如下SQL语句 USE University_Mis GO

CREATE TRIGGER T3 ON Teacher FOR UPDATE AS

IF UPDATE(Tno) BEGIN

PRINT ‘职工编号不能修改！’ ROLLBACK TRANSACTION END

(4) 在查询分析器中输入如下SQL语句

USE University_Mis

INSERT INTO Teacher VALUES(‘T03’,’ 李宏’,’F’,-10,’开发部’)

(5) 在查询分析器中输入如下SQL语句

USE University_Mis

UPDATE Teacher SET Tage=-7 WHERE Tno=’T01’

(6) 在查询分析器中输入如下SQL语句

USE University_Mis

DELETE FROM Teacher WHERE Tname=’李用’

(7) 在查询分析器中输入如下SQL语句

USE University_Mis

UPDATE Teacher SET Tno=’T07’ WHERE Tsex=’F’ 23

特别要求与说明

1、每做一个实验都要写实验报告，报告格式见参考模板。

2、以上10个实验中用粗体字书写的实验内容要求同学必须完成，并将相应命令和执行结果窗口剪贴到实验报告中。

3、 本课程实验1至实验9的要求都是统一的，但每个同学在实验时必将其修改成针对自己的、具有个性化的特殊实验要求。修改的关键是在数据库、基本表和属性的命名中必须加上自己的姓名信息。操作方法如下：

① 基本表命名与数据库名类似，都是姓的全拼加上名的第一个字母和下划线组成前缀，比如黄德才姓名为Huang de cai，则前缀是huangdecai_，因此数据库University_Mis和基本表Students分别命名为Huangdc_University_Mis和Huangdc_Students。

② 属性名的前缀以姓名的第一个字母和下划线组成，比如，黄德才姓名为huang de cai，则属性的前缀是hdc_，基本表huangdc_Students中属性名为Sname, Scredit, Sroom就分别命名为hdc_Sname, hdc_Scredit, hdc_Sroom。 4、要求把实验过程、SQL命令和执行结果窗口复制到实验报告中，请从精品课程网站下载毕业设计报告模板。

5、实验报告通过精品课程网站提交。

24

--(1)查询年级为2001的所有学生的名称并按编号升序排列。 select sname from students where grade=2001 order by sid

--(2)查询学生的选课成绩合格的课程成绩，并把成绩换算为积点（60分对应积点为1，每增加1分，积点增加0.1）。

select score,(score-60)*0.1+1 from choices where score>=60

--(3)查询课时是48或64的课程的名称。 select cname from courses where hour=48 or hour=64

--(4)查询所有课程名称中含有data的课程编号。 select cid from courses where cname like 'úta%'

--(5)查询所有选课记录的课程号（不重复显示）。 select distinct cid from choices

--(6)统计所有教师的平均工资。 select avg(salary) from teachers

--(7)查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列。 select tid,avg(score) from choices group by tid order by avg(score) desc

--(8)统计各个课程的选课人数和平均成绩。 select count(sid),avg(score) from choices group by cid

--(9)查询至少选修了三门课程的学生编号。 select sid from choices

group by sid having count(cid)>=3

--(10)查询编号800009026的学生所选的全部课程的课程名和成绩。 select courses.cname,choices.score from courses,choices where choices.sid='800009026'

--(11)查询所有选修了database的学生的编号。 select choices.sid from choices,courses

where choices.cid=courses.cid and courses.cname='database'

--(12)求出选择了同一个课程的学生对。

25

select stu1.sid,stu2.sid from choices stu1,choices stu2 where stu1.cid=stu2.cid and stu1.sid<>stu2.sid

--(13)求出至少被两名学生选修的课程编号。 select cid from choices

group by cid having count(sid)>=2

--(14)查询选修了编号80009026的学生所选的某个课程的学生编号。 select 2.sid from choices a1,choices a2

where a1.sid='800009026' and a1.cid=a2.cid

--(15)查询学生的基本信息及选修课程编号和成绩。

select students.*,choices.cid,choices.score from students,choices

--(16)查询学号850955252的学生的姓名和选修的课程名及成绩。

select students.sname,courses.cname,choices.score from students,courses,choices

where students.sid='850955252' and students.sid=choices.sid and choices.cid=courses.cid

--(17)查询与学号850955252的学生同年纪的所有学生资料。 select a2.* from students a1,students a2

where a1.sid='850955252' and a1.grade=a2.grade

--(18)查询所有有选课的学生的详细信息。 select distinct students.* from students,choices where students.sid=choices.sid

--(19)查询没有学生选的课程的编号。 select cid from courses where cid not in

(select distinct cid from choices)

--(20)查询选修了课程名为C++的课时一样课程名称。 select c2.cname from courses c1,courses c2 where c1.cname='C++' and c1.hour=c2.hour

--(21)找出选修课程成绩最好的选课记录。 select * from choices

where score=(select max(score) from choices)

--(22)找出和课程UML或课程C++的课时一样课程名称。 select c3.cname from courses c1,courses c2,courses c3

where (c1.cname='UML' and c1.hour=c3.hour) or (c2.cname='C++' and c2.hour=c3.hour)

--(23)查询所有选修编号10001的课程的学生的姓名。

26

select students.sname from students,choices

where choices.cid='10001' and students.sid=choices.sid

--(24)查询选修了所有课程的学生姓名。 select sname from students where not exists

(select * from courses where not exists

(select * from choices

where sid=students.sid and cid=courses.cid))

--(25)利用集合运算，查询选修课程C++或选修课程Java的学生的编号。 select choices.sid from choices,courses

where courses.cname='C++' and choices.cid=courses.cid union

select choices.sid from choices,courses

where courses.cname='Java' and choices.cid=courses.cid

--(26)实现集合交运算，查询既选修课程C++又选修课程Java的学生的编号。 select choices.sid from choices,courses

where courses.cname='C++' and choices.cid=courses.cid intersect

select choices.sid from choices,courses

where courses.cname='Java' and choices.cid=courses.cid

--(27)实现集合减运算，查询选修课程C++而没有选修课程Java的学生的编号。select choices.sid from choices,courses

where courses.cname='C++' and choices.cid=courses.cid except

select choices.sid from choices,courses

where courses.cname='Java' and choices.cid=courses.cid

27

本文来源：https://www.bwwdw.com/article/2cm7.html