04 子查询、联结表和组合查询

04 子查询、联结表和组合查询

标签：数据库、SQL

1.子查询的基本使用

SELECT cust_id 
FROM Orders
WHERE order_num 
IN (SELECT order_num 
	FROM OrderItems 
	WHERE prod_id = 'RGAN01');

  在SELECT语句中，子查询总是从内向外处 理。在处理上面的SELECT语句时，DBMS实 际上执行了两个操作。警告：作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。

2.以计算字段来

SELECT cust_name, cust_state, (SELECT COUNT(*) 
							   FROM Orders 
							   WHERE Orders.cust_id = Customers.cust_id) AS orders 
FROM Customers 
ORDER BY cust_name;

  这里用新生成的orders列，就是用子查询的方法生成的。

3.联结表

弄不清楚这部分的联结的话，可以参考：图解MySQL 内连接、外连接、左连接、右连接、全连接……太多了

联接类型	说明
INNER JOIN	内联接，结果为两个联接表中的匹配行的联接
LEFT OUTER JOIN	左联接：结果包括左表（出现在JOIN子句最左边）中的所有行，不包括右表中的不匹配行。
RIGHT OUTER JOIN	右联接：结果包括右表（出现在JOIN子句最右边）中的所有行，不包括有左表中的不匹配的行。
FULL OUTER JOIN	完全联接：结果包括所有联接中的所有行，不论他们是否匹配。
CROSS JOIN	交叉联接：结果包括两个联接表中的所有可能的行组合。交叉连接返回的是两个表的笛卡儿积。（Oracle不支持）
NATURAL JOIN	自然连接时在两张表中寻找那些数据类型 和列名都相等的字段，然后自动地将他们连接起来。

①内联结

SELECT vend_name, prod_name, prod_price 
FROM Vendors, Products 
WHERE Vendors.vend_id = Products.vend_id;

  实际上上一个例子已经用过了联结的方法。而且使用了WHERE来限制结果，如果没有用WHERE的话，即没有任何的限制条件，则输出的是这两个表的笛卡尔集。

  目前为止使用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种 联结也称为内联结（inner join）。因此，以下的代码效果与上例同样。

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products 
ON Vendors.vend_id = Products.vend_id

  这里，两个表之间的 关系是以INNER JOIN指定的部分FROM子句。 在使用这种语法时，联结条件用特定的ON子 句而不是WHERE子句给出。传递给ON的实际 条件与传递给WHERE的相同。

②联结多个表

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;

  警告：性能考虑。DBMS在运行时关联指定的每个表，以处 理联结。这种处理可能非常耗费资源，因 此应该注意，不要联结不必要的表。联结 的表越多，性能下降越厉害。

③启用别名

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

  这里的每个表都用 AS 关键词来定义了别名。
警告：Oracle中没有 中没有AS Oracle不支持AS关键字。要在Oracle中使用 别名，可以不用AS，简单地指定列名即可 （因此，应该是Customers C，而不 是Customers AS C）。

④自联结
  之前，我们一直使用的是等值联结（内联结）

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 cust_id, cust_name, cust_contact 
FROM Customers 
WHERE cust_name = (SELECT cust_name 
	FROM Customers 
	WHERE cust_contact = 'Jim Jones');

  总之，这两个句子都是要求 给与Jim Jones同一公司的所有顾客发送 一封信件。这个查询要求首先找出Jim Jones工 作的公司，然后找出在该公司工作的顾客。
  此查询中需要的两个表实际上是相同的表， 因此Customers表在FROM子句中出现了两 次。虽然这是完全合法的，但对Customers的引用具有歧义性，所以使用了表别名。Customers第一次出现用了别名C1，第二次出现 用了别名C2。

⑤自然联结

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 LEFT OUTER JOIN Orders 
ON Customers.cust_id = Orders.cust_id;

  当然，这里有左联结和右联结。在使用OUTER JOIN语法时，必须使用RIGHT、LEFT或者FULL关键字 指定包括其所有行的表。

警告：FULL OUTER JOIN的支持 的支持 Access、MariaDB、MySQL、Open Office Base或SQLite不支持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;

1.UNION

①使用UNIION
  利用UNION，可以把多条查询 的结果作为一条组合查询返回，不管结果中有 无重复。使用UNION可极大地简化复杂 的WHERE子句，简化从多个表中检索数据的工 作。

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

  这里其实合并了两个SELCET语句，这两个语句本身其实是可以单独写出来的，这里用了UNION来将它们的结果合并。这样，它们的结果就会显示在一个表里面了。UNION语句必须有两个以上的SELECT语句组成，意味着可以组合很多个SELECT。

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state 
IN ('IL','IN','MI') 
	OR cust_name = 'Fun4All';

  上句的作用和上上句的作用是一样的，可以换用。在不同的情况下性能是不一样的。

②UNION ALL——包含或取消重复的行
  使用UNION的时候，它单独从查询结果集中去掉重复行，而如果希望不去掉重复的行的话，可以使用UNION ALL。

SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state IN ('IL','IN','MI') 
UNION ALL SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_name = 'Fun4All'

③对组合查询结果排序
  SELECT语句的输出用ORDER BY子句排序。在用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之 后。
  因为对于结果集，不存在用一种方式排序一 部分，而又用另一种方式排序另一部分的情 况，因此不允许使用多条ORDER BY子句。

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' 
ORDER BY cust_name, cust_contact;