SQL-task4

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

4.1表的加减法

小测验1：

使用UNION
select product_id,product_name,product_type,sale_price,purchase_price
from product
where (sale_price - purchase_price) / purchase_price > 0.5
union 
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 - purchase_price) / purchase_price > 0.5 
or sale_price < 800;

对于同一个表的两个不同的筛选结果集, 使用 UNION 对两个结果集取并集, 和把两个子查询的筛选条件用 OR 谓词连接, 会得到相同的结果

倘若要将两个不同的表中的结果合并在一起, 就不得不使用 UNION 了

小测验2：

select product_id,product_name,product_type,sale_price,purchase_price
from product
where (sale_price - purchase_price) / purchase_price < 0.3
union 
select product_id,product_name,product_type,sale_price,purchase_price
from product
where (sale_price - purchase_price) is null;

select product_id,product_name,product_type,sale_price,purchase_price
from product
where (sale_price - purchase_price) / purchase_price < 0.3
or (sale_price - purchase_price) is null;

select * from product
where sale_price/purchase_price < 1.5
union all
select * from product
where sale_price < 1000;
bag 模型与 set 模型

Bag 是和 set 类似的一种数学结构, 不一样的地方在于: bag 里面允许存在重复元素, 如果同一个元素被加入多次, 则袋子里就有多个该元素.

对于 A = {1,1,1,2,3,5,7}, B = {1,1,2,2,4,6,8} 两个 bag, 它们的并就等于 {1,1,1,2,2,3,4,5,6,7,8}.

MySQL 8.0 不支持交运算INTERSECT

对于同一个表，交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现.

MySQL 8.0 还不支持 EXCEPT 运算

借助NOT IN 谓词, 同样可以实现表的减法.

两个集合的对称差等于 A-B并上B-A

-- 使用 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)
内连结

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

SELECT * FROM shopproduct NATURAL JOIN product

外连结

内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对的就是外连结. 

外连结有三种形式: 左连结, 右连结和全外连结.

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

比较运算符(<,<=,>,>=, BETWEEN)和谓词运算(LIKE, IN, NOT 等等)在内的所有的逻辑运算都可以放在 ON 子句内作为连结条件.

 SELECT  product_id
       ,product_name
       ,sale_price
       ,(COUNT(p2_id)+1) 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;