SQL开发指南二续

自连接的用法 面向集合思维

增强” 面向集合 " SQL这个特性, 自连接用两张表用运算符连接起来，将每张表视为集合

等值自连接 =

非等值自连接 > , < , <>

例子：在一张表中查询价格相等的不同商品

select distinct p1.name, p1.price from product p1,product p2 where p1.price=p2.price and p1.name<>p2.name

排序需求：商品按照价格高低显示排名，有两种情况，价格相同的商品之后的排名是否跳过。

例：

A 1：100，2：90，2：90，3：50

B 1：100，2：90，2：90，4：50

可以用窗口函数实现这种排序

select name,price, rank() over (order by price desc) as rank_A, dense_rank() over (order by price desc) as rank_B from product;

用非等值自连接 实现 排序B

select p1.name, p1.price, (select count(p2.price) from product p2 where p2.price > p1.price) +1 as rank_B from product p1 order by rank_B;

利用distinct关键词，就可以扩展实现排序A

select p1.name, p1.price, (select count(distinct p2.price) from product p2 where p2.price > p1.price) +1 as rank_A from product p1 order by rank_A;

最后说一个需要注意的地方，与多表之间进行的普通连接相比，自连接的性能开销更大（特别是与非等值连接结合使用的时候），因此用于自连接的列推荐使用主键或者在相关列上建立索引。本节例题里出现的连接大多使用的是主键

三值逻辑 和 NULL

数据库中的NULL，可以有两种意思：一种是“未知 unknown”；另一种是“不适用 not applicable”。

用数值和null进行比较，都会判定 为 unknown

1= null ; 1>null ; 4 <>null, null=null 等

有null参与的运算，有可能会导致产生我们意思不到的结果，那就是 unknown

unknown是 布尔类型的第三个真值

而unknown=unknown --- >true

case 的列值=null情况

case col when 1 then 'O' when null then 'X' end;

上述是错误的写法，因为 when null 相当于 col=null,但是在SQL中不是这样判断的，二是 is null

case when col=1 then 'O' when col is null then 'X' end;

在SQL性能优化中，常用到的一个技巧，是 IN 改写 EXISTS，这个是等价的改写；但是not in 和 not exists 不是等价的，结果未必一样。

select * from product where sale_price not in (100); 是有结果的

product_id | product_name | product_type | purchase_price | regist_data | sale_price
------------+--------------+--------------+----------------+-------------+------------
0001 | T衬衫 | 衣服 | 500 | 2009-09-20 | 250
0002 | 打孔机 | 办公用品 | 320 | 2009-09-11 | 160
0004 | 菜刀 | 厨房用具 | 5000 | 2009-09-20 | 2500
0005 | 高压锅 | 厨房用具 | 2800 | 2009-01-15 | 1400
0007 | 擦菜板 | 厨房用具 | 790 | 2008-04-28 | 395
0003 | 运动T血 | 衣服 | 0 | 2020-01-19 | 1000

1. select * from product where sale_price not in (100,null); 因为null存在，结果为空集

2. select * from product where not sale_price in (100,null);

3. select * from product where not ( (sale_price=100) or (sale_price=null) );

4. select * from product where not (sale_price=100) and not(sale_price=null);

5. select * from product where sale_price<>100 and sale_price<>null;
   
   
   
   

一步步转换后，因为任何值与null比较，其结果是unknown，而and运算如果有unknown存在，其结果都不会是true,所以条件永远都不为真，得到的结果是空集。

SELECT * FROM Class_A A WHERE NOT EXISTS ( SELECT * FROM Class_B B WHERE A.age = B.age AND B.city = '东京' );

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;

产生这样的结果，是因为 EXISTS 谓词永远不会返回 unknown。EXISTS 只会返回 true 或者 false。因此就有了
IN 和 EXISTS 可以互相替换使用，而 NOT IN 和 NOT EXISTS 却不可以互相替换的混乱现象

限定词和NULL

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

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

条件判断为false或 unknown ，不为真，空集。

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

