10000101101实验1数据定.docx

1、10000101101实验1数据定实验1、数据定义1.1 实验目的熟悉SQL的数据定义语言，能够熟练地使用SQL语句来创建和更改基本表，创建和取消索引。1.2 实验内容 用企业管理器创建数据库University_Mis 使用CREATE语句创建基本表。 更改基本表的定义: 增加列，删除列，修改列的数据类型。 创建表的升、降序索引。 删除基本表的约束、基本表的索引或基本表。1.3 实验步骤(1) 用企业管理器创建数据库University_Mis(2) 在查询分析器中用SQL语句创建关系数据库基本表：学生表Students(Sno,Sname, Semail,Scredit,Sroom)；教师

2、表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的数据类型改成长度

3、为30。(4) 删除表Students的一个属性Sroom。(5) 删除表Reports。(6) 为Courses表创建按Cno降序排列的索引。(7) 为Students表创建按Sno升序排列的索引。(8) 创建表Students的按Sname升序排列的唯一性索引。(9) 删除Students表Sno的升序索引。实验2、SQL的数据查询2.1 实验目的熟悉SQL语句的数据查询语言，能够SQL语句对数据库进行单表查询、连接查询、嵌套查询、集合查询和统计查询。2.2 实验内容实验内容主要是对数据库进行查询操作，包括如下四类查询方式：（1） 单表查询 查询的目标表达式为所有列、指定的列或指定的列的运

4、算三种不同。 使用DISTINCT保留字消除重复行。 对查询结果排序和分组。 集合分组使用集函数进行各项统计。（2） 连接查询 笛卡儿连接和等值连接。 自连接。 外连接 复合条件连接。 多表连接。（3） 嵌套查询 通过实验验证对子查询的两个限制条件。 体会相关子查询和不相关子查询的不同。 考察四类谓词的用法，包括：第一类，IN、NOT IN；第二类，带有比较运算符的子查询；第三类，SOME、ANY或ALL谓词的子查询，查询最大值和最小值；第四类，带有EXISTS谓词的子查询，实现“所有”等情况（如王宏的“所有”课程，“所有”女生选修的课程）（4） 集合运算 使用保留字UNION进行集合或运算。

5、 采用逻辑运算符AND或OR来实现集合交和减运算。2.3 实验步骤以University_Mis数据库为例，该数据库中有四张如实验1，其中Score是每门课的考试成绩，Scredit是学生所有考试合格课程所获得的积分总数，Ccredit每门课程的学分数。在数据库中，存在这样的联系：学生可以选择课程，一个课程对应一个教师。在表Reports中保存学生的选课记录和考试成绩。请先输入如下符合条件的元组后，再对数据库进行有关的查询操作： 图1.1、Students表 图1.2、Teachers表 图1.3、Courses表 图1.4、Reports表（1） 查询性别为“男”的所有学生的名称并按学号升序

6、排列。（2） 查询学生的选课成绩合格的课程成绩，并把成绩换算为积分。积分的计算公式为：1+(考试成绩-60)*0.1*Ccredit。考试成绩=60 否则=0（3） 查询学分是3或4的课程的名称。（4） 查询所有课程名称中含有“算法”的课程编号。（5） 查询所有选课记录的课程号（不重复显示）。（6） 统计所有老师的平均工资。（7） 查询所有教师的编号及选修其课程的学生的平均成绩，按平均成绩降序排列。（8） 统计各个课程的选课人数和平均成绩。（9） 查询至少选修了三门课程的学生编号和姓名。（10） 查询编号S26的学生所选的全部课程的课程名和成绩。（11） 查询所有选了“数据库原理及其应用”课程

7、的学生编号和姓名。（12） 求出选择了同一个课程的学生对。（13） 求出至少被两名学生选修的课程编号。（14） 查询选修了编号S26的学生所选的某个课程的学生编号。（15） 查询学生的基本信息及选修课程编号和成绩。（16） 查询学号S52的学生的姓名和选修的课程名称及成绩。（17） 查询和学号S52的学生同性别的所有学生资料。（18） 查询所有选课的学生的详细信息。（19） 查询没有学生选的课程的编号和名称。（20） 查询选修了课程名为C+的学生学号和姓名。（21） 找出选修课程UML或者课程C+的学生学号和姓名。（22） 找出和课程UML或课程C+的学分一样课程名称。（23） 查询所有选修编

