《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 '○'
       ELSE '×' END AS "6 月",
     CASE WHEN course_id IN 
         (SELECT course_id FROM OpenCourses
         WHERE month = 200707) THEN '○'
       ELSE '×' END AS "7 月",
     CASE WHEN course_id IN 
         (SELECT course_id FROM OpenCourses
         WHERE month = 200708) THEN '○'
       ELSE '×' END AS "8 月"
 FROM CourseMaster;

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

 这样的查询没有进行聚合,因此也不需要排序,月份增加的时候仅修 改 SELECT 子句就可以了,扩展性比较好。

无论使用 IN 还是 EXISTS,得到的结果是一样的,但从性能方面来说, EXISTS 更好。通过 EXISTS 进行的子查询能够用到“month, course_ id”这样的主键索引,因此尤其是当表 OpenCourses 里数据比较多的时候 更有优势

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

 接下来,我们按照下面的条件查询这张表里的数据。

1. 获取只加入了一个社团的学生的社团 ID。

2. 获取加入了多个社团的学生的主社团 ID。

很容易想到的办法是,针对两个条件分别写 SQL 语句来查询。要想 知道学生“是否加入了多个社团”,我们需要用 HAVING 子句对聚合结果 进行判断。

-- 条件 1 :选择只加入了一个社团的学生
SELECT std_id, MAX(club_id) AS main_club
 FROM StudentClub
 GROUP BY std_id
HAVING COUNT(*) = 1;

std_id main_club
------ ----------
300     4
400     5
500     6
-- 条件 2 :选择加入了多个社团的学生
SELECT std_id, club_id AS main_club
 FROM StudentClub
 WHERE main_club_flg = 'Y' ;

std_id main_club
------ ----------
100     1
200     3
--用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;

std_id main_club
------ ----------
100 1
200 3
300 4
400 5
500 6

通过这道例题我们可以明白:CASE 表达式用在 SELECT 子句里时,既 可以写在聚合函数内部,也可以写在聚合函数外部。这种高度自由的写法 正是 CASE 表达式的魅力所在。

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

练习题

1.

-- 求 x 和 y 和 z 三者中的最大值
SELECT key,
 CASE WHEN (CASE WHEN x < y THEN y ELSE x END) < z 
 THEN z
 ELSE (CASE WHEN x < y THEN y ELSE x END )
 END AS greatest
 FROM Greatests;

 2.

 使用 CASE 表达式对表格进行水平展开时,在某个列的统计中使用过 的行的数据也可以用于其他列的统计。从这一点上来说,各个列之间是相 互独立的。因此,我们分别按照“全国”“四国”这样的条件进行汇总就 可以了。

select sex,
    sum(population ) as total,
    sum(case when pref_name='德岛' then population else 0 end ) as col_1,
    sum(case when pref_name='香川' then population else 0 end) as col_2,
    sum(case when pref_name='爱媛' then population else 0 end) as col_3,
    sum(case when pref_name='高知' then population else 0 end) as col_4,
    sum(case pref_name in('德岛','香川','爱媛','高知')
            then population else o end) as zaijie
from PopTbl2
group by sex;

3.

SELECT key
 FROM Greatests
 ORDER BY CASE key
 WHEN 'B' THEN 1
 WHEN 'A' THEN 2
 WHEN 'D' THEN 3
 WHEN 'C' THEN 4
 ELSE NULL END;
key
---
B
A
D
C
如果想在结果中展示“排序”列,需要把“排序”列放到 SELECT 子
句中。
SELECT key,
 CASE key
 WHEN 'B' THEN 1
 WHEN 'A' THEN 2
 WHEN 'D' THEN 3
 WHEN 'C' THEN 4
 ELSE NULL END AS sort_col
 FROM Greatests
ORDER BY sort_col;

key sort_col
--- --------
B     1
A     2
D     3
C     4

2021/11/1

尾款人今天预支了这个月一半的生活费,55555555,助管工作还莫名其妙没了,笑死,工资没了。加油,SQL人。

自连接的用法

针对相同 的表进行的连接被称为“自连接”(self join)。

可重复排列、排列、组合

这里所说的组合其实分为两种类型。一种是有顺序的有序对(ordered pair),另一种是无顺序的无序对(unordered pair)。有序对用尖括号括起来, 如 ;无序对用花括号括起来,如{1, 2}。在有序对里,如果元素顺序相反, 那就是不同的对,因此 ≠ ;而无序对与顺序无关,因此 {1, 2}= {2, 1}。用学校里学到的术语来说,这两类分别对应着“排列”和“组合”。

