数据库实验报告 5.docx

1、数据库实验报告 51内外连接查询2嵌套查询3相关子查询二 实验目的1掌握索引的建立与删除操作。2掌握视图的创建和查询操作，理解视图的作用。3掌握触发器的创建与使用，理解数据库安全性的定义。三 实验内容1查询常量、命名列等（datediff函数的使用）说明：命名列的方法：as 、 =1) 查询计算机学院的学生的学号、姓名、年龄。（要求命令列，并使用两种方法计算年龄）。2) 查询课程号为10001的课程的原始成绩和调整成绩，其中调整成绩=原始成绩+5分。3) 查询所有学生的学号、姓名和出生日期（要求出生日期只显示年份和月份）。4) 查询9月份出生的学生5) 查询所有的课程信息，在查询结果中增加关于

2、学校的说明列“石家庄经济学院本部”。2Top练习(1) 从C表中查询前5门课的信息。(2) 从SC表中查询10005课程考试成绩较好的50%的成绩信息。(3) 查找“高级语言程序设计”成绩最好的两个学生的信息。(4) 查询女生人数最多的两个学院，查询结果显示：学院、女生人数。3通配符练习(1) 查找名字为刘某某的学生信息，要求名字必须是3个字。（假设有刘明、刘兰花、刘芳等同学）(2) 查找名字为刘某的学生信息，但是名字的第二个字不是“兰” 5逻辑运算符（运算符的优先级为 NOT 、 AND 、OR，求值顺序自左向右，可以利用括号改变求值顺序）(1) 查找不是外语学院和会计学院的，成绩不及格或成

3、绩为空的学生信息。6Group by 练习(1) 查询各学院男生和女生的人数。(2) 查询各个学院男生和女生人数，只显示人数超过5人的统计信息。(3) 查询各个学院男生、女生的人数及其总人数。（rollup或cube）(4) 统计各门课各个学院选修的人数、各学院选课人数、总人数。(5) 分别统计各学院男生、女生的平均年龄。说明：Group By 和 Having, Where ,Order by语句的执行顺序关键字是按照如下顺序进行执行的：Where, Group By, Having, Order by。首先where将最原始记录中不满足条件的记录删除(所以应该在where语句中尽量的将不符

4、合条件的记录筛选掉，这样可以减少分组的次数)，然后通过Group By关键字后面指定的分组条件将筛选得到的视图进行分组，接着系统根据Having关键字后面指定的筛选条件，将分组视图后不满足条件的记录筛选掉，然后按照Order By语句对视图进行排序，这样最终的结果就产生了。在这四个关键字中，只有在Order By语句中才可以使用最终视图的列名，如： SELECT FruitName, Place, Price, ID AS IDE, Discount FROM FRUITINFO WHERE (Place =china) ORDER BY IDE 这里只有在ORDER BY语句中才可以使用ID

5、E，其他条件语句中如果需要引用列名则只能使用ID，而不能使用IDE。7聚合函数(sum,min,max,avg,count)的使用执行顺序是，先通过where子句查询出满足条件的记录，然后使用聚合语句进行统计计算，最后用having对分组统计后的数据进行进一步的筛选。（1） 显示选课人数少于5人的课程名字（2） 显示计算机学院的学生选修人数最多的课程的名字（3） 查询不及格人数最多的两门课的课程号、课程名字和不及格人数（4） 假设课程10001的成绩中有空值和非空的数值，请对比以下两个查询的结果Select avg(score)From SCWhere cno=10001 和 Select a

