SQL 必知必会 - 多表查询/联结/组合

---

SQL 必知必会 - 全书目录

1. 第 01 - 10 章：SQL 必知必会 - 单表查询/过滤/汇总
2. 第 11 - 24 章：SQL 必知必会 - 多表查询/联结/组合
3. 第 15 - 24 章：SQL 必知必会 - 数据更新/存储/事务

使用子查询 - subquery

• SQL 还允许创建子查询（subquery）, 即嵌套在其他查询中的查询
• 把子查询分解为多行并进行适当的缩进, 能极大地简化子查询的使用
• 作为子查询的 SELECT 语句只能查询单个列, 企图检索多个列将返回错误

-- 利用子查询进行过滤
-- (1) 检索包含物品 RGAN01 的所有订单的编号。
-- (2) 检索具有前一步骤列出的订单编号的所有顾客的 ID。
-- (3) 检索前一步骤返回的所有顾客 ID 的顾客信息。
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
                  FROM Orders
                  WHERE order_num IN (SELECT order_num
                                      FROM OrderItems
                                      WHERE prod_id = 'RGAN01'));

-- 作为计算字段使用子查询
-- (1) 从 Customers 表中检索顾客列表；
-- (2) 对于检索出的每个顾客，统计其在 Orders 表中的订单数目。
-- 子查询在构造这种 SELECT 语句时极有用, 但必须注意限制有歧义的列。
SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM Orders
        WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

联结表 - join

• SQL 最强大的功能之一就是能在数据查询的执行中联结（ join）表
• 关系表
  • 关系表的设计就是要把信息分解成多个表，一类数据一个表
  • 各表通过某些共同的值互相关联（所以才叫关系数据库）
  • 可伸缩（scale）：能够适应不断增加的工作量而不失败。
  • 设计良好的数据库或应用程序称为可伸缩性好（scale well）
• 联结是一种机制，用来在一条 SELECT 语句中关联表，因此称为联结
• 使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行
• 创建联结：指定要联结的所有表以及关联它们的方式
• WHERE 子句作为过滤条件，只包含那些匹配给定条件（这里是联结条件）的行
• 笛卡儿积（cartesian product）：由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。也叫叉联结（cross join）。
• 等值联结（equijoin），也称为内联结（inner join）
• ANSI SQL 规范首选 INNER JOIN 语法，

-- prod_name, prod_price 在一个表中，而 vend_name 在另一个表中
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

-- 笛卡儿积
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;

-- 稍微不同的语法，结果相同
-- 两个表之间的关系是以 INNER JOIN 指定的部分 FROM 子句
-- 联结条件用特定的 ON 子句而不是 WHERE 子句给出
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

-- 联结多个表
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;

-- 第 11 章的例子可以简化为：
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';

创建高级联结

• 在创建计算字段中，可以使用 AS 关键字给列起别名
• SQL 除了可以对列名和计算字段使用别名，还允许给表名起别名
  • 缩短SQL 语句
  • 允许在一条 SELECT 语句中多次使用相同的表
  • Oracle 不支持 AS
• 表别名只在查询执行中使用
• 自联结：通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句
• 自然联结：排除多次出现的列，使每一列只返回一次
  • 要求你只能选择那些唯一的列
  • 一般通过对一个表使用通配符（ SELECT *），而对其他表的列使用明确的子集来完成
• 外联结：有时候需要包含没有关联行的那些行
  • 对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客
  • 列出所有产品以及订购数量，包括没有人订购的产品
  • 计算平均销售规模，包括那些至今尚未下订单的顾客。
  • 使用关键字 OUTER JOIN
    来指定联结类型
  • 在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表
  • RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN
    左边的表
  • SQLite 只支持 LEFT OUTER JOIN
• 全外联结：检索两个表中
  的所有行并关联那些可以关联的行
  • FULL OUTER JOIN
  • Access、MariaDB、MySQL、Open Office Base 和 SQLite 不支持 FULL OUTER JOIN 语法。
• 使用带聚集函数的联结
• 使用联结和联结条件
  • 注意所使用的联结类型。一般我们使用内联结，但使用外联结也有效。
  • 关于确切的联结语法，应该查看具体的文档，看相应的DBMS 支持何种语法（大多数DBMS 使用这两课中描述的某种语法）。
  • 保证使用正确的联结条件（不管采用哪种语法），否则会返回不正确的数据。
  • 应该总是提供联结条件，否则会得出笛卡儿积。
  • 在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。

-- FROM 子句中的三个表全都有别名
SELECT cust_name, cust_contact
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 = 'RGAN01';

-- 使用子查询
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
				   FROM Customers
				   WHERE cust_contact = 'Jim Jones');
-- 等同于上面，但使用自联结
-- 查询中需要的两个表实际上是相同的表，因此 Customers 表在 FROM 子句中出现了两次
-- 需要使用表别名
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 = 'Jim Jones';

-- 自然联结
SELECT C.*, O.order_num, O.order_date,
	   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 = 'RGAN01';

-- 内联结，检索所有顾客及其订单
SELECT Customers.cust_id, Orders.order_num
FROM Customers INNER JOIN Orders
  ON Customers.cust_id = Orders.cust_id;

-- 外联结
-- 从左边的表中选择所有行
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
  ON Customers.cust_id = Orders.cust_id;
-- 从右边的表中选择所有行
SELECT Customers.cust_id, Orders.order_num
FROM Customers RIGHT OUTER JOIN Orders
  ON Orders.cust_id = Customers.cust_id;

-- 全外联结
SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers
  ON Orders.cust_id = Customers.cust_id;

-- 使用带聚集函数的联结
-- 检索所有顾客及每个顾客所下的订单数
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;

组合查询

• 多数 SQL 查询只包含从一个或多个表中返回数据的单条 SELECT 语句
• SQL 也允许执行多个查询（多条 SELECT 语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）
• 主要有两种情况需要使用组合查询：
  • 在一个查询中从不同的表返回结构数据
  • 对一个表执行多个查询，按一个查询返回数据（等同于具有多个 WHERE
    子句的查询）
• 创建组合查询
  • 可用 UNION 操作符来组合数条SQL 查询
  • 给出每条 SELECT 语句，在各条语句之间放上关键字 UNION
• UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔（因此，如果组合四条``SELECT语句，将要使用三个UNION`
  关键字）。
• UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
• 列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。
• UNION 从查询结果集中自动去除了重复的行
• 如果想返回所有的匹配行，可使用 UNION ALL 而不是 UNION
• 对组合查询结果排序:
  • 在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须位于最后一条 SELECT 语句之后

-- 几个州的所有顾客
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI');
-- 所有的Fun4All
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

-- 组合查询
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 = 'Fun4All';
-- 等同于
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
   OR cust_name = 'Fun4All';

---

SQL 必知必会 - 全书目录

1. 第 01 - 10 章：SQL 必知必会 - 单表查询/过滤/汇总
2. 第 11 - 24 章：SQL 必知必会 - 多表查询/联结/组合
3. 第 15 - 24 章：SQL 必知必会 - 数据更新/存储/事务