目 录CONTENT

文章目录

Mysql 表之联合查询(多表查询)

Seven
2024-03-20 / 0 评论 / 0 点赞 / 255 阅读 / 27689 字 / 正在检测是否收录...

前言:本篇博客介绍MySQL数据库的MySQL表之联合查询(多表查询),学习MySQL之前要先安装好MySQL,如果还没有安装的小伙伴可以看看博主前面的博客,里面有详细的安装教程。
那我们废话不多说,直接进入主体!

一.联合查询

然后就到联合查询,所谓联合,就是把多个表的记录往一起合并,一起进行查询,也叫多表查询。多表查询是整个sql中最复杂的部分,也是笔试中爱考的部分,但是实际开发中一般禁止使用多表查询。

虽然多表查询是笔试中爱考的部分,但在实际工作中截然不同,就所谓面试造核弹,工作拧螺丝~

1.笛卡尔积

多表查询中的核心操作,就是笛卡尔积,笛卡尔积的计算非常简单,就类似于排列组合,笛卡尔积是针对任意两张表之间进行的运算。
比如:
image-1710925430638
再比如:
image-1710925468917
图中的就是笛卡尔积的运算过程:

  1. 先拿第一张表的第一条记录,和第二张表的每个记录,分别组合,得到一组新的数据。
  2. 然后继续再拿第一张表的第二条记录,重复刚刚的操作。
  3. 最后形成的所有新的数据,就是我们的笛卡尔积。
    所以针对A B两张表,得到的笛卡尔积,此时笛卡尔积的列数,就是A的列数+B的列数,笛卡尔积的行数,就是A的行数 * B的行数。所以在实际工作中,公司的数据量非常大,那么进行多表查询的时候就很可能把数据给搞挂了,所以就直接一刀切,禁止多表查询这种危险性较大的操作。
    上述操作我们在MySQL中操作一下:

先创建好studentclass表:

mysql> create table student(studentId int,name varchar(50),classId int);
	   insert into student values(1,'张三',1);
	   insert into student values(2,'李四',1);
	   insert into student values(3,'王五',2);
	   insert into student values(4,'赵六',2);
mysql> create table class(classId int,name varchar(20));
	   insert into class values(1,'帅哥');
	   insert into class values(1,'美女');

创建好之后我们查询一下确认没错:

MySQL [testdb]> select * from class ;
+---------+--------+
| classId | name   |
+---------+--------+
|       1 | 帅哥   |
|       1 | 美女   |
+---------+--------+
2 rows in set (0.001 sec)
MySQL [testdb]> select * from student;                              
+-----------+--------+---------+
| studentId | name   | classId |
+-----------+--------+---------+
|         1 | 张三   |       1 |
|         2 | 李四   |       1 |
|         3 | 王五   |       2 |
|         4 | 赵六   |       2 |
+-----------+--------+---------+
4 rows in set (0.000 sec)

然后就是我们的笛卡尔积,在SQL中进行笛卡尔积,最简单的做法就是直接select from后面跟上多个表名,表名之间用逗号分割。

mysql> select * from student,class;
MySQL [testdb]> select * from class,student;
+---------+--------+-----------+--------+---------+
| classId | name   | studentId | name   | classId |
+---------+--------+-----------+--------+---------+
|       1 | 美女   |         1 | 张三   |       1 |
|       1 | 帅哥   |         1 | 张三   |       1 |
|       1 | 美女   |         2 | 李四   |       1 |
|       1 | 帅哥   |         2 | 李四   |       1 |
|       1 | 美女   |         3 | 王五   |       2 |
|       1 | 帅哥   |         3 | 王五   |       2 |
|       1 | 美女   |         4 | 赵六   |       2 |
|       1 | 帅哥   |         4 | 赵六   |       2 |
+---------+--------+-----------+--------+---------+
8 rows in set (0.001 sec)

