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

快来打我* 2022-05-23 08:52 63阅读 0赞

## 常用的流程控制结构 ##

### 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 表。数据如下：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>class</th> 
   <th>sex</th> 
   <th>age</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>jack01</td> 
   <td>G01</td> 
   <td>male</td> 
   <td>18</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>jack02</td> 
   <td>G01</td> 
   <td>male</td> 
   <td>19</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>jack03</td> 
   <td>G02</td> 
   <td>male</td> 
   <td>19</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>lily01</td> 
   <td>G02</td> 
   <td>female</td> 
   <td>17</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>lily02</td> 
   <td>G02</td> 
   <td>female</td> 
   <td>null</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>lily03</td> 
   <td>G03</td> 
   <td>null</td> 
   <td>20</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>rose01</td> 
   <td>G01</td> 
   <td>female</td> 
   <td>null</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>rose02</td> 
   <td>G01</td> 
   <td>null</td> 
   <td>19</td> 
  </tr> 
 </tbody> 
</table>

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

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。