Day41.聚合函数(组函数)、GROUP BY分组、HAVING、查询总结

目录

聚合函数(组函数)

GROUP BY

查询总结

---

聚合函数(组函数)

• 什么是聚合函数

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

聚合函数类型 :  AVG()、 SUM()、  MAX()、 MIN()、 COUNT()

• 聚合函数语法

注：Mysql中聚合函数无法嵌套使用

• AVG和SUM函数 (平均数、总和)

可以对数值型数据使用AVG 和 SUM 函数。

• MIN和MAX函数 (最小、最大值)

可以对任意数据类型的数据使用 MIN 和 MAX 函数。

• COUNT函数

COUNT(*)返回表中记录总数，适用于任意数据类型。

COUNT(expr) 返回expr不为空的记录总数。

#需求：查询公司中平均奖金率
#错误的！
SELECT AVG(commission_pct)    #AVG()与COUNT(列)不会统计空值
FROM employees;

#正确的：
SELECT SUM(commission_pct) / COUNT(*),
AVG(IFNULL(commission_pct,0))
FROM employees;

问题1：用 count(*) ， count(1) ， count( 列名 ) 谁好呢 ?

其实，对于 MyISAM 引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。

Innodb 引擎的表用 count(*),count(1) 直接读行数，复杂度是 O(n) ，因为 innodb 真的要去数一遍。但好 于具体的count( 列名 )

问题2：能不能使用 count( 列名 ) 替换 count(*)?

不要使用 count( 列名 ) 来替代 count(*) ， count(*) 是 SQL92 定义的标准统计行数的语法，跟数 据库无关，跟 NULL 和非 NULL 无关。

说明：count(*) 会统计值为 NULL 的行，而 count( 列名 ) 不会统计此列为 NULL 值的行。

• GROUP BY

可以使用GROUP BY子句将表中的数据分成若干组

​​​​​​​#分组时可以有多个分组字段

出现在SELECT列表中的列都应该包含在 GROUP BY子句中 ，否则该列没有参考价值

• 多个列分组

• GROUP BY 中使用 WITH ROLLUP

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


#分组时可以有多个分组字段
-- 每一个人的部门编号 工作编号 薪资
select ename,did,job_id from t_employee; 
-- 统计 不同部门不同工作的 平均薪资 最高薪资 人数
select e.`did`,e.`job_id`,avg(salary),max(salary),count(*)
from t_employee e
group by e.`did`,e.`job_id`;

HAVING(替代where)

由于不能在 WHERE 子句中使用聚合函数

过滤分组： HAVING 子句

1. 行已经被分组。

2. 使用了聚合函数。

3. 满足 HAVING 子句中条件的分组将被显示。

4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。

 

 WHERE 与 HAVING 

开发中的选择：

WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING 。包含分组 统计函数的条件用 HAVING ，普通条件用 WHERE 。这样，我们就既用了 WHERE 条件的高效快速，又发 挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很 大的差别。

查询总结

• SELECT 语句的完整结构

#4. SQL底层执行原理
#4.1 SELECT 语句的完整结构
/*

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

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

*/

SELECT执行顺序  

#4.2 SQL语句的执行过程：
#FROM ...,...-> ON -> (LEFT/RIGNT  JOIN) -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> 
# ORDER BY -> LIMIT