备注:测试数据库版本为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)