MySQL 求一个表的个数

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

一.需求

计算一个表的行数，或计算某个列中值的个数。例如，找到职员总数及每个部门的职员数。

二.解决方案

计数使用count(*) 即可
如果要对表内分组进行计数，可以用group by和count(*)即可
count(*)是表的总数，count(列)是列不为空的总数

代码:

select count(*)
from emp;

select deptno,count(*)
 from emp
group by deptno;

select deptno,count(*),count(comm),count('hello')
 from emp
group by deptno;

测试记录:

mysql> select count(*)
    -> from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
1 row in set (0.01 sec)

mysql> select deptno,count(*)
    ->  from emp
    -> group by deptno;
+--------+----------+
| deptno | count(*) |
+--------+----------+
|     10 |        3 |
|     20 |        5 |
|     30 |        6 |
+--------+----------+
3 rows in set (0.00 sec)

-- count(comm)  只统计comm不为null的个数
mysql> select deptno,count(*),count(comm),count('hello')
    ->  from emp
    -> group by deptno;
+--------+----------+-------------+----------------+
| deptno | count(*) | count(comm) | count('hello') |
+--------+----------+-------------+----------------+
|     10 |        3 |           3 |              3 |
|     20 |        5 |           0 |              5 |
|     30 |        6 |           4 |              6 |
+--------+----------+-------------+----------------+
3 rows in set (0.01 sec)