计数行 count()
数据库通常用于回答以下问题:“表中某种类型的数据多久出现一次?”例如,您可能想知道您拥有多少只宠物,或者每个所有者拥有多少只宠物,或者您可能想对动物进行各种普查操作。
计算您拥有的动物总数与“表中有多少行pet
?”,因为每只宠物有一个记录。COUNT(*)
计算行数,因此查询动物数量的查询如下所示:
mysql>SELECT COUNT(*)FROM pet; +---------- + | COUNT(*) | +---------- + | 9 | +---------- +
之前,您检索了拥有宠物的人的名字。COUNT()
如果要找出每个主人有多少只宠物,可以使用:
mysql>SELECT owner , COUNT(*)FROM petGROUP BY owner ; +-------- +---------- + | owner | COUNT(*) | +-------- +---------- + | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +-------- +---------- +
前面的查询用于GROUP BY
将每个记录的所有记录分组owner
。使用的COUNT()
结合GROUP BY
是在各种分组表征您的数据非常有用。以下示例显示了执行动物普查操作的不同方法。
每个物种的动物数量:
mysql>SELECT species, COUNT(*)FROM petGROUP BY species; +--------- +---------- + | species | COUNT(*) | +--------- +---------- + | bird | 2 | | cat | 2 | | dog | 3 | | hamster | 1 | | snake | 1 | +--------- +---------- +
每性别的动物数:
mysql>SELECT sex, COUNT(*)FROM petGROUP BY sex; +------ +---------- + | sex | COUNT(*) | +------ +---------- + | NULL | 1 | | f | 4 | | m | 4 | +------ +---------- +
(在此输出中,NULL
表明性别未知。)
每个物种和性别组合的动物数量:
mysql>SELECT species, sex, COUNT(*)FROM petGROUP 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 | +--------- +------ +---------- +
使用时无需检索整个表COUNT()
。例如,仅对狗和猫执行的前一个查询如下所示:
mysql>SELECT species, sex, COUNT(*)FROM petWHERE species = 'dog' OR species = 'cat'GROUP BY species, sex; +--------- +------ +---------- + | species | sex | COUNT(*) | +--------- +------ +---------- + | cat | f | 1 | | cat | m | 1 | | dog | f | 1 | | dog | m | 2 | +--------- +------ +---------- +
或者,如果只想知道已知性别的动物,则按性别划分的动物数:
mysql>SELECT species, sex, COUNT(*)FROM petWHERE sex IS NOT NULLGROUP 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 | +--------- +------ +---------- +
如果除了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如果
ONLY_FULL_GROUP_BY
未启用,则通过将所有行视为一个组来处理查询,但是为每个命名列选择的值是不确定的。服务器可以从任何行中自由选择值:mysql>
SET sql_mode = ''; Query OK, 0 rows affected (0.00 sec) mysql>SELECT owner , COUNT(*)FROM pet; +-------- +---------- + | owner | COUNT(*) | +-------- +---------- + | Harold | 8 | +-------- +---------- + 1 row in set (0.00 sec)
另请参见“ GROUP BY的MySQL处理”。有关行为和相关优化的信息,请参见“聚合功能描述(GROUP BY)”。COUNT(expr)