文章目录
理论知识
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 有很多类似的处理,实际发现子查询要比连洁要慢一点点。