Group by

作用：分组

• 可以包含任意数目的列。
• 除了聚集计算语句外，SELECT 语句中的每个列都必须在GROUP BY子句中给出；
• 如果分组列中具有NULL，则将NULL做为一个分组返回，如果有多行NULL值，它们将分为一组；
• GROUP BY必须出现在WHERE子句后，ORDER BY语句之前；

HAVAING

作用：进行分组过滤，支持所有WHERE操作；WHERE在数据分组前进行过滤，HAVAING在数据分组后进行过滤；

mysql> SELECT * FROM student;
+------------+------+-----------+-------+
| studentNum | name | classroom | score |
+------------+------+-----------+-------+
| 1001       | 李菲 |         1 |    78 |
| 1002       | 王名 |         1 |    88 |
| 1003       | 赵琳 |         1 |    98 |
| 1004       | 杜悦 |         2 |    87 |
| 1005       | 周黎 |         2 |    55 |
| 1006       | 齐飞 |         3 |    89 |
| 1007       | 孙敏 |         3 |    87 |
| 1008       | 周克 |         4 |    97 |
+------------+------+-----------+-------+
8 rows in set (0.00 sec)

mysql> SELECT classroom ,AVG(score) AS avg_score FROM  student GROUP BY classroom;
+-----------+-----------+
| classroom | avg_score |
+-----------+-----------+
|         1 |   88.0000 |
|         2 |   71.0000 |
|         3 |   88.0000 |
|         4 |   97.0000 |
+-----------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT classroom ,AVG(score) AS avg_score FROM  student GROUP BY classroom HAVING avg_score>80;
+-----------+-----------+
| classroom | avg_score |
+-----------+-----------+
|         1 |   88.0000 |
|         3 |   88.0000 |
|         4 |   97.0000 |
+-----------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT classroom ,AVG(score) AS avg_score ,MAX(score) AS max_score,MIN(score) AS min_score FROM  student GROUP BY classroom;
+-----------+-----------+-----------+-----------+
| classroom | avg_score | max_score | min_score |
+-----------+-----------+-----------+-----------+
|         1 |   88.0000 |        98 |        78 |
|         2 |   71.0000 |        87 |        55 |
|         3 |   88.0000 |        89 |        87 |
|         4 |   97.0000 |        97 |        97 |
+-----------+-----------+-----------+-----------+
4 rows in set (0.00 sec)

mysql> SELECT classroom ,AVG(score) AS avg_score ,MAX(score) AS max_score,MIN(score) AS min_score FROM  student GROUP BY classroom HAVING classroom = 1;
+-----------+-----------+-----------+-----------+
| classroom | avg_score | max_score | min_score |
+-----------+-----------+-----------+-----------+
|         1 |   88.0000 |        98 |        78 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)

SELECT 子句的执行顺序