数据库系统原理实验指导书1123.docx
《数据库系统原理实验指导书1123.docx》由会员分享,可在线阅读,更多相关《数据库系统原理实验指导书1123.docx(28页珍藏版)》请在冰豆网上搜索。
数据库系统原理实验指导书1123
数据库系统原理实验指导书
第一次实验做:
实验1~2
第二次实验做:
实验3~4
第三次实验做:
实验5~7
2010-11-18
实验一基本表的定义、删除与修改(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)带谓词ANY或ALL的嵌套查询
例1.45查询非自动化系的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。
其查询命令为
SELECTSname,Sage
FROMStudents
WHERESdept<>'自动化'
ANDSage<=ALL(SELECTSage
FROMStudents
WHERESdept='自动化');
说明:
本查询也可以用集函数来实现。
其SQL语句如下:
SELECTSname,Sage
FROMStudents
WHERESdept<>'自动化'
ANDSage<=(SELECTMIN(Sa