MySQL基础(七)——DQL之分组查询

本篇文章主要是对MySQL学习时的一些总结，作为学习笔记记录。

数据来源

数据部分来自于b站尚硅谷MySQL课程

分组查询

语法

SELECT querylist FROM tablename WHERE conditions GROUP BY groupfield ORDER BY sortcondition;

特点

• 和分组函数一同查询的字段为group by后出现的字段
• 筛选分为两类，分组前筛选和分组后筛选，一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

	操作对象	位置	关键字
分组前筛选	原始表	group by前	where
分组后筛选	group by后的结果	group by后	having

• 分组可以按单个字段也可以按多个字段

主要操作

1. 简单分组查询

#案例1：查询每个工种的员工平均工资
SELECT 
  AVG(salary),
  job_id 
FROM
  employees 
GROUP BY job_id ;

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

2. 分组前筛选

#案例1：查询邮箱中包含a字符的 每个部门的最高工资
SELECT 
  MAX(salary),
  department_id 
FROM
  employees 
WHERE email LIKE '%a%' 
GROUP BY department_id ;


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

3. 分组后筛选

#案例1：查询哪个部门的员工个数>5
SELECT 
  COUNT(*) AS num,
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING num > 5 ;

#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT 
  job_id,
  MAX(salary) AS 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) AS min_salary 
FROM
  employees 
WHERE manager_id > 102 
GROUP BY manager_id 
HAVING min_salary > 5000 ;

4. 添加排序

#案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序
SELECT 
  job_id,
  MAX(salary) AS max_salary 
FROM
  employees 
WHERE commission_pct IS NOT NULL 
GROUP BY job_id 
HAVING max_salary > 6000 
ORDER BY max_salary ASC ;

5. 按多个字段分组

#案例：查询每个工种每个部门的最低工资,并按最低工资降序
SELECT 
  MIN(salary) AS min_salary,
  job_id,
  department_id 
FROM
  employees 
GROUP BY job_id,
  department_id 
ORDER BY min_salary DESC ;