数据库学习纪要（十一）：SQL Sever介绍-3

十一、使用子查询
1、子查询
查询（query）任何SQL语句都时查询，但此术语一般指SELECT语句。SQL还允许创建子查询(subquery)，即：嵌套在其他查询中的查询。

2、利用子查询进行过滤
SELECT cust_id FROM Orders
WHERE order_num in (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');
注：
1）首先执行：SELECT order_num FROM orderitems WHERE prod_id='RGAN01'
执行结果是返回两个订单号：20007和20008
2）这两个值以IN操作符要求的逗号分隔的格式传递给外部查询的WHERE子句
SELECT cust_id FROM orders WHERE order_num IN (20007, 20008)

3、作为计算字段使用子查询
SELECT cust_name, cust_state,
(SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

注：子查询中的WHERE使用了完全限定列名，它告诉SQL比较Orders表中的cust_id与当前正从Customers表中检索的cust_id

十二、联结表
1、联结表（join）是利用SQL的SELECT能执行的最重要的操作。
关系表：关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational））互相关联

2、创建联结
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

3、内部联结
目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试，这种联结也称为内部联结
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

4、联结多个表
SQL对一条SELECT语句中可以联结的表的数目没有限制
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;

此例子显示编号为20007的订单中的物品。订单物品存储在OrderItems表中，每个产品按其ID存储，它引用Products表中的产品。这些产品通过供应商ID联结到Verndors表中相应的供应商，供应商ID存储在每个产品的记录中。

十三、创建高级联结
1、使用表别名
SELECT RTRIM(vend_name) + '(' + RTRIM(vend_country) + ')' AS vend_title
FROM Vendors
ORDER BY vend_name;
别名除了用于列名和计算字段外，SQL还允许给表名起别名，好处有：
1）缩短SQL语句
2）允许在单条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';

注：在Oracle中没有AS，直接指定即可（Customers C)
注：表别名只在查询执行中使用，与列别名不一样，表别名不返回到客户机

2、自联结
自联结和子查询
举例：想发送一封邮件给Jim Jones所在的公司工作的所有客户。此查询要求首先找出Jim Jones工作的公司，然后找出为此公司工作的客户。
1）子查询
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name FROM Customers
WHERE cust_contact = 'Jim Jones');

2）自联结
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';

注：许多DBMS处理联结远比处理子查询快得多

3、自然联结
自然联结排除多次出现，使每个列只返回一次。一般通过对表使用通配符（SELECT *）,对所有其他表的列使用明确的子集来完成的。
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';

4、外部联结
联结包含在相关表中没有关联行的行，称为外部联结。
内联结：
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;

注：在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（Customers表）中选择所有行。

5、使用带聚集函数的联结
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;

此语句使用INNER JOIN将Customers和Orders表相互关联。GROUP BY子句按客户分组数据，因此函数调用COUNT(Orders.order_num)对每个客户的订单计数，将它作为num_ord返回。