用 SQL 生成有序对非常简单。像下面这样通过交叉连接生成笛卡儿 积(直积),就可以得到有序对。

 执行结果里每一行(记录)都是一个有序对。因为是可重排列,所以 结果行数为 3^2 = 9。结果里出现了(苹果 , 苹果)这种由相同元素构成的对, 而且(橘子 , 苹果)和(苹果 , 橘子)这种只是调换了元素顺序的对也被 当作不同的对了。这是因为,该查询在生成结果集合时会区分顺序

接下来,我们思考一下如何更改才能排除掉由相同元素构成的对。首 先,为了去掉(苹果 , 苹果)这种由相同元素构成的对,需要像下面这样 加上一个条件,然后再进行连接运算。

 加上 WHERE P1.name <> P2.name 这个条件以后,就能排除掉由相同元素构成的对,结果行数为排列 P2 ^3 = 6。理解这个连接的关键在于想象 一下这里存在下面这样的两张表。


2021/11/2

唉,昨晚和男朋友视频就没时间学习了,今天多学一点吧。今天看了一些视频,有些收获,想要成为一名数据分析师,光学会敲代码做表格什么的远远不够,还得有思维,不然就是“取数机器”。要有自己的想法,对行业的了解,分析的框架,分析的思维,还得有比较强的逻辑能力和数学能力,哭聊,是不拿手的,但是可以好好学的。加油吧。

这次的处理结果依然是有序对。接下来我们进一步对(苹果 , 橘子) 和(橘子 , 苹果)这样只是调换了元素顺序的对进行去重。请看下面的 SQL 语句。

-- 用于获取组合的 SQL 语句
SELECT P1.name AS name_1, P2.name AS name_2
 FROM Products P1, Products P2
 WHERE P1.name > P2.name;

■执行结果
name_1 name_2
------ ------
苹果 橘子
香蕉 橘子
香蕉 苹果

同样地,请想象这里存在 P1 和 P2 两张表。在加上“不等于”这个条 件后,这条 SQL 语句所做的是,按字符顺序排列各商品,只与“字符顺 序比自己靠前”的商品进行配对,结果行数为组合 C2 3 = 3。到这里,我们 终于得到了无序对。恐怕平时我们说到组合的时候,首先想到的就是这种 类型的组合吧。

想要获取 3 个以上元素的组合时,像下面这样简单地扩展一下就可以
了。这次的样本数据只有 3 行,所以结果应该只有 1 行。

-- 用于获取组合的 SQL 语句 :扩展成 3 列
SELECT P1.name AS name_1, P2.name AS name_2, P3.name AS name_3
 FROM Products P1, Products P2, Products P3
 WHERE P1.name > P2.name
 AND P2.name > P3.name;

■执行结果
name_1 name_2 name_3
------- -------- --------
香蕉 苹果    橘子

如这道例题所示,使用等号“=”以外的比较运算符,如“、<>” 进行的连接称为“非等值连接”。这里将非等值连接与自连接结合使用了, 因此称为“非等值自连接”。在需要获取列的组合时,我们经常需要用到 这个技术,请牢记。

最后补充一点,“>”和“<”等比较运算符不仅可以用于比较数值大小, 也可以用于比较字符串(比如按字典序进行比较)或者日期等

删除重复行

重复行有多少行都没有关系。通常,如果重复的列里不包含主键,就可以用主键来处理

但像这道例题一样所有的列都重复的情况,则需要使用由数据库独自实现的行 ID。这里的行 ID 可以理解成拥有“任何表都可以使用的主键”这种特征的虚拟列。在下面的 SQL 语句里,我们使用的 是 Oracle 数据库里的 rowid。

注A 设计表时不宜允许存在重复行, 原因这里恕不赘述。

注A 像这样给用户提供了可用的行 ID 的 数 据 库 只 有 Oracle(rowid) 和 PostgreSQL(oid)。如果是在 PostgreSQL 数据库里,那么我们 必须在建表时指定 WITH OIDS 才 能使用它。其他数据库则必须由 用户自己指定主键来使用,或者用其他办法(例如将去重后的结 果存入其他表)。

查找局部不一致的列

-- 用于查找是同一家人但住址却不同的记录的 SQL 语句
SELECT DISTINCT A1.name, A1.address
 FROM Addresses A1, Addresses A2
 WHERE A1.family_id = A2.family_id
 AND A1.address <> A2.address ;

排序

使用窗口函数排序

-- 排序 :使用窗口函数
SELECT name, price,
 RANK() OVER (ORDER BY price DESC) AS rank_1,
 DENSE_RANK() OVER (ORDER BY price DESC) AS rank_2
 FROM Products;

