数据库教程-mariadb教程-MariaDB Count()函数

跨零代码为大家提供高品质的解决方案,请大家多多来访,跨零不胜感激,在此谢过。

在MariaDB数据库中,COUNT()函数用于返回表达式的计数/行数。

语法:

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

示例:

假设有一个students表,有以下数据:

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表中统计student_id

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

执行上面查询语句,得到以下结果 –

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

1. COUNT()函数与单一表达式

统计student_nameMaxsuKobe的学生人数。参考以下查询语句 –

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

执行上面查询语句,得到以下结果 –

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()函数与Distinct子句

DISTINCT子句与COUNT()函数一起使用以防止重复计数。它只包含原始记录。

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

执行上面查询语句,得到以下结果 –

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) 

从查询结果中可以看到,比上一个示例少了两行。

3. COUNT()函数与NULL值

为了更好地演示COUNT()函数对NULL值的处理,这里再插入两条记录 –

-- 修改表字段接受NULL默认值 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); 

执行上面查询语句,得到以下结果 –

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) 

现在来看看使用count()函数来测试对NULL值的计算效果。

select count(student_address) from students; 

执行上面查询语句,得到以下结果 –

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

可以看到,COUNT(student_address)函数它并没有统计包含NULL值的行。

  

从零到一,创造未来!跨零综合IT问题解决服务站,欢迎你的到来。Oracle数据库教程 mariadb教程 只为你绽放。

本文固定链接: http://kua0.com/2019/01/31/数据库教程-mariadb教程-mariadb-count函数/

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注