Mysql创建高级联结

一、使用表别名

咱们之前看到了如何使用别名引用被检索的表列。给列起别名的语法：

mysql>select Concat(RTrim(vend_name),‘(’,RTrim(vend_country),‘)’) AS vend_title from vendors order by vend_name;

别名AS除了用于列名和计算字段外，SQL还允许给表名起别名，有两个主要理由：

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

mysql>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 = ‘TNT2’;

表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。

二、使用不同类型的联结

迄今为止，我们使用的只是称为内部联结或等值联结（equijoin）的简单联结。现在来看3种其他联结：自联结、自然联结、外部联结。

1.自联结

如前所述，使用表别名的主要原因之一是能在单条select语句中不止一次引用相同的表。

没有使用自联结而是子查询：mysql>select prod_id, prod_name from products where vend_id = (select vend_id from products where prod_id = ‘DTNTR’);

使用自联结:mysql>select p1.prod_id, p1.prod_name from products AS p1, products AS p2 where p1.vend_id = p2.vend_id and p2.prod_id = ‘DTNTR’;

2.自然联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的。

mysql> 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.外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。

例如:

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

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

下面的select语句给出一个简单的内部联结。它检索所有客户及其订单：

mysql>select customers.cust_id, orders.order_num from customers INNER JOIN orders ON customers.cust_id = orders.cust_id;

外部联结语法类似：为了检索所有客户，包括那些没有订单的客户

mysql>select customers.cust_id, orders.order_num from customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id;

输出：

cust_id	order_num
10001	20005
10001	20009
10002	NULL
10003	20006
10004	20007
10005	20008

类似于上一章所看到的内部联结，

这条SELECT语句使用了关键字OUTER JOIN来指定联结类型（而不是在WHERE子句中指定）

与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表

（RIGHT指出的是OUTER JOIN右边的表，而LEFT关键字指出的是OUTER JOIN左边的表）

而上述例子中便是使用LEFT OUTER JOIN从FROM自己的左边表（customers表）中选择所有行。

三、使用带聚集函数的联结

mysql>select customers.cust_name,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;

mysql>select customers.cust_name,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;

四、使用联结和联结条件

• 注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
• 保证使用正确的联结条件，否则将返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡尔积
• 在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。