数据库实验报告模版.docx
- 文档编号:27368556
- 上传时间:2023-06-29
- 格式:DOCX
- 页数:34
- 大小:244.83KB
数据库实验报告模版.docx
《数据库实验报告模版.docx》由会员分享,可在线阅读,更多相关《数据库实验报告模版.docx(34页珍藏版)》请在冰豆网上搜索。
数据库实验报告模版
《数据库系统原理》课程实验
姓名:
班级:
学号:
佛山科学技术学院计算机系
2012年12月
目录
一.实验需用表格3
二.实验内容5
2.1SQL数据定义5
2.2实验二SQL数据查询7
2.3实验三连接、嵌套和集合查询11
2.4实验四SQL的数据更新16
2.5实验五视图的定义和维护18
2.6实验六触发器和存储过程23
三.实验感想27
一.实验需用表格
学生-课程数据库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
二.实验内容
2.1SQL数据定义
一、实验目的和要求
1.掌握利用SQL查询分析器和企业管理器进行数据库及基本表的定义、删除与修改;
2.掌握索引的建立与删除的方法。
二、实验内容与步骤
(一)建立数据库
通过企业管理器或查询分析器建立学生-课程数据库xskc。
createdatabasexskc
on
(name=xskc_data,
filename='e:
\sjksy\xskc_data.mdf')
logon
(name=xskc_log,
filename='e:
\sjksy\xskc_log.ldf')
注:
先在E:
盘上建立一个文件夹(例如:
E:
\sjksy),数据库文件保存到自建的文件夹中。
(二)基本表的定义、修改与删除
1.定义基本表
利用查询分析器或企业管理器创建基本表,并输入数据。
【题1-01】建立一个学生表Student,它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。
要求“学号”为主键,“姓名”不能为空,“性别”默认值为“男”。
CREATETABLEStudent
(SnoCHAR(9)PRIMARYKEY,
SnameCHAR(20)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
);
【题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】增加课程名称必须取唯一值的约束条件。
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;
2.2实验二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,2012-Sageas‘2012-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,SdeptFROMStudents
WHERESnameLIKE'刘____';
【例15】查询所有不姓刘的学生姓名(Sname)和年龄(Sage)。
SELECTSname,Sage
FROMStudents
WHERESnameNOTLIKE'刘%';
【例16】查询课程名为“DB_设计”的课程号(Cno)和学分(Ccredit)。
其命令为:
SELECTCno,Ccredit
FROMCourse
WHERECnameLIKE'DB\_设计'ESCAPE'\';
【例17】查询以"DB_"开头,且倒数第2个汉字字符为“设”的课程的详细情况。
其命令为:
SELECT*
FROMCourse
WHERECnameLIKE'DB\_%设__'ESCAPE'\';
【例18】假设某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。
试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。
其命令为:
SELECTSno,Cno
FROMReports
WHEREGradeISNULL;
【例19】查询所有有成绩的学生学号(Sno)和课程号(Cno)。
其命令为:
SELECTSno,CnoFROMSCWHEREGradeISNOTNULL;
【题20】查询计算机系年龄在20岁以下的学生姓名。
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
FROMReports
GROUPBYSno
HAVINGCOUNT(Cno)>3
2.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复合条件连接
【例37】
【例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
FROMStudents
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.Cname='数据结构';
2带有比较运算符的嵌套查询
【例42】将例39改为带有比较运算符的嵌套查询。
由于一个学生只可能在一个系学习,因此子查询的结果是一个值,因此可以用=代替IN,其SQL语句如下:
SELECTSno,Sname,Sdept
FROMStudent
WHERESdept=
(SELECTSdept
FROMStudent
WHERESname=’李伟’);
3带谓词ANY或ALL的嵌套查询
【例43】查询计算机系(CS)的不超过自动化系所有学生的年龄的学生姓名(Sname)和年龄(Sage)。
其查询命令为
SELECTSname,Sage
FROMStudent
WHERESdept<>'CS'
ANDSage<=ALL(SELECTSage
FROMStudent
WHERESdept='CS');
说明:
本查询也可以用集函数来实现。
其SQL语句如下:
SELECTSname,Sage
FROMStudent
WHERESdept<>'CS'
ANDSage<=(SELECTMIN(Sage)
FROMStudent
WHERESdept='CS');
4带谓词EXISTS的嵌套查询
【例44】查询所有选修了编号为“1”课程的学生姓名(Sname)和所在系(Sdept)。
本查询的SQL语句是:
SELECTSname,Sdept
FROMStudent
WHEREEXISTS
(SELECT*
FROMSC
WHERESno=Student.SnoANDCno='1');
【例45】将例39改为带谓词EXISTS的查询,其SQL语句如下
SELECTSno,Sname,Sdept
FROMStudentA
WHEREEXISTS
(SELECT*
FROMStudentsB
WHEREB.Sdept=A.SdeptANDB.Sname=’李伟’);
【例46】查询选修了所有课程的学生姓名(Sname)和所在系。
由于没有全称量词,可将题目的意思转换成等价的用存在量词的形式:
查询这样的学生,没有一门课程是他不选修的。
其SQL语句为:
SELECTSname,Sdept
FROMStudent
WHERENOTEXISTS
(SELECT*
FROMCourse
WHERENOTEXISTS
(SELECT*FROMSCWHERESno=Student.Sno
A
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 实验 报告 模版