极值函数在统计时会把为 NULL 的数据排除掉

● ALL 谓词：他的年龄比在东京住的所有学生都小
● 极值函数：他的年龄比在东京住的年龄最小的学生还要小

表里存在 NULL 时它们是不等价的

聚合函数和NULL

比如

where age <( select avg(age) .....) ,age存在null情况，也永远查不到结果。

1. NULL 不是值。

2. 因为 NULL 不是值，所以不能对其使用谓词。

3. 对 NULL 使用谓词后的结果是 unknown。

4. unknown 参与到逻辑运算时，SQL 的运行会和预想的不一样。

5. 按步骤追踪 SQL 的执行过程能有效应对 4 中的情况。

having子句经典用法

SQL语句是面向集合的语句，from t1,t2 表是让两张表进行笛卡尔积，代表了集合之间的乘法。

而having子句将认识到SQL的另一个特性，以集合为单位进行操作。

众数

有缺陷的方式1：

select income,count(*) as cnt from graduates group by income having count( *) >=ALL(select count( *) from graduates group by income) 但是子查询返回的集合如果有null项，那么就可能返回空集，这是上节提到的。

用max代替count

​ 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))

中位数

用having子句进行自连接

求出平均值，用平均值将集合一分为二，中间位置的元素既是中位数。

select AVG(distinct income) from (select T1.income from graduates T1,graduates T2 group by T1.income having sum(case when T2.income>=T1.income then 1 else 0 end)>=count(*)/2 AND sum(case when T2.income <=T1.income then 1 else 0 end)>=count( *)/2 ) temp;

待余数除法

比如商品表Item一共有3个商品，商店正在出售的商品展示表ShopItem，计算那个商店正在出售商品表里的全部商品。

select S.shop from shopitem s, item i where s.item=i.item group by s.shop having count(s.item)=(select count(item) from item)

这条SQL语句，按照item将两张表连接，然后按照商店分组，再用having计算分组后的商店展示商品的个数是否和商品表的总数相等。

外连接

使用外连接进行 行列转换， 行-->列

即行信息 通过转换 展示为 列信息

技巧：通常外连接可以用 标量子查询代替。

标量子查询在select 语句中。

用外连接进行 列-->行转换，汇总重复项于一列。

用额外的视图表存储相关信息，然后再用外连接。

作为乘法的连接：

连接之后在聚合

聚合group,聚合函数

连接，列项一对多的连接，增加行数。

外连接可以进行集合运算，

A-B,比如left outter join

^(A-B) 异或运算，full outer join

用关联子查询进行 比较 行与行

使用 SQL 对同一行数据进行列间的比较很简单，只需要在 WHERE 子句里写上比较条件就可以了，例如 col_1 = col_2。但是，对不同行数据进行列间的比较却没那么简单。

使用 SQL 进行行间比较时，发挥主要作用的技术是关联子查询，特别是与自连接相结合的“自关联子查询”

增长，减少，维持现状

需要用到行间数据比较的具有代表性的业务场景是，使用基于时间序列的表进行时间序列分析。

比如求今年和去年一样的销售额。

select year , sale from Sales s1 where sale=(select Sales s2 where s2.year=s1.year-1) order by year;

如果不是去年，而是最近过去的时间，现实可能缺少一些中间时间。

累计求值

SQL进行集合运算

SQL能操作具有重复行的集合，可以通过可选项ALL来支持。

集合运算符有优先级

SQL里面没有实现除法的手段，具有代表的实现方法：

1. 嵌套使用NOT EXISTS
2. 使用HAVING 子句转换一对一的关系
3. 把除法变成减法，减法指的是 差集运算

比较两张表是否相等，可以使用union运算，两张表的结果合在一起，如果最后临时表的行数与两张表相等，则两张表相等。

select count(*) as cnt from (select * from tabA UNION select * from tabB ) temp ;

在集合论中判断集合相等，有以下的方法：