6、vg(isnull(score,0)From SCWhere cno=10001(6) 请总结各聚合函数对空值的统计是怎样的？8外连接查询练习注意多表（多于2个表）的内外连接查询时，表的顺序。（1） 查询所有学生的选课情况。（2） 查询所有课程的被选情况。（3） 查询所有学生的选课情况和所有课程的被选情况。1 10嵌套查询与子查询说明： 至多可以嵌套32层，并且子查询中不允许使用order by. 可以用子查询产生一个派生的表，用于代替 FROM 子句中的表 在 Transact-SQL 中，所有使用表达式的地方，都可以使用子查询代替例如：子查询产生一个派生表查询各门课程的平均分Select

7、ame,avg(score)From ( select C.cname,sc.score From C,sc Where C.cno=o) as tGroup by ame(1) 查询10001课程的平均分，以及各学生本门课成绩与平均分的差值(2) 查询最低分低于30分的学生信息。(3) 查询有补考情况的课程名称(4) 查询至少有两门课超过85分的学生的基本信息(5) 显示平均分超过85分的学生的学号、姓名、学院(6) 查询至少一门课（两门课）成绩不及格的学生信息(7) 被全部学生都选修了的课程(8) 被全部学生都不选修的课程四 实验步骤1查询常量、命名列等（datediff函数的使用）说明：

8、命名列的方法：as 、 =1) 查询计算机学院的学生的学号、姓名、年龄。（要求命令列，并使用两种方法计算年龄）。第一种方法代码： select Sno as 学号,Sname as 姓名,datediff(year,Sbirthday,getdate()as 年龄 from S运行结果：运行结果显示如下图表1：图表 1第二种代码： select Sno as 学号,Sname as 姓名,FLOOR(datediff(DY,Sbirthday,getdate()/365.25)as 年龄 from S运行结果：运行显示结果图表2：图表 2运行结果有些不同，datediff返回学生的大概年龄，不

9、会精确到月或日；而第二种可以精确算出学生的年龄。第三种代码：select Sno as 学号,Sname as 姓名,year(getdate()-year(Sbirthday)as 年龄 from S运行结果：显示结果与第一种相同2) 查询课程号为0001的课程的原始成绩和调整成绩，其中调整成绩=原始成绩+5分。代码：select Grade as 原始成绩, 调整成绩 =Grade+5 from SC where Cno=0001运行结果：结果显示如下图表3：图表 3调整成绩为原始成绩+53) 查询所有学生的学号、姓名和出生日期（要求出生日期只显示年份和月份）。代码：select Sno

10、as 学号,Sname as 姓名,substr(Sbirthday,0,7)as 出生日期 from S运行结果：代码：select Sno as 学号,Sname as 姓名,substring(Sbirthday,0,7)as 出生日期 from S运行结果：正确代码：select Sno as 学号,Sname as 姓名,CONVERT(varchar(6) ,Sbirthday, 112 )as 出生日期 from SCONVERT() 函数是把日期转换为新数据类型的通用函数，将Sbirthday的datetime类型改为varchar, 函数可以用不同的格式显示日期/时间数据.运

11、行结果：运行结果如下图表1所示：图表 44) 查询9月份出生的学生代码：select * from S where month(Convert(datetime,Sbirthday,120)=9运行结果：显示结果如图表5所示：图表 55) 查询所有的课程信息，在查询结果中增加关于学校的说明列“石家庄经济学院本部”。代码：select * ,石家庄经济学院本部 from C运行结果：命令已成功完成。 显示结果图表6：在 Select 语句的查询结果中增加一些 说明列，增加的文字使用单括号括起来图表 62 Top练习语法：SELECT TOP number|percent column_name(

12、s)FROM table_name(1) 从C表中查询前5门课的信息。代码：select top 5 * from C运行结果：命令已成功完成。显示结果图表7：只显示前五条信息图表 7(2) 从SC表中查询0006课程考试成绩较好的50%的成绩信息。SC表情况如下图表8所示图表 8代码; select top 50 percent * from SC where Cno=0006运行结果：命令已成功完成。显示结果图表9：图表 9(3) 查找“高等数学”成绩最好的两个学生的信息。代码： select top 2 C.Cname,SC.Sno,SC.Grade from SC,C WHERE C.

13、Cname=高等数学 and SC.Cno=C.Cno ORDER BY SC.Grade DESC运行结果：命令已成功完成。显示结果图表10：高等数学课程号为0006，基本情况如上题；查询结果如下图所示图表 10(4) 查询女生人数最多的两个学院，查询结果显示：学院、女生人数。各系男女信息如下图表11所示：图表 11代码：select top 2 count(*),Sdept,Ssex from S where Ssex=女group by Sdept,Ssex运行结果：命令已成功完成。显示结果图表12 体育系女生1人，外语系女生一人：图表 123通配符练习(1) 查找名字为刘某某的学生信息

