5.MySQL——分组查询

分组查询

语法：

          select     查询列表    列（要求出现在group by的后面）   5

          from       表                                                                         1

        【where 筛选条件】                                                             2

         group by      分组的字段                                                      3

       【having  分组后的筛选条件】                                              4

       【order by 排序的字段】                                                       6

 

分组查询的执行顺序：1——>2——>3——>4——>5——>6

 

注：（1）查询列表比较特殊，必须是分组函数和group by后面出现的字段（按谁分组，谁就可以被查询出来）

       （2）分组查询中的筛选条件分为两类：（划分标准是他们的数据源不同）

                                                                     分组前筛选：原始表                      GROUP BY关键字前面        关键字：WHERE

                                                                     分组后筛选：分组后的结果集        GROUP BY关键字后面        关键字：HAVING

          注意：a.分组函数做条件肯定是放在HAVING子句中

                     b.能用分组前筛选时，就优先使用分组前筛选，因为它的效率更高

       （3）GROUP BY子句支持单个字段分组，多个字段分组（多个字段之间用逗号隔开，没有顺序限制），也支持表达式和函                数

        （4）也可以添加排序，排序放在分组查询的最后

1.简单使用

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

         SELECT MAX(salary),job_id

         FROM employees

         GROUP BY job_id

 

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

        SELECT COUNT(*),location_id

        FROM departments

        ORDER BY location_id

2.添加分组前的筛选

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

        SELECT AVG(salary),department_id

        FROM employees

        WHERE email like`%e%`

        GROUP BY department_id

3.添加分组后的筛选（在查询后的结果进行处理）

案例一：查询那个部门的员工数大于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

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

           （1）查询每个工种有奖金的最高工资

                    SELECT MAX(salary),job_id

                    FROM employees

                    WHERE commission_pet IS NUT NULL

                    GROUP BY job_id

（2）根据（1）的结果继续进行筛选，最高工资>12000

                    SELECT MAX(salary),job_id

                    FROM employees

                    WHERE commission_pet IS NUT NULL

                    GROUP BY job_id

                    HAVING MAX(salary)>12000

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

             （1）查询每个领导下员工的最低工资

                    SELECT MIN(salary),manager

                     FROM employees

                     GROUP BY manager_id

               （2）添加筛选条件：领导编号>102

                     SELECT MIN(salary),manager

                     FROM employees

                     WHERE manager_id>102

                     GROUP BY manager_id

                （3）添加筛选条件：最低工资>5000

 

                     SELECT MIN(salary),manager

                     FROM employees

                     WHERE manager_id>102

                     GROUP BY manager_id

                     HAVING MIN(salary)>5000

 

4.按表达式或函数分组：

案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些

       （1）查询每个长度的员工个数

               SELECT COUNT(*),LENGTH(last_name) length_name

               FROM employees

               GROUP BY LENGTH(last_name)

        （2）添加筛选条件：

               SELECT COUNT(*),LENGTH(last_name) length_name

               FROM employees

               GROUP BY LENGTH(last_name)

               HAVING COUNT(*)>5

5.按多个字段分组：

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

         SELECT AVG(salary),department_id,job_id

         FROM employees

         GROUP BY department_id,job_id

6.添加排序：

   案例：查询每个部门每个工种的员工的平均工资，并按平均工资高低进行排序

          SELECT AVG(salary),department_id,job_id

          FROM employees

          GROUP BY department_id,job_id

          ORDER BY AVG(salary)

 

习题：

1.查询员工的最高工资与最低工资的差距（DIFFERENCE）

SELECT MAX(salary)-MIN(salary) DIFFERENCE

FROM employees

2.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不能计算在内

SELECT MIN(salary),manager_id

FROM employees

WHERE manager IS NOT NULL

GROUP BY manager_id

HAVING MIN(salary)>=6000

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

SELECT COUNT(*), AVG(salary),department_id

FROM employees

GROUP BY depaertment_id

ORDER BY AVG(salary) DESC

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

SELECT COUNT(*),job_id

FROM employees

GROUP BY job_id