[阿里云天池龙珠计划] SQL训练营-Task04-集合运算-表的加减法和join等

最新推荐文章于 2024-04-30 21:06:29 发布

库乐

最新推荐文章于 2024-04-30 21:06:29 发布

阅读量191

点赞数

分类专栏： sql 文章标签： sql

原文链接：https://tianchi.aliyun.com/forum/postDetail?spm=5176.20222307.J_9059755190.8.73dc4cb3W5HJtB&postId=167461

版权

sql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

文章目录

理论知识
学习心得

理论知识

4.1表的加减法

4.1.1 什么是集合运算

集合在数学领域表示“各种各样的事物的总和”,
使用集合运算符 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算
在这里插入图片描述

4.1.2 表的加法–UNION

4.1.2.1 UNION

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

在这里插入图片描述
UNION 等集合运算符通常都会除去重复的记录。

4.1.2.3 包含重复行的集合运算 UNION ALL

有时候需要不去重的并集, 在 UNION 的结果中保留重复行的语法，添加 ALL 关键字即可。

-- 保留重复行
SELECT product_id, product_name
  FROM product
 UNION ALL
SELECT product_id, product_name
  FROM product2;

结果：
在这里插入图片描述

4.1.2.5隐式类型转换

通过隐式类型转换来将两个类型不同的列放在一列里显示：

SELECT product_id, product_name, '1'
  FROM product
 UNION
SELECT product_id, product_name,sale_price
  FROM product2;

在这里插入图片描述

4.1.3 MySQL 8.0 不支持交运算INTERSECT

SELECT product_id, product_name
  FROM product
  
INTERSECT
SELECT product_id, product_name
  FROM product2

4.1.5 对称差

两个集合A,B的对称差是指那些仅属于A或仅属于B的元素构成的集合。

4.1.5.1 借助并集和差集迂回实现交集运算 INTERSECT

4.2 连结(JOIN)

在这里插入图片描述
语法格式是:

-- 内连结
FROM <tb_1> INNER JOIN <tb_2> ON <condition(s)>

4.2.1 内连结(INNER JOIN)

4.2.1.1 使用内连结从两个表获取信息

在这里插入图片描述
商品编号列是一个公共列，可以利用这个“桥梁”，将product和shopproduct这两张表连接起来。

如果在A表里每找一行，再到B表里全部找一遍，计算开销会很大的。

分解问题：
找出每个商店的商店编号, 商店名称, 商品编号, 商品名称, 商品类别, 商品售价,商品数量信息.

按照内连结的语法, 在 FROM 子句中使用 INNER JOIN 将两张表连接起来, 并为 ON 子句指定连结条件为 shopproduct.product_id=product.product_id, 就得到了如下的查询语句:

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 JOIN product AS P
    ON SP.product_id = P.product_id;

结果：
在这里插入图片描述

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

4.2.1.2 结合 WHERE 子句使用内连结

第一种：再上一步的基础上，增加 WHERE 语句

SELECT *
  FROM (-- 第一步查询的结果
        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) AS STEP1
 WHERE shop_name = '东京'
   AND product_type = '衣服' ;

子查询的结果是一张“视图”
由于执行顺序:
FROM 子句->WHERE 子句->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 = '衣服' ;

如果要连结多张表, 也可以更改任务顺序，使用 WHERE 进行筛选,然后把上述两个子查询连结起来

第三种：先筛选，后连接

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 *
          FROM shopproduct
         WHERE shop_name = '东京' ) AS SP
 INNER JOIN -- 子查询 2:从 product 表筛选出衣服类商品的信息
   (SELECT *
      FROM product
     WHERE product_type = '衣服') AS P
    ON SP.product_id = P.product_id;

4.2.1.3结合 GROUP BY 子句使用内连结

每个商店中, 售价最高的商品的售价分别是多少?

-- 参考答案
SELECT SP.shop_id
      ,SP.shop_name
      ,MAX(P.sale_price) AS max_price
  FROM shopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id
 GROUP BY SP.shop_id,SP.shop_name

4.2.1.4自连结(SELF JOIN)

同一张表也可以与自身作连结，

4.2.1.5内连结与关联子查询

