1. COUNT(*)计算table的行数
计算pet表中的动物
mysql> select count(*) from pet; +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.00 sec) mysql>
计算每个人有多少宠物
mysql> select owner, count(*) from pet group by owner; +--------+----------+ | owner | count(*) | +--------+----------+ | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +--------+----------+ 4 rows in set (0.04 sec) mysql>
每种类型的宠物数量
mysql> select species, count(*) from pet group by species; +---------+----------+ | species | count(*) | +---------+----------+ | bird | 2 | | cat | 2 | | dog | 3 | | hamster | 1 | | snake | 1 | +---------+----------+ 5 rows in set (0.00 sec) mysql>
每种性别的宠物数量
mysql> select sex, count(*) from pet group by sex; +------+----------+ | sex | count(*) | +------+----------+ | NULL | 1 | | f | 4 | | m | 4 | +------+----------+ 3 rows in set (0.00 sec) mysql>
根据类型和性别区分的每种宠物数量
mysql> select species, sex, count(*) from pet group by species, sex; +---------+------+----------+ | species | sex | count(*) | +---------+------+----------+ | bird | NULL | 1 | | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+ 8 rows in set (0.00 sec) mysql>
当使用count()时,也可以不从整个表里取。例如上一个查询,可以只是针对dogs和cats
mysql> select species, sex, count(*) from pet where species='dog' or species='cat' group by species, sex; +---------+------+----------+ | species | sex | count(*) | +---------+------+----------+ | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | +---------+------+----------+ 4 rows in set (0.00 sec) mysql>
已知性别的宠物中每种类型和性别宠物的数量
mysql> select species, sex, count(*) from pet where sex is not null group by species, sex; +---------+------+----------+ | species | sex | count(*) | +---------+------+----------+ | bird | f | 1 | | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | | hamster | f | 1 | | snake | m | 1 | +---------+------+----------+ 7 rows in set (0.00 sec) mysql>
如果在select语句中有除了count()以外的列名,group by子句中应该指明相同的列名,否则会发生以下情况:
- 如果ONLY_FULL_GROUP_BY SQL 模式打开,会出现一个错误
mysql> set sql_mode = 'only_full_group_by'; Query OK, 0 rows affected (0.00 sec) mysql> select owner, count(*) from pet; ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'menagerie.pet.owner'; this is incompatible with sql_mode=only_full_group_by mysql>
- 如果ONLY_FULL_GROUP_BY模式没打开,查询会将所有行当成一个单独的组,但是每列中的值是不确定的,MySQL可以自由的选择任何行的值
mysql> set sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql> select owner, count(*) from pet; +-------+----------+ | owner | count(*) | +-------+----------+ | Gwen | 9 | +-------+----------+ 1 row in set (0.00 sec) mysql>