How the CASE operator works in Mariadb?

MariaDB CASE 运算符是一个用于根据不同的条件返回不同的值的运算符，它可以用于 SELECT、UPDATE、DELETE 等语句中，也可以用于存储过程、函数、触发器等中。

发布于

MariaDB CASE 运算符是一个用于根据不同的条件返回不同的值的运算符，它可以用于 SELECT、UPDATE、DELETE 等语句中，也可以用于存储过程、函数、触发器等中。CASE 运算符有两种形式：简单形式和搜索形式。简单形式是根据一个表达式的值来匹配不同的情况，搜索形式是根据多个条件来匹配不同的情况。

语法

MariaDB CASE 运算符的语法如下：

  • 简单形式

    CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    [ELSE resultN]
END

  • 搜索形式

    CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    [ELSE resultN]
END

其中，expression 是一个要匹配的表达式，value1、value2 等是与 expression 相等的值，condition1、condition2 等是要满足的条件，result1、result2 等是返回的结果，ELSE resultN 是可选的，表示如果没有匹配到任何情况，返回的默认结果。END 是必须的，表示 CASE 运算符的结束。

实例

使用简单形式的 CASE 运算符

SELECT name, score,
CASE score
    WHEN 100 THEN 'Perfect'
    WHEN 90 THEN 'Excellent'
    WHEN 80 THEN 'Good'
    WHEN 70 THEN 'Pass'
    ELSE 'Fail'
END AS grade
FROM student;

输出结果：

+------+-------+---------+
| name | score | grade   |
+------+-------+---------+
| Tom  |    95 | Excellent |
| Bob  |    85 | Good    |
| Alice|    75 | Pass    |
| Jack |    65 | Fail    |
| Lily |   100 | Perfect |
+------+-------+---------+

说明：本例中，使用简单形式的 CASE 运算符根据学生的分数返回不同的等级，分数为 100 时返回 ‘Perfect’，分数为 90 时返回 ‘Excellent’，以此类推，分数低于 70 时返回 ‘Fail’。

使用搜索形式的 CASE 运算符

SELECT name, age, gender,
CASE
    WHEN age >= 18 AND gender = 'M' THEN 'Adult Male'
    WHEN age >= 18 AND gender = 'F' THEN 'Adult Female'
    WHEN age < 18 AND gender = 'M' THEN 'Young Male'
    WHEN age < 18 AND gender = 'F' THEN 'Young Female'
    ELSE 'Unknown'
END AS category
FROM person;

输出结果：

+------+-----+--------+-------------+
| name | age | gender | category    |
+------+-----+--------+-------------+
| Tom  |  20 | M      | Adult Male  |
| Bob  |  17 | M      | Young Male  |
| Alice|  19 | F      | Adult Female|
| Jack |  16 | M      | Young Male  |
| Lily |  18 | F      | Adult Female|
+------+-----+--------+-------------+

说明：本例中，使用搜索形式的 CASE 运算符根据人的年龄和性别返回不同的类别，年龄大于等于 18 且性别为 ‘M’ 时返回 ‘Adult Male’，年龄大于等于 18 且性别为 ‘F’ 时返回 ‘Adult Female’，以此类推，年龄小于 18 且性别为 ‘M’ 时返回 ‘Young Male’，年龄小于 18 且性别为 ‘F’ 时返回 ‘Young Female’，其他情况返回 ‘Unknown’。

使用 CASE 运算符更新数据

UPDATE product
SET price = CASE
    WHEN category = 'A' THEN price * 1.1
    WHEN category = 'B' THEN price * 1.2
    WHEN category = 'C' THEN price * 1.3
    ELSE price
END;

输出结果：

Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

说明：本例中，使用 CASE 运算符根据产品的类别更新价格，类别为 ‘A’ 时，价格乘以 1.1，类别为 ‘B’ 时，价格乘以 1.2，类别为 ‘C’ 时，价格乘以 1.3，其他类别不变。

使用 CASE 运算符删除数据

DELETE FROM employee
WHERE id IN (
    SELECT id FROM (
        SELECT id, salary,
        CASE
            WHEN salary >= 10000 THEN 'High'
            WHEN salary >= 5000 THEN 'Medium'
            WHEN salary >= 2000 THEN 'Low'
            ELSE 'Very Low'
        END AS level
        FROM employee
    ) AS t
    WHERE level = 'Very Low'
);

输出结果：

Query OK, 2 rows affected (0.01 sec)

