运行环境: postgreSQL。

第七章:集合运算

什么是集合运算

集合在数学领域表示“ (各 种各样的)事物的总和”,在数据库领域表示记录的集合。所谓的集合运算,就是对满足同一规则的记录进行的加减等四则运算。通过集合运算,可以得到两张表中记录的集合或者公共记录的集合,又或者其中 某张表中的记录的集合,用来进行集合运算的运算符被称为集合运算符

集合运算的注意事项

作为运算对象的记录的列数必须相同

像如下 SQL 语句中进行加法运算的列记录数不同,则会发生错误:

1
2
3
4
5
SELECT product_id, product_name
	FROM Product 
UNION 
SELECT product_id, product_name, sale_price
	FROM Product2;

作为运算对象的记录中的列类型必须一致

从左侧开始,相同位置的列类型必须一致,否则报错,当然可以通过CAST类型转换函数将类型转为一致:

1
2
3
4
5
6
-- 数据类型不一致时会发生错误 
SELECT product_id, sale_price
	FROM Product 
UNION 
SELECT product_id, regist_date
	FROM Product2;

可以使用任何 SELECT 语句,但 ORDER BY 子句只能在最后使用一次

1
2
3
4
5
6
SELECT product_id, product_name
	FROM Product WHERE product_type = '厨房用具'
UNION 
SELECT product_id, product_name
	FROM Product2 WHERE product_type = '厨房用具' 
 ORDER BY product_id;

表的加法 — UNION(并集)

创建表 Product2:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
-- DDL
CREATE TABLE Product2
(prodcut_id				CHAR(4)				NOT NULL,
 product_name			VARCHAR(100)	NOT NULL,
 product_type			VARCHAR(32)		NOT NULL,
 sale_price				INTEGER				,
 purchase_price		INTEGER				,
 regist_date			DATE					,
 PRIMARY KEY(product_id));
 
-- 插入数据
BEGIN TRANSACTION; 
INSERT INTO Product2 VALUES('0001', 'T 恤衫' ,'衣服', 1000, 500, '2008-09-20'); 
INSERT INTO Product2 VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'); 
INSERT INTO Product2 VALUES('0003', '运动 T 恤', '衣服', 4000, 2800, NULL); 
INSERT INTO Product2 VALUES('0009', '手套', '衣服', 800, 500, NULL); 
INSERT INTO Product2 VALUES('0010', '水壶', '厨房用具', 2000, 1700, '2009-09-20'); 
COMMIT;

示例,对表 Product 和表 Product2 进行加法运算:

1
2
3
4
5
SELECT product_id, product_name
	FROM Product 
UNION
SELECT product_id, product_name
	FROM Product2;

UNION 运算符会去除重复的记录。

image-20191205124702830

当需要包含重复行的集合运算时,使用ALL选项:

1
2
3
4
5
SELECT product_id, product_name
	FROM Product 
UNION ALL
SELECT product_id, product_name
	FROM Product2;

选取表中公共部分 — INTERSECT(交集)

MYSQL 暂不支持 INTERSECT。

示例,使用 INTERSECT 选取两表记录的公共部分:

1
2
3
4
5
6
SELECT product_id, product_name
	FROM Product
INTERSECT
SELECT product_id, product_name
	FROM Prodcut2
ORDER BY product_id;

截屏2019-12-05下午1.01.20

同样的,可以通过 INTERSECT ALL 保留重复行。

记录的减法 — EXCEPT(差集)

Oracle 使用其特有的 MINUS 实现差集,MYSQL 暂不支持 EXCEPT。

示例,使用 EXCEPT 对记录进行减法运算:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
SELECT product_id, product_name
	FROM Product
EXCEPT
SELECT product_id, product_name
	FROM Product2
 ORDER BY product_id;

-- Oracle
SELECT product_id, product_name
	FROM Product
MINUS
SELECT product_id, product_name
	FROM Product2
 ORDER BY product_id; 

