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 |
+----+--------+-----+------+--------+