太原理工数据库实验报告.docx
《太原理工数据库实验报告.docx》由会员分享，可在线阅读，更多相关《太原理工数据库实验报告.docx（30页珍藏版）》请在冰豆网上搜索。
太原理工数据库实验报告
本科实验报告
课程名称：
数据库系统原理B
实验项目：
交互式SQL、数据完整性
实验地点：
专业班级：
学号：
学生姓名：
指导教师：
2015年6月24日
实验二：
交互式SQL语句的使用
一、实验目的
（1）掌握数据库对象的操作过程,包括创建、修改、删除。
（2）熟悉表的各种操作，包括插入、修改、删除、查询
（3）熟练掌握常用SQL语句的基本语法
二、实验内容和要求
选择如下一个应用背景之一：
学生选课系统
其它你熟悉的应用
（1）建立一个数据库和相关的表、索引、视图等数据库对象，练习对表、索引和视图的各种操作。
（2）要求认真进行实验，记录各实验用例及执行结果。
（3）深入了解各个操作的功能。
实验要求包括如下方面的内容：
数据定义
1．基本表的创建、修改及删除
2．索引的创建
3．视图的创建
数据操作
完成各类更新操作包括：
1．插入数据
2．修改数据
3.删除数据
数据查询操作
完成各类查询操作
1．单表查询
2．分组统计
3.连接查询
4.嵌套查询
5.集合查询
数据操作
1．创建视图
2．视图查询
三、主要仪器设备
操作系统：
Windows7Windows2003Server数据库管理系统：
SQLServer。
四、操作方法与实验步骤
1.创建，修改及删除基本表
（1）创建Student表
CREATETABLEStudent
（SnoCHAR（8）PRIMARYKEY,
SnameCHAR（8）,
SsexCHAR
（2）NOTNULL,
SageINT,
SdeptCHAR（20）
）;
（2）创建Course表
CREATETABLECourse
（CnoCHAR（4）PRIMARYKEY,
CnameCHAR（40）NOTNULL,
CpnoCHAR（4）,
CcreditSMALLINT,
）;
（3）创建SC表
CreatetableSC
（Snochar（8）,
Cnochar（4）,
Gradesmallint,
Primarykey（Sno,Cno）,
Foreignkey（Sno）referencesStudent（Sno）,
Foreignkey（Cno）referencesCourse（Cno）
）;
（4）创建员工表Employee
CREATETABLEEmployee
（
编号CHAR（8）PRIMARYKEY,
姓名VARCHAR（8）notnull
部门CHR（40）,
工资numeric（8,2）,
生日datetime，
职称char（20）,
）;
指出该语句中的错误并改正后执行。
（5）检查表是否创建成
SELECT*FROMStudent
SELECT*FROMCourse
SELECT*FROMSC
SELECT*FROMEmployee
（6）修改表结构及约束
增加班级列
ALTERTABLEStudentADDSclasschar（4）
修改年龄列
ALTERTABLEStudentALTERCOLUMNSagesmallint
增加约束
ALTERTABLECourseADDUNIQUE（Cname）
（7）删除表
DROPTABLEEmployee
2．创建索引
（1）为Course表按课程名称创建索引
CREATEINDEXiCnameOnCourse（Cname）
（2）为Student表按学生姓名创建唯一索引
CREATEUNIQUEINDEXiSnameONStudent（Sname）
（3）为SC表按学号和课程号创建聚集索引
CREATECLUSTEREDINDEXiSnoCnoOnSC（Sno,Cnodesc）
（4）为Course表按课程号创建唯一索引
CREATEUNIQUEINDEXiCnoONCourse（Cno）
3．创建视图
建立信息系学生的视图：
CREATEVIEWIS_Student
AS
SELECTSno,Sname,SageFROMStudent
WHERESdept='IS'
数据操作
1.插入数据
（1）插入到Student表
INSERTINTOStudentVALUES（'','李勇','男',20,'CS','1001'）
INSERTINTOStudentVALUES（'','刘晨','女',19,'CS','1001'）
INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,Sclass）VALUES（'','王敏','女',18,'MA','1002'）
INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,Sclass）VALUES（'','张立','男',19,'IS','1003'）
INSERTINTOStudent（Sno,Sname,Ssex,Sclass）VALUES（'','刘洋','女','1001'）
INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,sclass）VALUES（'',’赵斌’,'男','19','IS','1005'）
INSERTINTOStudentVALUES（'',19,'张明明','男','CS','1002'）
（2）插入到Course表
insertintoCourse（Cno,Cname,Cpno,Ccredit）values（'1','数据库系统原理','5',4）
insertintoCourse（Cno,Cname,Cpno,Ccredit）values（'2','高等数学',null,2）
insertintoCourse（Cno,Cname,Cpno,Ccredit）values（'3','管理信息系统','1','4'）
insertintoCourse（Cno,Cname,Cpno,Ccredit）values（'4','操作系统原理','6','3'）
insertintoCourse（Cno,Cname,Cpno,Ccredit）values（'5','数据结构','7','4'）
insertintoCourse（Cno,Cname,Cpno,Ccredit）values（'6','数据处理',null,'2'）
insertintoCourse（Cno,Cname,Cpno,Ccredit）values（'7','C语言',null,'4'）
（3）插入到Sc表
insertintoScvalues（'','1',92）
insertintoScvalues（'','2',85）
insertintoSc（Sno,Cno）values（'','1'）
insertintoSc（Sno,Cno,Grade）values（'','3',null）
insertintoScvalues（'','3',88）
insertintoScvalues（'','1',90）
insertintoScvalues（'','2',80）
（4）多行插入到表中
createtablecs_Student
（
学号char（8）,
姓名char（8）,
年龄smallint
）;
insertintocs_Student
selectSno,Sname,Sage
fromStudentwhereSdept='CS';
（5）检查插入到表中的数据
SELECT*FROMStudent
SELECT*FROMCourse
SELECT*FROMSC
2.修改数据
（1）将学生的年龄改为22岁。
UPDATEstudentSETSage=22WHERESno='';
（2）将所有学生的年龄增一岁。
UPDATEStudentSETSage=Sage+1
（3）填写赵斌同学的管理信息系统课程的成绩
UPDATESCSETGrade=85
WHERESno=''ANDCno='3'
（4）将计算机科学系全体学生的成绩加5分
UPDATEscSETGrade=Grade+5
WHERE'CS'=（selectSdeptfromstudentwhere=;
（5）请自己完成如下操作
将刘晨同学的2号课程成绩修改为80
updateSCsetGrade=80
whereSno=''ANDCno='2'
将“”同学的学号修改为“”
updateStudentsetSno=whereSno='';
（6）检查数据是否修改
3.删除数据
（1）删除学号为2的学生记录
DELETEFROMStudentWHERESno=''
（2）删除学号学生的1号课程选课记录
将选课信息复制到一个临时表tmpSC中：
SELECT*INTOtmpSCFROMSC
在tmpSC中执行删除操作：
DELETEFROMtmpSCWHERESno=''andCno='1'
（3）删除临时表中学生的全部选课记录
deletefromtmpSCwhereSno='';
（4）删除计算机科学系所有学生的选课记录
DELETEFROMtmpSCWHERE'CS'=（selectSdeptfromstudentwhere=）;
（5）删除全部选课记录
DELETEFROMtmpSC
（6）检查数据是否删除
数据查询操作
完成如下查询操作：
1．单表查询
（1）按指定目标列查询
查询学生的详细记录：
SELECT*FROMStudent;
查询学生的学号、姓名和年龄
SELECTSno,Sname,SageFROMStudent;
（2）目标列包含表达式的查询
查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。
SELECTSname,'YearofBirth:
',2004-Sage,LOWER（Sdept）FROMStudent;
（3）查询结果集中修改列名称
查询全体学生的姓名、出生年份和所在系，要求用小写字母表示所有系名。
SELECTSname,'YearofBirth:
'asBIRTH,2000-SageBIRTHDAY,DEPARTMENT=LOWER（Sdept）
FROMStudent;
（4）取消重复行
查询选修了课程的学生学号：
比较ALL和DISTINCT的区别
SELECTSnoFROMSC;
SELECTDISTINCTSnoFROMSC;
（5）简单条件查询
查询计算机科学系全体学生的名单
SELECTSnameFROMStudentWHERESdept='CS';
（6）按范围查询
查询年龄在20~23岁之间的学生的姓名、系
SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23
（7）查询属性值属于指定集合的行
查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名
SELECTSname,SsexFROMStudentWHERESdeptIN（'IS','MA','CS'）;
（8）模糊查询
查询所有姓刘学生的姓名、学号
SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE'刘%'
（9）查询空值
查询缺少成绩的学生的学号和相应的课程号
SELECTSno,CnoFROMscWHEREGradeisnull;
（10）多重条件查询
查询计算机科学系年龄在岁以下的学生姓名
SELECTSnameFROMstudentWHERESdept='CS'andSage<20;
（11）结果集排序
查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列
SELECT*FROMStudentORDERBYSdept,SageDESC;
（12）完成下列查询
查询学生基本信息，结果集属性名使用汉字
selectSname'姓名',Ssex'性别',Sage'年龄',Sdept'所在系',Sclass'班级'
fromStudent;
查询信息系且年龄大于23岁同学的学号
selectSno,SnamefromStudentwhereSdept='IS'andSage>23;
查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系
selectSno,Sname,Sage,SdeptfromStudentwhereSagein（'17','18','20','23'）;
查询年龄不在21~24岁之间的学生的姓名、系别和年龄
selectSname,Sdept,SagefromStudentwhereSagenotbetween21and24;
2．分组统计
（1）聚集函数的使用
查询学生总人数
SELECTCOUNT（*）FROMStudent;
查询选修了课程的学生人数
SELECTCOUNT（DISTINCTSno）FROMSC
查询最高分
SELECTMAX（Grade）FROMSC
（2）聚集函数作用于部分行
统计2号课程的总分、均分
SELECTSUM（grade）总分,AVG（grade）均分,MAX（grade）最高分
FROMscWHERECno='2'
（3）分组统计
统计各门课程的选课人数、均分和最高分
selectcno课程号,count（*）人数,AVG（grade）均分,MAX（grade）最高分
fromscgroupbyCno
统计均分大于90的课程
selectcno课程号,count（*）人数,AVG（grade）均分,MAX（grade）最高分
fromscgroupbyCno
havingAVG（grade）>90
（4）完成下面的查询
统计每个同学的学号、选课数、平均成绩和最高成绩
selectSno'学号',COUNT（Cno）'选课数',AVG（Grade）'平均成绩',MAX（Grade）'最高成绩'
fromSCgroupbySno
统计每个班的每门课的选课人数、平均成绩和最高成绩
select班级,课程号,COUNT（*）选课人数,AVG'平均成绩',MAX'最高成绩'
fromSC,Student
where=
groupby,
3.连接查询
（1）在WHERE中指定连接条件
查询每个参加选课的学生信息及其选修课程的情况
SELECT,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudent,SC
WHERE=
查询每一门课的间接先修课
SELECT*FROMcoursefirst,coursesecond
WHERE=;
SELECT,FROMcoursefirst,coursesecond
WHERE=;
（2）在FROM中指定连接条件
查询每个参加选课的学生信息及其选修课程的情况
SELECT,Sname,Ssex,Sage,Sdept,Cno,Grade
FromStudentJOINSCON=
（3）使用外连接查询
查询每个学生信息及其选修课程的情况
SELECT,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudentLEFTOUTERJOINSCON=
（4）复合条件连接查询
查询选修号课程且成绩在分以上的所有学生
SELECT,Sname
FROMStudentjoinSCON=/*连接条件*/
WHERE='2'AND>90;/*过滤条件*/
（5）多表查询
查询每个学生的学号、姓名、选修的课程名及成绩
SELECT,Sname,Cname,Grade
FROMStudent,SC,Course
WHERE=AND=;
（6）完成下列查询
查询选修了2号课程的同学的学号和姓名
select,Sname
fromStudent,SC
where=and='2'
查询各门课程的课程号、课程名称以及选课学生的学号
select课程号,课程名称,学号
fromSC,Course
where=
查询选修了数据库系统原理课程的同学的学号和姓名和成绩
select,Sname,Grade
fromStudent,Course,SC
where=and
=andCname='数据库系统原理';
4.嵌套查询
（1）由In引出的子查询
查询与“刘晨”在同一个系学习的学生
SELECTSno,Sname,SdeptFROMStudent
WHERESdeptIN（SELECTSdeptFROMStudentWHERESname='刘晨'）;
（2）由比较运算符引出的子查询
找出每个学生超过他选修课程平均成绩的课程号。
SELECTSno,CnoFROMSCx
WHEREGrade>=（SELECTAVG（Grade）FROMSCy
WHERE=;
（3）带修饰符的比较运算符引出的子查询
查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
SELECTSname,SageFROMStudent
WHERESageANDSdept<>'CS';
（4）由EXISTS引出的子查询
查询所有选修了1号课程的学生姓名
SELECTSnameFROMStudent
WHEREEXISTS（SELECT*FROMSCWHERESno=ANDCno='1'）
5.集合查询
（1）集合并
查询计算机科学系的学生及年龄不大于19岁的学生
SELECT*FROMStudentWHERESdept='CS'
UNION
SELECT*FROMStudentWHERESage<=19
（2）集合交
查询计算机科学系且年龄不大于19岁的学生
SELECT*FROMStudentWHERESdept='CS'
INTERSECT
SELECT*FROMStudentWHERESage<=19
（3）集合差
查询计算机科学系且年龄大于19岁的学生
SELECT*FROMStudentWHERESdept='CS'
EXCEPT
SELECT*FROMStudentWHERESage<=19;
视图操作
建立视图并基于视图进行查询：
1.创建视图
（1）建立学生基本信息视图
CREATEVIEWStudent_VIEW（学号,姓名,性别,年龄,系,班级）
AS
SELECTSno,Sname,Ssex,Sage,Sdept,Sclass
FROMStudent;
（2）建立学生均分视图
CREATEVIEWS_G（Sno,Gavg）
AS
SELECTSno,avg（Grade）
FROMSCGROUPBYSno;
（3）建立选课信息视图
CREATEVIEWXK_VIEW
AS
SELECTStudent.*,Course.*,Grade
FROMStudent,SC,Course
WHERE=AND=;
2.视图查询
（1）查询学生基本信息
SELECT*FROMStudent_VIEW
（2）找出每个学生超过他选修课程平均成绩的课程号
SELECT,Cno,grade
FROMSC,S_G
WHERE=andGrade>=
（3）查询每个学生的学号、姓名、选修的课程名及成绩
SELECTSno,Sname,Cname,Grade
FROMXK_VIEW
（4）比较使用视图查询和直接从基表查询的优点
五、实验数据记录和处理
检查数据是否被修改：
检查数据是否删除：
单表查询：
查询学生基本信息，结果集属性名使用查询信息系且年龄大于23岁同学的学号
汉字
无结果
查询年龄是17、18、20、23岁同学的查询年龄不在21~24岁之间的学生的姓名、
学号、姓名、年龄和所在系系别和年龄
分组统计：
统计每个同学的学号、选课数、平均成绩统计每个班的每门课的选课人数、平均成绩
连接查询：
查询选修了2号课程的同学的学号和姓名查询各门课程的课程号、课程名称以及选课学
生的学号
查询选修了数据库系统原理课程的同学的学号
六、实验结果与分析
Student
CourseSc
比较使用视图查询和直接从基表查询的优点
1.视图可以简化用户的操作
视图机制使用户可以将注意力集中在所关心的数据上，而从基本表直接得来则显得复杂，定义了视图则可以简化查询操作。
2.视图使用户能以多种角度看待同一数据
视图可以让用户从不同的方式看待同一数据，适当利用视图可以比基本表更清晰地表达。
3.视图对重构数据库提供了一定程度的逻辑独立性
逻辑结构改变，不影响程序使用。
4.视图能够对机密数据提供安全保护
视图可以让特定的用户查询特定的内容，把用户限制在数据不同的子集，保证安全性。
七、讨论、心得
这个实验是对数据库基本操作的熟悉，花费了大量的时间去进行测试，有时候是出错不是因为代码错误，而是因为插入数据是不能一一对应，或是在连接时出现问题，与表的定义也有一定的关系。
总之编写的时候遇到不少问题，通过解决问题来熟悉了操作，更是要耐心的解决问题，有时候出错的问题非常简单，只要认真的检查一下代码就能发现，希望自己可以在编写代码时更加认真。
实验三：
数据完整性
1、实验目的
（1）了解SQLSerer数据库系统中数据完整性控制的基本方法
（2）熟练掌握常用CREATE或ALTER在创
升级会员 