Day-11:联结表

1、联结表
联结表是利用SQL的select执行的重要操作。
可伸缩：能够适应不断增加的工作量而不失败，设计良好的数据库或者应用程序称为可伸缩性好。
联结是一种机制，用一条select语句关联表，可以联结多个表返回一组输出。
2、创建联结表
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
*/

分析：
vendors的第一行去匹配products的满足where条件每一行。

3、内联结
select vend_name, prod_name, prod_price

from vendors inner join products

on 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
*/
分析：效果第一个例子一样
4、联结多个表
例子：找出订单20007的所物品
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;

/*
prod_name, vend_name, prod_price, quantity

18 inch teddy bear Bears R Us 11.99 50
Fish bean bag toy Doll House Inc. 3.49 100
Bird bean bag toy Doll House Inc. 3.49 100
Rabbit bean bag toy Doll House Inc. 3.49 100
Raggedy Ann Doll House Inc. 4.99 50

*/

例子：子查询例子改进
select cust_name, cust_contact

from customers, orders, orderitems

where orderitems.order_num = orders.order_num

and orders.cust_id = customers.cust_id

and prod_id = 'RGAN01';

/*
cust_name, cust_contact

Fun4All Denise L. Stephens
The Toy Store Kim Howard
*/