數(shù)據(jù)庫(kù)經(jīng)常用于回答這個(gè)問(wèn)題，“某個(gè)類(lèi)型的數(shù)據(jù)在表中出現(xiàn)的頻度?”例如，你可能想要知道你有多少寵物属提，或每位主人有多少寵物寂汇，或你可能想要對(duì)你的動(dòng)物進(jìn)行各種類(lèi)型的普查病往。

計(jì)算你擁有動(dòng)物的總數(shù)目與“在pet表中有多少行?”是同樣的問(wèn)題，因?yàn)槊總€(gè)寵物有一個(gè)記錄骄瓣。COUNT(*)函數(shù)計(jì)算行數(shù)停巷，所以計(jì)算動(dòng)物數(shù)目的查詢(xún)應(yīng)為：

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

• 在前面，你檢索了擁有寵物的人的名字榕栏。如果你想要知道每個(gè)主人有多少寵物畔勤，你可以使用COUNT( )函數(shù)：

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

注意，使用GROUP BY對(duì)每個(gè)owner的所有記錄分組扒磁，沒(méi)有它庆揪，你會(huì)得到錯(cuò)誤消息：

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以各種方式分類(lèi)你的數(shù)據(jù)。下列例子顯示出進(jìn)行動(dòng)物普查操作的不同方式妨托。

• 每種動(dòng)物的數(shù)量：

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+

• 每種性別的動(dòng)物數(shù)量：

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+

（在這個(gè)輸出中缸榛，NULL表示“未知性別”。）

• 按種類(lèi)和性別組合的動(dòng)物數(shù)量：

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( )兰伤，你不必檢索整個(gè)表内颗。例如, 前面的查詢(xún)，當(dāng)只對(duì)狗和貓進(jìn)行時(shí)敦腔，應(yīng)為：
  SELECT species, sex, COUNT() FROM pet GROUP BY species, sex;
  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 |
  +---------+------+----------+
  SELECT species, sex, COUNT() FROM pet GROUP BY species, sex;

• 如果你僅需要知道已知性別的按性別的動(dòng)物數(shù)目：

 SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
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 |
+---------+------+----------+