group by 是在 where 之后运行
在写单表查询语法的时候 应该把 group by 写在 where 之后
执行顺序
1. 先找到表 from 库. 表名
2. 按照 where 约束条件 过滤你想要的记录
3.group by 进行分组
4. 分完组以后 再进行相应的查询
分组查询: GROUP BY
一 什么是分组? 为什么要分组?
分类一定要找大家都有一样的属性
- #1, 首先明确一点: 分组发生在 where 之后, 即分组是基于 where 之后得到的记录而进行的
- #2, 分组指的是: 将所有记录按照某个相同字段进行归类, 比如针对员工信息表的职位分组, 或者按照性别进行分组等
- #3, 为何要分组呢?
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
小窍门:'每'这个字后面的字段, 就是我们分组的依据
#4, 大前提:
可以按照任意字段分组, 但是分组完毕后, 比如 group by post, 只能查看 post 字段, 如果想查看组内信息, 需要借助于聚合函数
- MySQL> select * from employee;
- +----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
- | id | name | sex | age | hire_date | post | post_comment | salary | Office | depart_id |
- +----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
- | 1 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
- | 2 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
- | 3 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
- | 4 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
- | 5 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 6 | 成龙 | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 7 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 8 | 丫丫 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 9 | 丁丁 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 10 | 星星 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 11 | 格格 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 12 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 13 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 14 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 15 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 16 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
- +----+------------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
- 16 rows in set (0.00 sec)
这样写 分完组以后 只能取分组字段 post
- select post from employee group by post
- # 由于没有设置 ONLY_FULL_GROUP_BY, 于是也可以有结果, 默认都是组内的第一条记录, 但其实这是没有意义的
- MySQL> select * from employee group by post;
- +----+--------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
- | id | name | sex | age | hire_date | post | post_comment | salary | Office | depart_id |
- +----+--------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| 12 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 7 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
- | 1 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
- +----+--------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
- 3 rows in set (0.06 sec)
- ONLY_FULL_GROUP_BY
查看现在 sql_mode 模式
- MySQL> select @@global.sql_mode;
- +--------------------------------------------+
- | @@global.sql_mode |
- +--------------------------------------------+
- | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
- +--------------------------------------------+
- 1 row in set (0.00 sec)
设置 MySQL 严格模式
"ONLY_FULL_GROUP_BY" 只能取分组的字段 以及每个组聚合函数结果
- MySQL> set global sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION";
- Query OK, 0 rows affected (0.00 sec)
- MySQL> select @@global.sql_mode;
- +---------------------------------------------------------------+
- | @@global.sql_mode |
- +---------------------------------------------------------------+
- | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
- +---------------------------------------------------------------+
- 1 row in set (0.00 sec)
退出一下 MySQL 再重新登 MySQL 查看
- MySQL> quit #设置成功后, 一定要退出, 然后重新登录方可生效
- Bye
报错 已经是严格模式了 只能取分组的字段
- MySQL> select * from employee group by post;
- ERROR 1055 (42000): 'company.employee.id' isn't in GROUP BY
- MySQL> select post from employee group by post;
- +-----------+
- | post |
- +-----------+
- | operation |
- | sale |
- | teacher |
- +-----------+
- 3 rows in set (0.00 sec)
想统计每个部门的相关数据 用到聚合函数
把某一个组里面的一推人统计出结果 以组为单位统计
- # 查看 MySQL 5.7 默认的 sql_mode 如下:
- MySQL> select @@global.sql_mode;
- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
- #!!! 注意
ONLY_FULL_GROUP_BY 的语义就是确定 select target list 中的所有列的值都是明确语义,
简单的说来, 在 ONLY_FULL_GROUP_BY 模式下, target list 中的值要么是来自于聚集函数的结果, 要么是来自于 group by list 中的表达式的值.
- # 设置 sql_mole 如下操作 (我们可以去掉 ONLY_FULL_GROUP_BY 模式):
- MySQL> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
!!!SQL_MODE 设置!!!
来源: http://www.bubuko.com/infodetail-2985404.html