備注:測試數(shù)據(jù)庫版本為MySQL 8.0

這個blog我們來聊聊常見的流程控制函數(shù)

如需要scott用戶下建表及錄入數(shù)據(jù)語句，可參考:
scott建表及錄入數(shù)據(jù)sql腳本

流程控制函數(shù)

函數(shù)名	函數(shù)用途
CASE	case語句用于條件判斷
if()	if/else條件判斷
ifnull()	null數(shù)據(jù)處理
nullif()	return null if expr1 = expr2

CASE語句?

case語句語法:
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result …] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result …] [ELSE result] END
?

-- 值判斷，根據(jù)職位英文名判斷職位中文名
select e.empno,e.job,case when e.job = 'SALESMAN' then '銷售員'when e.job = 'MANAGER' then '管理人員'else '未知' end  as job_china
from emp e;-- 條件判斷,小于某個時間點入職的是老員工，大于等于的是新員工
select e.empno,e.hiredate,case when e.hiredate < '1982-01-01' then '老員工' else '新員工' end is_old
from emp e;-- 沒有else語句且判斷不成功的時候，會返回為空
SELECT CASE 'c'WHEN 'a' THEN 1 WHEN 'b' THEN 2 END is_c;

mysql> -- 值判斷，根據(jù)職位英文名判斷職位中文名
mysql> select e.empno,->        e.job,->        case when e.job = 'SALESMAN' then '銷售員'->             when e.job = 'MANAGER' then '管理人員'->             else '未知' end  as job_china-> from emp e;
+-------+-----------+--------------+
| empno | job       | job_china    |
+-------+-----------+--------------+
|  7369 | CLERK     | 未知         |
|  7499 | SALESMAN  | 銷售員       |
|  7521 | SALESMAN  | 銷售員       |
|  7566 | MANAGER   | 管理人員     |
|  7654 | SALESMAN  | 銷售員       |
|  7698 | MANAGER   | 管理人員     |
|  7782 | MANAGER   | 管理人員     |
|  7788 | ANALYST   | 未知         |
|  7839 | PRESIDENT | 未知         |
|  7844 | SALESMAN  | 銷售員       |
|  7876 | CLERK     | 未知         |
|  7900 | CLERK     | 未知         |
|  7902 | ANALYST   | 未知         |
|  7934 | CLERK     | 未知         |
+-------+-----------+--------------+
14 rows in set (0.00 sec)mysql>
mysql> -- 條件判斷,小于某個時間點入職的是老員工，大于等于的是新員工
mysql> select e.empno,->        e.hiredate,->        case when e.hiredate < '1982-01-01' then '老員工' else '新員工' end is_old-> from emp e;
+-------+------------+-----------+
| empno | hiredate   | is_old    |
+-------+------------+-----------+
|  7369 | 1980-12-17 | 老員工    |
|  7499 | 1981-02-20 | 老員工    |
|  7521 | 1981-02-22 | 老員工    |
|  7566 | 1981-04-02 | 老員工    |
|  7654 | 1981-09-28 | 老員工    |
|  7698 | 1981-05-01 | 老員工    |
|  7782 | 1981-06-09 | 老員工    |
|  7788 | 1987-06-13 | 新員工    |
|  7839 | 1981-11-17 | 老員工    |
|  7844 | 1981-09-08 | 老員工    |
|  7876 | 1987-06-13 | 新員工    |
|  7900 | 1981-12-03 | 老員工    |
|  7902 | 1981-12-03 | 老員工    |
|  7934 | 1982-01-23 | 新員工    |
+-------+------------+-----------+
14 rows in set (0.00 sec)mysql>
mysql> -- 沒有else語句且判斷不成功的時候，會返回為空
mysql> SELECT CASE 'c'->       WHEN 'a' THEN 1 WHEN 'b' THEN 2 END is_c;
+------+
| is_c |
+------+
| NULL |
+------+
1 row in set (0.00 sec)mysql>

IF函數(shù)

if語法:
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.

-- 如果1>2條件成立,則返回2，否則返回3
select if(1>2,2,3);
-- 如果第一個值不為0或null，則返回2，否則返回3
select if(0,2,3);
select if(null,2,3);
select if(1,2,3);

mysql> -- 如果1>2條件成立,則返回2，否則返回3
mysql> select if(1>2,2,3);
+-------------+
| if(1>2,2,3) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)mysql> -- 如果第一個值不為0或null，則返回2，否則返回3
mysql> select if(0,2,3);
+-----------+
| if(0,2,3) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)mysql> select if(null,2,3);
+--------------+
| if(null,2,3) |
+--------------+
|            3 |
+--------------+
1 row in set (0.00 sec)mysql> select if(1,2,3);
+-----------+
| if(1,2,3) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

IFNULL函數(shù)

ifnull語法:
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.
如果expr1非空,則返回expr1，否則返回expr2

-- 將獎金為0的員工顯示為0
select e.ename,e.job,e.comm comm from emp e;
select e.ename,e.job,ifnull(e.comm,0) comm from emp e;

mysql> -- 將獎金為0的員工顯示為0
mysql> select e.ename,e.job,e.comm comm from emp e;
+--------+-----------+---------+
| ename  | job       | comm    |
+--------+-----------+---------+
| SMITH  | CLERK     |    NULL |
| ALLEN  | SALESMAN  |  300.00 |
| WARD   | SALESMAN  |  500.00 |
| JONES  | MANAGER   |    NULL |
| MARTIN | SALESMAN  | 1400.00 |
| BLAKE  | MANAGER   |    NULL |
| CLARK  | MANAGER   |    NULL |
| SCOTT  | ANALYST   |    NULL |
| KING   | PRESIDENT |    NULL |
| TURNER | SALESMAN  |    0.00 |
| ADAMS  | CLERK     |    NULL |
| JAMES  | CLERK     |    NULL |
| FORD   | ANALYST   |    NULL |
| MILLER | CLERK     |    NULL |
+--------+-----------+---------+
14 rows in set (0.00 sec)mysql> select e.ename,e.job,ifnull(e.comm,0) comm from emp e;
+--------+-----------+---------+
| ename  | job       | comm    |
+--------+-----------+---------+
| SMITH  | CLERK     |    0.00 |
| ALLEN  | SALESMAN  |  300.00 |
| WARD   | SALESMAN  |  500.00 |
| JONES  | MANAGER   |    0.00 |
| MARTIN | SALESMAN  | 1400.00 |
| BLAKE  | MANAGER   |    0.00 |
| CLARK  | MANAGER   |    0.00 |
| SCOTT  | ANALYST   |    0.00 |
| KING   | PRESIDENT |    0.00 |
| TURNER | SALESMAN  |    0.00 |
| ADAMS  | CLERK     |    0.00 |
| JAMES  | CLERK     |    0.00 |
| FORD   | ANALYST   |    0.00 |
| MILLER | CLERK     |    0.00 |
+--------+-----------+---------+
14 rows in set (0.00 sec)

nullif 函數(shù)

nullif語法:
NULLIF(expr1,expr2)
Returns NULL if expr1 = expr2 is true, otherwise returns expr1
This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

如果expr1=expr2則返回null，否則返回expr1
?

mysql> select nullif(1,1);
+-------------+
| nullif(1,1) |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)mysql> select nullif(1,2);
+-------------+
| nullif(1,2) |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)