7-1　表的加减法

什么是集合运算

集合运算，就是对满足同一规则的记录进行的加减等四则运算

表的加法——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))
DEFAULT CHARSET=utf8;

-- 将数据插入到表Product2（商品2）中
START 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;

-- 使用UNION对表进行加法运算
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name
FROM Product2;

集合运算的注意事项

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

-- 列数不一致时会发生错误
SELECT product_id, product_name
FROM Product
UNION
SELECT product_id, product_name, sale_price
FROM Product2;

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

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

• 可以使用任何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;

包含重复行的集合运算——ALL选项

在集合运算符中使用ALL选项，可以保留重复行。

-- 保留重复行
SELECT product_id, product_name
FROM Product
UNION ALL
SELECT product_id, product_name
FROM Product2;

7-2　联结（以列为单位对表进行联结）

什么是联结

就是将其他表中的列添加过来，进行“添加列”的运算

内联结——INNER-JOIN

• 内联结要点—FROM子句

-- 进行联结时需要在FROM子句中使用多张表。
FROM ShopProduct AS SP INNER JOIN Product AS P

• 内联结要点--ON子句

-- 进行内联结时必须使用ON子句，并且要书写在FROM和WHERE之间。
ON SP.product_id = P.product_id;

• 内联结要点-—SELECT子句

-- 使用联结时SELECT子句中的列需要按照“<表的别名>.<列名>”的格式进行书写。
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price

- 将两张表进行内联结
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子句结合使用
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

• 外联结要点-选取出单张表中全部的信息
• 外联结要点-每张表都是主表吗？
  • 使用 LEFT 时 FROM 子句中写在左侧的表是主表
  • 使用 RIGHT 时右侧的表是主表

-- 将两张表进行外联结
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;

-- 改写后外联结的结果完全相同
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;

3张以上的表的联结

-- 创建InventoryProduct表并向其中插入数据
-- 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));

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

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

交叉联结——CROSS-JOIN 笛卡儿积

-- 将两张表进行交叉联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name
FROM ShopProduct AS SP CROSS JOIN Product AS P;

联结的特定语法和过时语法

-- 使用过时语法的内联结
SELECT SP.shop_id, SP.shop_name, SP.product_id, P.product_name, P.sale_price
FROM ShopProduct SP, Product P
WHERE SP.product_id = P.product_id
AND SP.shop_id = '000A';

-- DDL ：创建表
CREATE TABLE Skills
(skill VARCHAR(32),
PRIMARY KEY(skill));
CREATE TABLE EmpSkills
(emp VARCHAR(32),
skill VARCHAR(32),
PRIMARY KEY(emp, skill))
DEFAULT CHARSET=utf8;

-- DML ：插入数据
START TRANSACTION; 
INSERT INTO Skills VALUES('Oracle');
INSERT INTO Skills VALUES('UNIX');
INSERT INTO Skills VALUES('Java');
INSERT INTO EmpSkills VALUES('相田', 'Oracle');
INSERT INTO EmpSkills VALUES('相田', 'UNIX');
INSERT INTO EmpSkills VALUES('相田', 'Java');
INSERT INTO EmpSkills VALUES('相田', 'C#');
INSERT INTO EmpSkills VALUES('神崎', 'Oracle');
INSERT INTO EmpSkills VALUES('神崎', 'UNIX');
INSERT INTO EmpSkills VALUES('神崎', 'Java');
INSERT INTO EmpSkills VALUES('平井', 'UNIX');
INSERT INTO EmpSkills VALUES('平井', 'Oracle');
INSERT INTO EmpSkills VALUES('平井', 'PHP');
INSERT INTO EmpSkills VALUES('平井', 'Perl');
INSERT INTO EmpSkills VALUES('平井', 'C++');
INSERT INTO EmpSkills VALUES('若田部', 'Perl');
INSERT INTO EmpSkills VALUES('渡来', 'Oracle');
COMMIT;

-- 选取出掌握所有3个领域的技术的员工
SELECT DISTINCT emp
FROM EmpSkills ES1
WHERE NOT EXISTS
(SELECT skill
FROM Skills
EXCEPT
SELECT skill
FROM EmpSkills ES2
WHERE EP1.emp = ES2.emp);

练习题

7.1 请说出下述 SELECT 语句的结果。

-- 使用本章中的Product表
SELECT *
FROM Product
UNION
SELECT *
FROM Product
INTERSECT
SELECT *
FROM Product
ORDER BY product_id;

会将 Product 表中的 8 行记录原封不动地选取出来。

7.2 7-2 节的代码清单 7-11 中列举的外联结的结果中，高压锅和圆珠笔 2 条 记录的商店编号（ shop_id）和商店名称（ shop_name）都是 NULL。
请使用字符串“不确定”替换其中的 NULL。期望结果如下所示。
执行结果

将商店编号和商店名称输出为"不确定"

SELECT COALESCE(s.shop_id, '不确定') as shop_id, 
COALESCE(s.shop_name, '不确定') as shop_name, 
p.product_id, p.product_name, p.sale_price
FROM ShopProduct s RIGHT OUTER JOIN Product p 
ON s.product_id = p.product_id;