19、MySQL基础之分组查询

1、语法

select 查询列表
from 表
where 分组前筛选-----对from表中的数据进行筛选，针对的是原始表数据的筛选
group by 分组列表
having   分组后筛选--------对分组查询结果进行筛选，针对的是分组后的结果集数据的筛选
order by 排序列表
# 注意事项：
# 分组查询的查询列表只能是 分组函数 或者group by 后出现的字段
# 分组函数做条件一定是放在having子句中
# 能用分组前筛选的，就优先考虑使用分组前筛选。
# where子句不支持别名
# 分组列表可以是单个字段，多个字段，函数，表达式，别名
# 分组筛选也支持别名
# group by 和having 支持别名，仅限MySQL。

二、案例

案例1：查询每个工种的最高工资

SELECT job_id,MAX(salary)
FROM employees
GROUP BY job_id;

案例2：查询每个位置上的部门个数

SELECT location_id,count(*)
FROM departments
GROUP BY location_id

案例3：查询邮箱中包含a字符的，每个部门的平均工资

SELECT department_id,AVG(salary)
FROM employees
WHERE email like '%a%'
GROUP BY department_id;

案例4：查询有奖金的每个领导手下员工的最高工资

SELECT manager_id,MAX(salary)
FROM employees
WHERE commission_pct is not null
GROUP BY manager_id;

案例5：查询出员工数大于2的部门

SELECT count(*),department_id
FROM employees
GROUP BY department_id
HAVING count(*)>2;

案例6：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

# 方法一
SELECT job_id,max(salary)
FROM employees
WHERE commission_pct is not null 
GROUP BY job_id
HAVING MAX(salary)>12000
# 方法二
SELECT job_id,max(salary)
FROM employees
WHERE commission_pct is not null and salary >12000  # where中筛选出大于12000的再求最大值
GROUP BY job_id

案例7：查询领导编号大于102的每个领导手下的最低工资>5000的领导编号，以及最低工资

SELECT manager_id,min(salary)
FROM employees
WHERE manager_id > 102    # 若在where中筛选出大于5000的，再求最小值，该值不一定是该组的最小值。
GROUP BY manager_id
HAVING min(salary)> 5000;
-- 第一步：先求每个领导手下的最低工资
-- 第二步：筛选出大于102的领导编号-------原始表就有，可以通过where筛选
-- 第三步：筛选出最低工资大于5000的领导编号----------分组查询后才有，使用having筛选

案例8：按员工姓名的长度分组，查询每一组的员工个数，筛选出员工个数大于分组

SELECT LENGTH(CONCAT(first_name,last_name)) as 姓名长度,
       COUNT(*) as 员工个数
FROM employees
GROUP BY 姓名长度
HAVING 员工个数 > 5;

案例9：查询每个部门每个工种的员工的平均薪资

SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id;

案例10：查询每个部门每个工种的员工的平均薪资，并且按平均工资的高低排序。

SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

三、练习

练习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;

练习2：查询员工最高工资和最低工资的差距（DIFFERENCE）

SELECT MAX(salary)-MIN(salary) 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;

练习1：选择具有各个job_id的员工人数

SELECT COUNT(*),job_id
FROM employees
GROUP BY job_id;