MariaDB中COUNT()函数的作用及用法诀窍是什么
|
这篇文章主要介绍了MariaDB中COUNT()函数的作用及用法技巧是什么相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇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 | +------------+--------------+-----------------+----------------+ 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_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 | +--------------------+ 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值的行。 （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
