COUNT()

COUNT() 函数返回匹配指定条件的行数。

COUNT 实例 Ⅰ

统计courses表中课程在2020年1月到5月之间的课程数量

SELECT COUNT(*) AS number_of_course
FROM courses
WHERE created_at BETWEEN '2020-01-01' AND '2020-05-31';

执行输出结果

mysql> SELECT COUNT(*) AS number_of_course
    -> FROM courses
    -> WHERE created_at BETWEEN '2020-01-01' AND '2020-05-31';
+------------------+
| number_of_course |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

其中AS是给COUNT(*)取的别名number_of_course

COUNT 实例 Ⅱ

COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目

统计课程表courses中不同teacher_id的记录数量

SELECT COUNT(DISTINCT teacher_id) AS number_of_teacher
FROM courses;

执行输出结果

mysql> SELECT COUNT(DISTINCT teacher_id) AS number_of_teacher
    -> FROM courses;
+-------------------+
| number_of_teacher |
+-------------------+
|                 3 |
+-------------------+
1 row in set (0.00 sec)

COUNT 总结

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）

语法

SELECT COUNT(column_name) 
FROM table_name;

COUNT(*) 函数返回表中的记录数

语法

SELECT COUNT(*) FROM table_name;

注意： COUNT(column_name) 与 COUNT(*) 的区别

COUNT(column_name) 中，如果 column_name 字段中的值为 null，则计数不会增加，而如果字段值为空字符串”“，则字段值会加一

COUNT() 中，除非整个记录全为 null，则计数不会增加，如果存在某一个记录不为 null，或者为空字符串”“，计数值都会加一。正常来说，表都会有主键，而主键不为空，所以 COUNT(\) 在有主键的表中等同于 COUNT(PRIMARY_KEY)，即查询有多少条记录

COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目

语法

SELECT COUNT(DISTINCT column_name) FROM table_name;

练习题：COUNT

题目描述：查询教师年龄在20到28岁之间，且国籍为中国或英国的教师人数，结果列名用teacher_count显示

SELECT COUNT(*) AS teacher_count
FROM teachers
WHERE (age BETWEEN 20 AND 28) AND (country IN ('CN','UK'));

目标输出结果

mysql> SELECT COUNT(*) AS teacher_count
    -> FROM teachers
    -> WHERE (age BETWEEN 20 AND 28) AND (country IN ('CN','UK'));
+---------------+
| teacher_count |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)