case表达式---多条件表达式

主题：case表达式—多条件表达式

一、概念

1、基本语法

case when 条件1  then 值1
	when 条件2  then 值2
	when 条件3  then 值3
	....
	else 值n  end

2、执行过程：

整个表达式有一个计算结果：和if else if else 一样

3、语法要求

1）条件是有顺序的

2）所有的条件后面的值，要是同一类型

3）建议不要少了else，如果省了，则所有when都不成立，则返回null

4）end不能少

5）每一个条件后不要打,

二、替换应用

select st_id,(
  CASE WHEN course_id='001' THEN 'java'
      WHEN  course_id='002' THEN 'oracle'
      WHEN  course_id='003' THEN 'python'
      ELSE null END
) as kcm,score
from cjb;

执行流程：

1）从from后面的表中取第一行，用这一行作为参数，执行select

2）select从左至右一个一个列得出结果，作为查询结果第一行的值

3）再取from后面表的下一行，执行上面两步，直到表中所有行

三、行列转换

表的竖横转换

注意：表查询处理单位是from后面表的行

select st_id,
       (case when course_id='001' then score else 0 end) as java,
       (case when course_id='002' then score else 0 end) as oracle,
       (case when course_id='003' then score else 0 end) as python
from cjb
group by st_id

执行流程：

1）选择from后面的表中第一行

2）用第一行的各列的值为参数，代入到select后面的各列的表达式中，计算结果，形成结果的第一行

3）用from后面的表中的下一行，执行12两步，直到from后面表中所有行处理完

select st_id,
       sum(case when course_id='001' then score else 0 end) as java,
       sum(case when course_id='002' then score else 0 end) as oracle,
       sum(case when course_id='003' then score else 0 end) as python
from cjb
group by st_id

练习：查询成绩表，显示为如下结果

学号 java oracle python

010103 已选修 已选修 未选修

select st_id,(case when st_id in(select st_id from cjb where course_id='001') then '已选修' else '未选修' end)as java,
(case when st_id in(select st_id from cjb where course_id='002') then '已选修' else '未选修' end)as oracle,
(case when st_id in(select st_id from cjb where course_id='003') then '已选修' else '未选修' end)as 离散数学
from xsb;

四、多条件约束

为cjb添加成绩约束：001课程【0，100】，002课程【0，120】，003课程【0，150】

方法一：

alter table cjb
    add(constraint ck_score check((case when course_id='001' and score>=0 and score<=100 then 1
                                       when course_id='002' and score>=0 and score<=120 then 1
                                       when course_id='003' and score>=0 and score<=150 then 1
                                       else 0 end)=1 ))                      

方法二：

alter table cjb
    add(constraint ck_score check(case when course_id='001' then case when score>=0 and score<=100 then 1 else 0 end
                                       when course_id='002' then case when score>=0 and score<=120 then 1 else 0 end
                                       when course_id='003' then case when score>=0 and score<=150 then 1 else 0 end
                                       else 1 end =1))

说明：

• check约束的本质是当往数据表中插入数据或更新数据时，执行check的约束语句，得出结果真假，真就插入或插入成功，假就拒绝插入或更新

• case返回的一个普通值，不是真假

• case不是返回一个表达式，而是返回一个表达式的计算结果，这个结果是一个普通的值

• 最后的else的值为0和1的含义是不同的

• 方法1和方法2是有区别的，方法1的else不能返回1

• 为了是的check中的返回值是真假，用case的结果和一个值比较

五、主键值交换

交换两个主键的值，交换xsb中两个学生的学号

select * from xsb
--方法一：
update xsb set st_id='a' where st_id='010110'
update xsb set st_id='010110' where st_id='010111'
update xsb set st_id='010111' where st_id='a'
---上面方法繁琐，还效率低
--方法二：
update xsb 
set st_id=case when st_id='010110' then '010111' 
               when st_id='010111' then '010110'
               else st_id end
where st_id in ('010110','010111')

说明：

• 方法一：如果不用轮换，是会违反主键约束
• 方法二：由于在一次update执行过程中，主键可以相同，但是在执行完毕必须不同

六、多条件更新

修改cjb中的记录的成绩值，【70-80】之间变成1.1倍，如果是【80-90】之间变成原来的0.9
根据上面方法，完成