1、数据库系统原理实验指导书数据库系统原理课程实验指导书计算机科学与技术专业网络工程专业佛山科学技术学院计算机系2015年10月摘 要本书根据计算机科学与技术专业和网络工程专业人才培养计划和实验教学大纲的要求编写，全书包括9个实验：SQL数据定义功能实验，SQL数据基本查询,连接、嵌套和集合查询，SQL的数据更新，视图的建立和维护，触发器和存储过程，宿主语言操纵数据库实验，数据安全性实验，数据库备份和恢复实验。其中前6个为必做实验，后3个学生可在课外完成。公共邮箱：fosucomputer邮箱密码：computerSQL Server 2000前 言数据库系统产生于20世纪60年代末。40多年来，
2、数据库技术得到迅速发展，已形成较为完整的理论体系和一大批实用系统，现已成为计算机软件领域的一个重要分支。数据库系统原理是计算科学与技术专业的一门重要专业必修课程。为了使理论教学与实践教学紧密结合，注重学生的实践能力的培养，我们注重本课程实验教学的目的和任务是使学生通过实践环节深入理解和掌握数据库系统的基本理论，学会数据库设计方法、DBMS的使用，数据库系统的管理和维护，使学生得到数据库应用的基本训练，提高其解决实际问题的能力。按照实验教学大纲本实验只有10学时，所以只能开设最基本的实验，如果要具有数据库应用系统的设计开发能力，必须投入大量的课外时间；系统设计与实现的训练在“数据库课程设计”中解
3、决。实验指导书共包含9个实验，其中实验一实验六为必做实验，实验七实验九学生可用课外时间学习。目 录前 言实验一 SQL数据定义 1实验二 SQL数据查询 3实验三 连接、嵌套和集合查询 7实验四 SQL的数据更新 12实验五 视图的建立和维护 14实验六 触发器和存储过程 19实验七 宿主语言操纵数据库实验 23实验八 数据安全性实验 33实验九 数据库备份和恢复实验 38参考资料1.王珊 萨师煊.数据库系统概论（第四版）.高等教育出版社，20062.吴德胜.SQL Server入门经典. 机械工业出版社，20133.李存斌.数据库应用技术-SQL Server 2000简明教程.中国水利水电
4、出版社，2001 学生-课程数据库xskc中用到的三个表文件如下：教材P82学生表：StudentSno（学号）Sname（姓名）Ssex（性别）Sage（年龄）Sdept（所在系）200215121李勇男20CS（计算机科学系）200215122刘晨女19CS（计算机科学系）200215123王敏女18MA（数学系）200215125张立男19IS（信息系）课程表：CourseCno（课程号）Cname（课程名）Cpno（先修课）Ccredit（学分）1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64学生选课表：SCSno（学号）Cno（课程号）
5、Grade（成绩）200215121192200215121285200215121388200215122290200215122380实验一 SQL数据定义一、实验目的和要求1掌握利用SQL查询分析器和企业管理器进行数据库及基本表的定义、删除与修改；2掌握索引的建立与删除的方法。二、实验内容与步骤（一）建立数据库通过企业管理器或查询分析器建立学生-课程数据库xskc。create database xskcon ( name=xskc_data, filename=e:sjksyxskc_data.mdf)log on ( name=xskc_log, filename=e:sjksyxs
6、kc_log.ldf)注：先在E：盘上建立一个文件夹（例如：E:sjksy），数据库文件保存到自建的文件夹中。（二）基本表的定义、修改与删除1定义基本表利用查询分析器或企业管理器创建基本表，并输入数据。【题1-01】 建立一个学生表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。要求“学号”为主键，“姓名”不能为空,“性别”默认值为“男”。CREATE TABLE Student (Sno CHAR(9) PRIMARY KEY, Sname CHAR(8) NOT NULL, Ssex CHAR(2) DEFAULT 男, Sage
7、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-
8、02】 建立课程表Course，它由课程号Cno、课程名Cname、先修课Cpno、Ccredit学分四个属性组成。要求“课程号”为主键，“课程名”属性不能为空。CREATE TABLE Course (Cno CHAR(4) PRIMARY KEY, Cname CHAR(40) NOT NULL , Cpno CHAR(4), Ccredit INT, FOREIGN KEY(Cpno) REFERENCES Course(Cno) );【题1-03】 建立学生选修课表SC，包含学号Sno、课程号Cno、成绩Grade三个字段。要求建立主键及与student、kc表联接的外键，并创建检查约
9、束(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_entr
10、ance DATETIME;【题1-05】 将Student表中Sage(年龄)的数据类型改为SMALLINT型。ALTER TABLE Student ALTER COLUMN Sage SMALLINT;【题1-06】 对Course表，增加课程名称必须取唯一值的约束条件。ALTER TABLE Course ADD UNIQUE(Cname);【题1-07】将Student表的Sdept列允许空值的属性更改为不允许为空。ALTER TABLE Student ALTER COLUMN Sdept CHAR(20) NOT NULL;【题1-08】删除Student表中的S_entranc
11、e列。ALTER TABLE Student DROP COLUMN S_entrance;3 删除基本表【题1-09】 删除Student表。DROP TABLE Student;说明：此表删除后，请立即将其建立起来，以便后面的例子使用。（三）索引的建立和删除 教材P901 建立索引【题1-10】 在基本表Student的Sname(姓名)列上建立一个聚簇索引，而且Student中的物理记录将按照Sname值的升序存放。其语句为：CREATE CLUSTERED INDEX Stu_Sname ON Student(Sname);【题1-11】分别为学生-课程数据库中的Student，Cou
12、rse，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_Snam
13、e;实验二 SQL数据查询 P100P115一、实验目的和要求1掌握SQL Server查询分析器的使用方法，加深对SQL查询语句的理解。2熟练掌握查询语句的一般格式。3熟练掌握数据查询中的排序、分组、统计、计算和集合的操作方法。二、实验内容及步骤1 无条件查询【题2-01】 查询全体学生的详细记录。这是一个无条件的选择查询，其命令为：SELECT * /*这里的“*”等价于ALL*/FROM Student;其结果为Student表中的全部数据。【题2-02】 查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。这是一个无条件的投影查询，其命令为：SELECT Sname
14、, 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】 查询选
15、修了课程的学生学号。其命令为：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 Sage20;【题2-09】查询
16、所有年龄在1820岁(包括18岁和20岁)之间的学生姓名(Sname)及年龄(Sage)。其命令为：SELECT Sname, SageFROM StudentWHERE Sage=18 AND Sage3实验三 连接、嵌套和集合查询一、实验目的和要求1掌握SQL Server查询分析器的使用方法，加深对SQL查询语句的理解。2熟练掌握查询语句的一般格式。3熟练掌握连接、嵌套和集合查询的使用。二、实验内容及步骤（一）连接查询1 不同表之间的连接查询【题3-01】 查询每个学生及其选修课程的情况。本查询实际上是涉及Student与SC两个表的连接操作。这两个表之间的联系是通过公共属性Sno实现的
17、，因此，其操作命令为：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表关系中，只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到
18、其先修课，再按此先修课的课程号，查找它的先修课程。这就需要要将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, GradeFROM StudentLEFT JOIN SC ONStuden
19、t.Sno= SC.Sno;说明：以上左连接操作也可以用如下的右连接操作代替，其结果完全一样。SELECT Student.Sno, Sname, Ssex, Sdept, Cno, GradeFROM SCRIGHT JOIN Student ONSC.Sno=Student.Sno;4 复合条件连接【例37】【例38】 查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。本查询涉及到三个表的连接操作，完成该查询的SQL语句如下：SELECT Student.Sno, Sname, Cname, GradeFROM Student, SC, Co
20、urseWHERE 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.SdeptF
21、ROM Student A , Student BWHERE A.Sdept=B.Sdept AND B.Sname=李伟;【例40】 查询选修了编号为“2”的课程的学生姓名(Sname)和所在系(Sdept)。SELECT Sname, SdeptFROM StudentWHERE Sno IN (SELECT Sno FROM SC WHERE Cno=2);【例41】 查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。本查询涉及学号、姓名和课程名(Cname)三个属性。学号和姓名存放在Student表中，课程名的存放在Course表中，但Student与Course
22、两个表之间没有公共属性，必须通过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改为带有比较运算符的嵌套查询。由于一个学生只可能在一个系学习，因此子查询的
