MySQL分组

分组 GROUP BY

• 查询男女生总数

select gender as 性别 ,count(*) from user group by gender;

+------+----------+
| 性别    | count(*) |
+------+----------+
| NULL |        1 |
|      |        2 |
| 
    |        8 |
+------+----------+
3 rows in set (0.02 sec)

• 查询各国家人数 HAVING

select home as 国家,count(*) from user group by home;

+------+----------+
| 国家     | count(*) |
+------+----------+
| NULL |        1 |
| 吴   |        4 |
| 蜀   |        3 |
| 魏   |        3 |
+------+----------+
4 rows in set (0.02 sec)

• 分组后的数据筛选

select home,count(*) from user group by home having home='蜀';

select home, count(*) from user where home = '蜀';

+------+----------+
| home | count(*) |
+------+----------+
| 蜀   |        3 |
+------+----------+
1 row in set (0.00 sec)

对比where与having

where是对from后面指定的表进行数据筛选，属于对原始数据的筛选
having是对group by的结果进行筛选