天池SQL训练营（四）—— 集合运算-表的加减法和join等

最新推荐文章于 2024-06-23 10:04:55 发布

雨雨雨_杰

最新推荐文章于 2024-06-23 10:04:55 发布

阅读量256

点赞数

分类专栏：天池学习文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/deep_learner_/article/details/126715897

版权

天池学习专栏收录该内容

7 篇文章 0 订阅

订阅专栏

引言

本节首先介绍了表的几种加减法运算，分别有求并集、求交集、求差集、补集和对称差。
之后介绍了表的连结，表连结类似于两表根据相同的列拼接在一起，分为内连结和外连结

文本连结

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

集合运算

在这里插入图片描述

表的加法 UNION

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

UNION 等集合运算符通常都会除去重复的记录

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

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

bag 模型与 set 模型

区别在于：bag 里面允许存在重复元素, 如果同一个元素被加入多次, 则袋子里就有多个该元素，set 里面各个元素是互异的

交集：MySQL 8.0 不支持交运算INTERSECT

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

8.0还不支持

差集 MySQL 8.0 还不支持 EXCEPT 运算

可以借用Not IN 谓词

-- 使用 IN 子句的实现方法
SELECT * 
  FROM product
 WHERE product_id NOT IN (SELECT product_id 
                            FROM product2)

对称差

两个集合的对称差等于 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)

连结

在这里插入图片描述

内连结

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

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 子句中的列最好按照表名.列名的格式来使用.

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

第二种： 实际上, 如果我们熟知 WHERE 子句将在 FROM 子句之后执行，也就是说, 在做完 INNER JOIN … ON 得到一个新表后, 才会执行 WHERE 子句, 那么就得到标准的写法

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 子句中的条件直接添加在 ON 子句中, 这时候 ON 子句后最好用括号将连结条件和筛选条件括起来

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
   AND SP.shop_name = '东京'
   AND P.product_type = '衣服') ;

但上述这种把筛选条件和连结条件都放在 ON 子句的写法, 不是太容易阅读, 不建议大家使用.
第四种： 先分别在两个表使用 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 *
          FROMshopproduct
         WHERE shop_name = '东京' ) AS SP
 INNER JOIN -- 子查询 2:从 product 表筛选出衣服类商品的信息
   (SELECT *
      FROMproduct
     WHERE product_type = '衣服') AS P
    ON SP.product_id = P.product_id;

结合 GROUP BY 子句使用内连结

如：求每个商店中, 售价最高的商品的售价

SELECT SP.shop_id
      ,SP.shop_name
      ,MAX(P.sale_price) AS max_price
  FROMshopproduct AS SP
 INNER JOINproduct AS P
    ON SP.product_id = P.product_id
 GROUP BY SP.shop_id,SP.shop_name

自连结

所谓自连结，就是连结的两个表为同一个表，就叫自连结
例子：找出每个商品种类当中售价高于该类商品的平均售价的商品

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;

自然连结(NATURAL JOIN)

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

外连结

内连结会丢弃两张表中不满足 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)>

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;

要点 1: 选取出单张表中全部的信息
要点 2: 使用 LEFT、RIGHT 来指定主表.
例子：使用外连结从shopproduct表和product表中找出那些在某个商店库存少于50的商品及对应的商店（并且包括没货的商品 NULL）
在这里插入图片描述

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

得到如下
在这里插入图片描述
观察发现,返回结果缺少了在所有商店都无货的高压锅和圆珠笔。因为NULL不能用 quantity判断，可以加入OR quantity IS NULL

然而在实际环境中，由于数据量大且数据质量并非像我们设想的那样"干净"，我们并不能容易地意识到缺失值等问题数据的存在，因此，还是让我们想一下如何改写我们的查询以使得它能够适应更复杂的真实数据的情形吧。

先写个子查询,用来从shopproduct表中筛选quantity<50的商品, 然后再把这个子查询和主表连结起来。

SELECT P.product_id
      ,P.product_name
      ,P.sale_price
       ,SP.shop_id
      ,SP.shop_name
      ,SP.quantity 
  FROMproduct AS P
  LEFT OUTER JOIN-- 先筛选quantity<50的商品
   (SELECT *
      FROMshopproduct
     WHERE quantity < 50 ) AS SP
    ON SP.product_id = P.product_id

在这里插入图片描述

在 MySQL 中实现全外连结

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

多表连结

SELECT SP.shop_id
       ,SP.shop_name
       ,SP.product_id
       ,P.product_name
       ,P.sale_price
       ,IP.inventory_quantity
  FROMshopproduct AS SP
 INNER JOINproduct 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';

多表进行外连接

SELECT P.product_id
       ,P.product_name
       ,P.sale_price
       ,SP.shop_id
       ,SP.shop_name
       ,IP.inventory_quantity
  FROMproduct 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

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

两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合

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