【MySQL学习】重要功能-联结表和高级联结

最新推荐文章于 2024-11-13 17:30:56 发布

王树杰

最新推荐文章于 2024-11-13 17:30:56 发布

阅读量279

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43561218/article/details/114421473

版权

联结表和高级联结

联结表
高级联结创建

联结表

SQL最强大的功能之一就是能在数据检索查询的执行中联结（join）表。联结是利用SQL的 SELECT 能执行的最重要的操作，很好地理解联结及其语法是学习SQL的一个极为重要的组成部分。

在能够有效地使用联结前，必须了解关系表以及关系数据库设计的一些基础知识。

下面的介绍并不是这个内容的全部知识，但作为入门已经足够了。

联结

为了节省储存空间，我们使用联结表尽量避免同样的信息出现多次。使用书中的表举个例子：

假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息。

现在，假如有由同一供应商生产的多种物品，那么在何处存储供应商信息（如，供应商名、地址、联系方法等）呢？将这些数据与产品信息分开存储的理由如下。

因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。

如果供应商信息改变（例如，供应商搬家或电话号码变动），只需
改动一次即可。
如果有重复数据（即每种产品都存储供应商信息），很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。

关键是，相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值（即关系设计中的关系（relational））互相关联。

在这个例子中，可建立两个表，一个存储供应商信息，另一个存储产品信息。 vendors 表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键（primary key）（在第1章中首次提到），可以是供应商ID或任何其他唯一值。products 表只存储产品信息，它除了存储供应商ID（ vendors 表的主键）外不存储其他供应商信息。 vendors 表的主键又叫作 products 的外键，它将 vendors 表与 products 表关联，利用供应商ID能从 vendors 表中找出相应供应商的详细信息。

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

这样做的好处如下：

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

总之，关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。

为什么要使用联结

正如所述，分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性。但这些好处是有代价的。如果数据存储在多个表中，怎样用单条 SELECT 语句检索出数据？答案是使用联结。简单地说，联结是一种机制，用来在一条 SELECT语句中关联表，因此称之为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。维护引用完整性重要的是，要理解联结不是物理实体。换句话说，它在实际的数据库表中不存在。联结由MySQL根据需要建立，它存在于查询的执行当中。

在使用关系表时，仅在关系列中插入合法的数据非常重要。回到这里的例子，如果在 products 表中插入拥有非法供应商ID（即没有在 vendors 表中出现）的供应商生产的产品，则这些产品是不可访问的，因为它们没有关联到某个供应商。为防止这种情况发生，可指示MySQL只允许在 products 表的供应商ID列中出现合法值（即出现在 vendors 表中的供应商）。这就是维护引用完整性，它是通过在表的定义中指定主键和外键来实现的。（这将在第21章介绍。）

创建联结

创建联结需要规定要联结的所有表以及他们如何关联

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

返回值是：

+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| 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 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+

这里 WHERE 语句有两个是建立联结的重要部分，如果没有 WHERE 而单出的用 FROM 指定两个表格，那么返回的数值将会是两个表格的笛卡尔积（cartesian product），检索出的行的数目将是第一个表的行数乘以第二个表中的行数。

我们去掉 WHERE 来看看这个 SELECT 语句会输出什么：

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   |      55.00 |
| Furball Inc.   | Oil can        |       8.99 |
| Furball Inc.   | Safe           |      50.00 |
| Furball Inc.   | Sling          |       4.49 |
| Furball Inc.   | TNT (1 stick)  |       2.50 |
| Furball Inc.   | TNT (5 sticks) |      10.00 |
| Jet Set        | .5 ton anvil   |       5.99 |
| Jet Set        | 1 ton anvil    |       9.99 |
| Jet Set        | 2 ton anvil    |      14.99 |
| Jet Set        | Bird seed      |      10.00 |
| Jet Set        | Carrots        |       2.50 |
| Jet Set        | Detonator      |      13.00 |
| Jet Set        | Fuses          |       3.42 |
| Jet Set        | JetPack 1000   |      35.00 |
| Jet Set        | JetPack 2000   |      55.00 |
| Jet Set        | Oil can        |       8.99 |
| Jet Set        | Safe           |      50.00 |
| Jet Set        | Sling          |       4.49 |
| Jet Set        | TNT (1 stick)  |       2.50 |
| Jet Set        | TNT (5 sticks) |      10.00 |
| Jouets Et Ours | .5 ton anvil   |       5.99 |
| Jouets Et Ours | 1 ton anvil    |       9.99 |
| Jouets Et Ours | 2 ton anvil    |      14.99 |
| Jouets Et Ours | Bird seed      |      10.00 |
| Jouets Et Ours | Carrots        |       2.50 |
| Jouets Et Ours | Detonator      |      13.00 |
| Jouets Et Ours | Fuses          |       3.42 |
| Jouets Et Ours | JetPack 1000   |      35.00 |
| Jouets Et Ours | JetPack 2000   |      55.00 |
| Jouets Et Ours | Oil can        |       8.99 |
| Jouets Et Ours | Safe           |      50.00 |
| Jouets Et Ours | Sling          |       4.49 |
| Jouets Et Ours | TNT (1 stick)  |       2.50 |
| Jouets Et Ours | TNT (5 sticks) |      10.00 |
| LT Supplies    | .5 ton anvil   |       5.99 |
| LT Supplies    | 1 ton anvil    |       9.99 |
| LT Supplies    | 2 ton anvil    |      14.99 |
| LT Supplies    | Bird seed      |      10.00 |
| LT Supplies    | Carrots        |       2.50 |
| LT Supplies    | Detonator      |      13.00 |
| LT Supplies    | Fuses          |       3.42 |
| LT Supplies    | JetPack 1000   |      35.00 |
| LT Supplies    | JetPack 2000   |      55.00 |
| LT Supplies    | Oil can        |       8.99 |
| LT Supplies    | Safe           |      50.00 |
| LT Supplies    | Sling          |       4.49 |
| LT Supplies    | TNT (1 stick)  |       2.50 |
| LT Supplies    | TNT (5 sticks) |      10.00 |
+----------------+----------------+------------+

