task4集合运算

第四章
4.1表的加减法
集合运算
表的加法使用union表示并集
例如:


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

or可以代替union使用
例如:4a1b80cae406964b002616e3f69b72b
SELECT product_id,product_name,sale_price,purchase_price
FROM product
WHERE sale_price<800
UNION
SELECT product_id,product_name,sale_price,purchase_price
FROM product
WHERE sale_price>1.5*purchase_price;

SELECT product_id,product_name,sale_price,purchase_price
FROM product
WHERE sale_price>800 or sale_price>1.5*purchase_price;

如果要将两个表合并在一起不得不使用union
练习题:分别使用 UNION 或者 OR 谓词,找出毛利率不足 30%或毛利率未知的商品。
答:16a893caceb637c9cdbedd1ed5e41c7
SELECT *
FROM product
WHERE sale_price / purchase_price <1.3 OR sale_price / purchase_price IS NULL;或者:
SELECT *
FROM product
WHERE sale_price / purchase_price <1.3
UNION
SELECT *
FROM product
WHERE sale_price / purchase_price IS NULL;
70847f5426522d2613a9bd829b00206


总结:union是指对于数据中重复的数据进行合并,即:去重

使用union all可以将数据保留重复行的数据。
例如:d60fa1ce066bb5bbe85d0f5441f47b3


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

练习:72707e635bdf2968ca7ad374bdf9a20


SELECT *
FROM product
WHERE sale_price < 1000
UNION ALL
SELECT *
FROM product
WHERE sale_price < 1.5*purchase_price;

4.1.2.4隐式数据类型转换
使用union合并在一起是代表相同属性,有时候数据类型不完全相同可以使用隐式数据类型转换将会不同类型的列放在一起显示。列入:
cdbc7b743bbf73c45cfd54686b44e5f


需要注意的是进行join进行关联时要避免使用隐式数据类型转换,容易导致数据倾斜。
4.1.3MySQL8.0不支持intersect运算
我们可以使用inner join进行替换使用
d59de56c62566ee4bb2de8287afcaeb
intersect是用来运行集合的交,就是两个集合的公共部分,可以使用inner join进行运行
4.1.1差集,补集与表的减法
求集合差集的减法运算和实数的减法运算不同,使用一个集合A减另一个集合B的时候,对于只存在于集合B而不存在于集合A的元素,采取直接忽略的策略,因此集合A和B做减法只是将集合A中同时属于集合B的元素减掉。

4.1.1.1MySQL8.0不支持except运算
但是可以使用not in进行替换使用

4.14.3intersect与and谓词
将两个查询的检索条件用and谓词连接运算来实现

4.1.5对称差
对称差是指仅属于A或者B的元素构成的集合,两个集合的交集去掉两个合集的对称差。用union求并集,用intersect求交集用并集减去交集得到对称差。
可以使用not in实现两个表的差集
两个交集的合可以看作两个集合的并去掉两个集合的对称差。

使用 UNION 会增加记录行数,而使用 INTERSECT 或者 EXCEPT 会减少记录行数

4.2连结(join)
连结是使用某种关联条件(=)将其他表的列添加进来,连结是SQL中核心的操作,掌握后可以使用较为复杂的查询从不同的表中

连结有很多分类方法,最基础的是内连结和外连结

4.2.1内连结(inner join)
语法格式:FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>
使用inner join可以从两个表中查询我们所需要的列
语句: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;
on后面表示相同的条件进行指定连结
from中使用多种表
select中使用时按照列的格式。

4.2.1.2结合where使用内连结
如果内连结使用时要进行筛选则可以加入where进行对数据的筛选
但是where要再on 的句子后面使用
例如:
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
先进行连结得到新的表,用where进行筛选,最后使用select选出我们所需要的列
也可以将where直接再on中使用、
例如:
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
AND SP.shop_name = ‘东京’
AND P.product_type = ‘衣服’) ;
但是句子不容易阅读理解尽量不使用

