实验5 SQL语言查询的综合练习

石家庄经济学院

实 验 报 告

学 院: 信息工程学院 专 业: 计算机科学与技术

信息工程学院计算机实验中心制

学号 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? 识别的日期部分和缩写。

日期部分 缩写

year quarter month dayofyear day week weekday Hour minute second millisecond

yy, yyyy qq, q mm, m dy, y dd, d wk, ww dw hh mi, n ss, s ms weekday (dw) 日期部分返回星期几（星期天、星期一等）。

是返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。对 1753 年 1 月 1 日之后的日期用datetime 数据类型。更早的日期存储为字符数据。当输入 datetime 值时，始终将其放入引号中。因为 smalldatetime 只精确到分钟，所以当用 smalldatetime 值时，秒和毫秒总是 0。有关指定日期的更多信息，请参见 datetime 和 smalldatetime。有关时间值指定的更多信息，请参见时间格式。

如果只指定年份的最后两位数字，则小于或等于 two digit year cutoff 配置选项的值的最后两位数字的值所在世纪与截止年所在世纪相同。大于该选项的值的最后两位数字的数字所在世纪为截止年所在世纪的前一个世纪。例如，如果 two digit year cutoff 为 2050（默认），则 49 被解释为 2049，50 被解释为 1950。为避免模糊，请使用四位数字的年份。 3) MONTH

返回代表指定日期月份的整数。 语法

MONTH ( date ) 参数

date

返回 datetime 或 smalldatetime 值或日期格式字符串的表达式。仅对 1753 年 1 月 1 日后的日期使用 datetime 数据类型。

4) CAST 和 CONVERT

将某种数据类型的表达式显式转换为另一种数据类型。CAST 和 CONVERT 提供相似的功能。 语法 使用 CAST：

CAST ( expression AS data_type ) 使用 CONVERT：

CONVERT (data_type[(length)], expression [, style]) 参数

expression

是任何有效的 Microsoft? SQL Server? 表达式。有关更多信息，请参见表达式。

data_type

目标系统所提供的数据类型，包括 bigint 和 sql_variant。不能使用用户定义的数据类型。有关可用的数据类型的更多信息，请参见数据类型。

length

nchar、nvarchar、char、varchar、binary 或 varbinary 数据类型的可选参数。

style

日期格式样式，借以将 datetime 或 smalldatetime 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）；或者字符串格式样式，借以将 float、real、money 或 smallmoney 数据转换为字符数据（nchar、nvarchar、char、varchar、nchar 或 nvarchar 数据类型）。

SQL Server 支持使用科威特算法的阿拉伯样式中的数据格式。

在表中，左侧的两列表示将 datetime 或 smalldatetime 转换为字符数据的 style 值。给 style 值加 100，可获得包括世纪数位的四位年份 (yyyy)。

(1) 查询计算机学院的学生的学号、姓名、年龄。（要求命令列，并使用两种方法计算年龄）。

查询原S表中数据： select *

from S

如图1- 1所示：

图1- 1原S表中数据

查询如题要求SQL语句：

SELECT SNO,SNAME,2014-YEAR(SBIRTHDAY)AS AGE FROM S

WHERE Sdept='计算机学院' 如图1- 2所示：

图1- 2查询如题要求

(2) 查询课程号为10001的课程的原始成绩和调整成绩，其中调整成绩=原始成绩+5分。

原表sc中的数据为： select * from sc

如图1- 3所示：

图1- 3原表sc中的数据

查询如题要求的SQL语句：

select grade as 原始成绩,grade+5 as 调整成绩 from SC

where cno='10001' 如图1- 4所示：

图1- 4查询如题要求

(3) 查询所有学生的学号、姓名和出生日期（要求出生日期只显示年份和月份）。

SQL语句：

select sno,sname,datename(yy,sbirthday)+'-'+datename(mm,sbirthday)as 出生年月 from S

如图1- 5所示：

图1- 5查询如题要求

(4) 查询7月份出生的学生

查询S表中所有学生信息： select * from s

如图1- 6所示：

图1- 6S表中所有学生信息

查询7月份出生的学生信息： select * from s

where month(sbirthday)='7' 如图1- 7所示：

图1- 7查询7月份出生的学生信息

(5) 查询所有的课程信息，在查询结果中增加关于学校的说明列“石家庄经济学院本部”。

SQL语句：

select *,cast('石家庄经济学院本部' as char)as school from c

如图1- 8所示：

图1- 8查询如题要求

2. Top练习

1) 使用 TOP 和 PERCENT 限制结果集

TOP 子句限制返回到结果集中的行数。 TOP n [PERCENT]

n 指定返回的行数。如果未指定 PERCENT，n 就是返回的行数。如果指定了 PERCENT，n 就是

返回的结果集行的百分比，如下所示：

