数据库原理与应用

1. 数据库原理与应用 第六章　数据的高级管理——学生数据的检索统计与汇总

2. 学习目标

3. 任务的提出 晓灵的好朋友学习委员莉莉提出了个问题：“晓灵，快替我查查上学期咱们班‘计算机网络技术’课程平均分是多少呀。正如大家说的这样，在实际情况中我们需要查询的数据可能不是表中的原始数据，需要对原有的数据进行计算，就好像莉莉需要的课程平均分。还有的数据分布在不同的表中，我们需要联合多个表实现数据的查询。有时我们还需要在对数据进行简单查询的基础上对数据进行进一步的分组汇总和排序，以便提炼我们需要的内容，这都是对数据的复杂查询。好，下面我们就一起来看看数据联合及分组汇总的方法。”

4. 6.2 数据的排序 • 通过使用Order by 子句就可以实现对结果集中行的顺序进行重新排序。 语句的语法如下： SELECT select_list FROM table_source WHERE search_condition ORDER BY order_expression [ASC|DESC] 主要参数说明： order_expression：排序所依据的列名称 ASC：升序排列，如果order by 语句后不明确标识则默认为升序 DESC：降序排列

5. 使用ORDER BY子句时注意 • ORDER BY子句中指定的列并不一定要出现在select 后的选择列表中。 • ORDER BY子句中指定的列不能超过8060字节。 • ORDER BY子句后可以指定多个列，那么先按照最左边的列排序，如果列的取值相同，再按照第二列的值排序，依次类推。 • 可以为不同的列设置不同的排序方式。

6. 6.3 数据的分组和汇总 6.3.1 使用TOP关键字列出前n个记录 使用TOP关键字可以列出结果集中前n个或前n%的记录， 1. 使用该关键字的部分语法为： SELECT  [ TOP n [ PERCENT ] [ WITH TIES ] ] < select_list > 2. 主要参数说明 TOP n：前n个 TOP n [ PERCENT ]：前百分之n个 WITH TIES：包含值相等的记录

7. 【任务6.3】查询学习成绩表中编号为“k008”课程前3名学生的成绩。【任务6.3】查询学习成绩表中编号为“k008”课程前3名学生的成绩。 • Select top 3 Sid,Kcid,gradenum • From grade • Where kcid=’k008’ • Order by gradenum desc

8. 使用TOP关键字应注意如下事项： • TOP n 关键字只是取结果集中前n条记录，其本身并没有将结果集进行排序的功能。所以如果需要查看某列n个最大或n个最小的行信息，需要在select语句中使用order by子句对结果集进行升序或降序排序，在使用TOP n关键字，才能显示出需要的结果。 • TOP后的n 应取无符号的整数，不能使用小数。 • 如果使用TOP n PERCENT，表示取结果集中的前n%条记录，如果n%生成了小数，则SQL Server将这个数取整。 • 如果使用了with ties子句，表示结果集中包含那些值相同的记录。所以结果集中的记录数可能会超过 n 值。 • ☺注意：只有在使用order by 时，才能使用 with ties

9. 6.3.2 使用聚集函数 • 常用的聚集函数有：

10. 【任务6.4】查询学习成绩表的所有课程中的最高分。【任务6.4】查询学习成绩表的所有课程中的最高分。 • Select max (gradenum) • From grade

11. 【任务6.5】查询学习成绩表中‘k009’号课程的最高分。【任务6.5】查询学习成绩表中‘k009’号课程的最高分。 • Select max (gradenum) • From grade • Where kcid=’k009’

12. 【任务6.6】查询学生信息表中学生的个数。 • Select count(*) from student

13. 使用聚集函数注意以下事项： • Count函数是唯一一个能用于text，ntext或image数据类型的函数 • Min和Max函数不能用于数据类型为bit的字段 • Sum和Avg函数只能用于数据类型是int，smallint，tinyint，decimal，numeric，float，real，money和smallmoney的字段。

14. 6.3.3 使用GROUP BY实现数据的分组汇总 • 【任务6.7】查询学习成绩表中各门课程的最高分。 • Select kcid, max (gradenum) • From grade • Group by kcid

15. 在使用GROUP BY子句时要注意一下几点： • Group by 子句将按照该语句指定的字段对数据进行分组，并进行汇总计算。但是只生成一条汇总数据，并不返回细节信息 • 所有在Group by子句中出现的字段，都必须出现在select语句的选择列表中 • Select语句中出现的字段，要么是聚集函数调用的列，要么出现在group by语句中，否则语句将无法运行 • 如果使用where 子句，那么where子句必须要写在Group by 语句前面。SQL Server只对满足where子句的记录进行分组汇总 • Group by子句后面可以有多个分组字段。 • 如果Group by子句后面有多个分组字段，那么分组的顺序是从右至左。

16. 任务6.9】在学习成绩表中查询所有课程平均成绩在80分以上的学生的信息。任务6.9】在学习成绩表中查询所有课程平均成绩在80分以上的学生的信息。 • Select sid, avg(gradenum) as 平均成绩 • From grade • Group by sid • Having avg(gradenum)>80

17. 注意：having子句和 where的区别：where用在from语句的后面，只有满足where条件的记录才能参与到分组计算中；having子句用在group by 语句后面，是对分组计算后的结果进行筛选。Where实现在分组之前，having实现在分组之后。Having子句可以在条件中包含聚集函数，where子句的条件则不能包含。

