集合运算

  • 1.表的加减法
  • 1.1集合运算
  • 1.2 表的加法 -UNION
  • 1.3 包含重复行的集合运算 UNION ALL
  • 1.4 bag模型与set模型
  • 1.5 隐式类型转换
  • 1.6 差集 EXCEPT 与 NOT
  • 1.7 交集 INTERSECT 与 AND
  • 1.8 对称差
  • 2.连结（join）
  • 2.1 内连结
  • 2.1.2 自连结
  • 2.2 自然连结
  • 2.3 外连结
  • 2.4多表连接
  • 2.5ON 子句进阶–非等值连结
  • 2.6 交叉连结—— CROSS JOIN(笛卡尔积)



1.表的加减法

1.1集合运算

数据库中选出的数据为集合，集合运算 交 并 补 差。

1.2 表的加法 -UNION

在两个select之间使用。 两个集合之间

SELECT product_id, product_name
  FROM product
 UNION
SELECT product_id, product_name
  FROM product2;

union可以在不同表之间使用，同一个表时可以使用or代替union。

1.3 包含重复行的集合运算 UNION ALL

SELECT product_id, product_name
  FROM product
 UNION ALL
SELECT product_id, product_name
  FROM product2;

1.4 bag模型与set模型

  • bag允许元素重复
  • bag 加法 1.该元素是否至少在一个 bag 里出现过, 2.该元素在两个 bag 中的最大出现次数

1.5 隐式类型转换

常量会进行广播

SELECT product_id, product_name, '1'
  FROM product
 UNION
SELECT product_id, product_name,sale_price
  FROM product2;

‘1’会进行广播

1.6 差集 EXCEPT 与 NOT

  • ·except与not in 可以实现相同的效果
  • except all 与union all 一样
  • 处理bag时 1.该元素是否属于作为被减数的 bag,2.该元素在两个 bag 中的出现次数差

1.7 交集 INTERSECT 与 AND

  • 用法与union类似
  • and 可以实现相同效果

1.8 对称差

  • 并集减去交集

2.连结（join）

分为三类 内连结，外连结，自然连结

可以和子查询实现相同的功能。连结后当做新表对待即可。

mysql 两个结果集联合起来 sql两个结果集相加_IP

2.1 内连结

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

不同的表是平行宇宙，即使变量名相同，实质不相同。因此连结与子查询都需要指定连结条件

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROMshopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id;

通过 id进行连结。并且选出一些列。连结后看成新表，然后再根据where条件select。FROM 子句->WHERE 子句->SELECT 子句。

SELECT  SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROMshopproduct AS SP
 INNER JOINproduct AS P
    ON SP.product_id = P.product_id
 WHERE SP.shop_name = '东京'
   AND P.product_type = '衣服' ;

结合group by时，依旧把连结后的表看做新表即可。

-- 每个商店中, 售价最高的商品的售价分别是多少?
SELECT SP.shop_id
      ,SP.shop_name
      ,MAX(P.sale_price) AS max_price
  FROMshopproduct AS SP
 INNER JOINproduct AS P
    ON SP.product_id = P.product_id
 GROUP BY SP.shop_id,SP.shop_name

回忆group by的使用，选出的列名与group by列名相同，然后使用聚合函数。

2.1.2 自连结

之前的内连结, 连结的都是不一样的两个表. 但实际上一张表也可以与自身作连结, 这种连接称之为自连结. 需要注意, 自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法。

2.2 自然连结

不需要指定条件，自动根据都包含的相同列名进行连结

SELECT *  FROM shopproduct NATURAL JOIN product

2.3 外连结

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

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

左右连结确定主表，最终结果会包含主表内所有数据。

2.4多表连接

通常连结只涉及 2 张表,但有时也会出现必须同时连结 3 张以上的表的情况, 原则上连结表的数量并没有限制。

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
       ,IP.inventory_quantity
  FROMshopproduct AS SP
 INNER JOINproduct AS P
    ON SP.product_id = P.product_id
 INNER JOIN Inventoryproduct AS IP
    ON SP.product_id = IP.product_id
 WHERE IP.inventory_id = 'P001';

我们可以看到, 连结第三张表的时候, 也是通过 ON 子句指定连结条件(这里使用最基础的等号将作为连结条件的product 表和shopproduct 表中的商品编号 product _id 连结了起来), 由于product 表和shopproduct 表已经进行了连结,因此就无需再对product 表和 Inventoryproduct 表进行连结了(虽然也可以进行连结,但结果并不会发生改变, 因为本质上并没有增加新的限制条件).

2.5ON 子句进阶–非等值连结

在刚开始介绍连结的时候, 书上提到过, 除了使用相等判断的等值连结, 也可以使用比较运算符来进行连接. 实际上, 包括比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件.
希望对 product 表中的商品按照售价赋予排名. 一个从集合论出发,使用自左连结的思路是, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数. 例如, 对于价格最高的商品,

SELECT  product_id
       ,product_name
       ,sale_price
       ,COUNT(p2_id) AS rank
  FROM (--使用自左连结对每种商品找出价格不低于它的商品
        SELECT P1.product_id
               ,P1.product_name
               ,P1.sale_price
               ,P2.product_id AS P2_id
               ,P2.product_name AS P2_name
               ,P2.sale_price AS P2_price 
          FROM product AS P1 
          LEFT OUTER JOIN product AS P2 
            ON P1.sale_price <= P2.sale_price 
        ) AS X
 GROUP BY product_id, product_name, sale_price
 ORDER BY rank;

注意group by配合聚合函数使用

2.6 交叉连结—— CROSS JOIN(笛卡尔积)

之前的无论是外连结内连结, 一个共同的必备条件就是连结条件–ON 子句, 用来指定连结的条件. 如果你试过不使用这个连结条件的连结查询, 你可能已经发现, 结果会有很多行. 在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积, 后者是一个数学术语. 两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合.

  • 去掉on
  • 全部连结
-- 1.使用关键字 CROSS JOIN 显式地进行交叉连结
SELECT SP.shop_id
      ,SP.shop_name
      ,SP.product_id
      ,P.product_name
      ,P.sale_price
 FROMshopproduct AS SP
CROSS JOINproduct AS P;
--2.使用逗号分隔两个表,并省略 ON 子句
SELECT SP.shop_id
      ,SP.shop_name
      ,SP.product_id
      ,P.product_name
      ,P.sale_price
 FROMshopproduct AS SP ,product AS P;

对笛卡尔连结用where筛选条件可以达到内连结的一致效果。