TOP 120 /*Return the top 120 rows of the result set. */ TOP 15 PERCENT /* Return the top 15% of the result set. */.

如果一个 SELECT 语句既包含 TOP 又包含 ORDER BY 子句，那么返回的行将会从排序后的结果集中选择。整个结果集按照指定的顺序建立并且返回排好序的结果集的前 n 行。 限制结果集大小的另一种方法是在执行一个语句之前执行 SET ROWCOUNT n 语句。SET ROWCOUNT 与 TOP 的不同之处在于：

图7- 2所有课程的被选情况

（3） 查询所有学生的选课情况和所有课程的被选情况。

SQL语句：

select s.sno,s.sname,c.cno,c.cname,sc.grade

from (s left join sc on(s.sno=sc.sno)) right join c on(sc.cno=c.cno) order by s.sno,c.cno 如图7- 3所示：

图7- 3所有学生的选课情况和所有课程的被选情况

8.嵌套查询与子查询 说明：

⑴ 至多可以嵌套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课程的平均分，以及各学生本门课成绩与平均分的差值

SQL语句: select sc.*,

grade-(select avg(isnull(grade,0)) from sc

where cno='10001')as 平均分差值, (

select avg(isnull(grade,0)) from sc

where cno='10001')as 平均成绩 from sc

where cno='10001' 如图8- 1所示：

图8- 1如题要求

(2) 查询最低分低于30分的学生信息。

查询所有学生的最低分： select s.sno,min(sc.grade) from s,sc

where s.sno=sc.sno group by s.sno 如图8- 2所示：

图8- 2查询所有学生的最低分

查询最低分低于30分： select s.sno,min(sc.grade) from s,sc

where s.sno=sc.sno group by s.sno

having min(sc.grade)<30 如图8- 3所示：

图8- 3查询最低分低于30分

查询低于30分的学生信息： select * from s

where sno in(select s.sno from s,sc

where s.sno=sc.sno group by s.sno

having min(sc.grade)<30) 如图8- 4所示：

图8- 4查询低于30分的学生信息

(3) 查询有补考情况的课程名称

SQL语句： select cname from c

where cno in(select cno from sc where grade<60) 如图8- 5所示：

图8- 5查询有补考情况的课程名称

(4) 查询至少有两门课超过85分的学生的基本信息

查询至少有两门课超过85分的学号： select sno from sc

where grade>85 group by sno

having count(*)>=2 如图8- 6所示：

图8- 6查询至少有两门课超过85分的学号

查询至少有两门课超过85分的学生的基本信息 select * from s

where sno in(select sno from sc

where grade>85 group by sno having count(*)>=2) 如图8- 7所示：

图8- 7查询至少有两门课超过85分的学生的基本信息

(5) 显示平均分超过85分的学生的学号、姓名、学院

查询每个学生的平均分：

select sno,avg(grade)as 平均成绩 from sc

group by sno 如图8- 8所示：

图8- 8查询每个学生的平均分

显示平均分超过85分的学生的学号、姓名、学院 select sno,sname,sdept from s

where sno in(select sno from sc group by sno having avg(isnull(grade,0))>85) 如图8- 9所示：

图8- 9显示平均分超过85分的学生的学号、姓名、学院

(6) 查询至少一门课（两门课）成绩不及格的学生信息

查询至少一门课成绩不及格的学生信息： select * from s

where sno in(select sno from sc

where grade<60 group by sno having count(*)>=1) 如图8- 10所示：

图8- 10至少一门课成绩不及格的学生信息

查询至少两门课成绩不及格的学生信息： select * from s

where sno in(select sno from sc where grade<60 group by sno having count(*)>=2) 如图8- 11所示：

图8- 11至少两门课成绩不及格的学生信息

(7) 被全部学生都选修了的课程

SQL语句：

SELECT Cname FROM C

WHERE Cno in( SELECT C.Cno FROM SC , C

WHERE SC.Cno=C.Cno AND C.Cno=( select Cno FROM SC

GROUP BY Cno

having ( count(Sno)=(select count(distinct Sno) from S)))) 运行结果如图8- 12所示：

图8- 12被全部学生都选修了的课程

原表sc中的数据： select * from sc

order by cno

如图8- 13图8- 14所示：

(8) 被全部学生都不选修的课程SQL语句：

SELECT cno,Cname

图8- 13原表sc中的数据1

图8- 14原表sc中的数据2

FROM C

WHERE Cno in ( select Cno FROM C WHERE Cno NOT IN ( SELECT Cno

FROM SC )) 如图8- 15所示：

原表sc中的数据： select * from sc

order by cno

如图8- 16所示：

8- 15被全部学生都不选修的课程

图8- 16原表sc中的数据

图

本文来源：https://www.bwwdw.com/article/5ad7.html