第12课、联结表
联结表
使用联结:之前的 SELECT 查询某些列,只是针对一张表进行检索的。(即 FROM 表名)。但是如果用户想要使用 SELECT 对存储在不同表中的某些列时,则需要使用联结表的方式来实现。
例子中有建立两个表:
- Vendors 表:存储供应商信息,每个供应商具有唯一的标识,为主键。
- Products 表:存储产品信息。还存储供应商 ID(Vendors 表的主键)。
如果既想要在 Vendors 表中查询 vend_name ,又想要在 Products 表中查询 prod_name 和 pro_price,则使用联结表的方式来处理。
1)创建联结 |
需求:如果两个表中存在相同的供应商ID,则输出其所有的供应商名称,产品名成,产品价格。
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
2)WHERE 子句的重要性 |
假如执行如下内容:
-- 去掉了 WHERE 子句判断条件:
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;
-- 第一个表中的每一行将与第二表中的每一行配对,而不管它们是否能配在一起。
-- 由于查询的列没有判断共同字段值的条件,将会以输出各自的结果相结合在一起。
-- A 表中有2个列a值,B 表中有3个列b值,则输出结果有可能为:
a b
------
a1 b1
a1 b2
a1 b3
a2 b1
a2 b2
a2 b3
--WHERE 有共同列的判断条件,才能在两张表中一一配对。
3)内联结 |
-- 语法1 和语法2,执行结果一致
--语法1
SELECT vend_name, prod_name, prod_price
FROM Vendors
INNER JOIN Products ON
Vendors.vend_id = Products.vend_id;
-- 语法2
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
4)联结多个表 |
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;
5)不同SELECT 语句执行相同的结果 |
需求:检索订购物品 RGAN01 的所有顾客。
-- 在 之前子查询中使用:
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN(SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'));
-- 在联结表中使用:
SELECT cust_name,cust_contact
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';
输出结果:
cust_name cust_contact
Fun4All Denise L. Stephens
The Toy Store Kim Howard
举一反三
-- 如下内容是否可以转成联结表的方式:
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
-- 目前尝试了一遍,发现不可以(不知道能不能解决这个问题):
-- 我的转成后是这样的:
SELECT cust_name, cust_state,COUNT(Customers.cust_id) as orders
FROM Customers,Orders
WHERE Customers.cust_id = Orders.cust_id
group by cust_name,cust_state;
总结
联结表
SELECT 表1列, 表2列...
FROM 表1, 表2...
WHERE 有共同列名的过滤条件;
另外语法:
SELECT 表1列, 表2列...
FROM 表1
INNER JOIN 表2.. ON
有共同列名的过滤条件;
联结表的性能
联结的每一张表,都需要耗费相应的资源。联结的表越多,性能就越下降。