欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

MySQL常用判断函数总结！！看你都用过没

程序员文章站 2024-03-16 19:07:34
...

小伙伴想精准查找自己想看的MySQL文章？喏 → MySQL江湖路 | 专栏目录

  说到if else 你肯定不陌生，这种判断函数在各种编程语言中是家常便饭，但在编写SQL语句中，或许你就很少用到了，甚至还没怎么玩儿过。

  在MySQL中基于对条件判断的函数又叫“控制流函数”，用于mysql语句中的逻辑判断。本文带大家一起来看一看MySQL中都有哪些常用的控制流函数，以及控制流函数的使用场景都有哪些？

MySQL常用判断函数总结！！看你都用过没

目录

一、函数：CASE WHEN … THEN … ELSE … END

  在SQL语句中，"CASE WHEN … THEN … ELSE … END"是较常见的用来判断的语句，适用于增删改查各类语句中，公式如下：

CASE expression
WHEN if_true_expr THEN return_value1
WHEN if_true_expr THEN return_value2
WHEN if_true_expr THEN return_value3
……
ELSE default_return_value
END

1、用在更新语句的更新条件中

  • 给个情景1：妇女节大回馈，2020年注册的新用户，所有成年女性账号送10元红包，其他用户送5元红包，自动充值。

示例语句如下：

-- 送红包语句
UPDATE users_info u 
    SET u.balance = CASE WHEN u.sex ='女' and u.age > 18 THEN u.balance + 10 
                         ELSE u.balance + 5 end 
                         WHERE u.create_time >= '2020-01-01'

需要注意的点，Case函数只返回第一个符合条件的值，剩下的Case when部分将会被自动忽略

2、用在查询语句的返回值中

  • 给个情景2：有个学生高考分数表，需要将等级列出来，650分以上是重点大学，600-650是一本，500-600分是二本，400-500是三本，400以下大专；

原测试数据如下：

mysql> select * from student_score;
+-----+-----------+-------------+------+
| SID | S_NAME    | TOTAL_SCORE | RANK |
+-----+-----------+-------------+------+
|   1 | 陈哈哈    |         385 | 1760 |
|   2 | 扈亚鹏    |         491 | 1170 |
|   3 | 刘晓莉    |         508 | 1000 |
|   5 | 徐立楠    |         599 |  701 |
|   6 | 顾昊      |         601 |  664 |
|   7 | 陈子凝    |         680 |    9 |
|  14 | 朱志鹏    |         335 | 1810 |
|  19 | 李昂      |         550 |  766 |
+-----+-----------+-------------+------+
8 rows in set (0.00 sec)

查询语句：

SELECT *,case when total_score >= 650  THEN '重点大学' 
              when total_score >= 600 and total_score <650 THEN '一本'
              when total_score >= 500 and total_score <600 THEN '二本'
              when total_score >= 400 and total_score <500 THEN '三本'        
              else '大专' end as status_student 
              from student_score;

mysql> SELECT *,case when total_score >= 650  THEN '重点大学' 
    ->               when total_score >= 600 and total_score <650 THEN '一本'
    ->               when total_score >= 500 and total_score <600 THEN '二本'
    ->               when total_score >= 400 and total_score <500 THEN '三本'        
    ->               else '大专' end as status_student 
    ->               from student_score;
+-----+-----------+-------------+------+----------------+
| SID | S_NAME    | TOTAL_SCORE | RANK | status_student |
+-----+-----------+-------------+------+----------------+
|   1 | 陈哈哈    |         385 | 1760 | 大专           |
|   2 | 扈亚鹏    |         491 | 1170 | 三本           |
|   3 | 刘晓莉    |         508 | 1000 | 二本           |
|   5 | 徐立楠    |         599 |  701 | 二本           |
|   6 | 顾昊      |         601 |  664 | 一本           |
|   7 | 陈子凝    |         680 |    9 | 重点大学       |
|  14 | 朱志鹏    |         335 | 1810 | 大专           |
|  19 | 李昂      |         550 |  766 | 二本           |
+-----+-----------+-------------+------+----------------+
8 rows in set (0.00 sec)

3、用在分组查询语句中

  • 给个情景3：用户包括中国各个省市，需要以省为单位进行统计，山东省、广州省和其他省市的用户数量；（这里用于测试使用，实际情况下讲道理表中应该会有归属省一列或者有另一张归属地表。）

数据如下：

