数据库实验报告 5.docx
《数据库实验报告 5.docx》由会员分享，可在线阅读，更多相关《数据库实验报告 5.docx（26页珍藏版）》请在冰豆网上搜索。
数据库实验报告5
1．内外连接查询
2．嵌套查询
3．相关子查询
二实验目的
1．掌握索引的建立与删除操作。
2．掌握视图的创建和查询操作，理解视图的作用。
3．掌握触发器的创建与使用，理解数据库安全性的定义。
三实验内容
1．查询常量、命名列等（datediff函数的使用）
说明：
命名列的方法：
as、=
1）查询计算机学院的学生的学号、姓名、年龄。
（要求命令列，并使用两种方法计算年龄）。
2）查询课程号为10001的课程的原始成绩和调整成绩，其中调整成绩=原始成绩+5分。
3）查询所有学生的学号、姓名和出生日期（要求出生日期只显示年份和月份）。
4）查询9月份出生的学生
5）查询所有的课程信息，在查询结果中增加关于学校的说明列“石家庄经济学院本部”。
2．Top练习
（1）从C表中查询前5门课的信息。
（2）从SC表中查询’10005’课程考试成绩较好的50%的成绩信息。
（3）查找“高级语言程序设计”成绩最好的两个学生的信息。
（4）查询女生人数最多的两个学院，查询结果显示：
学院、女生人数。
3．通配符练习
（1）查找名字为刘某某的学生信息，要求名字必须是3个字。
（假设有刘明、刘兰花、刘芳等同学）
（2）查找名字为刘某的学生信息，但是名字的第二个字不是“兰”
5．逻辑运算符（运算符的优先级为NOT、AND、OR，求值顺序自左向右，可以利用括号改变求值顺序）
（1）查找不是外语学院和会计学院的，成绩不及格或成绩为空的学生信息。
6．Groupby练习
（1）查询各学院男生和女生的人数。
（2）查询各个学院男生和女生人数，只显示人数超过5人的统计信息。
（3）查询各个学院男生、女生的人数及其总人数。
（rollup或cube）
（4）统计各门课各个学院选修的人数、各学院选课人数、总人数。
（5）分别统计各学院男生、女生的平均年龄。
说明：
GroupBy和Having,Where,Orderby语句的执行顺序
关键字是按照如下顺序进行执行的：
Where,GroupBy,Having,Orderby。
首先where将最原始记录中不满足条件的记录删除（所以应该在where语句中尽量的将不符合条件的记录筛选掉，这样可以减少分组的次数），然后通过GroupBy关键字后面指定的分组条件将筛选得到的视图进行分组，接着系统根据Having关键字后面指定的筛选条件，将分组视图后不满足条件的记录筛选掉，然后按照OrderBy语句对视图进行排序，这样最终的结果就产生了。
在这四个关键字中，只有在OrderBy语句中才可以使用最终视图的列名，如：
SELECTFruitName,Place,Price,IDASIDE,Discount
FROMFRUITINFO
WHERE（Place='china'）
ORDERBYIDE
这里只有在ORDERBY语句中才可以使用IDE，其他条件语句中如果需要引用列名则只能使用ID，而不能使用IDE。
7．聚合函数（sum,min,max,avg,count）的使用
执行顺序是，先通过where子句查询出满足条件的记录，然后使用聚合语句进行统计计算，最后用having对分组统计后的数据进行进一步的筛选。
（1）显示选课人数少于5人的课程名字
（2）显示计算机学院的学生选修人数最多的课程的名字
（3）查询不及格人数最多的两门课的课程号、课程名字和不及格人数
（4）假设课程10001的成绩中有空值和非空的数值，请对比以下两个查询的结果
Selectavg（score）
FromSC
Wherecno=’10001’
和Selectavg（isnull（score,0））
FromSC
Wherecno=’10001’
（6）请总结各聚合函数对空值的统计是怎样的？
8．外连接查询练习
注意多表（多于2个表）的内外连接查询时，表的顺序。
（1）查询所有学生的选课情况。
（2）查询所有课程的被选情况。
（3）查询所有学生的选课情况和所有课程的被选情况。
1．10．嵌套查询与子查询
说明：
⑴至多可以嵌套32层，并且子查询中不允许使用orderby.
⑵可以用子查询产生一个派生的表，用于代替FROM子句中的表
⑶在Transact-SQL中，所有使用表达式的地方，都可以使用子查询代替
例如：
子查询产生一个派生表
查询各门课程的平均分
Selectame,avg（score）
From（selectC.cname,sc.score
FromC,sc
WhereC.cno=o）ast
Groupbyame
（1）查询10001课程的平均分，以及各学生本门课成绩与平均分的差值
（2）查询最低分低于30分的学生信息。
（3）查询有补考情况的课程名称
（4）查询至少有两门课超过85分的学生的基本信息
（5）显示平均分超过85分的学生的学号、姓名、学院
（6）查询至少一门课（两门课）成绩不及格的学生信息
（7）被全部学生都选修了的课程
（8）被全部学生都不选修的课程
四实验步骤
1．查询常量、命名列等（datediff函数的使用）
说明：
命名列的方法：
as、=
1）查询计算机学院的学生的学号、姓名、年龄。
（要求命令列，并使用两种方法计算年龄）。
第一种方法代码：
selectSnoas学号,Snameas姓名,datediff（year,Sbirthday,getdate（））as年龄fromS
运行结果：
运行结果显示如下图表1：
图表1
第二种代码：
selectSnoas学号,Snameas姓名,FLOOR（datediff（DY,Sbirthday,getdate（））/365.25）as年龄fromS
运行结果：
运行显示结果图表2：
图表2
运行结果有些不同，datediff返回学生的大概年龄，不会精确到月或日；而第二种可以精确算出学生的年龄。
第三种代码：
selectSnoas学号,Snameas姓名,year（getdate（））-year（Sbirthday）as年龄fromS
运行结果：
显示结果与第一种相同
2）查询课程号为0001的课程的原始成绩和调整成绩，其中调整成绩=原始成绩+5分。
代码：
selectGradeas原始成绩,调整成绩=Grade+5fromSCwhereCno=0001
运行结果：
结果显示如下图表3：
图表3调整成绩为原始成绩+5
3）查询所有学生的学号、姓名和出生日期（要求出生日期只显示年份和月份）。
代码：
selectSnoas学号,Snameas姓名,substr（'Sbirthday',0,7）as出生日期fromS
运行结果：
代码：
selectSnoas学号,Snameas姓名,substring（Sbirthday,0,7）as出生日期fromS
运行结果：
正确代码：
selectSnoas学号,Snameas姓名,CONVERT（varchar（6）,Sbirthday,112）as出生日期fromS
CONVERT（）函数是把日期转换为新数据类型的通用函数，将Sbirthday的datetime类型改为varchar,函数可以用不同的格式显示日期/时间数据.
运行结果：
运行结果如下图表1所示：
图表4
4）查询9月份出生的学生
代码：
select*fromSwheremonth（Convert（datetime,Sbirthday,120））=9
运行结果：
显示结果如图表5所示：
图表5
5）查询所有的课程信息，在查询结果中增加关于学校的说明列“石家庄经济学院本部”。
代码：
select*,'石家庄经济学院本部'fromC
运行结果：
命令已成功完成。
显示结果图表6：
在Select语句的查询结果中增加一些说明列，增加的文字使用单括号括起来
图表6
2．Top练习
语法：
SELECTTOPnumber|percentcolumn_name（s）
FROMtable_name
（1）从C表中查询前5门课的信息。
代码：
selecttop5*fromC
运行结果：
命令已成功完成。
显示结果图表7：
只显示前五条信息
图表7
（2）从SC表中查询’0006’课程考试成绩较好的50%的成绩信息。
SC表情况如下图表8所示
图表8
代码;selecttop50percent*fromSCwhereCno='0006'
运行结果：
命令已成功完成。
显示结果图表9：
图表9
（3）查找“高等数学”成绩最好的两个学生的信息。
代码：
selecttop2C.Cname,SC.Sno,SC.GradefromSC,CWHEREC.Cname='高等数学'andSC.Cno=C.CnoORDERBYSC.GradeDESC
运行结果：
命令已成功完成。
显示结果图表10：
高等数学课程号为0006，基本情况如上题；查询结果如下图所示
图表10
（4）查询女生人数最多的两个学院，查询结果显示：
学院、女生人数。
各系男女信息如下图表11所示：
图表11
代码：
selecttop2count（*）,Sdept,SsexfromSwhereSsex='女'groupbySdept,Ssex
运行结果：
命令已成功完成。
显示结果图表12体育系女生1人，外语系女生一人：
图表12
3．通配符练习
（1）查找名字为刘某某的学生信息，要求名字必须是3个字。
（假设有刘明、刘兰花、刘芳等同学）
S表情况图表13：
图表13
代码：
select*fromSwhereLEN（Sname）=3andSnamelike'刘%'
运行结果：
命令已成功完成。
显示结果图表14：
图表14
（2）查找名字为刘某的学生信息，但是名字的第二个字不是“兰”
代码：
select*fromSwhereSnamelike'刘%'andSnamenotlike'刘兰%'
运行结果：
命令已成功完成。
显示结果图表15：
图表15
5．逻辑运算符（运算符的优先级为NOT、AND、OR，求值顺序自左向右，可以利用括号改变求值顺序）
（1）查找不是大学英语和高等数学的，成绩不及格或成绩为空的学生信息。
代码：
select*fromC,SC
whereC.Cno=SC.CnoandC.Cnamenotin（'英语','高等数学'）andSC.Grade<60andSC.Grade=null
运行结果：
命令已成功完成。
显示结果图表16：
数据库中没有满足条件的数据
图表16
6．Groupby练习
（1）查询各学院男生和女生的人数。
代码：
selectcount（*）,Sdept,SsexfromSgroupbySdept,Ssex
运行结果：
命令已成功完成。
结果显示如下图表17：
图表17
（2）查询各个学院男生和女生人数，只显示人数超过2人的统计信息。
代码：
selectcount（*）,Sdept,SsexfromSgroupbySdept,Ssexhavingcount（*）>=2
运行结果：
命令已成功完成。
显示结果图表18：
图表18
Having语句等于where语句进行条件查询
（3）查询各个学院男生、女生的人数及其总人数。
（rollup或cube）
代码
selectcount（*）,Sdept,SsexfromSgroupbySdept,Ssexwithcube
运行结果：
命令已成功完成。
结果显示图表19：
图表19
CUBE生成的结果集显示了所选列中值的所有组合的聚合。
ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合
（4）统计各门课各个学院选修的人数、各学院选课人数、总人数。
代码：
selectcount（*）,S.Sdeptas学院,SC.CnoAS所选的课程FROMS,SCwhereS.Sno=SC.SnogroupbyS.Sdept,SC.Cnowithcube
运行结果：
命令已成功完成。
显示结果图表20：
图表20
（5）分别统计各学院男生、女生的平均年龄。
代码：
selectSdept,Ssex,count（*）asnumber,avg（Sage）asaverageAge
fromSgroupbySdept,Ssexwithcube
运行结果：
averageaggregate运算不能以char数据类型作为参数。
错误原因：
avg为运算函数，原来表格Sage的数据类型为char应该为int
运行显示结果图表21：
图表21
7．聚合函数（sum,min,max,avg,count）的使用
执行顺序是，先通过where子句查询出满足条件的记录，然后使用聚合语句进行统计计算，最后用having对分组统计后的数据进行进一步的筛选。
（1）显示选课人数少于3人的课程名字
代码：
selectC.Cname,count（*）fromSC,CwhereSC.Cno=C.CnogroupbyC.Cnohavingcount（*）>=2
运行结果：
服务器:
消息8120，级别16，状态1，行1
列'C.Cname'在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在GROUPBY子句中。
把提示列补在groupby后面，错误消失
正确代码：
selectC.Cname,count（*）fromSC,C
whereSC.Cno=C.CnogroupbyC.Cno,C.Cnamehavingcount（*）>=2
运行结果：
命令已成功完成。
显示结果图表22：
图表22
（2）显示计算机学院的学生选修人数最多的课程的名字
错误代码：
SELECTTOP1withtiesC.Cnameas课程
FROMSC,S,C
whereS.Sno=SC.SnoandSC.Cno=C.Cno
GROUPBYC.Cname,S.SdepthavingS.Sdept='计算机系'
运行结果：
服务器:
消息1062，级别15，状态1，行4
在没有对应的ORDERBY子句时，不允许使用TOPNWITHTIES子句。
withties必须有对应的ORDERBY子句
正确代码：
SELECTTOP1withtiesC.Cnameas课程
FROMSC,S,C
whereS.Sno=SC.SnoandSC.Cno=C.Cno
GROUPBYC.Cname,S.SdepthavingS.Sdept='计算机系'
ORDERBYCOUNT（S.Sno）DESC
或者
ELECTTOP1C.Cnameas课程
FROMSC,S,C
whereS.Sno=SC.SnoandSC.Cno=C.Cno
GROUPBYC.Cname,S.SdepthavingS.Sdept='计算机系'：
运行结果：
命令已成功完成。
显示结果图表23：
图表23
（3）查询不及格人数最多的两门课的课程号、课程名字和不及格人数
代码：
selecttop2C.Cname,C.Cno,count（*）as不及格人数fromSC,C
whereSC.Cno=C.CnogroupbyC.Cno,C.Cname,SC.GradehavingSC.Grade<60
运行结果：
命令已成功完成。
显示结果图表24：
图表24
（4）假设课程0001的成绩中有空值和非空的数值，请对比以下两个查询的结果
Selectavg（score）
FromSC
Wherecno=’0001’
和Selectavg（isnull（score,0））
FromSC
Wherecno=’10001’
表基本情况图表25：
图表25
语句;
Selectavg（Grade）FromSCWhereCno='0001'
显示结果图表26：
图表26
语句2：
Selectavg（isnull（Grade,0））FromSCWherecno='0001'
显示结果图表27：
图表27
语句一没有将空值算入。
语句二将空值参与运算
（6）请总结各聚合函数对空值的统计是怎样的？
除了COUNT以外，聚合函数都会忽略空值。
8．外连接查询练习
注意多表（多于2个表）的内外连接查询时，表的顺序。
（1）查询所有学生的选课情况。
代码：
selectS.Sname,S.Sno,C.Cname,C.Cno,SC.GradefromS,C,SC
WHERES.Sno=SC.SnoandSC.Cno=C.Cno
显示结果图表28：
图表28
（2）查询所有课程的被选情况。
代码：
Selects.sno,ofromsrightjoinscons.sno=sc.sno
运行结果：
命令已成功完成。
运行显示结果图表29：
图表29
（3）查询所有学生的选课情况和所有课程的被选情况。
代码：
selects.sno,s.sname,ofromsfulljoinscons.sno=sc.sno
运行结果：
命令已成功完成。
显示结果图表30：
图表30
9.
（1）查询0001课程的平均分，以及各学生本门课成绩与平均分的差值
SC表基本信息如图表25
代码：
selectavg（Grade）as平均分,Grade-avg（Grade）as差值fromSCwhereCno='0001'groupbyCno
运行结果：
列'SC.Grade'在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在GROUPBY子句中。
代码：
selectavg（Cno）as平均分,Grade-avg（Cno）as差值fromSCwhereCno='0001'groupbyCno,Grade
运行结果：
averageaggregate运算不能以char数据类型作为参数。
以上运行代码，AVG函数运行时必须为整数，且为聚集函数必须有groupby；上述代码均显示不正确；此时可以声明一个变量@avg代替avg函数的结果，免除了变量不统一等问题，参与到后面的语句运算过程中：
正确代码：
declare@avgfloat
select@avg=avg（Grade）fromSCwhereCno='0001'
selectSno,Grade,@avgas平均分,（Grade-@avg）as差值fromSCwhereCno='0001'
运行结果：
命令已成功完成。
运行显示结果图表31：
图表31
（2）查询最低分低于30分的学生信息。
SC表基本情况如下图表32：
图表32
代码：
selectSno,min（Grade）as最低分fromSCgroupbySnohavingmin（Grade）<=30
运行结果：
命令已成功完成。
显示结果如图表32
图表33
（3）查询有补考情况的课程名称
代码：
selectC.CnamefromC,SCwhereSC.Grade<60andC.Cno=SC.Cno
运行结果：
命令已成功完成。
运行显示结果图表33：
图表34
（4）查询至少有两门课超过75分的学生的基本信息
符合条件的数据如下图表34所示：
图表35
代码：
SELECT*FROMSWHERESnoIN（SELECTSnoFROMSCwhereGrade>75
groupbySnohavingcount（Cno）>=2）
运行结果：
命令已成功完成。
显示结果图表35：
图表36
（5）显示平均分超过85分的学生的学号、姓名、学院
表的基本情况如图表36
图表37
代码：
selectSno,Sname,SdeptfromSwhereSnoin（selectSnofromSC
groupbySnohavingavg（Grade）>85）
运行结果：
命令已成功完成。
显示结果图表37：
图表38
（6）查询至少一门课（两门课）成绩不及格的学生信息
代码：
select*fromSwhereSnoin（selectSnofromSCwhereGrade<60groupbySnohavingcount（Cno）>=1）
运行结果：
命令已成功完成。
显示结果图表38：
图表39
（7）被全部学生都选修了的课程
代码：
SELECTC.Cno,C.CnameFROMCJOINSCON（C.Cno=SC.Cno）GROUPBYC.Cno,C.CnameHAVINGCOUNT（SC.Sno）=（SELECTCOUNT（Sno）FROMS）;
运行结果：
命令已成功完成。
显示结果图表39：
没有符合条件的查询结果
图表40
（8）被全部学生都不选修的课程
代码：
selectCnamefromCwhereCnonotin（selectdistinctCnofromSC）;
运行结果：
命令已成功完成。
显示结果图表40：
图表41
五实验要求
1．要求掌握查询的基本语法结构。
2．掌握连接查询、嵌套查询。
3．掌握SQL语句中关键字的执行的优先级别。
4．报告中由同学写明具体的操作意图（文字描述）、操作命令（SQL语句）、和执行结果（文字描述+适当截图）。
5．对于重要的运行界面和结果窗口，可以用Alt+PrintScreen来截取当前窗口
升级会员 