mysql group by 命令_MySQL常用命令（八）--GROUP BY、HAVING、SELECT子句的顺序

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

要检索的行数

否