MySQL 中的流程控制和用法示例

• • • CASE 语句
    • IF 语句
    • IFNULL
    • NULLIF
    • 流程控制的常见用法
    • 加减乘除运算符的注意事项

CASE 语句

基本语法：

CASE value 
    WHEN [compare-value] THEN result 
    [WHEN [compare-value] THEN result ...] 
    [ELSE result] 
    END 

或

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

第一种写法，表示等值比较（value 和 compare-value）。如果没有匹配，则返回 ELSE 后的结果，如果没有 ELSE 部分，则返回 NULL。

mysql> SELECT CASE 1 WHEN 1 THEN 'one' END;
+------------------------------+
| CASE 1 WHEN 1 THEN 'one' END |
+------------------------------+
| one                          |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE 2 WHEN 1 THEN 'one' END;
+------------------------------+
| CASE 2 WHEN 1 THEN 'one' END |
+------------------------------+
| NULL                         |
+------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE 2 WHEN 1 THEN 'one' ELSE 'none' END;
+------------------------------------------+
| CASE 2 WHEN 1 THEN 'one' ELSE 'none' END |
+------------------------------------------+
| none                                     |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END AS res;
+-----+
| res |
+-----+
| two |
+-----+

第二种写法，更为灵活，可以是任意条件。

SELECT
	CASE
WHEN 2 > 1 THEN
	'greater'
WHEN 2 = 1 THEN
	'equal'
ELSE
	'less'
END;

IF 语句

基本语法：

IF(expr1, expr2, expr3) 

如果 expr1 为 true （不为 0 和 NULL），则 IF() 的返回值为 expr2；否则，返回值为 expr3 。

IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

mysql> SELECT IF(1<2, 'yes ', 'no');
+-----------------------+
| IF(1<2, 'yes ', 'no') |
+-----------------------+
| yes                   |
+-----------------------+

mysql> SELECT IF(STRCMP('test','test1'), 'no', 'yes');
+-----------------------------------------+
| IF(STRCMP('test','test1'), 'no', 'yes') |
+-----------------------------------------+
| no                                      |
+-----------------------------------------+

IFNULL

基本语法：

IFNULL(expr1, expr2) 

如果 expr1 为 NULL，则 IFNULL() 的返回值为 expr2；否则其返回值为 expr1 。

mysql> SELECT IFNULL(null, 6);
+-----------------+
| IFNULL(null, 6) |
+-----------------+
|               6 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT IFNULL(0, 6);
+--------------+
| IFNULL(0, 6) |
+--------------+
|            0 |
+--------------+

NULLIF

基本语法：

NULLIF(expr1, expr2) 

如果 expr1 等于 expr2 成立，那么返回值为 NULL ；否则，返回值为 expr1 。

mysql> SELECT NULLIF(1, 1);
+--------------+
| NULLIF(1, 1) |
+--------------+
|         NULL |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT NULLIF(1, 2);
+--------------+
| NULLIF(1, 2) |
+--------------+
|            1 |
+--------------+

流程控制的常见用法

流程控制结构经常会结合聚合函数（如 count、sum 等）一起使用。 当然，也可单独使用，比如新增一个列值。

比如，有一张 student 表。数据如下：

id	name	class	sex	age
1	jack01	G01	male	18
2	jack02	G01	male	19
3	jack03	G02	male	19
4	lily01	G02	female	17
5	lily02	G02	female	null
6	lily03	G03	null	20
7	rose01	G01	female	null
8	rose02	G01	null	19

现在，要统计每个班级的学生人数、男生的人数、女生的人数、性别未知的人数、男生的年龄之和、女生的年龄之和。

SQL 语句至少有以下三种写法。

第一种写法：采用 CASE 语句。

SELECT
	class,
	COUNT(*) AS student_num,
	COUNT(
		CASE
    		WHEN sex = 'male' THEN 1
    		ELSE NULL
		END) AS count_male,
	COUNT(
		CASE
    		WHEN sex = 'female' THEN 1
		END) AS count_female,
	COUNT(
		CASE
		    WHEN sex is NULL THEN 1
		END) AS count_secret,
	SUM(
		CASE
		    WHEN sex = 'male' THEN age
		END) AS sum_male_age,
	SUM(
		CASE
		    WHEN sex = 'female' THEN age
		END) AS sum_female_age
FROM
	student
GROUP BY
	class

第二种写法：采用 IF 语句。

SELECT
	class,
	COUNT(*) AS student_num,
	COUNT(IF(sex = 'male', 1, NULL)) AS count_male,
	COUNT(IF(sex = 'female', 1, NULL)) AS count_female,
	COUNT(IF(sex is NULL, 1, NULL)) AS count_secret,
	SUM(IF(sex = 'male', age, NULL)) AS sum_male_age,
	SUM(IF(sex = 'female', age, NULL)) AS sum_female_age
FROM
	student
GROUP BY
	class

第三种写法：对于 count() 函数来说，里面的 IF 语句有一种简写形式（非主流写法）。

SELECT
	class,
	COUNT(*) AS student_num,
	COUNT(sex = 'male' OR NULL) AS count_male,
	COUNT(sex = 'female' OR NULL) AS count_female,
	COUNT(sex is NULL OR NULL) AS count_secret,
	SUM(IF(sex = 'male', age, NULL)) AS sum_male_age,
	SUM(IF(sex = 'female', age, NULL)) AS sum_female_age
FROM
	student
GROUP BY
	class

上面的三种写法，得到的结果都相同。

+-------+-------------+------------+--------------+--------------+--------------+----------------+
| class | student_num | count_male | count_female | count_secret | sum_male_age | sum_female_age |
+-------+-------------+------------+--------------+--------------+--------------+----------------+
| G01   |           4 |          2 |            1 |            1 |           37 |           NULL |
| G02   |           3 |          1 |            2 |            0 |           19 |             17 |
| G03   |           1 |          0 |            0 |            1 |         NULL |           NULL |
+-------+-------------+------------+--------------+--------------+--------------+----------------+

推荐使用第二种写法，它非常简洁明了。

MySQL 中关于 count() 函数的说明：

• count(*) 只要记录存在，就会进行计数。
• count(col_name) 只有当字段 col_name 的值不为 null 时，才会进行计数。
• count(1) 只要记录存在，就会进行计数，执行速度比 count(*) 要快。数值 1 可以用其他的任意数值替代，比如 0 或者 9 。
• count(null) 的结果为 0，等同于 count(NULL) 。
• count() 函数的结果不可能为 null，只会大于或等于 0。

MySQL 中关于 sum() 函数的说明：

• sum(col_name) 对字段 col_name 进行求和。如果 col_name 的值为 null ，就会忽略；如果记录不存在，结果为 null ；如果所有记录的值都是 null ，结果为 null 。
• sum(null) 的结果为 null 。
• sum() 函数的结果可能为 null 。

加减乘除运算符的注意事项

在 MySQL 中，对 null 值进行加减乘除运算符的操作时，结果都为 null。

mysql> select 2+null;
+--------+
| 2+null |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> select 2-null;
+--------+
| 2-null |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

mysql> select 2*null;
+--------+
| 2*null |
+--------+
|   NULL |
+--------+
1 row in set (0.01 sec)

mysql> select 2/null;
+--------+
| 2/null |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

此外，两个整数相减时，只要有一个整数不是有符号（signed）的，若差为负数，就会报错。

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range

解决方法是用 convert() 或 cast() 函数将它们都转换为有符号的。

关于，convert() 和 cast() 函数的用法，可点击这里。