《SQL必知必会》笔记二：11-14课
二、复杂查询与联结
在上一篇笔记中提到的所有 SELECT 语句都是简单查询，即从单个数据库表中检索数据的单条语句。
但现实中，一般的数据库表都是关系表。
2.1 子查询
子查询（subquery），即嵌套在其他查询中的查询。
实际上，子查询并不总是执行复杂 SELECT 操作的最有效方法。可以对比2.2节中的联结查询。
/*涉及到三张表的嵌套子查询*/ 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'));
注意：作为子查询的 SELECT 语句只能查询单个列。企图检索多个列将返回错误。
/*显示 Customers 表中每个顾客的订单总数。订单与相应的顾客 ID 存储在 Orders 表中*/
/*为了避免歧义，必须使用完全限定列名Orders.cust_id*/
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers ORDER BY cust_name;
2.2 联结表
一个例子理解关系表
有一个包含产品目录的数据库表，其中每类物品占一行。对于每一种物品，要存储的信息包括产品描述、价格，以及生产该产品的供应商。
现在有同一供应商生产的多种物品，那么在何处存储供应商名、地址、联系方法等供应商信息呢？
答案是将这些数据与产品信息分开存储。
关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）。
关系数据可以有效地存储，方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。
联结是一种机制，用来在一条 SELECT 语句中关联表，因此称为联结。
使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。
/*通过WHERE语句联结表Vendors和Products*/ SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE 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;
目前为止使用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（inner join）
/*一个内联结的标准示例，实际上输出与上例完全相同*/ SELECT vend_name, prod_name, prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;
/*2.1节中的子查询例子可以用如下联结方式代替*/ 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';
2.3 高级联结
SQL 除了可以对列名和计算字段使用别名，还允许给表名起别名。这样做有两个主要理由：
缩短 SQL 语句；
允许在一条 SELECT 语句中多次使用相同的表。
/*给表起别名*/ 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';
下面介绍三种其他联结：自联结（self-join）、自然联结（natural join）和外联结（outer join）。
/*找到与 Jim Jones 同一公司的所有顾客*/ /*自联结*/ /*必须使用别名，不然DBMS不知道想要的是哪一列（即使它们其实是同列）*/ 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';
许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。
例如：
对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客
列出所有产品以及订购数量，包括没有人订购的产品
/*在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN左边的表）。*/
/*此外还有FULL OUTER JOIN，即包括左右*/
SELECT Customers.cust_id, Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
这条 SELECT 语句使用 INNER JOIN 将 Customers 和 Orders 表互相关联。
GROUP BY 子句按顾客分组数据，因此，函数调用 COUNT(Orders.order_num)对每个顾客的订单计数，将它作为 num_ord 返回。
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;
2.4 组合查询
利用 UNION 操作符将多条 SELECT 语句组合成一个结果集。
主要有两种情况需要使用组合查询：
在一个查询中从不同的表返回结构数据；
对一个表执行多个查询，按一个查询返回数据。
/*UNION 指示 DBMS 执行这两条 SELECT 语句，并把输出组合成一个查询结果集。单独执行第一个SELECT返回三个结果，单独执行第二个SELECT返回两个结果*/
/*使用UNIOU则会过滤一个重复结果，只输出四个结果*/
/*另外，使用UNION ALL，则结果不取消重复的行*/
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';
UNION使用规则：
UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字UNION分隔（因此，如果组合四条SELECT语句，将要使用三个UNION关键字）。
UNION 中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。
列数据类型必须兼容：类型不必完全相同，但必须是 DBMS 可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。
在用 UNION 组合查询时，只能使用一条 ORDER BY 子句，它必须位于最后一条 SELECT 语句之后。
