MySQL基础教程13——函数——流程控制函数
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
