MySQL联结表

创建联结

select vend_name, prod_name, prod_price 
from vendors, products 
where vendors.vend_id = products.vend_id 
order by vend_name, prod_name;

内部联结=where 会过滤掉空数据

select vend_name, prod_name, prod_price 
from vendors inner join products 
on vendors.vend_id = products.vend_id;

联结多个表：显示编号为20005的订单中的物品

select vend_name, prod_name, prod_price, quantity 
from vendors, products, orderitems
where products.vend_id = vendors.vend_id 
and orderitems.prod_id = products.prod_id
and order_num = 20005;

另外，

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 = 'TNT2'));

可以写为

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 = 'TNT2';

使用表别名：

select cust_name,cust_contact
from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id
and oi.order_num=o.order_num
and prod_id = 'TNT2'

自联结：查询生产物品ID为DTNTR的供应商生产的产品

select p1.prod_id, p1.prod_name 
from products as p1, products as p2 
where p1.vend_id = p2.vend_id 
and p2.prod_id = 'DTNTR';

select prod_id, prod_name 
from products 
where vend_id = (select vend_id 
				 from products 
				 where prod_id = 'DTNTR');

自然联结

select c.*, o.order_num, o.order_date, oi.prod_id, oi.quantity, oi.item_price
from customers as c, orders as o, orderitems as oi
where c.cust_id = o.cust_id
and oi.order_num = o.order_num
and prod_id = 'FB';

外部联结

select customers.cust_id, orders.order_num 
from customers left outer join orders 
on customers.cust_id = orders.cust_id;

带聚集函数的联结

select customers.cust_name, customers.cust_id, count(orders.order_num) as num_ord 
from customers inner join orders 
on customers.cust_id = orders.cust_id 
group by customers.cust_id;

select customers.cust_name, customers.cust_id, count(orders.order_num) as num_ord 
from customers left outer join orders 
on customers.cust_id = orders.cust_id 
group by customers.cust_id;