8、联结表
阅读书籍《SQL必知必会（第4版）》笔记
关系表：把信息分解成多个表，一类数据一个表。
联结：是一种机制，用来在一条 SELECT 语句中关联表，因此称为联结。
解决的痛点：
- 重复信息存储，浪费存储空间
- 信息变更只需修改一次
- 重复数据存储时，很难保证每次输入该数据的方式相同，不一致的数据在报表中就很难利用
1、创建联结
等值联结/内联结：指定要联结的所有表，及关联他们的方式即可
- 或者使用关键词
INNER JOIN+ON
SELECT vend_name, prod_name, prod_price # 三个属性，来源于两个表
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id # 联结、匹配指定
-- INNER JOIN + ON
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id
-- 三个表联结
SELECT vend_name, prod_name, prod_price, quantity
FROM Vendors, Products, OrderItems
WHERE Vendors.vend_id = Products.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 2025; # 订单号为 2025 的物品
2、高级联结
- 使用表别名（只在查询时使用，不返回到客户端）
SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI # 别名
WhERE C.cust_id = O.cust_id
AND O.order_num = OI.order_num
AND prod_id = 'gran01';
- 自联结：同一张表可以有多个别名用于筛选
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Alice Qiu'; # 查询指定联系人的公司的所有联系人的练习方式
- 自然联结：要求只能选择那些唯一的列（一般都是自然连接，不会有重复的列被筛选出来）
SELECT C.*, O.order_num, O.order_date, # 只有C表是通配符
OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'rgna01';
- 外联结：包含了那些在相关表中没有关联的行
- 必须使用 LEFT 或者 RIGHT关键词指定包含其所有行的表
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
-- 结果
cust_id order_num
100001 20005
100002 20009
100003 (NULL) # 检索没有订单顾客在内的所有顾客
100004 20008
-- 如果上述查询 sql 中的 LEFT OUTER 外联结换成 INNER JOIN 内联结，会筛选出有订单顾客
-- 结果
cust_id order_num
100001 20005
100002 20009
100004 20008
- 全外联结：包含了两个表所有不关联的行 -> FULL OUTER JOIN
在联结中使用 聚集函数
# 检索所有顾客及每个顾客下的订单数
SELECT Customers.cust_id,
COUNT(Orders.order_num) AS num_ord # 计算每个顾客的订单总量
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id; # 按顾客分组
关键词 UNOIN 组合查询
把输出组合成一个查询结果集 -> 可以简化 WHERE 子句
- 1、每个查询必须包含相同的列、表达式、聚集函数
- 2、查询结果中自动去除重复的行（如果想要返回所有的匹配行，可以使用 UNION ALL 而不是 UNION）
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION # 组合了上下两次查询
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Full4';
-- 上面的组合可以写成 -> 查询得到的结果是一样的，但是每一行顺序是不同的
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
OR cust_name = 'Full4';