读SQL进阶教程笔记04_集合运算

1. 集合论是SQL语言的根基

1.1. UNION

1.1.1. SQL-86标准

1.2. NTERSECT和EXCEPT

1.2.1. SQL-92标准

1.3. 除法运算（DIVIDE BY）

1.3.1. 没有被标准化

2. 注意事项

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

2.1.1. 不允许重复
- 2.1.1.1. 直接使用UNION或INTERSECT
- 2.1.1.2. 集合运算符为了排除掉重复行，默认地会发生排序
2.1.2. 允许重复
- 2.1.2.1. 加上可选项ALL
  
  2.1.2.1.1. 不会再排序，所以性能会有提升
  
  2.1.2.1.2. 非常有效的用于优化查询性能的方法
- 2.1.2.2. UNION ALL
  
  2.1.2.2.1. 不具有幂等性

2.2. 集合运算符有优先级

2.2.1. INTERSECT比UNION和EXCEPT优先级更高
2.2.2. 括号明确地指定运算顺序

2.3. 各个DBMS提供商在集合运算的实现程度上参差不齐

2.3.1. SQL Server从2005版开始支持INTERSECT和EXCEPT
2.3.2. Oracle这样，实现了EXCEPT功能但却命名为MINUS的数据库
2.3.3. INTERSECT和EXCEPT不能在MySQL里执行

2.4. 除法运算没有标准定义

2.4.1. 四则运算里的和（UNION）、差（EXCEPT）、积（CROSS JOIN）都被引入了标准SQL

3. 检查集合相等性

3.1. “相等”指的是行数和列数以及内容都相同

3.2. “是同一个集合”

3.3. 原理1

3.3.1. S UNION S = S
3.3.2. 幂等性（indempotency）
- 3.3.2.1. 抽象代数里群论等理论中的概念
- 3.3.2.2. 二目运算符对任意S，都有SS = S成立”

3.4. 示例1

3.4.1.

  SELECT COUNT(＊) AS row_cnt
     FROM ( SELECT ＊
             FROM tbl_A
           UNION
           SELECT ＊
             FROM tbl_B ) TMP;

3.4.2. 这个查询的结果与tbl_A及tbl_B的行数一致，则两张表是相等的
3.4.3. 表tbl_A和表tbl_B的行数是一样的
- 3.4.3.1. 如果行数不一样，那就不需要比较其他的了

3.5. 原理2

3.5.1. 如果A UNION B = A INTERSECT B，则集合A和集合B相等
3.5.2. (A UNION B) EXCEPT (A INTERSECT B)的结果集是不是空集就可以了
3.5.3. INTERSECT
- 3.5.3.1. 幂等性（indempotency）
3.5.4. EXCEPT不具有幂等性

3.6. 示例2

3.6.1. --两张表相等时返回“相等”，否则返回“不相等”

   SELECT CASE WHEN COUNT(＊) = 0
               THEN ’相等’
               ELSE’不相等’END AS result
     FROM ((SELECT ＊ FROM  tbl_A
           UNION
           SELECT ＊ FROM  tbl_B)
           EXCEPT
           (SELECT ＊ FROM  tbl_A
           INTERSECT
           SELECT ＊ FROM  tbl_B)) TMP;

3.6.2. 改进版不需要事先查询两张表的行数
3.6.3. 需要进行4次排序（3次集合运算加上1次DISTINCT）
- 3.6.3.1. 性能会有所下降

3.7. 示例3

3.7.1. --用于比较表与表的diff

   (SELECT ＊ FROM  tbl_A
    EXCEPT
    SELECT ＊ FROM  tbl_B)
   UNION ALL
   (SELECT ＊ FROM  tbl_B
    EXCEPT
    SELECT ＊ FROM  tbl_A);

4. 用差集实现关系除法运算

4.1. 嵌套使用NOT EXISTS

4.2. 使用HAVING子句转换成一对一关系

4.3. 把除法变成减法

4.3.1. 示例
- 4.3.1.1.

4.3.1.2. --用求差集的方法进行关系除法运算（有余数）

   SELECT DISTINCT emp
     FROM EmpSkills ES1
    WHERE NOT EXISTS
           (SELECT skill
             FROM Skills
           EXCEPT
           SELECT skill
             FROM EmpSkills ES2
             WHERE ES1.emp = ES2.emp);

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

5. 寻找相等的子集

5.1. IBM过去研制的第一个关系数据库实验系统——System R

5.1.1. 用CONTAINS这一谓词来检查集合间的包含关系
5.1.2. 后来因为性能原因被删除掉了，直到现在也没有恢复
5.1.3.

SELECT 'A CONTAINS B'

     FROM SupParts
    WHERE (SELECT part
             FROM SupParts
           WHERE  sup ='A')
             CONTAINS
                 (SELECT part
                   FROM SupParts
                   WHERE  sup ='B')

5.2. 示例

5.2.1. --生成供应商的全部组合

   SELECT SP1.sup AS s1, SP2.sup AS s2
     FROM SupParts SP1, SupParts SP2
    WHERE SP1.sup ＜ SP2.sup
    GROUP BY SP1.sup, SP2.sup;

5.2.2.

SELECT SP1.sup AS s1, SP2.sup AS s2

     FROM SupParts SP1, SupParts SP2
    WHERE SP1.sup ＜ SP2.sup                  --生成供应商的全部组合
     AND SP1.part = SP2.part                --条件1：经营同种类型的零件
    GROUP BY SP1.sup, SP2.sup
   HAVING COUNT(＊) = (SELECT COUNT(＊)       --条件2：经营的零件种类数相同
                       FROM SupParts SP3
                       WHERE SP3.sup = SP1.sup)
     AND COUNT(＊) = (SELECT COUNT(＊)
                       FROM SupParts SP4
                       WHERE SP4.sup = SP2.sup);

5.2.3. SQL在比较两个集合时，并不是以行为单位来比较的，而是把集合当作整体来处理的

6. 用于删除重复行的高效SQL

6.1. --删除重复行：使用关联子查询

    DELETE FROM Products
     WHERE rowid ＜ ( SELECT MAX(P2.rowid)
                      FROM Products P2
                      WHERE Products.name  = P2. name
                        AND Products.price = P2.price ) ;

6.2. --用于删除重复行的高效SQL语句(1)：通过EXCEPT求补集

    DELETE FROM Products
     WHERE rowid IN ( SELECT rowid           --全部rowid
                        FROM Products
                      EXCEPT                 --减去
                      SELECT MAX(rowid)     --要留下的rowid
                        FROM Products
                      GROUP BY name, price) ;

6.3. --删除重复行的高效SQL语句(2)：通过NOT IN求补集

    DELETE FROM Products
     WHERE rowid NOT IN ( SELECT MAX(rowid)
                            FROM Products
                          GROUP BY name, price);

6.3.1. 不支持EXCEPT的数据库也可以使用

6.4. 实现了行ID的数据库只有Oracle和PostgreSQL

6.4.1. PostgreSQL里的相应名字是oid，如果要使用，需要事先在CREATE TABLE的时候指定可选项WITH OIDS
6.4.2. 如果其他数据库想要使用这些SQL，则需要在表中创建类似的具有唯一性的“id”列

posted @ 2023-04-01 07:39 躺柒阅读(86) 评论(0) 编辑收藏举报

刷新页面返回顶部