SQL 联结表
一、联结:
- SQL最强大的功能之一就是能在
数据查询的执行中联结(join)表。
- 联结是利用SQL的SELECT能执行的最重要的操作,很好地理解联结及其语法学习SQL的极为重要的部分。
1.1:关系表:
- 关系表的设计:
- 关系表的设计就是要把信息分解成多个表,一类数据一个表。名表通过某些共同的值互相关联(所以才叫关系数据库)。
- 关系表的优点:
- 可伸缩性要比非关系数据库要好。
- 可伸缩:能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好。
- 可伸缩性要比非关系数据库要好。
1.2:为什么使用联结:
- 上面所述,将数据分解为多个表能更有效地存储,更方便地处理,并且可伸缩性更好。但这些好处是有代价的。
- 如果数据存储再多个表中,怎样用一条SELECT语句就检索出数据呢?
- 答案是
使用联结
。- 联结是一种机制,用来再一条SELECT语句中关联表,因此称为联结。
- 使用特殊语法,可以联结多个表返回一组输出,联结再运行时关联表中正确的行。
- 说明:使用交互式DBMS工具
- 要理解
联结不是物理实体
。它实际的数据库表中并不存在。DBMS会根据需要建立联结,它再查询执行期间一直存在。
- 要理解
二、创建联结:
-
首先看Vendors表和Products表有哪些数据:
mysql> SELECT * FROM Vendors; +---------+-----------+--------------+------------+----------+--------------+ | vend_id | vend_name | vend_address | vend_city | vend_zip | vend_country | +---------+-----------+--------------+------------+----------+--------------+ | BRE02 | Bear Empo | 500 Park Str | Anytown | 44333 | USA | | BRS01 | Bears R U | 123 Main Str | Bear Town | 44444 | USA | | DLL01 | Doll Hous | 555 High Str | Dollsville | 99999 | USA | | FNG01 | Fun and G | 42 Galaxy Ro | London | N16 6PS | England | | FRB01 | Furball I | 1000 5th Ave | New York | 11111 | USA | | JTS01 | Jouets et | 1 Rue Amusem | Paris | 45678 | France | +---------+-----------+--------------+------------+----------+--------------+ 6 rows in set (0.00 sec)
mysql> SELECT * FROM products; +---------+---------+------------+------------+--------------------------------+ | prod_id | vend_id | prod_name | prod_price | prod_desc | +---------+---------+------------+------------+--------------------------------+ | BNBG01 | DLL01 | Fish bean | 3.49 | Fish bean bean bag worms with | | BNBG02 | DLL01 | Bird bean | 3.49 | Bird beanincluded | | BNBG03 | DLL01 | Rabbit bey | 3.49 | Rabbit bebean bag carrots | | BR01 | BRS01 | 8 inch te | 5.99 | 8 inch tep and jacket | | BR02 | BRS01 | 12 inch t | 8.99 | 12 inch tap and jacket | | BR03 | BRS01 | 18 inch t | 11.99 | 18 inch tap and jacket | | RGAN01 | DLL01 | Raggedy A | 4.99 | 18 inch R | | RYL01 | FNG01 | King doll | 9.49 | 12 inch kments and crown | | RYL02 | FNG01 | Queen dol | 9.49 | 12 inch qrments and crown | +---------+---------+------------+------------+---------------------------------
-
创建联结非常简单,指定要联结的所有表以及关联它们的方式:
SELECT vend_name, prod_name, prod_price FROM Vendors, Products WHERE Vendors.vend_id = Products.vend_id;
+-----------------+---------------------+------------+ | vend_name | prod_name | prod_price | +-----------------+---------------------+------------+ | Doll House Inc. | Fish bean bag toy | 3.49 | | Doll House Inc. | Bird bean bag toy | 3.49 | | Doll House Inc. | Rabbit bean bag toy | 3.49 | | Bears R Us | 8 inch teddy bear | 5.99 | | Bears R Us | 12 inch teddy bear | 8.99 | | Bears R Us | 18 inch teddy bear | 11.99 | | Doll House Inc. | Raggedy Ann | 4.99 | | Fun and Games | King doll | 9.49 | | Fun and Games | Queen doll | 9.49 | +-----------------+---------------------+------------+ 9 rows in set (0.00 sec)
- SELECT 语句与前面所有语句一样指定要检索的列。
- 这里最大的差别是指定的两列(prod_name和prod_price)再一个表中,而第三列(vend_name)再另一个表中。
- 现在来看FROM子句。与以前的SELECT语句不一样,
这条语句的FROM子句列出了两个表的名字
。 - 这两个表用WHERE子句正确地联结,
WHERE子句指示DBMS将Vend_id与Products表中的vend_id匹配起来。
- 这里需要这种完全限定列名,如果只给出vend_id,DBMS就不知道指的哪一个(每个表中有一个)。
- 注意:完全限定列名
再引用的列可能出现歧义时,必须使用完全限定列名(用一个句点分隔表名和列名)。如果引用一个没有用表名限制的具有歧义的列名,大多数DBMS会返回错误。
2.1:WHERE子句的重要性:
-
使用WHERE子句建立联结关系似乎有点奇怪,但实际上是有个很充分的理由。
-
再一条SELECT语句中联结几个表时,相应的关系是再运行中构成的。再数据库表的定义中没有指示DBMS如何对表进行联结的内容。
-
再联结两个表时,实际要做的将第一表中的每一行与第二个表中的每一行
配对
。-
WHERE子句作为过滤条件,只包含那些
匹配给定条件(这里是联结条件)的行
。 -
没有WHERE子句,第一个表中的每一行将与第二个表中的每一行配对,而不管它们逻辑上是否能配在一起。
-
笛卡儿积:
- 由没有联结条件的表关系返回的结果为笛卡儿积。
检索出的行数目将是第一个表中的行数乘以第二个表中的行数。
- 返回笛卡儿积的联结,也称为叉联结。
SELECT vend_name, prod_name, prod_price FROM Vendors, Products;
+-----------------+---------------------+------------+ | vend_name | prod_name | prod_price | +-----------------+---------------------+------------+ | Bear Emporium | Fish bean bag toy | 3.49 | | Bears R Us | Fish bean bag toy | 3.49 | | Doll House Inc. | Fish bean bag toy | 3.49 | | Fun and Games | Fish bean bag toy | 3.49 | | Furball Inc. | Fish bean bag toy | 3.49 | | Jouets et ours | Fish bean bag toy | 3.49 | | Bear Emporium | Bird bean bag toy | 3.49 | | Bears R Us | Bird bean bag toy | 3.49 | | Doll House Inc. | Bird bean bag toy | 3.49 | | Fun and Games | Bird bean bag toy | 3.49 | | Furball Inc. | Bird bean bag toy | 3.49 | | Jouets et ours | Bird bean bag toy | 3.49 | | Bear Emporium | Rabbit bean bag toy | 3.49 | | Bears R Us | Rabbit bean bag toy | 3.49 | | Doll House Inc. | Rabbit bean bag toy | 3.49 | | Fun and Games | Rabbit bean bag toy | 3.49 | | Furball Inc. | Rabbit bean bag toy | 3.49 | | Jouets et ours | Rabbit bean bag toy | 3.49 | | Bear Emporium | 8 inch teddy bear | 5.99 | | Bears R Us | 8 inch teddy bear | 5.99 | | Doll House Inc. | 8 inch teddy bear | 5.99 | | Fun and Games | 8 inch teddy bear | 5.99 | | Furball Inc. | 8 inch teddy bear | 5.99 | | Jouets et ours | 8 inch teddy bear | 5.99 | | Bear Emporium | 12 inch teddy bear | 8.99 | | Bears R Us | 12 inch teddy bear | 8.99 | | Doll House Inc. | 12 inch teddy bear | 8.99 | | Fun and Games | 12 inch teddy bear | 8.99 | | Furball Inc. | 12 inch teddy bear | 8.99 | | Jouets et ours | 12 inch teddy bear | 8.99 | | Bear Emporium | 18 inch teddy bear | 11.99 | | Bears R Us | 18 inch teddy bear | 11.99 | | Doll House Inc. | 18 inch teddy bear | 11.99 | | Fun and Games | 18 inch teddy bear | 11.99 | | Furball Inc. | 18 inch teddy bear | 11.99 | | Jouets et ours | 18 inch teddy bear | 11.99 | | Bear Emporium | Raggedy Ann | 4.99 | | Bears R Us | Raggedy Ann | 4.99 | | Doll House Inc. | Raggedy Ann | 4.99 | | Fun and Games | Raggedy Ann | 4.99 | | Furball Inc. | Raggedy Ann | 4.99 | | Jouets et ours | Raggedy Ann | 4.99 | | Bear Emporium | King doll | 9.49 | | Bears R Us | King doll | 9.49 | | Doll House Inc. | King doll | 9.49 | | Fun and Games | King doll | 9.49 | | Furball Inc. | King doll | 9.49 | | Jouets et ours | King doll | 9.49 | | Bear Emporium | Queen doll | 9.49 | | Bears R Us | Queen doll | 9.49 | | Doll House Inc. | Queen doll | 9.49 | | Fun and Games | Queen doll | 9.49 | | Furball Inc. | Queen doll | 9.49 | | Jouets et ours | Queen doll | 9.49 | +-----------------+---------------------+------------+ 54 rows in set (0.00 sec)
- 由没有联结条件的表关系返回的结果为笛卡儿积。
-
从上面的输出可以看到,相应的笛卡儿积不是我们想要的。这里返回的数据用每个供应商匹配了每个产品,包括了供应商不正确的产品。
-
-
注意:不要忘了WHERE子句
- 要保证所有联结都有WHERE子句,否则DBMS将返回比想要的数据多得多的数据。
-
2.2:内联结:
-
目前为止使用的联结称为
等值联结,它基于两个表之间的相等测试
。这种联结也成为内联结(inner join)
-
首先看一下Vendors表和Products表都有哪些数据?
SELECT * FROM Products; +---------+---------+------------+------------+--------------------------------+ | prod_id | vend_id | prod_name | prod_price | prod_desc | +---------+---------+------------+------------+--------------------------------+ | BNBG01 | DLL01 | Fish bean | 3.49 | Fish bean bean bag worms with | | BNBG02 | DLL01 | Bird bean | 3.49 | Bird beanincluded | | BNBG03 | DLL01 | Rabbit bey | 3.49 | Rabbit bebean bag carrots | | BR01 | BRS01 | 8 inch te | 5.99 | 8 inch tep and jacket | | BR02 | BRS01 | 12 inch t | 8.99 | 12 inch tap and jacket | | BR03 | BRS01 | 18 inch t | 11.99 | 18 inch tap and jacket | | RGAN01 | DLL01 | Raggedy A | 4.99 | 18 inch R | | RYL01 | FNG01 | King doll | 9.49 | 12 inch kments and crown | | RYL02 | FNG01 | Queen dol | 9.49 | 12 inch qrments and crown | +---------+---------+------------+------------+---------------------------------
mysql> SELECT * FROM Vendors; +---------+-----------+--------------+------------+----------+--------------+ | vend_id | vend_name | vend_address | vend_city | vend_zip | vend_country | +---------+-----------+--------------+------------+----------+--------------+ | BRE02 | Bear Empo | 500 Park Str | Anytown | 44333 | USA | | BRS01 | Bears R U | 123 Main Str | Bear Town | 44444 | USA | | DLL01 | Doll Hous | 555 High Str | Dollsville | 99999 | USA | | FNG01 | Fun and G | 42 Galaxy Ro | London | N16 6PS | England | | FRB01 | Furball I | 1000 5th Ave | New York | 11111 | USA | | JTS01 | Jouets et | 1 Rue Amusem | Paris | 45678 | France | +---------+-----------+--------------+------------+----------+--------------+ 6 rows in set (0.00 sec)
-
下面使用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 | +-----------------+---------------------+------------+ | Doll House Inc. | Fish bean bag toy | 3.49 | | Doll House Inc. | Bird bean bag toy | 3.49 | | Doll House Inc. | Rabbit bean bag toy | 3.49 | | Bears R Us | 8 inch teddy bear | 5.99 | | Bears R Us | 12 inch teddy bear | 8.99 | | Bears R Us | 18 inch teddy bear | 11.99 | | Doll House Inc. | Raggedy Ann | 4.99 | | Fun and Games | King doll | 9.49 | | Fun and Games | Queen doll | 9.49 | +-----------------+---------------------+------------+ 9 rows in set (0.00 sec)
- 语句中的SELECT与前面SELECT语句相同,但FROM子句不同。
两个表之间的关系是以INNER JOIN指定的部分FROM子句。联结条件用特定的ON子句而不是WHERE子句给出
- 传递给ON的实际条件与传递给WHERE的相同
- 语句中的SELECT与前面SELECT语句相同,但FROM子句不同。
-
提示:应该首选INNER JOIN 语法
- SQL规范首选
INNER JOIN
语法,之前使用的简单的等值语法
。SQL语言纯正论者是用鄙视的眼光看待简单语法的。还是建议大家使用INNER JOIN
- 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 = 20007;
+---------------------+-----------------+------------+----------+ | prod_name | vend_name | prod_price | quantity | +---------------------+-----------------+------------+----------+ | 18 inch teddy bear | Bears R Us | 11.99 | 50 | | Fish bean bag toy | Doll House Inc. | 3.49 | 100 | | Bird bean bag toy | Doll House Inc. | 3.49 | 100 | | Rabbit bean bag toy | Doll House Inc. | 3.49 | 100 | | Raggedy Ann | Doll House Inc. | 4.99 | 50 | +---------------------+-----------------+------------+----------+ 5 rows in set (0.01 sec)
-
分析一下这个栗子:
- 显示订单20007中的物品。
- 订单物品存储再OrderItems表中。
- 每个产品按其产品ID存储,它引用Products表中的产品。
- 这些产品通过供应商ID联结到Vendors表中相应的供应商,供应商ID存储在每个产品的记录中。
- 这里的FROM子句列出三个表,WHERE子句定义这两个联结条件,而第三个联结条件用来过滤出订单20007中的物品。
-
注意:性能考虑
- DBMS再运行时关联指定的每个表,以处理联结。
这种处理可能非常耗费资源,因此应该注意,不要联结不必要的表。联结的表越多,性能下降越厉害
。
- DBMS再运行时关联指定的每个表,以处理联结。
-