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 子句的执行顺序
子句
说明
是否必须执行
SELECT
要返回的列表或表达式
是
FROM
从中检索数据的表
仅在从表选择数据时使用
WHERE
行级过滤
否
GROUP BY
分组说明
仅在按组计算聚集时使用
HAVING
组级过滤
否
ORDER BY
输出排序顺序
否
LIMIT
要检索的行数
否