8、号C01的课程的学生的姓名。（24） 查询选修了所有课程的学生姓名。（25） 利用集合查询方式，查询选修课程C+或选择课程JAVA的学生的编号、姓名和积分。（26） 实现集合交运算，查询既选修课程C+又选修课程JAVA的学生的编号、姓名和积分。（27） 实现集合减运算，查询选修课程C+而没有选修课程JAVA的学生的编号。实验3、数据更新3.1 实验目的熟悉数据库的数据更新操作，能够使用SQL语句对数据库进行数据的插入、更新、删除操作。3.2 实验内容 在本实验中，主要内容是如何用SQL语句对数据进行更新。 使用INSERT INTO语句插入数据，包括插入一个元组或将子查询的结果插入到数据库中两

9、种方式。 使用SELECT INTO语句，产生一个新表并插入数据。 使用UPDATE语句可以修改指定表中满足WHERE子句条件的元组，有三种修改的方式：修改某一个元组的值；修改多个元组的值；带子查询地修改语句。 使用DELETE语句删除数据：删除某一个元组的值；删除多个元组的值；带子查询地删除语句。3.3 实验步骤在数据库University_Mis上按下列要求进行数据更新。(1) 使用SQL语句向Students表中插入元组(Sno：S78; Sname：李迪; Semail:LD; Scredit：0；Ssex：男)。(2) 对每个课程，求学生的选课人数和学生的平均成绩，并把结果存入数据库

10、。使用SELECT INTO 和INSERT INTO 两种方法实现。(3) 在Students表中使用SQL语句将姓名为李迪的学生的学号改为S70。(4) 在Teachers表中使用SQL语句将所有教师的工资加500元。(5) 将姓名为刘华的学生的课程“数据库原理及其应用”的成绩加上6分。(6) 在Students表中使用SQL语句删除姓名为李迪的学生信息。(7) 删除所有选修课程JAVA的选修课记录。(8) 对Courses表做删去学分=4的元组操作，讨论该操作所受到的约束。实验4、SQL的视图4.1 实验目的熟悉SQL支持的有关视图的操作，能够熟练使用SQL语句来创建需要的视图，对视图进

11、行查询和取消视图。4.2 实验内容（1） 定义常见的视图形式，包括： 行列子集视图 WITH CHECK OPTION的视图 基于多个基表的视图 基于视图的视图 带表达式的视图 分组视图（2） 通过实验考察WITH CHECK OPTION这一语句在视图定义后产生的影响，包括对修改操作、删除操作、插入操作的影响。（3） 讨论视图的数据更新情况，对子行列视图进行数据更新。（4） 使用DROP语句删除一个视图，由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除。同样的原因，删除基表时，由该基表导出的所有视图定义都必须显式删除。4.3 实验步骤（1） 创建一个行列子集视图CS_Vi

12、ew，给出选课成绩合格的学生的编号、教师编号、所选课程号和该课程成绩。（2） 创建基于多个基本表的视图SCT_View，这个视图由学生姓名和他所选修的课程名及讲授该课程的教师姓名构成。（3） 创建带表达式的视图EXP_View，由学生姓名及所选课程名和所有课程成绩都比原来多5分这几个属性组成。（4） 创建分组视图Group_View，将学生的学号及他的平均成绩定义为一个视图。（5） 创建一个基于视图的视图，基于（1）中建立的视图，定义一个包括学生编号、学生所选课程数目和平均成绩的视图VV_View。（6） 查询所有选修课程“数据库原理及其应用”的学生姓名。（7） 插入元组（S52,T02,C0

13、2,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实验5、数据控制5.1 实验目的熟悉SQL的数据控制功能，能够使用SQL语句来向用户授予和收回权限。5.2 实验内