14、，要求名字必须是3个字。（假设有刘明、刘兰花、刘芳等同学）S表情况图表13：图表 13 代码： select * from S where LEN(Sname)=3 and Sname like 刘% 运行结果：命令已成功完成。 显示结果图表14：图表 14(2) 查找名字为刘某的学生信息，但是名字的第二个字不是“兰”代码： select * from S where Sname like 刘% and Sname not like 刘兰% 运行结果：命令已成功完成。显示结果图表15：图表 15 5逻辑运算符（运算符的优先级为 NOT 、 AND 、OR，求值顺序自左向右，可以利用括号改变求值

15、顺序）(1) 查找不是大学英语和高等数学的，成绩不及格或成绩为空的学生信息。代码：select * from C,SC where C.Cno=SC.Cno and C.Cname not in(英语,高等数学) and SC.Grade=2运行结果：命令已成功完成。显示结果图表18：图表 18Having语句等于where语句进行条件查询(3) 查询各个学院男生、女生的人数及其总人数。（rollup或cube）代码select count(*),Sdept,Ssex from S group by Sdept,Ssex with cube运行结果：命令已成功完成。结果显示图表19：图表 19

16、CUBE 生成的结果集显示了所选列中值的所有组合的聚合。 ROLLUP生成的结果集显示了所选列中值的某一层次结构的聚合(4) 统计各门课各个学院选修的人数、各学院选课人数、总人数。代码：select count(*),S.Sdept as 学院,SC.Cno AS 所选的课程 FROM S,SC where S.Sno=SC.Sno group by S.Sdept,SC.Cno with cube运行结果：命令已成功完成。显示结果图表20：图表 20(5) 分别统计各学院男生、女生的平均年龄。代码： select Sdept,Ssex ,count(*) as number, avg(Sag

17、e) as averageAge from S group by Sdept,Ssex with cube运行结果：average aggregate 运算不能以 char 数据类型作为参数。错误原因：avg为运算函数，原来表格Sage的数据类型为char 应该为int运行显示结果图表21： 图表 217聚合函数(sum,min,max,avg,count)的使用执行顺序是，先通过where子句查询出满足条件的记录，然后使用聚合语句进行统计计算，最后用having对分组统计后的数据进行进一步的筛选。（1） 显示选课人数少于3人的课程名字代码：select C.Cname, count(*) f

18、rom SC ,C where SC.Cno = C.Cno group by C.Cno having count(*)=2运行结果：服务器: 消息 8120，级别 16，状态 1，行 1列 C.Cname 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。 把提示列补在group by后面，错误消失 正确代码：select C.Cname, count(*) from SC ,C where SC.Cno = C.Cno group by C.Cno ,C.Cname having count(*)=2运行结果：命令已成功完成。显示结果图表22：图表 2

19、2（2） 显示计算机学院的学生选修人数最多的课程的名字错误代码：SELECT TOP 1 with ties C.Cname as 课程FROM SC,S,Cwhere S.Sno=SC.Sno and SC.Cno=C.Cno GROUP BY C.Cname,S.Sdept having S.Sdept=计算机系运行结果：服务器: 消息 1062，级别 15，状态 1，行 4在没有对应的 ORDER BY 子句时，不允许使用 TOP N WITH TIES 子句。with ties必须有对应的ORDER BY 子句正确代码：SELECT TOP 1 with ties C.Cname as

20、 课程FROM SC,S,Cwhere S.Sno=SC.Sno and SC.Cno=C.Cno GROUP BY C.Cname,S.Sdept having S.Sdept=计算机系 ORDER BY COUNT(S.Sno) DESC或者ELECT TOP 1 C.Cname as 课程FROM SC,S,Cwhere S.Sno=SC.Sno and SC.Cno=C.Cno GROUP BY C.Cname,S.Sdept having S.Sdept=计算机系：运行结果：命令已成功完成。显示结果图表23：图表 23（3） 查询不及格人数最多的两门课的课程号、课程名字和不及格人数代

