欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql多表连接查询实例讲解

程序员文章站 2024-01-29 18:22:04
实际的项目,存在多张表的关联关系。不可能在一张表里面就能检索出所有数据。如果没有表连接的话,那么我们就需要非常多的操作。比如需要从a表找出限制性的条件来从b表中检索数据。不...

实际的项目,存在多张表的关联关系。不可能在一张表里面就能检索出所有数据。如果没有表连接的话,那么我们就需要非常多的操作。比如需要从a表找出限制性的条件来从b表中检索数据。不但需要分多表来操作,而且效率也不高。比如书中的例子:

代码如下:

select fid
from t_customer
where fname='mike'

这个sql语句返回2,也就是姓名为mike 的客户的fid值为2,这样就可以到t_order中检索fcustomerid等于2 的记录:

代码如下:

select fnumber,fprice
from t_order
where fcustomerid=2

下面我们详细来看看表连接。表连接有多种不同的类型,有交叉连接(cross join)、内连接(inner join)、外连接(outter join)。

(1)内连接(inner join):内连接组合两张表,并且只获取满足两表连接条件的数据。

代码如下:

select o.fid,o.fnumber,o.fprice,
c.fid,c.fname,c .fage
from t_order o join t_customer c
on o.fcustomerid= c.fid

注:在大多数数据库系统中,inner join中的inner是可选的,inner join 是默认的连接方式。

在使用表连接的时候可以不局限于只连接两张表,因为有很多情况下需要联系许多表。例如,t_order表同时还需要连接t_customer和t_ordertype两张表才能检索到所需要的信息,编写如下sql语句即可:

代码如下:

select o.fid,o.fnumber,o.fprice,
c.fid,c.fname,c .fage
from t_order o join t_customer c
on o.fcustomerid= c.fid
inner join t_ordertype
on t_order.ftypeid= t_ordertype.fid

(2)交叉连接(cross join):交叉连接所有涉及的表中的所有记录都包含在结果集中。可以采用两种方式来定义交叉连接,分别是隐式和显式的连接。

下面看看隐式的例子:

代码如下:

select t_customer.fid, t_customer.fname, t_customer.fage,
t_order.fid, t_order.fnumber, t_order.fprice
from t_customer, t_order

使用显式的连接则需要使用cross join,例子如下:

代码如下:

select t_customer.fid, t_customer.fname, t_customer.fage,
t_order.fid, t_order.fnumber, t_order.fprice
from t_customer
cross join t_order

(3)外连接(outter join):内部连接只获取满足连接条件的数据,而对于外部连接来说,主要是解决这样的一种场景。满足条件的数据检索出来,这个没有疑问,外部连接还会检索另一部分数据,那就是将不满足条件的数据以null来填充。先来看一下外连接的分类:左外部连接(left outer join)、右外部连接(right outer join)和全外部连接(fullouter join)。

i、左外部连接(left outer join):前头也说了,将不满足条件的数据以null来填充。那么具体是哪些需要以null来填充呢,对于左外连接来说的话,连接条件当中,如果出现满足条件的左表的数据在右表中没有相应匹配时,需要把相应的右表字段填充null值。也就是说左外部连接的主体是左表,右表来配合。

代码如下:

select o.fnumber,o.fprice,o.fcustomerid,
c.fname,c.fage
from t_order o
left outer join t_customer c
on o.fcustomerid=c.fid

注:如果使用左外部连接的话,通过where语句能过滤其中不符合的数据

代码如下:

select o.fnumber,o.fprice,o.fcustomerid,
c.fname,c.fage
from t_order o
left outer join t_customer c
on o.fcustomerid=c.fid
where o.fprice>=150

ii、右外部连接(right outer join):右外部连接与左外连部接相反,将会被填充null值的是左表的字段。也就是说右外部连接的主体是右表,左表来配合。

代码如下:

select o.fnumber,o.fprice,o.fcustomerid,
c.fname,c.fage
from t_order o
right outer join t_customer c
on o.fcustomerid=c.fid

注:同左外连接一样,可以使用where语句进行过滤

iii、全外部连接(fullouter join):全外部连接是左外部连接和右外部连接的合集。也就是既包括左外部连接的结果集,也包括右外部连接的结果集。

代码如下:

select o.fnumber,o.fprice,o.fcustomerid,
c.fname,c.fage
from t_order o
full outer join t_customer c
on o.fcustomerid=c.fid

其结果相当于:

select o.fnumber,o.fprice,o.fcustomerid,
c.fname,c.fage
from t_order o
left outer join t_customer c
on o.fcustomerid=c.fid
union
select o.fnumber,o.fprice,o.fcustomerid,
c.fname,c.fage
from t_order o
right outer join t_customer c
on o.fcustomerid=c.fid

多表查询的多种sql写法:(下面是从两个表里查询<也可以看成从三个表里查询>,显示表v_goods里的所有字段,显示admin2表里的name字段作为添加人,显示表admin2表的name字段作为操作人) 多个表的查询都可按照下面三个例句进行编写sql

select v.*,(select a.name from admin2 a where a.adminid=v.loadinid) as aname,(select a.name from admin2 a where a.adminid=v.operatorid) as uname from v_goods v where 1=1;
select v.*,a.name aname,b.name uname from v_goods v,admin2 a,admin2 b where a.adminid=v.loadinid and b.adminid=v.operatorid ;
select v.*,a.name aname,b.name uname from v_goods v left join admin2 a on a.adminid=v.loadinid left join admin2 b on b.adminid=v.operatorid ;