在MariaDB數(shù)據(jù)庫(kù)中，COUNT()函數(shù)用于返回表達(dá)式的計(jì)數(shù)/行數(shù)。

語(yǔ)法：

SELECT COUNT(aggregate_expression)  
FROM tables  
[WHERE conditions];

注：COUNT()函數(shù)只計(jì)算NOT NULL值。

示例：

假設(shè)有一個(gè)students表，有以下數(shù)據(jù)：

MariaDB [testdb]> select * from students;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
|          1 | Maxsu        | Haikou          | 2017-01-07     |
|          3 | JMaster      | Beijing         | 2016-05-07     |
|          4 | Mahesh       | Guangzhou       | 2016-06-07     |
|          5 | Kobe         | Shanghai        | 2016-02-07     |
|          6 | Blaba        | Shengzhen       | 2016-08-07     |
|          7 | Maxsu        | Sanya           | 2017-08-08     |
|          8 | Maxsu        | Haikou          | 2015-11-17     |
+------------+--------------+-----------------+----------------+
7 rows in set (0.00 sec)

從students表中統(tǒng)計(jì)student_id：

SELECT COUNT(student_id) FROM Students;
-- 或者
SELECT COUNT(*) FROM Students;

執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -

MariaDB [testdb]> SELECT COUNT(student_id) FROM Students;
+-------------------+
| COUNT(student_id) |
+-------------------+
|                 7 |
+-------------------+
1 row in set (0.07 sec)

1. COUNT()函數(shù)與單一表達(dá)式

統(tǒng)計(jì)student_name是Maxsu或Kobe的學(xué)生人數(shù)。參考以下查詢語(yǔ)句 -

SELECT COUNT(*) AS "Number of Students"  
FROM Students  
WHERE student_name in ('Maxsu', 'Kobe');

執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -

MariaDB [testdb]> SELECT COUNT(*) AS "Number of Students"
    -> FROM Students
    -> WHERE student_name in ('Maxsu', 'Kobe');
+--------------------+
| Number of Students |
+--------------------+
|                  4 |
+--------------------+
1 row in set (0.00 sec)

2. COUNT()函數(shù)與Distinct子句

DISTINCT子句與COUNT()函數(shù)一起使用以防止重復(fù)計(jì)數(shù)。它只包含原始記錄。

SELECT COUNT(DISTINCT student_name) AS "Number of Unique names"  
FROM Students  
WHERE student_name in ('Maxsu', 'Kobe');

執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -

MariaDB [testdb]> SELECT COUNT(DISTINCT student_name) AS "Number of Unique names"
    -> FROM Students
    -> WHERE student_name in ('Maxsu', 'Kobe');
+------------------------+
| Number of Unique names |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.08 sec)

從查詢結(jié)果中可以看到，比上一個(gè)示例少了兩行。

3. COUNT()函數(shù)與NULL值

為了更好地演示COUNT()函數(shù)對(duì)NULL值的處理，這里再插入兩條記錄 -

-- 修改表字段接受NULL默認(rèn)值
ALTER TABLE students CHANGE student_address student_address varchar(32) default NULL;
-- 插入第1行
INSERT INTO students  
(student_name, student_address, admission_date)  
VALUES('Himin',NULL,'2017-01-07 00:00:00');

-- 插入第2行
INSERT INTO students  
(student_name, student_address, admission_date)  
VALUES('Hiavg',NULL,NULL);

執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -

MariaDB [testdb]> select * from students;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
|          1 | Maxsu        | Haikou          | 2017-01-07     |
|          3 | JMaster      | Beijing         | 2016-05-07     |
|          4 | Mahesh       | Guangzhou       | 2016-06-07     |
|          5 | Kobe         | Shanghai        | 2016-02-07     |
|          6 | Blaba        | Shengzhen       | 2016-08-07     |
|          7 | Maxsu        | Sanya           | 2017-08-08     |
|          8 | Maxsu        | Haikou          | 2015-11-17     |
|          9 | Himin        | NULL            | 2017-01-07     |
|         10 | Hiavg        | NULL            | NULL           |
+------------+--------------+-----------------+----------------+
9 rows in set (0.00 sec)

現(xiàn)在來(lái)看看使用count()函數(shù)來(lái)測(cè)試對(duì)NULL值的計(jì)算效果。

select count(student_address) from students;

執(zhí)行上面查詢語(yǔ)句，得到以下結(jié)果 -

MariaDB [testdb]> select count(student_address) from students;
+------------------------+
| count(student_address) |
+------------------------+
|                      7 |
+------------------------+
1 row in set (0.00 sec)

可以看到，COUNT(student_address)函數(shù)它并沒(méi)有統(tǒng)計(jì)包含NULL值的行。