0008《SQL必知必会》笔记04-子查询、联接与组合查询

1、子查询：就是嵌套的查询，用一个查询的结果作为另一个查询的条件。

　　比如要列出订购了物品“RGAN01”的所有顾客的ID、姓名和联系人，需要经历以下几步

　　　　（1）从orderitems中找出订购了“RGAN01”的所有订单编号

　　　　（2）用（1）的订单编号在orders中找出顾客的ID

　　　　（3）用（2）的顾客ID在customers中找出顾客的姓名和联系人

　　书写SQL语句的时候，从第3步开始写起，SQL代码如下：

SELECT cust_id,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.作为子查询的SELECT只能查询单列；2.子查询嵌套的数目没有限制；3.子查询的效率比较低

2、联接：在一条SELECT语句中关联表。用法：指定要联接的表以及联接他们的方式。上面的列子用联接法如下：

SELECT customers.cust_id,cust_name,cust_contact
 FROM customers,orders,orderitems
 WHERE customers.cust_id=orders.cust_id
  AND orders.order_num=orderitems.order_num
  AND prod_id='RGAN01';

注意：1.在多个表中有相同的列名（比如customers和orders中都有“cust_id”），必须清楚指明是哪个表的列名；2.WHERE字句不可少，否则就会形成笛卡尔积，左右表的每行都将进行联接，总行数=表行数的连乘；3.联接一定要正确

 3、作为计算字段使用子查询：对该部分理解不能

4、内联接：（2）中使用的就是内联接（又称为等值联接，联接的两个表列是相等的），下面换个例子内联接2个表：列出每个供应商的名字、供应的产品、价格。

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id=products.vend_id
ORDER BY vendors.vend_id;

5、自联接：可以联接2个不同的表，也可以联接2个相同的表。比如列出与“Jim Jones”同一客户下的其他人的email地址：

SELECT c1.cust_name,c1.cust_contact,c1.cust_email
FROM customers c1,customers c2
WHERE c1.cust_name=c2.cust_name
 AND c2.cust_contact='Jim Jones';

　　　　　　　　其中的C1，C2，是表的别名，在表名后面用空格隔开。

6、外联接：左、右、全。内联接能列出有关联行的那些行，而有些时候我们需要把没有关联行的那些行也列出来，这就需要外联接。比如列出每个顾客的订单号，包括没有订单的顾客：

SELECT customers.cust_id,orders.cust_id,orders.order_num
FROM customers  LEFT OUTER JOIN orders
  ON customers.cust_id=orders.cust_id;

　　　　说明：LEFT OUTER JOIN把左侧表（customers）在右侧表（orders）中没有关联行（cust_id=1000000002）的行给列出来。

　　　　　　　左联接：LEFT OUTER JOIN；右联接：RIGHT OUTER JOIN；全联接：FULL OUTER JOIN。左右是相对而言的，联接词前后的两个表换个顺序，就把LEFT和RIGHT换一下。

7、将聚集函数与联接结合

SELECT customers.cust_id,COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id=orders.cust_id
GROUP BY customers.cust_id;

8、联接的使用注意：不要联接不必要的表，联接的表越多，性能下降越厉害

9、组合查询：将多条SELECT语句组合到一起

SELECT cust_name,cust_contact,cust_email
FROM customers
WHERE cust_state IN('IL','IN','MI')
  OR 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';

　　　　其他：UNION默认排除重复的行，若不排除则用UNION ALL；

　　　　　　　对UNION结果排序的ORDER BY语句要放到最后

UNION规则：1.可以将多条SELECT语句组合到一起，是否限制数目，取决于DBMS；

　　　　　 　2.每个SELECT必须包含相同的列、表达式、聚集函数；

　　　　 　　3.同一列的数据类型必须兼容