开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> datawhale9月组队学习task04集合运算 -> 正文阅读

[大数据]datawhale9月组队学习task04集合运算

datawhale9月组队学习task04集合运算

一.表的加减法

1.何为集合运算

之前学的表、视图还有查询的执行结果都是集合
- 其中的元素就是记录，就是表或者查询结果的每一行
集合运算
- 集合运算符
  - UNION 并运算
  - INTERSECT 交运算
  - EXCEPT 差运算
  - (但是MySQL 8.0不支持INTERSECT和EXCEPT)
- 在解决问题时，就可以把表当成集合进行集合运算

2.表的加法 UNION

互异性是集合三要素之一
- 那么合并两个表肯定有去重功能咯
我们来试试将product 和 product2 的product_id、product_name给合并一下
- 首先看看是各自是啥样
- 根据基本的数学知识，就能预测UNION之后是啥样，来测试一下
```
SELECT product_id,product_name
FROM product
UNION
SELECT product_id,product_name
FROM product2;
```
  预料之中🤭
  
  “ 0001 ”~“ 0003 ”的 3 条记录在两个表中都存在，但UNION之后并不会重复出现，这就起到了

上面是对两张表求并集，其实对一张表也可以，怎么理解呢？

是一些条件查询的时候可以把满足A条件或满足B 条件的记录合并到一起就变成了满足A或B的记录

比如这个问题

在没有学习集合运算的时候，我们会用OR

SELECT product_id,product_name,product_id, product_name, product_type,
       sale_price, purchase_price
FROM product
WHERE sale_price < 800
   OR sale_price > 1.5*purchase_price;

现在就可以用UNION啦！

SELECT product_id,product_name,product_id, product_name, product_type,
       sale_price, purchase_price
FROM product
WHERE sale_price < 800

UNION
SELECT product_id,product_name,product_id, product_name, product_type,
       sale_price, purchase_price
FROM product
WHERE sale_price > 1.5*purchase_price;

结果都是一样滴！

UNION与OR谓词

上面结果一样是不是就说明UNION是多余的呢？你看用UNION写sql语句还要多写两行
- 非也！
  - 对于两张表，要用UNION
  - 即使对一张表，有时也会出于查询效率方面的因素来使用 UNION

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

虽然说集合并运算一般都是去重，但是有时候我们不希望去重
- 这时候就要用到UNION ALL了
```
SELECT product_id,product_name
FROM  product
UNION ALL
SELECT product_id,product_name
FROM product2;
```

隐式数据类型转换

通常来说, 我们会把类型完全一致, 并且代表相同属性的列使用 UNION 合并到一起显示, 但有时候, 即使数据类型不完全相同, 也会通过隐式类型转换来将两个类型不同的列放在一列里显示, 例如字符串和数值类型

3.交运算 INEER JOIN

(MySQL 8.0 不支持INTERSECT)

SELECT p1.product_id, p1.product_name
  FROM Product p1
INNER JOIN Product2 p2
ON p1.product_id=p2.product_id

上面是省略了AS

最好不要省略：

SELECT p1.product_id,p1.product_name
FROM product AS p1
INNER JOIN product2 AS p2
ON p1.product_id=p2.product_id

4.差集、补集，表的减法

NOT 谓词

MySQL 8.0 还不支持 EXCEPT 运算

但是NOT IN 也可以实现和SQL标准语法中的EXCEPT运算相同的效果

找出只存在于Product表但不存在于Product2表的商品

SELECT *
FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2);

使用NOT谓词进行集合的减法运算, 求出Product表中, 售价高于2000,但利润低于30%的商品

SELECT *
FROM product
WHERE sale_price > 2000
AND product_id NOT IN (SELECT product_id FROM product WHERE sale_price < 1.3*purchase_price);

AND 谓词

对于同一个表的两个查询结果而言, 他们的交INTERSECT实际上可以等价地将两个查询的检索条件用AND谓词连接来实现

查找product表中利润率高于50%,并且售价低于1500的商品

SELECT *
FROM product
WHERE sale_price > 1.5*purchase_price
AND sale_price < 1500

5.对称差

在其他数据库里也可以用来简单地实现表或查询结果的对称差运算: 首先使用UNION求两个表的并集, 然后使用INTERSECT求两个表的交集, 然后用并集减去交集, 就得到了对称差
但由于在MySQL 8.0 里, 由于两个表或查询结果的并不能直接求出来, 因此并不适合使用上述思路来求对称差
所以可以就用A-B并B-A

例

使用Product表和Product2表的对称差来查询哪些商品只在其中一张表

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)就是使用某种关联条件(一般是使用相等判断谓词"="), 将其他表中的列添加过来, 进行“添加列”的集合运算

1.内连结( INNER JOIN )

语法格式

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

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

先来看看product表
```
SELECT * FROM product;
```
再来看看shopproduct表
```
SELECT *
FROM shopproduct;
```
两张表有不同的信息列，我们想把他们放到一起分析，那就要连结啦！

操作

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子句来指定连结条件
SELECT 子句中的列最好按照表名.列名的格式来使用
- 这样能看出很快看出哪一列来自哪一张表
- 如果两张表有其他名称相同的列, 必须使用上述格式来选择列名, 否则查询语句会报错

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

注意上述查询的执行顺序

结合 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

自连结

之前的内连结, 连结的都是不一样的两个表。但实际上一张表也可以与自身作连结, 这种连接称之为自连结. 需要注意, 自连结并不是区分于内连结和外连结的第三种连结, 自连结可以是外连结也可以是内连结, 它是不同于内连结外连结的另一个连结的分类方法

内连结与关联子查询

找出售价高于该类商品平均价格的商品

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;

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

自然连结(NATURAL JOIN)

SELECT *  FROM shopproduct NATURAL JOIN product;

使用连结求交集

SELECT *
FROM product AS P1
INNER JOIN product2 AS P2
ON P1.product_id = P2.product_id;

2.外连结

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

按照保留的行位于哪张表,外连结有三种形式
- 左连结
- 左连结会保存左表中无法按照 ON 子句匹配到的行, 此时对应右表的行均为缺失值
- 右连结
- 右连结则会保存右表中无法按照 ON 子句匹配到的行, 此时对应左表的行均为缺失值
- 外连结
- 全外连结则会同时保存两个表中无法按照 ON子句匹配到的行, 相应的另一张表中的行用缺失值填充
  
  ? 遗憾的是, MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结

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

-- 左连结     
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)>

3.多表连结

多表内连结

使用内连接找出每个商店都有那些商品, 每种商品的库存总量分别

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

多表外连结

外连结一般能比内连结有更多的行, 从而能够比内连结给出更多关于主表的信息, 多表连结的时候使用外连结也有同样的作用

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