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 使用联结和联结条件
- 注意联结类型。一般为内部联结,有时需要外部联结。
- 保证使用正确联结条件。
- 应该总是提供联结条件,否则产生笛卡尔积。
- 联结中可能包含多个表。测试前需要分别测试单个联结。