分组查询
mysql> select countrycode as total fromCity where id<10;
+-------+
| total |
+-------+
| AFG |
| AFG |
| AFG |
| AFG |
| NLD |
| NLD |
| NLD |
| NLD |
| NLD |
+-------+
9 rows in set (0.00 sec)
mysql> select countrycode,count(*) astotal from City where id<10 group by countrycode;
+-------------+-------+
| countrycode | total |
+-------------+-------+
| AFG | 4 |
| NLD | 5 |
+-------------+-------+
2 rows in set (0.00 sec)
把相同的字段进行分组，并对分组内的数据进行统计。
使用having过滤分组
Having用于分组之后过滤数据，where用于分组之前选择数据。
mysql> select countrycode,count(*) astotal from City where id<101 group by countrycode;
+-------------+-------+
| countrycode | total |
+-------------+-------+
| AFG | 4 |
| AGO | 5 |
| AIA | 2 |
| ALB | 1 |
| AND | 1 |
| ANT | 1 |
| ARE | 5 |
| ARG | 32 |
| ASM | 2 |
| ATG | 1 |
| DZA | 18 |
| NLD | 28 |
+-------------+-------+
12 rows in set (0.01 sec)
mysql> select countrycode,count(*) astotal from City where id<101 group by countrycode having count(*)>10;
+-------------+-------+
| countrycode | total |
+-------------+-------+
| ARG | 32 |
| DZA | 18 |
| NLD | 28 |
+-------------+-------+
3 rows in set (0.00 sec)
先选择数据，然后分组，然后having过滤数据。
在group by后求和
mysql> select countrycode,count(*) astotal from City where id<10 group by countrycode with rollup;
+-------------+-------+
| countrycode | total |
+-------------+-------+
| AFG | 4 |
| NLD | 5 |
| NULL | 9 |
+-------------+-------+
3 rows in set (0.00 sec)
在最后增加一行显示求和结果。
多字段分组
先按照第一个字段进行分组，按照分组内容进行第二个字段的分组。
mysql> select countrycode,district fromCity where id<10;
+-------------+---------------+
| countrycode | district |
+-------------+---------------+
| AFG | Kabol |
| AFG | Qandahar |
| AFG | Herat |
| AFG | Balkh |
| NLD | Noord-Holland |
| NLD | Zuid-Holland |
| NLD | Zuid-Holland |
| NLD | Utrecht |
| NLD | Noord-Brabant |
+-------------+---------------+
9 rows in set (0.00 sec)
mysql> select countrycode,district fromCity where id<10 group by countrycode,district;
+-------------+---------------+
| countrycode | district |
+-------------+---------------+
| AFG | Balkh |
| AFG | Herat |
| AFG | Kabol |
| AFG | Qandahar |
| NLD | Noord-Brabant |
| NLD | Noord-Holland |
| NLD | Utrecht |
| NLD | Zuid-Holland |
+-------------+---------------+
8 rows in set (0.00 sec)
注：这里有一个知识点，5.7里sql_mode=only_full_group_by是默认打开的，不允许下面这样查询。
select * from City where id<10 group by countrycode,district; 列名countrycode,district需要在select后写明。除了countrycode,district，多余的列名出现会报错。
mysql5.6开启方法
mysql> setsql_mode=concat('ONLY_FULL_GROUP_BY,', @@sql_mode );
Query OK, 0 rows affected (0.00 sec)
限制查询结果行数量
mysql> select * from City limit 3;
+----+----------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
| 1| Kabul | AFG | Kabol | 1780000 |
| 2| Qandahar | AFG | Qandahar | 237500 |
| 3| Herat | AFG | Herat | 186800 |
+----+----------+-------------+----------+------------+
3 rows in set (0.00 sec)
前3行
mysql> select * from City limit 10,10;
+----+-------------------+-------------+---------------+------------+
| ID | Name | CountryCode | District | Population |
+----+-------------------+-------------+---------------+------------+
| 11 | Groningen | NLD | Groningen | 172701 |
| 12 | Breda | NLD | Noord-Brabant | 160398 |
| 13 | Apeldoorn | NLD | Gelderland | 153491 |
| 14 | Nijmegen | NLD | Gelderland | 152463 |
| 15 | Enschede | NLD | Overijssel | 149544 |
| 16 | Haarlem | NLD | Noord-Holland | 148772 |
| 17 | Almere | NLD | Flevoland | 142465 |
| 18 | Arnhem | NLD | Gelderland | 138020 |
| 19 | Zaanstad | NLD | Noord-Holland | 135621 |
| 20 | 麓s-Hertogenbosch | NLD | Noord-Brabant | 129170 |
+----+-------------------+-------------+---------------+------------+
10 rows in set (0.00 sec)
从10开始往后的10行。
