《数据库系统原理》实验报告.docx
《《数据库系统原理》实验报告.docx》由会员分享，可在线阅读，更多相关《《数据库系统原理》实验报告.docx（64页珍藏版）》请在冰豆网上搜索。
《数据库系统原理》实验报告
《数据库系统原理》课程实验指导书
计算机科学与技术专业
网络工程专业
佛山科学技术学院计算机系
2014年10月
摘要
本书根据计算机科学与技术专业和网络工程专业人才培养计划和实验教学大纲的要求编写，全书包括9个实验：
SQL数据定义功能实验，SQL数据基本查询,连接、嵌套和集合查询，SQL的数据更新，视图的建立和维护，触发器和存储过程，宿主语言操纵数据库实验，数据安全性实验，数据库备份和恢复实验。
其中前6个为必做实验，后3个学生可在课外完成。
公共邮箱：
fosucomputer@
邮箱密码：
computer
SQLServer2000
前言
数据库系统产生于20世纪60年代末。
40多年来，数据库技术得到迅速发展，已形成较为完整的理论体系和一大批实用系统，现已成为计算机软件领域的一个重要分支。
数据库系统原理是计算科学与技术专业的一门重要专业必修课程。
为了使理论教学与实践教学紧密结合，注重学生的实践能力的培养，我们注重本课程实验教学的目的和任务是使学生通过实践环节深入理解和掌握数据库系统的基本理论，学会数据库设计方法、DBMS的使用，数据库系统的管理和维护，使学生得到数据库应用的基本训练，提高其解决实际问题的能力。
按照实验教学大纲本实验只有12学时，所以只能开设最基本的实验，如果要具有数据库应用系统的设计开发能力，必须投入大量的课外时间；系统设计与实现的训练在“数据库课程设计”中解决。
实验指导书共包含9个实验，其中实验一～实验六为必做实验，实验七～实验九学生可用课外时间学习。
前言
实验一SQL数据定义……………………………………………………………………1
实验二SQL数据查询……………………………………………………………………3
实验三连接、嵌套和集合查询……………………………………………………………7
实验四SQL的数据更新………………………………………………………………12
实验五视图的建立和维护………………………………………………………………14
实验六触发器和存储过程……………………………………………………………19
实验七宿主语言操纵数据库实验……………………………………………………23
实验八数据安全性实验………………………………………………………………33
实验九数据库备份和恢复实验………………………………………………………38
参考资料
1．王珊萨师煊.《数据库系统概论》（第四版）.高等教育出版社，2006
2．李存斌.数据库应用技术---SQLServer2000简明教程.中国水利水电出版社，2001
学生-课程数据库xskc中用到的三个表文件如下：
学生表：
Student
Sno（学号）
Sname（姓名）
Ssex（性别）
Sage（年龄）
Sdept（所在系）
200215121
李勇
男
20
CS（计算机科学系）
200215122
刘晨
女
19
CS（计算机科学系）
200215123
王敏
女
18
MA（数学系）
200215125
张立
男
19
IS（信息系）
课程表：
Course
Cno（课程号）
Cname（课程名）
Cpno（先修课）
Ccredit（学分）
1
数据库
5
4
2
数学
2
3
信息系统
1
4
4
操作系统
6
3
5
数据结构
7
4
6
数据处理
2
7
PASCAL
6
4
学生选课表：
SC
Sno（学号）
Cno（课程号）
Grade（成绩）
200215121
1
92
200215121
2
85
200215121
3
88
200215122
2
90
200215122
3
80
实验一SQL数据定义
一、实验目的和要求
1．掌握利用SQL查询分析器和企业管理器进行数据库及基本表的定义、删除与修改；
2．掌握索引的建立与删除的方法。
二、实验内容与步骤
（一）建立数据库
通过企业管理器或查询分析器建立学生-课程数据库xskc。
createdatabasexskc
on
（name=xskc_data,
filename='d:
\sjksy\xskc_data.mdf'）
logon
（name=xskc_log,
filename='d:
\sjksy\xskc_log.ldf'）
注：
先在E：
盘上建立一个文件夹（例如：
E:
\sjksy），数据库文件保存到自建的文件夹中。
（二）基本表的定义、修改与删除
1．定义基本表
利用查询分析器或企业管理器创建基本表，并输入数据。
【题1-01】建立一个学生表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。
要求“学号”为主键，“姓名”不能为空,“性别”默认值为“男”。
CREATETABLEStudent
（SnoCHAR（9）PRIMARYKEY,S
SnameCHAR（8）NOTNULL,
SsexCHAR
（2）DEFAULT‘男’,
SageINT,
SdeptCHAR（20）
）;
说明：
在MicrosoftSQLServer2000的查询分析器（QueryAnalyzer）中使用单条SQL语句，其末尾不需要分号“；”作为命令结尾标记。
通常，SQLServer2000对大多数末尾带有分号的SQL命令都能顺利执行，但对少数的SQL命令，末尾若带分号，则SQLServer2000会给出错误信息提示。
比如，若在实验五的例1的SQL命令末尾加上一个分号“；”，SQLServer2000就会出现“Incorrectsyntaxnear';'”的提示，虽然SQLServer2000实际上已经执行了该命令。
【题1-02】建立课程表Course，它由课程号Cno、课程名Cname、先修课Cpno、Ccredit学分四个属性组成。
要求“课程号”为主键，“课程名”属性不能为空。
CREATETABLECourse
（CnoCHAR（4）PRIMARYKEY,
CnameCHAR（40）NOTNULL,
CpnoCHAR（4）,
CcreditINT,
FOREIGNKEY（Cpno）REFERENCESCourse（Cno）
）;
【题1-03】建立学生选修课表SC，包含学号Sno、课程号Cno、成绩Grade三个字段。
要求建立主键及与student、kc表联接的外键，并创建检查约束（Grade>=0andGrade<=100）。
CREATETABLESC
（SnoCHAR（9）,
CnoCHAR（4）,
GradeINT,
PRIMARYKEY（Sno,Cno）,
FOREIGNKEY（Sno）REFERENCESStudent（Sno）,
FOREIGNKEY（Cno）REFERENCESCourse（Cno）
）;
2修改基本表
利用查询分析器或企业管理器修改基本表。
【题1-04】向基本表Student中增加“入学时间”属性列，其属性名为S_entrance，数据类型为日期型。
ALTERTABLEStudentADDS_entranceDATETIME;
【题1-05】将Student表中Sage（年龄）的数据类型改为SMALLINT型。
ALTERTABLEStudentALTERCOLUMNSageSMALLINT;
【题1-06】对Course表，增加课程名称必须取唯一值的约束条件。
ALTERTABLECourseADDUNIQUE（Cname）;
【题1-07】将Student表的Sdept列允许空值的属性更改为不允许为空。
ALTERTABLEStudentALTERCOLUMNSdeptCHAR（20）NOTNULL;
【题1-08】删除Student表中的S_entrance列。
ALTERTABLEStudentDROPCOLUMNS_entrance;
3删除基本表
【题1-09】删除Student表。
DROPTABLEStudent;
说明：
此表删除后，请立即将其建立起来，以便后面的例子使用。
（三）索引的建立和删除
1建立索引
【题1-10】在基本表Student的Sname（姓名）列上建立一个聚簇索引，而且Student中的物理记录将按照Sname值的升序存放。
其语句为：
CREATECLUSTEREDINDEXStu_SnameONStudent（Sname）;
【题1-11】分别为学生-课程数据库中的Student，Course，SC三个表建立索引。
其中Student表按Sno（学号）升序建唯一索引，Course表按Cno（课程号）升序建唯一索引，SC表按Sno（学号）升序和Cno（课程号）号降序建唯一索引。
其语句为：
CREATEUNIQUEINDEXStu_SnoONStudent（Sno）;
CREATEUNIQUEINDEXCou_CnoONCourse（Cno）;
CREATEUNIQUEINDEXSCnoONSC（SnoASC,CnoDESC）;
2删除索引
【题1-12】删除Student表的Stu_Sname索引。
DROPINDEXStudent.Stu_Sname;
实验二SQL数据查询
一、实验目的和要求
1．掌握SQLServer查询分析器的使用方法，加深对SQL查询语句的理解。
2．熟练掌握查询语句的一般格式。
3．熟练掌握数据查询中的排序、分组、统计、计算和集合的操作方法。
二、实验内容及步骤
1无条件查询
【题2-01】查询全体学生的详细记录。
这是一个无条件的选择查询，其命令为：
SELECT*/*这里的“*”等价于ALL*/
FROMStudent;
其结果为Student表中的全部数据。
【题2-02】查询全体学生的姓名（Sname）、学号（Sno）、所在系（Sdept）。
这是一个无条件的投影查询，其命令为：
SELECTSname,Sno,Sdept
FROMStudent;
【题2-03】查询全体学生的学号（Sno）、姓名（Sname）及出生年份。
由于SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式，故可以查询经过计算的值。
其命令为：
SELECTSno,Sname,2014-Sageas'2014-Sage'
FROMStudent;
【题2-04】查询全体学生的学号、姓名、出生年份和所在系，要求用小写字母表示所有系名。
其命令为：
SELECTSno,Sname,2012-Sage'YearofBirth',LOWER（Sdept）Sdept
FROMStudent;
【题2-05】查询选修了课程的学生学号。
其命令为：
SELECTDISTINCTSno
FROMSC;
2条件查询
【题2-06】查询数学系（MA）全体学生的学号（Sno）和姓名（Sname）。
其命令为：
SELECTSno,Sname
FROMStudent
WHERESdept='MA';
【题2-07】查询考试成绩有不及格的学生的学号。
SELECTDISTINCTSno
FROMSC
WHEREGrade<60;
【题2-08】查询所有年龄在20岁以下的学生姓名（Sname）及年龄（Sage）。
其命令为：
SELECTSname,Sage
FROMStudent
WHERESage<20;
【题2-09】查询所有年龄在18~20岁（包括18岁和20岁）之间的学生姓名（Sname）及年龄（Sage）。
其命令为：
SELECTSname,Sage
FROMStudent
WHERESage=18ANDSage<=22;
或
SELECTSname,Sage
FROMStudent
WHERESageBETWEEN18AND22;
【题2-10】查询年龄不在18-20岁之间的学生姓名（Sname）及年龄（Sage）。
其命令为：
SELECTSname,Sage
FROMStudent
WHERESageNOTBETWEEN18AND20;
【例11】查询计算机系、数学系和信息系学生的学号（Sno）、姓名（Sname）和性别（Ssex）。
其命令为：
SELECTSno,Sname,SsexFROMStudent
WHERESdeptIN（'CS','MA','IS'）;
等价于：
SELECTSno,Sname,Ssex
FROMStudent
WHERESdept='CS'ORSdept='MA'ORSdept='IS';
【例12】查询既不是信息系（IS）、数学系（MA）、也不是计算机系（CS）的学生的姓名（Sname）和性别（Ssex）。
其命令为：
SELECTSname,Ssex
FROMStudent
WHERESdeptNOTIN（'IS','MA','CS'）;
【例13】查询所有姓刘的学生的姓名（Sname）、学号（Sno）和性别（Ssex）。
其命令为：
SELECTSname,Sno,Ssex
FROMStudent
WHERESnameLIKE'刘%';
【例14】查询姓“刘”且全名为4个汉字的学生的姓名（Sname）和所在系（Sdept）。
其命令为：
SELECTSname,SdeptFROMStudent
WHERESnameLIKE'刘____';
【例15】查询所有不姓刘的学生姓名（Sname）和年龄（Sage）。
SELECTSname,Sage
FROMStudent
WHERESnameNOTLIKE'刘%';
【例16】查询课程名为“DB_设计”的课程号（Cno）和学分（Ccredit）。
其命令为：
SELECTCno,Ccredit
FROMCourse
WHERECnameLIKE'DB\_设计'ESCAPE'\';
【例17】查询以"DB_"开头，且倒数第2个汉字字符为“设”的课程的详细情况。
其命令为：
SELECT*
FROMCourse
WHERECnameLIKE'DB\_%设__'ESCAPE'\';
【例18】假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。
试查询缺少成绩的学生的学号（Sno）和相应的课程号（Cno）。
其命令为：
SELECTSno,Cno
FROMSC
WHEREGradeISNULL;
【例19】查询所有有成绩的学生学号（Sno）和课程号（Cno）。
其命令为：
SELECTSno,CnoFROMSCWHEREGradeISNOTNULL;
【题20】查询计算机系年龄在20岁以下的学生姓名。
selectSname
fromstudent
whereSage<20andSdept='CS';
3查询结果排序
例24查询选修了3号课程的学生的学号（Sno）和成绩（Grade），并按成绩降序排列。
其命令为：
SELECTSno,Grade
FROMSC
WHERECno='3'
ORDERBYGradeDESC;
【例25】查询全体学生情况，查询结果按所在系的系名（Sdpet）升序排列，同一系中的学生按年龄（Sage）降序排列。
其命令为：
SELECT*
FROMStudent
ORDERBYSdept,SageDESC;
4集函数的使用
例26查询学生总人数。
其命令为：
SELECTCOUNT（*）
FROMStudent;
例27查询选修了课程的学生人数。
其命令为：
SELECTCOUNT（DISTINCTSno）
FROMSC;
例28计算选修2号课程的学生平均成绩。
其命令为：
SELECTAVG（Grade）
FROMSC
WHERECno='2';
例29查询选修2号课程的学生最高分数。
其命令为：
SELECTMAX（Grade）
FROMSC
WHERECno='2';
例30查询学生200215122选修课程的总学分数。
其命令为：
SELECTSUM（Ccredit）
FROMSC,Course
WHERESno='200215122'ANDSC.Cno=Course.Cno;
5查询结果分组
例31求各个课程号（Cno）及相应的选课人数。
其命令为：
SELECTCno,COUNT（Sno）CntSno
FROMSC
GROUPBYCno;
例32查询选修了3门或3门以上课程的学生学号（Sno）。
其命令为：
SELECTSno
FROMSC
GROUPBYSno
HAVINGCOUNT（Cno）>3
实验三连接、嵌套和集合查询
一、实验目的和要求
1．掌握SQLServer查询分析器的使用方法，加深对SQL查询语句的理解。
2．熟练掌握查询语句的一般格式。
3．熟练掌握连接、嵌套和集合查询的使用。
二、实验内容及步骤
（一）连接查询
1不同表之间的连接查询
【题3-01】查询每个学生及其选修课程的情况。
本查询实际上是涉及Student与SC两个表的连接操作。
这两个表之间的联系是通过公共属性Sno实现的，因此，其操作命令为：
SELECTStudent.*,SC.*
FROMStudent,SC
WHEREStudent.Sno=SC.Sno；
说明：
若在以上等值连接中把目标列中重复的属性列去掉则为自然连接，其命令为
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.Sno;
2自身连接
【例35】查`询每一门课的间接先修课（即先修课的先修课）。
在Course表关系中，只有每门课的直接先修课信息，而没有先修课的先修课。
要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。
这就需要要将Course表与其自身连接。
为方便连接运算，这里为Course表取两个别名分别为A，B。
则完成该查询的SQL语句为：
SELECTA.Cno,A.Cname,B.Cpno
FROMCourseA,CourseB
WHEREA.Cpno=B.Cno;
3外连接
【例36】把例33中的等值连接改为左连接。
该左连接操作在SQLServer2000中的命令格式为：
SELECTStudent.Sno,Sname,Ssex,Sdept,Cno,Grade
FROMStudent
LEFTJOINSCON
Student.Sno=SC.Sno;
说明：
以上左连接操作也可以用如下的右连接操作代替，其结果完全一样。
SELECTStudent.Sno,Sname,Ssex,Sdept,Cno,Grade
FROMSC
RIGHTJOINStudentON
SC.Sno=Student.Sno;
4复合条件连接
【例38】查询每个学生的学号（Sno）、姓名（Sname）、选修的课程名（Cname）及成绩（Grade）。
本查询涉及到三个表的连接操作，完成该查询的SQL语句如下：
SELECTStudent.Sno,Sname,Cname,Grade
FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;
（二）嵌套查询
1带谓词IN的嵌套查询
【例39】查询与“李伟”在同一个系学习的学生学号（Sno）、姓名（Sname）和系名（Sdept）。
该查询可构造嵌套查询实现，其SQL语句如下：
SELECTSno,Sname,Sdept
FROMStudent
WHERESdeptIN
（SELECTSdept
FROMStudent
WHERESname=’李伟’）;
说明：
本例中的查询也可以用自身连接来完成，其SQL语句如下：
SELECTA.Sno,A.Sname,A.Sdept
FROMStudentA,StudentB
WHEREA.Sdept=B.SdeptANDB.Sname=’李伟’;
【例40】查询选修了编号为“2”的课程的学生姓名（Sname）和所在系（Sdept）。
SELECTSname,Sdept
FROMStudent
WHERESnoIN
（SELECTSno
FROMSC
WHERECno='2'）;
【例41】查询选修了课程名为“数据结构”的学生学号（Sno）和姓名（Sname）。
本查询涉及学号、姓名和课程名（Cname）三个属性。
学号和姓名存放在Student表中，课程名的存放在Course表中，但Student与Course两个表之间没有公共属性，必须通过SC表建立它们之间的联系。
所以本查询实际上涉及三个关系的连接操作。
SELECTSno,Sname/*③最后在Studen关系中*/
FROMStudent/*取出Sno和Sname*/
WHERESnoIN
（SELECTSno/*②然后在SC关系中找出*/
FROMSC/*选修了3号课程的学生学号*/
WHERECnoIN
（SELECTCno/*①首先在Course关系中*/
FROMCourse/*找出“数据结构”的课程号*/
WHERECname=‘数据结构’））；/*结果为5号*/
说明：
本查询同样可以用连接查询实现：
SELECTS.Sno,Sname
FROMStudentS,SCR,CourseC
WHERES.Sno=R.SnoANDR.Cno=C.CnoANDC.C
升级会员 