《SQL进阶教程》学习

2021/10/31

今天开始学习SQL进阶教程。听了两天的培训讲座，受益还挺多，如果我是想要毕业直接去工作的话，实习的经历非常重要，可以多多学习待人处事的方法，当然，技术的学习也非常重要。所以加油吧！

CASE表达式

CASE表达式概述

简单CASE表达式

CASE sex
    when '1' then '男'
    when '2' then '女‘
ELSE '其他' END

搜索CASE表达式

CASE when sex='1' then '男'
        when sex='2' then '女'
else '其他' end

使用CASE表达式需要注意的事情

我们在编写 SQL 语句的时候需要注意，在发现为真的 WHEN 子句时， CASE 表达式的真假值判断就会中止，而剩余的 WHEN 子句会被忽略。为了 避免引起不必要的混乱，使用 WHEN 子句时要注意条件的排他性。

CASE WHEN COL_1 IN ('a','b') then '第一'
        when col_1 in ('a') then '第二'
else '其他' end

例如这条SQL语句，结果里不会出现“第二”，因为判断第一条WHEN子句为TRUE，剩余的WHEN子句就被忽略了。

注意事项1：统一各分支返回的数据类型

        CASE表达式里各个分支返回的数据类型都要一致。

注意事项2：不要忘了写END

注意事项3：养成写ELSE子句的习惯

        虽然不写ELSE子句也不会报错（不写ELSE子句时，CASE表达式的执行结果时NULL）。但是不写可能会造成“语法没有错误，结果却不对”的情况。养成习惯可以减少失误。

将已有编号方式转换为新的方式并统计

-- 把县编号转换成地区编号 (1)
SELECT CASE pref_name

 WHEN '德岛' THEN '四国'
 WHEN '香川' THEN '四国'
 WHEN '爱媛' THEN '四国'
 WHEN '高知' THEN '四国'
 WHEN '福冈' THEN '九州'
 WHEN '佐贺' THEN '九州'
 WHEN '长崎' THEN '九州'
 ELSE '其他' END AS district,

 SUM(population)
 FROM PopTbl

 GROUP BY CASE pref_name
 WHEN '德岛' THEN '四国'
 WHEN '香川' THEN '四国'
 WHEN '爱媛' THEN '四国'
 WHEN '高知' THEN '四国'
 WHEN '福冈' THEN '九州'
 WHEN '佐贺' THEN '九州'
 WHEN '长崎' THEN '九州'
 ELSE '其他' END;

   这里的关键在于将 SELECT 子句里的 CASE 表达式复制到 GROUP BY 子句里。需要注意的是，如果对转换前的列“pref_name”进行 GROUP BY，就得不到正确的结果（因为这并不会引起语法错误，所以容易被忽视）。     

-- 按人口数量等级划分都道府县
SELECT 
    CASE WHEN population < 100 THEN '01'
    WHEN population >= 100 AND population < 200 THEN '02'
    WHEN population >= 200 AND population < 300 THEN '03'
    WHEN population >= 300 THEN '04'
    ELSE NULL END AS pop_class,
 COUNT(*) AS cnt
 FROM PopTbl

 GROUP BY CASE WHEN population < 100 THEN '01'
 WHEN population >= 100 AND population < 200 THEN '02'
 WHEN population >= 200 AND population < 300 THEN '03'
 WHEN population >= 300 THEN '04'
 ELSE NULL END;

pop_class cnt
--------- ----
01 1
02 3
03 3
04 2

 自己尝试写的。

这个技巧非常好用。不过，必须在 SELECT 子句和 GROUP BY 子句这 两处写一样的 CASE 表达式，这有点儿麻烦。后期需要修改的时候，很容 易发生只改了这一处而忘掉改另一处的失误。 所以，如果我们可以像下面这样写，那就方便多了。

-- 把县编号转换成地区编号 (2) ：将 CASE 表达式归纳到一处
SELECT CASE pref_name
 WHEN '德岛' THEN '四国'
 WHEN '香川' THEN '四国'
 WHEN '爱媛' THEN '四国'
 WHEN '高知' THEN '四国'
 WHEN '福冈' THEN '九州'
 WHEN '佐贺' THEN '九州'
 WHEN '长崎' THEN '九州'
 ELSE '其他' END AS district,
 SUM(population)
