表联结
表关系分为一对一、一对多、多对多三种。
表联结:在一条select语句中联结多个表。
案例:现在有两个表:vendors、products分别存储 供应商信息、产品信息。
如何查询所有的产品及其供应商信息?
运用表联结:
select prod_id, vend_name from vendors,products where vendors.vend_id = products.vend_id;
+---------+-------------+
| prod_id | vend_name |
+---------+-------------+
| ANV01 | Anvils R Us |
| ANV02 | Anvils R Us |
| ANV03 | Anvils R Us |
| FU1 | LT Supplies |
| OL1 | LT Supplies |
| DTNTR | ACME |
| FB | ACME |
| FC | ACME |
| SAFE | ACME |
| SLING | ACME |
| TNT1 | ACME |
| TNT2 | ACME |
| JP1000 | Jet Set |
| JP2000 | Jet Set |
+---------+-------------+
14 rows in set (0.00 sec)
还可以使用join(或者是inner join):
select prod_id, vend_name from vendors inner join products on vendors.vend_id = products.vend_id;
+---------+-------------+
| prod_id | vend_name |
+---------+-------------+
| ANV01 | Anvils R Us |
| ANV02 | Anvils R Us |
| ANV03 | Anvils R Us |
| FU1 | LT Supplies |
| OL1 | LT Supplies |
| DTNTR | ACME |
| FB | ACME |
| FC | ACME |
| SAFE | ACME |
| SLING | ACME |
| TNT1 | ACME |
| TNT2 | ACME |
| JP1000 | Jet Set |
| JP2000 | Jet Set |
+---------+-------------+
14 rows in set (0.00 sec)
如何查询产品价格信息及其供应商信息?
select item_price, products.prod_id, vend_name
from orderitems, products,vendors
where orderitems.prod_id = products.prod_id
and
products.vend_id = vendors.vend_id;
+------------+---------+-------------+
| item_price | prod_id | vend_name |
+------------+---------+------