读SQL进阶教程笔记07_EXISTS谓词

1. 特点

1.1. 将多行数据作为整体来表达高级的条件

1.2. 使用关联子查询时性能仍然非常好

1.3. EXISTS的参数不像是单一值

1.3.1. 参数是行数据的集合

2. 什么是谓词

2.1. 一种特殊的函数，返回值是真值

2.2. 返回值都是true、false或者unknown

2.2.1. 一般的谓词逻辑里没有unknown

2.2.2. SQL采用的是三值逻辑，因此具有三种真值

2.3. 谓词逻辑提供谓词是为了判断命题（可以理解成陈述句）的真假

2.3.1. 为命题分析提供了函数式的方法

2.4. 只有能让WHERE子句的返回值为真的命题，才能从表（命题的集合）中查询到

3. 谓词的阶

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

3.2. 一阶谓词

3.2.1. =或者BETWEEEN等输入值为一行的谓词

3.3. 二阶谓词

3.3.1. EXISTS这样输入值为行的集合的谓词

3.3.2. 谓词也是函数的一种，因此我们也可以说EXISTS是高阶函数

3.4. 三阶谓词

3.4.1. 输入值为“集合的集合”的谓词

3.5. 四阶谓词

3.5.1. 输入值为“集合的集合的集合”的谓词

3.6. SQL里并不会出现三阶以上的情况

4. SELECT子句的列表

4.1. 通配符：SELECT ＊

4.2. 常量：SELECT ‘这里的内容任意’

4.3. 列名：SELECT col

5. 全称量化和存在量化

5.1. 形式语言没必要同时显式地支持EXISTS和FORALL两者

5.1.1. 因为全称量词和存在量词只要定义了一个，另一个就可以被推导出来

5.1.2. ∀ xPx = ¬ ∃ x¬P

5.1.3. 所有的x都满足条件P＝不存在不满足条件P的x

5.1.4. ∃ xPx = ¬ ∀ x¬Px

5.1.5. 存在x满足条件P＝并非所有的x都不满足条件P

5.2. SQL支持EXISTS，不支持FORALL

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

5.3. 全称量词

5.3.1. 所有的x都满足条件P

5.4. 存在量词

5.4.1. 存在（至少一个）满足条件P的x

6. 查询表中“不”存在的数据

6.1. 示例

6.1.1.

6.1.1.1.

 SELECT DISTINCT M1.meeting, M2.person
      FROM Meetings M1 CROSS JOIN Meetings M2;

6.1.1.2. 所有人都参加了全部会议时

6.1.1.3. --求出缺席者的SQL语句(1)：存在量化的应用

    SELECT DISTINCT M1.meeting, M2.person
      FROM Meetings M1 CROSS JOIN Meetings M2
     WHERE NOT EXISTS
          (SELECT ＊
              FROM Meetings M3
            WHERE M1.meeting = M3.meeting
              AND M2.person = M3.person);

6.1.1.3.1. ----求出缺席者的SQL语句(2)：使用差集运算

    SELECT M1.meeting, M2.person
      FROM Meetings M1, Meetings M2
    EXCEPT
    SELECT meeting, person
      FROM Meetings;

6.1.1.3.2. NOT EXISTS直接具备了差集运算的功能

7. “肯定⇔双重否定”之间的转换

7.1. 示例

7.1.1.

7.1.2. 所有科目分数都在50分以上

7.1.2.1. 没有一个科目分数不满50分

7.1.3.

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分的科目

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

7.1.4.1.

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

7.1.4.1.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;   --必须两门科目都有分数

7.1.4.1.2. EXISTS和HAVING有一个地方很像，即都是以集合而不是个体为单位来操作数据

8. 集合VS谓词

8.1. 示例

8.1.1.

8.1.2. 查询出哪些项目已经完成到了工程1

8.1.2.1. --查询完成到了工程1的项目：面向集合的解法

    SELECT project_id
      FROM Projects
     GROUP BY 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);

8.1.2.1.1. --查询完成到了工程1的项目：谓词逻辑的解法

    SELECT ＊
      FROM Projects P1
     WHERE NOT EXISTS
          (SELECT status
            FROM Projects P2
            WHERE P1.project_id = P2. project_id      --以项目为单位进行条件判断
              AND status ＜＞ CASE WHEN step_nbr ＜= 1   --使用双重否定来表达全称量化命题
                              THEN ’完成’
                              ELSE ’等待’ END);

8.1.2.1.1.1. 性能好。只要有一行满足条件，查询就会终止

8.1.2.1.1.2. 结果里能包含的信息量更大

9. 对列进行量化

9.1. 示例：：查询全是1的行

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

    SELECT ＊
      FROM ArrayTbl
     WHERE col1 = 1
      AND col2 = 1
          ·
          ·
          ·
      AND col10 = 1;

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

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

9.2. 示例：至少有一个9

9.2.1. --列方向的存在量化(1)

    SELECT ＊
      FROM ArrayTbl
     WHERE 9 = ANY (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);

9.2.1.1. --列方向的存在量化(2)

    SELECT ＊
      FROM ArrayTbl
     WHERE 9 IN (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10);

9.2.1.1.1. 这种写法也是被允许的

9.2.1.1.2. 如果左边不是具体值而是NULL，这种写法就不行了

9.2.2. --查询全是NULL的行：错误的解法

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

9.2.2.1. --查询全是NULL的行：正确的解法

    SELECT ＊
      FROM ArrayTbl
     WHERE COALESCE(col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) IS NULL;

10. C.J. Date曾经这样调侃过：数据库这种叫法有点名不副实，它存储的与其说是数据，还不如说是命题