学习SQL【9】-集合与联结

爱吃西瓜的番茄酱

发布于 2018-04-04 11:38:42

1.2K0

发布于 2018-04-04 11:38:42

文章被收录于专栏：一个爱吃西瓜的程序员一个爱吃西瓜的程序员

现在我们开始学习使用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行记录。

每天学习一点点，每天进步一点点。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2017-12-11，如有侵权请联系 cloudcommunity@tencent.com 删除

其他

本文分享自小白客微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

其他

登录后参与评论

0 条评论

热度