语法：
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
3.group by 子句支持单个字段分组，多个字段分组（多个字段用逗号隔开，没有顺序要求），表达式，函数，别名（当使用别名时，要全部使用别名），也可以添加排序。
√一. 简单分组查询练习
mysql> #1. 查询每个部门的评价工资
mysql> select avg(salary) 平均工资, department_id from employees group by department_id;
+--------------+---------------+
| 平均工资 | department_id |
+--------------+---------------+
| 7000.000000 | NULL |
| 4400.000000 | 10 |
| 9500.000000 | 20 |
| 4150.000000 | 30 |
| 6500.000000 | 40 |
| 3475.555556 | 50 |
| 5760.000000 | 60 |
| 10000.000000 | 70 |
| 8955.882353 | 80 |
| 19333.333333 | 90 |
| 8600.000000 | 100 |
| 10150.000000 | 110 |
+--------------+---------------+
12 rows in set (0.02 sec)
#2. 查询每个位置上的部门个数。
mysql> select count(*) 部门个数, location_id from departments group by location_id;
+--------------+-------------+
| 部门个数 | location_id |
+--------------+-------------+
| 1 | 1400 |
| 1 | 1500 |
| 21 | 1700 |
| 1 | 1800 |
| 1 | 2400 |
| 1 | 2500 |
| 1 | 2700 |
+--------------+-------------+
7 rows in set (0.07 sec)
mysql> #3.查询每个工种的最高工资
mysql> select max(salary) 最高工资, job_id from employees group by job_id;
+--------------+------------+
| 最高工资 | job_id |
+--------------+------------+
| 8300.00 | AC_ACCOUNT |
| 12000.00 | AC_MGR |
| 4400.00 | AD_ASST |
| 24000.00 | AD_PRES |
| 17000.00 | AD_VP |
| 9000.00 | FI_ACCOUNT |
| 12000.00 | FI_MGR |
| 6500.00 | HR_REP |
| 9000.00 | IT_PROG |
| 13000.00 | MK_MAN |
| 6000.00 | MK_REP |
| 10000.00 | PR_REP |
| 3100.00 | PU_CLERK |
| 11000.00 | PU_MAN |
| 14000.00 | SA_MAN |
| 11500.00 | SA_REP |
| 4200.00 | SH_CLERK |
| 3600.00 | ST_CLERK |
| 8200.00 | ST_MAN |
+--------------+------------+
19 rows in set (0.00 sec)
√二. 添加筛选条件分组
mysql> #1. 查询邮箱包含e字符的，每个部门的平均工资
mysql> select avg(salary) 平均工资,department_id from employees where email like '%e%' group by department_id;
+--------------+---------------+
| 平均工资 | department_id |
+--------------+---------------+
| 4400.000000 | 10 |
| 13000.000000 | 20 |
| 6750.000000 | 30 |
| 3221.052632 | 50 |
| 5100.000000 | 60 |
| 10000.000000 | 70 |
| 9781.250000 | 80 |
| 17000.000000 | 90 |
| 9733.333333 | 100 |
| 8300.000000 | 110 |
+--------------+---------------+
10 rows in set (0.09 sec)
注意⚠️当我又想看邮箱是否含有‘e’时，我把字段‘email’也放在查询列表里，然后就报错了因为查询列表必须只能是在分组函数和group by 后出现的字段。
mysql> #2. 查询邮箱包含e字符的，每个部门的平均工资
mysql> select avg(salary) 平均工资,department_id from employees where email like '%e%' group by department_id;
+--------------+---------------+
| 平均工资 | department_id |
+--------------+---------------+
| 4400.000000 | 10 |
| 13000.000000 | 20 |
| 6750.000000 | 30 |
| 3221.052632 | 50 |
| 5100.000000 | 60 |
| 10000.000000 | 70 |
| 9781.250000 | 80 |
| 17000.000000 | 90 |
| 9733.333333 | 100 |
| 8300.000000 | 110 |
+--------------+---------------+
10 rows in set (0.09 sec)
mysql> select avg(salary) 平均工资,department_id,email from employees where email like '%e%' group by department_id;
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'myemployees.employees.email' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql> #3. 查询有奖金的每个领导手下员工的最高工资
mysql> select max(salary) 最高工资,manager_id from employees where commission_pct is not null group by manager_id;
+--------------+------------+
| 最高工资 | manager_id |
+--------------+------------+
| 14000.00 | 100 |
| 10000.00 | 145 |
| 10000.00 | 146 |
| 10500.00 | 147 |
| 11500.00 | 148 |
| 11000.00 | 149 |
+--------------+------------+
6 rows in set (0.29 sec)
√三. 按表达式/函数/别名分组查询
mysql> #1. 查询员工个数大于2 的部门
mysql> #(1)首先查询出每个部门的员工个数
mysql> select count(*) 员工个数, department_id from employees group by department_id;
+--------------+---------------+
| 员工个数 | department_id |
+--------------+---------------+
| 1 | NULL |
| 1 | 10 |
| 2 | 20 |
| 6 | 30 |
| 1 | 40 |
| 45 | 50 |
| 5 | 60 |
| 1 | 70 |
| 34 | 80 |
| 3 | 90 |
| 6 | 100 |
| 2 | 110 |
+--------------+---------------+
12 rows in set (0.00 sec)
mysql> #根据（1）的结果进行筛选
mysql> select count(*) 员工个数, department_id from employees group by department_id having 员工个数 >2;
+--------------+---------------+
| 员工个数 | department_id |
+--------------+---------------+
| 6 | 30 |
| 45 | 50 |
| 5 | 60 |
| 34 | 80 |
| 3 | 90 |
| 6 | 100 |
+--------------+---------------+
6 rows in set (0.00 sec)
mysql> #2. 查询每个工种有奖金的员工的最高工资> 12000的工种号和最高工资
mysql> select job_id , max(salary) 最高工资 from employees where commission_pct is not null group by job_id having 最高工资>12000;
+--------+--------------+
| job_id | 最高工资 |
+--------+--------------+
| SA_MAN | 14000.00 |
+--------+--------------+
1 row in set (0.00 sec)
mysql> #3. 查询领导编号>102 的每个领导手下的最低工资>5000的领导编号是哪个，以及其 最低工资
mysql> select min(salary),manager_id from employees where manager_id>102 group by manager_id having min(salary)>5000;
+-------------+------------+
| min(salary) | manager_id |
+-------------+------------+
| 6900.00 | 108 |
| 7000.00 | 145 |
| 7000.00 | 146 |
| 6200.00 | 147 |
| 6100.00 | 148 |
| 6200.00 | 149 |
| 6000.00 | 201 |
| 8300.00 | 205 |
+-------------+------------+
8 rows in set (0.00 sec)
mysql> #4. 按员工姓名的长度分组，查询每一组的员工的个数，筛选员工个数>5的有哪些
mysql> select count(*) c,length(last_name) l from employees group by l having c>5;
+----+------+
| c | l |
+----+------+
| 29 | 5 |
| 15 | 7 |
| 28 | 6 |
| 8 | 9 |
| 11 | 4 |
| 7 | 8 |
+----+------+
6 rows in set (0.06 sec)
√. 四 按多个字段分组查询
mysql> #查询每个部门每个工种的员工的平均工资
mysql> select avg(salary), department_id ,job_id from employees group by department_id,job_id;
+--------------+---------------+------------+
| avg(salary) | department_id | job_id |
+--------------+---------------+------------+
| 24000.000000 | 90 | AD_PRES |
| 17000.000000 | 90 | AD_VP |
| 5760.000000 | 60 | IT_PROG |
| 12000.000000 | 100 | FI_MGR |
| 7920.000000 | 100 | FI_ACCOUNT |
| 11000.000000 | 30 | PU_MAN |
| 2780.000000 | 30 | PU_CLERK |
| 7280.000000 | 50 | ST_MAN |
| 2785.000000 | 50 | ST_CLERK |
| 12200.000000 | 80 | SA_MAN |
| 8396.551724 | 80 | SA_REP |
| 7000.000000 | NULL | SA_REP |
| 3215.000000 | 50 | SH_CLERK |
| 4400.000000 | 10 | AD_ASST |
| 13000.000000 | 20 | MK_MAN |
| 6000.000000 | 20 | MK_REP |
| 6500.000000 | 40 | HR_REP |
| 10000.000000 | 70 | PR_REP |
| 12000.000000 | 110 | AC_MGR |
| 8300.000000 | 110 | AC_ACCOUNT |
+--------------+---------------+------------+
20 rows in set (0.00 sec)
√. 五 添加排序
mysql> #1. 查询每个部门每个工种的员工的平均工资>10000,并按工资的高低排序
mysql> select avg(salary), department_id ,job_id from employees group by department_id,job_id having avg(salary)>10000 order by avg(salary) desc;
+--------------+---------------+---------+
| avg(salary) | department_id | job_id |
+--------------+---------------+---------+
| 24000.000000 | 90 | AD_PRES |
| 17000.000000 | 90 | AD_VP |
| 13000.000000 | 20 | MK_MAN |
| 12200.000000 | 80 | SA_MAN |
| 12000.000000 | 110 | AC_MGR |
| 12000.000000 | 100 | FI_MGR |
| 11000.000000 | 30 | PU_MAN |
+--------------+---------------+---------+
7 rows in set (0.00 sec)
注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！
网友评论