■执行结果
name price rank_1 rank_2
------- ------ ------- -------
橘子 100 1 1
西瓜 80 2 2
苹果 50 3 3
香蕉 50 3 3
葡萄 50 3 3
柠檬 30 6 4

对于没有实现窗口函数的数据库,可以考虑用费等之连接实现排序 

相当于RANK()函数

所以我们还要考虑一下有没有不依赖于具体数据库来实现的方法。下
面是用非等值自连接(真的很常用)写的代码。
-- 排序从 1 开始。如果已出现相同位次,则跳过之后的位次
SELECT P1.name,
 P1.price,
 (SELECT COUNT(P2.price)
 FROM Products P2
 WHERE P2.price > P1.price) + 1 AS rank_1
 FROM Products P1
 ORDER BY rank_1;

这段代码的排序方法看起来很普通,但很容易扩展。例如去掉标量 子查询后边的 +1,就可以从 0 开始给商品排序,而且如果修改成 COUNT(DISTINCT P2.price),那么存在相同位次的记录时,就可以 不跳过之后的位次,而是连续输出(相当于 DENSE_RANK 函数)。由此 可知,这条 SQL 语句可以根据不同的需求灵活地进行扩展,实现不同的 排序方式。

相当于DENSE_RANK函数 

--不跳过之后的位次,而是连续输出(相当于 DENSE_RANK 函数)
select p1.name,p1.price,
       (select count(distinct p2.price)
           from products as p2
           where p2.price>p1.price)+1 as dense_rank
from products p1
order by dense_rank;

实现原理

 

这个子查询的代码还可以像下面这样按照自连接的写法 来改写。

-- 排序 :使用自连接
SELECT P1.name,
 MAX(P1.price) AS price, 
 COUNT(P2.name) +1 AS rank_1
 FROM Products P1 LEFT OUTER JOIN Products P2
 ON P1.price < P2.price
 GROUP BY P1.name
 ORDER BY rank_1;

 从执行结果可以看出,集合每增大 1 个,元素也增多 1 个,通过数集合里元素的个数就可以算出位次。

2021/11/3

我emo了,烦死,明明自己也在努力的学习SQL,怎么就体现不在成绩上呢。今天的数据库作业老师只给了6分,有一个是因为忘记粘贴了,剩下的我感觉是没有问题的,我都检查过的,也不知道错到哪里去了,唉。

l练习题

1.

SELECT P1.name AS name_1, P2.name AS name_2
 FROM Products P1, Products P2
 WHERE P1.name >= P2.name;

2.

 

3.

这个问题使用自连接就能轻松地解决。我们可以像下面这样,在
UPDATE 语句的 SET 子句中加入计算位次的逻辑

UPDATE DistrictProducts2 P1
 SET ranking = (SELECT COUNT(P2.price) + 1
 FROM DistrictProducts2 P2
 WHERE P1.district = P2.district
 AND P2.price > P1.price);

 

三值逻辑和NULL

普通语言里的布尔型只有 true 和 false 两个值,这种 逻辑体系被称为二值逻辑。而 SQL 语言里,除此之外还有第三个值 unknown,因此这种逻辑体系被称为三值逻辑(three-valued logic)。

理论篇

两 种 NULL 分 别 指 的 是“ 未 知 ”(unknown)“ 不 适 用 ”(not applicable, inapplicable)。以“不知道戴墨镜的人眼睛是什么颜色”这种情 况为例,这个人的眼睛肯定是有颜色的,但是如果他不摘掉眼镜,别人就 不知道他的眼睛是什么颜色。这就叫作未知。而“不知道冰箱的眼睛是什 么颜色”则属于“不适用”。因为冰箱根本就没有眼睛,所以“眼睛的颜色” 这一属性并不适用于冰箱。

对 NULL 使用比较谓词后得到的结果总 是 unknown。而查询结果只会包含 WHERE 子句里的判断结果为 true 的行, 不会包含判断结果为 false 和 unknown 的行。不只是等号,对 NULL 使用 其他比较谓词,结果也都是一样的。

那么,为什么对 NULL 使用比较谓词后得到的结果永远不可能为真呢? 这是因为,NULL 既不是值也不是变量。NULL 只是一个表示“没有值”的 标记,而比较谓词只适用于值。因此,对并非值的 NULL 使用比较谓词本来就是没有意义的。

真值unknown