1. (A∩B ) 且 (A∩B) ⇔ (A = B)
2. (A ∪ B ) = (A ∩ B) ⇔ (A = B) 推荐，更容易实现

如果 A UNION B = A INTERSECT B，则集合 A 和集合 B 相等。

只需要判定 (A UNION B) EXCEPT (A INTERSECT B) 的结果集是不是空集就可以了。如果 A = B，则这个结果集是空集，否则，这个结果集里肯定有数据。

EXCEPT 返回两个结果集的差（即从左查询中返回右查询没有找到的所有非重复值）。A-B

-- 用求差集的方法进行关系除法运算（有余数）
SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS
(SELECT skill
FROM Skills
EXCEPT
SELECT skill
FROM EmpSkills ES2
WHERE ES1.emp = ES2.emp);

关联子查询是为了使 SQL 能够实现类似面向过程语言中循环的功能而引入的

补集，高效删除重复行

delete from where(全部的数据 EXCEPT 留下的数据)

UNION 和 INTERSECT 都具有幂等性这一重要性质，而 EXCEPT 不具有幂等性。

EXISTS谓词的用法

EXISTS 不仅可以将多行数据作为整体来表达高级的条件，而且使用关联子查询时性能仍然非常好

实际上，谓词是一种特殊的函数，返回值是真值。前面提到的每个谓词，返回值都是 true、false 或者 unknown（一般的谓词逻辑里没有unknown，但是 SQL 采用的是三值逻辑，因此具有三种真值）。

exists谓词不像其他的谓词，比如=号谓词，可以取标量值，name=‘林’，

谓词逻辑中，根据输入值的阶数对谓词进行分类。= 或者 BETWEEEN 等输入值为一行的谓词叫作“一阶谓词”，
而像 EXISTS 这样输入值为行的集合的谓词叫作“二阶谓词”

阶（order）是用来区分集合或谓词的阶数的概念。

三阶谓词＝输入值为“集合的集合”的谓词
四阶谓词＝输入值为“集合的集合的集合”的谓词

Java 的读者可能知道“高阶函数”这一概念。它指的是不以一般的原子性的值为参数，而以函数为参数的函数。这里说的“阶”和谓词逻辑里的“阶”是一个意思（“阶”的概念原本就源于集合论和谓词逻辑）

全称量词和存在量词

EXISTS实现了存在量词逻辑

实战：

查询表中 不存在的数据，比如寻找表中没有参加某次会议的人？

这种时候正是 EXISTS 谓词大显身手的好时机。思路是先假设所有人都参加了全部会议，并以此生成一个集合，然后从中减去实际参加会议的人。这样就能得到缺席会议的人。

全称量词 <-- > 双重否定 之间的转换

比如“查询所以科目都在50以上的同学”

没有一个科目分数不满50的同学， NOT EXISTS

SELECT DISTINCT student_id
FROM TestScores TS1
WHERE NOT EXISTS -- 不存在满足以下条件的行
(SELECT *
FROM TestScores TS2
WHERE TS2.student_id = TS1.student_id
AND TS2.score < 50); -- 分数不满 50 分的科目

当然也能用差集实现

全部的学生- 不满50分的学生=满足的学生

NOT EXISTS 在关联子查询中使用CASE进行更复杂的条件判定

对列进行量化，查询全是1的行，或者至少有一个9的行？

//不优雅
SELECT *
FROM ArrayTbl
WHERE col1 = 1
AND col2 = 1
·
·
·
AND col10 = 1;

--“列方向”的全称量化 ：优雅的解答

SELECT *
FROM ArrayTbl
WHERE 1 = ALL (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);

SQL 中没有与全称量词相当的谓词，可以使用 NOT EXISTS 代替

SQL处理数列

基于它实现的关系数据库中的表和视图的行和列也必然没有顺序。同样地，处理有序集合也并非 SQL 的直接用途

SQL 处理数列或日期等有序数据的方法

实战：

寻找三个连续的空座位？

聚合函数技巧

count(*) = sum(case end) 可以用来求和