【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 | +-------------+------------+---------------+----------+----------+ */
编程是个人爱好