可以看见,笛卡尔积是两张表中数据尽可能排列组合得到的,在这些排列组合中,有一些数据是我们需要的,有一些是没有意义的。也就是说,笛卡尔积是一个单纯无脑的排列组合,这些组合不一定都有意义。

像两张表中,classId这一列相等的才是我们需要的数据,对于这样的相等条件,就称为“连接条件”。带有连接条件的笛卡尔积,其实就是"多表查询"了。如果笛卡尔积中两个列的名字相同,在写条件的时候可以通过表名.列名的格式来访问,如果列名不会混淆,也就是两个列名条件不同名的话,可以通过表名.列名访问,也可以省略表名。
如:

MySQL [testdb]> select * from class,student where class.classId = student.classId;
+---------+--------+-----------+--------+---------+
| classId | name   | studentId | name   | classId |
+---------+--------+-----------+--------+---------+
|       1 | 美女   |         1 | 张三   |       1 |
|       1 | 帅哥   |         1 | 张三   |       1 |
|       1 | 美女   |         2 | 李四   |       1 |
|       1 | 帅哥   |         2 | 李四   |       1 |
+---------+--------+-----------+--------+---------+
4 rows in set (0.001 sec)

在查询两张表后加入where连接"连接条件"。

然后我们看到还是有一些其实我们不需要的数据,在实际查询中,我们需要的数据往往只有几列,所以我们在查询的时候可以不用select * ,而是select 我们需要的那几列。如我们下面的表只想看学生对应班级名。
如:

MySQL [testdb]> select class.name,student.name from class,student where class.classId = student.classId;
+--------+--------+
| name   | name   |
+--------+--------+
| 美女   | 张三   |
| 帅哥   | 张三   |
| 美女   | 李四   |
| 帅哥   | 李四   |
+--------+--------+
4 rows in set (0.001 sec)

2.创建查询例子表

然后我们学习一下各种查询,在学习这些查询之前,为了更好的说明和了解这些查询,我们先创建下列几个表,让大家更容易理解各类查询:

  • classes班级表
mysql> create table classes(id int primary key auto_increment, name varchar(20), `desc` varchar(100)); 
	   insert into classes(name, `desc`) values 
	   ('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
	   ('中文系2019级3班','学习了中国传统文学'),
	   ('自动化2019级5班','学习了机械自动化');
  • student学生表
mysql> create table student (id int primary key auto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20) , classes_id int);
	   insert into student(sn, name, qq_mail, classes_id) values
	   ('09982','黑旋风李逵','xuanfeng@qq.com',1),
	   ('00835','菩提老祖',null,1),
	   ('00391','白素贞',null,1),
	   ('00031','许仙','xuxian@qq.com',1),
	   ('00054','不想毕业',null,1),
	   ('51234','好好说话','say@qq.com',2),
	   ('83223','tellme',null,2),
	   ('09527','老外学中文','foreigner@qq.com',2);
  • course课程表
mysql> create table course(id int primary key auto_increment, name varchar(20));
	   insert into course(name) values
	   ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
  • score分数表
