02-DQL语言-05分组查询

理论

一、语法

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】

二、特点

使用关键字	筛选的表	位置
分组前的筛选	Where 原始表	Group by前面
分组后的筛选	Having 分组后的结果	Group by后面

案例

语法：
select 分组函数，列（要求吹西安group by的后面）
from 表
【where 筛选条件】
group by 分组的列表
【order by 子句】 注意：
查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
1、分组查询中的筛选条件分为两类

            数据源               位置
分组前筛选   原始表              group by 子句的前面
分组后筛选   分组后的结果表       group by 子句的后面

①分组函数做条件肯定是放在having子句中
②能在分组前筛选的，就优先考虑使用分组前筛选

2、group子句支持单个字段分组，多个字段分组（多个字段之间用都好隔开没有顺序要求），表达式或函数（用得较少）
3、也可以添加顺序（排序放在整个分组查询的最后）

*/

#引入：每个部门的平均工资、

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

一、简单的分组查询

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

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

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

SELECT  COUNT(*) 个数,location_id 部门位置
FROM departments
GROUP BY location_id;

二、添加筛选条件

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

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

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

SELECT MAX(salary),`manager_id`
FROM employees
WHERE `commission_pct` IS NOT NULL
GROUP BY `manager_id`;

三、添加复杂的筛选条件

#案例1：查哪个部门的员工个数>2

#①：查询每个部门的员工个数
SELECT COUNT(*) 个数,`department_id`部门
FROM employees
GROUP BY `department_id`;
#②：根据①的结果进行筛选，查询那个部门的员工个数>2
SELECT COUNT(*) 个数,`department_id`部门
FROM employees
GROUP BY `department_id`
HAVING COUNT(*)>2;

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

#①查询每个工种有奖金的员工的最高工资
SELECT `job_id`,MAX(salary)
FROM employees
WHERE salary > 12000 AND `commission_pct` IS NOT NULL
GROUP BY `job_id`;
#②根据①的结果继续筛选，最高工资》12000
SELECT `job_id`,MAX(salary)
FROM employees
WHERE salary > 12000 AND `commission_pct` IS NOT NULL
GROUP BY `job_id`
#having max(salary) >12000;

#案例3：查询领导编号》120的每个领导手下的最低工资》5000的领导编号是哪个。

#①查询领导编号》120的每个领导手下
SELECT `manager_id`,last_name
FROM employees
WHERE `manager_id`>120
#②的最低工资》5000的领导编号是哪个
SELECT last_name 姓名,`manager_id` 领导编号,MIN(salary) 最低工资
FROM employees
WHERE `manager_id`>120 AND salary>5000
GROUP BY `manager_id`
    #having salary>5000

四、按表达式或函数分组

#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数》5的有哪些

#①查询每个长度的员工个数
SELECT COUNT(*) 数量,LENGTH(last_name) 名字长度
FROM employees
GROUP BY LENGTH(last_name);
#②添加筛选条件
SELECT COUNT(*) 数量,LENGTH(last_name) 名字长度
FROM employees
GROUP BY LENGTH(last_name)
HAVING 数量>5;

五、按多个字段分组

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

SELECT last_name,AVG(salary) 平均工资,`department_id` ,`job_id`
FROM employees
GROUP BY `department_id` ,`job_id`

六、添加排序

#案例：查询每个部门每个工种的员工的平均工资，并按平均工资的高低显示

SELECT last_name,AVG(salary) 平均工资,`department_id` ,`job_id`
FROM employees
WHERE department_id IS NOT NULL
GROUP BY `department_id` ,`job_id`
#having
ORDER BY AVG(salary) DESC