0
0

【MySQL】MySQL表之联合查询（多表查询）

📌前言：本篇博客介绍MySQL数据库的MySQL表之联合查询（多表查询），学习MySQL之前要先安装好MySQL，如果还没有安装的小伙伴可以看看博主前面的博客，里面有详细的安装教程。

那我们废话不多说，直接进入主体！🚀🚀🚀

MySQL表之联合查询（多表查询）

一.联合查询

然后就到联合查询，所谓联合，就是把多个表的记录往一起合并，一起进行查询，也叫多表查询。多表查询是整个

sql

中最复杂的部分，也是笔试中爱考的部分，但是实际开发中一般禁止使用多表查询。

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

1.笛卡尔积

📌多表查询中的核心操作，就是笛卡尔积，笛卡尔积的计算非常简单，就类似于排列组合，笛卡尔积是针对任意两张表之间进行的运算。

比如：

再比如：

图中的就是笛卡尔积的运算过程：

  1. 先拿第一张表的第一条记录，和第二张表的每个记录，分别组合，得到一组新的数据。
  2. 然后继续再拿第一张表的第二条记录，重复刚刚的操作。
  3. 最后形成的所有新的数据，就是我们的笛卡尔积。

所以针对

A

B

两张表，得到的笛卡尔积，此时笛卡尔积的列数，就是A的列数+B的列数，笛卡尔积的行数，就是

A的行数 * B的行数

。所以在实际工作中，公司的数据量非常大，那么进行多表查询的时候就很可能把数据给搞挂了，所以就直接一刀切，禁止多表查询这种危险性较大的操作。

上述操作我们在MySQL中操作一下：

先创建好

student

和

class

表：

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 classvalues(1,'java1班');
       insert into classvalues(1,'java2班');

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

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

mysql> select * from student,class;


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

像两张表中，classId这一列相等的才是我们需要的数据，对于这样的相等条件，就称为“连接条件”。带有连接条件的笛卡尔积，其实就是"多表查询"了。如果笛卡尔积中两个列的名字相同，在写条件的时候可以通过

表名.列名

的格式来访问，如果列名不会混淆，也就是两个列名条件不同名的话，可以通过

表名.列名

访问，也可以省略表名。

如:

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

然后我们看到还是有一些其实我们不需要的数据，在实际查询中，我们需要的数据往往只有几列，所以我们在查询的时候可以不用

select *

，而是select 我们需要的那几列。如我们下面的表只想看学生对应班级名。

如：

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','黑旋风李逵','[email protected]',1),('00835','菩提老祖',null,1),('00391','白素贞',null,1),('00031','许仙','[email protected]',1),('00054','不想毕业',null,1),('51234','好好说话','[email protected]',2),('83223','tellme',null,2),('09527','老外学中文','[email protected]',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);

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

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

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

3.内连接

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

  • 例子1：查询“许仙”同学的成绩

📌第一步：查表

首先我们要想到，查询某位同学的成绩，所用到的表应该是哪些，这里是学生表和成绩表，所以我们查询的应该是学生表和成绩表，最简单的联合起来就是在select的时候用逗号连接两个表，比如

select * from student,score;

我们看一下查询结果（此处省略n行）：

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

📌第二步：确定条件

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

📌第三步：查询结果

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

这便是我们其中一种内连接，也就是用条件匹配来联合多张表，我们还有一种内连接的写法是用

join...on

，其实都差不多，前面的是用逗号和where连接查询两张表，这里就用

join...on

连接，如下面的写法的查询结果是和上面一样的：

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

内连接：

  1. select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
  2. 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;

查询结果：

像这样的多表查询，看起来比较复杂，按顺序多写几遍就可以得到规律，很流畅的就写出来了。比如这里的查询的是同学信息和总成绩，使用的表是学生表和成绩表，条件是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;

查询结果：

当然三张表的也可以用

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 classvalues(1,'java1班');
          insert into classvalues(,'java2班');

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

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

查询结果：

mysql> select student2.name,class.name from class,student2 where student2.classes_id =class.id;+--------+----------+| name   | name     |+--------+----------+| 张三   | java1班  || 李四   | java1班  || 王五   | java2班  |+--------+----------+

上面的这种写法就相当于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不支持！

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是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 去写。

🚩以上便上MySQL表之联合查询（多表查询），欢迎关注。一起学习，共同努力！

还有一件事：

标签： mysql 数据库 java
本文转载自: https://blog.csdn.net/weixin_54225715/article/details/124528207
版权归原作者 恒等于C 所有， 如有侵权，请联系我们删除。
登录后发布评论

“【MySQL】MySQL表之联合查询（多表查询）”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

【Python】【持续项目】Python-安全项目搜集

泷羽sec学习day5-安全见闻6

随身 WiFi 通过 USB 连接路由器共享网络 扩展网络覆盖范围

如何将 Kubernetes 中的两个 Nginx Ingress 合并成一个：操作步骤与注意事项

单元测试使用JUnit 5

【大数据学习 | HBASE高级】hive操作hbase

MySQL Workbench 8.0 CE 中文汉化教程

文章导航