【MYSQL】流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。

MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

 

函数	用法
IF(value,value1,value2)	如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2)	如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2… [ELSE resultn] END	相当于Java的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END	相当于Java的switch…case…

#5.流程控制函数
#5.1 IF(value,value1,value2)
#如果value的值为TRUE，返回value1，否则返回value2
SELECT last_name,salary,IF(salary >= 6000,'高工资','低工资') "details"
FROM employees;
/*部分输出
+-------------+----------+-----------+
| last_name   | salary   | details   |
+-------------+----------+-----------+
| King        | 24000.00 | 高工资    |
| Kochhar     | 17000.00 | 高工资    |
| De Haan     | 17000.00 | 高工资    |
| Hunold      |  9000.00 | 高工资    |
| Ernst       |  6000.00 | 高工资    |
| Austin      |  4800.00 | 低工资    |
*/
SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details",
salary * 12 * (1 + IF(commission_pct IS NOT NULL,commission_pct,0)) "annual_sal"
FROM employees;
/*
+-------------+----------------+---------+------------+
| last_name   | commission_pct | details | annual_sal |
+-------------+----------------+---------+------------+
| King        |           NULL |    0.00 |  288000.00 |
| Kochhar     |           NULL |    0.00 |  204000.00 |
| De Haan     |           NULL |    0.00 |  204000.00 |
| Hunold      |           NULL |    0.00 |  108000.00 |
| Ernst       |           NULL |    0.00 |   72000.00 |
*/
#5.2 IFNULL(VALUE1,VALUE2):看做是IF(VALUE,VALUE1,VALUE2)的特殊情况
#如果value1不为NULL，返回value1，否则返回value2
SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details"
FROM employees;
/*
+-------------+----------------+---------+
| last_name   | commission_pct | details |
+-------------+----------------+---------+
| King        |           NULL |    0.00 |
| Kochhar     |           NULL |    0.00 |
| De Haan     |           NULL |    0.00 |
*/

#5.3 CASE WHEN ... THEN ...WHEN ... THEN ... ELSE（可以省略） ... END
# 类似于java的if ... else if ... else if ... else
SELECT last_name,salary,
                 CASE 
                   WHEN salary >= 15000 THEN '白骨精' 
                   WHEN salary >= 10000 THEN '潜力股'
                   WHEN salary >= 8000 THEN '小屌丝'
                   ELSE '草根' 
                 END 
                "details",department_id
FROM employees;
/*部分输出
+-------------+----------+-----------+---------------+
| last_name   | salary   | details   | department_id |
+-------------+----------+-----------+---------------+
| King        | 24000.00 | 白骨精    |            90 |
| Kochhar     | 17000.00 | 白骨精    |            90 |
| De Haan     | 17000.00 | 白骨精    |            90 |
| Hunold      |  9000.00 | 小屌丝    |            60 |
| Ernst       |  6000.00 | 草根      |            60 |
| Austin      |  4800.00 | 草根      |            60 |
| Pataballa   |  4800.00 | 草根      |            60 |
*/
#ELSE可省略，省略时除以上情况外就为NULL
SELECT last_name,salary,
                CASE
                  WHEN salary >= 15000 THEN '白骨精' 
                  WHEN salary >= 10000 THEN '潜力股'
                  WHEN salary >= 8000 THEN '小屌丝'
                 END "details"
FROM employees;             #ELSE可省略，省略时除以上情况外就为NULL
/*部分输出
+-------------+----------+-----------+
| last_name   | salary   | details   |
+-------------+----------+-----------+
| King        | 24000.00 | 白骨精    |
| Kochhar     | 17000.00 | 白骨精    |
| De Haan     | 17000.00 | 白骨精    |
| Hunold      |  9000.00 | 小屌丝    |
| Ernst       |  6000.00 | NULL      |
| Austin      |  4800.00 | NULL      |
| Pataballa   |  4800.00 | NULL      |
*/

#5.4 CASE 表达式... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END
# 类似于java的swich ... case...
/*

练习1
查询部门号为 10,20, 30 的员工信息, 
若部门号为 10, 则打印其工资的 1.1 倍, 
20 号部门, 则打印其工资的 1.2 倍, 
30 号部门,打印其工资的 1.3 倍数,
其他部门,打印其工资的 1.4 倍数

*/
SELECT employee_id,last_name,department_id,salary,
                                 CASE department_id 
                                     WHEN 10 THEN salary * 1.1
                                     WHEN 20 THEN salary * 1.2
                                     WHEN 30 THEN salary * 1.3
                                     ELSE salary * 1.4 
                                 END "details"
FROM employees;
/*部分输出
+-------------+-------------+---------------+----------+----------+
| employee_id | last_name   | department_id | salary   | details  |
+-------------+-------------+---------------+----------+----------+
|         100 | King        |            90 | 24000.00 | 33600.00 |
|         101 | Kochhar     |            90 | 17000.00 | 23800.00 |
|         102 | De Haan     |            90 | 17000.00 | 23800.00 |
|         103 | Hunold      |            60 |  9000.00 | 12600.00 |
|         104 | Ernst       |            60 |  6000.00 |  8400.00 |
|         105 | Austin      |            60 |  4800.00 |  6720.00 |
|         106 | Pataballa   |            60 |  4800.00 |  6720.00 |
*/

/*
练习2
查询部门号为 10,20, 30 的员工信息, 
若部门号为 10, 则打印其工资的 1.1 倍, 
20 号部门, 则打印其工资的 1.2 倍, 
30 号部门打印其工资的 1.3 倍数
*/
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1
                                     WHEN 20 THEN salary * 1.2
                                     WHEN 30 THEN salary * 1.3
                                     END "details"
FROM employees
WHERE department_id IN (10,20,30);
/*输出
+-------------+------------+---------------+----------+----------+
| employee_id | last_name  | department_id | salary   | details  |
+-------------+------------+---------------+----------+----------+
|         200 | Whalen     |            10 |  4400.00 |  4840.00 |
|         201 | Hartstein  |            20 | 13000.00 | 15600.00 |
|         202 | Fay        |            20 |  6000.00 |  7200.00 |
|         114 | Raphaely   |            30 | 11000.00 | 14300.00 |
|         115 | Khoo       |            30 |  3100.00 |  4030.00 |
|         116 | Baida      |            30 |  2900.00 |  3770.00 |
|         117 | Tobias     |            30 |  2800.00 |  3640.00 |
|         118 | Himuro     |            30 |  2600.00 |  3380.00 |
|         119 | Colmenares |            30 |  2500.00 |  3250.00 |
+-------------+------------+---------------+----------+----------+
*/