真值 unknown作为 NULL 的一种的 UNKNOWN(未知)是不同的东西。前者是明确的布尔型的真值,后者既不是值也不 是变量。为了便于区分,前者采用粗体的小写字母 unknown,后者用普通 的大写字母 UNKNOWN 来表示。为了让大家理解两者的不同,我们来看一 个 x=x 这样的简单等式。x 是真值 unknown 时,x=x 被判断为 true,而 x 是 UNKNOWN 时被判断为 unknown。

 三值逻辑真值表

 三个真值之间有下面这样的优先级顺序。

● AND 的情况:false > unknown > true

● OR 的情况:true > unknown > false

实践篇

 1.比较谓词和NULL(1):排中律不成立

像这样,“把命题和它的否命题通 过‘或者’连接而成的命题全都是真命题”这个命题在二值逻辑中被称为 排中律(Law of Excluded Middle)。顾名思义,排中律就是指不认可中间 状态,对命题真伪的判定黑白分明,是古典逻辑学的重要原理。“是否承认这一原理”被认为是古典逻辑学和非古典逻辑学的分界线。

在 SQL 的世界里,排中律是不成立的。

例如

 

 2.比较谓词和NULL(2):CASE表达式和NULL

--在CASE表达式里将Null作为条件
select case col_1
when 1 then 'o'
when null then 'x'
end;
--该CASE表达式一定不会返回NUll
--因为第二个子句为:col_1 =NULL的缩写,该式子的返回值永远为unknown。
--而且CASE表达式的判断方法与where子句一样,只认可真值为TRUE的条件。
--正确的写法如下

select case 
when col_1 =1 then 'o'
when col_1 is null then 'x'
end;

3. NOT IN 和NOT EXIST 不是等价的

在对 SQL 语句进行性能优化时,经常用到的一个技巧是将 IN 改写成 EXISTSA。这是等价改写,并没有什么问题。问题在于,将 NOT IN 改写 成 NOT EXISTS 时,结果未必一样。

 

--查询与B班住在东京的学生年龄不同的A班的学生
select *
from Class_A
where age not in ( select age
                    from Class_B
                    where city='东京');
--该语句查不到任何数据
--如果山田的年龄不是NULL,就可以顺利查出正确结果

--执行过程
--1. 执行子查询,获取年龄列表
select *
    from Class_A
where age not in(22,23,NULL);

--2. 用 NOT 和 IN 等价改写 NOT IN
SELECT *
 FROM Class_A
 WHERE NOT age IN (22, 23, NULL);

--3. 用 OR 等价改写谓词 IN
SELECT *
 FROM Class_A
 WHERE NOT ( (age = 22) OR (age = 23) OR (age = NULL) );

--4. 使用德 · 摩根定律等价改写
SELECT *
 FROM Class_A
 WHERE NOT (age = 22) AND NOT(age = 23) AND NOT (age = NULL);

--5. 用 <> 等价改写 NOT 和 =
SELECT *
 FROM Class_A
 WHERE (age <> 22) AND (age <> 23) AND (age <> NULL);

--6. 对 NULL 使用 <> 后,结果为 unknown
SELECT *
 FROM Class_A
 WHERE (age <> 22) AND (age <> 23) AND unknown;

--7.如果 AND 运算里包含 unknown,则结果不为 true(参考“理论篇”中的矩阵)
SELECT *
 FROM Class_A
 WHERE false 或 unknown;

可以看出,这里对 A 班的所有行都进行了如此繁琐的判断,然而没 有一行在 WHERE 子句里被判断为 true。也就是说,如果 NOT IN 子查询 中用到的表里被选择的列中存在 NULL,则 SQL 语句整体的查询结果永远 是空。这是很可怕的现象。

为了得到正确的结果,我们需要使用 EXISTS 谓词。

-- 正确的 SQL 语句 :拉里和伯杰将被查询到
SELECT *
 FROM Class_A A
 WHERE NOT EXISTS ( SELECT *
 FROM Class_B B
 WHERE A.age = B.age
 AND B.city = '东京' );

■ 执行结果
name age city
----- ---- ----
拉里 19 埼玉
伯杰 21 千叶
--1. 在子查询里和 NULL 进行比较运算
SELECT *
 FROM Class_A A
 WHERE NOT EXISTS ( SELECT *
 FROM Class_B B
 WHERE A.age = NULL
 AND B.city = '东京' );

--2. 对 NULL 使用“=”后,结果为 unknown
SELECT *
 FROM Class_A A
 WHERE NOT EXISTS ( SELECT *
 FROM Class_B B
 WHERE unknown
 AND B.city = '东京' );

--3. 如果 AND 运算里包含 unknown,结果不会是 true
SELECT *
 FROM Class_A A
 WHERE NOT EXISTS ( SELECT *
 FROM Class_B B
 WHERE false 或 unknown);

