小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL江湖路 | 专栏目录
说到if else 你肯定不陌生,这种判断函数在各种编程语言中是家常便饭,但在编写SQL语句中,或许你就很少用到了,甚至还没怎么玩儿过。
在MySQL中基于对条件判断的函数又叫“控制流函数”,用于mysql语句中的逻辑判断。数据库技术:MySQL常用判断函数总结!!看你都用过没带大家一起来看一看MySQL中都有哪些常用的控制流函数,以及控制流函数的使用场景都有哪些?
目录
- 一、函数:CASE WHEN … THEN … ELSE … END
-
- 1、用在更新语句的更新条件中
- 2、用在查询语句的返回值中
- 3、用在分组查询语句中
- 二、函数:IF(expr,if_true_expr,if_false_expr)
- 三、函数:IFNULL(expr1,expr2)
- 附、一张有故事的照片(九)
一、函数: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)
附、一张有故事的照片(九)
这张图片来自08年汶川;
在地震中痛失妻子的男子饱含深情,
不忍将亡妻弃之野外,
将其身体与自己绑在一起,
用摩托车载着她前往当地的太平间。
在极大悲痛的折磨中,
他仍努力要给妻子些许死后的尊严。
需要了解更多数据库技术:MySQL常用判断函数总结!!看你都用过没,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!
本文来自网络收集,不代表计算机技术网立场,如涉及侵权请联系管理员删除。
ctvol管理联系方式QQ:251552304
本文章地址:https://www.ctvol.com/dtteaching/813669.html