SQL 必知必会第十二课 联结表
## 创建链接
select vend_name, prod_name, Prod_price
from vendors, products
where vendors.vend_id = Products.vend_id; -- 此处的where就是作为联结的工具,将两个表配对
select vend_name, prod_name, Prod_price
from vendors
inner join products on vendors.vend_id = products.vend_id;
##连接多个表
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;
select * from orderitems;
## challenges
select cust_name, order_num
from customers
inner join orders on customers.cust_id = orders.cust_id
order by order_num ; -- 1
select cust_name, orders.order_num, (quantity * item_price) as OrderTotal
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orders.order_num = orderitems.order_num
order by order_num ; -- 2
select cust_id, order_date
from orders, orderitems
where orders.order_num = orderitems.order_num
and prod_id = 'BR01'; -- 3
select cust_email
from customers
inner join orders on customers.cust_id = orders.cust_id
where customers.cust_id in(select orders.cust_id
from orders
inner join OrderItems on orders.order_num and OrderItems.order_num
where prod_id = 'BR01')
group by cust_email; -- 4
select cust_name
from customers
inner join orders on customers.cust_id = orders.cust_id
where customers.cust_id in(select orders.cust_id
from orders
inner join OrderItems on orders.order_num and OrderItems.order_num
where (orderitems.quantity * OrderItems.item_price) >= 1000)
group by cust_name
order by cust_name; -- 5, 用having的做不出来。。
注意点:
- 运用关系数据库的可伸缩性比非关系数据要好。
可伸缩性:能够适应不断增加的工作量而不失败。
- 当引用出现歧义时,必须要用完全限定语句。