MYSQL学习4分组查询

MYSQL学习4分组查询
#进阶5：分组查询
/*
语法：
select 分组函数，列（要求出现在group by后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】
注意：
查询列表比较特殊，要求分组函数和group by后出现的字段
特点：
1.分组查询中的筛选条件分为两类
数据源 位置 关键字
分组前筛选 原始表 group by字句的前面 where
分组后筛选 分组后的结果集 group by字句的后面 having

   1.分组函数做条件肯定是放在having子句中
   2.能用分组前筛选的，就优先考虑使用分组前筛选
   
   2.group by字句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求），
   表达式或函数（用的较少）
   3.也可以添加排序（排序放在整个分组查询的最后）

*/
#引入：查询每个部门的平均工资
#简单的分组查询
#案例1：查询每个工种的最高工资
SELECT
MAX(salary),job_id
FROM
employees
GROUP BY job_id ;

#案例2：查询每个位置上的部门个数
SELECT
COUNT(department_id),
location_id
FROM
departments
GROUP BY location_id ;

#添加分组前的筛选条件
#案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT
AVG(salary),
email,
department_id
FROM
employees
WHERE email LIKE ‘%a%’
GROUP BY department_id;

#案例二：查询有奖金的,每个领导手下员工的最高工资
SELECT
MAX(salary),
manager_id
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

#添加分组后的筛选条件
#案例1：查询哪个部门的员工个数>2
#1.查询每个部门的员工个数
SELECT
COUNT(*),
department_id
FROM
employees
GROUP BY department_id ;

#2.根据1的结果进行筛选，查询哪个部门的员工个数>2
SELECT
COUNT(),
department_id
FROM
employees
GROUP BY department_id
HAVING COUNT()>2;

#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT
job_id,
MAX(salary) 最高工资
FROM
employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING 最高工资 > 12000 ;

#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资
SELECT
manager_id,
MIN(salary)
FROM
employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000 ;

#按表达式或函数分组
#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
#1.查询每个长度的员工个数
SELECT COUNT(),LENGTH(last_name) FROM employees GROUP BY LENGTH(last_name);
#2.添加筛选条件
SELECT
COUNT()员工数,
LENGTH(last_name)长度
FROM
employees
GROUP BY LENGTH(last_name)
HAVING 员工数>5;

SELECT
COUNT(*)员工数,
LENGTH(last_name)长度
FROM
employees
GROUP BY 长度
HAVING 员工数>5;
#group by和having支持别名，where不支持别名

#按多个字段分组
#案例：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id;

#添加排序
#案例：查询每个部门每个工种的员工平均工资，并按平均工资的高低显示
SELECT AVG(salary) a,department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING a>10000
ORDER BY a DESC;

#练习
#1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序
SELECT MAX(salary),MIN(salary),AVG(salary),COUNT(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id;

#2.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary)-MIN(salary) DIFFERENCE
FROM employees;

#3.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;

#4.查询所有部门的编号，员工数量和工资平均值，并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary) a
FROM employees
GROUP BY department_id
ORDER BY a DESC;

#5.查询各个job_id的员工人数
SELECT COUNT(*),job_id
FROM employees
GROUP BY job_id;