MySQL流程函数
IF(value,x y)
如果value是真,返回x,否则返回y
MariaDB [learn]> INSERT INTO salary(sal) VALUES (1000),(2000),(3000),(4000),(5000),(6000),(NULL);
Query OK, 7 rows affected (0.06 sec)
Records: 7 Duplicates: 0 Warnings: 0
MariaDB [learn]> SELECT * FROM salary;
+----+------+
| id | sal |
+----+------+
| 1 | 1000 |
| 2 | 2000 |
| 3 | 3000 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | NULL |
+----+------+
MariaDB [learn]> SELECT id,if(sal>2000,'high','low') FROM salary;
+----+---------------------------+
| id | if(sal>2000,'high','low') |
+----+---------------------------+
| 1 | low |
| 2 | low |
| 3 | high |
| 4 | high |
| 5 | high |
| 6 | high |
| 7 | low |
+----+---------------------------+
7 rows in set (0.01 sec)
IFNULL(value1,value2)
如果value1不为空,返回value1,否则返回value2
IFNULL(value1,value2)函数:这个函数一般用来替换NULL值,我们知道NULL值是不能参与数值运算的,下面这个语句就是把NULL值用0来替换。
MariaDB [learn]> SELECT id,ifnull(sal,0) FROM salary;
+----+---------------+
| id | ifnull(sal,0) |
+----+---------------+
| 1 | 1000 |
| 2 | 2000 |
| 3 | 3000 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 0 |
+----+---------------+
7 rows in set (0.01 sec)
CASE WHEN [value] THEN [result]…ELSE [default] END
如果value为真 ,返回result,否则返回default
CASE WHEN [value] THEN [result]…ELSE [default] END函数:这里也可以用case when…then 实现上面例子中的高薪低能的问题。
MariaDB [learn]> SELECT id,case when sal<=2000 then 'low' else 'high' end FROM salary;
+----+------------------------------------------------+
| id | case when sal<=2000 then 'low' else 'high' end |
+----+------------------------------------------------+
| 1 | low |
| 2 | low |
| 3 | high |
| 4 | high |
| 5 | high |
| 6 | high |
| 7 | high |
+----+------------------------------------------------+
7 rows in set (0.01 sec)
CASE [expr] WHEN [value] THEN [result]…ELSE [default] END
如果expr等于value,返回result,否则返回default。
CASE [expr] WHEN [value] THEN [result]…ELSE [default] END函数:这里可以分多种情况把职员的薪水分多个档次,比如下面的例子成分高、中、低3种情况。同样还可以分成更多情况,这里就不再举例了。
MariaDB [learn]> SELECT id,CASE sal WHEN 1000 THEN 'low' WHEN 2000 THEN 'mid' ELSE 'low' END AS LEVEL FROM salary;
+----+-------+
| id | LEVEL |
+----+-------+
| 1 | low |
| 2 | mid |
| 3 | low |
| 4 | low |
| 5 | low |
| 6 | low |
| 7 | low |
+----+-------+
7 rows in set (0.01 sec)
原创文章,作者:dance_man,如若转载,请注明出处:http://www.178linux.com/74581