3.3.4.8 计算行数
数据库通常用于回答以下问题:“table 中某种类型的数据多久出现一次?”例如,您可能想知道您拥有多少只宠物,或者每个所有者拥有多少只宠物,或者您可能想对动物进行各种普查操作。
计算您拥有的动物总数与“ pet
table 中有几行?”是一个相同的问题。因为每只宠物有一个记录。 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
结合使用对于在各种分组下 table 征数据很有用。以下示例显示了执行动物普查操作的不同方法。
每个物种的动物数量:
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
table 示性别未知.)
每个物种和性别组合的动物数量:
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()时无需检索整个 table。例如,仅对狗和猫执行的前一个查询如下所示:
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 节“ MySQL BY GROUP BY 的处理”。有关COUNT(expr)行为和相关优化的信息,请参见第 12.20.1 节“聚合函数描述”。