【MySQL】MySQL进阶教程学习笔记1

CASE表达式概述

• CASE表达式的写法

  

  CASE WHEN sex = '1' THEN '男'
  	 WHEN sex = '2' THEN '女'
  ELSE '其他' END
  

• case 表达式的真假判断在发现为真的when 子句时，CASE表达式的真假值判断就会终止，剩余的when子句会被忽略。

• 因此，在使用when子句时需要注意条件的【排他性】

用一条SQL语句进行不同条件的统计

• 进行不同条件的统计是CASE表达式的著名用法之一

  

SELECT pref_name,
       /* 男性人口 */
       SUM( CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_m,
       /* 女性人口 */
       SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_f
  FROM PopTbl2
 GROUP BY pref_name;

用CHECK 约束定义多个列的条件关系

• 蕴含式与逻辑与

  P^Q 要想让逻辑与为真，需要两个命题均为真，或者一个为真，另一个未知

  P >> Q要想让蕴含式为真，则需要两个命题均为真，或者P为假，或者P无法判断真假

• 相比而言，蕴含式的约束较为宽松

在Update 语句中进行条件分支

/* 用CASE表达式写正确的更新操作 */
UPDATE Salaries
   SET salary = CASE WHEN salary >= 300000
                     THEN salary * 0.9
                     WHEN salary >= 250000 AND salary < 280000
                     THEN salary * 1.2
                     ELSE salary END;

• 调换主键值a,b

/* 用CASE表达式调换主键值 */
UPDATE SomeTable
   SET p_key = CASE WHEN p_key = 'a'
                    THEN 'b'
                    WHEN p_key = 'b'
                    THEN 'a'
                    ELSE p_key END
 WHERE p_key IN ('a', 'b');

表之间的数据匹配

• CASE 表达式的一大优势，是能够判断表达式
• 在CASE表达式里，可以使用between 、like 、<、 > 等便利的谓词组合，以及能嵌套子查询的in和exists谓词。因此，CASE表达式具有非常强大的表达能力。

• 现在需要用这两张表来生成交叉表，以便于一目了然的知道每个月开设的课程

/* 表的匹配：使用IN谓词 */
SELECT CM.course_name,
       CASE WHEN CM.course_id IN 
                    (SELECT course_id FROM OpenCourses 
                      WHERE month = 200706) THEN '○'
            ELSE '×' END AS "6月",
       CASE WHEN CM.course_id IN 
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 200707) THEN '○'
            ELSE '×' END AS "7月",
       CASE WHEN CM.course_id IN 
                    (SELECT course_id FROM OpenCourses
                      WHERE month = 200708) THEN '○'
            ELSE '×' END  AS "8月"
  FROM CourseMaster CM;

/* 表的匹配：使用EXISTS谓词 */
SELECT CM.course_name,
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200706
                        AND CM.course_id = OC.course_id) THEN '○'
            ELSE '×' END AS "6月",
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200707
                        AND CM.course_id = OC.course_id) THEN '○'
            ELSE '×' END AS "7月",
       CASE WHEN EXISTS
                    (SELECT course_id FROM OpenCourses OC
                      WHERE month = 200708
                        AND CM.course_id = OC.course_id) THEN '○'
            ELSE '×' END  AS "8月"
  FROM CourseMaster CM;
														

• 从性能方面来说，exists表现更好。通过exists进行的子查询能够用到’month，course_id’这样的主键索引，尤其在表opencourses里的数据较多时更有优势！

在CASE表达式中使用聚合函数

根据上表查询
1.获取只加入了一个社团的学生的社团ID
2.获取加入了多个社团的学生的主社团ID

--方法一
select std_id, max(club_id) as main_club 
from studentclub 
group by std_id 
having count(*) = 1;

select std_id,club_id as main_club 
from studentclub
where main_club_flg = 'Y';

--方法二  使用case
select std_id, 
case when count(*) = 1 
	then max(club_id)
else max(case when main_club_flg = 'Y'
				then club_id
				else NUll end)
end as main_club
from studentclub
group by std_id;

CASE 表达式用在SELECT子句里时，既可以写在聚合函数内部，也可以写在聚合函数外部。

* 作为表达式，CASE表达式在执行时会被判定为一个固定值，因此它可以写在聚合函数内部；也正因为它是表达式，所以还可以写在SELECT子句，GROUP BY子句，WHERE子句，ORDER BY子句里。简单而言，在能够写列名和常量的地方，通常都是可以写CASE表达式的。