mysql 流程控制函数

常用函数

IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2
IFNULL(v1,v2)	如果 v1 的值不为 NULL，则返回 v1，否则返回 v2
类似 if-else if-else	CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了你

快速入门案例

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | smith  | clerk     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | allen  | salesman  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | ward   | salesman  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | jones  | manager   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | martin | salesman  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | blake  | manager   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
+-------+--------+-----------+------+------------+---------+---------+--------+
13 rows in set (0.00 sec)

if 函数的使用

mysql> select empno,ename,sal,if(comm is null,0,comm) from emp;
+-------+--------+---------+-------------------------+
| empno | ename  | sal     | if(comm is null,0,comm) |
+-------+--------+---------+-------------------------+
|  7369 | smith  |  800.00 |                       0 |
|  7499 | allen  | 1600.00 |                  300.00 |
|  7521 | ward   | 1250.00 |                  500.00 |
|  7566 | jones  | 2975.00 |                       0 |
|  7654 | martin | 1250.00 |                 1400.00 |
|  7698 | blake  | 2850.00 |                       0 |
+-------+--------+---------+-------------------------+
13 rows in set (0.01 sec)

// 求总收入时 薪水+津贴 如果有null 则值会是null
mysql> select empno,ename,sal+comm from emp;
+-------+--------+----------+
| empno | ename  | sal+comm |
+-------+--------+----------+
|  7369 | smith  |     NULL |
|  7499 | allen  |  1900.00 |
|  7521 | ward   |  1750.00 |
|  7566 | jones  |     NULL |
|  7654 | martin |  2650.00 |
|  7698 | blake  |     NULL |
+-------+--------+----------+
13 rows in set (0.00 sec)

//如果津贴为 null 时，则津贴等于0，否则不变
mysql> select empno,ename,sal+if(comm is null,0,comm) from emp;
+-------+--------+-----------------------------+
| empno | ename  | sal+if(comm is null,0,comm) |
+-------+--------+-----------------------------+
|  7369 | smith  |                      800.00 |
|  7499 | allen  |                     1900.00 |
|  7521 | ward   |                     1750.00 |
|  7566 | jones  |                     2975.00 |
|  7654 | martin |                     2650.00 |
|  7698 | blake  |                     2850.00 |
+-------+--------+-----------------------------+
13 rows in set (0.00 sec)


//显示雇员总数，以及获得补助的雇员数，如果补助为0，则改为null，count不统计null

mysql> select count(*),count(if(comm=0,null,comm)) from emp;
+----------+-----------------------------+
| count(*) | count(if(comm=0,null,comm)) |
+----------+-----------------------------+
|       13 |                           3 |
+----------+-----------------------------+
1 row in set (0.00 sec)

ifnull 函数使用

//if() 和 ifnull() 作用一样，if() 可读性更强
//ifnull(comm,0) 如果comm不为空，则返回comm值，否则返回0
mysql> select empno,ename,sal+ifnull(comm,0) from emp;
+-------+--------+--------------------+
| empno | ename  | sal+ifnull(comm,0) |
+-------+--------+--------------------+
|  7369 | smith  |             800.00 |
|  7499 | allen  |            1900.00 |
|  7521 | ward   |            1750.00 |
|  7566 | jones  |            2975.00 |
|  7654 | martin |            2650.00 |
|  7698 | blake  |            2850.00 |
+-------+--------+--------------------+
13 rows in set (0.00 sec)

多重分支控制 if-else if-else

如果comm 是 null 则显示 0，如果comm 是 大于 500 则显示 显示 ‘保密’，其它的情况，就显示本身的值

mysql> select empno,ename,sal,(select case when comm is null then 0 when comm > 500 then '保密' else comm end) from emp;
+-------+--------+---------+----------------------------------------------------------------------------------+
| empno | ename  | sal     | (select case when comm is null then 0 when comm > 500 then '保密' else comm end) |
+-------+--------+---------+----------------------------------------------------------------------------------+
|  7369 | smith  |  800.00 | 0                                                                                
|  7499 | allen  | 1600.00 | 300.00                                                                           
|  7521 | ward   | 1250.00 | 500.00                                                                           
|  7566 | jones  | 2975.00 | 0                                                                                
|  7654 | martin | 1250.00 | 保密                                                                             
|  7698 | blake  | 2850.00 | 0                                                                                                                                                            
+-------+--------+---------+----------------------------------------------------------------------------------+
13 rows in set (0.00 sec)