3.3.4.8.計數行
3.3.4.8. 計數行
數據庫經常用于回答這個問題,“某個類型的數據在表中出現的頻度?”例如,你可能想要知道你有多少寵物,或每位主人有多少寵物,或你可能想要對你的動物進行各種類型的普查。
計算你擁有動物的總數目與“在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的所有記錄分組,沒有它,你會得到錯誤消息:
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause
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 |
+---------+------+----------+