mysql必会知识之两个表以两个表中都存在的关系字段进行联结、两个表不以关系字段进行联结
主键(该表的唯一值,如id)
外键(是另外一个表的主键值,在此表中就叫外键)
第15章------联结
一、两个表以两个表中都存在的关系字段进行联结
方法一:where
表A和表B关联的字段为:id
联结查询如下:
Select 字段a1,字段a2,字段b1
from 表A , 表B
where表A.id=表B.id
order by 字段a;
注意:select 指定的列字段a1、字段a2在表A中存在,字段b1在表B中存在。
例子:
①、【两表联结】
Select vend_name,prod_name,prod_price
From vendors, products
where vendors.vend_id = products.vend_id
Order by vend_name, prod_name;
②、【多表联结】
Select prod_name,vend_name,prod_price,quantity
From orderitems,products,vendots
Where products.vend_id = vendors.vend_id
And orderitems.prod_id = products.prod_id
And order_num =2005;
③、优化前:
select cuts_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 cuts_name,cust_contact
From customers,ordres,orderitmes
Where customers.cust_id=orders.cust_id
And ordertiems.order_num=orders.order_num
And prod_id=‘TNT2’
注意:③和④的查询结果相同
方法二:inner join....on
表A和表B关联的字段为:id
联结查询如下:
Select 字段a1,字段a2,字段b1
from 表A inner join表B
on 表A.id=表B.id;
例子:
select vend_name,prod_name,prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id;
二、两个表不以关系字段进行联结
笛卡儿积方法(结果查询出来的结果显示的行数=表A行数*表B行数)
Select 字段a1,字段a2,字段b1
from 表A , 表B
order by 字段a;
例子:
select vend_name,prod_name,prod_price
From vendors,products
order by vend_name,prod_name;
第16章