--4. 子查询没有返回结果,因此相反地,NOT EXISTS 为 true
SELECT *
 FROM Class_A A
 WHERE true;


2021/11/4

希望我的生活少一点emo,多一点朝着目标前进的坚定、对未来的憧憬和对生活的热爱。

希望家人安康,朋友安好,和男朋友感情稳定,顺利毕业,元气满满地迎接新生活!

4.限定谓词和NULL

SQL 里有 ALL 和 ANY 两个限定谓词。因为 ANY 与 IN 是等价的,所以 我们不经常使用 ANY。

ALL 谓词其实是多个以 AND 连接的逻辑表达式的省略写法。

--查询比 B 班住在东京的所有学生年龄都小的 A 班学生
select *
from Class_A
where age<all(select age
    from Class_B
    where city='东京');

--若其中有人地年龄不详 NULL 查询结果为空
--执行过程
--1.执行子查询获取年龄列表‘
select *
from Class_A
where age < All(22,23,NULL);

--2.将ALL谓词等价改写为AND
select *
from Class_A
where (age<22) AND (age<23) AND (age,NULL);

--3.对NULL使用“<”后,结果变为UNKNOWN
select *
from Class_A
where (age<22) AND (age<23) AND unknown;

--4.若AND运算符里包含unknown,则结果不为true
select *
from Class_A
where false and unknown;
--但是where子句只会判断为true的记录。

5.限定谓词和极值函数不是等价的

        有时候可能会使用极值函数替代ALL谓词,例如

--查询比B 班住在东京的年龄最小的学生还要小的 A 班学生
select *
from Class_A
where age < (select min(age)
                from Class_B
             where city='东京');

执行结果
name age city
----- ---- ----
拉里 19 埼 玉
伯杰 21 千 叶

执行结果没有问题,即使山田的年龄无法确定,这段代码也可以执行成功。

这是因为,极值函数在统计时会把为NULL的数据排除掉。

然而在三值逻辑的世界里,事情没有这么简单。ALL 谓词和极值函数表达的命题含义分别如下所示。

● ALL 谓词:他的年龄比在东京住的所有学生都小 —— Q1

● 极值函数:他的年龄比在东京住的年龄最小的学生还要小 —— Q2

在现实世界中,这两个命题是一个意思。但是,正如我们通过前面的 例题看到的那样,表里存在 NULL 时它们是不等价的。其实还有一种情况 下它们也是不等价的,大家知道是什么吗?

答案是,谓词(或者函数)的输入为空集的情况。例如 Class_B 这张 表为如下所示的情况。

--1.极值函数返回NULL
select *
from Class_A
where age < NULL;

--2.对NULL使用 “<"后的结果为 unknown
select *
from Class_A
where unknown;

6.聚合函数和NULL

输入为空表时返回 NULL 的不只是极值函数,COUNT 以外 的聚合函数也是如此

-- 查询比住在东京的学生的平均年龄还要小的 A 班学生的 SQL 语句
select *
from Class_A
where age< (select avg(age)
                from Class_B
                where city='东京');

没有住在东京的学生时,AVG 函数返回 NULL。因此,外侧的 WHERE 子句永远是 unknown,也就查询不到行。使用 SUM 也是一样。

这种情况的 解决方法只有两种:要么把 NULL 改写成具体值,要么闭上眼睛接受 NULL。但是如果某列有 NOT NULL 约束,而我们需要往其中插入平均值或汇总值,那么就只能选择将 NULL 改写成具体值了。


2021/11/6

昨天没有学sql,感情还遇上了一些小问题,又开始迷茫了,什么时候我才能清楚的知道自己想要的是什么,该走什么路,我该怎么努力呢。

HAVING子句的力量

SQL是一种基于“面向集合”思想设计的语言。

寻找缺失的编号

SQL会将多条记录作为一个集合来处理,因此将表看作一个集合,就可以解决“找出是否存在缺失编号”的问题。

--如果查询有结果,说明存在缺失编号
select '存在缺失编号' as gap
    from SeqTbl
HAVING count(*) <> max(seq);

 集合运算的过程

 按照现在的 SQL 标准来说,HAVING 子句是可以单独使用的 。不过这种情况下,就不能在 SELECT 子句里引 用原来的表里的列了,要么就得像示例里一样使用常量,要么就得像 SELECT COUNT(*) 这样使用聚合函数。

也可以认为是对空字段进行了 GROUP BY 操作,只不过省略了 GROUP BY 子句。如果使用窗口 函数时不指定 PARTITION BY 子 句,就是把整个表当作一个分区 来处理的,思路与这里也是一样 的。详情请参考本书 2-5 节。

