MySQL流程函数

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)函数：这个函数一般用来替换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函数：这里也可以用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函数：这里可以分多种情况把职员的薪水分多个档次，比如下面的例子成分高、中、低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)