計算行數 · mysql 8.0 中文文檔

#### 3.3.4.8 計算行數數據庫通常用于回答以下問題, “某一類型的數據在表中出現的頻率是多少?” 例如, 你可能想知道你有多少寵物或者每個主人有多少寵物或者你可能對動物進行各種各樣的普查. 計算你擁有的動物總數與“ pet 表中有多少條紀錄?” 是同樣的問題, 因為每只寵物只有一條紀錄. [COUNT(*)](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count) 計算行數, 因此你的動物數量的查詢如下: ```sql mysql> SELECT COUNT(*) FROM pet; +----------+ | COUNT(*) | +----------+ | 9 | +----------+ ``` 之前, 你檢索了擁有寵物的人的名字. 如果你想找出每個主人擁有多少寵物, 你可以使用 [`COUNT()`](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count): ```sql mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner; +--------+----------+ | owner | COUNT(*) | +--------+----------+ | Benny | 2 | | Diane | 2 | | Gwen | 3 | | Harold | 2 | +--------+----------+ ``` 前面的查詢使用 `GROUP BY` 對每個 `owner` 的所有紀錄進行分組. [`COUNT()`](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count) 和 `GROUP BY` 結合使用, 對描述不同分組下的數據非常有用. 下面的例子展示了執行動物普查的不同方法. 每種動物的數量: ```sql mysql> SELECT species, COUNT(*) FROM pet GROUP BY species; +---------+----------+ | species | COUNT(*) | +---------+----------+ | bird | 2 | | cat | 2 | | dog | 3 | | hamster | 1 | | snake | 1 | +---------+----------+ ``` 每個性別的動物數量: ```sql mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex; +------+----------+ | sex | COUNT(*) | +------+----------+ | NULL | 1 | | f | 4 | | m | 4 | +------+----------+ ``` (在這個輸出中, `NULL` 表示性別未知.) 按照動物的種類和性別組合計算數量: ```sql 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()`](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count) 時檢索整個表. 例如, 在上一個查詢, 僅對貓和狗進行查詢, 如下所示: ```sql 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 | +---------+------+----------+ ``` 或者如果你想知道已知性別的動物每個性別的數量: ```sql 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()`](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count) 值之外查詢其他列, `GROUP BY` 子句應該用來命名為相同的列. 否則,會發生以下情況: 如果 [`ONLY_FULL_GROUP_BY`](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by) SQL 模式啟用了, 則會出現以下錯誤: ```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`](https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_only_full_group_by) 未啟用, 查詢會將所有的紀錄當做單個組來處理t, 但是為每個指定的列選擇的值是不確定的. 服務器可以從任意行中自由選擇值: ```sql 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) ``` 參閱 [Section 12.20.3, “MySQL GROUP BY 處理”](https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html). 參閱 [Section 12.20.1, 聚合 (GROUP BY) 函數描述”](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html) 獲取有關 [`COUNT(expr)`](https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_count) 行為和相關優化的信息.