进阶5、分组函数与分组查询

进阶5、分组函数

特点

分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数

清单

**sum(字段名)：**求和

avg(字段名)： 求平均数

max(字段名)： 求最大值

min(字段名)： 求最小值

count(字段名)： 计算非空字段值的个数

特点

1. 实参的字段的类型，sum和avg只支持数值型，其他三个支持任意类型。
2. 这五个函数都忽略null值

# 案例1：查询员工信息表中，所有员工的工资和，最大工资，最低工资，工资平均值，工资个数
SELECT SUM(salary) FROM employees;
SELECT SUM(salary),MAX(salary),MIN(salary),COUNT(salary),AVG(salary)
FROM employees;

案例2：添加筛选条件

1 查询emp表中记录数
SELECT COUNT(employee_id) FROM employees;
SELECT COUNT(salary) FROM employees WHERE salary>2500;

COUNT 补充

1 统计结果集的行数，建议 * 号

count(字段) ： 查询该字段非空值的个数

SELECT COUNT(*) FROM employees;  #查询表中总行数
SELECT COUNT(*) FROM employees WHERE salary>10000;

相当于新建了一列全部为1的常量列，来查询行数
SELECT COUNT(1) FROM employees;  #查询表中总行数
SELECT COUNT(1) FROM employees WHERE salary>10000;
# 相当于新建了一列全部为 A 的常量列，来查询行数
SELECT COUNT('A') FROM employees;  #查询表中总行数
SELECT COUNT('A') FROM employees WHERE salary>10000;

2 搭配DISTINCT 实现去重的统计

SELECT DISTINCT COUNT(department_id) FROM employees;  #  ×
#例：查询有员工的部门个数
SELECT COUNT(DISTINCT department_id) FROM employees;  #  √

每个部门的总工资 平均工资？

分组查询 GROUP BY

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

进阶6、分组查询

GROUP BY子句

语法

select 查询列表（基本是分组函数，或分组的字段）

from 表名

where 筛选条件

group by 分组列表

having 筛选条件 （分组后筛选）

order by 排序列表；

执行顺序

1. from子句
2. where子句
3. group by子句
4. having 子句
5. select 子句
6. order by 子句

特点

1. 查询列表往往是 分组函数 与 被分组的字段。
2. 分组查询中的筛选分为两类。

	筛选的基表	使用的关键词	位置
分组前筛选	原始表	where	group by前面
分组后筛选	分组后的结果集	having	group by后面

3. group by子句支持单个字段分组，多个字段分组（之间用逗号隔开），也支持表达式或函数

顺序 where ——group by —— having

注意

• 分组函数作为条件进行筛选时，只能在having后面，不可能用where
• 能用分组前筛选的，优先使用分组前筛选

每个工种的员工平均工资

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

每个领导的员工人数

SELECT COUNT(employee_id),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id;

邮箱中包含a的 每个部门的最高工资

SELECT MAX(salary) 最高工资,department_id FROM employees WHERE email LIKE '%a%' GROUP BY department_id;

每个领导手下有奖金的员工的平均工资

SELECT AVG(salary) 平均工资,manager_id FROM employees WHERE commission_pct IS NOT NULL GROUP BY manager_id;

实现分组之后的筛选

​ HAVING 支持分组之后的筛选； WHERE 分组之前的筛选

SELECT COUNT(*) 员工个数,department_id FROM employees GROUP BY department_id HAVING 员工个数>5;

每个工种 有奖金的 员工的最高工资>12000 的工种编号和最高工资

SELECT job_id,MAX(salary)FROM employees WHERE commission_pct IS NOT NULL GROUP BY job_id HAVING MAX(salary)>12000;

SELECT manager_id,MIN(salary)FROM employees WHERE manager_id>102 GROUP BY manager_id HAVING MIN(salary)>5000;

分组之后进行排序

SELECT MAX(salary) 最高工资,job_idFROM employees WHERE commission_pct IS NULL GROUP BY job_idHAVING MAX(salary)>5000 ORDER BY MAX(salary) ASC;

按多个字段分组

查询每个工种每个部门的最低工资，按最低工资降序

SELECT MIN(salary) 最低工资,job_id,department_idFROM employees GROUP BY job_id, department_id ORDER BY MIN(salary) DESC ;