回顾：找出每个商品种类当中售价高于该类商品的平均售价的商品.可以使用关联子查询来实现的

SELECT product_type, product_name, sale_price
  FROM product AS P1
 WHERE sale_price > (SELECT AVG(sale_price)
                       FROM product AS P2
                      WHERE P1.product_type = P2.product_type
                      GROUP BY product_type);

使用内连结同样可以解决这个问题

-- 查询与表 product 按照 product_type (商品种类)进行内连结
SELECT  P1.product_id
       ,P1.product_name
       ,P1.product_type
       ,P1.sale_price
       ,P2.avg_price
  FROM product AS P1
 INNER JOIN 
   ( --使用 GROUP BY 按商品类别分类计算每类商品的平均价格.
   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;

4.2.1.6自然连结(NATURAL JOIN)

自然连结是内连结的一种特例–当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件

SELECT *  FROM shopproduct NATURAL JOIN product

会把两个表的公共列(这里是 product_id, 可以有多个公共列)放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来.

在这里插入图片描述表 product 和表 product2 中的公共部分, 也可以用自然连结来实现:

由于运动 T 恤的 regist_date 字段为空, 在进行自然连结时, 来自于 product 和 product2 的运动 T 恤这一行数据在进行比较时, 实际上是在逐字段进行等值连结, 回忆我们在 6.2ISNULL,IS NOT NULL 这一节学到的缺失值的比较方法就可得知, 两个缺失值用等号进行比较, 结果不为真. 而连结只会返回对连结条件返回为真的那些行.

如果我们将查询语句进行修改:

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

那就可以得到正确的结果了:

在这里插入图片描述

4.2.1.7使用连结求交集

使用内连结求product 表和product2 表的交集.

SELECT P1.*
  FROM product AS P1
 INNER JOIN product2 AS P2
    ON (P1.product_id  = P2.product_id
   AND P1.product_name = P2.product_name
   AND P1.product_type = P2.product_type
   AND P1.sale_price   = P2.sale_price
   AND P1.regist_date  = P2.regist_date)

得到如下结果
在这里插入图片描述结果少了 product_id='0001’这一行, 观察源表数据可发现, 少的这行数据的 regist_date 为缺失值, 回忆第六章讲到的 IS NULL 谓词, 我们得知, 这是由于缺失值是不能用等号进行比较导致的.

如果我们仅仅用 product_id 来进行连结:

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 子句匹配到的行, 此时对应右表的行均为缺失值; 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值; 而全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充.

三种外连结的对应语法分别为:

-- 左连结     
FROM <tb_1> LEFT  OUTER JOIN <tb_2> ON <condition(s)>
-- 右连结     
FROM <tb_1> RIGHT OUTER JOIN <tb_2> ON <condition(s)>
-- 全外连结
FROM <tb_1> FULL  OUTER JOIN <tb_2> ON <condition(s)>

4.2.2.1 左连结与右链接

统计每种商品分别在哪些商店有售, 需要包括那些在每个商店都没货的商品

4.2.2.2 使用左连结从两个表获取信息

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
  FROM product AS P
  LEFT OUTER JOIN shopproduct AS SP
    ON SP.product_id = P.product_id;

结果：（并未使用 ORDER BY 可能顺序不同）
在这里插入图片描述

外连结要点 1: 选取出单张表中全部的信息
内连结 - 13 条，外连结 - 15 条记录
多出的 2 条记录是高压锅和圆珠笔,多的 2 条记录在shopproduct 表中并不存在,也就是说,这 2 种商品在任何商店中都没有销售。
外连结要点 2:使用 LEFT、RIGHT 来指定主表.
使用 LEFT 时 FROM 子句中写在左侧的表是主表,使用 RIGHT 时右侧的表是主表

4.2.2.3 结合 WHERE 子句使用左连结

用外连结从shopproduct表和product表中找出那些在某个商店库存少于50的商品及对应的商店.希望得到如下结果.
在这里插入图片描述
注意高压锅和圆珠笔两种商品在所有商店都无货, 所以也应该包括在内.
按照"结合WHERE子句使用内连结"的思路, 我们很自然会写出如下代码

SELECT P.product_id
       ,P.product_name
       ,P.sale_price
       ,SP.shop_id
       ,SP.shop_name
       ,SP.quantity
  FROMproduct AS P
  LEFT OUTER JOINshopproduct AS SP
    ON SP.product_id = P.product_id
 WHERE quantity< 50

得到的却是如下的结果:
在这里插入图片描述
在进行完外连结后才会执行WHERE子句, 因此那些主表中无法被匹配到的行就被WHERE条件筛选掉了。所以应把WHERE子句挪到外连结之前进行: 先写个子查询,用来从shopproduct表中筛选quantity<50的商品, 然后再把这个子查询和主表连结起来。

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

在这里插入图片描述

4.2.3多表连结

4.2.3.1 多表进行内连结

创建一个用于三表连结的表 Inventoryproduct.首先我们创建一张用来管理库存商品的表, 假设商品都保存在 P001 和 P002 这 2 个仓库之中.
在这里插入图片描述建表语句如下:

CREATE TABLE Inventoryproduct
( inventory_id       CHAR(4) NOT NULL,
product_id         CHAR(4) NOT NULL,
inventory_quantity INTEGER NOT NULL,
PRIMARY KEY (inventory_id, product_id));

--- DML：插入数据
START TRANSACTION;
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0001', 0);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0002', 120);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0003', 200);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0004', 3);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0005', 0);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0006', 99);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0007', 999);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P001', '0008', 200);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0001', 10);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0002', 25);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0003', 34);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0004', 19);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0005', 99);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0006', 0);
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0007', 0 );
INSERT INTO Inventoryproduct (inventory_id, product_id, inventory_quantity)
VALUES ('P002', '0008', 18);
COMMIT;