--查询缺失编号的最小值
select min(seq +1) as gap
from SeqTbl
where (seq +1 ) not in (select  seq from SeqTble);

执行结果
gap
---
4

像这条语句一样进行行与行之间的比 较时其实是不进行排序的。 顺便说一下,如果表 SeqTbl 里包含 NULL,那么这条 SQL 语句的查 询结果就不正确了。

上面展示了通过 SQL 语句查询缺失编号的最基本的思路,然而这个查询还不够周全,并不能涵盖所有情况。例如,如果表 SeqTbl 里没有编 号 1,那么缺失编号的最小值应该是 1,但是这两条 SQL 语句都不能得出正确的结果。

用HAVING子句进行子查询:求众数

 从这个例子可以看出,简单地求平均值有一个缺点,那就是很容易 受到离群值(outlier)的影响。这种时候就必须使用更能准确反映出群 体趋势的指标——众数(mode)就是其中之一。

--求众数的SQL语句(1):使用谓词
select income, count(*) as cnt
from Graduates
group by income
having count(*) >= all( select count(*)
                            from Graduates
                            group by income);

执行结果
income cnt
------ ---
10000 3
20000 3

 ALL 谓词用于 NULL 或空集时会出现问题, 可以用极值函数来代替。这里要求的是元素数最多的集合,因此可以用 MAX 函数。

--求众数的SQL语句(2):使用极值函数
select income, count(*) as cnt
from Graduates
group by income
having count(*) >= (select max(cnt)
                        from ( select count(*) as cnt
                                from Graduates
                                group by income) tmp);

用HAVING子句进行自连接:求中位数

--求中位数的SQL语句:在HAVING子句中使用非等值连接
SELECT AVG( DISTINCT income)
 from (select T1.income
        from Graduates T1,Graduates T2
        group by T1.income
        --S1的条件
        having sum( case when T2.income >=T1.income then 1 else 0 end)
                    >= count(*)/2
        --S2的条件
        and sum( case when T2.income <= T1.income then 1 else 0 end)
                >= count(*)/2) tmp ;

 这条 SQL 语句的要点在于比较条件“>= COUNT(*)/2”里的等号, 这个等号是有意地加上的。加上等号并不是为了清晰地分开子集 S1 和 S2,而是为了让这 2 个子集拥有共同部分。如果去掉等号,将条件改成“> COUNT(*)/2”,那么当元素个数为偶数时,S1 和 S2 就没有共同的元素了, 也就无法求出中位数了。


2021/11/7

救命,为什么时间过得这么快,感觉还没有干什么,就一天了,好像效率也没有很低啊,但是学了一天也感觉没学个啥,又把SQL拖到最后,又没多少时间看了。


2021/11/8

昨天并不是什么都没有学,我我我我解决了利用having子句求中位数的问题,大致懂了书上的代码是什么意思。嘤嘤嘤,现在继续学习,这比基础教程的进度慢多了,主要是因为难度变大了而且最近半期考试比较忙复习啊啊啊。

查询不包含NULL的子句

基础教程中就学过函数 COUNT(*) 和 COUNT(列名) 的区别,前者可以用于NULL,后者与其他聚合函数一样,首先排除NULL再进行统计。

create table students(
    student_id varchar(10),
    dpt varchar(100),
    sbmt_date date
);

insert into students values ('100','理学院','2005-10-10');
insert into students values ('101','理学院','2005-09-22');
insert into students (student_id, dpt) values ('102','文学院');
 insert into students values ('103','文学院','2005-09-10');
 insert into students values  ('200','文学院','2005-09-22');
 insert into students (student_id, dpt) values   ('201','工学院');
  insert into students values ('202','经济学院','2005-09-25');

--查找那些学院全部提交了报告
--按照学院进行分组
--再找出提交日期全不为NULL的集合
select dpt
from students
group by dpt
having count(*)=count(sbmt_date);

select dpt
from students
group by dpt
having count(*)=sum(case when sbmt_date is not null
                        then 1 else 0 end);

用关系出发运算进行购物篮分析

 购物篮分析是市场分析领域常用 的一种分析手段用来发现“经常被一起购买的商品”具有的规 律。有一个有名的例子:某家超 市发现,虽然不知为什么,但啤 酒和纸尿裤经常被一起购买(也 许是因为来买纸尿裤的爸爸都会 想顺便买些啤酒回去),于是便将啤酒和纸尿裤摆在相邻的货 架,从而提升了销售额。

