目录

聚合函数介绍

什么是聚合函数

聚合函数类型

聚合函数语法​

聚合函数不能嵌套调用。

AVG / SUM ：只适用于数值类型的字段（或变量）

拓展：字符串求总和：MySQL中 0 Order 直接报无效的数字

MAX / MIN :适用于数值类型、字符串类型、日期时间类型的字段（或变量）

字符串可以比较 大小

COUNT：

计算表中有多少条记录，如何实现？

如何需要统计表中的记录数，使用COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高呢？

其他聚合函数：

GROUP BY 的使用

单列分组

实例1：查询各个部门的平均工资，最高工资

实例2：查询各个job_id的平均工资

实例3：查询各个department_id,job_id的平均工资

注意：错误写法

结论：

 使用多个列分组

案例：查询各个部门的平均工资，按照平均工资升序排列

说明：

HAVING

过滤分组：HAVING子句 

 案例：查询 个个部门的最大工资，并且最大工资大于 1000的 部门 

非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数：

 要求：

案例：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息

结论：

WHERE 与 HAVING 的对比

引出问题：为什么where 的效率要比 having的效率高？ 看SQL的执行原理

SQL底层执行原理

查询的结构

SQL语句的执行过程：

WHERE 为什么不包含聚合函数的过滤条件？

为什么where 的效率要比 having的效率高？

聚合函数介绍

• 什么是聚合函数

•         聚合函数作用于一组数据，并对一组数据返回一个值。

• 

• 聚合函数类型

  • AVG()
  • SUM()
  • MAX()
  • MIN()
  • COUNT() 

• 聚合函数语法

• 聚合函数不能嵌套调用。

  • 比如不能出现类似“AVG(SUM(字段名称))”形式的调用。

AVG / SUM ：只适用于数值类型的字段（或变量）

语句

SELECT AVG(salary),SUM(salary),AVG(salary) * 107FROM employees;

结果

拓展：字符串求总和：MySQL中 0 Order 直接报无效的数字

语句

#如下的操作没有意义SELECT SUM(last_name),AVG(last_name),SUM(hire_date)FROM employees;

结果

MAX / MIN :适用于数值类型、字符串类型、日期时间类型的字段（或变量）

语句

SELECT MAX(salary),MIN(salary)FROM employees;

结果

字符串可以比较 大小

语句

SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date)FROM employees;

结果

COUNT：

• ① 作用：计算指定字段在查询结构中出现的个数（不包含NULL值的）
• ② 注意：计算指定字段出现的个数时，是不计算NULL值的。
• ③ 公式：AVG = SUM / COUNT   AVG SUM 默认也不计算 null

① 作用：计算指定字段在查询结构中出现的个数（不包含NULL值的）

语句

SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*)FROM employees ;

结果

② 注意：计算指定字段出现的个数时，是不计算NULL值的。

计算表中有多少条记录，如何实现？

• 方式1：COUNT(*)  
• 方式2：COUNT(1) 常数
• 方式3：COUNT(具体字段) : 不一定对！

SELECT COUNT(commission_pct)FROM employees;SELECT commission_pctFROM employeesWHERE commission_pct IS NOT NULL;

③ 公式：AVG = SUM / COUNT   AVG SUM 默认也不计算 null 

SELECT AVG(salary),SUM(salary)/COUNT(salary),AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),SUM(commission_pct) / 107FROM employees;--------------------------------#需求：查询公司中平均奖金率#错误的！SELECT AVG(commission_pct)FROM employees;--------------------------------#正确的：SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)),AVG(IFNULL(commission_pct,0))FROM employees;--------------------------------

 

如何需要统计表中的记录数，使用COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高呢？

• 如果使用的是MyISAM 存储引擎，则三者效率相同，都是O(1)
• 如果使用的是InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1)> COUNT(字段)

其他聚合函数：

        如：方差、标准差、中位数

GROUP BY 的使用

单列分组

实例1：查询各个部门的平均工资，最高工资

#需求：查询各个部门的平均工资，最高工资SELECT department_id,AVG(salary),SUM(salary)FROM employeesGROUP BY department_id

结果

实例2：查询各个job_id的平均工资

#需求：查询各个job_id的平均工资SELECT job_id,AVG(salary)FROM employeesGROUP BY job_id;

