计数行
数据库通常用于回答“表中某种类型的数据出现频率多少?”的问题。例如,你可能想知道你拥有多少只宠物,或每个拥有者拥有多少只宠物,或者你可能想要对你的动物进行各种类型的普查操作。
计算你拥有的动物总数与“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 | +--------+----------+
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。