数据库系统原理与设计（万常选版）总复习CH3 SQL语言_2-复习.pptVIP

3.5　聚合查询 SQL查询提供了丰富的数据分类、统计和计算的功能 统计功能通过聚合函数来实现 分类功能通过分组子句来实现 统计和分组结合在一起实现丰富的查询功能 3.5.1 聚合函数 3.5.2 分组聚合 3.5.3 复杂查询 3.5.1 聚合函数 SQL提供的聚合函数(aggregate function)包括： count( [DISTINCT | ALL] {* | 列名} )：统计关系的元组个数或一列中值的个数； sum( [DISTINCT | ALL] 列名 )：统计一列中值的总和(此列必须为数值型)； avg( [DISTINCT | ALL] 列名 )：统计一列中值的平均值(此列必须为数值型)； max( [DISTINCT | ALL] 列名 )：统计一列中值的最大值； min( [DISTINCT | ALL] 列名 )：统计一列中值的最小值。 指定DISTINCT谓词，表示在计算时首先消除列名取重复值的元组，然后再进行统计 指定ALL谓词或没有DISTINCT谓词，表示不消除列名取重复值的元组 3.5.1 聚合函数 [例3.47] 查询学生总人数。 SELECT count(*) FROM Student 或 SELECT count(*) 学生人数 FROM Student [例3.48] 查询所有选课学生的人数。 SELECT count(studentNo) 学生人数 FROM Score 查询结果是40 由于一个学生可以选修多门课程，学号存在重复，为消除重复的元组，使用DISTINCT短语，将查询修改为： SELECT count(DISTINCT studentNo) 学生人数 FROM Score 查询结果为10 3.5.1 聚合函数 [例3.49] 查询学号为“0800005”同学所选修课程的总学分。 SELECT sum(creditHour) 总学分 FROM Score a, Course b WHERE studentNo=0800005 AND a.courseNo=b.courseNo 在聚合函数遇到空值时，除count(*)外所有的函数皆跳过空值，只处理非空值。 3.5.2 分组聚合 在SQL查询中，往往需要对数据进行分类运算(即分组运算) SQL通过GROUP BY和HAVING子句实现分组运算 GROUP BY对查询结果按某一列或某几列进行分组，值相等的分为一组； HAVING对分组的结果进行选择，仅输出满足条件的组。该子句必须与GROUP BY子句配合使用 3.5.2 分组聚合 [例3.50] 查询每个同学的选课门数、平均分和最高分。 SELECT studentNo, count(*) 门数, avg(score) 平均分, max(score) 最高分 FROM Score GROUP BY studentNo 结果按学号StudentNo分组，将具有相同StudentNo值的元组作为一组 然后对每组进行相应的计数、求平均值和求最大值 3.5.2 分组聚合 [例3.51] 查询平均分在80分以上的每个同学的选课门数、平均分和最高分。 SELECT StudentNo, count(*) 门数, avg(score) 平均分, max(score) 最高分 FROM Score GROUP BY StudentNo HAVING avg(score)=80 3.5.2 分组聚合 [例3.52] 查询成绩最高分的学生的学号、课程号和相应成绩 SELECT studentNo, courseNo, score FROM Score WHERE score=( SELECT max(score) FROM Score ) 聚合函数可直接用在HAVING子句中(如例3.51)，也可用于子查询中(如例3.52)，但在WHERE子句中不可以直接使用聚合函数。如下语句是不正确的： SELECT * FROM Score WHERE score=max(score) 3.5.3 复杂查询 SQL语句可构造非常复杂的查询，可将选择、投影、连接、子查询、分组聚合等操作混合使用，从而完成几乎所有的查询操作 [例3.54] 查询至少选修了28个学分的同学的学号、姓名以及所选修各门课程的课程名、成绩和学分，并按学号排序输出 SELECT a.studentNo, studentName, co