<MySQL学习九>创建高级联结-优快云博客

本文深入探讨SQL中的联结技巧，包括使用表别名、自联结、自然联结和外部联结的方法，以及如何结合聚集函数进行数据汇总。通过实例解析不同联结类型的应用场景，帮助读者掌握更高效的SQL查询技巧。

摘要: 本篇博客仅作为笔记，如有侵权，请联系，立即删除（网上找博客学习，然后手记笔记，因纸质笔记不便保存，所以保存到网络笔记）。

　　本章将讲解另外一些联结类型（包括它们的含义和使用方法），介绍如何对被联结的表使用表别名和聚集函数。

一、使用表别名

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

　　（1）缩短SQL语句；

　　（2）允许在单挑SELECT语句中多次使用相同的表。

　　请看下面的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 = 'TNT2';

　　分析：可以看到，FROM子句中3个表全都具有别名。customers AS c建立作为customers的别名。这里使用省写的c而不是全名customers。在此例中，表别名只用于WHERE子句。但是，表别名不仅能用于WHERE子句，它还可以用于SELECT的列表、ORDER BY子句以及语句的其他部分。

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

二、使用不同类型的联结

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

　　1、自联结

　　使用表别名的主要原因之一是能在单挑SELECT语句中不止一次引用相同的表：

　　如果你发现某物品（其ID为DINTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。下面是解决此问题的一种方法：

SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = 'DTNTR');

　　分析：这是第一种解决方案，它使用了子查询。内部的SELECT语句做了一个简单的检索，返回生产ID为DTNTR的物品供应商的vend_id。该ID用于外部查询的WHERE子句中，以便检索出这个供应商生产的所有物品。

　　现在来看一下使用联结的相同查询：

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

　　分析：此查询中需要的两个表实际上是相同的表，因此products表在FROM子句中出现了两次。虽然这是完全合法的，但对products的引用具有二义性，因为MySQL不知道你引用的是products表中的哪个实例。

　　为解决此问题，使用了表别名。products的第一次出现别名p1，第二次出现为别名p2。现在可以将这些别名用作表名，例如，SELECT语句使用p1前缀明确地给出所需列的全名。如果不这样，MySQL不知道想要的是哪一个列（即使它们事实上是同一列）。WHERE（通过匹配p1中的vend_id和p2中的vend_id）首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需数据。

注意：用自联结而不用子查询自联结通常通常作为外部语句用来代替从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，确定哪一种的性能更好。

　　2、自然联结

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

　　自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符（SELECT *），对所有其他表的列使用明确的子集来完成的：

SELECT c.*,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、外部联结

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

　　（1）对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；

　　（2）列出所有产品以及订购数量，包括没有人订购的产品；

　　（3）计算平均销售规模，包括那些至今尚未下订单的客户。

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

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

　　分析：这条SELECT语句使用了关联字OUTER JOIN来指定联结的类型（而不是WHERE子中指定）。但是，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。

注意：没有*=操作符 MySQL不支持简化字符*=和=*的使用，这两种操作符在其他DBMS中很流行。

　　存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。换句话说，左外部联结可通过颠倒FROM或WHERE子句中表的顺序转换为右外部联结。因此，两种类型的外部联结可互换使用，而究竟使用哪一种纯粹是根据方便而定。

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

　　聚集函数是用来汇总数据的。虽然至今为止聚集函数的所有例子只是从单个表汇总数据，但这些函数也可以与联结一起使用。

　　为了说明这一点，请看下面的例子。如果要检索所有客户及每个客户所下的订单数，下面使用COUNT()函数的代码可完成此工作：

SELECT customers.cust_name,
       customers.cust_id,
       COUNT(orders.order_num) AS num_order
FROM customers INNER JOIN orders
ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

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

　　聚集函数也可以方便地与其他联结一起使用。请看下面的例子：

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;