SQL进阶总结（一）

学而不思则罔，趁着假期好好总结下SQL的编程知识。

掌握SQL，首先有两个知识点要明确，要贯穿在我们整个学习SQL的过程中。

SQL 不同于之前学习的面向过程、面向编程语言，SQL是一门面向集合的编程语言。

面向集合编程语言的特性：

1、三值逻辑判断（TRUE&FALSE&UNKNOWN）

在我们以往我们所接触的编程语言都包含布尔类型，其中只要有TRUE和FALSE两个值，这种逻辑体系被称为二值逻辑。

但是在SQL语言中，除此之外还有第三个值UNKNOWN，这种逻辑体系被称为三值逻辑，三值逻辑的出现是因为在SQL中存在NULL值。

使用要点：

1）NULL值和任何值作比较都是UNKNOWN

      1=NULL 、 2>NULL、3<NULL 、4<>NULL、NULL=NULL 结果都为UNKNOWN

2）三值逻辑真值表

                 

 

 其中浅蓝色部分是三值逻辑特有的判断

总结一下就是：在and运算中 FALSE>UNKNOWN>TRUE

                         在or运算中    TRUE>UNKNOWN>FALSE

3）另外三值逻辑造成的问题就是排中律不存在

例如：在我们正常认知中假设有一群学生，他们年龄肯定是20岁，或者不是20岁，二者必居其一。

那么在我们SQL查询逻辑就应为：

--查询年龄是20岁或者不是20岁的学生
select * from students where age = 20 or age <> 20;

那么显然在我们SQL查询中 如果学生年龄中存在NULL值，上述查询是无法通过将全集查出的。

那么要完全查询出全集则应该为

/*查询全集*/
select  * from students where age = 20 or age <> 20 or age is NULL;

4）NOT IN 和 NOT EXISTS是不等价的

首先说明 在SQL查询中 EXISTS总是返回TRUE或FALSE，而 IN 则会返回 TRUE,FALSE还有对于 NULL 值会返回 UNKNOWN。但在过滤器中对 UNKNOWN和FALSE处理方式是相同的，因此在使用EXISTS和IN二者是可以相互替换的。

下面我们将对下面案例进行分析，来探究NOT IN 和 NOT EXISTS的区别

                                     

                            ClassA                                                                                                           ClassB

现在我们要查询 “与B班住在东京且年龄不同的A班同学” 

那么在 NOT IN 的SQL查询应为

--与B班住在东京的学生年龄不同的A班学生
SELECT * FROM ClassA WHERE age NOT IN (SELECT age FROM ClassB WHERE city = '东京')；

通过改写得

--与B班住在东京的学生年龄不同的A班学生
SELECT * FROM ClassA WHERE age NOT IN (22,23,NULL)

--SQL处理1:
SELECT * FROM ClassA WHERE NOT (age = 22) or (age = 23) or (age = NULL)
--SQL处理2：
SELECT * FROM ClassA WHERE NOT (TRUE or UNKNOWN)
--通过三值逻辑得
=>SELECT * FROM ClassB WHERE UNKONOWN
则结果集为空

我们再看看NOT EXISTS 如何处理

--与B班住在东京的学生年龄不同的A班学生
SELECT * FROM ClassA a WHERE NOT EXISTS (SELECT age FROM ClassB b WHERE a.age = b.age AND b.city = '东京')

通过改写得

--与B班住在东京的学生年龄不同的A班学生
SELECT * FROM ClassA a WHERE NOT EXISTS (SELECT age FROM ClassB b WHERE a.age = NULL AND b.city = '东京')；

--SQL处理1：
SELECT * FROM ClassA a WHERE NOT EXISTS (SELECT age FROM ClassB b WHERE UNKNOWN AND TRUE或者FALSE)

--SQL处理2:
--子查询不会返回结果  则父查询返回所有结果集
=> SELECT * FROM ClassA WHERE TRUE
--最后结果集会出现 '拉里' 和 '伯杰' 两条记录 

 所以我们可以得出结论 当我们筛选的集合中存在 NULL 值时，在使用 NOT IN 查询时总是得到空集，而在使用 NOT EXISTS 总是返回条件匹配的所有结果集。 

