MySQL分组查询知识总结
- MySQL分组查询知识总结
- 一、基本语法
- 二、注意事项
- 三、示例代码
- 1. 基础使用
- 2. 添加筛选条件
- 3. 添加复杂筛选条件
- 4.按表达式或者函数分组
- 5. 按多个字段进行分组
- 6. 综合案例
MySQL分组查询知识总结
一、基本语法
SELECT
分组依据列, 分组函数()
FROM
要操作的表
WHERE
分组前筛选条件, 处理原表中存在的数据
GROUP BY
分组依据列
HAVING
分组后筛选条件, 处理分组后的结果集
ORDER BY
排序依据列 ASC / DESC;
二、注意事项
- 查询列表是分组函数和 GROUP BY 后出现的字段
- 分组函数做条件一定放在 HAVING 语句后
- 优先使用分组前筛选, 速度快
- GROUP BY 子句支持多字段分组, 表达式或函数分组
- 排序应放在整个语句的最后
三、示例代码
1. 基础使用
# 查询每个部门的平均工资
SELECT
AVG(salary)
FROM
employees;
# 分组后代码
SELECT
department_id,
AVG(salary)
FROM
employees
GROUP BY
department_id;
# 查询每个工种的最高工资
SELECT
job_id,
MAX(salary)
FROM
employees
GROUP BY
job_id;
# 查询每个位置上的部门个数降序排列
SELECT
location_id,
COUNT(*) AS count
FROM
departments
GROUP BY
location_id
ORDER BY
count DESC;
2. 添加筛选条件
/* 添加筛选条件 */
# 案例1: 查询邮箱中含有a字符的, 每个部门的平均工资
SELECT
email,
department_id,
AVG(salary) AS avg
FROM
employees
WHERE
email LIKE '%a%'
GROUP BY
department_id
ORDER BY
avg DESC;
# 案例2: 查询有奖金的每个领导手下员工的最高工资
SELECT
manager_id,
MAX(salary) AS max
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
manager_id
ORDER BY
max DESC;
3. 添加复杂筛选条件
/* 添加复杂筛选条件 */
# 案例1: 查询哪个部门的员工个数 > 2
# 分两步: 先查询每个部门的员工个数, 再根据此结果筛选员工数 > 2
SELECT
department_id,
COUNT(*) AS count
FROM
employees
GROUP BY
department_id
HAVING
count > 2
ORDER BY
count DESC;
# 注意: where条件是对原始表进行筛选, having条件是对查询后的表进行筛选操作
# 案例2: 查询每个工种有奖金的员工的最高工资 > 12000 的工种编号和最高工资
# 第一步: 查询每个工种有奖金的员工的最高工资
SELECT
job_id,
MAX(salary) AS max
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
job_id
HAVING
max > 12000
ORDER BY
max;
# 案例3: 查询领导编号 > 102 的每个领导手下的最低工资 > 5000 的领导编号是哪个?, 以及其最低工资
SELECT
manager_id, MIN(salary) AS min
FROM
employees
WHERE
manager_id > 102
GROUP BY
manager_id
HAVING
min > 5000;
4.按表达式或者函数分组
/* 按表达式或者函数分组 */
# 案例: 按员工姓名长度分组, 查询每一组的员工个数, 筛选员工个数 > 5 的有哪些
SELECT
COUNT(*) AS c,
LENGTH(last_name) AS len_name
FROM
employees
GROUP BY
len_name
HAVING
c > 5
ORDER BY
len_name ASC;
5. 按多个字段进行分组
/* 按多个字段进行分组 */
# 案例: 查询每个部门每个工种的员工的平均工资, 并排序
SELECT
department_id, job_id, AVG(salary)
FROM
employees
WHERE
department_id IS NOT NULL
GROUP BY
department_id, job_id
HAVING
AVG(salary) > 10000
ORDER BY
AVG(salary) DESC;
6. 综合案例
/* 案例测试 */
# 1. 查询各job_id的员工工资的最大值, 最小值, 平均值, 总和, 并按 job_id 升序
SELECT
job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM
employees
GROUP BY
job_id
ORDER BY
job_id ASC;
# 2. 查询员工最高工资和最低工资的差距 (DIFFERENCE)
SELECT
(MAX(salary) - MIN(salary)) AS DIFFERENCE
FROM
employees;
# 3. 查询各个管理者手下员工的最低工资, 其中最低工资不能低于6000, 没有管理者的员工不计算在内
SELECT
manager_id,
MIN(salary)
FROM
employees
WHERE
manager_id IS NOT NULL
GROUP BY
manager_id
HAVING
MIN(salary) >= 6000;
# 4. 查询所有部门的编号, 员工数量和工资平均值, 并按平均工资降序
SELECT
department_id,
COUNT(*),
AVG(salary)
FROM
employees
GROUP BY
department_id
ORDER BY
AVG(salary) DESC;
# 5. 选择具有各个 job_id 的员工人数
SELECT
job_id, COUNT(*)
FROM
employees
GROUP BY
job_id;