MySQL中的聚合函数_count()+分组函数_group...by+过滤条件_having

常见的几个聚合函数

1、avg():求平均数

2、sum():求和

字符串求和、求平均数，没有意义、不合理
avg():求平均数、sum():求和，只适用在数值类型 ！！！

mysql> SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
    -> FROM employees
    -> WHERE job_id LIKE '%REP%';
+-------------+-------------+-------------+-------------+
| AVG(salary) | MAX(salary) | MIN(salary) | SUM(salary) |
+-------------+-------------+-------------+-------------+
| 8272.727273 |    11500.00 |     6000.00 |   273000.00 |
+-------------+-------------+-------------+-------------+
1 row in set (0.23 sec)
1
2
3
4
5
6
7
8
9

3、max():求最大值

4、min():求最小值

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

mysql> SELECT MIN(hire_date), MAX(hire_date)
    -> FROM employees;
+----------------+----------------+
| MIN(hire_date) | MAX(hire_date) |
+----------------+----------------+
| 1987-06-17     | 2000-04-21     |
+----------------+----------------+
1 row in set (0.10 sec)
1
2
3
4
5
6
7
8

5、count(*)返回记录总数

1、适用于任意数据类型
2、count(expr) 返回expr不为空的记录总数

问题：用count(*)，count(1)，count(列名)谁好呢?
count(1)和count(100)都没啥区别
count(*) 和 count(1) 没啥区别，但好于具体的count(列名)。

不要使用 count(列名)来替代 count(*) 
说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
1
2
3
4
5
6

案例：查询公司的平均奖金率

错误的：原因：聚合函数会自动过滤null值，而求平均数要用总数除以每一个人，包括null（没有奖金的）
select avg(commission_pct) 
from employees;

正确的：
select avg(ifnull(commission_pct,0))
或者sum(commission_pct)/count(ifnull(commission_pct,0)),
from employees;
1
2
3
4
5
6
7
8
9
10

分组函数

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

明确：WHERE一定放在FROM后面

案例：查询各个部门的平均薪资和最高工资
select department_id,avg(ifnull(salary,0)),max(salary)
from employees
group by department_id;
1
2
3
4

二级分组

select department_id,job_id,max(salary)
from employees
group by department,job_id;
1
2
3

总结：

• 1、二级分组，直接加个逗号，跟着后面写就完事了
• 2、select 中出现的 非组函数，必须声明在 group by 中
• 使用 with rollup 关键字之后，在所有查询出的分组记录之后增加一条记录， 该记录计算查询出的所有记录的总和，即统计记录数量。
• 但是使用了with rollup后就不可以使用排序 order by

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;
1
2
3
4

having的使用

案例：查询各个部门的最高工资比10000高的部门信息
select department_id,max(salary)
from employees
group by department_id
having max(salary)>10000 ;
1
2
3
4
5

总结：
1、前提是使用了聚合函数
2、having 不能单独使用，必须要跟 group by 一起使用
3、having 用来过滤聚合函数的
4、having 的效率比where 低

where 和 having 的对比

开发中的选择：
WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用
统计函数的条件用 HAVING，普通条件用WHERE。