SQL中COUNT()函数介绍

超、凢脫俗 2022-05-15 01:49 401阅读 0赞

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

# 1 概述 #

## 1.1 What ##

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

## 1.2 How ##

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

![70][]

**语法格式：**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)

[70]: /images/20220515/c7dcfacd0a2e493b9a7699181e2c2a59.png