MySQL数据库——多表查询（子查询、外连接、内连接、交叉连接）

多个表关联查询需要依据多表之间列关系将其连接起来，这种连接方式分为三种：内连接(inner join)、外连接(outer join)及交叉连接(cross join)

创建两张表并插入数据

user_infor表

create table user_info(
      id int(2) primary key,
      user_name varchar(12) unique,
      password varchar(15) not null,
      real_name varchar(8) not null,
      age int(3)
); 

address表

create table address(
      id int(2) primary key,
      user_id int(2) not null,
      real_name varchar(8),
      mobile char(11),
      address varchar(150)
); 

插入数据

insert into user_info values(1,'浅唱灬幸福','8912@321','王晓明',12);
insert into address values(1,1,'王小明','15516472282','山西太原');
insert into address values(2,1,'王鑫','18404905139','山西大同');
insert into address values(3,1,'任建','15333021730','山西晋城');

insert into user_info values(2,'ぅ浮生若梦〤','56701wz','王楠',36);
insert into address values(4,2,'王楠','15010303314','北京海淀');
insert into address values(5,2,'赵婕','18435224278','山西长治');

insert into user_info values(3,'街角の风铃','27w4921','李晓飞',9);

insert into address values(6,6,'刘倩','13159775555','吉林长春');

子查询

当需要查询两张表中的数据的时候

• 首先需要在user_info表中把’王晓明’对应的id查找出来

#查询user_info表中的王晓明对应的数据
select id from user_info where real_name = '王晓明';

• 然后在address表中查找id为1的数据

select * from address where user_id=1;

结果

• 可以看出来这两句SQL语句是分开的，需要执行两次，所以这里可以使用子查询，把这两句SQL语句写在一句SQL语句中

select * from address where user_id = (select id from user_info where real_name = '王晓明');

结果和上面的结果相同

外连接(outer join）

如果依据多表之间列关系进行外连接，查询结果集不仅仅包括满足on连接条件的数据行,还包括不满足on连接条件的数据行。

左连接(left join)

返回的结果集中不仅包含表之间满足on连接条件的全部数据行，还包含左表（“left [outer] join”关键字左边的表）不满足on连接条件的数据行

#这里为了方便阅读我们把SQL语句分成多行书写
select ui.*,addr.*
from user_info ui
left join address addr
on addr.user_id =  ui.id;

结果

这里可以看出来如果在address表中左连接一个user_info表则可以查出来user_info表中的所有数据，即使address表中没有对应的数据，也会用null显示。但是address表中没有对应user_info表中的数据没有显示出来。所以左连接的表就是以left join 关键字左边的表为主要的表，即从表中没有对应的数据依然会把所有的数据查出来，但是从表中如果没有主表中的数据则不显示。

右连接(right join)

右连接和左连接相似，但是右连接是right join关键字右边的表为主表，左边的为从表。同样的查询结果会把所有的主表中的数据显示出来，从表中的数据如果不符合条件则不显示。
比如

select ui.*,addr.* 
from user_info ui
right join address addr
on addr.user_id = ui.id;

结果

左连接和右连接可以互相转换

可以在不改变主表和从表的关系为前提的条件下left和right关键字可以互换，只需要对应的表的位置也进行互换即可。

全连接(full [outer] join)

返回的结果集中不仅包含表之间满足on连接条件的全部数据行，还包含左表（“full [outer] join”关键字左边的表）和右表（“full [outer join”关键字右边的表）中不满足on连接条件的数据行

select ui.*,addr.* from user_info ui full address addr on ui.id = addr.user_id;

• 注意：MySQL数据不支持全连接，在MySQL数据库这句SQL语句会报错

内连接(inner join)

如果依据多个表之间列关系进行内连接，查询结果集仅包括满足连接条件的数据行。内连接分为等值连接、不等值连接以及自然连接，其中等值连接最为常见。
主要介绍一下等值连接

等值连接：在连接条件中使用等号(=)运算符比较被连接列的列值是否相等，分显式等值连接和隐式等值连接：

• 显式等值连接

select ui.*,addr.*
from user_info ui
inner join address addr
on addr.user_id = ui.id;

结果

• 隐式等值连接

select ui.*,addr.*
from address addr,user_info ui
where addr.user_id = ui.id;

结果

这里可以看出来这两种方法的结果相同。但是个人还是建议使用显示等值连接，这样SQL语句更便于阅读

交叉连接(cross join)

中的每一行与右表（“cross join”关键字右边的表）中的所有行组合，交叉联接的结果是一个笛卡尔积。

select ui.*,addr.*
from user_info ui
cross join address addr;

这里一同是18条数据，user_info中3条 * address表中6条=18