最新要闻

广告

手机

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

iphone11大小尺寸是多少？苹果iPhone11和iPhone13的区别是什么？

 警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

警方通报辅警执法直播中被撞飞：犯罪嫌疑人已投案

家电

  1. 首页
  2. 移动通信
  3. 内容

全球动态:读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(＊) = 0THEN ’相等’ELSE’不相等’END AS resultFROM ((SELECT ＊ FROM tbl_AUNIONSELECT ＊ FROM tbl_B)EXCEPT(SELECT ＊ FROM tbl_AINTERSECTSELECT ＊ 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_AEXCEPTSELECT ＊ FROM tbl_B)UNION ALL(SELECT ＊ FROM tbl_BEXCEPTSELECT ＊ 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 empFROM EmpSkills ES1WHERE NOT EXISTS(SELECT skillFROM SkillsEXCEPTSELECT skillFROM EmpSkills ES2WHERE 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 SupPartsWHERE (SELECT partFROM SupPartsWHERE sup ="A")CONTAINS(SELECT partFROM SupPartsWHERE sup ="B")

5.2.示例

  • 5.2.1.--生成供应商的全部组合
SELECT SP1.sup AS s1, SP2.sup AS s2FROM SupParts SP1, SupParts SP2WHERE SP1.sup ＜ SP2.supGROUP BY SP1.sup, SP2.sup;
  • 5.2.2.
SELECT SP1.sup AS s1, SP2.sup AS s2FROM SupParts SP1, SupParts SP2WHERE SP1.sup ＜ SP2.sup --生成供应商的全部组合AND SP1.part = SP2.part --条件1：经营同种类型的零件GROUP BY SP1.sup, SP2.supHAVING COUNT(＊) = (SELECT COUNT(＊) --条件2：经营的零件种类数相同FROM SupParts SP3WHERE SP3.sup = SP1.sup)AND COUNT(＊) = (SELECT COUNT(＊)FROM SupParts SP4WHERE 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”列

关键词：