MySQL必知必会_分组查询

进阶5：分组查询

语法：
select 查询的字段，分组函数
from 表
group by 分组的字段


特点：
1、可以按单个字段分组
2、和分组函数一同查询的字段最好是分组后的字段
3、分组筛选
        针对的表    位置          关键字
分组前筛选：  原始表     group by的前面     where
分组后筛选：  分组后的结果集 group by的后面     having

4、可以按多个字段分组，字段之间用逗号隔开
5、可以支持排序
6、having后可以支持别名

#

    一、语法
    select 分组函数，分组后的字段
    from 表
    【where 筛选条件】
    group by 分组的字段
    【having 分组后的筛选】
    【order by 排序列表】
    注意执行顺序，group by 与 order by 在其他语法中不能用别名

    二、特点

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

##################案例演示

    #进阶5：分组查询(关键字：每个)

    /*
    语法：

    select 查询列表
    from 表
    【where 筛选条件】
    group by 分组的字段
    【order by 排序的字段】;

    特点：
    1、和分组函数一同查询的字段必须是group by后出现的字段（否则表格会不规则）
    2、筛选分为两类：分组前筛选和分组后筛选
            数据源                位置       连接的关键字
    分组前筛选   原始表             group by前   where

    分组后筛选   group by后的结果集           group by后   having

    问题1：分组函数做筛选能不能放在where后面
    答：不能，因为原始函数不存在 分组函数结果 这个字段，无法根据这个进行筛选

    问题2：where——group by——having

    一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率

    3、分组可以按单个字段也可以按多个字段（多个字段用逗号隔开，没有前后次序），同样，也可以按照函数或者表达式分组
    4、可以搭配着排序使用（排序一般放在最后）




    */



    #引入：查询每个部门的员工个数

    SELECT COUNT(*) FROM employees WHERE department_id=90;
    #1.简单的分组

    #案例1：查询每个工种的员工平均工资
    SELECT AVG(salary),job_id
    FROM employees
    GROUP BY job_id;

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

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


    #2、可以实现分组前的筛选（WHERE 先筛选，再根据字段（每个部门）分组，即分成多个表，然后对每一个组执行统计函数）

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

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


    #案例2：查询有奖金的每个领导手下员工的平均工资
    # 每个领导：说明根据此字段分组
    SELECT AVG(salary),manager_id
    FROM employees
    WHERE commission_pct IS NOT NULL
    GROUP BY manager_id;



    #3、分组后筛选（对统计结果进行筛选，用HAVING。 先分组，然后每个组执行统计函数，然后对每个组的统计结果进行筛选）

    #案例：查询哪个部门的员工个数>5

    #①查询每个部门的员工个数
    SELECT COUNT(*),department_id
    FROM employees
    GROUP BY department_id;

    #② 筛选刚才①结果

    SELECT COUNT(*),department_id
    FROM employees

    GROUP BY department_id

    HAVING COUNT(*)>5;


    #案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
    #注意WHERE GROPBY BY HAVING 的顺序，分组前筛选，分组，分组后筛选
    #一般先写基本架构（select from group by），再追加筛选条件，包括筛选前和筛选后,若有排序，最后追加排序
    SELECT job_id,MAX(salary)
    FROM employees
    WHERE commission_pct IS NOT NULL
    GROUP BY job_id
    HAVING MAX(salary)>12000;


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

    manager_id>102

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

    ###### 按照表达式或者函数进行分组(不只是可以用字段来分组)

    #案例：按照员工姓名的长度进行分组，查询每一个的员工个数，筛选员工个数>5的有哪些
    #GROUP BY 和HAVING 后都支持别名，where 不支持
    SELECT 
        COUNT(*) AS 个数,
        LENGTH(last_name) AS  姓名长度
    FROM employees
    GROUP BY 姓名长度
    HAVING 个数 > 5;

    ###以下这样写也可以
    SELECT 
        COUNT(*) AS 个数,
    FROM employees
    GROUP BY LENGTH(last_name)
    HAVING 个数 > 5;

    #4.添加排序

    #案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

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




    #5.按多个字段分组，分的更细了

    #案例：查询每个工种每个部门的最低工资,并按最低工资降序

    SELECT MIN(salary),job_id,department_id
    FROM employees
    GROUP BY department_id,job_id   # 这两个顺序可以颠倒
    ORDER BY MIN(salary) DESC;

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

    SELECT 
        department_id AS 部门编号,
        COUNT(*) AS 员工数量,
        AVG(salary) AS 平均工资
    FROM
        employees
    GROUP BY 部门编号
    ORDER BY 平均工资 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.查询员工最高工资和最低工资的差距（DIFFERENCE）
    SELECT MAX(salary)-MIN(salary) DIFFRENCE
    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 department_id,COUNT(*),AVG(salary) a
    FROM employees
    GROUP BY department_id
    ORDER BY a DESC;
    #5.选择具有各个job_id的员工人数
    SELECT COUNT(*) 个数,job_id
    FROM employees
    GROUP BY job_id;