- MariaDB简介
- MariaDB功能特点
- MariaDB安装
- MariaDB数据类型
- 数据库和表
- CRUD操作
- MariaDB子句
- 聚合函数
- MariaDB连接
- MariaDB操作符
- MariaDB高级部分
MariaDB Count()函数
在MariaDB数据库中,COUNT()
函数用于返回表达式的计数/行数。
语法:
SELECT COUNT(aggregate_expression) FROM tables [WHERE conditions];
注:
COUNT()
函数只计算NOT NULL
值。
示例:
假设有一个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 | +------------+--------------+-----------------+----------------+ 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 | +-------------------+ row in set (0.07 sec)
1. COUNT()函数与单一表达式
统计student_name
是Maxsu
或Kobe
的学生人数。参考以下查询语句 -
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 | +--------------------+ 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 | +------------------------+ 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 | +------------+--------------+-----------------+----------------+ 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 | +------------------------+ row in set (0.00 sec)
可以看到,COUNT(student_address)
函数它并没有统计包含NULL
值的行。
上一篇:MariaDB From子句
下一篇:MariaDB Sum()函数
扫描二维码
程序员编程王