主页 系列 标签 关于 简体中文

多表查询的使用

 收录于  类别 数据库 和 系列 mysql数据库
     约 2217 字  预计阅读 5 分钟 
系列 - mysql数据库

在实际开发中，我们经常会遇到要查的数据出现在两个甚至更多表的情况，所以想查出需要的数据，就需要进行多表查询。多表查询的关键点就是表与表之间的共同点，也就是共有的字段。多表查询的基础语法如下。

1

select 字段1,字段2... from 表1,表2... [where 条件]

通常情况下，某些字段会同时存在于不同的表中，所以为了避免混淆，我们会给表起一个别名，一般格式如下。

1
2

select 别名1.字段1,别名2.字段2... from 表1 as 别名1,表2 as 别名2... 
[where 条件]

例.查询学生的成绩

1
2

select s1.id,s1.name,s2.score from student as s1,score as s2
where s1.id=s2.stu_id;

将student表起别名为s1，将score表起别名为s2，起别名的时候关键字as可以省略。

内连接就是表间的主键与外键相连，只取得键值一致的，可以获取双方表中的数据连接方式。其基本语法如下：

1
2

SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外键=表2.主键 
WhERE 条件语句;

例. 内连接查询学生的成绩

1
2

select s1.id,s1.name,s2.score from student s1
inner join score s2 on s1.id = s2.stu_id;

连接条件可以使用 on using where on 是在连表的过程中，根据on条件判断是否保留连表的结果。在连表查询中，建议使用 on。 using 是在连表查询的字段名一致时，可以使用。如 using(id)。using 条件中使用的字段，返回结果中只有一遍。 where 是在连表操作完成后，再根据where条件进行数据过滤。不建议使用，因为这样效率很低。

左连接是以左表为标准，只查询在左边表中存在的数据，当然需要两个表中的键值一致。基本语法如下：

SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外键=表2.主键 WhERE 条件语句;

例.优先全部显示学生表的数据

1
2

select * from student as s1
left join score as s2 on s1.id = s2.stu_id;

同理，右连接将会以右边作为基准，进行检索。基本的语法如下：

1
2

SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外键=表2.主键 
WhERE 条件语句;

左连接与右连接的左右指的是以两张表中的哪一张为基准，它们都是外连接。外连接就好像是为非基准表添加了一行全为空值的万能行，用来与基准表中找不到匹配的行进行匹配。假设两个没有空值的表进行左连接，左表是基准表，左表的所有行都出现在结果中，右表则可能因为无法与基准表匹配而出现是空值的字段。

例.优先全部显示成绩表的数据

1

select * from student as s1 right join score as s2 on s1.id = s2.stu_id;

我们先创建本章的数据库p08。

1
2

create database p08;
use p08;

假设我们现在有两个表，一个是学生表student，另一个是成绩表score，这两个表的共同点就是学生的id，我们先创建一下，顺便插入一些数据。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22

create table student(
id int primary key auto_increment,    
name varchar(10)
)ENGINE=InnoDB  DEFAULT CHARSET=utf8;
insert into student values
(null,'陈一'),
(null,'林二'),
(null,'张三'),
(null,'李四'),
(null,'王五');
create table score(
id int primary key auto_increment,
stu_id int not null,
score int,
check(score>=0 and score<=100)
);
insert into score values
(null,1,88),
(null,2,85),
(null,3,90),
(null,4,95),
(null,5,60);

现教务处中要获取学生成绩预计统计处理。根据之前设计的表中，我们无法直接查询到成绩。student表中仅有学生的姓名，而score表中有成绩，也有stu_id，但并没有学生的姓名。此时我们可以采用多表查询，以student表中的id和score表中的stu_id为例。

可采用如下语法：

1
2

select s1.id,s1.name,s2.score from student as s1,score as s2 
where s1.id=s2.stu_id;

将student表起别名为s1，将score表起别名为s2，起别名的时候关键字as可以省略。

内连接查询在某些情况下跟普通的多表查询查出来的结果差距不大。上面的普通查询语句，用内连接查询的语句格式如下：

1
2

select s1.id,s1.name,s2.score from student s1
inner join score s2 on s1.id = s2.stu_id;

连接条件可以使用 on using where on 是在连表的过程中，根据on条件判断是否保留连表的结果。在连表查询中，建议使用 on。 using 是在连表查询的字段名一致时，可以使用。如 using(id)。using 条件中使用的字段，返回结果中只有一遍。 where 是在连表操作完成后，再根据where条件进行数据过滤。不建议使用，因为这样效率很低。

现教务处想要查询所有学生的成绩，即使score成绩表中没有该学生的成绩，也要将其列举出来。意思就是，以student学生表为准，只要student表中存在的学生，无论他是否有成绩，都要打印出来。这个时候我们就需要用到我们的左外连接查询，其语句格式如下：

1
2

select * from student as s1 left join score as s2
on s1.id = s2.stu_id;

![[2022-11-18-16-32-51-image.png]]

现教务处想要查询有成绩的学生的成绩，即使student学生表中有该学生，但是只要score成绩表中没有该学生的成绩，就不把这个学生打印出来。意思就是以score成绩表为准，有成绩的学生就打印，没有成绩的学生，就不打印。这个时候我们就需要用到我们的右外连接查询，其语句格式如下：

1
2

select * from student as s1 right join score as s2
on s1.id = s2.stu_id;

![[2022-11-18-16-34-11-image.png]]

从上面这几步看，好像各种查询都没什么区别，我们现在假设多了一位同学，没有参加考试，再试试看有什么区别。

我们先往学生student表插入数据。可以看到此时学生表有赵六的记录，但是分数表没有李四的记录。

1

insert into student values(null, "赵六");

我们重新执行一下刚刚那几种查询语句看看分别是什么结果。 ![[2022-11-18-16-36-33-image 1.png]]

分数score表没有李四的记录，结果还是会查询到李四。相反地，右外连接查询的时候是以右表score表为基准，由于score表没有李四的记录，所以结果就没有李四。这就是左外连接与右外连接本质上的区别，主要就是看以哪张表为基准进行查询。

更新于 2023-05-26
 mysql
返回 | 主页