根据上表及shopproduct 表和product 表, 使用内连接找出每个商店都有那些商品, 每种商品的库存总量分别是多少.

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

在这里插入图片描述

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 JOINshopproduct AS SP
ON SP.product_id = P.product_id
LEFT OUTER JOIN Inventoryproduct AS IP
ON SP.product_id = IP.product_id

结果：
在这里插入图片描述

4.2.4 ON 子句进阶–非等值连结

4.2.4.1 非等值自左连结(SELF JOIN)

使用非等值自左连结实现排名。
希望对 product 表中的商品按照售价赋予排名. 一个从集合论出发,使用自左连结的思路是, 对每一种商品,找出售价不低于它的所有商品, 然后对售价不低于它的商品使用 COUNT 函数计数. 例如, 对于价格最高的商品,

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

注 1: COUNT 函数的参数是列名时, 会忽略该列中的缺失值, 参数为 * 时则不忽略缺失值.
注 2: 上述排名方案存在一些问题–如果两个商品的价格相等, 则会导致两个商品的排名错误, 例如, 叉子和打孔器的排名应该都是第六, 但上述查询导致二者排名都是第七. 试修改上述查询使得二者的排名均为第六.

在这里插入图片描述

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

在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积
进行交叉连结时无法使用内连结和外连结中所使用的ON 子句,这是因为交叉连结是对两张表中的全部记录进行交叉组合,因此结果中的记录数通常是两张表中行数的乘积.本例中,因为shopproduct 表存在 13 条记录,product 表存在 8 条记录,所以结果中就包含了 13 × 8 = 104 条记录.

-- 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.2.6 连结的特定语法和过时语法

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

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
 WHERE SP.product_id = P.product_id;

学习心得

WHERE 和 JOIN 有很多类似的处理，实际发现子查询要比连洁要慢一点点。

库乐

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
[阿里云天池龙珠计划] SQL训练营-Task04-集合运算-表的加减法和join等

文章目录4.1表的加减法4.1.1 什么是集合运算4.1.2 表的加法–UNION4.1.2.1 UNION4.1.2.3 包含重复行的集合运算 UNION ALL4.1.2.5隐式类型转换4.1.3 MySQL 8.0 不支持交运算INTERSECT4.1.5 对称差4.1.5.1 借助并集和差集迂回实现交集运算 INTERSECT4.2 连结(JOIN)4.2.1 内连结(INNER JOIN)4.2.1.1 使用内连结从两个表获取信息4.2.1.2 结合 WHERE 子句使用内连结4.2.1.3结合
复制链接

扫一扫