MySQL中IF的使用

  MySQL的IF既可以作为表达式用，也可在存储过程中作为流程控制语句使用。

1 IF表达式

IF(expr1 , expr2 , expr3)
expr1的值为TRUE，则返回值为expr2
expr1的值为FALSE，则返回值为expr3

mysql> SELECT IF(TRUE, 3, 4);
    -> 3
mysql> SELECT IF(FALSE, 3, 4);
    -> 4
mysql> SELECT IF(STRCMP('111', '222'), 'unequal', 'equal');
    -> unequal

2 IFNULL表达式

IFNULL(expr1 , expr2)
判断第一个参数expr1是否为NULL
如果expr1不为空，直接返回expr1
如果expr1为空，返回第二个参数expr2

mysql> SELECT IFNULL(NULL, 321);
    -> 321
mysql> SELECT IFNULL(123, 321);
    -> 123

3 NULLIF表达式

NULLIF(expr1 , expr2)
如果expr1和expr2的值相等返回NULL
如果值不相等返回expr1的值

mysql> SELECT NULLIF(123, 123)
    -> NULL
mysql> SELECT NULLIF(123, 321)
    -> 123

4 做流程控制语句

  IF也可以用CASE WHEN来实现

4.1 CASE WHEN ELSE

4.1.1 simple case

CASE  value
    WHEN  [compare_value] THEN  result
    [WHEN [compare_value] THEN  result ...] 
    [ELSE  result]  END

• 语义
  1. 将case后面的值value分别和每个when子句后面的值compare_value进行相等比较：
  2. 如果一旦和某个when子句后面的值相等则返回相应的then子句后面的值result；
  3. 如果和所有when子句后面的值都不相等，则返回else子句后面的值；
  4. 如果没有else部分则返回null。

mysql> SELECT user_id, salary, CASE salary
    -> WHEN 1000 THEN 'low'
    -> WHEN 2000 THEN 'mid'
    -> WHEN 3000 THEN 'high'
    -> ELSE 'undefined' END salary_grade
    -> FROM user_salary_table;
# result
+---------+---------+--------------+
| user_id |  salary | salary_grade |
+---------+---------+--------------+
|    1    |  1000   |   low        |
|    2    |  3000   |   high       |
|    3    |  4000   |   undefined  |
|    4    |  2000   |   mid        |
|    5    |  3000   |   high       |
+---------+---------+--------------+

• 注意
  1. value可以是字面量、表达式或者列名
  2. case表达式的数据类型取决于跟在then或else后面的表达式的类型。类型必须相同（可尝试做隐式类型转换），否则出错

4.1.2 search case

CASE
    WHEN [search_condition] THEN result
    [WHEN [search_condition] THEN result ...]
    [ELSE result]  END

• 语义:
  1. 如果某个when子句后面的条件condition为true，则返回相应的when子句后面的值result;
  2. 如果所有的when子句后面的条件condition都不为true，则返回else子句后面的值;
  3. 如果没有else部分则返回null。

mysql> SELECT user_id, salary, CASE
    -> WHEN salary <= 1000 THEN 'low'
    -> WHEN salary = 2000 THEN 'mid'
    -> WHEN salary >= 3000 THEN 'high' 
    -> ELSE 'undefined' END salary_grade
    -> FROM user_salary_table;
# result
+---------+---------+--------------+
| user_id |  salary | salary_grade |
+---------+---------+--------------+
|    1    |  900    |   low        |
|    2    |  3100   |   high       |
|    3    |  2500   |   undefined  |
|    4    |  2000   |   mid        |
|    5    |  3000   |   high       |
+---------+---------+--------------+

4.2 IF ELSEIF END

IF search_condition THEN 
    statement_list  
[ELSEIF search_condition THEN]  
    statement_list ...  
[ELSE statement_list]  
END IF

• 语义:
  1. 当IF中条件search_condition成立时,执行THEN后的statement_list语句;
  2. 否则判断ELSEIF中的条件成立时，执行其后的statement_list语句，否则继续判断其他分支;
  3. 当所有分支的条件均不成立时，执行ELSE分支。

  search_condition是一个条件表达式，可以由“=、<、<=、>、>=、!=”等条件运算符组成，并且可以使用AND、OR、NOT对多个表达式进行组合。

# 1.建立一个存储过程;
# 2.通过学号和课程编号查询其成绩,返回成绩和成绩的等级;
# 3.成绩大于90分的为A级,小于90分大于等于80分的为B级,依次到E级;

# 设定数据库如下
+----+------------+-----------+-------+
| id | student_no | course_no | grade |
+----+------------+-----------+-------+
| 1  |      1     |     1     |   91  |
| 2  |      1     |     2     |   81  |
| 3  |      2     |     1     |   71  |
| 4  |      2     |     2     |   61  |
| 5  |      3     |     1     |   59  |
+----+------------+-----------+-------+
# student_no 学号; course_no 课程编号; grade 成绩;

# 将语句的结束符号从分号;临时改为两个&&(可以是自定义)
# 该操作以及恢复分号操作在命令行可用,
# 自己用Navicat软件不起作用,在Navicat软件中忽略这两个操作,下面存储操作最后END后面不加自定义的参数(这里是&&)可用
mysql> DELIMITER &&
# 新建存储过程
mysql> CREATE PROCEDURE proc_grade(IN stu_no VARCHAR(20), cour_no VARCHAR(10))
    -> BEGIN
    -> DECLARE stu_grade FLOAT;  
    -> SELECT grade INTO stu_grade FROM grade WHERE student_no = stu_no AND course_no = cour_no;
    -> IF stu_grade >= 90 THEN
    ->    SELECT stu_no, cour_no, stu_grade, 'A';
    -> ELSEIF stu_grade < 90 AND stu_grade >= 80 THEN
    ->    SELECT stu_no, cour_no, stu_grade, 'B';
    -> ELSEIF stu_grade < 80 AND stu_grade >= 70 THEN
    ->    SELECT stu_no, cour_no, stu_grade, 'C';
    -> ELSEIF stu_grade < 70 AND stu_grade >= 60 THEN
    ->    SELECT stu_no, cour_no, stu_grade, 'D';
    -> ELSE
    ->    SELECT stu_no, cour_no, stu_grade, 'E'; 
    -> END IF; 
    -> END &&
# 将语句的结束符号恢复为分号(Navicat软件忽略操作)
mysql> DELIMITER ;

# 查看存储结果
mysql> CALL proc_grade(3, 1);
+--------+---------+-----------+---+
| stu_no | cour_no | stu_grade | E |
+--------+---------+-----------+---+
|    3   |    1    |     59    | E |
+--------+---------+-----------+---+