SQL中COUNT()函数介绍

本文主要介绍SQL(Structured Query Language)中COUNT()函数的相关知识，同时通过用法示例介绍COUNT()函数的使用方法。

1 概述

1.1 What

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

1.2 How

示例数据库的表信息如下，接下来以此表内容为基础，展示COUNT()函数的用法。

语法格式：COUNT(column_name)

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

用法示例如下：

mysql> select COUNT(camp) as num from roles where role_id in (1, 7, 8);
+-----+
| num |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)
 
mysql> 
mysql> select COUNT(camp) as num from roles where role_id in (1, 8, 9);
+-----+
| num |
+-----+
|   2 |
+-----+
1 row in set (0.01 sec)
 
mysql> 

语法：COUNT(*)

COUNT(*)函数返回符合查询条件的表的记录数。

用法示例如下：

mysql> select COUNT(*) as num from roles;
+-----+
| num |
+-----+
|   9 |
+-----+
1 row in set (0.00 sec)

mysql> 
mysql> select COUNT(*) as num from roles where role_id in (1, 8, 9);
+-----+
| num |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)

mysql> 

语法格式：COUNT(DISTINCT column_name)

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

用法示例如下：

mysql> select COUNT(camp) as num from roles where role_id in (1, 2, 3);
+-----+
| num |
+-----+
|   3 |
+-----+
1 row in set (0.01 sec)

mysql> 
mysql> select COUNT(DISTINCT camp) as num from roles where role_id in (1, 2, 3);
+-----+
| num |
+-----+
|   2 |
+-----+
1 row in set (0.01 sec)