| MySQL | 您所在的位置：网站首页 › case用法mysql › MySQL |
MySQL
|
概述：sql语句中的case语句与高级语言中的switch语句，是标准sql的语法，适用于一个条件判断有多种值的情况下分别执行不同的操作。 首先，让我们看一下CASE的语法。在一般的SELECT中，其语法格式如下： CASE WHEN THEN WHEN THEN ... WHEN THEN ELSE END ★ 第一部分 # 创建一个用户表 CREATE TABLE `user` (`id` int(10) unsigned NOT NULL auto_increment, `sex` tinyint(1) default 1 COMMENT '性别：0女；1男；2保密',`age` int(3) default 1 COMMENT '年龄',`province` char(254) default NULL COMMENT '所在省份',PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 # 向表中插入测试数据 INSERT INTO user(sex,age,province) VALUES('1','22','北京'),('0','25','广东'),('0','56','天津'),('1','14','北京'),('0','36','广东'),('1','68','湖南'),('1','45','北京'),('1','17','河北'),('2','33','天津'),('1','27','湖南'),('1','29','北京'),('2','70','广东'),('0','24','北京') 数据表如下图： ------------------------------------------------------------------------------------------ 实验1.1：将用户性别用文字直观显示 1.简单Case函数写法(注意sex的位置) select *,(CASE sex WHEN '1' THEN '男' WHEN '0' THEN '女' ELSE '保密' END) as sex_textfrom user2.Case搜索函数写法(注意sex的位置【推荐】) select *,(CASE WHEN sex='1' THEN '男' WHEN sex='0' THEN '女' ELSE '保密' END) as sex_textfrom user
实验1.2：将用户性别用文字直观显示：0女；1男；2保密；并按性别显示排序 select *,(CASE WHEN sex='1' THEN '男' WHEN sex='0' THEN '女' ELSE '保密' END) as sex_textfrom userorder by sex_text DESC
实验1.3：将用户年龄用文字直观显示（涉及数值范围判断，只能使用“Case搜索函数”写法），如下： select *,(CASE WHEN age>=60 THEN '老年' WHEN age=30 THEN '中年' WHEN age=18 THEN '青年' ELSE '未成年' END) as age_textfrom user ------------------------------------------------------------------------------------------实验1.4：综合上面两实验select *,(CASE WHEN sex='1' THEN '男' WHEN sex='0' THEN '女' ELSE '保密' END) as sex_text,(CASE WHEN age>=60 THEN '老年' WHEN age=30 THEN '中年' WHEN age=18 THEN '青年' ELSE '未成年' END) as age_textfrom user
实验1.5：将区域分组，统计华北、华南分别的注册人数select count(*),(CASE province WHEN '北京' THEN '华北' WHEN '天津' THEN '华北' WHEN '河北' THEN '华北' WHEN '广东' THEN '华南' WHEN '湖南' THEN '华南' END) as areafrom usergroup by area
========================================================================================== ★ 第二部分 # 创建数据表 CREATE TABLE `dj_zt` (`id` int(10) unsigned NOT NULL auto_increment, `zt` char(254) default NULL,`bs` char(254) default NULL,`qylx_dm` char(254) default NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 # 插入测试数据 INSERT INTO dj_zt(zt, bs, qylx_dm) VALUES('01','a','01'),('02','b','02'),('03','c','03'),('11','d','03'),('03',null,'04'),('07','f','03'),('12','g','02'),('07','h','03'),('11','i','03'),('07','j','03'),('11','k','04') 实验2.1：查询dj_zt表状态值(zt)为'07'或'11'，当条件为 qylx_dm = '03' 的所有记录数。 A：用CASE语句select count(CASE a.zt WHEN '07' THEN a.bs END) + count(CASE a.zt WHEN '11' THEN a.bs END) as numfrom dj_zt awhere a.qylx_dm = '03' B：不用CASE语句select count(*)from dj_zt awhere a.qylx_dm = '03' and a.zt in ('07', '11')
------------------------------------------------------------------------------------------实验2.2： 分别查询dj_zt表状态为'07'和'11'且qylx_dm = '03'的所有记录数。 A：用CASE语句select count(CASE a.zt WHEN '07' THEN a.bs END) as num1,count(CASE a.zt WHEN '11' THEN a.bs END) as num2from dj_zt awhere a.qylx_dm = '03'
总结：CASE 和 IF的区别:·在高级语言中，CASE的可以用IF来替代，但是在SQL中不行。·CASE是SQL标准定义的，IF是数据库系统的扩展。·CASE可以用于SQL语句和SQL存储过程、触发器，IF只能用于存储过程和触发器。·在SQL过程和触发器中，用IF替代CASE代价都相当的高，相当的麻烦，难以实现。 通过上面几组实例可以看出，应用CASE语句可以让SQL变得简洁高效，从而大大提高了执行效率。而且，CASE的使用一般不会引起性能（相比没有用CASE的语句）低下，反而增加了操作的灵活性 ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ◆ ★ 第三部分 IF语句的用法 ▶ IF(expr1,expr2,expr3) 规则：如果 expr1 是TRUE，则返回expr2， 否则返回expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。 实验3.1：如下： select *,IF(sex='1','男','非男') as sex_textfrom user
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');-> 'no' 如果expr2 或expr3中只有一个明确是 NULL，则IF() 函数的结果类型 为非NULL表达式的结果类型。expr1 作为一个整数值进行计算，就是说，假如你正在验证浮点值或字符串值， 那么应该使用比较运算进行检验。 mysql> SELECT IF(0.1,1,0);-> 0 mysql> SELECT IF(0.10,1,0);-> 1 在所示的第一个例子中，IF(0.1)的返回值为0，原因是 0.1 被转化为整数值，从而引起一个对 IF(0)的检验。这或许不是你想要的情况。在第二个例子中，比较检验了原始浮点值，目的是为了了解是否其为非零值。比较结果使用整数。 ------------------------------------------------------------------------------------------ ▶ IFNULL(expr1,expr2) 规则：假如 expr1 不为 NULL，就返回 expr1，否则返回 expr2。 IFNULL()的返回值是数字或是字符串，具体情况取决于其所使用的语境。 实验3.2：如果字段bs为空就返回字段zt的值 select *,IFNULL(bs,zt)from dj_ztwhere id in (5,6,12)
mysql> SELECT IFNULL(1,0);-> 1 mysql> SELECT IFNULL(NULL,10);-> 10 mysql> SELECT IFNULL(1/0,10);-> 10 mysql> SELECT IFNULL(1/0,'yes');-> 'yes' IFNULL(expr1,expr2)的默认结果值为两个表达式中更加“通用”的一个，顺序为STRING、 REAL或 INTEGER。假设一个基于表达式的表的情况， 或MySQL必须在内存储器中储存一个临时表中IFNULL()的返回值： ------------------------------------------------------------------------------------------ ▶ NULLIF(expr1,expr2) 规则：如果 expr1 = expr2 成立，那么返回值为NULL，否则返回值为 expr1。 这和CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同。 实验3.4：如果字段zt 与 字段qylx_dm有相等的值，就返回空，否则返回zt select *,NULLIF(zt,qylx_dm)from dj_zt mysql> SELECT NULLIF(1,1);-> NULL mysql> SELECT NULLIF(1,2);-> 1 注意，如果参数不相等，则 MySQL 两次求得的值为 expr1 |
总结：简单Case函数写法只适合相等条件判断，不能用于大于、小于及不等于的判断， Case搜索函数写法适合复杂条件判断：可用于大于、小于及不等于的判断。------------------------------------------------------------------------------------------
总结：用临时生成的字段是可以排序的，MySQL查询流程：先对表数据查询，查出数据后再排序显示。------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
总结：同样道理，临时生成的字段 area 是可以在查询结束后，用来做排序或分组的。
结果：A、B两组耗费的代价一样的，相比较B的写法简洁，平局。
B：不用CASE语句(写了两条语句，扫描表两遍,效率明显低下)select count(*)from dj_zt awhere a.qylx_dm = '03' and a.zt='07'
select count(*)from dj_zt awhere a.qylx_dm = '03' and a.zt='11'
结果：B组代价明显高出A组很多，执行的效率比较低。
总结：IF不像CASE那样可以多条件判断，IF只能判断“真”、“假”；mysql> SELECT IF(1>2,2,3);-> 3 mysql> SELECT IF(1 'yes'
实验3.3：如果字段bs为空就返回'ZZX'select *,IFNULL(bs,'ZZX')from dj_ztwhere id in (5,6,12)
【本文地址】