chapter05_聚合与分组1.pptVIP

第五章 聚合函数与分组 回顾 数据库应用程序与持久层概念 JDBC概述 JDBC使用步骤 使用Statement和PreparedStatement 使用结果集 数据分组＆汇总概述 采用汇总和分组数据的方法，可以执行更多的数据操作技术 查询某个列的最大值或平均值，比如，一个学生在历次考试中的最高得分 分组查询列的最大值或者平均值，比如，我们可以根据参加的考试将在表中的行分组，然后查找每次考试的最低分、最高分和平均分 可以在应用程序代码中实现这些功能。在使用基本的SQL语句从数据库中抽取数据后，然后在应用程序代码中进行汇总。但是，在网络环境中，如果从数据库中返回成千上万条记录，仅仅是为了得到一个汇总值，这样的效率显然很差的。使用SQL中的聚合函数在数据库中计算，最后通过网络返回单一的值，无疑是更好的解决方案 常用的聚合函数 SQL提供几种方法来同时查找多行记录，并且抽取汇总数据。大部分方法与使用聚合函数有关 常用的聚合函数如下： COUNT()－－－行计数 SUM()－－－－获取单个列的合计值 AVG()－－－－计算某个列的平均值 MAX()－－－－计算列的最大值 MIN()－－－－－计算列的最小值 执行行和列计数 语法 COUNT()示例 返回表Employees中记录的数目 返回一个未命名的单行单列值，表示Employees表中有多少非NULL的Region列值。 排除重复值，只对重复值计数一次 返回列合计值 语法 不能使用SUM(*) 可以使用SUM(ALL 列)、SUM(DISTINCT 列) Sample DEMO 获取所有课程的总学分 获取列平均值 语法（可以使用ALL/DISTINCT忽略NULL值） Sample DEMO 计算StudentExam表中的StudentID为10的学生的考试平均分 注意事项： 如果想要获得平均值的列是一个整数数据类型，那么获得的平均值可能是不精确的。为避免上述问题发生，在计算平均值前，我们需要把该列转换为浮点类型 返回最大值＆最小值 语法 DEMO 获取某次考试的最高分和最低分 数据分组 Problem 如果要计算某个数据库的产品表Products中每个供应商的产品单价总计值，那么我们需要为每个供应商写如下的查询 Solution 执行查询后，列A的值被列举出来，被创建的组数目与列A的值的数目匹配。例如，如果有40条记录，而列A有15个不同的值，那么就创建15个组。这意味着将返回15行，每行由一个列A值和应用于该组的聚合函数的计算结果组成 分组查询注意事项 错误的做法 DEMO 执行方式为： 先将将考试分数小于等于70的行过滤掉，然后具有相同StudentID列值的行分组在一起，最后执行COUNT函数，计算余下的行中每个学生通过考试的次数 使用having子句 假设我们不想通过数据表中的实际值，而是通过聚合函数的结果来过滤查询结果集。例如，我们想要查询哪些学生考试成绩特别好或者特别差，我们不想基于某个特定分数来过滤查询结果，而是想基于所有考试的平均分来过滤。我们也许会简单认为在WHERE子句中添加如下的过滤条件就可以了： 解决方法：使用having子句 DEMO 如果想同时在聚合函数计算之前和之后过滤数据，可以使用HAVING与WHERE协作完成。例如，假如要将上述的查询结果限制在考试号ExamID为5、8和11的考试上，可以使用如下查询： WHERE过滤条件在聚合函数计算之前起作用，所以ExamID不是5、8或者11的行将被完全忽略。聚合函数计算之后，HAVING子句将返回的行限制为平均成绩小于50或者大于70的学生 目标 掌握如何在SQL中对数据进行汇总 掌握如何在SQL中对数据进行分组 SELECT COUNT(计数规范) FROM 表名; 计数指定列的所有唯一非空值行。 DISTINCT 列 计数指定列的所有的非空值行。如果仅仅是指定列而不带ALL或者DISTINCT，这是默认操作 ALL 列 计数所有选择的行，包括NULL值 * SELECT COUNT(计数规范) as 列名 FROM 表名; SELECT COUNT(ReportsTo), FirstName, LastName FROM Employees; X SELECT COUNT(*) AS EmployeeCount FROM Employees; SELECT COUNT(ALL Region) FROM Employees; SELECT COUNT(DISTINCT Region) FROM Employees; SELECT COUNT(*) AS NumberOfExams,