FROM PopTbl
GROUP BY district;

 但是严格来说，这种写法是违反标准 SQL 的规则的。 因为 GROUP BY 子句比 SELECT 语句先执行，所以在 GROUP BY 子句中引 用在 SELECT 子句里定义的别称是不被允许的。事实上，在 Oracle、DB2、 SQL Server 等数据库里采用这种写法时就会出错。

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

-- 男性人口
SELECT pref_name,
 SUM(population)
 FROM PopTbl2
 WHERE sex = '1'
 GROUP BY pref_name;
-- 女性人口
SELECT pref_name,
 SUM(population)
 FROM PopTbl2
 WHERE sex = '2'
 GROUP BY pref_name;

--再用UNION连接

如果使用 CASE 表达式，下面这一条 简单的 SQL 语句就可以搞定。

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;

上面这段代码所做的是，分别统计每个县的“男性”（即 '1'）人数和 “女性”（即 '2'）人数。也就是说，这里是将“行结构”的数据转换成了“列 结构”的数据。除了 SUM，COUNT、AVG 等聚合函数也都可以用于将行结构的数据转换成列结构的数据。

这个技巧可贵的地方在于，它能将 SQL 的查询结果转换为二维表的格式。

新手用 WHERE 子句进行条件分支，高手用 SELECT 子句进行条件 分支。

 自己写的，不过不是一个好例子（捂脸）。

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

CASE 表达式和 CHECK 约束是很般配的一对组合。

--假设某公司规定“女性员工的工资必须在 20 万日元以下”
CONSTRAINT check_salary CHECK
        (CASE WHEN sex='2'
            THEN CASE WHEN salary <= 2000
                    THEN 1 ELSE 0 END
            ELSE 1 END =1)

在这段代码里，CASE 表达式被嵌入到 CHECK 约束里，描述了“如果 是女性员工，则工资是 20 万日元以下”这个命题。在命题逻辑中，该命题是叫作蕴含式（conditional）的逻辑表达式，记作 P → Q。

这里需要重点理解的是蕴含式和逻辑与（logical product）的区别。逻 辑与也是一个逻辑表达式，意思是“P 且 Q”，记作 P ∧ Q。用逻辑与改 写的 CHECK 约束如下所示。

CONSTRAINT check_salary CHECK
        (sex = '2' and salary<=2000)

 

在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;

-- 条件 1
UPDATE Salaries
 SET salary = salary * 0.9
 WHERE salary >= 300000;
-- 条件 2
UPDATE Salaries
 SET salary = salary * 1.2
 WHERE salary >= 250000 AND salary < 280000;
--然后用UNION连接

这种写法是错误的。

这个技巧的应用范围很广。例如，可以用它简单地完成主键值调换这 种繁重的工作。通常，当我们想调换主键值 a 和 b 时，需要将主键值临时 转换成某个中间值。使用这种方法时需要执行 3 次 UPDATE 操作，但是如 果使用 CASE 表达式，1 次就可以做到。

--1. 将 a 转换为中间值 d
UPDATE SomeTable
 SET p_key = 'd'
 WHERE p_key = 'a';

--2. 将 b 调换为 a
UPDATE SomeTable
 SET p_key = 'a'
1-1　CASE表达式 13 ●
 WHERE p_key = 'b';

--3. 将 d 调换为 b
UPDATE SomeTable
 SET p_key = 'b'
 WHERE p_key = 'd';

-- 用 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');

显而易见，这条 SQL 语句按照“如果是 a 则更新为 b，如果是 b 则 更新为 a”这样的条件分支进行了 UPDATE 操作。不只是主键，唯一键的 调换也可以用同样的方法进行。本例的关键点和上一例的加薪与降薪一样， 即用 CASE 表达式的条件分支进行的更新操作是一气呵成的，因此可以避免出现主键重复所导致的错误。

表之间的数据匹配

与 DECODE 函数等相比，CASE 表达式的一大优势在于能够判断表达式。 也就是说，在 CASE 表达式里，我们可以使用 BETWEEN、LIKE 和 等 便利的谓词组合，以及能嵌套子查询的 IN 和 EXISTS 谓词。因此，CASE 表达式具有非常强大的表达能力。

--我们需要做的是，检查表 OpenCourses 中的各月里有表 CourseMaster
--中的哪些课程。这个匹配条件可以用 CASE 表达式来写。

--表的匹配：使用IN谓词
SELECT course_name,
     CASE WHEN course_id IN 
         (SELECT course_id FROM OpenCourses 
         WHERE month = 200706) THEN '○'