VB.net 2010 视频教程 VB.net 2010 视频教程 python基础视频教程
SQL Server 2008 视频教程 c#入门经典教程 Visual Basic从门到精通视频教程
当前位置:
首页 > 数据库 > T-SQL >
  • 读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 ( SELECTFROM tbl_A
           UNION
           SELECTFROM 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 ((SELECTFROM  tbl_A
           UNION
           SELECTFROM  tbl_B)
           EXCEPT
           (SELECTFROM  tbl_A
           INTERSECT
           SELECTFROM  tbl_B)) TMP;
  • 3.6.2. 改进版不需要事先查询两张表的行数

  • 3.6.3. 需要进行4次排序(3次集合运算加上1次DISTINCT)

    • 3.6.3.1. 性能会有所下降

3.7. 示例3

  • 3.7.1. --用于比较表与表的diff
   (SELECTFROM  tbl_A
    EXCEPT
    SELECTFROM  tbl_B)
   UNION ALL
   (SELECTFROM  tbl_B
    EXCEPT
    SELECTFROM  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”列
     

    出处:https://www.cnblogs.com/lying7/p/17277846.html

     


相关教程