【MySQL】创建高级联结

浅浅的花香味﹌ 2022-10-25 11:21 141阅读 0赞

### 目录 ###

*   *  使用表别名
     *  使用不同类型的联结
     *   *  自联结
         *  自然联结
     *  外部联结
     *  使用带聚集函数的联结
     *  使用联结和联结条件

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

## 使用表别名 ##

如何使用别名引用被检索的表列。给列起别名的语法如下：

select concat(rtrim(vend_name),'(',rtrim(vend_country),')') as vend_title 
     from vendors order by vend_name;

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

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

请看下面的SELECT语句。它与前面的例子中所用的语句基本相同，但改成了使用别名:

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

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

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种其他联结，它们分别是自联结、自然联结和外部联结.

### 自联结 ###

使用表别名的主要原因之一是能在单条SELECT语句中不止一次引用相同的表。下面举一个例子。

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产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子句中，以便检索出这个供应商生产的所有物品。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70]  
现在来看使用联结的相同查询：

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';

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

为解决此问题，使用了表别名。 products的第一次出现为别名p1，第二次出现为别名p2。现在可以将这些别名用作表名。例如， SELECT语句使用p1前缀明确地给出所需列的全名。

如果不这样， MySQL将返回错误，因为分别存在两个名为prod\_id、 prod\_name的列。 MySQL不知道想要的是哪一个列（即使它们事实上是同一个列）。 WHERE（通过匹配p1中的vend\_id和p2中的vend\_id）首先联结两个表，然后按第二个表中的prod\_id过滤数据，返回所需的数据。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 1]  
**用自联结而不用子查询**  
自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

### 自然联结 ###

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

怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符（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';

在这个例子中，通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来  
![在这里插入图片描述][20210131160344352.png]事实上，迄今为止我们建立的每个内部联结都是自然联结，很可能我们永远都不会用到不是自然联结的内部联结.

## 外部联结 ##

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

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

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

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

select customers.cust_id,orders.order_num from customers inner join orders on customers.cust_id = orders.cust_id;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 2]外部联结语法类似。为了检索所有客户，包括那些没有订单的客户，可如下进行

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左边的表）。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 3]  
上面的例子使用LEFT OUTER JOIN从FROM子句的左边表（customers表）中选择所有行。为了从右边的表中选择所有行，应该使用RIGHT OUTER JOIN，如下例所示：

select customers.cust_id,orders.order_num from customers 
    right outer join orders on customers.cust_id = orders.cust_id;

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

## 使用带聚集函数的联结 ##

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

如果要检索所有客户及每个客户所下的订单数，下面使用了COUNT()函数的代码可完成此工作：

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;

此SELECT语句使用INNER JOIN将customers和orders表互相关联。GROUP BY 子 句 按 客 户 分 组 数 据 ， 因 此 ， 函 数 调 用 COUNT(orders.order\_num)对每个客户的订单计数，将它作为num\_ord返回。  
![在这里插入图片描述][20210131162232495.png]  
聚集函数也可以方便地与其他联结一起使用。请看下面的例子：

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;

这个例子使用左外部联结来包含所有客户，甚至包含那些没有任何下订单的客户。结果显示也包含了客户Mouse House，它有0个订单。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 5]

## 使用联结和联结条件 ##

汇总一下关于联结及其使用的某些要点。

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70]: /images/20221024/3f50268570244ef18831d2ce26b071fe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 1]: /images/20221024/4a96817a117a4ff5a95230c297f42883.png
[20210131160344352.png]: /images/20221024/df350e37ea3d46c8a275c252492963d4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 2]: /images/20221024/31855966705a45ee82eedb73e7d557de.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 3]: /images/20221024/3badbc1150014a9cbeda96b97fab4b60.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 4]: /images/20221024/e4a082ea783f46d7bb8d4757e783c449.png
[20210131162232495.png]: /images/20221024/a28eef67e8804fb7a3f4a7ea5384f0e0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3hpYW5nanVueWVz_size_16_color_FFFFFF_t_70 5]: /images/20221024/5ad76d07fa444994a672e1e22e09c619.png