SQL学习笔记task04——集合运算

学习目标：

SQL学习笔记task04——集合运算

---

学习产出

加法

1. UNION并集运算
   和OR语句有异曲同工之处，但是出于查询效率使用UNION更好

SELECT
	FROM
UNION
SELECT
	FROM

练习题

SELECT *
	FROM product
WHERE sale_price>500
UNION
SELECT *
	FROM product2
WHERE sale_price>500;

UNION中会对查询结果集进行合并去重，如果不需要去除可以使用UNION ALL语句

差集与补集

MySQL8.0暂时不支持EXCEPT命令，我们可以用NOT IN谓词代替

交集

INTERSECT可以用AND实现

JOIN联结

内联结

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

要点一: 进行连结时需要在 FROM 子句中使用多张表.
要点二:必须使用 ON 子句来指定连结条件.
要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用

结合WHERE语句

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 = '衣服' ;

上述查询的执行顺序:
FROM 子句->WHERE 子句->SELECT 子句

自然连结(NATURAL JOIN)

自动找可作为标准的列
eg:

SELECT * 
  FROM (SELECT product_id, product_name
          FROM product ) AS A 
NATURAL JOIN 
   (SELECT product_id, product_name 
      FROM product2) AS B;

外联结OUTER JOIN

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

• 左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值;
• 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值;
• 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充.
• 选取出单张表中全部的信息
• 使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表.

练习题

SELECT  P1.product_id
       ,P1.product_name
       ,P1.product_type
       ,P1.sale_price
       ,P2.avg_price
  FROM product AS P1
 INNER JOIN 
   (SELECT product_type,MAX(sale_price) AS avg_price 
      FROM product 
     GROUP BY product_type) AS P2 
    ON P1.product_type = P2.product_type``
    -----------------------------------------
    关联子查询
    SELECT product_type, product_name, sale_price
  FROM product AS P1
 WHERE sale_price = (SELECT MAX(sale_price)
                       FROM product AS P2
                      WHERE P1.product_type = P2.product_type
                      GROUP BY product_type);

--找出每个商店里的衣服类商品的名称及价格等信息.
SELECT  SP.shop_id,SP.shop_name,SP.product_id 
       ,P.product_name, P.product_type, P.purchase_price
  FROM shopproduct  AS SP 
 INNER JOIN product AS P 
    ON SP.product_id = P.product_id
 WHERE P.product_type = '衣服';


SELECT  SP.shop_id, SP.shop_name, SP.product_id
       ,P.product_name, P.product_type, P.purchase_price
  FROM shopproduct AS SP 
INNER JOIN --从 product 表找出衣服类商品的信息
  (SELECT product_id, product_name, product_type, purchase_price
     FROM product	
    WHERE product_type = '衣服')AS P 
   ON SP.product_id = P.product_id;

PART2

--T1-----------------
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
--多表联结
SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
       ,IP.inventory_quantity
  FROM shopproduct AS SP
 INNER JOIN product 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';

PART3

SELECT p1.product_id, 
 p1.product_name, 
 sale_price, 
 (SELECT SUM(sale_price) FROM product AS p2 WHERE p1.sale_price > p2.sale_price 
 OR (p1.sale_price = p2.sale_price AND p1.product_id <= p2.product_id) 
 ORDER BY p1.sale_price,p1.product_id) AS cum_price
FROM product AS p1 
ORDER BY cum_price