MySQL（5.分组查询）

#五.分组查询
/*
	1.语法
	select 查询列表
	from 表
	where 筛选条件
	group by 分组列表
	having 分组条件
	order by 排序列表;
	
	2.执行顺序
	from -> where -> group by -> having -> select -> order by
	
	3.注意
	1）.顺序：where——group by ——having
	2）.where  在分组之前，做的筛选
	  having 在分组之后，做的筛选
	3）.一旦分组后，select查询列表只能出现group by后的分组字段，或者分组函数	
*/

#4.案例
-- 1）简单的分组
#案例1：查询每个工种的员工平均工资
select job_id , avg(salary) 
from employees
group by job_id;

#案例2：查询每个领导的手下人数
select manager_id , count(*)
from employees
group by manager_id;

-- 2）可以实现分组前的筛选  where
#案例1：查询邮箱中包含a字符的每个部门的最高工资
select department_id , max(salary)
from employees
where email like '%a%'
group by department_id;

#案例2：查询每个领导手下有奖金的员工的平均工资
select manager_id , avg(salary)
from employees
where commission_pct is not null
group by manager_id;

-- 3）可以实现分组后的筛选  having
#案例1：查询哪个部门的员工个数>5
select department_id , count(*)
from employees
group by department_id
having count(*) > 5;

#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
select job_id , max(salary)
from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;

#案例3：领导编号>102的每个领导手下的最低工资大于5000的最低工资
select manager_id , min(salary)
from employees
where manager_id > 102
group by manager_id
having min(salary)>5000;

-- 4）可以实现排序
#案例：查询没有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
select job_id , max(salary) max
from employees 
where commission_pct is null
group by job_id
having max(salary)>6000
order by max;

-- 5）按多个字段分组
#案例：查询每个工种每个部门的最低工资,并按最低工资降序。提示：工种和部门都一样，才是一组
select job_id , department_id , min(salary) min
from employees
group by job_id , department_id
order by min desc;

#5.作业
-- 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 abs(max(salary)-min(salary)) '差值'
from employees;
-- 3. 查询各个管理者手下员工的最低工资，其中最低工资不能低于 6000，没有管理者的员
-- 工不计算在内
select manager_id , min(salary)
from employees
where salary > 6000
group by manager_id
having manager_id is not null;
-- 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
having job_id is not null;