MySQL学习 分组函数 分组查询 20210709

#二、分组函数
/*
功能：用作统计使用

分类：sum 求和；avg 平均值；max 最大值；min 最小；count 计算个数
特点：
        1. sum、avg 一般用于处理数值型
                max min count 可以处理任何类型
        2.以上分组函数都忽略null值
        3.可以和dinstinct 搭配实现去重运算
        4.count函数的单独介绍 一般用作统计函数
        5.和分组函数一同查询的字段要求是group by 后的字段
*/

#1.简单使用
SELECT SUM(salary) FROM employees
SELECT COUNT(salary) FROM employees #非空的值

SELECT sum(salary) 和,avg(salary) 平均,max(salary) 最高,count(salary) 个数, min(salary) 最低
FROM employees

SELECT sum(salary) 和,ROUND(avg(salary),2) 平均,max(salary) 最高,count(salary) 个数, min(salary) 最低
FROM employees

#2.参数支持哪些类型

SELECT SUM(last_name) FROM employees #不报错 但是已然没意义了

SELECT MAX(hiredate),MIN(hiredate) FROM employees

#3.是否忽略null

SELECT SUM(commission_pct),AVG(commission_pct),SUM(commission_pct)/35,SUM(commission_pct)/107 FROM employees

#都忽略null值

#4.和dinstinct搭配
SELECT SUM(DISTINCT salary), SUM(salary) FROM employees

SELECT COUNT(DISTINCT salary), COUNT(salary) FROM employees

#5.count函数的详细介绍
SELECT COUNT(*) FROM employees
SELECT COUNT(1) FROM employees

#效率：
#MYISAM存储引擎，count(*)效率高
#INNODBC存储引擎下，count（*）跟count（1）效率差不多

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

#测试
#1.查询公司员工工资的最大值、最小值、平均值和总和
SELECT MAX(salary) mx_sal,MIN(salary) mi_sal FROM employees
#2.查询员工表中的最大入职时间和最小入职时间的相差天数（difference）
MAX(hiredate) MIN(hiredate)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) difference FROM employees
#3.查询部门编号为90的员工个数
SELECT COUNT(*)
FROM employees
WHERE department_id = 90

#___________________________________________________

#进阶5： 分组查询
/*
语法：
            select 分组函数，列（要求出现在group by的后面）
            from 表
            【where 筛选条件】
            group by 分组的列表 ｛根据by 后面的字段进行分组｝
            【order by 子句】

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

特点：
            1.分组查询中的筛选条件分为两类
                                            数据源                                位置
                分组前筛选        原始表                                group by 子句的前面 WHERE
                分组后筛选        分组后的结果集合            group by 子句的后面 HAVING
                a.分组函数做条件肯定是放在having子句后
                b.能用分组前筛选的，就优先考虑使用分组前筛选（WHERE）
            2.GROUP BY子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开没有顺序要求） 表达式或函数
            3.也可以添加排序 order by（放在整个分组查询的最后）
*/

 

#案例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
#1.每个部门的员工个数
SELECT COUNT(*)，department_id
FROM employees 
GROUP BY department_id

#2.根据1.的结果进行筛选才，查询哪个部门的员工个数大于2
SELECT COUNT(*),department_id
FROM employees 
GROUP BY department_id
HAVING COUNT(*)>2
#having 分组后的筛选

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

SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY salary
HAVING MAX(salary)>12000
#(看题目 是每个工种 所有分组是用job_id 来筛选的)
#1.查询每个工种有奖金的员工的最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id

#2.根据1.的结果继续筛选，最高工资>12000
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000

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

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

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

#1.查询每个长度的员工个数
SELECT COUNT(*) ,LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
#2.添加筛选条件
SELECT COUNT(*),LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>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 
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
HAVING AVG(salary) > 10000
ORDER BY AVG(salary) DESC

#测试：
#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.查询各个管理者手下员工的最低工资，其中最低工资不能地域6000，没有管理者的员工不计算在内

SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL 
GROUP BY manager_id
HAVING MIN(salary) >=6000

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

SELECT AVG(salary),COUNT(*),department_id
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC