SQL学习之“集合运算”

本篇文章记录学习SQL集合运算的过程与心得。

对于集合这个概念，印象最深的就是数学课里学的，简单的说就是把研究对象当做元素，则一些元素组成的整体就叫集合。

而在数据库领域，则表示记录的集合，具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。在数据库中, 所有的表--以及查询结果--都可以视为集合。

1、表的加减法

UNION就是相当于2个表的并集，但是 UNION 等集合运算符通常都会除去重复的记录。若想包含重复行，使用集合运算 UNION ALL方法即可。

使用 UNION 对两个查询结果取并集, 和在一个查询中使用 WHERE 子句, 然后使用 OR 谓词连接两个查询条件, 能够得到相同的结果。

示例：

找出毛利率不足 30%或毛利率未知的商品的语句如下所示。

 

通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型。

集合的交, 就是两个集合的公共部分, 由于集合元素的互异性, 集合的交只需通过文氏图就可以很直观地看到它的意义。

 但是MySQL 目前不支持INTERSECT操作，可以用INNER JOIN来代替。

集合的差集

求集合差集的减法运算和实数的减法运算有些不同, 当使用一个集合A减去另一个集合B的时候,对于只存在于集合B而不存在于集合A的元素, 采取直接忽略的策略,因此集合A和B做减法只是将集合A中也同时属于集合B的元素减掉。

MySQL目前也不支持表的减法运算符 EXCEPT，不过, 借助NOT IN 谓词, 同样可以实现表的减法。

对称差

两个集合A，B的对称差是指那些仅属于A或仅属于B的元素构成的集合。懂得2集合的差集计算就很容易理解该概念。对称差也是个非常基础的运算，例如，两个集合的交就可以看作是两个集合的并去掉两个集合的对称差。从直观上就能看出来, 两个集合的对称差等于 A-B并上B-A，因此实践中可以用这个思路来求对称差。

2、连结

UNION和INTERSECT 等集合运算, 这些集合运算的特征就是以行方向为单位进行操作。进行这些集合运算时, 会导致记录行数的增减。使用 UNION 会增加记录行数,而使用 INTERSECT 或者 EXCEPT 会减少记录行数。但这些运算不能改变列的变化, 虽然使用函数或者 CASE表达式等列运算, 可以增加列的数量, 但仍然只能从一张表中提供的基础信息列中获得一些"引申列", 本质上并不能提供更多的信息。

连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算. 可以说,连结是 SQL 查询的核心操作, 掌握了连结, 能够从两张甚至多张表中获取列, 能够将过去使用关联子查询等过于复杂的查询简化为更加易读的形式, 以及进行一些更加复杂的查询。
本次主要学习了内连结、外连结、自连结和自然连结。

内连结

内连结就是按照特定的条件，将两个不同的表进行连结，这个条件可理解为桥梁，通常是公共列。

语法：

FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

关于内连结，需要注意的有三点。

（1） 进行连结时需要在 FROM 子句中使用多张表。

（2）必须使用 ON 子句来指定连结条件。

（3）SELECT 子句中的列最好按照 表名.列名 的格式来使用。

如果需要在使用内连结的时候同时使用 WHERE 子句对检索结果进行筛选, 则需要把 WHERE 子句写在 ON 子句的后边。

结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待。

外连结

内连结会丢弃两张表中不满足 ON 条件的行，和内连结相对的就是外连结。外连结会根据外连结的种类有选择地保留无法匹配到的行。

按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结.

左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充。三种连结的语法如下所示。

外连结的要点有两个：

（1）使用外连结能够得到固定行数的结果，可选取出单张表中全部的信息。

（2）使用外连结时，要用LEFT、RIGHT 来指定主表，通常使用 LEFT 的情况会多一些,但也并没有非使用这个不可的理由,使用 RIGHT 也没有问题。

自连结

一张表也可以与自身作连结, 这种连接称之为自连结。自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法。

自然连结

自然连接其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件。这种连结并不是太常用到。

3、多表连结

通常连结只涉及 2 张表,但有时也会出现必须同时连结 3 张以上的表的情况, 原则上连结表的数量并没有限制，可以对多个表进行内连结合外连结。

4、交叉连结

对两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合。在数据库领域，交叉连结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积。

练习题

练习题4.1答案

SELECT *

FROM product

WHERE sale_price > 500

UNION

SELECT *

FROM product2

WHERE sale_price > 500;

练习题4.2答案

SELECT * 
  FROM Product
 WHERE product_id NOT IN (SELECT product_id FROM Product2)
UNION
SELECT * 
  FROM Product2
 WHERE product_id NOT IN (SELECT product_id FROM Product)

练习题4.3答案

 SELECT sail.product_id, shop_name, product_type
  FROM (
        SELECT product_id, max_price.product_type
          FROM (SELECT *
                  FROM product
                 UNION
                SELECT *
                  FROM product2) AS all_product
          JOIN (SELECT MAX(sale_price) AS max, product_type
                  FROM (SELECT *
                          FROM product
                         UNION
                        SELECT *
                          FROM product2) AS all_product
                 GROUP BY product_type) AS max_price
            ON all_product.product_type = max_price.product_type
         WHERE sale_price = max
       ) AS id
  LEFT JOIN ShopProduct AS sail
    ON id.product_id = sail.product_id;

 

练习题4.4答案

内连结查询每类商品中售价最高的商品都在哪些商店有售 

   SELECT product_id
FROM (SELECT *
        FROM product
       UNION
      SELECT *
        FROM product2) AS all_product
  JOIN (SELECT MAX(sale_price) AS max, product_type
          FROM (SELECT *
                  FROM product
                 UNION
                SELECT *
                  FROM product2) AS all_product
         GROUP BY product_type) max_price
    ON all_product.product_type = max_price.product_type
 WHERE sale_price = max; 

 

 

使用关联子查询来查询每类商品中售价最高的商品都在哪些商店有售。

练习题4.5答案

    SELECT product_id, product_name, sale_price,
    (SELECT SUM(sale_price)
       FROM product AS p2
      WHERE p2.sale_price <= p1.sale_price) AS sum
  FROM product AS p1
 ORDER BY sale_price;