可以把它应用到很多业务场景。例如在医疗领域 查询同时服用多种药物的患者,或者从员工技术资料库里查询 UNIX 和 Oracle 两者都精通的程序员,等等。

--查询啤酒、纸尿裤和自行车同时在库的店铺
select si.shop
from ShopItems si,items i
where si.item=i.item
group by si.shop
having count(si.item) =(select count(item) from items);
count(si.item) =(select count(item) from items) 不能写成 count(i.item)

因为经过连接之后此时的count(i.item) 已经不一定是原来的3了,因为

select si.shop,si.item,i.item
from ShopItems si,items i
where si.item=i.item
group by si.shop, si.item, i.item;


--count(i.item)的值已经不一定是3了
select si.shop,count(si.item),count(i.item)
from shopitems si,items i
where si.item=i.item
group by si.shop;

 

 与大版店的商品进行匹配时,发现大版店里满足条件 “si.item=i.item” 的只有两条记录

与仙台店进行匹配时,“窗帘” 已经就被排除了。

 

那么接下来我们把条件变一下,看看如何排除掉仙台店 (仙台店的仓库中存在“窗帘”,但商品表里没有“窗帘”),让结果里只出 现东京店。这类问题被称为“精确关系除法”(exact relational division), 即只选择没有剩余商品的店铺(与此相对,前一个问题被称为“带余除法” (division with a remainder))。解决这个问题我们需要使用外连接

--精确查询出完全与items相符的店
--精确关系除法:使用外连接和COUNT函数

select si.shop,si.item,i.item
from shopitems si left outer join items i
on si.item=i.item;

 首先使用使用外连接观察结果,仙台的窗帘和大版的电视在表ITEMS中没有对应的数据。

--按店进行分组并对两张表分别查询出满足条件的商品数
select si.shop,count(si.item),count(i.item)
from shopitems si left outer join items i
on si.item=i.item
group by si.shop;

--最终的全部代码
select si.shop,count(si.item),count(i.item)
from shopitems si left outer join items i
on si.item=i.item
group by si.shop
having 
        --满足“带余除法”的条件
    count(si.item) =(select count(item) from items)
        --满足“精确关系除法”的条件
    and count(i.item)=(select count(item) from items);

 关系除法的运算


2021/11/10

昨天没有学习,今天不知道怎么了好累,希望学习SQL能让我找回精力。

 练习题

1.

create table SeqTbl(
    seq integer,
    name varchar(10)
);

insert into SeqTbl values (1,'andy'),(2,'jimmy'),(3,'lucy'),(5,'ka'),(6,'marry'),(8,'ben');

select '存在缺失编号' as gap
from SeqTbl
having count(*)<>max(seq);

--查找最小缺失编号
select min(seq+1) as gap
from SeqTbl
where (seq+1) not in(select seq
    from SeqTbl);
--有BUG

--第一题
select case when count(*)<>max(seq)
        then '存在缺失编号'
else '不存在缺失编号'
end as gap
from SeqTbl;

 2.

--第二题
--自己做的
select dpt
from students
where sbmt_date>'2005-10-1'
group by dpt
having count(*)=count(sbmt_date);

--答案写的:使用 BETWEEN 谓词。
select dpt
from students
group by dpt
having count(*) = sum(case when sbmt_date between '2005-9-01' and '2005-9-30'
                            then 1 else 0 end )  ;

--使用 EXTRACT 函数(SQL 标准函数,返回数值)将日期中的年、月、日等要素解析出来分别用于匹配
select dpt
from students
group by dpt
having count(*) =sum( case when extract (year from sbmt_date) =2005
                            and extract (month from sbmt_date)=09
                        then 1 else 0 end);

 使用EXTRACT这种写法的好处是,即使查询条件的月份变化了,也不用关心月末日 期是 30 日还是 31 日(或者是其他),相比前一个写法,这种写法容易应 对查询条件的变化。如果大家在工作中经常需要对日期进行操作,不妨记 住这个函数的用法。

自己写的答案局限性很大,考虑的不够充分。

3.

--第三题
select si.shop,
       count(si.item ) as my_item_cnt,
    ((select count(item)
        from items)-count(*)) as diff_cnt
from ShopItems si, items i
where i.item=si.item
group by si.shop;

2021/11/11

感觉最近都没怎么学习SQL,学的太慢了,至少得在这个月把它学完啊。

1-5外连接的用法

用外连接进行行列转换(1)(行→列):制作交叉表

方法一: 要使用外连接

create table Courses(
    name varchar(10),
    course varchar(225)
);