14、容(1) 使用GRANT语句来对用户授权，对单个用户或多个用户授权，或使用保留字PUBLIC对所有用户授权。对不同的操作对象包括数据库、视图、基本表等进行不同权限的授权。(2) 使用WITH GRANT OPTION字句授予用户传播该权限的权利。(3) 当在授权时发生循环授权，考察DBS能否发现这个错误。如果不能，结合取消权限操作，查看DBS对循环授权的控制。(4) 使用REVOKE子句收回授权，取消授权的级联反应。5.3 实验步骤用企业管理器在数据库University_Mis中建立三个用户USER1、USER2和USER3，他们在数据库中的角色是PUBLIC。请按以下要求，分别以管理员身份

15、或这三个用户的身份登陆到数据库中，进行操作，并记录操作结果。(1) 授予所有用户对表Courses的查询权限。以USER1的身份登陆查询分析器，用SQL语言查询Courses和Students表，查询结果如何？(2) 授予用户USER1对表Students插入和更新的权限，但不授予删除权限，并且授予用户USER1传播这两个权限的权利。以USER？的身。(3) 允许用户USER2在表Reports中插入元组，更新Score列，可以查询除了Sno以外的所有列。以USER？的身。(4) 用户USER1授予用户USER2对表Students插入和更新的权限，并且授予用户USER2传播插入操作的权利。以

16、USER？的身。(5) 收回对用户USER1对表Courses查询权限的授权。以USER？的身。(6) 由上面（2）和（4）的授权，再由用户USER2对用户USER3授予表Students插入和更新的权限，并且授予用户USER3传播插入操作的权力。这时候，如果由USER3对USER1授予表Students的插入和更新权限是否能得到成功？如果能够成功，那么如果有用户USER2取消USER3的权限，对USER1会有什么影响？如果再由DBA取消USER1的权限，对USER2有什么影响？实验6、SQL的空值和空集处理6.1 实验目的认识NULL值在数据库中的特殊含义，了解空值和空集对于数据库的数据查询

17、操作，特别是空值在条件表达式中与其他的算术运算符或者逻辑运算符的运算中，空集作为嵌套查询的子查询的返回结果时候的特殊性，能够熟练使用SQL语句来进行与空值，空集相关的操作。6.2 实验内容通过实验验证在原理解析中分析过的SQL Server对NULL的处理，包括： 在查询的目标表达式中包含空值的运算。 在查询条件中空值与比较运算符的运算结果。 使用IS NULL或IS NOT NULL 来判断元组该列是否为空值。 对存在取空值的列按值进行ORDER BY排序。 使用保留字DISTINCT对空值的处理，区分数据库的多中取值与现实中的多种取值的不同。 使用 GROUP BY对存在取空值的属性值进行

18、分组。 结合分组考察空值对各个集合函数的影响，特别注意对COUNT（*）和COUNT（列名）的不同影响。 考察结果集是空集时，各个集函数的处理情况。 验证嵌套查询中返回空集的情况下与各个谓词的运算结果。 进行与空值有关的等值连接运算。6.3 实验步骤(1) 查询所有选课记录的成绩并将它换算为五分制（满分为5分，合格为3分），注意,创建表时允许Score取NULL值。(2) 通过查询选修编号C07的课程的学生的人数，其中成绩合格的学生人数，不合格的人数，讨论NULL值的特殊含义。(3) 通过实验检验在使用ORDER BY进行排序时，取NULL的项是否出现在结果中？如果有，在什么位置？(4) 在上

19、面的查询的过程中如果加上保留字DISTINCT会有什么效果呢？(5) 通过实验说明使用分组GROUP BY对取值为NULL的项的处理。(6) 结合分组，使用集合函数求每个同学的平均分、总的选课记录、最高成绩、最低成绩和总成绩。(7) 查询成绩小于0的选课记录，统计总数、平均分、最大值和最小值。(8) 采用嵌套查询的方式，利用比较运算符和谓词ALL的结合来查询表Courses中最少的学分。假设数据库中只有一个记录的时候，使用前面的方法会得到什么结果，为什么？(9) 创建一个学生表S（No，Sno，Sname），教师表T（No，Tno，Tname）作为实验用的表。其中，No分别是这两个表的主键，其

