联结表

什么是链接，为什么要使用，如何使用。

关系表

例子: 一个包含目录的数据库，其中每种类型物品占用一行，每种物品要存储的信息包括产品描述和价格，以及生产该产品的供应商信息。

有一个供应商生产多种物品，何处存储供应商的信息(地址，电话等)，如何分开存储。

  • 同一个供应商存储的信息都是相同的，每种产品重复信息，浪费空间
  • 如果供应商信息改变，执行改一次。

  • 重复数据，难保证每次储存信息一致，不一致信息难管理，利用。
    关系数据库设计:

  • 避免相同数据出现多次

  • 信息被分解成一种数据，一个表

  • 各表通过某些常用值相互关联
    上面的例子，设计两个表，一个存储供应商信息，一个存储产品信息。

  • Vendors 表包含所有供应商信息，供应商的primary key 唯一的标识值vend_id。

  • Products表只存储产品信息，与供应商的primary key vend_id 表关联，利用供应商的ID从Vendors表中找出相应的供应商详细信息。
    这样的设计刚好符合上面3点。

关系数据库优点可伸缩性(scale),能够适应不断增加的工作量。

使用联结的好处

分解多个表方便存储，方便处理，可伸缩性强。

使用链接可以用一条SELECT中关联多个表返回一组输出。

注意:在设计关系数据库，避免在另一个关系表中插入非法的ID，可以设置关系表中值，只出现合法的值

创建联结

链接多个表

  1. mysql> SELECT vend_name, prod_name, prod_price
  2.     -> FROM Vendors, Products
  3.     -> WHERE Vendors.vend_id = Products.vend_id;
  4. +-----------------+---------------------+------------+
  5. | vend_name       | prod_name           | prod_price |
  6. +-----------------+---------------------+------------+
  7. | Bears R Us      | 8 inch teddy bear   |       5.99 |
  8. | Bears R Us      | 12 inch teddy bear  |       8.99 |
  9. | Bears R Us      | 18 inch teddy bear  |      11.99 |
  10. | Doll House Inc. | Fish bean bag toy   |       3.49 |
  11. | Doll House Inc. | Bird bean bag toy   |       3.49 |
  12. | Doll House Inc. | Rabbit bean bag toy |       3.49 |
  13. | Doll House Inc. | Raggedy Ann         |       4.99 |
  14. | Fun and Games   | King doll           |       9.49 |
  15. | Fun and Games   | Queen doll          |       9.49 |
  16. +-----------------+---------------------+------------+
  17. 9 rows in set (0.00 sec)

