石家庄经济学院
实 验 报 告
学 院: 信息工程学院 专 业: 计算机科学与技术
信息工程学院计算机实验中心制
学号 513109030101 姓名 张雪洁 指导教师 实验日期 2014/4/30 张有华 设备编号 JF260-20 实验室 260 实验内容 实验5 SQL语言查询的综合练习 一 实验题目 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．Group by 练习
(1) 查询各学院男生和女生的人数。
(2) 查询各个学院男生和女生人数，只显示人数超过5人的统计信息。 (3) 查询各个学院男生、女生的人数及其总人数。（rollup或cube） (4) 统计各门课各个学院选修的人数、各学院选课人数、总人数。 (5) 分别统计各学院男生、女生的平均年龄。
说明：Group By 和 Having, Where ,Order by语句的执行顺序
关键字是按照如下顺序进行执行的：Where, Group By, Having, Order by。首先where将最原始记录中不满足条件的记录删除(所以应该在where语句中尽量的将不符合条件的记录筛选掉，这样可以减少分组的次数)，然后通过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语句中才可以使用IDE，其他条件语句中如果需要引用列名则只能使用ID，而不能使用IDE。
7．聚合函数(sum,min,max,avg,count)的使用
执行顺序是，先通过where子句查询出满足条件的记录，然后使用聚合语句进行统计计算，最后用having对分组统计后的数据进行进一步的筛选。
（1） 显示选课人数少于5人的课程名字
（2） 显示计算机学院的学生选修人数最多的课程的名字
（3） 查询不及格人数最多的两门课的课程号、课程名字和不及格人数
（4） 假设课程10001的成绩中有空值和非空的数值，请对比以下两个查询的结果
Select avg(score) From SC
Where cno=’10001’
和 Select avg(isnull(score,0))
From SC
Where cno=’10001’
(6) 请总结各聚合函数对空值的统计是怎样的？
8．外连接查询练习
注意多表（多于2个表）的内外连接查询时，表的顺序。 （1） 查询所有学生的选课情况。 （2） 查询所有课程的被选情况。
（3） 查询所有学生的选课情况和所有课程的被选情况。
10．嵌套查询与子查询
说明：
⑴ 至多可以嵌套32层，并且子查询中不允许使用order by.
⑵ 可以用子查询产生一个派生的表，用于代替 FROM 子句中的表
⑶ 在 Transact-SQL 中，所有使用表达式的地方，都可以使用子查询代替 例如：
子查询产生一个派生表 查询各门课程的平均分 Select t.cname,avg(score)
From ( select C.cname,sc.score From C,sc
Where C.cno=sc.cno) as t
Group by t.cname
(1) 查询10001课程的平均分，以及各学生本门课成绩与平均分的差值 (2) 查询最低分低于30分的学生信息。 (3) 查询有补考情况的课程名称
(4) 查询至少有两门课超过85分的学生的基本信息 (5) 显示平均分超过85分的学生的学号、姓名、学院 (6) 查询至少一门课（两门课）成绩不及格的学生信息 (7) 被全部学生都选修了的课程 (8) 被全部学生都不选修的课程
四 实验要求
1． 要求掌握查询的基本语法结构。 2． 掌握连接查询、嵌套查询。
3． 掌握SQL语句中关键字的执行的优先级别。 4． 报告中由同学写明具体的操作意图（文字描述）、操作命令（SQL语句）、和执行结果（文字
描述+适当截图）。
5． 对于重要的运行界面和结果窗口，可以用Alt+PrintScreen来截取当前窗口，并粘贴到实验报告中。
五 实验步骤
1. 查询常量、命名列等（datediff函数的使用）
说明：命名列的方法：as 、 =
1) DATEPART
返回代表指定日期的指定日期部分的整数。 语法
DATEPART ( datepart , date ) 参数
datepart
是指定应返回的日期部分的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩写。
日期部分 year quarter month dayofyear day yy, yyyy qq, q mm, m dy, y dd, d 缩写 week weekday Hour minute second millisecond
wk, ww dw hh mi, n ss, s ms week (wk, ww) 日期部分反映对 SET DATEFIRST 作的更改。任何一年的 1 月 1 日定义了 week 日期部分的开始数字，例如：DATEPART(wk, 'Jan 1, xxxx') = 1，此处 xxxx 代表任一年。 weekday (dw) 日期部分返回对应于星期中的某天的数，例如：Sunday = 1、Saturday = 7。weekday 日期部分产生的数取决于 SET DATEFIRST 设定的值，此命令设定星期中的第一天。
date
是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。对 1753 年 1 月 1 日之后的日期用datetime 数据类型。更早的日期存储为字符数据。当输入 datetime 值时，始终将其放入引号中。因为 smalldatetime 只精确到分钟，所以当用 smalldatetime 值时，秒和毫秒总是 0。 如果只指定年份的最后两位数字，则小于或等于\两位数年份截止期\配置选项的值的最后两位数字的数字所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如，如果 two digit year cutoff 为 2049 （默认），则 49 被解释为 2049，2050 被解释为 1950。为避免模糊，请使用四位数的年份。
有关时间值指定的更多信息，请参见时间格式。有关日期指定的更多信息，请参见 datetime 和 smalldatetime。
2) DATENAME
返回代表指定日期的指定日期部分的字符串。 语法
DATENAME ( datepart , date ) 参数
datepart
是指定应返回的日期部分的参数。下表列出了 Microsoft? SQL Server? 识别的日期部分和缩写。
日期部分 缩写