mysql学习笔记（七）—— 分组函数和分组查询

分组函数

/*
功能： 用于统计计算，又称为聚合函数和统计函数(输出为一个值)
分类：sum 求和、avg 求平均、max 求最大值、min 求最小值、count 计数

*/

#1. 简单使用

SELECT SUM(salary) FROM employees;

SELECT ROUND(AVG(salary),2) 平均工资,COUNT(salary)  人数 FROM employees;

2. 特点

#（1）sum、avg一般用于处理数值类型，max、min、count可以处理任意类型
#（2）以上分组函数在使用时都会忽略null
#（3）count支持count(distinct X)，即去重后再计数（sum等也支持）
#（4）和分组函数一同查询的字段要求时group by后的字段（group by后面讲）

count扩展

SELECT COUNT(*) FROM employees;   # 统计总行数
SELECT COUNT(1) FROM employees;   # 也可以统计个数，1可以是任意常量

分组查询

/*
语法结构：

select		分组函数,分组列表（要求出现在group by之后）
from 		表
【where		筛选条件】
group by 	分组列表
【order by 	子句】

注意：
分组列表是要进行分组的标准，即按该列表分组
分组查询中的筛选分为两类：
（1）分组前筛选（筛选条件为表中现成的字段，用where，放在from后）
（2）分组后筛选（筛选条件不为字段，而是分组函数，用having，放在group by后）
*/

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

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

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

SELECT 
	COUNT(department_id),location_id   #department_id为主键，所以不需要去重
FROM 
	departments
GROUP BY
	location_id;

**

– 分组前的筛选

**

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

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

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

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

– 分组后的筛选（使用having来连接）

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

/*
分析：
（1）查询每个部门的员工个数
（2）通过（1）的结果来查询>2的部门id
*/

SELECT 
	COUNT(*),department_id
FROM 	
	employees
GROUP BY
	department_id  # 以上得到每个部门的员工个数
HAVING	
	COUNT(*)>2;    # 输出员工个数大于2的情况

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

/*
分析：
（1）查什么：工种编号和最高工资
（2）从哪查：employees表
（3）筛选条件：

where:有奖金（因为”commission_pct IS NOT NULL“中的commission_pct是employees表中的字段，用where）

having:员工最高工资大于12000（最高工资是max(salary)，是在from完以后才能得到的结果，必须放外面 ）
（4）分组条件：工种
*/

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

案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导编号和最低工资

/*
分析：
（1）领导编号>102 是分组前的条件
（2）最低工资>5000 是分组后的条件
（3）分组条件：领导

*/

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

按表达式或函数分组

案例：按员工姓名长度分组，查询每一组的员工个数，以及员工个数>5的组

/*
分析：
（1）先对长度进行分组，分组完后再得到员工个数>5的组
*/

SELECT
	COUNT(employee_id) AS 员工个数,LENGTH(last_name) AS 员工姓名长度
FROM
	employees
GROUP BY
	员工姓名长度
HAVING
	员工个数>5;

按多个字段进行分组

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

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

添加排序

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

SELECT
	AVG(salary) 平均工资,department_id,job_id
FROM
	employees
GROUP BY
	department_id,job_id
ORDER BY
	平均工资 DESC;

练习

#1.查询员工最高工资和最低工资的差距（difference）

SELECT MAX(salary)-MIN(salary)  差距 FROM employees;

#2.查询各个管理者手下的员工的最低工资，其中最低工资不能低于6000，没有管理者的员工除外

SELECT
	MIN(salary) 最低工资,manager_id
FROM
	employees
WHERE
	manager_id IS NOT NULL
GROUP BY
	manager_id
HAVING
	最低工资>=6000;

#3.查询所有部门的编号、员工数量和平均工资，并按平均工资降序排列

SELECT
	department_id 部门编号,COUNT(*) 员工数量,AVG(salary) 平均工资
FROM
	employees
GROUP BY
	department_id
ORDER BY
	平均工资 DESC;