MySQL基础教程13——函数——流程控制函数

if(条件判断)

if(条件判断式,value1,value2);

mysql> select if(true,'ok','not');
+---------------------+
| if(true,'ok','not') |
+---------------------+
| ok                  |
+---------------------+
1 row in set
mysql> select if(false,'ok','not');
+----------------------+
| if(false,'ok','not') |
+----------------------+
| not                  |
+----------------------+
1 row in set
复制代码

条件判断式为true返回value1,为false则返回value2。

ifnull(空判断)

select ifnull(value1,value2);

mysql> select ifnull('hello','isnull');
+--------------------------+
| ifnull('hello','isnull') |
+--------------------------+
| hello                    |
+--------------------------+
1 row in set
mysql> select ifnull(null
,'isnull');
+-----------------------+
| ifnull(null,'isnull') |
+-----------------------+
| isnull                |
+-----------------------+
1 row in set
复制代码

如果value1为null则返回value2,不为null则返回value1,这里需要注意如果value1为""(空字符)不属于null返回空字符本身。

case … when … then … end

case 字段 when 条件 then 语句 end;

+------+-----+
| name | age |
+------+-----+
| 张三 |  20 |
| 李四 |  38 |
| 王五 |  40 |
+------+-----+
mysql> select
    -> name,
    -> case age when 20 then '今年20岁' when 38 then '今年38岁' when 40 then '今年40岁' end
    -> from text;
+------+--------------------------------------------------------------------------------------+
| name | case age when 20 then '今年20岁' when 38 then '今年38岁' when 40 then '今年40岁' end |
+------+--------------------------------------------------------------------------------------+
| 张三 | 今年20岁                                                                             |
| 李四 | 今年38岁                                                                             |
| 王五 | 今年40岁                                                                             |
+------+--------------------------------------------------------------------------------------+
3 rows in set
复制代码

case when … then … end

case when 字段条件判断 then 语句 else ... end;

+------+-----+
| name | scr |
+------+-----+
| 张三 |  50 |
| 李四 |  78 |
| 王五 |  98 |
+------+-----+
mysql> select
    -> name,
    -> case when scr < 60 then '不及格' when scr <=80 then '良' else '优' end
    -> from sc;
+------+------------------------------------------------------------------------+
| name | case when scr < 60 then '不及格' when scr <=80 then '良' else '优' end |
+------+------------------------------------------------------------------------+
| 张三 | 不及格                                                                 |
| 李四 | 良                                                                     |
| 王五 | 优                                                                     |
+------+------------------------------------------------------------------------+
3 rows in set
复制代码