mysql> select * from users_area;
+----+--------------+-------------+
| id | city         | users_count |
+----+--------------+-------------+
|  1 | 北京         |         650 |
|  2 | 上海         |         500 |
|  3 | 济南         |         300 |
|  4 | 青岛         |         100 |
|  5 | 广州         |         350 |
|  6 | 深圳         |         400 |
|  7 | 枣庄         |         120 |
|  8 | 乌鲁木齐     |          80 |
+----+--------------+-------------+
8 rows in set (0.00 sec)

分组查询SQL：

SELECT
    SUM(c.users_count) AS '用户数量',
    CASE c.city
        WHEN '济南'   THEN '山东省'
        WHEN '青岛'   THEN '山东省'
        WHEN '枣庄'   THEN '山东省'
        WHEN '广州'   THEN '广东省'
        WHEN '深圳'   THEN '广东省'
 ELSE '其他' END AS '归属省'
FROM
    users_area c
GROUP BY CASE c.city
        WHEN '济南'   THEN '山东省'
        WHEN '青岛'   THEN '山东省'
        WHEN '枣庄'   THEN '山东省'
        WHEN '广州'   THEN '广东省'
        WHEN '深圳'   THEN '广东省'
 ELSE '其他' END;

查询结果：

mysql> SELECT
    ->     SUM(c.users_count) AS '用户数量',
    ->     CASE c.city
    ->         WHEN '济南'   THEN '山东省'
    ->         WHEN '青岛'   THEN '山东省'
    ->         WHEN '枣庄'   THEN '山东省'
    ->         WHEN '广州'   THEN '广东省'
    ->         WHEN '深圳'   THEN '广东省'
    ->  ELSE '其他' END AS '归属省'
    -> FROM
    ->     users_area c
    -> GROUP BY CASE c.city
    ->         WHEN '济南'   THEN '山东省'
    ->         WHEN '青岛'   THEN '山东省'
    ->         WHEN '枣庄'   THEN '山东省'
    ->         WHEN '广州'   THEN '广东省'
    ->         WHEN '深圳'   THEN '广东省'
    ->  ELSE '其他' END;
+--------------+-----------+
| 用户数量     | 归属省    |
+--------------+-----------+
|         1230 | 其他      |
|          520 | 山东省    |
|          750 | 广东省    |
+--------------+-----------+
3 rows in set (0.00 sec)

二、函数：IF(expr,if_true_expr,if_false_expr)

在mysql中if()函数的用法类似于java中的三目表达式，具体语法如下：

  IF(expr,if_true_expr,if_false_expr)，如果expr的值为true，则返回if_true_expr的值，如果expr的值为false，则返回if_false_expr的值。

  • 使用场景1：IF函数通常用于真实数据被替代的列；如性别，我们在库中一般用tinyint存储，男 = 1，女 = 2；如查询时需转成字符，该场景就适用于IF函数。

原数据：

mysql> select * from student;
+----+-----------+-----+---------+-----------+
| ID | NAME      | SEX | GRADE   | HOBBY     |
+----+-----------+-----+---------+-----------+
|  1 | 陈哈哈    | 1   | 9年级   | 上网      |
|  2 | 扈亚鹏    | 1   | 9年级   | 美食      |
|  3 | 刘晓莉    | 2   | 9年级   | 金希澈    |
|  5 | 徐立楠    | 2   | 9年级   | 阅读      |
|  6 | 顾昊      | 1   | 9年级   | 篮球      |
|  7 | 陈子凝    | 2   | 9年级   | 看电影    |
| 14 | 朱志鹏    | 1   | 9年级   | 看小说    |
| 15 | 贾旭      | 1   | 9年级   | 吹牛逼    |
| 19 | 李昂      | 1   | 9年级   | 看片儿    |
+----+-----------+-----+---------+-----------+
9 rows in set (0.00 sec)

处理sex字段为字符格式展示；

mysql> SELECT `NAME`,IF(sex = 1,'男','女') FROM student;
+-----------+-------------------------+
| NAME      | IF(sex = 1,'男','女')   |
+-----------+-------------------------+
| 陈哈哈    | 男                      |
| 扈亚鹏    | 男                      |
| 刘晓莉    | 女                      |
| 徐立楠    | 女                      |
| 顾昊      | 男                      |
| 陈子凝    | 女                      |
| 朱志鹏    | 男                      |
| 贾旭      | 男                      |
| 李昂      | 男                      |
+-----------+-------------------------+
9 rows in set (0.00 sec)

