備注:測(cè)試數(shù)據(jù)庫(kù)版本為MySQL 8.0
這個(gè)blog我們來聊聊常見的流程控制函數(shù)
如需要scott用戶下建表及錄入數(shù)據(jù)語(yǔ)句，可參考:
scott建表及錄入數(shù)據(jù)sql腳本
流程控制函數(shù)
| 函數(shù)名 | 函數(shù)用途 |
|---|---|
| CASE | case語(yǔ)句用于條件判斷 |
| if() | if/else條件判斷 |
| ifnull() | null數(shù)據(jù)處理 |
| nullif() | return null if expr1 = expr2 |
一.CASE語(yǔ)句
case語(yǔ)句語(yǔ)法:
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;
-- 條件判斷,小于某個(gè)時(shí)間點(diǎn)入職的是老員工，大于等于的是新員工
select e.empno,
e.hiredate,
case when e.hiredate < '1982-01-01' then '老員工' else '新員工' end is_old
from emp e;
-- 沒有else語(yǔ)句且判斷不成功的時(shí)候，會(huì)返回為空
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> -- 條件判斷,小于某個(gè)時(shí)間點(diǎn)入職的是老員工，大于等于的是新員工
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語(yǔ)句且判斷不成功的時(shí)候，會(huì)返回為空
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語(yǔ)法:
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);
-- 如果第一個(gè)值不為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> -- 如果第一個(gè)值不為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語(yǔ)法:
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.
如果expr1非空,則返回expr1，否則返回expr2
-- 將獎(jiǎng)金為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> -- 將獎(jiǎng)金為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語(yǔ)法:
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)
