【MySQL】第十部分 常用的聚合函数
文章目录
10. 常用的聚合函数
定义: 对一组数据进行汇总的函数,输入的是一组数据的集合,输出的是单个值
聚合函数不能像单行函数那样嵌套使用!!!
10.1 MIN和MAX函数
可以对任意数据类型的数据使用 MIN 和 MAX 函数。
SELECT MAX(commission_pct),MIN(commission_pct)
FROM employees;
SELECT MAX(last_name),MIN(last_name)
FROM employees;
10.2 COUNT函数
COUNT(*) : 返回表中记录总数,适用于任意数据类型,跟 NULL 和非 NULL 无关
COUNT(1) : 里面的1表示的是将每行用1去表示,一样是记录总数,里面的1可以替换任何常数,适用于任意数据类型
COUNT(列名) : 需要注意它不会统计此列为 NULL 值的行!!!
SELECT COUNT(1),COUNT(2),COUNT(*),COUNT(commission_pct),COUNT(IFNULL(commission_pct,1))
FROM employees;
10.3 AVG和SUM函数
可以对数值型数据使用AVG 和 SUM 函数。
# 公式 : AVG() = SUM() / COUNT() 可以体现出SUM(列名),AVG(列名),COUNT(列名)都是不会去统计列为NULL值的行
SELECT AVG(IFNULL(commission_pct,0)),SUM(commission_pct)/COUNT(IFNULL(commission_pct,0)),
AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct)
FROM employees;
10.4 GROUP BY
# 按照这个顺序
SELECT *
FROM TABLE
WHERE 条件
GROUP BY 分组
ORDER BY 排序
LIMIT 限制
单分组
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
多分组
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;
GROUP BY 声明的字段, 虽然SELECT可以不出现, 但是这样做会降低可读性
# 造成可读性很差,你不知道哪个部门对应哪个数据 SELECT AVG(salary) FROM employees GROUP BY department_id;
SELECT出现的非聚合函数字段,GROUP BY也必须要声明,否则会出错!!! 例如下面举例
# 一个部门有多个工种,如果以部门为单位求平均值,那么多个工种该如何显示?? SELECT department_id,job_id,AVG(salary) FROM employees GROUP BY department_id;
综上所述: 在SELECT列表中所有未包含在聚合函数中的列都应该包含在 GROUP BY子句中,需要一一对应.
10.5 HAVING
HAVING的使用规范:
当过滤条件包含聚合函数的时候使用 HAVING,并且是写在GROUP BY后面
在WHERE中不能使用聚合函数作为筛选条件
HAVING 不能单独使用,必须要跟 GROUP BY 一起使用
WHERE 和 HAVING 也不是互相排斥的,我们可以在一个查询里面同时使用 WHERE 和 HAVING
从开发中HAVING的适用范围更广, 但是当过滤条件中没有聚合函数, WHERE和HAVING都可以使用的情况下,WHERE的执行效率更高
# 查询各个部门的最高薪资并且大于12000
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)> 12000;
# 查询10,20,30,40号部门中最高薪资并且大于10000
# 写法一
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN(10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;
# 写法二 (不推荐,效率低,后面会说)
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000 AND department_id IN(10,20,30,40);
总结
以上就是今天要讲的内容,希望对大家有所帮助!!!