如果将(1,2)格式数据改为(‘男’,‘女’)也可以通过IF函数修改(记得先修改列类型)，SQL如下：

mysql> UPDATE student set sex = IF(sex = 1,'男','女');
Query OK, 9 rows affected (0.06 sec)
Rows matched: 9  Changed: 9  Warnings: 0

修改后数据：

mysql> select * from student;
+----+-----------+-----+---------+-----------+
| ID | NAME      | SEX | GRADE   | HOBBY     |
+----+-----------+-----+---------+-----------+
|  1 | 陈哈哈    | 男  | 9年级   | 上网      |
|  2 | 扈亚鹏    | 男  | 9年级   | 美食      |
|  3 | 刘晓莉    | 女  | 9年级   | 金希澈    |
|  5 | 徐立楠    | 女  | 9年级   | 阅读      |
|  6 | 顾昊      | 男  | 9年级   | 篮球      |
|  7 | 陈子凝    | 女  | 9年级   | 看电影    |
| 14 | 朱志鹏    | 男  | 9年级   | 看小说    |
| 15 | 贾旭      | 男  | 9年级   | 吹牛逼    |
| 19 | 李昂      | 男  | 9年级   | 看片儿    |
+----+-----------+-----+---------+-----------+
9 rows in set (0.00 sec)
  • 使用场景2：沿用上面的班级表，查询男生和女生的总人数；SQL如下：
    （sex='男’的返回1，然后用SUM相加得出男生人数，女生同理。）
SELECT SUM(IF(sex = '男',1,0)) as boyNum, SUM(IF(sex = '女',1,0)) as girlNum from student;

mysql> SELECT SUM(IF(sex = '男',1,0)) as boyNum,SUM(IF(sex = '女',1,0)) as girlNum from student;
+--------+---------+
| boyNum | girlNum |
+--------+---------+
|      6 |       3 |
+--------+---------+
1 row in set (0.00 sec)

三、函数：IFNULL(expr1,expr2)

  IFNULL函数是MySQL控制流函数之一，它有两个参数，两个参数可以是真实值或表达式，如果expr1不是NULL，则返回第一个参数（expr1）。 否则，IFNULL函数返回第二个参数。

原始数据：

mysql> select * from student;
+----+-----------+------+---------+-----------+
| ID | NAME      | SEX  | GRADE   | HOBBY     |
+----+-----------+------+---------+-----------+
|  1 | 陈哈哈    | 男   | 9年级   | 上网      |
|  2 | 扈亚鹏    | 男   | 9年级   | 美食      |
|  3 | 刘晓莉    | 女   | 9年级   | 金希澈    |
|  5 | 徐立楠    | 女   | 9年级   | 阅读      |
|  6 | 顾昊      | 男   | 9年级   | 篮球      |
|  7 | 陈子凝    | 女   | 9年级   | 看电影    |
| 14 | 朱志鹏    | NULL | 9年级   | 看小说    |
| 19 | 李昂      | NULL | 9年级   | 看片儿    |
+----+-----------+------+---------+-----------+
8 rows in set (0.00 sec)

将SEX为NULL的数据展示为：‘未知’：

mysql> SELECT `NAME`,IFNULL(sex,'未知') from student;
+-----------+----------------------+
| NAME      | IFNULL(sex,'未知')   |
+-----------+----------------------+
| 陈哈哈    | 男                   |
| 扈亚鹏    | 男                   |
| 刘晓莉    | 女                   |
| 徐立楠    | 女                   |
| 顾昊      | 男                   |
| 陈子凝    | 女                   |
| 朱志鹏    | 未知                 |
| 李昂      | 未知                 |
+-----------+----------------------+
8 rows in set (0.00 sec)

附、一张有故事的照片（九）

MySQL常用判断函数总结！！看你都用过没

这张图片来自08年汶川；
在地震中痛失妻子的男子饱含深情，
不忍将亡妻弃之野外，
将其身体与自己绑在一起，
用摩托车载着她前往当地的太平间。
在极大悲痛的折磨中，
他仍努力要给妻子些许死后的尊严。

相关标签： MySQL江湖路 mysql 控制流函数 判断语句 SQL技巧

上一篇： MySQL定时任务（EVENT|事件）如何配置，必会技能！

下一篇： Java NIO使用及原理分析 (一) 博客分类： Java基础 javaNIOIO 

推荐阅读