MySQL必知必会——联表查询

一、联结

SQL最强大的功能之一就是能在数据检索查询的执行中联结(join)表。联结是利用SQL的SELECT能执行的最重要的操作。在能够有效地使用联结前，必须了解关系表以及关系型数据库设计的一些基础知识。

通过主键和外键来建立表之间的联系，维护表间引用的完整性。
外键相当于一个指针，指向另一个表的主键。

1、关系表

假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息。现在，假如有同一供应商生产的多种物品，那么在何处存储供应商信息(如，供应商名、地址、联系方法等)呢?将这些数据与产品信息分开存储的理由如下：

• 因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息浪费时间和存储空间
• 如果供应商信息发生改变(供应商搬家或电话号码变动)，只需改动一次即可。
• 如果有重复数据(即每种产品都存储供应商信息)，很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。

关键是，相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值(即关系设计中的关系(relational))互相关联。
在这个例子中，可建立两个表，一个 存储供应商信息，另一个存储产品信息。vendors表包含所有供应商的信息，每个供应商占一行，每个供应商具有唯一标识。此标识称为主键(primary key)，可以是供应商ID或任何其他唯一值。products表只存储产品信息，它除了存储供应商ID(vendors表中的外键)外不存储其他供应商信息。vendors表的主键又叫做products表的外键，它将vendors表和products表关联，利用供应商ID能从vendors表中找出供应商的详细信息。

外键(foreign key)：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

这样做(关系表、设外键)的好处如下供应商信息不重复，从而不浪费时间和空间。如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动。由于数据无重复，显然数据是一致的，这使得处理数据更简单。

关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。
可伸缩性(scale)：能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称之为可伸缩性好

2、为什么要使用联结

分解数据为多个表能更有效地存储，更方便的处理，并且具有很大地可伸缩性。然而紧接着就有问题出现了，如果数据存储在多个表中，怎样用单条SELECT语句检索出数据，这时候就需要使用联结了。
联结是一种机制，用来在一条SELECT语句中关联表，因此称为联结。
使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

维护引用完整性：要理解联结不是物理实体，它在实际的数据库表中并不存在，联结由MySQL根据需要建立，它存在于查询的执行当中。在使用关系表时，仅在关系列中插入合法的数据非常重要。如果在products表中处入拥有非法供应商ID(即没有在vendors表中出现)的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商。为防止这种情况的发生，可指示MySQL只允许在products表的供应商ID列中出现合法值。这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的。

二、创建联结

联结的创建需要规定要联结的所有表以及它们如何关联即可。

1、笛卡儿积

笛卡尔积(cartesian product)： 由没有联结条件的表关系返回的结果称为笛卡儿积，检索出的行的数目将是一个表中的行数乘以第二个表中的行数。

笛卡尔积在SQL中的实现方式既是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合。
在一条SELECT语句中联结几个表时，相应的关系是在运行构造的。在数据库表的定义中是不存在能指示MySQL如何对表进行联结的东西，在联结两个表时，实际上做的是将第一个表中的每一行和第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定的条件。没有WHERE子句则将会全部匹配。

MariaDB [course]> SELECT vend_name,prod_name,prod_price
    -> FROM vendors,products
    -> ORDER BY vend_name,prod_name;
+----------------+----------------+------------+
| vend_name      | prod_name      | prod_price |
+----------------+----------------+------------+
| ACME           | .5 ton anvil   |       5.99 |
| ACME           | 1 ton anvil    |       9.99 |
| ACME           | 2 ton anvil    |      14.99 |
| ACME           | Bird seed      |      10.00 |
| ACME           | Carrots        |       2.50 |
| ACME           | Detonator      |      13.00 |
| ACME           | Fuses          |       3.42 |
| ACME           | JetPack 1000   |      35.00 |
| ACME           | JetPack 2000   |      55.00 |
| ACME           | Oil can        |       8.99 |
| ACME           | Safe           |      50.00 |
| ACME           | Sling          |       4.49 |
| ACME           | TNT (1 stick)  |       2.50 |
| ACME           | TNT (5 sticks) |      10.00 |
| Anvils R Us    | .5 ton anvil   |       5.99 |
| Anvils R Us    | 1 ton anvil    |       9.99 |
| Anvils R Us    | 2 ton anvil    |      14.99 |
| Anvils R Us    | Bird seed      |      10.00 |
| Anvils R Us    | Carrots        |       2.50 |
| Anvils R Us    | Detonator      |      13.00 |
| Anvils R Us    | Fuses          |       3.42 |
| Anvils R Us    | JetPack 1000   |      35.00 |
| Anvils R Us    | JetPack 2000   |      55.00 |
| Anvils R Us    | Oil can        |       8.99 |
| Anvils R Us    | Safe           |      50.00 |
| Anvils R Us    | Sling          |       4.49 |
| Anvils R Us    | TNT (1 stick)  |       2.50 |
| Anvils R Us    | TNT (5 sticks) |      10.00 |
| Furball Inc.   | .5 ton anvil   |       5.99 |
| Furball Inc.   | 1 ton anvil    |       9.99 |
| Furball Inc.   | 2 ton anvil    |      14.99 |
| Furball Inc.   | Bird seed      |      10.00 |
| Furball Inc.   | Carrots        |       2.50 |
| Furball Inc.   | Detonator      |      13.00 |
| Furball Inc.   | Fuses          |       3.42 |
| Furball Inc.   | JetPack 1000   |      35.00 |
| Furball Inc.   | JetPack 2000   |