image-20191205130818246

EXCEPT 和 UNION 及 INTERSECT 相比有一点要注意,即在减法运算中减数和被减数的位置区别,当把 Product 和 Product2 位置互换,则是以下结果:

截屏2019-12-05下午1.10.20

联结(以列为单位对表进行联结)

前面的 UNION 和 INTERSECT 等运算的运算特征都是以行方向为单位进行操作,即进行运算时,会造成记录行数的增减,但不会导致列数的改变。

联结(JOIN)简单来说,就是将其他表中的列添加过来,进行“添加列”的运算。该操作通常用于无法从一张表中获取期望数据(列)的情况。

内联结 — INNER JOIN

回顾表 Product 和表 ShopProduct 两张表包含的列:

截屏2019-12-05下午1.29.51

可以分为以下两类:

  • A: 两张表中都包含的列 — 商品编号
  • B: 只存在一站表内的列 — 商品之外的列

联结即“将 A 中的列作为桥梁,将 B 中满足同样条件的列汇总到同意结果中”。

示例,从表 Product 选醋 product_name 和 sale_price 和表 ShopProduct 进行结合:

1
2
3
SELECT SP.shop_id, SP.shop_name, SP.product_id, p.product_name, p.sale_price
	FROM shopproduct AS SP INNER JOIN Product AS P
		ON SP.product_id = P.product_id;

内联结要点:

  • FROM 子句包含两张表,使用 INNER JOIN 将两张表联结在一起,SELECT 中也可以直接使用表名,但表明太长影响可读性,因此建议使用别名。
  • ON 为联结条件,其指定两张表联结所使用的列(联结键),当需要使用多个键时,同样可以使用 AND, OR,ON 必须写在 FROM 和 WHERE 中间。

  • SELECT 子句,从语法上来说,只有那些同时出现在两张表的列才必须使用<表的别名>.<列名>这样的形式指定列,但为了避免混乱及提高 SQL 的可读性,依然建议使用该形式书写 SELECT 子句中全部的列。

  • 结合 WHERE 子句,示例,当只想知道东京店(000A)的信息时:

    1
    2
    3
    4
    
    SELECT SP.shop_id, SP.shop_name, SP.product_id, p.product_name, p.sale_price
    	FROM shopproduct AS SP INNER JOIN Product AS P
    		ON SP.product_id = P.product_id
    	WHERE SP.shop_id = '000A';

外联结 — OUTER JOIN

1
2
3
SELECT SP.shop_id, SP.shop_name, SP.product_id, p.product_name, p.sale_price
	FROM shopproduct AS SP RIGHT OUTER JOIN Product AS P
		ON SP.product_id = P.product_id;

外联结要点

  • 外联结关键点在于只要数据存在于某一张表当中,就能够被读出来,在实际业务中,例如想要生成固定行数的单据时,外联结就可以派上用场了,而内联结结果的行数可能会发生改变,因此不适用。
  • 每张表都是主表吗?指定主表的关键字是 LEFT 和 RIGHT 。因此上述外联结的主表是 Product 。

3张以上的表联结

创建管理库存商品的表 InventoryProduct:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- DDL
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));
 
-- 插入数据
BEGIN 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;

示例,从表 InventoryProduct 取出 P001 仓库中的商品数量,并将该列添加到上述内联结得到的结果中,联结方式为内联结,联结键为商品编号(product_id):

1
2
3
4
5
6
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';		

交叉联结 — CROSS JOIN

将表 Product 和表 ShopProduct 进行联结:

1
2
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
	FROM ShopProduct AS SP CROSS JOIN Product AS P;

对满足相同规则的表进行交叉联结的集合运算符是 CROSS JOIN(笛卡儿积) 。进行交叉联结时无法使用 ON 子句,因为交叉联结是对表中全部记录进行交叉结合,因此上述的 SQL 语句结果包含的记录是 ShopProduct(13 条记录) x Product(8 条记录) = 104 条记录。