19、MySQL的联结表

外键包含另一个表的主键值
1、如何联结多个关联表 jion语句

mysql> select vend_name, prod_id, prod_name from vendors, products where vendors.vend_id = products.vend_id;
+-------------+---------+----------------+
| vend_name   | prod_id | prod_name      |
+-------------+---------+----------------+
| Anvils R Us | ANV01   | .5 ton anvil   |
| Anvils R Us | ANV02   | 1 ton anvil    |
| Anvils R Us | ANV03   | 2 ton anvil    |
| LT Supplies | FU1     | Fuses          |
| LT Supplies | OL1     | Oil can        |
| ACME        | DTNTR   | Detonator      |
| ACME        | FB      | Bird seed      |
| ACME        | FC      | Carrots        |
| ACME        | SAFE    | Safe           |
| ACME        | SLING   | Sling          |
| ACME        | TNT1    | TNT (1 stick)  |
| ACME        | TNT2    | TNT (5 sticks) |
| Jet Set     | JP1000  | JetPack 1000   |
| Jet Set     | JP2000  | JetPack 2000   |
+-------------+---------+----------------+

简化模式 inner join内联结 标准默认的连接方式

mysql> select v.vend_name, p.prod_id, p.prod_name from vendors v, products p where v.vend_id = p.vend_id;
+-------------+---------+----------------+
| vend_name   | prod_id | prod_name      |
+-------------+---------+----------------+
| Anvils R Us | ANV01   | .5 ton anvil   |
| Anvils R Us | ANV02   | 1 ton anvil    |
| Anvils R Us | ANV03   | 2 ton anvil    |
| LT Supplies | FU1     | Fuses          |
| LT Supplies | OL1     | Oil can        |
| ACME        | DTNTR   | Detonator      |
| ACME        | FB      | Bird seed      |
| ACME        | FC      | Carrots        |
| ACME        | SAFE    | Safe           |
| ACME        | SLING   | Sling          |
| ACME        | TNT1    | TNT (1 stick)  |
| ACME        | TNT2    | TNT (5 sticks) |
| Jet Set     | JP1000  | JetPack 1000   |
| Jet Set     | JP2000  | JetPack 2000   |
+-------------+---------+----------------+

mysql> select v.vend_name, p.prod_id, p.prod_name from vendors v, products p where v.vend_id = p.vend_id and v.vend_id = 1003;
+-----------+---------+----------------+
| vend_name | prod_id | prod_name      |
+-----------+---------+----------------+
| ACME      | DTNTR   | Detonator      |
| ACME      | FB      | Bird seed      |
| ACME      | FC      | Carrots        |
| ACME      | SAFE    | Safe           |
| ACME      | SLING   | Sling          |
| ACME      | TNT1    | TNT (1 stick)  |
| ACME      | TNT2    | TNT (5 sticks) |
+-----------+---------+----------------+

多表连接

mysql> select c.cust_name, o.order_num, oi.prod_id from customers c, orders o, orderitems oi where c.cust_id = o.cust_id and o.order_num = oi.order_num;
+----------------+-----------+---------+
| cust_name      | order_num | prod_id |
+----------------+-----------+---------+
| Coyote Inc.    |     20005 | ANV01   |
| Coyote Inc.    |     20005 | ANV02   |
| Coyote Inc.    |     20005 | TNT2    |
| Coyote Inc.    |     20005 | FB      |
| Coyote Inc.    |     20009 | FB      |
| Coyote Inc.    |     20009 | OL1     |
| Coyote Inc.    |     20009 | SLING   |
| Coyote Inc.    |     20009 | ANV03   |
| Wascals        |     20006 | JP2000  |
| Yosemite Place |     20007 | TNT2    |
| E Fudd         |     20008 | FC      |
+----------------+-----------+---------+

mysql> select c.cust_name, o.order_num, oi.prod_id from customers c, orders o, orderitems oi where c.cust_id = o.cust_id and o.order_num = oi.order_num and oi.prod_id = 'TNT2';
+----------------+-----------+---------+
| cust_name      | order_num | prod_id |
+----------------+-----------+---------+
| Coyote Inc.    |     20005 | TNT2    |
| Yosemite Place |     20007 | TNT2    |
+----------------+-----------+---------+