mysql> create table score(score decimal(3, 1), student_id int, course_id int);
	   insert into score(score, student_id, course_id) values
	   //黑旋风李逵
	   (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
	   //菩提老祖
	   (60, 2, 1),(59.5, 2, 5),
	   //白素贞
	   (33, 3, 1),(68, 3, 3),(99, 3, 5),
	   //许仙
	   (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
	   //不想毕业
	   (81, 5, 1),(37, 5, 5),
	   //好好说话
	   (56, 6, 2),(43, 6, 4),(79, 6, 6),
	   //tellme
	   (80, 7, 2),(92, 7, 6);

创建好之后我们一一查询一下:

MySQL [testdb]> select * from classes;
+----+-------------------------+-------------------------------------------------------------------+
| id | name                    | desc                                                              |
+----+-------------------------+-------------------------------------------------------------------+
|  1 | 计算机系2019级1班       | 学习了计算机原理、C和Java语言、数据结构和算法                     |
|  2 | 中文系2019级3班         | 学习了中国传统文学                                                |
|  3 | 自动化2019级5班         | 学习了机械自动化                                                  |
+----+-------------------------+-------------------------------------------------------------------+
3 rows in set (0.001 sec)

MySQL [testdb]> select * from student;
+----+-------+-----------------+------------------+------------+
| id | sn    | name            | qq_mail          | classes_id |
+----+-------+-----------------+------------------+------------+
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com  |          1 |
|  2 | 00835 | 菩提老祖        | NULL             |          1 |
|  3 | 00391 | 白素贞          | NULL             |          1 |
|  4 | 00031 | 许仙            | xuxian@qq.com    |          1 |
|  5 | 00054 | 不想毕业        | NULL             |          1 |
|  6 | 51234 | 好好说话        | say@qq.com       |          2 |
|  7 | 83223 | tellme          | NULL             |          2 |
|  8 | 09527 | 老外学中文      | foreigner@qq.com |          2 |
+----+-------+-----------------+------------------+------------+
8 rows in set (0.001 sec)

MySQL [testdb]> select * from score;  
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  70.5 |          1 |         1 |
|  98.5 |          1 |         3 |
|  33.0 |          1 |         5 |
|  98.0 |          1 |         6 |
|  60.0 |          2 |         1 |
|  59.5 |          2 |         5 |
|  33.0 |          3 |         1 |
|  68.0 |          3 |         3 |
|  99.0 |          3 |         5 |
|  67.0 |          4 |         1 |
|  23.0 |          4 |         3 |
|  56.0 |          4 |         5 |
|  72.0 |          4 |         6 |
|  81.0 |          5 |         1 |
|  37.0 |          5 |         5 |
|  56.0 |          6 |         2 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  80.0 |          7 |         2 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+
20 rows in set (0.000 sec)

MySQL [testdb]> select * from course;
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | Java               |
|  2 | 中国传统文化       |
|  3 | 计算机原理         |
|  4 | 语文               |
|  5 | 高阶数学           |
|  6 | 英文               |
+----+--------------------+
6 rows in set (0.001 sec)

实际上,在这个场景中,涉及到的主体有三个,学生,班级和课程,学生和班级是一对一的关系,学生和课程是多对多的关系,而分数表,就是学生和课程之间的关联表。

下面正式开始介绍我们的联合查询中的各种查询~

3.内连接

我们用案例来学习我们的查询,首先登场的是内连接查询,这种查询是比较常用的查询,也是我们上面有了解过的查询。
image-1710930238171

例1

  • 查询“许仙”同学的成绩

第一步:查表

首先我们要想到,查询某位同学的成绩,所用到的表应该是哪些,这里是学生表和成绩表,所以我们查询的应该是学生表和成绩表,最简单的联合起来就是在select的时候用逗号连接两个表,比如select * from student,score; 我们看一下查询结果(此处省略n行):

|  7 | 83223 | tellme          | NULL             |          2 |  80.0 |          7 |         2 |
|  6 | 51234 | 好好说话        | say@qq.com       |          2 |  80.0 |          7 |         2 |
|  5 | 00054 | 不想毕业        | NULL             |          1 |  80.0 |          7 |         2 |
|  4 | 00031 | 许仙            | xuxian@qq.com    |          1 |  80.0 |          7 |         2 |
|  3 | 00391 | 白素贞          | NULL             |          1 |  80.0 |          7 |         2 |
|  2 | 00835 | 菩提老祖        | NULL             |          1 |  80.0 |          7 |         2 |
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com  |          1 |  80.0 |          7 |         2 |
|  8 | 09527 | 老外学中文      | foreigner@qq.com |          2 |  92.0 |          7 |         6 |
|  7 | 83223 | tellme          | NULL             |          2 |  92.0 |          7 |         6 |
|  6 | 51234 | 好好说话        | say@qq.com       |          2 |  92.0 |          7 |         6 |
|  5 | 00054 | 不想毕业        | NULL             |          1 |  92.0 |          7 |         6 |
|  4 | 00031 | 许仙            | xuxian@qq.com    |          1 |  92.0 |          7 |         6 |
|  3 | 00391 | 白素贞          | NULL             |          1 |  92.0 |          7 |         6 |
|  2 | 00835 | 菩提老祖        | NULL             |          1 |  92.0 |          7 |         6 |
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com  |          1 |  92.0 |          7 |         6 |
+----+-------+-----------------+------------------+------------+-------+------------+-----------+
160 rows in set (0.001 sec)

可以看到我们查询的数据有160行那么多,我们仔细观察发现,虽然我们查询的是两张表的结果,但是查询结果的表是合并的,也就是说如果我们没有用上一些条件的话,查出来的表就相当于排列组合出来的无效数据了,所以我们要在查询多张表时 应该有一个匹配的数据作为桥梁。

第二步:确定条件

我们把学生表和成绩表拿下来,发现他们的学生id是一样的,所以我们可以在查询这两张表的时候用id作为配对条件,这时候用我们之前学过的where子句就可以啦。

select * from student;
+----+-------+-----------------+------------------+------------+
| id | sn    | name            | qq_mail          | classes_id |
+----+-------+-----------------+------------------+------------+
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com  |          1 |
|  2 | 00835 | 菩提老祖        | NULL             |          1 |
|  3 | 00391 | 白素贞          | NULL             |          1 |
|  4 | 00031 | 许仙            | xuxian@qq.com    |          1 |
|  5 | 00054 | 不想毕业        | NULL             |          1 |
|  6 | 51234 | 好好说话        | say@qq.com       |          2 |
|  7 | 83223 | tellme          | NULL             |          2 |
|  8 | 09527 | 老外学中文      | foreigner@qq.com |          2 |
+----+-------+-----------------+------------------+------------+
8 rows in set (0.003 sec)

MySQL [testdb]> select * from score;
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  70.5 |          1 |         1 |
|  98.5 |          1 |         3 |
|  33.0 |          1 |         5 |
|  98.0 |          1 |         6 |
|  60.0 |          2 |         1 |
|  59.5 |          2 |         5 |
|  33.0 |          3 |         1 |
|  68.0 |          3 |         3 |
|  99.0 |          3 |         5 |
|  67.0 |          4 |         1 |
|  23.0 |          4 |         3 |
|  56.0 |          4 |         5 |
|  72.0 |          4 |         6 |
|  81.0 |          5 |         1 |
|  37.0 |          5 |         5 |
|  56.0 |          6 |         2 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  80.0 |          7 |         2 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+
20 rows in set (0.002 sec)

第三步:查询结果

最后查询得到的就是一张对应学生对应成绩的表了,然后我们题目写的是要得到许仙同学的成绩,所以我们再筛选名字为许仙的就可以啦:

MySQL [testdb]> select * from student,score where student.id = score.student_id;
+----+-------+-----------------+-----------------+------------+-------+------------+-----------+
| id | sn    | name            | qq_mail         | classes_id | score | student_id | course_id |
+----+-------+-----------------+-----------------+------------+-------+------------+-----------+
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |          1 |  70.5 |          1 |         1 |
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |          1 |  98.5 |          1 |         3 |
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |          1 |  33.0 |          1 |         5 |
|  1 | 09982 | 黑旋风李逵      | xuanfeng@qq.com |          1 |  98.0 |          1 |         6 |
|  2 | 00835 | 菩提老祖        | NULL            |          1 |  60.0 |          2 |         1 |
|  2 | 00835 | 菩提老祖        | NULL            |          1 |  59.5 |          2 |         5 |
|  3 | 00391 | 白素贞          | NULL            |          1 |  33.0 |          3 |         1 |
|  3 | 00391 | 白素贞          | NULL            |          1 |  68.0 |          3 |         3 |
|  3 | 00391 | 白素贞          | NULL            |          1 |  99.0 |          3 |         5 |
|  4 | 00031 | 许仙            | xuxian@qq.com   |          1 |  67.0 |          4 |         1 |
|  4 | 00031 | 许仙            | xuxian@qq.com   |          1 |  23.0 |          4 |         3 |
|  4 | 00031 | 许仙            | xuxian@qq.com   |          1 |  56.0 |          4 |         5 |
|  4 | 00031 | 许仙            | xuxian@qq.com   |          1 |  72.0 |          4 |         6 |
|  5 | 00054 | 不想毕业        | NULL            |          1 |  81.0 |          5 |         1 |
|  5 | 00054 | 不想毕业        | NULL            |          1 |  37.0 |          5 |         5 |
|  6 | 51234 | 好好说话        | say@qq.com      |          2 |  56.0 |          6 |         2 |
|  6 | 51234 | 好好说话        | say@qq.com      |          2 |  43.0 |          6 |         4 |
|  6 | 51234 | 好好说话        | say@qq.com      |          2 |  79.0 |          6 |         6 |
|  7 | 83223 | tellme          | NULL            |          2 |  80.0 |          7 |         2 |
|  7 | 83223 | tellme          | NULL            |          2 |  92.0 |          7 |         6 |
+----+-------+-----------------+-----------------+------------+-------+------------+-----------+
20 rows in set (0.001 sec)

MySQL [testdb]> select * from student,score where student.id = score.student_id and student.name = '许仙';
+----+-------+--------+---------------+------------+-------+------------+-----------+
| id | sn    | name   | qq_mail       | classes_id | score | student_id | course_id |
+----+-------+--------+---------------+------------+-------+------------+-----------+
|  4 | 00031 | 许仙   | xuxian@qq.com |          1 |  67.0 |          4 |         1 |
|  4 | 00031 | 许仙   | xuxian@qq.com |          1 |  23.0 |          4 |         3 |
|  4 | 00031 | 许仙   | xuxian@qq.com |          1 |  56.0 |          4 |         5 |
|  4 | 00031 | 许仙   | xuxian@qq.com |          1 |  72.0 |          4 |         6 |
+----+-------+--------+---------------+------------+-------+------------+-----------+
4 rows in set (0.001 sec)

这便是我们其中一种内连接,也就是用条件匹配来联合多张表,我们还有一种内连接的写法是用join...on,其实都差不多,前面的是用逗号和where连接查询两张表,这里就用join...on连接,如下面的写法的查询结果是和上面一样的:

select * from student join score on student.id = score.student_id and student.name = '许仙'

内连接:
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

例2

  • 查询所有同学的总成绩,及同学的个人信息
    同样的先看一下总成绩和个人信息需要用到的表,然后连接两张表,条件是id相同,以及需要的是同学的个人信息,当然这里还有总成绩需要排序后计算和,所以我们就可以写出查询:
select student.sn,student.name,student.qq_mail,sum(score.score) from student,score where student.id = score.student_id group by student_id;

查询结果:

+-------+-----------------+-----------------+------------------+
| sn    | name            | qq_mail         | sum(score.score) |
+-------+-----------------+-----------------+------------------+
| 09982 | 黑旋风李逵      | xuanfeng@qq.com |            300.0 |
| 00835 | 菩提老祖        | NULL            |            119.5 |
| 00391 | 白素贞          | NULL            |            200.0 |
| 00031 | 许仙            | xuxian@qq.com   |            218.0 |
| 00054 | 不想毕业        | NULL            |            118.0 |
| 51234 | 好好说话        | say@qq.com      |            178.0 |
| 83223 | tellme          | NULL            |            172.0 |
+-------+-----------------+-----------------+------------------+
7 rows in set (0.002 sec)

像这样的多表查询,看起来比较复杂,按顺序多写几遍就可以得到规律,很流畅的就写出来了。比如这里的查询的是同学信息和总成绩,使用的表是学生表和成绩表,条件是id相同,总成绩需要排序计算。

例3

  • 查询所有同学的各科成绩
    这里的话同样是先分析一下,查询的是各科成绩,需要用到的表是学生表,成绩表还有课程表,那么三个表联合的时候也一样,只要条件匹配就可以很好的连起来。
select student.name,course.name,score.score from student,course,score where student.id = score.student_id and course.id = score.course_id;

查询结果:

+-----------------+--------------------+-------+
| name            | name               | score |
+-----------------+--------------------+-------+
| 黑旋风李逵      | Java               |  70.5 |
| 黑旋风李逵      | 计算机原理         |  98.5 |
| 黑旋风李逵      | 高阶数学           |  33.0 |
| 黑旋风李逵      | 英文               |  98.0 |
| 菩提老祖        | Java               |  60.0 |
| 菩提老祖        | 高阶数学           |  59.5 |
| 白素贞          | Java               |  33.0 |
| 白素贞          | 计算机原理         |  68.0 |
| 白素贞          | 高阶数学           |  99.0 |
| 许仙            | Java               |  67.0 |
| 许仙            | 计算机原理         |  23.0 |
| 许仙            | 高阶数学           |  56.0 |
| 许仙            | 英文               |  72.0 |
| 不想毕业        | Java               |  81.0 |
| 不想毕业        | 高阶数学           |  37.0 |
| 好好说话        | 中国传统文化       |  56.0 |
| 好好说话        | 语文               |  43.0 |
| 好好说话        | 英文               |  79.0 |
| tellme          | 中国传统文化       |  80.0 |
| tellme          | 英文               |  92.0 |
+-----------------+--------------------+-------+
20 rows in set (0.002 sec)

当然三张表的也可以用join...on来实现,可以写成下面的样子

from 表1 join 表2 on 条件 join 表3 on 条件

但是这种内连接的形式,我们还是推荐使用where子句,那join有什么用呢,这就到我们另一个连接,也就是外连接了。

4.外连接

join...on能够做到上面写法做不到的事情,也就是外连接。

定义:外连接分为 左外连接 和 右外连接 。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接。

例表创建:
学生表

mysql> create table student2(id int,name varchar(50),classes_id int);
	   insert into student2 values(1,'张三',1);
	   insert into student2 values(2,'李四',1);
	   insert into student2 values(3,'王五',2);
	   insert into student2 values(4,'赵六',3);
//班级表
mysql> create table class(id int,name varchar(50));
 	   insert into class values(1,'java1班');
   	   insert into class values(,'java2班');

那么外连接是什么意思呢,我们同样用查询来说明一下,我们先看看这里的学生表和班级表的内容:

select * from student2;
MySQL [testdb]> select * from student2;
+------+--------+------------+
| id   | name   | classes_id |
+------+--------+------------+
|    1 | 张三   |          1 |
|    1 | 李四   |          1 |
|    1 | 王五   |          2 |
|    1 | 赵六   |          3 |
+------+--------+------------+
4 rows in set (0.001 sec)

MySQL [testdb]> select * from class;   
+------+----------+
| id   | name     |
+------+----------+
|    1 | java1班  |
|    2 | java2班  |
+------+----------+
2 rows in set (0.001 sec)

当我们学生表中有班级编号是3的同学,但是班级表却没有的时候,如果使用内连接时,就会省略掉班级编号为3的同学,比如我们现在使用内连接:
查询结果:

mysql> select student2.name,class.name from class,student2 where student2.classes_id = class.id;
+--------+----------+
| name   | name     |
+--------+----------+
| 张三   | java1班  |
| 李四   | java1班  |
| 王五   | java2班  |
+--------+----------+
3 rows in set (0.001 sec)

上面的这种写法就相当于inner join,所以当我们需要保存其中一张表的完整性的时候,我们就用到了外连接,这里的左外连接和右外连接的意思就是,左外就是保留左边的完整性,右外就是保留右边的完整性。也就是说左外链接的时候就会以左侧的表为主,尽可能的把数据都列出来,大不了后侧表对应列变成null。

  • 语法:

左外连接,表1完全显示 select 字段名 from 表名1 left join 表名2 on 连接条件;
右外连接,表2完全显示 select 字段 from 表名1 right join 表名2 on 连接条件;

例子(当连接中为保留表的完整性就会在没有数据的地方加入null):

mysql> select student2.name,class.name from student2 left join class on student2.classes_id = class.id;
+--------+----------+
| name   | name     |
+--------+----------+
| 张三   | java1班  |
| 李四   | java1班  |
| 王五   | java2班  |
| 赵六   | NULL     |
+--------+----------+

这就是外连接。

也就是说,内连接就是包含两个表中同时拥有的记录,而外连接中,左外连接是以左侧表为主,左侧表中的每个记录都会在左外连接中有体现,右外连接则是以右侧表为主,每个记录在右外连接都有体现,那么有没有既包含左侧表又包含右侧表的"全外连接"呢,答案是mysql不支持!
image-1710991682656

5.自连接

自连接是指在同一张表连接自身进行查询。

我们可以继续使用上面的成绩表和课程表来给大家说明,为了可观我们先把表拿下来:

mysql> select * from course;
+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | Java               |
|  2 | 中国传统文化       |
|  3 | 计算机原理         |
|  4 | 语文               |
|  5 | 高阶数学           |
|  6 | 英文               |
+----+--------------------+
6 rows in set (0.00 sec)
mysql> select * from score;
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  70.5 |          1 |         1 |
|  98.5 |          1 |         3 |
|  33.0 |          1 |         5 |
|  98.0 |          1 |         6 |
|  60.0 |          2 |         1 |
|  59.5 |          2 |         5 |
|  .... |     .....  |     ....  |
+-------+------------+-----------+
(省略了后面部分数据)
20 rows in set (0.00 sec)

我们依然用例题的方式来理解:

例子:显示所有"计算机原理"成绩比"Java"成绩高的成绩信息

首先我们要知道这里的是成绩比较,而成绩都在成绩表中,所以应该是用自连接来查看自己,但是这里涉及到两个科目,所以需要一些课程表的信息,但需要的不多,所以没必要也连接课程表,我们只需要知道这两个科目在成绩表中的id就行。

第一步:查看两个科目在成绩表中的course_id

select id,name from course where name = 'Java' or name = '计算机原理';
+----+-----------------+
| id | name            |
+----+-----------------+
|  1 | Java            |
|  3 | 计算机原理      |
+----+-----------------+
2 rows in set (0.001 sec)

第二步:发现id是1和3,我们就可以在成绩表中自连接查询了

mysql> select s1.* from score s1,score s2 where s1.student_id = s2.student_id and
     s1.score < s2.score and
     s1.course_id = 1 and
     s2.course_id = 3;

这里的意思是查询成绩表中,s1代表id为1的科目,s2代表id为3的科目,查询其中学生id一样并且s1成绩小于s2的,显示s1的所有信息,也就是成绩低那一科的信息。这里就是用自连接的方式去显示,同样也可以用join on 去写。

+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  70.5 |          1 |         1 |
|  33.0 |          3 |         1 |
+-------+------------+-----------+
2 rows in set (0.001 sec)

以上便上MySQL表之联合查询(多表查询),欢迎关注。一起学习,共同努力!

原文来至:https://devpress.csdn.net/devcloud/63bd281b80b9983378cda4b7.html?spm=1001.2101.3001.6650.6&utm_medium=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~activity-6-124528207-blog-131547981.235^v43^pc_blog_bottom_relevance_base2&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~CTRLIST~activity-6-124528207-blog-131547981.235^v43^pc_blog_bottom_relevance_base2&utm_relevant_index=11

0

评论区