mysql中的条件语句case when/if函数
主要知识点为case函数,if函数,ifnull函数,elt函数几部分,主要用于mysql语句中的逻辑判断
待操作的表如下:
+----+-----------+-----+-------+--------+
| id | name | sex | level | weight |
+----+-----------+-----+-------+--------+
| 1 | xiaohong | 1 | 1 | 50 |
| 2 | xiaoming | 0 | 0 | 90 |
| 3 | xiaohuang | 1 | 2 | 80 |
| 4 | xiaoming | 0 | 3 | NULL |
+----+-----------+-----+-------+--------+
sex:0-woman,1-man,其中-unknown;level是客户的级别:1-normal,2-vip,3-vvip
1.case函数
语法1:
CASE expression WHEN value1 THEN returnvalue1 WHEN value2 THEN returnvalue2 WHEN value3 THEN returnvalue3 …… ELSE defaultreturnvalue END
举栗:
- select t.name,(case t.sex when 0 then 'woman' when 1 then 'man' else 'unknown' end) sex from test t;
CASE WHEN condition1 THEN returnvalue1 WHEN condition 2 THEN returnvalue2 WHEN condition 3 THEN returnvalue3 …… ELSE defaultreturnvalue END
- select t.name,(case when t.sex=0 then 'woman' when t.sex=1 then 'man' else 'unknown' end) sex from test t;
输出为:
+-----------+-------+
| name | sex |
+-----------+-------+
| xiaohong | man |
| xiaoming | woman |
| xiaohuang | man |
| xiaoming | woman |
+-----------+-------+
2.if函数
IF(expr1,expr2,expr3),如果 expr1 为真(expr1 <> 0 以及 expr1 <> NULL),那么 IF() 返回 expr2,否则返回expr3。
select name,if(weight>85,'fat','normal') from test;
+-----------+------------------------------+
| name | if(weight>85,'fat','normal') |
+-----------+------------------------------+
| xiaohong | normal |
| xiaoming | fat |
| xiaohuang | normal |
| xiaoming | normal |
+-----------+------------------------------+
当weight为NULL时,NULL>85的返回值为NULL,所以返回expr3 normal
3.IFNULL()函数
ifnull(value1,value2) 用来替换 NULL 值,若value1为NULL,则替换NULL为value2
select name,ifnull(weight,0) from test;
+-----------+------------------+
| name | ifnull(weight,0) |
+-----------+------------------+
| xiaohong | 50 |
| xiaoming | 90 |
| xiaohuang | 80 |
| xiaoming | 0 |
+-----------+------------------+
select name,ifnull(weight,0)+100 from test;
+-----------+----------------------+
| name | ifnull(weight,0)+100 |
+-----------+----------------------+
| xiaohong | 150 |
| xiaoming | 190 |
| xiaohuang | 180 |
| xiaoming | 100 |
+-----------+----------------------+
4.ELT函数
ELT(N,str1,str2,str3,...)
如果 N = 1,返回 str1,如果N = 2,返回 str2,等等。如果 N 小于 1 或大于参数的数量,返回 NULL。
select name,elt(level,'normal','vip','vvip') from test;
+-----------+----------------------------------+
| name | elt(level,'normal','vip','vvip') |
+-----------+----------------------------------+
| xiaohong | normal |
| xiaoming | NULL |
| xiaohuang | vip |
| xiaoming | vvip |
+-----------+----------------------------------+
其他:
Mysql的if既可以作为表达式用,也可在存储过程中作为流程控制语句使用,在该文中只讲述了if表达式。