1、聚合函数
- 聚合函数作用于一组数据,并对一组数据返回一个值。
- 聚合函数类型:AVG()、SUM()、MAX()、MIN()、COUNT()。
- 不能在 WHERE 子句中使用聚合函数。
SELECT AVG(salary), MAX(salary), MIN(salary),SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';
SELECT COUNT(*)
FROM employees;
# 107
-- COUNT(expr) 返回expr不为空的记录总数。
SELECT COUNT(commission_pct)
FROM employees;
# 35
- 可以对数值型数据使用AVG 和 SUM 函数。
- 可以对任意数据类型的数据使用 MIN 和 MAX 函数。
- COUNT(*)返回表中记录总数,适用于任意数据类型。
- COUNT(expr) 返回expr不为空的记录总数。
- 用count(*),count(1),count(列名)对于MyISAM引擎的表是没有区别的,引擎内部有一计数器在维护着行数。
- 不能使用 count(列名)来替代 count() , count() 是 SQL92 定义的标准统计行数的语法,跟数据库无关,跟 NULL 和非 NULL 无关。
- count(*)会统计值为 NULL 的行,而 count(列名)不会统计此列为 NULL 值的行。
2、GROUP BY
- 可以使用GROUP BY子句将表中的数据分成若干组
- WHERE一定放在FROM后面
- 包含在 GROUP BY 子句中的列不必包含在SELECT 列表中
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
SELECT AVG(salary)
FROM employees
GROUP BY department_id;
# 使用多个列分组
SELECT department_id dp_id, job_id, SUM(salary)
FROM employees
GROUP BY dp_id, job_id;
- 使用WITH ROLLUP 关键字之后,在所有查询出的分组记录之后增加一条记录,计算查询出的所有记录的总和,即统计记录数量。
- ROLLUP和ORDER BY是互相排斥的。当使用ROLLUP时,不能同时使用ORDER BY子句进行结果排序。
SELECT department_id, AVG(salary)
FROM employees
WHERE department_id > 60
GROUP BY department_id WITH ROLLUP;
3、HAVING
- HAVING子句用于过滤分组
- 行已经被分组。
- 使用了聚合函数
- 满足HAVING 子句中条件的分组将被显示。
- HAVING 不能单独使用,必须要跟 GROUP BY 一起使用。
- 可以在一个查询里面同时使用 WHERE 和 HAVING。
- 包含分组统计函数的条件用 HAVING,普通条件用 WHERE。
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 1000;
WHERE和HAVING的对比
- WHERE 先筛选数据再关联,执行效率高。不能使用分组中的计算函数进行筛选。
- HAVING 可以使用分组中的计算函数在最后的结果集中进行筛选,执行效率较低。
区别1:
- WHERE 在 GROUP BY 之前,可以直接使用表中的字段作为筛选条件,但不能使用分组中的计算函数作为筛选条件。
- HAVING 必须要与 GROUP BY 配合使用,且在 GROUP BY 之后,可以把分组计算的函数和分组字段作为筛选条件,对分组的结果集进行筛选,这是 WHERE 无法完成的。
区别2:
- 在关联查询中,WHERE 比 HAVING 更高效。当数据量特别大的时候,运行效率会有很大的差别。
- WHERE 是先筛选后连接,用一个筛选后的较小数据集和关联表进行连接,这样占用的资源比较少,执行效率也比较高。
- HAVING 是先连接后筛选。用未被筛选的数据集进行关联,然后对这个大的数据集进行筛选,这样占用的资源就比较多,执行效率也较低。
4、练习
#1.where子句可否使用组函数进行过滤?
NO
#2.查询公司员工工资的最大值,最小值,平均值,总和
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees;
#3.查询各job_id的员工工资的最大值,最小值,平均值,总和
SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id;
# 4.选择具有各个job_id的员工人数
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;
# 5.查询员工最高工资和最低工资的差距(DIFFERENCE)
SELECT MAX(salary), MIN(salary), MAX(salary) - MIN(salary) AS DIFFERENCE
FROM employees;
# 6.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;
# 7.查询所有部门的名字,location_id,员工数量和平均工资,并按平均工资降序
SELECT d.department_id, d.department_name, d.location_id, COUNT(*), AVG(salary) avg_sal
FROM departments d
LEFT JOIN employees e
ON e.department_id = d.department_id
GROUP BY department_id
ORDER BY avg_sal DESC;
SELECT department_name, location_id, COUNT(employee_id), AVG(salary) avg_sal
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id
GROUP BY department_name, location_id
ORDER BY avg_sal DESC;
# 8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT job_id, department_name, MIN(salary)
FROM departments d
LEFT JOIN employees e
ON e.department_id = d.department_id
GROUP BY department_name, job_id