表的加减法

集合运算

表，视图，查询的执行结果都是记录的集合，其中的元素为表或者查询结果中的每一行
集合运算：UNION, INTERSECT,EXCEPT（并，交，差）
在MYSQL中的实现有所不同：

  • 并—UNION
  • 差—WHERE NOT IN
  • 交—并集-对称差

表的加法UNION

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

USE shop;
#交集UNION	
SELECT product_id,product_name FROM product
UNION
SELECT product_id,product_name FROM product2;

使⽤ UNION 对两个查询结果取并集 和在⼀个查询中使⽤ WHERE ⼦句, 然后使⽤ OR 谓词连接两个查询条件, 能够得到相同的结果但倘若要将两个不同的表中的结果合并在⼀起, 就不得不使⽤ UNION 了.⽽且, 即便是对于同⼀张表, 有时也会出于查询效率⽅⾯的因素来使⽤ UNION.
练习题：

#练习题
SELECT * 
FROM product
WHERE sale_price > 500
UNION
SELECT *
FROM product2
WHERE sale_price > 500;

MYSQL中有交集吗 mysql的并集_IP


保留重复行的并集UNION ALL

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

练习题： Product 和 Product2 中所包含的商品种类及每种商品的数量

select p.product_type,COUNT(*)
from
(select	product_type from product
UNION ALL
select product_type from product2) AS p
GROUP BY p.product_type;

MYSQL中有交集吗 mysql的并集_mysql_02


MYSQL中有交集吗 mysql的并集_mysql_03

隐式类型转换

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

#隐式类型转换--转化成了字符串类型
SELECT product_id,product_name,'1'
FROM product
UNION
SELECT product_id,product_name,sale_price
FROM product2;

#SYSDATE()
SELECT SYSDATE(),SYSDATE(),SYSDATE()
UNION
SELECT 'ASDSA',123,NULL;

MYSQL中有交集吗 mysql的并集_mysql_04


MYSQL中有交集吗 mysql的并集_MYSQL中有交集吗_05


!!!MySQL 8.0 不⽀持交运算 INTERSECT,可以用AND实现类似功能

MYSQL中有交集吗 mysql的并集_mysql_06


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

差集，补集与表的减法

A-B = A-A∩B
!!!MySQL 8.0 还不⽀持 表的减法运算符 EXCEPT,但是可以用NOT IN 实现类似的功能（在A不在B中）。
找出只存在于 product 表但不存在于 product2 表的商品.

#not in 实现差集
SELECT *
FROM product
WHERE product.product_id NOT IN (SELECT product_id 
									FROM product2);
#求出product表中, 售价高于2000，且利润不低于30%的商品							
SELECT *
FROM product
WHERE sale_price >2000
	AND product_id NOT IN (SELECT product_id 
							FROM product
							WHERE sale_price/purchase_price < 1.3);

MYSQL中有交集吗 mysql的并集_并集_07

对称差

两个集合 A,B 的对称差是指那些仅属于 A 或仅属于 B 的元素构成的集合. 对称差也是个⾮常基础的运算, 例如, 两个集合的交就可以看作是两个集合的并去掉两个集合的对称差.
并集-交集=对称差
在Mysql中可用，(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);

实现INTERSECT交集的功能：
并集（UNION）-对称差(NOT IN UNION) = 交集
-：用NOT IN 实现
练习题

#创建对称差视图
CREATE VIEW view_duichengcha
AS
SELECT product_id
FROM product
WHERE product_id NOT IN (SELECT product_id FROM product2)
UNION
SELECT product_id
FROM product2
WHERE product_id NOT IN (SELECT product_id FROM product);

SELECT * FROM view_duichengcha;

#实现求product和product2的交集
SELECT *
FROM product P1
WHERE P1.product_id NOT IN  (SELECT * FROM view_duichengcha)
UNION 
SELECT *
FROM product2 P2
WHERE P2.product_id NOT IN  (SELECT * FROM view_duichengcha);

MYSQL中有交集吗 mysql的并集_mysql_08

连结（JOIN）

从多个表中获取数据，连结( JOIN)就是使⽤某种关联条件(⼀般是使⽤相等判断谓词"="), 将其他表中的列添加过来,进⾏“添加列”的集合运算. 可以说, 连结是 SQL 查询的核⼼操作, 掌握了连结, 能够从两张甚⾄多张表中获取列, 能够将过去使⽤关联⼦查询等过于复杂的查询简化为更加易读的形式, 以及进⾏⼀些更加复杂的查询.

MYSQL中有交集吗 mysql的并集_mysql_09


SQL 中的连结有多种分类方法, 我们这里使用最基础的内连结和外连结的分类方法来分别进行讲解.

