数据库系统原理实验指导书110630.docx
《数据库系统原理实验指导书110630.docx》由会员分享，可在线阅读，更多相关《数据库系统原理实验指导书110630.docx（24页珍藏版）》请在冰豆网上搜索。
数据库系统原理实验指导书110630
数据库系统原理实验指导书
邢振祥
电子与信息工程系计算机应用教研室
2010-11-18
《数据库系统原理》为计算机科学与技术、软件工程和网络工程专业核心课程，是一门实用性较强的课程，也是计算机学科的基础课程之一。
本课程的教学目的是使学生了解和熟悉数据库系统的实现原理和应用开发基础。
要求学生了解数据库特别是关系数据库的基本概念，发展历史，和发展动向；理解关系数据库的查询优化，规范化理论，并发控制，安全性完整性控制等相关理论；掌握关系代数、sql语言使用，数据库设计，开发，运行和维护等相关技术。
本课程总学时为48学时，其中理论课程学时为40学时，实验学时为8学时，实验内容安排如下：
1．创建数据库和表（2学时）
2．查询数据库（2学时）
3．创建和使用视图、索引（2学时）
4．实现数据完整性和安全性（2学时）
实验一基本表的定义、删除与修改（2课时，验证）
一、实验目的
熟练掌握基本表的定义、删除与修改，为后继学习作准备。
二、实验要求
1．了解并掌握SQL查询分析器及企业管理器的使用;
2．掌握基本表的定义、删除与修改。
三、实验环境
1．windows操作系统；
2．Sqlserver数据库管理系统软件。
四、实验内容
数据表的定义、删除及修改。
五、实验步骤
1．启动SQL查询分析器；
2．选择SQL SERVER后，按确认；
3．选择数据库；
4．验证如下例题：
表1.1关系Students
Sno
Sname
Ssex
Sage
Sdept
S01
S02
S03
S04
S05
S06
王建平
刘华
范林军
李伟
黄河
长江
男
女
女
男
男
男
21
19
18
19
18
20
自动化
自动化
计算机
数学
数学
数学
表1.2关系Courses
Cno
Cname
Pre_Cno
Credits
C01
C02
C03
C04
C05
C06
C07
英语
数据结构
数据库
DB_设计
C++
网络原理
操作系统
C05
C02
C03
C07
C05
4
2
2
3
3
3
3
表1.3关系Reports
Sno
Cno
Grade
S01
S01
S02
S02
S02
S03
S03
S04
C01
C03
C01
C02
C03
C01
C02
C03
92
84
90
94
82
72
90
75
1定义基本表
例1.1建立表1.1所示的学生表Students，每个属性名的意义为Sno-学号、Sname-姓名、Ssex-性别、Sage-年龄、Sdept-所在系。
这里要求Sno和Sname不能为空值，且取值唯一。
CREATETABLEStudents/*列级完整性约束条件*/
（SnoCHAR（5）NOTNULL,/*Sno不能为空值*/
SnameCHAR（20）NOTNULL,/*Sname不能为空值*/
SsexCHAR
（2）,
SageINT,
SdeptCHAR（15）,
CONSTRAINTun_SnoUNIQUE（Sno）,/*Sno取值唯一的约束*/
CONSTRAINTun_SnameUNIQUE（Sname））;/*Sname取值唯一的约束*/
说明：
在MicrosoftSQLServer2000的查询分析器（QueryAnalyzer）中使用单条SQL语句，其末尾不需要分号“；”作为命令结尾标记。
通常，SQLServer2000对大多数末尾带有分号的SQL命令都能顺利执行，但对少数的SQL命令，末尾若带分号，则SQLServer2000会给出错误信息提示。
比如，若在例1.59的SQL命令末尾加上一个分号“；”，SQLServer2000就会出现“Incorrectsyntaxnear';'”的提示，虽然SQLServer2000实际上已经执行了该命令。
例1.1-1建立表1.2所示的课程表Courses，其属性名意义分别为Cno-课程号,Cname-课程名,Pre_Cno-先修课程号,Credits-学分。
CREATETABLECourses
（CnoCHAR（5）NOTNULL,/*Cno不能为空值*/
CnameCHAR（20）NOTNULL,/*Cname不能为空值*/
Pre_CnoCHAR（5）,
CreditsINT,
CONSTRAINTun_CnoUNIQUE（Cno））;/*Cno取值唯一的约束*/
例1.1-2建立表1.3所示的成绩表Reports。
其中的属性名意义分别为Sno-学号，Cno-课程号和Grade-考试成绩。
CREATETABLEReports
（SnoCHAR（5）NOTNULL,/*Sno不能为空值*/
CnoCHAR（5）NOTNULL,/*Cno不能为空值*/
GradeINT,
CONSTRAINTSno_CnoUNIQUE（Sno,Cno））;/*Sno+Cno取值唯一的约束*/
2修改基本表
例1.2向基本表Students中增加“入学时间”属性列，其属性名为Sentrancedate，数据类型为DATETIME型。
ALTERTABLEStudentsADDSentrancedateDATETIME;例1.3将Sage（年龄）的数据类型改为SMALLINT型。
ALTERTABLEStudentsALTERCOLUMNSageSMALLINT;
例1.4删除Sname（姓名）必须取唯一值的约束。
ALTERTABLEStudentsDROPCONSTRAINTun_Sname;
注意：
SQLServer2000增加了删除属性的命令。
比如，删除属性列Sentrancedate的命令为：
ALTERTABLEStudentsDROPCOLUMNSentrancedate;
说明：
（1）为了保证后面例子能够顺利运行，请大家一定将属性列Sentrancedate从Students表中删除。
（2）为了调试SQL语句方便，这里没有在表Reports中增加参照完整性约束，甚至没有定义主键。
等本章学完后，第7章的实验系统就是把这些约束全部加上了。
3删除基本表
例1.5删除Students表。
DROPTABLEStudents;
说明：
此表删除后，请立即用例1.1将其建立起来，以便后面的例子使用。
4向表中添加元组
例1.6将一个学生元组（S01，王建平，男，21，计算机）添加到基本表Students中。
INSERT
INTOStudents
VALUES（'S01','王建平','男',21,'自动化'）;
说明：
（1）请读者用这个命令将其余5个学生的元组也添加到基本表Students中。
（2）向Courses表插入元组（‘C01’,’英语’,’’,4）的命令为：
INSERT
INTOCourses
VALUES（'C01','英语','',4）;
请大家将其余6门课程的信息插入Courses表中。
例1.7将学习成绩的元组（‘S01’,’C01’）添加到基本表Reports中。
INSERT
INTOReports（Sno,Cno）
VALUES（‘S01’,’C01’）;
说明：
请大家用这个命令将其余7个选课元组也添加到基本表Reports中.
实验二建立与删除索引（2课时，验证）
一、实验目的
熟练掌握索引的建立与删除的方法。
二、实验要求
1．掌握建立索引的二种方法，即在基本表中建立和用命令方式建立；
2．掌握删除索引的方法。
三、实验环境
1．windows操作系统；
2．Sqlserver数据库管理系统软件。
四、实验内容
索引的建立与删除。
五、实验步骤
1．启动SQL查询分析器；
2．选择SQL SERVER后，按确认；
3．选择数据库；
4．验证如下例题：
1建立索引
例1.8为学生选课数据库中的Students，Courses，Reports三个表建立索引。
其中Students表按Sno（学号）升序建唯一索引，Courses表按Cno（课程号）升序建唯一索引，Reports表按Sno（学号）升序和Cno（课程号）号降序建唯一索引。
其语句为：
CREATEUNIQUEINDEXStu_SnoONStudents（Sno）;
CREATEUNIQUEINDEXCou_CnoONCourses（Cno）;
CREATEUNIQUEINDEXRep_ScnoONReports（SnoASC,CnoDESC）;
例1.9在基本表Students的Sname（姓名）和Sno（学号）列上建立一个聚簇索引，而且Students中的物理记录将按照Sname值和Sno值的升序存放。
其语句为：
CREATECLUSTEREDINDEXStu_Sname_SnoONStudents（Sname,Sno）;
2删除索引
例1.10删除基本表Reports上的Rep_SCno索引。
DROPINDEXReports.Rep_Scno;
实验三sql数据查询（2课时，综合）
一、实验目的
熟练掌握查询语句的使用。
二、实验要求
1．掌握查询语句的一般格式;
2．掌握无条件、有条件查询及查询结果排序与分组。
三、实验环境
1．windows操作系统；
2．Sqlserver数据库管理系统软件。
四、实验内容
数据的各种查询方法。
五、实验步骤
1．启动SQL查询分析器；
2．选择SQL SERVER后，按确认；
3．选择数据库；
4．综合练习如下例题：
1无条件查询
例1.11查询全体学生的详细记录。
这是一个无条件的选择查询，其命令为：
SELECT*/*这里的“*”等价于ALL*/
FROMStudents;
其结果为表1.3中的全部数据。
例1.12查询全体学生的姓名（Sname）、学号（Sno）、所在系（Sdept）。
这是一个无条件的投影查询，其命令为：
SELECTSname,Sno,Sdept
FROMStudents;
例1.13查询全体学生的姓名（Sname）、出生年份及学号（Sno）。
由于SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式，故可以查询经过计算的值。
其命令为：
SELECTSno,Sname,2001-Sage
FROMStudents;
例1.14查询全体学生的姓名、出生年份和学号，要求用小写字母表示学号中的字母。
其命令为：
SELECTSname,’Birth:
’Title,1996-SageBirthYear,LOWER（Sno）Lsno
FROMStudents;
例1.15查询选修了课程的学生学号。
其命令为：
SELECTDISTINCTSno
FROMReports;
2条件查询
例1.16查询数学系全体学生的学号（Sno）和姓名（Sname）。
其命令为：
SELECTSno,Sname
FROMStudents
WHERESdept='数学';
例1.17查询所有年龄在18~22岁（包括18岁和22岁）之间的学生姓名（Sname）及年龄（Sage）。
其命令为：
SELECTSname,Sage
FROMStudents
WHERESage=18ANDSage<=22;
例1.18查询年龄在18~22岁（包括18岁和22岁）之间的学生姓名（Sname）及年龄（Sage）。
其命令为：
SELECTSname,Sage
FROMStudents
WHERESageBETWEEN18AND22;
例1.19查询年龄不在18-22岁之间的学生姓名（Sname）及年龄（Sage）。
其命令为：
SELECTSname,Sage
FROMStudents
WHERESageNOTBETWEEN18AND22;
例1.20查询自动化系、数学和计算机系学生的学号（Sno）、姓名（Sname）和性别（Ssex）。
其命令为：
SELECTSno,Sname,SsexFROMStudents
WHERESdeptIN（'自动化','数学','计算机'）;
等价于：
SELECTSname,Ssex
FROMStudents
WHERESdept='自动化'ORSdept='数学'ORSdept='计算机';
例1.21查询既不是信息系、数学系、也不是计算机系的学生的姓名（Sname）和性别（Ssex）。
其命令为：
SELECTSname,Ssex
FROMStudents
WHERESdeptNOTIN（'自动化','数学','计算机'）;
例1.22查询所有姓刘的学生的姓名（Sname）、学号（Sno）和性别（Ssex）。
其命令为：
SELECTSname,Sno,Ssex
FROMStudents
WHERESnameLIKE'刘%';
例1.23查询姓“刘”且全名为4个汉字的学生的姓名（Sname）和所在系（Sdept）。
其命令为：
SELECTSname,SdeptFROMStudentsWHERESnameLIKE'刘____';例1.24查询所有不姓刘的学生姓名（Sname）和年龄（Sage）。
SELECTSname,Sage
FROMStudents
WHERESnameNOTLIKE'刘%';
例1.25查询课程名为“DB_设计”的课程号（Cno）和学分（Credits）。
其命令为：
SELECTCno,Credits
FROMCourses
WHERECnameLIKE'DB\_设计'ESCAPE'\';
例1.26查询以"DB_"开头，且倒数第2个汉字字符为“设”的课程的详细情况。
其命令为：
SELECT*
FROMCourses
WHERECnameLIKE'DB\_%设__'ESCAPE'\';
例1.27假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。
试查询缺少成绩的学生的学号（Sno）和相应的课程号（Cno）。
其命令为：
SELECTSno,Cno
FROMReports
WHEREGradeISNULL;
例1.28查询所有有成绩的学生学号（Sno）和课程号（Cno）。
其命令为：
SELECTSno,CnoFROMReportsWHEREGradeISNOTNULL;
3查询结果排序
例1.29查询选修了C03号课程的学生的学号（Sno）和成绩（Grade），并按成绩降序排列。
其命令为：
SELECTSno,Grade
FROMReports
WHERECno='C03'
ORDERBYGradeDESC;
例1.30查询全体学生情况，查询结果按所在系的系名（Sdpet）升序排列，同一系中的学生按年龄（Sage）降序排列。
其命令为：
SELECT*
FROMStudents
ORDERBYSdept,SageDESC;
4集函数的使用
例1.31查询学生总人数。
其命令为：
SELECTCOUNT（*）
FROMStudents;
例1.32查询选修了课程的学生人数。
其命令为：
SELECTCOUNT（DISTINCTSno）
FROMReports;
例1.33计算选修C01号课程的学生平均成绩。
其命令为：
SELECTAVG（Grade）
FROMReports
WHERECno='C01';
例1.34查询选修C01号课程的学生最高分数。
其命令为：
SELECTMAX（Grade）
FROMReports
WHERECno='C01';
5查询结果分组
例1.35求各个课程号（Cno）及相应的选课人数。
其命令为：
SELECTCno,COUNT（Sno）CntSno
FROMReports
GROUPBYCno;
例1.36查询选修了3门或3门以上课程的学生学号（Sno）。
其命令为：
SELECTSno
FROMReports
GROUPBYSno
HAVINGCOUNT（Cno）>
实验四连接、嵌套和集合查询（2课时，综合）
一、实验目的
熟练掌握连接、嵌套和集合查询的使用。
二、实验要求
1．掌握连接、嵌套和集合查询语句的一般格式；
2．掌握连接、嵌套和集合查询的各种使用方法。
三、实验环境
1．windows操作系统；
2．Sqlserver数据库管理系统软件。
四、实验内容
各种连接、嵌套和集合查询方法。
五、实验步骤
1．启动SQL查询分析器；
2．选择SQL SERVER后，按确认；
3．选择数据库；
4．综合练习如下例题：
1．连接查询
（1）不同表之间的连接查询
例1.37查询每个学生及其选修课程的情况。
本查询实际上是涉及Students与Reports两个表的连接操作。
这两个表之间的联系是通过公共属性Sno实现的，因此，其操作命令为：
SELECTStudents.*,Reports.*
FROMStudents,Reports
WHEREStudents.Sno=Reports.Sno；
说明：
若在以上等值连接中把目标列中重复的属性列去掉则为自然连接，其命令为
SELECTStudents.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudents,Reports
WHEREStudents.Sno=Reports.Sno;
例1.38查询每个学生的学号（Sno）、姓名（Sname）、选修的课程名（Cname）及成绩（Grade）。
本查询涉及到三个表的连接操作，完成该查询的SQL语句如下：
SELECTStudents.Sno,Sname,Cname,Grade
FROMStudents,Reports,Courses
WHEREStudents.Sno=Reports.SnoANDReports.Cno=Courses.Cno;
（2）自身连接
例1.39查`询每一门课的间接先修课（即先修课的先修课）。
在Courses表关系中，只有每门课的直接先修课信息，而没有先修课的先修课。
要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。
这就需要要将Courses表与其自身连接。
为方便连接运算，这里为Courses表取两个别名分别为A，B。
则完成该查询的SQL语句为：
SELECTA.Cno,A.Cname,B.Pre_Cno
FROMCoursesA,CoursesB
WHEREA.Pre_Cno=B.Cno;
（3）外连接
例1.40把例1.37中的等值连接改为左连接。
该左连接操作在SQLServer2000中的命令格式为：
SELECTStudents.Sno,Sname,Ssex,Sdept,Cno,Grade
FROMStudents
LEFTJOINReportsON
Students.Sno=Reports.Sno;
说明：
以上左连接操作也可以用如下的右连接操作代替，其结果完全一样。
SELECTStudents.Sno,Sname,Ssex,Sdept,Cno,Grade
FROMReports
RIGHTJOINStudentsON
Reports.Sno=Students.Sno;
2．嵌套查询
（1）带谓词IN的嵌套查询
例1.41查询选修了编号为“C02”的课程的学生姓名（Sname）和所在系（Sdept）。
SELECTSname,Sdept
FROMStudents
WHERESnoIN
（SELECTSno
FROMReports
WHERECno='C02'）;
例1.42查询与“李伟”在同一个系学习的学生学号（Sno）、姓名（Sname）和系名（Sdept）。
该查询可构造嵌套查询实现，其SQL语句如下：
SELECTSno,Sname,Sdept
FROMStudents
WHERESdeptIN
（SELECTSdept
FROMStudents
WHERESname=’李伟’）;
说明：
本例中的查询也可以用自身连接来完成，其SQL语句如下：
SELECTA.Sno,A.Sname,A.Sdept
FROMStudentsA,StudentsB
WHEREA.Sdept=B.SdeptANDB.Sname=’李伟’;
例1.43查询选修了课程名为“数据结构”的学生学号（Sno）和姓名（Sname）。
本查询涉及学号、姓名和课程名（Cname）三个属性。
学号和姓名存放在Students表中，课程名的存放在Courses表中，但Students与Courses两个表之间没有公共属性，必须通过Reports表建立它们之间的联系。
所以本查询实际上涉及三个关系的连接操作。
SELECTSno,Sname/*③最后在Studenst关系中*/
FROMStudents/*取出Sno和Sname*/
WHERESnoIN
（SELECTSno/*②然后在SC关系中找出*/
FROMReports/*选修了3号课程的学生学号*/
WHERECnoIN
（SELECTCno/*①首先在Courses关系中*/
FROMCourses/*找出“数据结构”的课程号，*/
WHERECname=‘数据结构’））；/*结果为C02号*/
说明：
本查询同样可以用连接查询实现：
SELECTS.Sno,Sname
FROMStudentsS,ReportsR,CoursesC
WHERES.Sno=R.SnoANDR.Cno=C.CnoANDC.Cname='数据结构';
（2）带有比较运算符的嵌套查询
例1.44将例1.42改为带有比较运算符的嵌套查询。
由于一个学生只可能在一个系学习，因此子查询的结果是一个值，因此可以用=代替IN，其SQL语句如下：
SELECTSno,Sname,Sdept
FROMStudents
WHERESdept=
（SELECTSdept
FROMStudents
WHERESname=’李伟’）;
（3）带
升级会员 