解释:

  • SELECT vend_name, prod_name, prod_price 指定检索的列，prod_name, prod_price 在同一个表。vend_name 在另外一个表
  • From 指定联结两个表Vendors, Products

  • WHERE子句限定 Vendors.vend_id = Products.vend_id 完全限定名。

    WHERE子句的重要

  • 笛卡儿积(cartesian product) 由没有联结的条件表关系返回的结果，
    保证所有联结都有WHERE子句，否则返回比的数据会比想要的数据多很多。

  2. mysql> SELECT vend_name, prod_name, prod_price
  3.     -> FROM Vendors, Products;
  4. +-----------------+---------------------+------------+
  5. | vend_name       | prod_name           | prod_price |
  6. +-----------------+---------------------+------------+
  7. | Bear Emporium   | Fish bean bag toy   |       3.49 |
  8. | Bears R Us      | Fish bean bag toy   |       3.49 |
  9. | Doll House Inc. | Fish bean bag toy   |       3.49 |
  10. | Fun and Games   | Fish bean bag toy   |       3.49 |
  11. | Furball Inc.    | Fish bean bag toy   |       3.49 |
  12. | Jouets et ours  | Fish bean bag toy   |       3.49 |
  13. | Bear Emporium   | Bird bean bag toy   |       3.49 |
  14. | Bears R Us      | Bird bean bag toy   |       3.49 |
  15. | Doll House Inc. | Bird bean bag toy   |       3.49 |
  16. | Fun and Games   | Bird bean bag toy   |       3.49 |
  17. | Furball Inc.    | Bird bean bag toy   |       3.49 |
  18. | Jouets et ours  | Bird bean bag toy   |       3.49 |
  19. | Bear Emporium   | Rabbit bean bag toy |       3.49 |
  20. | Bears R Us      | Rabbit bean bag toy |       3.49 |
  21. | Doll House Inc. | Rabbit bean bag toy |       3.49 |
  22. | Fun and Games   | Rabbit bean bag toy |       3.49 |
  23. | Furball Inc.    | Rabbit bean bag toy |       3.49 |
  24. | Jouets et ours  | Rabbit bean bag toy |       3.49 |
  25. | Bear Emporium   | 8 inch teddy bear   |       5.99 |
  26. | Bears R Us      | 8 inch teddy bear   |       5.99 |
  27. | Doll House Inc. | 8 inch teddy bear   |       5.99 |
  28. | Fun and Games   | 8 inch teddy bear   |       5.99 |
  29. | Furball Inc.    | 8 inch teddy bear   |       5.99 |
  30. | Jouets et ours  | 8 inch teddy bear   |       5.99 |
  31. | Bear Emporium   | 12 inch teddy bear  |       8.99 |
  32. | Bears R Us      | 12 inch teddy bear  |       8.99 |
  33. | Doll House Inc. | 12 inch teddy bear  |       8.99 |
  34. | Fun and Games   | 12 inch teddy bear  |       8.99 |
  35. | Furball Inc.    | 12 inch teddy bear  |       8.99 |
  36. | Jouets et ours  | 12 inch teddy bear  |       8.99 |
  37. | Bear Emporium   | 18 inch teddy bear  |      11.99 |
  38. | Bears R Us      | 18 inch teddy bear  |      11.99 |
  39. | Doll House Inc. | 18 inch teddy bear  |      11.99 |
  40. | Fun and Games   | 18 inch teddy bear  |      11.99 |
  41. | Furball Inc.    | 18 inch teddy bear  |      11.99 |
  42. | Jouets et ours  | 18 inch teddy bear  |      11.99 |
  43. | Bear Emporium   | Raggedy Ann         |       4.99 |
  44. | Bears R Us      | Raggedy Ann         |       4.99 |
  45. | Doll House Inc. | Raggedy Ann         |       4.99 |
  46. | Fun and Games   | Raggedy Ann         |       4.99 |
  47. | Furball Inc.    | Raggedy Ann         |       4.99 |
  48. | Jouets et ours  | Raggedy Ann         |       4.99 |
  49. | Bear Emporium   | King doll           |       9.49 |
  50. | Bears R Us      | King doll           |       9.49 |
  51. | Doll House Inc. | King doll           |       9.49 |
  52. | Fun and Games   | King doll           |       9.49 |
  53. | Furball Inc.    | King doll           |       9.49 |
  54. | Jouets et ours  | King doll           |       9.49 |
  55. | Bear Emporium   | Queen doll          |       9.49 |
  56. | Bears R Us      | Queen doll          |       9.49 |
  57. | Doll House Inc. | Queen doll          |       9.49 |
  58. | Fun and Games   | Queen doll          |       9.49 |
  59. | Furball Inc.    | Queen doll          |       9.49 |
  60. | Jouets et ours  | Queen doll          |       9.49 |
  61. +-----------------+---------------------+------------+
  62. 54 rows in set (0.00 sec

上面的例子包含很多，不正确的数据。

内部联结

基于两边直接的相对测试，称为等值联结(euqijoin) 

  1. mysql> SELECT vend_name, prod_name, prod_price
  2.     -> FROM Vendors INNER JOIN Products
  3.     -> ON Vendors.vend_id = Products.vend_id;
  4. +-----------------+---------------------+------------+
  5. | vend_name       | prod_name           | prod_price |
  6. +-----------------+---------------------+------------+
  7. | Bears R Us      | 8 inch teddy bear   |       5.99 |
  8. | Bears R Us      | 12 inch teddy bear  |       8.99 |
  9. | Bears R Us      | 18 inch teddy bear  |      11.99 |
  10. | Doll House Inc. | Fish bean bag toy   |       3.49 |
  11. | Doll House Inc. | Bird bean bag toy   |       3.49 |
  12. | Doll House Inc. | Rabbit bean bag toy |       3.49 |
  13. | Doll House Inc. | Raggedy Ann         |       4.99 |
  14. | Fun and Games   | King doll           |       9.49 |
  15. | Fun and Games   | Queen doll          |       9.49 |
  16. +-----------------+---------------------+------------+
  17. 9 rows in set (0.01 sec)

联结多个表

先列出所有列，再定义表之间的关系。

  1. mysql> SELECT prod_name, vend_name, prod_price, quantity
  2.     -> FROM OrderItems, Products, Vendors
  3.     -> WHERE Products.vend_id = Vendors.vend_id
  4.     -> AND OrderItems.prod_id = Products.prod_id
  5.     -> AND order_num = 20007;
  6. +---------------------+-----------------+------------+----------+
  7. | prod_name           | vend_name       | prod_price | quantity |
  8. +---------------------+-----------------+------------+----------+
  9. | 18 inch teddy bear  | Bears R Us      |      11.99 |       50 |
  10. | Fish bean bag toy   | Doll House Inc. |       3.49 |      100 |
  11. | Bird bean bag toy   | Doll House Inc. |       3.49 |      100 |
  12. | Rabbit bean bag toy | Doll House Inc. |       3.49 |      100 |
  13. | Raggedy Ann         | Doll House Inc. |       4.99 |       50 |
  14. +---------------------+-----------------+------------+----------+
  15. 5 rows in set (0.00 sec)

返回订购产品RGAN01的客户列表

  1. mysql> SELECT cust_name, cust_contact
  2.     -> FROM Customers
  3.     -> WHERE cust_id IN (SELECT cust_id
  4.     -> FROM Orders
  5.     -> WHERE order_num IN (SELECT order_num
  6.     -> FROM OrderItems
  7.     -> WHERE prod_id = 'RGAN01'));
  8. +---------------+--------------------+
  9. | cust_name     | cust_contact       |
  10. +---------------+--------------------+
  11. | Fun4All       | Denise L. Stephens |
  12. | The Toy Store | Kim Howard         |
  13. +---------------+--------------------+
  14. 2 rows in set (0.00 sec)

下面使用联结查询

  1. mysql> SELECT cust_name, cust_contact
  2.     -> FROM Customers, Orders, OrderItems
  3.     -> WHERE Customers.cust_id = Orders.cust_id
  4.     -> AND OrderItems.order_num = Orders.order_num
  5.     -> AND prod_id = 'RGAN01';
  6. +---------------+--------------------+
  7. | cust_name     | cust_contact       |
  8. +---------------+--------------------+
  9. | Fun4All       | Denise L. Stephens |
  10. | The Toy Store | Kim Howard         |
  11. +---------------+--------------------+
  12. 2 rows in set (0.00 sec)

解释:返回的数据需要使用3个表，三个WHERE子句，最后过滤出RGAN01产品的数据

原文: https://cxiaodian.gitbooks.io/mysql/content/chapter11.html