4.2.1.3结合group by使用连结
在内连结之前使用group by
如果分组和被聚合的列不在同一张表上,且二者都没有用在连结的表
则只能先连结再聚合
练习:

4.2.1.4自连结(self join)
自连结是指在同一张表上面进行连结,自连结可以外连结也可以内连结

4.2.1.5内连结与子查询
在原本的基础上加入inner join进行内连结,同时加入where进行条件筛选
这样的思路更加清晰便于理解

4.2.1.3自然连结(natural join)
是内连结中的一种特例,在两个表进行自然连结时,表中的包含的列名进行等值内连结,这时就不需要进行on语句进行指定连接条件。
例如:SELECT * FROM shopproduct NATURAL JOIN Product

4.2.1.7使用连结进行求交集
通过inner join进行连结用on进行条件连接
例如:SELECT P1.*
FROM Product AS P1
INNER JOIN Product2 AS P2
ON P1.product_id = P2.product_id
4.2.2外连结(outer join)
内连结会丢弃两张表中不满足on的连接条件的行,与之相反的是外连结,外连结通过种类有选择的保留无法匹配的行。
外连结又三种形式:左连结,右连结和全外连结
左连结会保存左表中无法按照 ON 子句匹配到的行,对应右表的行均为缺失值
语句:
FROM <tb_1> LEFT OUTER JOIN <tb_2> ON <condition(s)>
右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值;
语句:
FROM <tb_1> LEFT OUTER JOIN <tb_2> ON <condition(s)>

全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充。
语句:
FROM <tb_1> FULL OUTER JOIN <tb_2> ON <condition(s)>

4.2.2.1左连结和右连结
由于可以交换左右位置,所以两者没有本质区别

4.2.2.2使用左连结从两个表获取信息
外连结要点 1: 选取出单张表中全部的信息
只包含表内信息的连结也就被称为内连结,外连结名称的由来也跟 NULL 有关,即“结果中包含原表中不存在(在原表之外)的信息
外连结要点 2:使用 LEFT、RIGHT 来指定主表.
外连结还有一点非常重要,那就是要把哪张表作为主表。
指定主表的关键字是 LEFT 和 RIGHT
使用 LEFT 时 FROM 子句中写在左侧的表是主表,
使用 RIGHT 时右侧的表是主表.
通常使用 LEFT 的情况会多一些,但也并没有非使用这个不可的理由,使用 RIGHT 也没有问题
通过交换两个表的顺序, 同时将 LEFT 更换为 RIGHT(如果原先是 RIGHT,则更换为 LEFT), 两种方式会到完全相同的结果。

4.2.2.3结合where字句进行左连结
结合where语句使用外连结很有可能与内连结结果不一样,会包含主表无法匹配到的行,并用缺失值填写的另一表中的列,由于这些行的存在使用where会导致情况不一样。

我们要将where子句在外连结之前进行使用,先写子查询在进行筛选将子查询和主表连结起来。

4.2.2.4在MySQL中实现全外连结
全连结本质上是对左表和右表所有行都予以保留,能用on关联到的把就把左表和右表内容显示到同一行,不能关联的就分别显示,把多余的列用缺失值填充。
但是,mysql8.0还不支持全外连结,不过可以对左连结和右连结的结果用union来实现全外连结。

4.2.3多表连结
连结表数量上没有限制

4.2.3.1多表进行内连结
首先创造一个连结三个表的表’‘,使用内连结将我们所需要的数据找出来
可以 用on来指定连结条件,想要增加连结的表可以使用inner join进行添加。

4.2.3.2多表进行外连结
外连结会比内连结有更多的行,从而能够连结出更多的关于主表的信息,多表连接的时候使用外连结也有同样作用。
例如:
SELECT P.product_id
,P.product_name
,P.sale_price
,SP.shop_id
,SP.shop_name
,IP.inventory_quantity
FROM Product AS P
LEFT OUTER JOIN ShopProduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN InventoryProduct AS IP
ON SP.product_id = IP.product_id