5）限定谓词和极值函数

SQL当中存在两个限定谓词ALL、ANY，由于ANY与IN是等价的，我们这里就说明一下ALL的用法。

当使用ALL时等价 让每个条件进行AND连接

例如 我们需要计算年龄小于所有输入列表时：

ALL的使用：

age < ALL (11,22,NULL) => (age < 11) and (age < 22) and (age < NULL) => 结果中存在UNKNOWN 那么结果集为空

极值函数的使用：

age < MIN(11,22,NULL) => age < 11 因为极值函数会自动忽略掉 NULL 值

而如果输入列表为一个空集时：

ALL 会返回查询数据表的所有记录，而极值函数则会返回NULL。

 

整个三值逻辑是基于谓词而来的。

那么谓词是什么？答：谓词是一种特殊的函数，返回值都是真值（true、false或者unknown）

谓词逻辑的出现提供了谓词来判断命题的真假。在关系数据库里，表里每一行数据可以看作是一个命题。

                             Tbl_A

例如：按照我们之前的理解表里第一行数据是一条记录他有对应的是三个属性值，而按照逻辑谓词的观点为 田中的性别是男，而且年龄是28岁。

谓词的出现是具有划时代意义的，原因就在于为命题分析提供了函数式的方法。

 5）实体的阶层

同样是谓词，但是与=、BETWEEN等相比，EXISTS的用法还是大不相同的。概括来说，区别在于“谓词的参数可以取什么值”。=、Between 我们只能使用单一值而 Exists 的参数我们取的是行数据的集合。

 从上面的图表我们可以知道，EXISTS的特殊性在于输入值的阶数（输出值和其他谓词一样，都是真值）。谓词逻辑中，根据输入值的阶数对谓词进行分类。=或者BETWEEEN等输入值为一行的谓词叫作“一阶谓词”，

而像EXISTS这样输入值为行的集合的谓词叫作“二阶谓词”。阶（order）是用来区分集合或谓词的阶数的概念。

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

SQL中采用的是狭义的“一阶谓词逻辑”，这是因为SQL里的EXISTS谓词最高只能接受一阶的实体作为参数。如果想要支持二阶、三阶等更高阶的实体，SQL必须提供相应的支持。理论上这也是可以做到的，只是目前还没有实现。

6)全称量化和存在量化

谓词逻辑中有量词（限量词、数量词）这类特殊的谓词。我们可以用它们来表达一些这样的命题：“所有的x都满足条件P”或者“存在（至少一个）满足条件P的x”。前者称为“全称量词”，后者称为“存在量词”，

分别记作∀、∃。这两个符号看起来很奇怪。其实，全称量词的符号其实是将字母A上下颠倒而形成的，存在量词则是将字母E左右颠倒而形成的。“对于所有的x, ……”的英语是“for All x,…”，而“存在满足……的x”的英语是“there Exists x that…”，

这就是这两个符号的由来。

SQL中的EXISTS谓词实现了谓词逻辑中的存在量词却不存在全称量词。不过二者是可以相互转换的，有如下定义：

∀ xPx = ¬ ∃ x¬P（所有的x都满足条件P＝不存在不满足条件P的x）

∃ xPx = ¬ ∀ x¬Px（存在x满足条件P＝并非所有的x都不满足条件P）

例1：查询“没有参加某次会议的人”

/*法一：查询没有参加某次会议的人*/
SELECT 
temp.`meeting`,
temp.`person`
FROM
(SELECT 
* FROM
(SELECT DISTINCT person FROM Meetings)a,(SELECT DISTINCT meeting FROM Meetings)b)temp
LEFT JOIN Meetings c ON temp.meeting = c.meeting AND temp.person = c.person
WHERE c.person IS NULL

/*法二：查询没有参加某次会议的人*/
SELECT DISTINCT
a.`meeting`,b.person
FROM Meetings a,Meetings b
WHERE NOT EXISTS
(
SELECT * FROM Meetings c WHERE a.`meeting` = c.meeting AND c.`person` = b.person
)

例2：全称量化