18. 6.3.4 使用COMPUTE和COMPUTE BY子句 • 想查看汇总结果又要查看细节信息就可以使用COMPUTE 和COMPUTE BY子句实现。 【任务6.10】在学习成绩表中查询“k008”号课程的学生考试成绩并生成平均成绩。 Select kcid, gradenum From grade Where kcid=’k008’ Compute avg(gradenum)

19. 【任务6.11】在学习成绩表中查询每门课程的学生考试成绩并生成平均成绩。【任务6.11】在学习成绩表中查询每门课程的学生考试成绩并生成平均成绩。 • Select kcid, gradenum • From grade • Order by kcid • Compute avg(gradenum) by kcid

20. 使用compute 和 compute by子句要注意以下几点： • Compute子句中引用的列必须出现在该语句的select列表中 • 必须同时使用order by 和compte by子句，这样记录才能被正确的分组显示 • 要在compute by子句后指定字段名，这样SQL Server才能决定要生成的汇总值是什么 • 出现在compte by子句后的字段一定和order by后的字段相同，或是order by的字段的子集。它们的顺序（从左到右）也必须相同，不能略过任何一个表达式

21. 6.4 数据的多表连接查询 • 表连接允许同时从两个或两个以上的表中检索数据，并指定这些表中的某个或者某些列作为连接条件。在SQL Server2000 中，可以使用两种语法形式：一种是ANSI连接语法形式，这种形式是在 FROM子句中设置连接条件；另外一种是SQL Server连接语法形式，这种形式是在 WHERE子句中设置连接条件。

22. SQL Server连接语法形式如下： • SELECT 表名.列名1[, … n] • FROM 表名1 [, … n] • Where {查询条件 AND | OR 连接条件| [, … n]}

23. 在使用连接的时候应注意以下事项： • 在表的主键（primary key）和外部键（foreign key）的基础上，指定连接的条件。 • 如果你的表中有由多个字段组成的主键，在连接表的时候，你必须在ON子句中引用所有这些字段。 • 如果连接的表必须同拥有某些字段，这些字段必须具有相同或类似的数据类型。 • 如果要连接的表中有些字段同名，则在引用这些字段的时候，必须同时指定表名。要使用下面的格式：表名.字段名 • 尽量在连接中限制表的个数。

24. 使用内连接 • 所谓内连接指的是多个表通过连接条件中共享的列的相等值进行的匹配连接（这些列是被连接的表中所共有的）。SQL Serve将只返回满足连接条件的数据。使用内连接可以把两个单独的表的数据合并，并返回一个结果集。

25. 【任务6.12】查询参加“计算机网络技术”课程（k008）考试的学生的学号、姓名、班级和成绩。【任务6.12】查询参加“计算机网络技术”课程（k008）考试的学生的学号、姓名、班级和成绩。 • Select s.sID , sName, sBanji, gradenum • From student as s inner join grade as g on s.sid=g.sid • Where g.kcid ='k008'

26. 6.4.2 使用外连接 • 左外连接和右外连接可以从两个表中返回符合连接条件的记录，同时也将返回左边或右边的表中不符合连接条件的记录，具体是哪个表由连接子句中LEFT或RIGHT关键字指定。在返回的结果集中，不满足连接条件的记录中将显示空值。当我们需要满足连接条件的记录，同时也需要其中一个表中，不满足连接条件的记录时，可以使用左外连接或右外连接。使用外连接时要注意如下事项：

27. 【任务6.14】查询所有学生的学号、姓名、班级和所参加考试的课程编号及成绩【任务6.14】查询所有学生的学号、姓名、班级和所参加考试的课程编号及成绩 • Select s.sID , sName, sBanji, kcid,gradenum • From student as s left outer join grade as g on s.sid=g.sid

28. 6.4.4 使用自连接 自连接就是使用内连接或外连接把一个表中的行同该表中的另外一些行连接起来，主要用在查询比较相同的信息。使用自连接时要注意以下事项： • 引用表的第二份拷贝时，必须使用表的别名。 • 当生成自连接时，表中的每一行都和自己比较一下，并生成重复的记录。使用where子句消除这些重复的记录。

29. 任务6.15】使用自连接的形式查询一次显示每个学生的两门课程成绩。任务6.15】使用自连接的形式查询一次显示每个学生的两门课程成绩。 • Select a.sid,a.kcid,a.gradenum,b.kcid,b.gradenum • From grade as a inner join grade as b on a.sid=b.sid • where a.kcid < b.kcid

30. 6.5 数据的嵌套查询 • 所谓嵌套查询，指的是在一个SELECT查询内再嵌入一个SELECT查询语句，我们将内嵌的查询也称为子查询，内层子查询可以作为where子句的限制条件，或者作为新曾列的值。

31. 嵌套查询要注意以下事项 • 子查询一定要用括号（）括起来 • 只要需要一个值或一系列的值，就可以用子查询代替一个表达式。可以用子查询返回一个含有多个字段的结果集，这个结果集可以替代一个表或完成一个连接语句的操作。 • 子查询不能查询包含数据类型是 text 或 image 的字段 • 子查询中也可以再包含子查询，嵌套最多可以为32层。 • 子查询中不能包含compute [by]和 into 子句

32. 【任务6.16】使用子查询，查询每个学生的平均分。【任务6.16】使用子查询，查询每个学生的平均分。 • select distinct sid , 平均分=(select avg(gradenum) from grade b where b.sid =a.sid) • from grade a

33. 任务6.17】使用平均分低于70的学生的学号。 • select distinct sid • from grade a • where (select avg(gradenum) from grade b where b.sid =a.sid)<70