学习.png
现在我们开始学习使用2张以上的表的SQL语句。通过以行方向为单位的集合运算符和以列方向为单位的联结，就可以将分散在多张表中的数据组合成期望的结果。
表的加减法
什么是集合运算
集合在数据库中表示为记录的集合。具体来说，表、视图和查询的执行结果都是记录的集合。
集合运算就是对满足同一规则的记录进行的加减等四则运算。
用来进行集合运算的运算符称为集合运算符。
表的加法—UNION
UNION（并集）是进行记录加法运算的集合运算符。在学习使用方法之前，我们先创建一张表：
--创建表Product2(商品2)
CREATE TABLE Product2
( product_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));
将数据插入到表Product2中
--将数据插入到表Product2中
BEGIN TRANSACTION;BEGIN
INSERT INTO Product2 VALUES ('0001', 'T衫', '衣服', 1000, 500, '2017-09-20');INSERT 0 1
INSERT INTO Product2 VALUES ('0002', '打孔器', '办公用品', 500, 320, '2017-09-11');INSERT 0 1
INSERT INTO Product2 VALUES ('0003', '运动T衫', '衣服', 4000, 2800, NULL);INSERT 0 1
INSERT INTO Product2 VALUES ('0009', '手套', '衣服', 800,500, NULL);INSERT 0 1
INSERT INTO Product2 VALUES ('0010', '水壶', '厨房用具', 2000, 1700, '2017-09-20');INSERT 0 1
COMMIT;COMMIT
确认一下我们创建的表：
SELECT * FROM Product2;
执行结果：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
0001 | T衫 | 衣服 | 1000 | 500 | 2017-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2017-09-11
0003 | 运动T衫 | 衣服 | 4000 | 2800 |
0009 | 手套 | 衣服 | 800 | 500 |
0010 | 水壶 | 厨房用具 | 2000 | 1700 | 2017-09-20
(5 行记录)
现在我们的准备工作已经做好，接下来，我们就对表Product和表Product2进行加法运算吧。
--使用UNION对表进行加法运算
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;
执行结果：
product_id | product_name------------+--------------
0009 | 手套
0004 | 菜刀
0002 | 打孔器
0001 | T衫
0003 | 运动T衫
0010 | 水壶
0006 | 叉子
0005 | 高压锅
0007 | 擦菜板
0008 | 圆珠笔
(10 行记录)
如上所示，执行结果包含了两张表的全部商品，UNION就类似于数学里面的并集运算。
注释：集合运算符会除去重复的记录。
集合运算的注意事项
注意事项1—作为运算对象的记录的列数必须相同
例如，向下面这样，一部分包含2列，另一部分包含3列，是无法进行运算的：
--列数不一致时会发生错误
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name, sale_price
FROM Product2;
错误提示：
错误: 每一个 UNION 查询必须有相同的字段个数
第4行SELECT product_id, product_name, sale_price
注意事项2—作为运算对象的记录中列的数据类型必须一致
从左侧开始，相同位置上的列必须为同一数据类型，否则会出错：
--数据类型不一致时会发生错误
SELECT product_id, sale_price
FROM Product
UNION
SELECT product_id, regist_date
FROM Product2;
错误提示：
错误: UNION 的类型 integer 和 date 不匹配
第4行SELECT product_id, regist_date
注意事项3—可以使用任意SELECT语句，但ORDER BY子句只能在最后使用一次
--ORDER BY子句只能在最后使用一次
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;
执行结果：
product_id | product_name------------+--------------
0004 | 菜刀
0005 | 高压锅
0006 | 叉子
0007 | 擦菜板
0010 | 水壶
(5 行记录)
包含重复行的集合运算—ALL选项
只需要在UNION后面添加关键字ALL就可以在运算结果中保留重复行：
--保留重复行
SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;
执行结果：
product_id | product_name------------+--------------
0001 | T衫
0002 | 打孔器
0003 | 运动T衫
0004 | 菜刀
0005 | 高压锅
0006 | 叉子
0007 | 擦菜板
0008 | 圆珠笔
0001 | T衫
0002 | 打孔器
0003 | 运动T衫
0009 | 手套
0010 | 水壶
(13 行记录)
选取表的公共部分—INTERSECT
INTERSECT（交集）可以选取两个记录集合中的公共部分，其语法和UNION相同：
--使用INTERSECT选取出表中的公共部分
SELECT product_id, product_name
FROM Product
INTERSECT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;
执行结果：
product_id | product_name------------+--------------
0001 | T衫
0002 | 打孔器
0003 | 运动T衫
(3 行记录)
如上所示，结果只包含两张表的公共部分，INTERSECT也就类似于数学里面的交集运算。
记录的减法—EXCEPT
EXCEPT（差集）是进行减法运算的集合运算符，其语法和UNION相同：
--使用EXCEPT对记录进行减法运算
SELECT product_id, product_name
FROM Product
EXCEPT
SELECT product_id, product_name
FROM Product2
ORDER BY product_id;
执行结果：
product_id | product_name------------+--------------
0004 | 菜刀
0005 | 高压锅
0006 | 叉子
0007 | 擦菜板
0008 | 圆珠笔
(5 行记录)
如上所示，结果中只包含了表Product中记录除去表Product2中记录之后的剩余部分。
EXCEPT有一点与UNION和INTERSECT不同，那就是在减法运算中减数与被减数的位置不同，其结果也会不同：
--被减数与减数的位置不同，结果也不同
--从Product2表的记录中除去Product表中的记录
SELECT product_id, product_name
FROM Product2
EXCEPT
SELECT product_id, product_name
FROM Product
ORDER BY product_id;
执行结果：
product_id | product_name------------+--------------
0009 | 手套
0010 | 水壶
(2 行记录)
如上所示，结果中只包含了表Product2中记录除去表Product中记录之后的剩余部分。
联结（以列为单位对表进行联结）
联结（JOIN）就是将其他表中的列添加过来，进行“添加列”的集合运算。
UNION是以行为单位进行操作，而联结则是对列为单位进行操作。
联结大体上分为内联结和外联结两种。
什么是联结
联结（JOIN）运算，简单来说，就是将其他表中的列添加过来，进行“添加列”的运算。
SQL中的联结有很多种，我们主要学习内联结和外联结两种。
内联结—INNER JOIN
内联结（INNER JOIN）是运用最广泛的联结运算。
我们继续使用Product表和ShopProduct表来进行后续的学习。我们先来温习一下两张表的内容：
Product(商品)表：
product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
0001 | T衫 | 衣服 | 1000 | 500 | 2017-09-20
0002 | 打孔器 | 办公用品 | 500 | 320 | 2017-09-11
0003 | 运动T衫 | 衣服 | 4000 | 2800 |
0004 | 菜刀 | 厨房用具 | 3000 | 2800 | 2017-09-20
0005 | 高压锅 | 厨房用具 | 6800 | 5000 | 2017-01-15
0006 | 叉子 | 厨房用具 | 500 | | 2017-09-20
0007 | 擦菜板 | 厨房用具 | 880 | 790 | 2016-04-28
0008 | 圆珠笔 | 办公用品 | 100 | | 2017-11-11
(8 行记录)
ShopProduct(商店商品)表：
shop_id | shop_name | product_id | quantity---------+-----------+------------+----------
000A | 成华区 | 0001 | 30
000A | 成华区 | 0002 | 50
000A | 成华区 | 0003 | 15
000B | 金牛区 | 0002 | 30
000B | 金牛区 | 0003 | 120
000B | 金牛区 | 0004 | 20
000B | 金牛区 | 0006 | 10
000B | 金牛区 | 0007 | 40
000C | 武侯区 | 0003 | 20
000C | 武侯区 | 0004 | 50
000C | 武侯区 | 0006 | 90
000C | 武侯区 | 0007 | 70
000D | 锦江区 | 0001 | 100
(13 行记录)
如上所示，两张表都包含的列是：商品编号（product_id）
其他的列都是只存在一张表中。
所谓联结运算，就是以两张表都包含的列（如上面的商品编号列）作为桥梁，将其他满足同样条件的列汇集到同一结果中
下面我们就试着从Product表中取出商品名称（product_name）和销售单价（sale_price），并与ShopProduct表中的内容进行结合：
--将表Product和表ShopProduct进行内联结
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;
执行结果：
shop_id | shop_name | product_id | product_name | sale_price---------+-----------+------------+--------------+------------
000D | 锦江区 | 0001 | T衫 | 1000
000A | 成华区 | 0001 | T衫 | 1000
000B | 金牛区 | 0002 | 打孔器 | 500
000A | 成华区 | 0002 | 打孔器 | 500
000C | 武侯区 | 0003 | 运动T衫 | 4000
000B | 金牛区 | 0003 | 运动T衫 | 4000
000A | 成华区 | 0003 | 运动T衫 | 4000
000C | 武侯区 | 0004 | 菜刀 | 3000
000B | 金牛区 | 0004 | 菜刀 | 3000
000C | 武侯区 | 0006 | 叉子 | 500
000B | 金牛区 | 0006 | 叉子 | 500
000C | 武侯区 | 0007 | 擦菜板 | 880
000B | 金牛区 | 0007 | 擦菜板 | 880
(13 行记录)
内联结要点1—FROM子句
进行联结是需要在FROM子句中使用多个表：
FROM ShopProduct AS SP INNER JOIN Product AS P
使用INNER JOIN关键字将两张表联结在一起，SP和P分别是这两张表的别名。
内联结要点2—ON子句
在ON之后指定两张表联结所使用的列（联结键）：
ON SP.product_id = P.product_id
进行内联结是必须使用ON子句，并且要书写在FROM和WHERE子句之间。
内联结要点3—SELECT子句
在SELECT子句中指定需要的列：
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写。
内联结和WHERE子句结合使用
选取出成华区商店的记录：
--内联结和WHERE子句结合使用
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';
执行结果：
shop_id | shop_name | product_id | product_name | sale_price---------+-----------+------------+--------------+------------
000A | 成华区 | 0001 | T衫 | 1000
000A | 成华区 | 0002 | 打孔器 | 500
000A | 成华区 | 0003 | 运动T衫 | 4000
(3 行记录)
外联结—OUTER JOIN
外联结也是通过ON子句的联结键将两张表进行联结，并从两张表中同时选取出相应的列。基本的使用方法与内联结相同，只是结果有所不同。我们把刚才的内联结语句改为外联结语句看看就知道差异了：
--将两张表进行外联结
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;
执行结果：
shop_id | shop_name | product_id | product_name | sale_price---------+-----------+------------+--------------+------------
000D | 锦江区 | 0001 | T衫 | 1000
000A | 成华区 | 0001 | T衫 | 1000
000B | 金牛区 | 0002 | 打孔器 | 500
000A | 成华区 | 0002 | 打孔器 | 500
000C | 武侯区 | 0003 | 运动T衫 | 4000
000B | 金牛区 | 0003 | 运动T衫 | 4000
000A | 成华区 | 0003 | 运动T衫 | 4000
000C | 武侯区 | 0004 | 菜刀 | 3000
000B | 金牛区 | 0004 | 菜刀 | 3000
| | | 高压锅 | 6800
000C | 武侯区 | 0006 | 叉子 | 500
000B | 金牛区 | 0006 | 叉子 | 500
000C | 武侯区 | 0007 | 擦菜板 | 880
000B | 金牛区 | 0007 | 擦菜板 | 880
| | | 圆珠笔 | 100
(15 行记录)
如上所示，比内联结时多出了两行（高压锅和圆珠笔）
外联结要点1—选取出单张表中全部的信息
内联结只能选出同时存在于两张表中的数据，相反，对于外联结来说，只要数据存在于某一张表中，就能够读取出来。
外联结要点2—关于主表问题
外联结还有一点非常重要，那就是要把哪张表作为主表。最终结果会包含主表中所有的数据。指定主表的关键字是LEFT和RIGHT。顾名思义，使用LEFT时FROM子句中写在左侧的表是主表，使用RIGHT时右侧的表是主表。
上面的例子使用的是RIGHT关键字：
FROM ShopProduct AS SP RIGHT OUTER JOIN Product AS P
说了这么多，最要的是：使用二者所得到的结果完全相同！
所以不必纠结使用LEFT还是RIGHT，结果都是一样的。
3张以上的表的联结
原则上联结表的数量并没有限制，所以我们可以尝试一下联结3张表，
首先再创建一个用来管理库存商品的表InventoryProduct:
--创建InventoryProduct表
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));CREATE TABLE
--插入数据
BEGIN TRANSACTION;BEGIN
INSERT INTO InventoryProduct VALUES ('P001', '0001', 0);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P001', '0002', 120);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P001', '0003', 200);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P001', '0004', 3);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P001', '0005', 0);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P001', '0006', 99);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P001', '0007', 999);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P001', '0008', 200);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P002', '0001', 10);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P002', '0002', 25);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P002', '0003', 34);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P002', '0004', 19);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P002', '0005', 99);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P002', '0006', 0);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P002', '0007', 0);INSERT 0 1
INSERT INTO InventoryProduct VALUES ('P002', '0008', 18);INSERT 0 1
COMMIT;COMMIT
确认一下创建的表的内容：
SELECT * FROM InventoryProduct;
执行结果：
inventory_id | product_id | inventory_quantity--------------+------------+--------------------
P001 | 0001 | 0
P001 | 0002 | 120
P001 | 0003 | 200
P001 | 0004 | 3
P001 | 0005 | 0
P001 | 0006 | 99
P001 | 0007 | 999
P001 | 0008 | 200
P002 | 0001 | 10
P002 | 0002 | 25
P002 | 0003 | 34
P002 | 0004 | 19
P002 | 0005 | 99
P002 | 0006 | 0
P002 | 0007 | 0
P002 | 0008 | 18
(16 行记录)
然后我们对三张表进行内联结，联结键为商品编号（product_id）:
--对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';
执行结果：
shop_id | shop_name | product_id | product_name | sale_price | inventory_quantity---------+-----------+------------+--------------+------------+--------------------
000A | 成华区 | 0001 | T衫 | 1000 | 0
000A | 成华区 | 0002 | 打孔器 | 500 | 120
000A | 成华区 | 0003 | 运动T衫 | 4000 | 200
000B | 金牛区 | 0002 | 打孔器 | 500 | 120
000B | 金牛区 | 0003 | 运动T衫 | 4000 | 200
000B | 金牛区 | 0004 | 菜刀 | 3000 | 3
000B | 金牛区 | 0006 | 叉子 | 500 | 99
000B | 金牛区 | 0007 | 擦菜板 | 880 | 999
000C | 武侯区 | 0003 | 运动T衫 | 4000 | 200
000C | 武侯区 | 0004 | 菜刀 | 3000 | 3
000C | 武侯区 | 0006 | 叉子 | 500 | 99
000C | 武侯区 | 0007 | 擦菜板 | 880 | 999
000D | 锦江区 | 0001 | T衫 | 1000 | 0
(13 行记录)
交叉联结—CROSS JOIN
交叉联结的语法十分简单，但结果特别长，例如对两张表进行交叉联结：
--将两张表进行交叉联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP CROSS JOIN Product AS P;
上述的SQL语句的执行结果有104行，我就不贴了，交叉联结的原理就是笛卡尔积。因此结果中的记录通常是两张表行数的乘积。本例中，因为ShopProduct表有13行记录，Product表有8行记录，所以，结果就有13 * 8 = 104行记录。
每天学习一点点，每天进步一点点。
网友评论