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)