1.“肯定⇔双重否定”之间的转换 请查询出“所有科目分数都在50分以上的学生”

 查询“所有科目分数都在50分以上的学生” 转换 查询 “没有一个科目分数不满50分”

/*查询所有科目分数50分以上*/
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)

接下来我们把条件改得复杂一些再试试。

请查询“某个学生的所有行数据中，如果科目是数学，则分数在80分以上；如果科目是语文，则分数在50分以上。”

/* 全称量化（1）：习惯“肯定<＝>双重否定”之间的转换 */
SELECT student_id
  FROM TestScores TS1
 WHERE subject IN ('数学', '语文')
   AND NOT EXISTS
        (SELECT *
           FROM TestScores TS2
          WHERE TS2.student_id = TS1.student_id
            AND 1 = CASE WHEN subject = '数学' AND score < 80 THEN 1
                         WHEN subject = '语文' AND score < 50 THEN 1
                         ELSE 0 END)
 GROUP BY student_id
HAVING COUNT(*) = 2; /* 必须两门科目都有分数 */

例3：查询出哪些项目已经完成到了工程1

having子句解题：

--having 子句
/*查询哪些项目已经完成到了工程1*/
SELECT
a.project_id
FROM Projects a
GROUP BY a.project_id
HAVING COUNT(*) = SUM(CASE WHEN step_nbr <= 1 AND STATUS = '完成' THEN 1
WHEN step_nbr >1 AND STATUS = '等待' THEN 1
ELSE 0 END)

exists子句解题：

/*查询哪些项目已经完成到了工程1*/
/*--->查询不存在哪些项目没有完成工程1以及完成过了工程1*/
法一：
SELECT 
*
FROM Projects a
WHERE NOT EXISTS (
SELECT STATUS 
FROM Projects b 
WHERE a.project_id = b.project_id
AND STATUS <> CASE WHEN step_nbr <= 1 THEN '完成' ELSE '等待' END
)
法二：
SELECT
*
FROM Projects b
WHERE NOT EXISTS(
SELECT
*
FROM 
Projects a WHERE a.project_id = b.project_id AND(
(step_nbr > 1 AND STATUS = '完成') OR (step_nbr = 1 AND STATUS = '等待'))
)

例四：查询现在能出勤的队伍

 

 having子句：

/*查询可以出勤的队伍 having*/
SELECT
a.`team_id`
FROM
Teams a
GROUP BY a.`team_id`
HAVING COUNT(*) = (SELECT COUNT(*) FROM Teams b WHERE a.`team_id` = b.`team_id` AND b.`status` = '待命')

exists 子句：

/*查询可以出勤的队伍 全称量化*/
/*队伍中不存在不是待命的队员*/
SELECT 
*
FROM
Teams a WHERE NOT EXISTS (
SELECT * FROM Teams b WHERE a.`team_id` = b.`team_id` AND b.`status` <> '待命'
)

例五：查询存在重复材料的生产地

 

 having子句：

/*查询存在重复材料的生产地 having*/
SELECT 
a.center
FROM 
Materials a 
GROUP BY a.center 
HAVING COUNT(*) <> COUNT(DISTINCT a.material)

exists子句：

/*存在重复的生产地*/
SELECT
*
FROM
Materials a
WHERE EXISTS (
SELECT * FROM Materials b WHERE a.center = b.`center` AND a.receive_date <> b.`receive_date` AND a.material = b.`material`
)

例六：查询出75%以上的学生分数都在80以上的班级。

 

/*请查询出75%以上的学生分数都在80分以上的班级*/
SELECT b.class FROM TestResults b 
GROUP BY b.class 
HAVING SUM(CASE WHEN score>=80 THEN 1 ELSE 0 END)/COUNT(*) > 0.75

例7：找出连续为3个空位的组合

/*全是被未预订的座位*/
/*--->不存在不是被未预订的座位*/
SELECT
*
FROM Seats a,Seats b 
WHERE b.seat = a.seat + 2 
AND NOT EXISTS (
SELECT * 
FROM Seats c
WHERE c.`seat` BETWEEN a.seat AND b.seat AND c.`status` <> '未预订'
)