MYSQL-条件判断-IF-IFNULL-CASE

IF

IF(expr1,expr2,expr3), 如expr1为true, 则返回expr2; 否则返回expr3
类似编程中的三目表达式

mysql> select *, if(age>30, 1, 0) from persons;
+----+--------+-----+------+------------------+
| id | name   | age | addr | if(age>30, 1, 0) |
+----+--------+-----+------+------------------+
|  1 | zhang3 |  20 | sz   |                0 |
|  2 | li4    |  33 | gz   |                1 |
|  3 | wang5  |  44 | sz   |                1 |
|  4 | zhao6  |  33 | bj   |                1 |
|  5 | chen7  |  29 | cq   |                0 |
+----+--------+-----+------+------------------+
5 rows in set (0.01 sec)

IFNULL

IFNULL(expr1,expr2) 如expr1为null, 返回expr2；否则返回expr1.

mysql> select ifnull('abc', 'def');
+----------------------+
| ifnull('abc', 'def') |
+----------------------+
| abc                  |
+----------------------+
1 row in set (0.00 sec)

mysql> select ifnull(null, 'def');
+---------------------+
| ifnull(null, 'def') |
+---------------------+
| def                 |
+---------------------+
1 row in set (0.00 sec)

CASE

case语句有两种用法
else语句允许省略

-- 使用枚举值或字段值
CASE case_value/col_name
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END
---------------------------------------------
-- 使用条件表达式
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END

mysql> select *, (
		case addr 
		when 'sz' then '深圳' 
		when 'gz' then '广州' 
		when 'bj' then '北京' 
		else '重庆' 
		end) '地址' 
	   from persons;
+----+--------+-----+------+--------+
| id | name   | age | addr | 地址   |
+----+--------+-----+------+--------+
|  1 | zhang3 |  20 | sz   | 深圳   |
|  2 | li4    |  33 | gz   | 广州   |
|  3 | wang5  |  44 | sz   | 深圳   |
|  4 | zhao6  |  33 | bj   | 北京   |
|  5 | chen7  |  29 | cq   | 重庆   |
+----+--------+-----+------+--------+
5 rows in set (0.00 sec)

mysql> select *, (
		case 
		when age > 30 then 0 
		when (age <=30 and age>20) then 1 
		else 2 
		end) result 
	   from persons;
+----+--------+-----+------+--------+
| id | name   | age | addr | result |
+----+--------+-----+------+--------+
|  1 | zhang3 |  20 | sz   |      2 |
|  2 | li4    |  33 | gz   |      0 |
|  3 | wang5  |  44 | sz   |      0 |
|  4 | zhao6  |  33 | bj   |      0 |
|  5 | chen7  |  29 | cq   |      1 |
+----+--------+-----+------+--------+