《数据库系统原理》实验报告Word格式文档下载.docx
《《数据库系统原理》实验报告Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《《数据库系统原理》实验报告Word格式文档下载.docx（64页珍藏版）》请在冰点文库上搜索。
学生表：
Student
Sno（学号）
Sname（姓名）
Ssex（性别）
Sage（年龄）
Sdept（所在系）
200215121
李勇
男
20
CS（计算机科学系）
200215122
刘晨
女
19
200215123
王敏
18
MA（数学系）
200215125
张立
IS（信息系）
课程表：
Course
Cno（课程号）
Cname（课程名）
Cpno（先修课）
Ccredit（学分）
1
数据库
5
4
2
数学
3
信息系统
操作系统
6
数据结构
7
数据处理
PASCAL
学生选课表：
SC
Grade（成绩）
92
85
88
90
80
实验一SQL数据定义
一、实验目的和要求
1．掌握利用SQL查询分析器和企业管理器进行数据库及基本表的定义、删除与修改；
2．掌握索引的建立与删除的方法。
二、实验内容与步骤
（一）建立数据库
通过企业管理器或查询分析器建立学生-课程数据库xskc。
createdatabasexskc
on
（name=xskc_data,
filename='
d:
\sjksy\xskc_data.mdf'
）
logon
（name=xskc_log,
\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
【题2-03】查询全体学生的学号（Sno）、姓名（Sname）及出生年份。
由于SELECT子句的<
目标列表达式>
不仅可以是表中的属性列，也可以是表达式，故可以查询经过计算的值。
其命令为：
SELECTSno,Sname,2014-Sageas'
2014-Sage'
【题2-04】查询全体学生的学号、姓名、出生年份和所在系，要求用小写字母表示所有系名。
SELECTSno,Sname,2012-Sage'
YearofBirth'
LOWER（Sdept）Sdept
【题2-05】查询选修了课程的学生学号。
SELECTDISTINCTSno
FROMSC;
2条件查询
【题2-06】查询数学系（MA）全体学生的学号（Sno）和姓名（Sname）。
SELECTSno,Sname
FROMStudent
WHERESdept='
MA'
【题2-07】查询考试成绩有不及格的学生的学号。
FROMSC
WHEREGrade<
60;
【题2-08】查询所有年龄在20岁以下的学生姓名（Sname）及年龄（Sage）。
SELECTSname,Sage
WHERESage<
20;
【题2-09】查询所有年龄在18~20岁（包括18岁和20岁）之间的学生姓名（Sname）及年龄（Sage）。
WHERESage=18ANDSage<
=22;
或
WHERESageBETWEEN18AND22;
【题2-10】查询年龄不在18-20岁之间的学生姓名（Sname）及年龄（Sage）。
WHERESageNOTBETWEEN18AND20;
【例11】查询计算机系、数学系和信息系学生的学号（Sno）、姓名（Sname）和性别（Ssex）。
SELECTSno,Sname,SsexFROMStudent
WHERESdeptIN（'
CS'
'
IS'
）;
等价于：
SELECTSno,Sname,Ssex
ORSdept='
【例12】查询既不是信息系（IS）、数学系（MA）、也不是计算机系（CS）的学生的姓名（Sname）和性别（Ssex）。
SELECTSname,Ssex
WHERESdeptNOTIN（'
【例13】查询所有姓刘的学生的姓名（Sname）、学号（Sno）和性别（Ssex）。
SELECTSname,Sno,Ssex
WHERESnameLIKE'
刘%'
【例14】查询姓“刘”且全名为4个汉字的学生的姓名（Sname）和所在系（Sdept）。
SELECTSname,SdeptFROMStudent
刘____'
【例15】查询所有不姓刘的学生姓名（Sname）和年龄（Sage）。
WHERESnameNOTLIKE'
【例16】查询课程名为“DB_设计”的课程号（Cno）和学分（Ccredit）。
SELECTCno,Ccredit
FROMCourse
WHERECnameLIKE'
DB\_设计'
ESCAPE'
\'
【例17】查询以"
DB_"
开头，且倒数第2个汉字字符为“设”的课程的详细情况。
SELECT*
DB\_%设__'
ESCAPE'
【例18】假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。
试查询缺少成绩的学生的学号（Sno）和相应的课程号（Cno）。
SELECTSno,Cno
WHEREGradeISNULL;
【例19】查询所有有成绩的学生学号（Sno）和课程号（Cno）。
SELECTSno,CnoFROMSCWHEREGradeISNOTNULL;
【题20】查询计算机系年龄在20岁以下的学生姓名。
selectSname
fromstudent
whereSage<
20andSdept='
3查询结果排序
例24查询选修了3号课程的学生的学号（Sno）和成绩（Grade），并按成绩降序排列。
SELECTSno,Grade
WHERECno='
3'
ORDERBYGradeDESC;
【例25】查询全体学生情况，查询结果按所在系的系名（Sdpet）升序排列，同一系中的学生按年龄（Sage）降序排列。
ORDERBYSdept,SageDESC;
4集函数的使用
例26查询学生总人数。
SELECTCOUNT（*）
例27查询选修了课程的学生人数。
SELECTCOUNT（DISTINCTSno）
例28计算选修2号课程的学生平均成绩。
SELECTAVG（Grade）
2'
例29查询选修2号课程的学生最高分数。
SELECTMAX（Grade）
例30查询学生200215122选修课程的总学分数。
SELECTSUM（Ccredit）
FROMSC,Course
WHERESno='
200215122'
ANDSC.Cno=Course.Cno;
5查询结果分组
例31求各个课程号（Cno）及相应的选课人数。
SELECTCno,COUNT（Sno）CntSno
GROUPBYCno;
例32查询选修了3门或3门以上课程的学生学号（Sno）。
SELECTSno
GROUPBYSno
HAVINGCOUNT（Cno）>
实验三连接、嵌套和集合查询
3．熟练掌握连接、嵌套和集合查询的使用。
（一）连接查询
1不同表之间的连接查询
【题3-01】查询每个学生及其选修课程的情况。
本查询实际上是涉及Student与SC两个表的连接操作。
这两个表之间的联系是通过公共属性Sno实现的，因此，其操作命令为：
SELECTStudent.*,SC.*
FROMStudent,SC
WHEREStudent.Sno=SC.Sno；
若在以上等值连接中把目标列中重复的属性列去掉则为自然连接，其命令为
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
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
LEFTJOINSCON
Student.Sno=SC.Sno;
以上左连接操作也可以用如下的右连接操作代替，其结果完全一样。
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
WHERESdeptIN
（SELECTSdept
FROMStudent
WHERESname=’李伟’）;
本例中的查询也可以用自身连接来完成，其SQL语句如下：
SELECTA.Sno,A.Sname,A.Sdept
FROMStudentA,StudentB
WHEREA.Sdept=B.SdeptANDB.Sname=’李伟’;
【例40】查询选修了编号为“2”的课程的学生姓名（Sname）和所在系（Sdept）。
SELECTSname,Sdept
WHERESnoIN
（SELECTSno
FROMSC
WHERECno='
【例41】查询选修了课程名为“数据结构”的学生学号（Sno）和姓名（Sname）。
本查询涉及学号、姓名和课程名（Cname）三个属性。
学号和姓名存放在Student表中，课程名的存放在Course表中，但Student与Course两个表之间没有公共属性，必须通过SC表建立它们之间的联系。
所以本查询实际上涉及三个关系的连接操作。
SELECTSno,Sname/*③最后在Studen关系中*/
FROMStudent/*取出Sno和Sname*/
（SELECTSno/*②然后在SC关系中找出*/
FROMSC/*选修了3号课程的学生学号*/
WHERECnoIN
（SELECTCno/*①首先在Course关系中*/
FROMCourse/*找出“数据结构”的课程号*/
WHERECname=‘数据结构’））；
/*结果为5号*/
本查询同样可以用连接查询实现：
SELECTS.Sno,Sname
FROMStudentS,SCR,CourseC
WHERES.Sno=R.SnoANDR.Cno=C.CnoANDC.C
升级会员 