关于or、or null、count的介绍
or是短路逻辑或运算,当左边为true时,则不再继续运算右边,当左边为false时才继续运算右边;在mysql内部中true、false用1、0表示;exp or null先计算exp如果为true则直接返回true(1),否则继续运算右边,而右边是null,遇到null直接返回null;count(exp | 字段)函数统计指定表达式或者字段中非null记录的个数
1. MySQL 中 TRUE 为 1, False 为 0
mysql> SELECT TRUE, FALSE; +------+-------+ | TRUE | FALSE | +------+-------+ | 1 | 0 | +------+-------+ 1 row in set (0.00 sec)
2. 0 or 则会继续运算右边;遇到 NULL 直接返回 NULL
mysql> SELECT 1 OR NULL, 0 OR NULL, 1 AND NULL; +-----------+-----------+------------+ | 1 OR NULL | 0 OR NULL | 1 AND NULL | +-----------+-----------+------------+ | 1 | NULL | NULL | +-----------+-----------+------------+ 1 row in set (0.00 sec)
3. COUNT() 在统计列值时不统计 NULL,COUNT(NULL)的值为0; 但会统计 FALSE (NULL 和 FALSE 要区分),所以 COUNT(exp) 即使 exp 为 FALSE,COUNT也会进行统计。
mysql> SELECT COUNT(TRUE), COUNT(FALSE), COUNT(1), COUNT(0), COUNT(NULL); +-------------+--------------+----------+----------+-------------+ | COUNT(TRUE) | COUNT(FALSE) | COUNT(1) | COUNT(0) | COUNT(NULL) | +-------------+--------------+----------+----------+-------------+ | 1 | 1 | 1 | 1 | 0 | +-------------+--------------+----------+----------+-------------+ 1 row in set (0.00 sec)
4.
mysql> SELECT COUNT(TRUE OR NULL), COUNT(1 OR NULL), COUNT(FALSE OR NULL), COUNT(0 OR NULL), COUNT(NULL OR NULL); +---------------------+------------------+----------------------+------------------+---------------------+ | COUNT(TRUE OR NULL) | COUNT(1 OR NULL) | COUNT(FALSE OR NULL) | COUNT(0 OR NULL) | COUNT(NULL OR NULL) | +---------------------+------------------+----------------------+------------------+---------------------+ | 1 | 1 | 0 | 0 | 0 | +---------------------+------------------+----------------------+------------------+---------------------+ 1 row in set (0.00 sec)
示例: 查询items表,统计color字段有多少种颜色,即统计某个字段不同值的个数。
SELECT COUNT(color = 'red' OR NULL) AS 'red', COUNT(color = 'blue' OR NULL) AS 'blue' FROM items; SELECT SUM(IF(color = 'red', 1, 0)) AS 'red', SUM(IF(color = 'blue', 1, 0)) AS 'blue' FROM items; SELECT SUM(color = 'red') AS 'red', SUM(color = 'blue') AS 'blue' FROM items;
mysql if 语句:
IF(expr1,expr2,expr3) : 如果expr1的值为true,则返回expr2的值,如果expr1的值为false,则返回expr3的值。
MySQL SUM 语句:
(1)sum()函数里面的参数是列名的时候,是计算列名的值的相加,而不是有值项的总数。 (2)sum(条件表达式),如果记录满足条件表达式就加1,统计满足条件的行数
参考链接: https://blog.csdn.net/E_N_T_J/article/details/104108375