数据库经常用来回答这样的问题:“某一类型的数据在一个表中多久发生一次?”例如,你可能想知道你有多少宠物,或者每个主人有多少宠物,或者你想对你的动物进行各种普查操作。
计算你所拥有的动物总数与“宠物表中有多少行”是相同的问题。“因为每个宠物都有一条记录。count(*)计数行数,因此查询您的动物的查询如下所示:
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
对于有NULL值的列,count不计算在内
mysql> select count(deat
+--------------+
| count(death) |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> select count(*) f
+----------+
| count(*) |
+----------+
| 10 |
+----------+
1 row in set (0.00 sec)
早些时候,你取回了拥有宠物的人的名字。你可以使用count(),如果你想找出每个人有多少宠物:
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+--------+----------+
前面的查询使用组来为每个所有者分组所有记录。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 |
+------+----------+
每种物种和性别组合的动物数:
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 |
+---------+------+----------+