内部联结

前面所用的联结为 等值联结（equijoin） ，它基于两个表之间的相等测试。这种联结也成为了内部联结。我们可以使用稍微不同的语法来明确指定联结的类型。下面的 SELECT 语句返回与前面例子完全相同的数据：

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

+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
| ACME        | Detonator      |      13.00 |
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
+-------------+----------------+------------+

ANSL SQL 规范首选上述的语法。

多表联结

SQL 对一条 SELECT 语句中可以联结的表的数目没有限制。创建联结的基本规则也相同。首先列出所有的表，然后定义表之间的关系。

我们举一个例子：

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;

+----------------+-------------+------------+----------+
| prod_name      | vend_name   | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil   | Anvils R Us |       5.99 |       10 |
| 1 ton anvil    | Anvils R Us |       9.99 |        3 |
| TNT (5 sticks) | ACME        |      10.00 |        5 |
| Bird seed      | ACME        |      10.00 |        1 |
+----------------+-------------+------------+----------+

现在我们回过头考察之前见到的那个复杂的子查询：

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

这个子查询从 orderitems 中调用了 TNT2 的订单数编号列表K，又从 orders 表中调用了订单K中所有的客户id列表J，接着又从 customers 列表中调用了列表Y中所有客户的信息

现在我们可以用等价的联结进行查询

SELECT cust_name, cust_contact
FROM  customers, orders ,orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';

我们将 orderitems 和 orders 列表中的订单编号关联，将 customers 和 orders 中的客户id关联，并查询产品id为 TNT2 的产品的相关客户。

+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

正如所见，为执行任一给定的SQL操作，一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此，有必要对不同的选择机制进行实验，以找出最适合具体情况的方法。

高级联结创建

使用表别名

标的别名可以用来引用被检索的表列，也能够给表起别名。这样做可以缩短SQL语句，并允许在单条SELECT 语句中多次使用相同的表。

SELECT cust_name，cust_contact
FROM customers AS c, orders AS o, orderitem 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 建立 c 作为 customers 的别名，等等。这使得能使用省写的 c 而不是全名 customers 。在此例子中，表别名只用于 WHERE 子句。但是，表别名不仅能用于 WHERE 子句，它还可以用于 SELECT 的列表、 ORDER BY 子句以及语句的其他部分。

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

不同类型的联结

迄今为止，我们只使用过内部联结（或者叫等值联结（equijoin））。还有三种联结叫做自联结、自然联结和外部联结。

自联结

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

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

下面是解决此问题的一种方法：

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


+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

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

我们使用联结进行相同查询

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 过滤数据，返回所需的数据。

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

自然联结

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

怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符**（ SELECT * ）**，对所有其他表的列使用明确的子集来完成的。下面举一个例子：

SELECT c.*, order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price
FROM customers A c, orders A O, orderitems AS oi
WHERE c.cust_id = o.cust_id
AND prod_id = 'FB'

事实上，迄今为止我们建立的每个内部联结都是自然联结，henknengwomenyuanyo

外部联结

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

对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；
列出所有产品以及订购数量，包括没有人订购的产品；
计算平均销售规模，包括那些至今尚未下订单的客户。
在上述例子中，联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。

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

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

输出是这样的

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

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

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

输出增加了没有订单的10002客户：

+---------+-----------+
| 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 表）中选择所有行。为了从右边的表中选择所有行，应该使用 RIGHT OUTER JOIN ，如下例所示：

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

其实左右外部联结可以通过颠倒语句顺序来自由变换，所以感觉哪种方便就用哪种吧。

使用带聚集函数的联结

其实聚集函数也可以联结使用，我们来看一个例子：现在如果要检索所有客户以及每个客户所下的订单数，下面使用了 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;

+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+

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

+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Mouse House    |   10002 |       0 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+

使用联结和联结条件

在总结关于联结的这两章前，有必要汇总一下关于联结及其使用的某些要点。

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