21、码：select top 2 C.Cname,C.Cno ,count(*) as 不及格人数 from SC ,C where SC.Cno = C.Cno group by C.Cno ,C.Cname,SC.Grade having SC.Grade60运行结果：命令已成功完成。显示结果图表24：图表 24（4） 假设课程0001的成绩中有空值和非空的数值，请对比以下两个查询的结果Select avg(score)From SCWhere cno=0001 和 Select avg(isnull(score,0)From SCWhere cno=10001表基本情况图表25：图表 25语

22、句; Select avg(Grade) From SC Where Cno=0001显示结果图表26：图表 26语句2：Select avg(isnull(Grade,0) From SC Where cno=0001显示结果图表27：图表 27语句一没有将空值算入。语句二将空值参与运算(6) 请总结各聚合函数对空值的统计是怎样的？除了 COUNT 以外，聚合函数都会忽略空值。8外连接查询练习注意多表（多于2个表）的内外连接查询时，表的顺序。（1） 查询所有学生的选课情况。代码：select S.Sname,S.Sno,C.Cname,C.Cno,SC.Grade from S,C,SC W

23、HERE S.Sno=SC.Sno and SC.Cno=C.Cno显示结果图表28：图表 28（2） 查询所有课程的被选情况。 代码： Select s.sno,o from s right join sc on s.sno=sc.sno 运行结果：命令已成功完成。 运行显示结果图表29：图表 29（3） 查询所有学生的选课情况和所有课程的被选情况。 代码：select s.sno,s.sname,o from s full join sc on s.sno=sc.sno 运行结果：命令已成功完成。 显示结果图表30：图表 309.(1) 查询0001课程的平均分，以及各学生本门课成绩与平均

24、分的差值SC表基本信息如图表25代码：select avg(Grade)as 平均分 ,Grade-avg(Grade)as 差值 from SC where Cno=0001 group by Cno运行结果：列 SC.Grade 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。代码：select avg(Cno)as 平均分 ,Grade-avg(Cno)as 差值 from SC where Cno=0001 group by Cno,Grade运行结果：average aggregate 运算不能以 char 数据类型作为参数。以上运行代码，AVG

25、函数运行时必须为整数，且为聚集函数必须有group by ；上述代码均显示不正确；此时可以声明一个变量avg代替avg函数的结果，免除了变量不统一等问题，参与到后面的语句运算过程中：正确代码：declare avg float select avg=avg(Grade) from SC where Cno=0001select Sno,Grade,avg as 平均分,(Grade-avg)as 差值 from SC where Cno=0001运行结果：命令已成功完成。运行显示结果图表31：图表 31(2) 查询最低分低于30分的学生信息。SC表基本情况如下图表32：图表 32代码：sele

26、ct Sno,min(Grade)as 最低分 from SC group by Sno having min(Grade)=30 运行结果：命令已成功完成。显示结果如图表32图表 33(3) 查询有补考情况的课程名称代码：select C.Cname from C,SC where SC.Grade 75 group by Sno having count(Cno)=2) 运行结果：命令已成功完成。显示结果图表35：图表 36(5) 显示平均分超过85分的学生的学号、姓名、学院表的基本情况如图表36图表 37代码：select Sno,Sname,Sdept from S where Sno

27、 in ( select Sno from SC group by Sno having avg(Grade) 85 ) 运行结果：命令已成功完成。显示结果图表37：图表 38(6) 查询至少一门课（两门课）成绩不及格的学生信息代码： select * from S where Sno in(select Sno from SC where Grade=1) 运行结果：命令已成功完成。显示结果图表38：图表 39(7) 被全部学生都选修了的课程代码：SELECT C.Cno,C.Cname FROM C JOIN SC ON (C.Cno = SC.Cno) GROUP BY C.Cno,C.

28、Cname HAVING COUNT(SC.Sno) = (SELECT COUNT(Sno) FROM S); 运行结果：命令已成功完成。显示结果图表39：没有符合条件的查询结果图表 40(8) 被全部学生都不选修的课程代码： select Cname from C where Cno not in (select distinct Cno from SC); 运行结果：命令已成功完成。 显示结果图表40： 图表 41五 实验要求1 要求掌握查询的基本语法结构。2 掌握连接查询、嵌套查询。3 掌握SQL语句中关键字的执行的优先级别。4 报告中由同学写明具体的操作意图（文字描述）、操作命令（SQL语句）、和执行结果（文字描述+适当截图）。5 对于重要的运行界面和结果窗口，可以用Alt+PrintScreen来截取当前窗口