数据库通常用于回答以下问题:“ 表中某种类型的数据多久出现一次?” 例如,您可能想知道您拥有多少只宠物,或者每个所有者拥有多少只宠物,或者您可能想对动物进行各种普查操作。
计算您拥有的动物总数与“ 表中有多少行 pet
?”,因为每只宠物有一个记录。COUNT(*)
计算行数,因此查询动物数量的查询如下所示:
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
之前,您检索了拥有宠物的人的名字。COUNT()
如果要查找每个所有者拥有多少宠物,可以使用:
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+--------+----------+
前面的查询用于GROUP BY
将每个记录的所有记录分组owner
。使用的 COUNT()
结合 GROUP BY
是在各种分组表征您的数据非常有用。以下示例显示了执行动物普查操作的不同方法。
每个物种的动物数量:
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird | 2 |
| cat | 2 |
| dog | 3 |
| hamster | 1 |
| snake | 1 |
+---------+----------+
每性别的动物数:
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex | COUNT(*) |
+------+----------+
| NULL | 1 |
| f | 4 |
| m | 4 |
+------+----------+
(在此输出中,NULL
表明性别未知。)
每个物种和性别组合的动物数量:
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 |
+---------+------+----------+
使用时无需检索整个表 COUNT()
。例如,仅对狗和猫执行的前一个查询如下所示:
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 |
+---------+------+----------+
或者,如果只想知道已知性别的动物,则按性别划分的动物数:
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 |
+---------+------+----------+
如果您为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)
另请参见第12.20.3节“ GROUP BY的MySQL处理”。有关 行为和相关优化的信息 ,请参见 第12.20.1节“集合函数描述”。 COUNT(
expr
)