SQL训练营集合运算学习笔记

本学习笔记为阿里云天池龙珠计划SQL训练营的学习内容，学习链接为

 

Task04：集合运算-表的加减法和join等-天池龙珠计划SQL训练营-天池技术圈-天池技术讨论区

一、学习知识点概要

4.1表的加减法
4.1.1 什么是集合运算
4.1.2 表的加法–UNION
4.1.2.1 UNION
4.1.2.2UNION 与 OR 谓词
4.1.2.3 包含重复行的集合运算 UNION ALL
4.1.2.4[扩展阅读]bag 模型与 set 模型 
4.1.2.5隐式类型转换
4.1.3 MySQL 8.0 不支持交运算INTERSECT
4.1.3.1[扩展阅读]bag 的交运算 
4.1.4 差集,补集与表的减法
4.1.4.1 MySQL 8.0 还不支持 EXCEPT 运算
4.1.4.2 EXCEPT 与 NOT 谓词
4.1.4.3EXCEPT ALL 与bag 的差 
4.1.4.4INTERSECT 与 AND 谓词
4.1.5对称差 
4.1.5.1借助并集和差集迂回实现交集运算 INTERSECT
4.2连结(JOIN)
4.2.1 内连结(INNER JOIN)
4.2.1.1 使用内连结从两个表获取信息
4.2.1.2 结合 WHERE 子句使用内连结
4.2.1.3结合 GROUP BY 子句使用内连结
4.2.1.4自连结(SELF JOIN)
4.2.1.5内连结与关联子查询
4.2.1.6自然连结(NATURAL JOIN)
4.2.1.7使用连结求交集
4.2.2 外连结(OUTER JOIN)
4.2.2.1 左连结与右连接
4.2.2.2 使用左连结从两个表获取信息
4.2.2.3结合 WHERE 子句使用左连结
4.2.2.4在 MySQL 中实现全外连结
4.2.3多表连结
4.2.3.1 多表进行内连结
4.2.3.2多表进行外连结
4.2.4ON 子句进阶–非等值连结
4.2.4.1非等值自左连结(SELF JOIN)
4.2.5 交叉连结—— CROSS JOIN(笛卡尔积)
4.2.5.1[扩展阅读]连结与笛卡儿积的关系
4.2.6 连结的特定语法和过时语法
练习题

二、学习内容

UNION 去重

UNION ALL不去重

MYSQL不支持intersect/except

//两种写法
①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 = '衣服' ;

②SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.product_type
       ,P.sale_price
       ,SP.quantity
  FROM (-- 子查询 1:从shopproduct 表筛选出东京商店的信息
        SELECT *
          FROMshopproduct
         WHERE shop_name = '东京' ) AS SP
 INNER JOIN -- 子查询 2:从 product 表筛选出衣服类商品的信息
   (SELECT *
      FROMproduct
     WHERE product_type = '衣服') AS P
    ON SP.product_id = P.product_id;

注意点：查看真值表可以发现 两个不确定的值返回为不确定而非TRUE，而自然连接只返回为true的记录。

SELECT P.product_id
      ,P.product_name
      ,P.sale_price
       ,SP.shop_id
      ,SP.shop_name
      ,SP.quantity 
  FROMproduct AS P
  LEFT OUTER JOIN-- 先筛选quantity<50的商品
   (SELECT *
      FROMshopproduct
     WHERE quantity < 50 ) AS SP
    ON SP.product_id = P.product_id
//把where子句放在外连接之前可能更好

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

三、学习问题与解答

--4.1 找出 product 和 product2 中售价高于 500 的商品的基本信息。
SELECT * FROM product
WHERE SALE_PRICE > 500
UNION 
SELECT * FROM product2
WHERE sale_price > 500

--4.2 借助对称差的实现方式, 求product和product2的交集
SELECT * FROM PRODUCT 
WHERE PRODUCT_ID IN (SELECT product_id FROM product2)

--4.3 每类商品中售价最高的商品都在哪些商店有售 ？
SELECT p2.PRODUCT_TYPE,p2.PRODUCT_NAME,p2.SALE_PRICE,sp.shopname
FROM PRODUCT p2 
INNER JOIN (SELECT p.PRODUCT_TYPE,max(SALE_PRICE) AS maxprice
FROM PRODUCT p
GROUP BY PRODUCT_TYPE)pp
ON maxprice = p2.SALE_PRICE AND pp.PRODUCT_TYPE = p2.PRODUCT_TYPE 
LEFT JOIN shopproduct  sp
ON sp.product_id=p2.product_id
            
 --4.4 分别使用内连结和关联子查询每一类商品中售价最高的商品。
 --内连结：
 SELECT p2.PRODUCT_TYPE,p2.PRODUCT_NAME,p2.SALE_PRICE
FROM PRODUCT p2 
INNER JOIN (SELECT p.PRODUCT_TYPE,max(SALE_PRICE) AS maxprice
FROM PRODUCT p
GROUP BY PRODUCT_TYPE)pp
ON maxprice = p2.SALE_PRICE AND pp.PRODUCT_TYPE = p2.PRODUCT_TYPE           
            
 --关联子查询：
 SELECT PRODUCT_TYPE ,PRODUCT_NAME ,p.SALE_PRICE 
 FROM PRODUCT p 
 WHERE p.SALE_PRICE >=(SELECT max(SALE_PRICE) FROM PRODUCT p2 
 WHERE p.PRODUCT_TYPE = p2.PRODUCT_TYPE 
 GROUP BY PRODUCT_TYPE)
 
 --4.5用关联子查询实现：在product表中，取出 product_id, product_name, sale_price, 并按照商品的售价从低到高进行排序、对售价进行累计求和。
SELECT P1.product_id
               ,P1.product_name
               ,P1.sale_price
               ,(select sum(sale_price) from product  p
	where p1.sale_price > p.sale_price or(p1.sale_price = p.sale_price AND p1.PRODUCT_ID>=p.PRODUCT_ID)
	)  AS sumprice
          FROM product  P1 
ORDER BY p1.SALE_PRICE

四、学习思考与总结

        4.5没有想起来可以把select子句也作为列展示，对关联子查询感觉还是没太熟悉，4.2我看了别人同学的，我好像写的虽然结果一样但不是对称差的实现方式哈哈；知道现在用where来连结表的方式不是很好，更多还是用JOIN，left join比较常用在生活中。