Mysql学习之子查询和联结表(INNER JOIN ON和OUTER JOIN ON)

一.接下面学习我们以下面的样例表为应用对象

二.什么是子查询？

简单来说，子查询就是嵌套查询，用嵌套查询我们可以一步完成简单查询好几步才能完成的功能。

比如：假如我们需要列出订购TNT2的所有客户，我们一般这样检索：
1.检索包含物品TNT2的所有订单编号
2.检索具有前一步骤列出的订单编号的所有用户的ID
3.根据2返回的ID查询出客户

我们下面先用简单查询来实现：

1.SELECT order_num FROM orderitems WHERE prod_id='TNT2';

2.SELECT cust_id FROM orders WHERE order_num IN(1的结果);

3.SELECT cust_name,cust_contact FROM customers WHERE cust_id IN(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='TNT2'));

我们可以很明显的感觉第二种方式更简便，但我们不能无限制的使用嵌套语句，因为太多的嵌套会影响查询性能。

三.联结表

我们知道在关系型数据库中，数据往往存在多个表中，怎样用单条SELECT语句检索出数据呢？
答案是联结，简单来说，联结是一种机制，用来在一条SELECT语句中关联表，因此称之为联结，使用联结的特殊语法可以联结多个表返回一组输出。而输出的表我们称之为联结表

下面是使用例子：

1.SELECT vend_name,prod_name,prod_price
FORM vendors,products
WHERE vendors.vend_id=products.vend_id
ORDER BY vend_name,prod_name;

很明显，这条语句要查询的是vendors和products中的vend_id相等的相关信息混合式输出。上面所使用的连接我们可以称之为WHERE联结。因为它是用where子句实现混合查询的效果的。

1.上面所用的联结称为等值联结，也称为内部联结，它基于两个表之间的相等测试。
内部联结其实还有语义更明确的语法：（INNER JOIN ON）
内部联结：

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

2.上面的语句只联结了两个表，如果我们要联结多个表我们怎么做呢？
有下面的例子:

2.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=20005;

和上面类似，只不过限制关系复杂度罢了。
注意：尽量联结必要的表，联结的表越多性能下降的越厉害。

三.高级联结

1.使用表别名
我们除了给列名和计算字段起别名以外，还可以给表名其别名，这样做的好处有：

a.缩短SQL语句
b.允许在单条SELECT语句中多次使用相同的表。

例如：

SELECT cust_name,cust_contact
FROM customers AS c,order AS o ,orderitems AS oi
WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num AND prod_id='TNT2';

2.使用不同类型的联结
除了等值联结（内联结）以外我们还有其他联结方式：

1）自联结：将同一种表其多个不同的别名进行联结

SELECT p1.prod_id,p1.prod_name
FORM products AS p1,products AS p2
WHERE p1.vend_id=p2.vend_id AND p2.prod_id='DTNTR';

此查询中需要的两个表实际上是相同的表，但这个查询完全合理，这种方式就叫自联结。

2）自然联结

内部联结返回的数据可能存在重复，自然联结排除多次出现，使每个列只返回一次。
怎样完成这个工作呢？ 我们可以选择哪些唯一的列。 这一般是通过对表使用通配符（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='FB';

3)外部联结
许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。
例如，我们可能需要使用联结来完成以下工作：

a.对每个客户下了多少订单进行计数，包括那些至今尚未下单的客户
b.列出所有产品以及订购数量，包括没有人订购的产品
c.计算平均销售规模，包括那些至今尚未下单的客户

在上面例子中，联结表包含了那些在相关表中没有关联行的行，这种类型的联结称为外部联结。

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

关键字OUTER JOIN 指定联结的类型。在使用词语法时必须使用RIGHT或LEFT关键字指定包括其所有行的表(RIGHT指出的是OUTER JOIN右边的表)