20、他键允许为空。(10) 向S插入元组(n1，S01，李迪)、(n2，S02，李岚)、(n3，S05，NULL)、(n4，S04，关红)；(11) 向T插入元组(n1，T09，李迪)、(n2，T08，李兰)、(n3，T01，NULL)、(n4，T02，NULL)。(12) 对这两个表作对姓名的等值连接运算，找出既是老师又是学生的人员的学生编号和教师编号。实验7、实体完整性7.1 试验目的学习实体完整性的建立，以及实践违反实体完整性的结果。7.2 试验内容（1） 在数据库University_Mis中建立表Stu_Union，进行主键约束，在没有违反实体完整性的前提下插入并更新一条记录。（2） 演

21、示违反实体完整性的插入操作。（3） 演示违反实体完整性的更新操作。（4） 演示事务的处理，包括事务的建立、处理以及出错时的事务回滚。（5） 通过建立University_Misarship表，插入数据，演示当与现有的数据环境不等时，无法建立实体完整性以及参照完整性。7.3 实验步骤以系统管理员或sa用户登录进入查询分析器，在查询分析器窗口中输入如下命令，运行并观察和记录结果。（1） 在查询分析器中输入如下SQL语句：USE University_MisCREATE TABLE Stu_Union(Sno CHAR(8) NOT NULL UNIUE, Sname CHAR(8), Ssex C

22、HAR(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_MisINSERT Stu_Union VALUES (S02,黄山,M,23

23、,CS);（3） 在查询分析器中输入如下SQL语句：USE University_MisUPDATE Stu_Union SET Sno =NULL WHERE Sno=S02;（4）1 在查询分析器中输入如下SQL语句：USE University_MisSET XACT_ABORT ONBEGIN TRANSACTION T1INSERT 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,E

24、E);SELECT * FROM Stu_union;COMMIT TRANSACTION T12 在查询分析器中输入如下SQL语句：USE University_MisSET XACT_ABORT ONBEGIN TRANSACTION T2INSERT INTO Stu_union VALUES (S07,李宁,M,25,EE);SELECT * FROM Stu_union;INSERT INTO Stu_union VALUES (S09,李靖,F,22,CS);COMMIT TRANSACTION T23 在查询分析器中输入如下SQL语句：USE University_MisSELE

25、CT * FROM Stu_union;（5）1 在查询分析器中输入如下SQL语句：USE University_MisCREATE 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 Scholarship2 在查询分析器中输入如下SQL语句：USE University_MisALTER TABLE Schol

26、arship ADDCONSTRAINT PK_Scholarship PRIMARY KEY(M_ID)3 在查询分析器中输入如下SQL语句：USE University_MisALTER TABLE Scholarship ADDCONSTRAINT FK_Scholarship FOREIGN KEY(Stu_id) REFERENCES Students(Sno)实验8、参照完整性8.1 实验目的学习建立外键，以及利用FOREIGN KEYREFERENCES子句以及各种约束保证参照完整性。8.2 实验内容（1） 为演示参照完整性，建立表Course，令Cno为其主键，并在Stu_Un

27、ion中插入数据。为下面的实验步骤做预先准备。（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_U

28、nion表的外键，令Card_id为其主键，并插入数据。（8） 通过删除Students表中的一条记录，演示三个表的多重级联删除。（9） 演示事务中进行多重级联删除失败的处理。修改ICBC_Card表的外键属性，使其变为On delete No action，演示事务中通过删除Students表中的一条记录，多重级联删除失败，整个事务回滚到事务的初始状态。（10） 演示互参考问题及其解决方法。要建立教师授课和课程指定教师听课关系的两张表，规定一个教师可以授多门课，但是每个课程只能指定一个教师去听课，所以要为两张表建立相互之间的参照关系。8.3 实验步骤以系统管理员或sa账号登录查询分析器，在查

29、询分析器窗体中输入如下命令，运行并观察和记录结果。(1) 在查询分析器中输入如下SQL语句：USE University_MisINSERT 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

30、 Course VALUES(C02,ArtificialIntelligence,3);(2) 在查询分析器中输入如下SQL语句：USE University_MisCREATE Table SC(Sno CHAR(5),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