MYSQL - 计算行数

1. COUNT(*)计算table的行数

计算pet表中的动物

mysql> select count(*) from pet;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)


mysql>

计算每个人有多少宠物

mysql> select owner, count(*) from pet group by owner;
+--------+----------+
| owner  | count(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+
4 rows in set (0.04 sec)


mysql>

每种类型的宠物数量

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)


mysql>

每种性别的宠物数量

mysql> select sex, count(*) from pet group by sex;
+------+----------+
| sex  | count(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+
3 rows in set (0.00 sec)


mysql>

根据类型和性别区分的每种宠物数量

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)


mysql>

当使用count()时，也可以不从整个表里取。例如上一个查询，可以只是针对dogs和cats

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


mysql>

已知性别的宠物中每种类型和性别宠物的数量

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)


mysql>

如果在select语句中有除了count()以外的列名，group by子句中应该指明相同的列名，否则会发生以下情况：

• 如果ONLY_FULL_GROUP_BY 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
mysql>

• 如果ONLY_FULL_GROUP_BY模式没打开，查询会将所有行当成一个单独的组，但是每列中的值是不确定的，MySQL可以自由的选择任何行的值

mysql> set sql_mode = "";
Query OK, 0 rows affected (0.00 sec)


mysql> select owner, count(*) from pet;
+-------+----------+
| owner | count(*) |
+-------+----------+
| Gwen  |        9 |
+-------+----------+
1 row in set (0.00 sec)


mysql>