《数据库系统原理》课程实验.doc

数据库系统原理课程实验 姓 名： 班 级： 学 号： 佛山科学技术学院计算机系 2012年12月目录一、实验所需表格2二、实验内容32.1 实验一 SQL数据定义32.2 实验二 SQL数据查询72.3 实验三 连接、嵌套和集合查询142.4 实验四 SQL的数据更新212.5 实验五 视图的建立和维护242.6 实验六 触发器和存储过程30三、实验感想37参考资料1王珊 萨师煊.数据库系统概论（第四版）.高等教育出版社，20062李存斌.数据库应用技术-SQL Server 2000简明教程.中国水利水电出版社，20011、 实验所需表格学生-课程数据库xskc中用到的三个表文件如下：学生表：StudentSno（学号）Sname（姓名）Ssex（性别）Sage（年龄）Sdept（所在系）200215121李勇男20CS（计算机科学系）200215122刘晨女19CS（计算机科学系）200215123欧阳原野女18MA（数学系）200215125刘阳光男19IS（信息系）课程表：CourseCno（课程号）Cname（课程名）Cpno（先修课）Ccredit（学分）1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL64学生选课表：SCSno（学号）Cno（课程号）Grade（成绩）200215121192200215121285200215121388200215122290200215122380二、实验内容2.1 实验一 SQL数据定义一、实验目的和要求1掌握利用SQL查询分析器和企业管理器进行数据库及基本表的定义、删除与修改；2掌握索引的建立与删除的方法。二、实验内容及结果（一）建立数据库通过查询分析器或企业管理器建立学生-课程数据库xskc。create database xskcon ( name=xskc_data, filename=e:sjksyxskc_data.mdf)log on ( name=xskc_log, filename=e:sjksyxskc_log.ldf)注：先在E：盘上建立一个文件夹（例如：E:sjksy），数据库文件保存到自建的文件夹中。（二）基本表的定义、修改与删除1定义基本表利用查询分析器或企业管理器创建基本表，并输入数据。【题1-01】 建立一个学生表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。要求“学号”为主键，“姓名”不能为空,“性别”默认值为“男”。CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(20) NOT NULL, Ssex CHAR(2) DEFAULT 男, Sage INT, Sdept CHAR(20) ）;说明：在Microsoft SQL Server 2000的查询分析器(Query Analyzer)中使用单条SQL语句，其末尾不需要分号“；”作为命令结尾标记。通常，SQL Server 2000对大多数末尾带有分号的SQL命令都能顺利执行，但对少数的SQL命令，末尾若带分号，则SQL Server 2000会给出错误信息提示。 比如，若在实验五的例1的SQL命令末尾加上一个分号“；”，SQL Server 2000就会出现“Incorrect syntax near ;”的提示，虽然SQL Server 2000实际上已经执行了该命令。【题1-02】 建立课程表Course，它由课程号Cno、课程名Cname、先修课Cpno、Ccredit学分四个属性组成。要求“课程号”为主键，“课程名”属性不能为空。CREATE TABLE Course (Cno CHAR(4) PRIMARY KEY, Cname CHAR(40) NOT NULL , Cpno CHAR(4), Ccredit INT );【题1-03】 建立学生选修课表SC，包含学号Sno、课程号Cno、成绩Grade三个字段。要求建立主键及与student、kc表联接的外键，并创建检查约束(Grade=0 and Grade=100)。 CREATE TABLE SC ( Sno CHAR(9), Cno CHAR(4), Grade INT, PRIMARY KEY（Sno，Cno）， FOREIGN KEY(Sno) REFERENCES Student(Sno), FOREIGN KEY(Cno) REFERENCES Course(Cno) );2 修改基本表利用查询分析器修改基本表。【题1-04】 向基本表Student中增加“入学时间”属性列，其属性名为S_entrance，数据类型为日期型。ALTER TABLE Student ADD S_entrance DATETIME;【题1-05】 将Student表中Sage(年龄)的数据类型改为SMALLINT型。ALTER TABLE Student ALTER COLUMN Sage SMALLINT;【题1-06】 增加课程名称必须取唯一值的约束条件。ALTER TABLE Course ADD UNIQUE(Cname);【题1-07】将Student表的Sdept列允许空值的属性更改为不允许为空。ALTER TABLE Student ALTER COLUMN Sdept CHAR(20) NOT NULL; 【题1-08】删除Student表中的S_entrance列。ALTER TABLE Student DROP COLUMN S_entrance;3 删除基本表【题1-09】 删除Student表。DROP TABLE Student;说明：此表删除后，请立即将其建立起来，以便后面的例子使用。（三）索引的建立和删除1 建立索引【题1-10】 在基本表Student的Sname(姓名)列上建立一个聚簇索引，而且Student中的物理记录将按照Sname值的升序存放。其语句为：CREATE CLUSTERED INDEX Stu_Sname ON Student(Sname);【题1-11】分别为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按Sno(学号)升序建唯一索引，Course表按Cno(课程号)升序建唯一索引，SC表按Sno(学号)升序和Cno(课程号)号降序建唯一索引。其语句为：CREATE UNIQUE INDEX Stu_Sno ON Student(Sno);CREATE UNIQUE INDEX Cou_Cno ON Course(Cno);CREATE UNIQUE INDEX SCno ON SC(Sno ASC, Cno DESC);2 删除索引【题1-12】删除Student表的Stu_Sname索引。DROP INDEX Student.Stu_Sname;2.2 实验二 SQL数据查询一、实验目的和要求1掌握SQL Server查询分析器的使用方法，加深对SQL查询语句的理解。2熟练掌握查询语句的一般格式。3熟练掌握数据查询中的排序、分组、统计、计算和集合的操作方法。二、实验内容及结果1 无条件查询【题2-01】 查询全体学生的详细记录。这是一个无条件的选择查询，其命令为：SELECT * /*这里的“*”等价于ALL*/FROM Student;【题2-02】 查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。这是一个无条件的投影查询，其命令为：SELECT Sname, Sno, SdeptFROM Student;【题2-03】 查询全体学生的学号(Sno)、姓名(Sname)及出生年份。由于SELECT子句的不仅可以是表中的属性列，也可以是表达式，故可以查询经过计算的值。其命令为：SELECT Sno, Sname, 2012-Sage as 2012-SageFROM Student;【题2-04】 查询全体学生的学号、姓名、出生年份和所在系，要求用小写字母表示所有系名。其命令为：SELECT Sno, Sname, 2012-Sage Year of Birth, LOWER(Sdept) SdeptFROM Student;【题2-05】 查询选修了课程的学生学号。其命令为：SELECT DISTINCT SnoFROM SC;2 条件查询【题2-06】 查询数学系（MA）全体学生的学号(Sno)和姓名 (Sname)。其命令为：SELECT Sno, SnameFROM StudentWHERE Sdept=MA;【题2-07】查询考试成绩有不及格的学生的学号。SELECT DISTINCT SnoFROM SCWHERE Grade60;【题2-08】查询所有年龄在20岁以下的学生姓名(Sname)及年龄(Sage)。其命令为：SELECT Sname, SageFROM StudentWHERE Sage=18 AND Sage32.3 实验三 连接、嵌套和集合查询一、实验目的和要求1掌握SQL Server查询分析器的使用方法，加深对SQL查询语句的理解。2熟练掌握查询语句的一般格式。3熟练掌握连接、嵌套和集合查询的使用。二、实验内容及结果（一）连接查询1 不同表之间的连接查询【题3-01】 查询每个学生及其选修课程的情况。本查询实际上是涉及Student与SC两个表的连接操作。这两个表之间的联系是通过公共属性Sno实现的，因此，其操作命令为：SELECT Student.*, SC.*FROM Student, SCWHERE Student.Sno = SC.Sno； 说明：若在以上等值连接中把目标列中重复的属性列去掉则为自然连接，其命令为SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student, SCWHERE Student.Sno= SC.Sno;2 自身连接【例35】 查询每一门课的间接先修课(即先修课的先修课)。在Course表关系中，只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。这就需要要将Course表与其自身连接。为方便连接运算，这里为Course表取两个别名分别为A，B。则完成该查询的SQL语句为：SELECT A.Cno, A.Cname, B.CpnoFROM Course A, Course BWHERE A.Cpno =B.Cno;3 外连接【例36】把例33中的等值连接改为左连接。该左连接操作在SQL Server 2000中的命令格式为： SELECT Student.Sno, Sname, Ssex, Sdept, Cno, Grade FROM Student LEFT JOIN SC ON Student.Sno= SC.Sno;说明：以上左连接操作也可以用如下的右连接操作代替，其结果完全一样。 SELECT Student.Sno, Sname, Ssex, Sdept, Cno, Grade FROM SC RIGHT JOIN Student ON SC.Sno=Student.Sno;4 复合条件连接【例38】 查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。本查询涉及到三个表的连接操作，完成该查询的SQL语句如下：SELECT Student.Sno, Sname, Cname, GradeFROM Student, SC, CourseWHERE Student.Sno= SC.Sno AND SC.Cno=Course.Cno; （二）嵌套查询1 带谓词IN的嵌套查询【例39】查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。该查询可构造嵌套查询实现，其SQL语句如下：SELECT Sno, Sname, SdeptFROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname=李伟);说明：本例中的查询也可以用自身连接来完成，其SQL语句如下：SELECT A.Sno , A.Sname , A.SdeptFROM Student A , Student BWHERE A.Sdept=B.Sdept AND B.Sname=李伟;【例40】 查询选修了编号为“2”的课程的学生姓名(Sname)和所在系(Sdept)。SELECT Sname, SdeptFROM StudentsWHERE Sno IN (SELECT Sno FROM SC WHERE Cno=2);【例41】 查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。本查询涉及学号、姓名和课程名(Cname)三个属性。学号和姓名存放在Student表中，课程名的存放在Course表中，但Student与Course两个表之间没有公共属性，必须通过SC表建立它们之间的联系。所以本查询实际上涉及三个关系的连接操作。SELECT Sno, Sname /* 最后在Studen关系中 */FROM Student /* 取出Sno和Sname */WHERE Sno IN (SELECT Sno /* 然后在SC关系中找出 */ FROM SC /*选修了3号课程的学生学号*/ WHERE Cno IN (SELECT Cno /* 首先在Course关系中 */ FROM Course /*找出“数据结构”的课程号*/ WHERE Cname = 数据结构)； /*结果为5号 */说明：本查询同样可以用连接查询实现：SELECT S.Sno, SnameFROM Student S, SC R, Course CWHERE S.Sno=R.Sno AND R.Cno=C.Cno AND C.Cname=数据结构;2 带有比较运算符的嵌套查询【例42】 将例39改为带有比较运算符的嵌套查询。由于一个学生只可能在一个系学习，因此子查询的结果是一个值，因此可以用=代替IN，其SQL语句如下：SELECT Sno , Sname, SdeptFROM StudentWHERE Sdept = (SELECT Sdept FROM Student WHERE Sname=李伟);3 带谓词ANY或ALL的嵌套查询【例43】 查询计算机系（CS）的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。其查询命令为SELECT Sname, SageFROM StudentWHERE SdeptCS AND Sage=ALL (SELECT Sage FROM Student WHERE Sdept= CS);说明：本查询也可以用集函数来实现。其SQL语句如下：SELECT Sname, SageFROM StudentWHERE SdeptCS AND Sage= (SELECT MIN(Sage) FROM Student WHERE Sdept=CS);4 带谓词EXISTS的嵌套查询【例44】 查询所有选修了编号为“1”课程的学生姓名(Sname)和所在系(Sdept)。本查询的SQL语句是：SELECT Sname, SdeptFROM StudentWHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1);【例45】 将例39改为带谓词EXISTS的查询，其SQL语句如下SELECT Sno, Sname, SdeptFROM Student AWHERE EXISTS (SELECT * FROM Students B WHERE B.Sdept=A.Sdept AND B.Sname=李伟);【例46】 查询选修了所有课程的学生姓名(Sname)和所在系。由于没有全称量词，可将题目的意思转换成等价的用存在量词的形式：查询这样的学生，没有一门课程是他不选修的。其SQL语句为：SELECT Sname, SdeptFROM StudentWHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno=Student.SnoAND Cno=Course.Cno);（三）集合查询【例48】 查询计算机系（CS）的学生或年龄不大于20岁的学生信息。SELECT *FROM StudentWHERE Sdept=CSUNIONSELECT *FROM StudentWHERE Sage=20;【例49】 查询计算机系（CS）的学生且年龄不大于20岁的学生的交集，这实际上就是查询数学系中年龄不大于20岁的学生。SELECT *FROM StudentWHERE Sdept=CS AND Sage20;2.4 实验四 SQL的数据更新一、实验目的和要求掌握SQL数据插入、修改和删除语句的一般格式和使用方法。二、实验内容及结果1插入数据【题4-01】 将学生陈冬的信息（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。INSERTINTO Student(Sno,Sname,Ssex,Sdept,Sage)VALUES(200215128,陈冬,男,IS,18);或INSERTINTO Student(Sno,Sname,Ssex,Sage,Sdept)VALUES(200215128,陈冬,男,18,IS);或INSERTINTO StudentVALUES(200215128,陈冬,男,18,IS);【题4-02】 插入一条选课记录（200215128,1）INSERTINTO SC(Sno,Cno)VALUES(200215128,1);【题4-03】 设数据库中已有一个关系History_Student，其关系模式与Student完全一样，试将关系Student中的所有元组插入到关系History_Student中去，其SQL命令为：INSERTINTO History_StudentSELECT *FROM Student;【例4-04】对每一个系，求学生的平均年龄，并把结果存入表文件Dept_age中。Dept_age表文件属性为：Sdept（系名）、Avg_age（平均年龄）。INSERTINTO Dept_age(Sdept,Avg_age)SELECT Sdept,AVG(Sage)FROM StudentGROUP BY Sdept; 2修改数据【题4-05】 将学号为“200215121”的学生年龄改为22岁。即要修改满足条件的一个元组的属性值。UPDATE StudentSET Sage=22WHERE Sno=200215121;【题4-06】 将所有学生的年龄增加1岁。即要修改多个元组的值。UPDATE StudentSET Sage=Sage+1;【题4-07】将计算机科学系所有学生的成绩置零。由于学生所在系的信息在Student表中，而学习成绩在SC表中，因此，可以将SELECT子查询作为WHERE子句的条件表达式。故该更新要求的SQL命令为：UPDATE SCSET Grade=0WHERE CS=(SELECT Sdept FROM StudentWHERE Student.Sno=SC.Sno); 3删除数据 【题4-08】删除学号为200215128的学生记录。DELETEFROM StudentWHERE Sno=200215128;【题4-09】删除所有学生的选课记录。DELETEFROM SC;这条DELETE语句将删除SC的所有元组，使SC成为空表。【题4-10】删除计算机科学系所有学生的选课记录。DELETEFROM SC WHERE CS=(SELECT SdeptFROM StudentWHERE Student.Sno=SC.Sno);2.5 实验五 视图的建立和维护一、实验目的和要求1掌握SQL视图建立、修改和删除;2掌握SQL视图查询。二、实验内容及结果方法一：利用SQL语言实现视图的建立、删除、查询、更新（一）定义视图1 建立视图【题5-01】建立数学系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有数学系（MA）的学生，视图的属性名为Sno，Sname，Sage，Sdept。CREATE VIEW C_StudentASSELECT Sno, Sname, Sage, Sdept FROM StudentWHERE Sdept=MAWITH CHECK OPTION 【题5-02】建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图。本视图由三个基本表的连接操作导出，其SQL语句如下：CREATE VIEW Student_CRASSELECT Student.Sno, Sname, Cname, GradeFROM Student, SC, CourseWHERE Student.Sno= SC.Sno AND SC.Cno=Course.Cno【题5-03】定义一个反映学生出生年份的视图。CREATE VIEW Student_birth(Sno, Sname, Sbirth)AS SELECT Sno, Sname, 2007-SageFROM Student 2 删除视图【题5-04】删除视图Student_birth。DROP VIEW Student_birth;（二）查询视图【题5-05】在数学系的学生视图C_Student中找出年龄(Sage)小于20岁的学生姓名(Sname)和年龄(Sage)。SELECT Sname, SageFROM C_StudentWHERE Sage20;说明：本例转换后的查询语句为：SELECT Sname, Sage FROM StudentWHERE Sdept=MA AND Sage85; （三）更新视图【题5-07】将数学系(MA)学生视图C_Student中学号为200215123的学生姓名改为“黄海”。UPDATE C_StudentSET Sname=黄海WHERE Sno=200215123;说明：DBMS自动转换为对基本表的更新语句如下：UPDATE StudentSET Sname=黄海WHERE Sno=200215123 AND Sdept=数学;【题5-08】向数学系学生视图C_Student中插入一个新的学生记录，其中学号为“200215124”，姓名为“王海”，年龄为20岁。INSERTINTO C_StudentVALUES (200215124, 王海, 20, MA);【题5-09】删除数学系学生视图C_Student中学号为“200215124”的记录。DELETEFROM C_StudentWHERE Sno=200215124方法二：利用企业管理器和视图创建向导实现视图的建立、删除、查询、更新视图是根据子模式建立的虚拟表。一个视图可以由一个表构造，也可以由多个表构造。利用企业管理器和视图创建向导进行创建，查看和修改视图就如同对表的操作一样，非常容易。1创建视图下面利用了SQL Server中提供的视图创建向导，来实现建立计算机科学系（CS）选修了1号课程的学生视图。（1）打开企业管理器窗口，确认服务器，打开数据库文件夹，选中新视图所在的数据库（此例为xskc）。（2）选择菜单：工具|向导（3）在如图5-1所示的向导选择对话框中，单击数据库左边的“+”号，使之展开。选择“创建视图向导”项，单击“确定”按键。 图5-1 向导选择对话框（4）进入创建视图向导后，首先出现的是欢迎使用创建视图向导对话框，其中简单介绍了该向导的功能。单击“下一步”后，就会出现选择数据库对话框。（5）在选择数据库对话框中，选择视图所属的数据库。本例的数据库为“xskc”。单击“下一步”按键，则进入选择表对话框。（6）在选择表对话框中，列出了指定数据库中所有用户定义的表。用户可以从中选择构造视图所需的一个表或者多个表，被选中的表成为构造视图的参考表。选择构造视图参考表的方法是：用鼠标单击表后的“包含在视图中”列，使复选框为选中状态。在本例的数据库中选中学生表（Student）和选课表(SC)。单击“下一步”按键，则进入选择对话框。（7）列选择对话框中以表格形式列出了创建视图参考表的全部属性，每个属性占表的一行，创建视图参考属性可以在表格中选出。选择视图参考属性的方法是用鼠标单击属性名后边的“选择列”列，使复选框为选中状态。本例中Student.Sno、Student.Sname、SC.Grade属性要被选中。单击“下一步”按键，进入创建视图的定义限制对话框，如图5-2所示。 图5-2 定义限制对话框（8）在定义限制对话框中，输入表的连接和元组选择条件。本例输入: WHERE Sdept=CS and Student.Sno=SC.Sno and SC.Cno=1。单击“下一步”按键，出现视图名对话框。（9）在输入视图名对话框中输入所建视图的标识名。本例的视图名为“CSSC-View”。单击“下一步”按键，则出现视图创建完成对话框。在完成对话框中给出了根据前面对话框输入的内容译成的SQL语句。您可以认真阅读该SQL语句，如果发现与要求有不符合之处可以直接进行修改。确认无误后单击“完成”按钮。随后出现一个通知用户已成功创建视图的信息框，单击“确认”按钮后，整个创建视图工作就完成了。2查看和修改视图视图创建好后，就可以利用它进行查询信息了。如果发现视图的结构不能很好地满足要求，还可以在企业管理器对它进行修改。在企业管理器中修改视图结构需要首先选择服务器，数据库，并使数据库展开，然后用鼠标右击要修改的视图，在弹出的菜单上选择“设计视图”项，则弹出一个视图设计对话框，如图5-3所示。当对其修改完毕后关闭窗口，新的视图结构就会取代原先的结构。 图5-32.6 实验六 触发器和存储过程一、实验目的1.通过实验使学生加深对数据完整性的理解，学会创建和使用触发器。2.熟练创建存储过程，体会存储过程的作用。二、实验内容及结果1触发器在学生表Student中定义一个触发器tri1，保证新添加的学生的年龄（Sage）在1625岁之间。在成绩表SC中定义一个触发器tri2，当修改一个记录时，确保此记录的成绩(Grade)在0100分之间。在学生表Student中创建一个触发器tri2，当删除学生表Student中学生时，自动删除成绩表SC中相应学生的选课记录。在学生表Student中创建一个触发器tri4，当修改学生表Student中学生的学号时，自动修改成绩表SC中的相应学号值。并验证该触发器是否可以正常工作。在学生表Student中创建一个触发器tri5，当删除学生表Student中学生时，自动删除成绩表SC中相应学生的选课记录；当修改学生表Student中学生的学号时，自动修改成绩表SC中的相应学号值。并验证该触发器是否可以正常工作。2存储过程利用存储过程查找Student表中所有人的记录利用存储过程查找Student表中某学号的记录利用存储过程查找Student表中某学号、某门课程的记录计算出某学生的平均分创建一个带返回值的存储过程，返回某人的平均成绩创建一个存储过程pro6将课程中某门课程的学分修改为指定的值，其中某门课程的课程号、指定的值通过参数传递。并多次调用执行该存储过程来修改不同的课程的学分值。三、具体实现1触发器在学生表Student中定义一个触发器tri1，保证新添加的学生的年龄（Sage）在1625岁之间。【创建触发器】create trigger tri1 on studentfor insertasif(select Sage from inserted)25 or (select Sage from inserted)16 begin print 年龄在16-25岁之间 rollback end【调用触发器】insert into Student values(200231001,王涛,男,30,IS) 在成绩表SC中定义一个触发器tri2，当修改一个记录时，确保此记录的成绩(Grade)在0100分之间。create trigger tri2 on SCfor updateasif not exists(select * from inserted where Grade between 0 and 100)print 成绩应在0-100分之间rollback注：就是插入语句后，如果inserted表（插入或更新影响行构成的表）内的值不在0-100之间，就撤销插入。【调用触发器】update SCset Grade=120where Sno=200215122 在学生表Student中创建一个触发器tri3，当删除学生表Student中学生时，自动删除成绩表SC中相应学生的选课记录。【创建触发器】create trigger tri3 on studentfor deleteasdelete SC where Sno in(select Sno from deleted)delete Student where Sno in(select Sno from deleted)【调用触发器】 delete Student where Sno=200215122 【注意】实现级联删除时，因为Student表和SC表的主键、外