1.3.4.8 行数

在使用 MySQL 时，经常会使碰到，查看某一种类型的数据有多少行？比方我们的例子中，想知道每个 owner 各有多少只宠物？计算多少只宠物和计算表中有多少行数据，其实是一个概念。下面我们使用 COUNT(*) 来实现。

检索拥有宠物的总量

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

检索每个 owner 各有多少只宠物

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+
4 rows in set (0.00 sec)

检索每个 species 各有多少只宠物

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

检索每个 sex 各有多少只宠物

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+
3 rows in set (0.00 sec)

NULL 表示未知 sex

检索每个species、sex 各有多少只宠物

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 |
+---------+------+----------+
8 rows in set (0.00 sec)

上面的例子中，您仅想要 species 为 cat 、dog 的数据

mysql> SELECT species, sex, count(*) 
    -> FROM pet
    -> WHERE species = 'cat' or species = 'dog'
    -> GROUP BY species,sex;
+---------+------+----------+
| species | sex  | count(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+
4 rows in set (0.00 sec)

如果您想过滤掉 sex 为 NULL 的数据

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 |
+---------+------+----------+
7 rows in set (0.00 sec)

关于 ONLY_FULL_GROUP_BY

ONLY_FULL_GROUP_BY 是 MySQL 提供的一个 sql_mode，通过这个sql_mode来提供SQL语句 GROUP BY 合法性的检查，在MySQL的sql_mode是非 ONLY_FULL_GROUP_BY 语义时。一条SELECT语句，MySQL允许target list中输出的表达式是除聚集函数或group by column以外的表达式 。

而对于语义限制都比较严谨的多家数据库，如SQLServer、Oracle、PostgreSql都不支持select target list中出现语义不明确的列，这样的语句在这些数据库中是会被报错的，所以从MySQL 5.7版本开始修正了这个语义，就是我们所说的 ONLY_FULL_GROUP_BY 语义，例如查看MySQL 5.7默认的sql_mode如下：

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------+
| @@sql_mode                                                                    |
+------------------------------------------------------------------------------ +
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,
  ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION         |
+-------------------------------------------------------------------------------+
1 row in set (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='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 
Query OK, 0 rows affected (0.00 sec)

再次执行查询

mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Harold |        9 |
+--------+----------+
1 row in set (0.00 sec)

关于 ONLY_FULL_GROUP_BY 后续的章节将详细介绍。