数据库实验报告 5.docx
- 文档编号:5678180
- 上传时间:2022-12-31
- 格式:DOCX
- 页数:22
- 大小:245.17KB
数据库实验报告 5.docx
《数据库实验报告 5.docx》由会员分享,可在线阅读,更多相关《数据库实验报告 5.docx(22页珍藏版)》请在冰豆网上搜索。
数据库实验报告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来截取当前窗口,并粘贴到实验报告中。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库实验报告 数据库 实验 报告