MySQL学习足迹记录13--联结表--INNER JOIN...ON_MySQL

bitsCN.com

MySQL学习足迹记录13--联结表--INNER JOIN...ON

 

  学数据库联结表这一节内容几乎吐血，看书上的介绍和百度到的内容，总感到一脸茫然.....也许是我理解能力太差了吧。但我是一个不会轻易放弃的人，研究了半小时，终于搞明白了联结的原理，自我总结如下：关键在于“select语句，如果没有WHERE子句限定，将返回两个表的笛卡尔积.”

 

Example:    表一： Kemu+-----+--------+| kno | kname  |+-----+--------+| b   | yuwen  || b1  | shuxue |+-----+--------+表二： Chenji；+------+------+---------+| sno  | kno  | chengji |+------+------+---------+|    1 | a    |      10 ||    2 | a1   |      20 |+------+------+---------+2 rows in set (0.00 sec)*重点：select语句，如果没有WHERE子句限定，将返回两个表的笛卡尔积.mysql> SELECT * FROM Kemu,Chenji;+-----+--------+------+------+---------+| kno | kname  | sno  | kno  | chengji |+-----+--------+------+------+---------+| b   | yuwen  |    1 | a    |      10 || b1  | shuxue |    1 | a    |      10 || b   | yuwen  |    2 | a1   |      20 || b1  | shuxue |    2 | a1   |      20 |+-----+--------+------+------+---------+4 rows in set (0.00 sec)

 

 

 Tips:SELECT 语句后面的所有表将会合成一个大表，所以，SELECT后所跟的各种子句就相当于在一个表中筛选数据了。

 

 

1.外键：某个表的一列，它包含另一个表的主键值，定义了两个表之间的关系

 

2. 联结不是物理实体，它在实际的数据库表中不存在，它存在与查询执行当中

 

3.创建联结

  Example:  mysql> SELECT vend_name,prod_name,prod_price            -> FROM vendors,products           -> WHERE vendors.vend_id = products.vend_id        #WHERE子句指示MySQL匹配vendors表的             -> ORDER BY vend_name,prod_name;                     #vend_id和products表中的vend_id.+-------------+----------------+------------+| 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 |+-------------+----------------+------------+14 rows in set (0.01 sec)

 

   

TIPS:

   *WHERE联结两个表时，实际是将第一个表中的每一行和第二个表中的每一行配对.并过滤不符合条件的行.

 

4.内部联结INNER JOIN .... ON

  Example:  mysql> SELECT vend_name,prod_name,prod_price           -> FROM vendors INNER JOIN products                     #使用明确的联结语法能狗确保不会忘记联结条件           -> ON vendors.vend_id = products.vend_id                #联结条件用特定的ON子句，           -> ORDER BY vend_name,prod_name;                        #传递的实际条件与传递给WHERE的相同+-------------+----------------+------------+| 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 |+-------------+----------------+------------+14 rows in set (0.00 sec)

 

5.联结多个表

   *SQL对一条SELECT语句中可以联结的表的数目没有限制。

   Example:    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 |+----------------+-------------+------------+----------+4 rows in set (0.00 sec)

 

bitsCN.com