二、分组函数

功能：用作统计使用，又称为聚合函数或者统计函数或者组函数
分类：SUM求和,AVG平均,MAX,MIN,COUNT计数

SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(salary)
FROM employees;

特点
1.SUM,AVG，一般用于处理数值型。MAX,MIN,MIN,COUNT计数，可以处理任何类型
2.以上分组函数都忽略NULL值
3.和DISTINCT搭配，（去重）

SELECT SUM(DISTINCT salary),COUNT(DISTINCT salary)
FROM `employees`;

4.count()函数（常用）

SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;#一般用于统计行数      
SELECT COUNT(1) FROM employees;#统计1的个数，有多山行，就有多少个1

5.和分组函数一同查询的字段有限制

SELECT SUM(salary),employee_id
FROM employees;
测试

1.查询公司员工工资的最大值，最小值，平均值，总和。

SELECT MAX(salary),MIN(salary),ROUND(AVG(salary),2),SUM(salary)
FROM employees;

2.查询员工表中的最大入职时间和最小入职时间的相差天（DIFFRENCE）

SELECT DATEDIFF(MAX(`hiredate`),MIN(`hiredate`))
FROM employees;
#注：datadiff(日期1，日期2) 可以计算相差的天数

3.查询部门编号为90的员工个数

SELECT COUNT(*) AS 个数
FROM employees
WHERE `department_id`=90;

进阶5 分组查询

#引入：查询每个部门的平均工资
语法

SELECT 分组函数,列（出现在GROUP BY的后面）
FROM 表
WHERE 筛选条件
GROUP BY 分组的列表

注意：
查询列表必须特殊，要求是分组函数和GROUP BY后出现的字段

特点：
1、分组查询中的筛选条件分为两类

数据源

位置

关键字

分组前筛选

原始表

GROUP BY子句的前面

WHERE

分组后筛选

分组后的结果集

GROUP BY子句的后面

HAVING

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

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

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

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

SELECT COUNT(*),`location_id`
FROM `departments`
GROUP BY `location_id`;

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

SELECT ROUND(AVG(`salary`),2) AS 平均工资,`department_id`
FROM `employees`
WHERE `email` LIKE "%a%"
GROUP BY `department_id`;

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

SELECT MAX(`salary`) AS 最高工资,`manager_id`
FROM `employees`
WHERE `commission_pct` IS NOT NULL
GROUP BY `manager_id`;

#分组后筛选
案例5：查询哪个部门的员工个数>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;
#Having的本质和where一样，是用来进行数据条件筛选。
#Having是在group by子句之后：可以针对分组数据进行统计筛选，但是where不行

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

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

案例7：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个，以及其最低工资

SELECT MIN(salary) AS 最低工资,`manager_id` AS 领导编号
FROM employees
WHERE `manager_id`>102
GROUP BY `manager_id`
HAVING MIN(salary)>5000;

#按表达式或者函数分组
案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

SELECT COUNT(*),`employee_id`
FROM `employees`
GROUP BY LENGTH(`last_name`)
HAVING COUNT(*)>5;

#按多个字段分组
案例：查询每个部门每个工种的员工的平均工资

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

#添加排序
案例：查询每个部门每个工种的员工的平均工资,平均工资大于10000的
并且部门编号不为NULL，和按平均工资的高低显示

SELECT AVG(salary) AS 平均工资,`department_id`,`job_id`
FROM employees
WHERE `department_id` IS NOT NULL
GROUP BY `department_id`,`job_id`
HAVING AVG(salary)>10000
ORDER BY AVG(salary);

测试

1.查询各job_id的员工工资的最大值，最小值，平均值，总和，并按job_id升序

SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id
ORDER BY job_id;

2.查询员工最高工资和最低工资的差距（DIFFERENCE）

SELECT MAX(salary)-MIN(salary) AS 差距
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 COUNT(*),`department_id`,AVG(salary)
FROM employees
GROUP BY `department_id`
ORDER BY AVG(salary) DESC;

5.选择具有各个job_id的员工人数

SELECT COUNT(*) AS 员工个数,`job_id`
FROM `employees`
GROUP BY `job_id`;

待