详述多表查询

最新推荐文章于 2022-06-01 17:37:42 发布

余生为莹

最新推荐文章于 2022-06-01 17:37:42 发布

阅读量140

点赞数 1

分类专栏： sql 文章标签： MySQL

本文链接：https://blog.csdn.net/qq_40831381/article/details/90107504

版权

sql 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

一般网购的地址管理中，我们都会保存几个不同的地址（因为可以帮亲人朋友买），因此可以创建两个关联的数据库表：

#user_info表
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表：

address表：

子查询

查询中的查询即为子查询，一般使用括号将子查询sql括起来

当在查询过程中需要知道一个已知量的不确定数据时使用子查询

1.如果子查询只返回一个结果，则可以使用 = ,也可以使用in；但是如果确定子查询永远只返回一个结果，则不建议使用in,最好使用 =，代码如下：
select id from user_info where real_name='王晓明';
select * from address where user_id=1;
上述代码是先在user_info中查询出真实姓名为王晓明的id，查询出来id为1，再根据id查询地址表(同样存在在地址表中的user_id)中的所有信息，这一过程可以将其转换成子循环查询：
select * from address where user_id=(select id from user_info where real_name='王晓明');
2.如果子查询返回多个结果，则使用 in、any、all：

in：相当于or连接多个条件
select id from user_info where real_name like'王%';
select * from address where user_id in(1,2);
上述代码是先在user_info中查询出真实姓王的id，查询出来id为1和2，再根据id查询地址表(同样存在在地址表中的user_id)中的所有信息，这一过程可以将其转换成子循环查询：
select * from address where user_id in(select id from user_info where real_name like'王%');
特殊运算符：

!= all() 相当于 not in
= some() 相当于 in。any 是 some 的别名
!= some() 不等同于 not in，不等于其中某一个。
all, some 可以配合其他运算符一起使用。

any：某一个的

  <any:小于子查询结果中的某一个，即小于最大的，比如 age <any(12,36,9)，这时只要age小于36即可

>any:大于子查询结果中的某一个，即大于最小的，比如age >any(12,36,9)，这时只要age大于9即可

=any:等于子查询结果中的某一个，等同于in 注意：any运算符等同于some运算符

  all：所有的

  <all：小于子查询结果中所有的一个，即小于最小的，比如age <all(12,36,9)，这时只要age小于9即可

>all：大于子查询结果中所有的一个，即大于最大的，比如age >all(12,36,9)，这时只要age大于36即可

多表查询

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

现实中我们更多的运用的是多表连接查询，而不用子查询（因为其影响性能）

外连接(outer join)

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

左外连接（left [outer] join）

返回的结果集中不仅包含表之间满足on连接条件的全部数据行，还包含左表（“left [outer] join”关键字左边的表）不满足on连接条件的数据行；代码如下：
select ui.*,ad.*
from user_info ui
left join address ad
on ad.user_id=ui.id
以上面代码的为例，结果说明：以 left为参照，显示左边表中的所有数据，如果从表中没有对应的数据，则显示 null

右外连接（right [outer] join）

返回的结果集中不仅包含表之间满足on连接条件的全部数据行，还包含右表（即“right [outer] join”关键字右边的表）不满足on连接条件的数据行；代码如下：
select ui.*,ad.*
from user_info ui
right join address ad
on ad.user_id=ui.id
左外连接和右外连接之间的转换

将上面的左外连接转换成左外连接，使其结果保持不变的方式的：交换两个表的位置，右连接的时候是将address表放在right join的右边，而左连接的时候将address表写在left join的左边。代码如下：
select ui.*,ad.*
from address ad
left join user_info ui
on ad.user_id=ui.id
结果依旧是不谋而合

全外连接（full [outer] join）

返回的结果集中不仅包含表之间满足on连接条件的全部数据行，还包含左表（“full [outer] join”关键字左边的表）和右表（“full [outer join”关键字右边的表）中不满足on连接条件的数据行；
select ui.*,ad.*
from user_info ui 
full join address ad
on ad.user_id=ui.id;
MySQL数据不支持全连接，在MySQL数据库这句SQL语句会报错

内连接(inner join)

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

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

显式等值连接
select ui.*,ad.*
from user_info ui
inner join address ad
on ad.user_id = ui.id;
隐式等值连接
select ui.*,ad.*
from address ad,user_info ui
where ad.user_id = ui.id;
上述两种方法的结果相同

交叉连接(cross join)

左表（“cross join”关键字左边的表）中的每一行与右表（“cross join”关键字右边的表）中的所有行组合，交叉联接的结果是一个笛卡尔积。
select ui.*,ad.* 
from user_info ui 
cross join address ad;