内连结

FROM < table1 > INNER JOIN < table2 > ON < conditions >

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;

MYSQL中有交集吗 mysql的并集_MYSQL中有交集吗_10

内连结的使用要点

  • FROM子句要含有多张表（FROM INNER JOIN ON）
  • 使用ON子句来指定连结条件
  • SELECT中的列 用 表名.列名 的形式来表示
    结合WHERE子句使用内连结

—如果要用WHERE则应该放在ON子句后面， 两张表是先按照连结列进行了连结, 得到了一张新表, 然后 WHERE 子句对这张新表的行按照两个条件进行了筛选, 最后, SELECT 子句选出了那些我们需要的列。

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

练习：找出每个商店⾥的⾐服类商品的名称及价格等信息

SELECT   SP.shop_id
		,SP.shop_name
		,SP.product_id
		,P.product_name
		,P.product_type
		,P.purchase_price
FROM shopproduct AS SP
INNER JOIN product AS P
ON SP.product_id = P.product_id
WHERE P.product_type = '衣服' ;

MYSQL中有交集吗 mysql的并集_mysql_11

练习题：找出东京商店⾥, 售价低于 2000 的商品信息

SELECT   SP.shop_id
		,SP.shop_name
		,SP.product_id
		,P.product_name
		,P.product_type
		,P.sale_price
		,SP.quantity
FROM (SELECT * FROM shopproduct  WHERE shop_id = '000A') AS SP
INNER JOIN (SELECT * FROM product  WHERE sale_price < 2000) AS P
ON SP.product_id = P.product_id;

MYSQL中有交集吗 mysql的并集_sql_12

结合GROUP BY 子句使用内连结

结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待.最简单的情形, 是在内连结之前（子查询）就使用 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;

MYSQL中有交集吗 mysql的并集_并集_13


练习题：每类商品中售价最高的商品在那些商店有售？

SELECT sp.shop_id,sp.shop_name,p.product_id,p.product_name,p.product_type
FROM (SELECT product_type,product_id,product_name,Max(sale_price) AS sale_price
		FROM product
		GROUP BY product_type) AS p
INNER JOIN shopproduct AS sp
ON sp.product_id = p.product_id;

MYSQL中有交集吗 mysql的并集_MYSQL中有交集吗_14

自连结（SELF JOIN）

一张表自己与自己进行连结

SELECT  product_type
       ,AVG(sale_price) AS avg_price 
  FROM product 
 GROUP BY product_type;

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;

练习题：内连接和关联子查询实现每一类商品中售价最高的商品
（有问题~~~~~）

#内连结
SELECT *
FROM product AS p1
INNER JOIN (SELECT product_id,Max(sale_price) AS sale_price
			FROM product
			GROUP BY product_type) AS p2
ON p1.product_id = p2.product_id
#关联子查询
SELECT *
FROM product AS p1
WHERE p1.sale_price =(SELECT Max(sale_price) AS sale_price
						FROM product AS p2
						WHERE p1.product_type =  p2.product_type
						GROUP BY product_type);

自然连结（NATURAL JOIN）

当两个表进行自然连结时, 会按照两个表中都包含的列名来进行等值内连结, 此时无需使用 ON 来指定连接条件.查询得到的结果, 会把两个表的公共列(这里是 product_id, 可以有多个公共列)放在第一列, 然后按照两个表的顺序和表中列的顺序, 将两个表中的其他列都罗列出来
使用自然连结还可以求出两张表或子查询的公共部分（类似取 交集）
内连结只能选取出同时存在于两张表中的数据
-----：使用连结求 product 表和 product2 表的交集

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

外连结（OUTER JOIN）

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

按照保留的行位于哪张表,外连结有三种形式: 左连结, 右连结和全外连结.

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

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

FROM <TABLE1> LEFT OUTER JOIN <TABLE2> ON <CONDITIONS>
FROM <TANLE1> RIGHT OUTER JOIN <TABLE2> ON <CONDITIONS>
FROM <TABLE1> FULL OUTER JOIN <TABLE2> ON <CONDITIONS>

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

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

结合WHERE子句的左连结

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

MYSQL中有交集吗 mysql的并集_mysql_15


多表连结

#多表内连接
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

ON 子句进阶—非等值连结

交叉连结（CROSS JOIN）-笛卡尔积

在连结去掉 ON 子句, 就是所谓的交叉连结(CROSS JOIN), 交叉连结又叫笛卡尔积, 后者是一个数学术语. 两个集合做笛卡尔积, 就是使用集合 A 中的每一个元素与集合 B 中的每一个元素组成一个有序的组合

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

未完待续……

DataWhale:SQL编程语言