阿里云AI训练营SQL入门与实践Task4

表的加减法
表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。
在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像**UNION,INTERSECT**, **EXCEPT**这种用来进行集合运算的运算符称为集合运算符。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xnpEpSE4-1640243090299)(https://s3-us-west-2.amazonaws.com/secure.notion-static.com/4356ea0e-245b-4a51-a80e-eeb82c896b32/Untitled.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xZI2mx36-1640243090301)(https://s3-us-west-2.amazonaws.com/secure.notion-static.com/8a33a134-cb68-46d2-925c-92cc90681af5/Untitled.png)]
- 表的加法
  - UNION
    UNION 会对两个查询的结果集进行合并和去重, 这种去重不仅会去掉两个结果集相互重复的, 还会去掉一个结果集中的重复行
    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jWacdlRv-1640243090302)(https://s3-us-west-2.amazonaws.com/secure.notion-static.com/ea984434-f10b-4604-86ea-b88fc023ff59/Untitled.png)]
```
--例子：
--分别使用 UNION 或者 OR 谓词,找出毛利率不足 30%或毛利率未知的商品.
-- 参考答案:
SELECT * 
  FROM product 
 WHERE sale_price / purchase_price < 1.3
 
 UNION
SELECT * 
  FROM product 
 WHERE sale_price / purchase_price IS NULL;
```
  - 包含重复行的UNION ALL
    需要不去重的并集, 在 UNION 的结果中保留重复行的语法其实非常简单,只需要在 UNION 后面添加 ALL 关键字就可以了.
```
-- 商店决定对product表中利润低于50%和售价低于1000的商品提价
SELECT * 
  FROM product 
 WHERE sale_price < 1000
 UNION ALL
SELECT * 
  FROM product 
 WHERE sale_price < 1.5 * purchase_price
```
- 对称差
```
-- 使用 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)
```

连结(JOIN)

连结(JOIN)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算。

内连接(INNER JOIN)

要点一: 进行连结时需要在 FROM 子句中使用多张表.

要点二:必须使用 ON 子句来指定连结条件.

要点三: 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 = '衣服' ;

自连接

一张表与自身作连结。

--例子：
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,AVG(sale_price) AS avg_price 
      FROM product 
     GROUP BY product_type) AS P2 
    ON P1.product_type = P2.product_type
 WHERE P1.sale_price > P2.avg_price;
--关联子查询：以表 A 为主表, 然后根据表 A 的关联列的每一行的取值,逐个到表 B 中的关联列中去查找取值相等的行.

自然连结(NATURAL JOIN)
自然连结并不是区别于内连结和外连结的第三种连结, 它其实是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件.
```
--求表 product 和表 product2 中的公共部分, 可以用自然连结来实现:
SELECT * FROM product NATURAL JOIN product2
```

外连结(OUTER JOIN)

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

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

左连接

保留写在做边的表。

外连结要点 1: 选取出单张表中全部的信息

外连结要点 2:使用 LEFT、RIGHT 来指定主表

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROMproduct AS P
  LEFT OUTER JOINshopproduct AS SP
    ON SP.product_id = P.product_id;

非等值自左连结(SELF JOIN)

--对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数
SELECT  product_id
       ,product_name
       ,sale_price
       ,COUNT(p2_id) AS rank_id
  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_id;

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

-- 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;

练习题

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 (select * from product union select * from product2) as u
	where product_id not in (
select product_id from product where product_id not in (select product_id from product2)
union
select product_id from product2 where product_id not in (select product_id from product)
							);

4.3

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

select sp.shop_id, sp.shop_name, sp.quantity,
        p.product_id, p.product_name, p.product_type, p.sale_price,
        mp.maxp as '该类商品的最大售价' 
from product as p 
inner join shopproduct as sp 
on sp.product_id = p.product_id
inner join (
			select product_type, max(sale_price) as maxp from product 
            group by product_type
            ) as mp
on mp.product_type = p.product_type and p.sale_price = mp.maxp;

4.4

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

--内连接
select p.product_id, p.product_name, p.product_type, p.sale_price
from product as p 
inner join (
			select product_type, max(sale_price) as maxp from product 
            group by product_type
            ) as mp
on mp.product_type = p.product_type and p.sale_price = mp.maxp;
--关联子查询
select p.product_id,p.product_name,p.product_type,p.sale_price from product as p
where sale_pirce=(
		select max(sale_price) from product as p1 
		where p.product_type=p1.product_type);
)

4.5

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

select p.product_id, p.product_name, p.product_type, p.sale_price,
	(select sum(sale_price) from product as p1
	where p.sale_price >= p1.sale_price
	) as '累计求和'
from product as p 
order by sale_price;

原文链接：https://blog.csdn.net/m0_46209092/article/details/122108120