Mysql学习入门Task04：集合运算

Mysql学习入门Task04：集合运算

4.1 表的加减法

表的加法–UNION

一个表union,和or效果一样

SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM product 
 WHERE sale_price<800
  
 UNION
 
SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM product 
 WHERE sale_price>1.5*purchase_price;
 

两个表union，不能用or了这时

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

UNION是去重复的，如果不需要去重就用UNION ALL

交集运算INTERSECT，mysql不支持
所以俩表交集运算只能用inner join，一个表就是AND

SELECT p1.product_id, p1.product_name
  FROM Product as p1
INNER JOIN Product2 as p2
ON p1.product_id=p2.product_id;

差集运算EXCEPT ，mysql也不支持，所以用NOT IN

SELECT * 
  FROM product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM product2)

对称差集, 就是俩差集再并，所以俩not in 再union

-- 使用 NOT IN 实现两个表的差集
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.2 连结 (JOIN)

内连结(INNER JOIN)

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

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

外连结(OUTER JOIN)

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

-- 左连结     
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)>

example

SELECT P.product_id
      ,P.product_name
      ,P.sale_price
       ,SP.shop_id
      ,SP.shop_name
      ,SP.quantity 
  FROM product AS P
  LEFT OUTER JOIN-- 先筛选quantity<50的商品
   (SELECT *
      FROM shopproduct
     WHERE quantity < 50 ) AS SP
    ON SP.product_id = P.product_id

全外连结
全外连结本质上就是对左表和右表的所有行都予以保留, 能用 ON 关联到的就把左表和右表的内容在一行内显示, 不能被关联到的就分别显示, 然后把多余的列用缺失值填充.

遗憾的是, MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结.

练习题
4.1
找出 product 和 product2 中售价高于 500 的商品的基本信息。

4.2
借助对称差的实现方式, 求product和product2的交。

4.3
每类商品中售价最高的商品都在哪些商店有售 ？

4.4
分别使用内连结和关联子查询每一类商品中售价最高的商品。

4.5用关联子查询实现：在 product 表中，取出 product_id, produc_name, slae_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。