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
- 语义
- 将case后面的值value分别和每个when子句后面的值compare_value进行相等比较:
- 如果一旦和某个when子句后面的值相等则返回相应的then子句后面的值result;
- 如果和所有when子句后面的值都不相等,则返回else子句后面的值;
- 如果没有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 |
+---------+---------+--------------+
- 注意
- value可以是字面量、表达式或者列名
- case表达式的数据类型取决于跟在then或else后面的表达式的类型。类型必须相同(可尝试做隐式类型转换),否则出错
4.1.2 search case
CASE
WHEN [search_condition] THEN result
[WHEN [search_condition] THEN result ...]
[ELSE result] END
- 语义:
- 如果某个when子句后面的条件condition为true,则返回相应的when子句后面的值result;
- 如果所有的when子句后面的条件condition都不为true,则返回else子句后面的值;
- 如果没有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
- 语义:
- 当IF中条件search_condition成立时,执行THEN后的statement_list语句;
- 否则判断ELSEIF中的条件成立时,执行其后的statement_list语句,否则继续判断其他分支;
- 当所有分支的条件均不成立时,执行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 |
+--------+---------+-----------+---+