4.24on字句的进阶-非等值连结
使用相等判断的等值连结,还可以使用比较运算符进行连接,包括比较运算符和谓词运算等在内的所有逻辑运算都可以放在on字句内作为连接条件。

4.2.4.1非等值自左连结(SELF JOIN)
使用非等值自左连结实现排名
注意:count函数参数是列名时,会忽略该列中的缺失值,参数为 * 时则不忽略缺失值
如果两个商品的价格相等, 则会导致两个商品的排名错误,
实际上, 进行排名有专门的函数,
使用非等值自左连结进行累计求和:
例如:先按照题意, 对每种商品使用自左连结, 找出比该商品售价价格更低或相等的商品
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
ORDER BY P1.sale_price,P1.product_id
然后进行分组求和:
SELECT product_id
,product_name
,sale_price
,SUM(P2_price) AS cum_price
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
ORDER BY P1.sale_price,P1.product_id ) AS X
GROUP BY product_id, product_name, sale_price
ORDER BY sale_price,product_id;

4.2.5交叉连结-cross join (笛卡尔积)
无论是外连结内连结,共同的必备条件是连结条件–ON 子句,在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN),
就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合.
数据库表(或者子查询)的并,交和差都是在纵向上对表进行扩张或筛选限制等运算的, 这要求表的列数及对应位置的列的数据类型"相容", 因此这些运算并不会增加新的列, 而交叉连接(笛卡尔积)则是在横向上对表进行扩张, 即增加新的列, 这一点和连结的功能是一致的. 但因为没有了ON子句的限制, 会对左表和右表的每一行进行组合, 这经常会导致很多无意义的行出现在检索结果中. 当然, 在某些查询需求中, 交叉连结也有一些用处。
交叉连结的语法有如下几种形式:

  • 1.使用关键字 CROSS JOIN 显式地进行交叉连结
    SELECT SP.shop_id
    ,SP.shop_name
    ,SP.product_id
    ,P.product_name
    ,P.sale_price
    FROM ShopProduct AS SP
    CROSS JOIN Product AS P;
    -2.使用逗号分隔两个表,并省略 ON 子句
    SELECT SP.shop_id
    ,SP.shop_name
    ,SP.product_id
    ,P.product_name
    ,P.sale_price
    FROM ShopProduct AS SP , Product AS P;

交叉连结没有应用到实际业务之中的原因有两个.一是其结果没有实用价值,二是由于其结果行数太多,需要花费大量的运算时间和高性能设备的支持。

交叉连结没有应用到实际业务之中的原因有两个.一是其结果没有实用价值,二是由于其结果行数太多,需要花费大量的运算时间和高性能设备的支持。

4.2.6 连结的特定语法和过时语法
在笛卡尔积的基础上, 我们增加一个 WHERE 子句, 将之前的连结条件作为筛选条件加进去, 我们会发现, 得到的结果恰好是直接使用内连接的结果。

作业:

4.1:
语句:SELECT *
FROM product
WHERE sale_price >500
UNION
SELECT *
FROM product2
WHERE sale_price >500;

4.2:
语句:
SELECT *
FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2);


另一种方法:

4.3:


4.4:
语句:SELECT p1.product_id,p1.product_name,p1.product_type,p2.max_price
FROM product AS p1
INNER JOIN
(SELECT product_type,max(sale_price) AS max_price FROM product GROUP BY product_type) AS p2
ON p1.product_type = p2.product_type
WHERE p1.sale_price = p2.max_price;

4.5:
语句:
SELECT product_id,product_name,sale_price,SUM(p2_price) AS cum_price
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)
OR (p1.sale_price = p2.sale_price AND p1.product_id<=p2.product_id))
ORDER BY p1.sale_price,p1.product_id) AS x
GROUP BY product_id,product_name,sale_price
ORDER BY sale_price,cum_price;


浙ICP备19012682号