外键包含另一个表的主键值
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 |
+----------------+-----------+---------+