结果

实例3：查询各个department_id,job_id的平均工资

#需求：查询各个department_id,job_id的平均工资#方式1：SELECT department_id,job_id,AVG(salary)FROM employeesGROUP BY  department_id,job_id;#方式2：SELECT job_id,department_id,AVG(salary)FROM employeesGROUP BY job_id,department_id;

结果

注意：错误写法

        MySQL出现数据，但是数据不对   ORDER 报不是GROUP BY 表达式

#错误的！  MySQL出现数据，但是数据不对   ORDER 不是GROUP BY 表达式SELECT department_id,job_id,AVG(salary)FROM employeesGROUP BY department_id;

结论：

• 1：SELECT 中出现的 非组函数的字段必须声明在GROUP BY 中。反之，GROUP BY中声明的字段可以不出现在SELECT中。
• 2：GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面
• 3：MySQL中 GROUP BY 中 使用 WITH ROLLUP
  • 把所有的组分完之后，在把所有组当成一个组，在进行一次分组

SELECT department_id,AVG(salary)FROM employees# 把所有的组分完之后，在把所有组当成一个组，在进行一次分组GROUP BY department_id WITH ROLLUP; 

 使用多个列分组

案例：查询各个部门的平均工资，按照平均工资升序排列

#需求：查询各个部门的平均工资，按照平均工资升序排列SELECT department_id,AVG(salary) avg_salFROM employeesGROUP BY department_idORDER BY avg_sal ASC;

结果

说明：

        当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。  （最后一组数据无法进行排序）

#错误的：SELECT department_id,AVG(salary) avg_salFROM employeesGROUP BY department_id WITH ROLLUPORDER BY avg_sal ASC;

HAVING

过滤分组：HAVING子句 

        1. 行已经被分组。
        2. 使用了聚合函数。
        3. 满足HAVING 子句中条件的分组将被显示。
        4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

 案例：查询 个个部门的最大工资，并且最大工资大于 1000的 部门 

SELECT department_id,MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary) > 10000;

非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数：

SELECT employee_id emp_id,last_name AS lname,department_id "部门id",salary * 12 AS "annual sal"FROM employees;

结果

 要求：

1、如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。

2、HAVING 必须声明在 GROUP BY 的后面。

3、开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。

案例：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息

#练习：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息#方式1：推荐，执行效率高于方式2.SELECT department_id,MAX(salary)FROM employeesWHERE department_id IN (10,20,30,40)GROUP BY department_idHAVING MAX(salary) > 10000;#方式2：SELECT department_id,MAX(salary)FROM employeesGROUP BY department_idHAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);

结论：

• 当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
• 当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。

WHERE 与 HAVING 的对比

1. 从适用范围上来讲，HAVING的适用范围更广。 
2. 如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING

	优点	缺点
WHERE	先筛选数据再关联，执行效率高	不能使用分组中的计算函数进行筛选
HAVING	可以使用分组中的计算函数	在最后的结果集中进行筛选，执行效率较低

引出问题：为什么where 的效率要比 having的效率高？ 看SQL的执行原理

SQL底层执行原理

查询的结构

#sql92语法：
SELECT ....,....,....(存在聚合函数)
FROM ...,....,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件、不可以使用别名
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

#sql99语法：
SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件 
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件、不可以使用别名
GROUP BY ...,....AND/OR  不包含聚合函数的过滤条件
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....

#其中：
（1）from：从哪些表中筛选
（2）on：关联多表查询时，去除笛卡尔积
（3）where：从表中筛选的条件
（4）group by：分组依据
（5）having：在统计结果中再次筛选
（6）order by：排序
（7）limit：分页

SQL语句的执行过程：

FROM ...,...-> ON -> (LEFT/RIGNT  JOIN) -> WHERE -> GROUP BY ->HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

WHERE 为什么不包含聚合函数的过滤条件？

 从执行过程来看 可以先前引用，但是没有办法向下引用

为什么where 的效率要比 having的效率高？

        由SQL的执行顺序可知，where的执行在 having的前面，现在where 中过滤一些条件，分组时效率高很多，提升很大，如果此时 过滤条件写在 having 中 先分组后进行条件筛选，很多数据都是不需要的数据，无用功