说明：本例中，使用 CASE 运算符根据员工的工资删除数据，工资大于等于 10000 时，级别为 ‘High’，工资大于等于 5000 时，级别为 ‘Medium’，工资大于等于 2000 时，级别为 ‘Low’，其他情况为 ‘Very Low’。然后删除级别为 ‘Very Low’ 的员工。

使用 CASE 运算符在存储过程中

DELIMITER //
CREATE PROCEDURE get_discount (IN p_category VARCHAR(10), OUT p_discount DECIMAL(5,2))
BEGIN
    SET p_discount = CASE p_category
        WHEN 'A' THEN 0.1
        WHEN 'B' THEN 0.2
        WHEN 'C' THEN 0.3
        ELSE 0
    END;
END //
DELIMITER ;

CALL get_discount('A', @discount);
SELECT @discount;

输出结果：

+-----------+
| @discount |
+-----------+
|      0.10 |
+-----------+

说明：本例中，创建了一个存储过程，使用 CASE 运算符根据产品的类别返回不同的折扣，类别为 ‘A’ 时，折扣为 0.1，类别为 ‘B’ 时，折扣为 0.2，类别为 ‘C’ 时，折扣为 0.3，其他类别为 0。然后调用该存储过程，传入类别为 ‘A’，返回折扣为 0.1。

相关函数

除了 CASE 运算符，MariaDB 还提供了一些其他的用于根据条件返回值的函数，比如：

  • IF() 函数：根据一个条件返回两个值中的一个。例如：

    SELECT name, score, IF(score >= 60, 'Pass', 'Fail') AS result FROM student;

    输出结果：

    +------+-------+--------+
| name | score | result |
+------+-------+--------+
| Tom  |    95 | Pass   |
| Bob  |    85 | Pass   |
| Alice|    75 | Pass   |
| Jack |    65 | Pass   |
| Lily |   100 | Pass   |
+------+-------+--------+

    说明：本例中，使用 IF() 函数根据学生的分数返回 ‘Pass’ 或 ‘Fail’，分数大于等于 60 时返回 ‘Pass’，否则返回 ‘Fail’。

  • IFNULL() 函数：如果第一个参数不为 NULL，返回第一个参数，否则返回第二个参数。

  • NULLIF() 函数：如果两个参数相等，返回 NULL，否则返回第一个参数。例如：

    SELECT name, age, NULLIF(age, 18) AS new_age FROM person;

    输出结果：

    +------+-----+---------+
| name | age | new_age |
+------+-----+---------+
| Tom  |  20 | 20      |
| Bob  |  17 | 17      |
| Alice|  19 | 19      |
| Jack |  16 | 16      |
| Lily |  18 | NULL    |
+------+-----+---------+

    说明：本例中，使用 NULLIF() 函数根据人的年龄返回新的年龄，年龄等于 18 时返回 NULL，否则返回原来的年龄。

  • COALESCE() 函数：返回第一个不为 NULL 的参数，如果所有参数都为 NULL，返回 NULL。例如：

    SELECT name, phone, email, COALESCE(phone, email, 'N/A') AS contact FROM customer;

    输出结果：

    +------+-------+-----------------+-----------------+
| name | phone | email           | contact         |
+------+-------+-----------------+-----------------+
| Tom  | NULL  | [email protected] | [email protected] |
| Bob  | 12345 | NULL            | 12345           |
| Alice| NULL  | NULL            | N/A             |
| Jack | 67890 | [email protected]| 67890           |
| Lily | NULL  | [email protected]| [email protected]|
+------+-------+-----------------+-----------------+

    说明：本例中，使用 COALESCE() 函数根据客户的电话和邮箱返回联系方式，如果电话不为 NULL，返回电话，如果电话为 NULL，但邮箱不为 NULL，返回邮箱，如果电话和邮箱都为 NULL，返回 ‘N/A’。

结论

MariaDB CASE 运算符是一个用于根据不同的条件返回不同的值的运算符，它可以用于 SELECT、UPDATE、DELETE 等语句中，也可以用于存储过程、函数、触发器等中。CASE 运算符有两种形式：简单形式和搜索形式。简单形式是根据一个表达式的值来匹配不同的情况，搜索形式是根据多个条件来匹配不同的情况。本文介绍了 CASE 运算符的语法，以及一些基础的用法和实例。同时，本文也介绍了一些与 CASE 运算符相关的函数，以及它们的用法和实例。