- 15章 联结表
- 1 联结
- 11 关系表
- 12 为何使用联结
- 2 创建联结
- 21 WHERE子句的重要性
- 22 内部联结
- 23 联结多个表
- 总结
- 16章 创建高级联结
- 1 使用表别名
- 2 使用不同类型的联结
- 21 自联结
- 22 自然联结
- 23 外部联结
- 3 使用带聚集函数的联结
- 4 使用联结和联结条件
15章 联结表
15.1 联结
15.1.1 关系表
外键(foreign key)：外键为某个表中的一列，包含另一个表的主键值，定义了两个表的关系。
可伸缩性(scale)：能够适应不断增加的工作量而不失败。
15.1.2 为何使用联结
如果数据存储在多个表中，想要用单条SELECT语句检索数据？ 使用联结。
15.2 创建联结
mysql> 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 |
+-------------+----------------+------------+分析：与前面语句检索区别在于两个列在其中一个表中，而另外一列(vend_name)在另外一个表中。
FROM语句，列出两个表，分别是vendors 和 products，这里需要使用 完全限定列名。
完全限定列名：再次强调，出现二义性时，必须使用。
mysql> SELECT vend_name, prod_price FROM vendors, products ORDER BY vend_name, prod_name;
+----------------+------------+
| vend_name | prod_price |
+----------------+------------+
| ACME | 5.99 |
| ACME | 9.99 |
| ACME | 14.99 |
| ACME | 10.00 |
| ACME | 2.50 |
| ACME | 13.00 |
| ACME | 3.42 |
| ACME | 35.00 |
| ACME | 55.00 |
| ACME | 8.99 |
| ACME | 50.00 |
| ACME | 4.49 |
| ACME | 2.50 |
| ACME | 10.00 |
| Anvils R Us | 5.99 |
| Anvils R Us | 9.99 |
| Anvils R Us | 14.99 |
| Anvils R Us | 10.00 |
| Anvils R Us | 2.50 |
| Anvils R Us | 13.00 |
| Anvils R Us | 3.42 |
| Anvils R Us | 35.00 |
| Anvils R Us | 55.00 |
| Anvils R Us | 8.99 |
| Anvils R Us | 50.00 |
| Anvils R Us | 4.49 |
| Anvils R Us | 2.50 |
| Anvils R Us | 10.00 |
| Furball Inc. | 5.99 |
| Furball Inc. | 9.99 |
| Furball Inc. | 14.99 |
| Furball Inc. | 10.00 |
| Furball Inc. | 2.50 |
| Furball Inc. | 13.00 |
| Furball Inc. | 3.42 |
| Furball Inc. | 35.00 |
| Furball Inc. | 55.00 |
| Furball Inc. | 8.99 |
| Furball Inc. | 50.00 |
| Furball Inc. | 4.49 |
| Furball Inc. | 2.50 |
| Furball Inc. | 10.00 |
| Jet Set | 5.99 |
| Jet Set | 9.99 |
| Jet Set | 14.99 |
| Jet Set | 10.00 |
| Jet Set | 2.50 |
| Jet Set | 13.00 |
| Jet Set | 3.42 |
| Jet Set | 35.00 |
| Jet Set | 55.00 |
| Jet Set | 8.99 |
| Jet Set | 50.00 |
| Jet Set | 4.49 |
| Jet Set | 2.50 |
| Jet Set | 10.00 |
| Jouets Et Ours | 5.99 |
| Jouets Et Ours | 9.99 |
| Jouets Et Ours | 14.99 |
| Jouets Et Ours | 10.00 |
| Jouets Et Ours | 2.50 |
| Jouets Et Ours | 13.00 |
| Jouets Et Ours | 3.42 |
| Jouets Et Ours | 35.00 |
| Jouets Et Ours | 55.00 |
| Jouets Et Ours | 8.99 |
| Jouets Et Ours | 50.00 |
| Jouets Et Ours | 4.49 |
| Jouets Et Ours | 2.50 |
| Jouets Et Ours | 10.00 |
| LT Supplies | 5.99 |
| LT Supplies | 9.99 |
| LT Supplies | 14.99 |
| LT Supplies | 10.00 |
| LT Supplies | 2.50 |
| LT Supplies | 13.00 |
| LT Supplies | 3.42 |
| LT Supplies | 35.00 |
| LT Supplies | 55.00 |
| LT Supplies | 8.99 |
| LT Supplies | 50.00 |
| LT Supplies | 4.49 |
| LT Supplies | 2.50 |
| LT Supplies | 10.00 |
+----------------+------------+15.2.1 WHERE子句的重要性
笛卡尔积(cartesian product)：在没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目是将第一个表中的行数* 第二表中的行数。
WHERE子句：应当确保所有联结都是WHERE子句，否则MySQL将会返回比想象的数据多的多。
叉联结：有时我们会听到返回称为叉联结（cross join）的笛卡尔积的联结类型。
15.2.2 内部联结
目前所有的联结都是等值联结(equijion)，此联结也称为内部联结，修改豫剧来明确指定联结的类型。
mysql> SELECT vend_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id ORDER BY vend_name;
+-------------+------------+
| vend_name | prod_price |
+-------------+------------+
| ACME | 13.00 |
| ACME | 10.00 |
| ACME | 2.50 |
| ACME | 50.00 |
| ACME | 4.49 |
| ACME | 2.50 |
| ACME | 10.00 |
| Anvils R Us | 5.99 |
| Anvils R Us | 9.99 |
| Anvils R Us | 14.99 |
| Jet Set | 35.00 |
| Jet Set | 55.00 |
| LT Supplies | 3.42 |
| LT Supplies | 8.99 |
+-------------+------------+分析：FROM子句中，以INNER JOIN指定，联结条件使用特定的 ON子句而不是WHERE传出。
15.2.3 联结多个表
-方式：首先列出所有表，然后定义表之间的关系。
mysql> 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 |
+----------------+-------------+------------+----------+分析：显示编号为20005的订单物品。通过三个联结条件过滤出所需要的信息。
注意：性能问题，联结表越多，性能下降越厉害。
同样的14章中的例子可以使用联结表方式
mysql> 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';
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+分析： 这里没有使用嵌套子查询，而是使用两个联结。
总结
联结表称得上SQL最重要的特征，有效使用联结需要对**关系数据库**设计有基本了解。16章 创建高级联结
本章讲解另一些联结类型，介绍对联结的表使用表别名和聚集函数。
16.1 使用表别名
给表名起别名的优点：
1. 缩短SQL语句
2. 允许单条SELECT语句中多次使用相同的表
与14章中相比，下文中更改了表名称
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';
+----------------+--------------+
| cust_name | cust_contact |
+----------------+--------------+
| Coyote Inc. | Y Lee |
| Yosemite Place | Y Sam |
+----------------+--------------+分析：FROM子句中三个表名全部具有别名。注意，表别名只能在查询执行中使用，与列名不同，表别名不返回客户机。
16.2 使用不同类型的联结
此前一直使用的联结为内部联结或等值联结(equijoin)，现有三种其他形式联结，*自联结*、**自然联结**和**外部联结**。16.2.1 自联结
目的：因很多情况下，在一条SELECT子句中不止一次引用相同表格，为避免繁琐，引入自联结。
假如需要通过通过某物品，检索所有此供应商的物品。需要首先查找供应商→检索所有物品
mysql> 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) |
+---------+----------------+分析：此方法利用 子查询(嵌套查询)，缺点：多次输入vend_id。相应的联结查询方式为：
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';
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+分析：纵使使用同一个表，也需要使用 完全限定。同时使用自联结，用外部语句替代相同表中检索数据时使用的子查询语句。性能上，比子查询快。
16.2.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';
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email | order_num | order_date | prod_id | quantity | item_price |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | 20005 | 2005-09-01 00:00:00 | FB | 1 | 10.00 |
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com | 20009 | 2005-10-08 00:00:00 | FB | 1 | 10.00 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+16.2.3 外部联结
有时需要包含没有关联行进行关联。例如可能需要使用联结来完成如下工作：
1. 对每个客户订单进行计数，包括至今未下订单的客户。（此时订单表中不包含未下订单客户）
2. 列出所有产品及订购数量，包括没人订购的产品。
如下，检索所有客户和订单：
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, orders WHERE customers..cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+为了检索没有订单的客户，需要进行如下(外部联结)操作：
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 |
+---------+-----------+- 分析：使用关键字
OUTER JOIN来指定联结类型（不是在WHERE中指定）。与INNER JOIN不同地方在于，还包括为包含的关联行。 - 注意：使用
OUTER JOIN时，必须指明RIGHT，LEFT关键字（RIGHT指出，OUTER JOIN右侧的表）。customers LEFT OUTER JOIN orders从左侧customers表中选择所有行。
16.3 使用带聚集函数的联结
聚集函数用来汇总函数。可以同联结一起使用。
如要检索所有客户及每个客户所下订单数，使用COUNT()完成：
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;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------+- 注意：函数
COUNT()需要同GROUP BY一起使用，进行分组计算。同时聚集函数也可以用在如下外部联结：
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;
+----------------+---------+---------+
| 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 |
+----------------+---------+---------+16.4 使用联结和联结条件
- 注意联结类型。一般为内部联结，有时需要外部联结。
- 保证使用正确联结条件。
- 应该总是提供联结条件，否则产生笛卡尔积。
- 联结中可能包含多个表。测试前需要分别测试单个联结。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