--自己写的,错的
select distinct name,
       (case when course='SQL入门'
           then '⚪' else '' end) as SQL入门,
       (case when course='UNIX基础'
           then '⚪' else '' end) as UNIX基础,
       (case when course ='Java中级'
           then '⚪' else '' end) as Java中级
from courses
group by name,course;

--水平展开求交叉表(1):使用外连接
select CO.name,
  CASE WHEN C1.name IS NOT NULL THEN '○' ELSE NULL END AS "SQL 入门",
  CASE WHEN C2.name IS NOT NULL THEN '○' ELSE NULL END AS "UNIX 基础",
  CASE WHEN C3.name IS NOT NULL THEN '○' ELSE NULL END AS "Java 中级"
from (select distinct name from courses ) CO
left outer join
    (select name from courses where course='SQL入门') C1
    on CO.name=C1.name
        left outer join
            (select name from courses where course='UNIX基础') C2
            on CO.name=C2.name
                left outer join (select name from courses where course='Java中级') C3
                on CO.name=C3.name;

使用子查询,生成了C0-C3这四个子集,将生成以下四个集合。

 这个代码大量用了内嵌式图和连接操作,代码会显得很臃肿。

方法二:使用标量子查询

一般情况下,外连接都可以用标量子查询替代。

-- 水平展开 (2):使用标量子查询
SELECT C0.name,
 (SELECT '○'
 FROM Courses C1
 WHERE course = 'SQL 入门'
 AND C1.name = C0.name) AS "SQL 入门",
 (SELECT '○'
 FROM Courses C2
 WHERE course = 'UNIX 基础'
 AND C2.name = C0.name) AS "UNIX 基础",
 (SELECT '○'
 FROM Courses C3
 WHERE course = 'Java 中级'
 AND C3.name = C0.name) AS "Java 中级"
 FROM (SELECT DISTINCT name FROM Courses) C0; -- 这里的 C0 是表侧栏

这种做法的优点在于,需要增加或者减少课程时,只需要在SELECT子句最后加上一句即可。

(SELECT '○'
 FROM Courses C4
 WHERE course = 'PHP 入门'
 AND C4.name = C0.name ) AS "PHP 入门"

这种做法不仅利于应对需求变更,对于需要动态生成 SQL 的系统也 是很有好处的。缺点是性能不太好,目前在 SELECT 子句中使用标量子查 询(或者关联子查询)的话,性能开销还是相当大的。

第三种方法:嵌套使用CASE表达式

CASE表达式可以写在聚合函数内部,也可以写在聚合函数外部。

-- 水平展开 (3):嵌套使用 CASE 表达式
SELECT name,
 CASE WHEN SUM(CASE WHEN course = 'SQL 入门' THEN 1 ELSE NULL END) = 1
 THEN '○' ELSE NULL END AS "SQL 入门",
 CASE WHEN SUM(CASE WHEN course = 'UNIX 基础' THEN 1 ELSE NULL END) = 1
 THEN '○' ELSE NULL END AS "UNIX 基础",
 CASE WHEN SUM(CASE WHEN course = 'Java 中级' THEN 1 ELSE NULL END) = 1
 THEN '○' ELSE NULL END AS "Java 中级 "
 FROM Courses
 GROUP BY name;

今天就先到这里吧,休息了。


2021/11/15

好几天没学了,都有点生疏了,要加油。

用外连接进行行列转换(2)(列→行):汇总重复项于一列

create table Personnel(
    employee varchar(10),
    child_1 varchar(10),
    child_2 varchar(10),
    child_3 varchar(10)
);

--将列数据转换成行数据:使用UNION ALL
select employee,child_1 as child from Personnel
union all
SELECT employee, child_2 AS child FROM Personnel
UNION ALL
SELECT employee, child_3 AS child FROM Personnel;
--UNION ALL不会排除重复的行

--生成储存子女列表的视图
CREATE VIEW Children(child)
AS SELECT child_1 FROM Personnel
 UNION
 SELECT child_2 FROM Personnel
 UNION
 SELECT child_3 FROM Personnel;

select * from Children;

--获取员工子女列表的SQL语句(没有孩子的员工也要输出)
select emp.employee,children.child
from Personnel emp
left outer join Children
on children.child in(emp.child_1,emp.child_2,emp.child_3);

 

 


2021/11/22

妈耶,一晃就这么多天了,一点没学,而且最近上课在学数据库的设计,听得云里雾里的,好抽象,感觉自己还得加把劲才能理解。

在交叉表里制作嵌套式表侧栏

已知表

想要生成如下表: 

 

 想要生成固定的6行的表的侧栏,需要用到外连接,想要将表做成嵌套式,得在花些功夫。

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页

打赏作者

weixin_53794695

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值