1、CASE WHEN
case when 语句能在SQL语句中放入判断逻辑,类似Java中学习的 if else 语句。
case when 语句分为简单函数和条件表达式。
(1)简单函数
case 字段 when 预期值 then 结果1 else 结果2 end
如果字段等于预期值,则返回结果1,否则返回结果2,end代表语句结束。
例如在表中sex为男改为女,为女改为男,代码展示:
select name,(case sex when '男' then '女' else '男' end)sex from student;
结果对比展示:
(2)条件表达式
上述简单函数使用简单,但引用范围不广,无法应对复杂的场景,这就需要用到条件表达式了,语法结构是:
case
when 条件判断 than result1 else result2
end
当条件判断为真是返回result1,否则返回result2,如果没有else则返回null。case 和 end之间可以有多个when ....... then ......else 语句,end表示case语句结束。
例如员工薪水等级,代码展示:
select tb_emp.name,(case
when salary between 0 and 3000 then 1
when salary between 3001 and 5000 then 2
when salary between 5001 and 8000 then 3
when salary between 8001 and 10000 then 4
when salary between 10001 and 15000 then 5
when salary between 15001 and 20000 then 6
when salary between 20001 and 25000 then 7
when salary between 25001 and 30000 then 8
end
)grade from tb_emp;
结果展示(图一员工表,图二薪水等级,图三结果图):
(3)综合使用
case when 和聚合函数使用,能实现更加复杂的统计功能
例如:统计有多少个男生和女生以及男女生青年人数的有几个
select
sum(case when stu_sex='男' then 1 else 0 end) as '男生人数',
sum(case when stu_sex='女' then 1 else 0 end) as '女生人数',
sum(case when stu_age<20 and stu_sex='男' then 1 else 0 end) as '男生青年人数',
sum(case when stu_age<20 and stu_sex='女' then 1 else 0 end) as '女生青年人数'
from student1;
2、IF
if函数也能通过判断条件来返回特定值,语法结构是:
if (表达式,result_true,result_false)
当表达式结果对时,返回result_true,否则返回result_false。
例如变更性别,男变女,女变男
select stu_name,if(stu_sex='男','女','男')stu_sex from student1;
结果展示:
能看出 if 函数和case when 函数有同样的效果,但 if 相对简单,case when 可以运用到更复杂的判断,另外,if 函数也可以和聚合函数使用,例如看一个班男生女生有多少人:
select sum(if(stu_sex='男',1,0)) '男生人数',sum(if(stu_sex='女',1,0)) '女生人数' from student1;
结果展示(图一学生信息,图二结果图)
3、IFNULL
在程序中调用sql语句时,如果返回结果是null,是非常容易引发一些意外情况,因此希望在sql中做一些处理,如果查询结果是null ,就转为特定的值,就要用到Mysql中 ifnull 函数。
sql一般写法是:
select salary from student where name='张三';
使用ifnull 进行改写
select ifnull(salary,0) salary from student where name='张三';
但使用ifnull 语句,如果where 条件中name 值是不存在的,那么仍将返回null 。
返回结果:null
select ifnull(salary,0) salary from student where name='张三';
返回结果:0
select ifnull((select salary from student where name='张三'),0) salary;
在实际应用中,如果确定where 条件的值一定存在,使用者可以使用前者,否则要用后者。ifnull 函数还可以结合聚合函数使用。
avg、count等用同样方式处理,而且,无论where条件存不存在,结果都会返回0。