从入门到精通MySQL联合查询！

admin • • SQL技巧

从入门到精通MySQL联合查询！

作者：长安城没有风
这篇文章主要介绍了从入门到精通MySQL联合查询,本文通过实例代码给大家介绍的非常详细,需要的朋友可以参考下。

📕摘要

前面我们学习了数据库设计时要满足三大范式，也就意味着数据会被拆分到许多张表中，当我们想查询一个学生的基本信息与成绩时，此时就会涉及到学生表，班级表，成绩表等多张数据表，但我们给用户展示信息时并不会把冗余的数据也展示给用户，所以我们就需要用到联合查询从多张表中查询出有用的数据。此时的‘联合’，就是指多张数据表的组合。

📕1. 多表联合查询时MySQL内部原理

当我们进行多表联合查询时，MySQL内部会进行以下操作：

参与查询的所有表取笛卡尔积，结果集在临时表中

20251126001

观察哪些记录是有效数据，根据两个表的关联关系过滤掉无效数据

20251126002

=======================================================================
首先我们要构造一个练习数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
create database if not exists test; -- 创建库
use test;
-- 课程表
create table if not exists course(
id bigint primary key auto_increment,
`name` varchar(20) not null
);
insert into course (name) values ('Java'), ('C++'), ('MySQL'), ('操作系统'), ('计
算机网络'), ('数据结构');
-- 学生表
create table if not exists student(
id bigint primary key auto_increment,
`name` varchar(20),
sno varchar(20),
age bigint,
gender bigint,
enroll_date varchar(20),
class_id bigint
);
insert into student (name, sno, age, gender, enroll_date, class_id) values
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('孙悟空', '100002', 18, 1, '1986-09-01', 1),
('猪悟能', '100003', 18, 1, '1986-09-01', 1),
('沙悟净', '100004', 18, 1, '1986-09-01', 1),
('宋江', '200001', 18, 1, '2000-09-01', 2),
('武松', '200002', 18, 1, '2000-09-01', 2),
('李逹', '200003', 18, 1, '2000-09-01', 2),
('不想毕业', '200004', 18, 1, '2000-09-01', 2);
-- 班级表
create table if not exists class(
id bigint primary key auto_increment,
`name` varchar(20)
);
insert into class(name) values ('Java001班'), ('C++001班'), ('前端001班');
-- 分数表
create table if not exists score(
id bigint primary key auto_increment,
score bigint,
student_id bigint,
course_id bigint
);
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),
(80, 7, 2),(92, 7, 6);

Navicat可视化图：

班级表

20251126003

课程表

20251126004

分数表

20251126005

学生表

20251126006

✏️1.1 实例：一个完整的联合查询过程

查询学生姓名为孙悟空的详细信息，包括学生个人信息和班级信息

  1. 首先确定参与查询的表，分别是student表与class表
1
select * from student,class;

20251126007

  1. 确定连接条件，条件为student表中的class_id要与class表中的id相等
1
select * from student,class where student.class_id = class.id;

20251126008

  1. 加入查询条件
1
select * from student,class where student.class_id = class.id and student.`name` = '孙悟空';

20251126009

  1. 精减查询结果字段
1
2
3
4
5
6
7
8
9
10
select
student.id,
student.name,
class.name
from
student,class
where
student.class_id = class.id
and
student.`name` = '孙悟空';

20251126010

  1. 可以为表名指定别名
1
2
3
4
5
6
7
8
9
10
select
stu.id,
stu.name,
c.name
from
student as stu,class as c
where
stu.class_id =c.id
and
stu.`name` = '孙悟空';

📕2. 内连接

1
select * from 表名1 as 别名1 , 表名2 as 别名2 where 连接条件 and 其他条件;
  1. 查询"唐三藏"同学的成绩
1
2
3
4
5
6
7
8
9
10
11
-- 查询唐三藏同学的成绩
select
student.`name`,score.score,course.`name`
from
student,score,course
where
student.id = score.student_id
and
score.course_id = course.id
and
student.`name` = '唐三藏';

20251126011

  1. 查询所有同学的总成绩，及同学的个人信息
1
2
3
4
5
6
7
8
select
student.`name`,sum(score.score) as '总分'
from
student,score
where
student.id = score.student_id
group by
`name`;

20251126012

  1. 查询所有同学每门课的成绩，及同学的个人信息
1
2
3
4
5
6
7
8
select
student.`name`,score.score,course.`name`
from
student,score,course
where
student.id = score.student_id
and
score.course_id = course.id;

20251126013

📕3. 外连接

外连接分为左外连接、右外连接和全外连接三种类型，因为MySQL不支持全外连接，所以本文不再介绍外连接部分。
• 左外连接：返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对
应字段会显示为NULL。
• 右外连接：与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记
录，则结果集中对应字段会显示为NULL。

1
2
3
4
-- 左外连接，表1完全显⽰
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显⽰
select 字段 from 表名1 right join 表名2 on 连接条件;
  1. 查询没有参加考试的同学信息
1
select * from student left join score on student.id = score.student_id where score.score is null;

pZAW0G6.png

  1. 查询没有学生的班级
1
select * from student right join class on class.id = student.class_id where student.id is null;

20251126015

📕4. 自连接

自连接是自己与自己取笛卡尔积，可以把行转化成列，在查询的时候可以使用where条件对结果进行过滤，以至于实现行与行之间的比较，在做自连接时要为表起别名（否则报错）。

1
2
3
4
5
--不为表指定别名
mysql> select * from score, score;
ERROR 1066 (42000): Not unique table/alias: 'score'
--指定别名
mysql> select * from score s1, score s2;
  1. 显示所有"MySQL"成绩比"JAVA"成绩高的成绩信息
1
select s1.student_id as '学生',s1.score as 'MySQL',s2.score as 'JAVA' from (select * from score where  course_id = 3) as s1 ,(select * from score where course_id = 1 ) as s2 where s1.student_id = s2.student_id and s1.score > s2.score;

思路：先查出JAVA的成绩，在查出MYSQL的成绩，两张表分别各自包含JAVA和MYSQL成绩，然后进行连接，连接条件为表一与表二学生id相同，限制条件为MYSQL成绩大于JAVA成绩

20251126016

📕5. 子查询

子查询是把⼀个SELECT语句的结果当做别一个SELECT语句的条件，也叫嵌套查询。

1
select * from table1 where condition [= |in]（select * from where (......)）
✏️5.1 单行子查询

示例： 查询与"不想毕业"同学的同班同学

1
2
3
4
5
6
7
select student.`name`,student.class_id
from
student 
where
class_id   = (select class_id from student where `name` = '不想毕业' )
and
`name` != '不想毕业';

20251126017

✏️5.2 多行子查询

示例：查询"MySQL"或"Java"课程的成绩信息

1
select * from score where course_id in (select course.id from course where `name` = 'Java' or `name` = 'MySQL');

20251126018

使用 not in 可以查询除了"MySQL"或"Java"课程的成绩

✏️5.3 多列子查询

单行子查询和多行子查询都只返回一列数据，多列子查询中可以返回多个列的数据，外层查询与嵌套的内层查询的列要匹配

示例：查询重复录入的分数

1
select *  from score where (score,student_id,course_id) in (select score,student_id,course_id from score group by score,student_id,course_id having count(*)>1);

20251126019

✏️5.4 在from子句中使用子查询

当⼀个查询产生结果时，MySQL自动创建一个临时表，然后把结果集放在这个临时表中，最终返回
给用户，在from子句中也可以使用临时表进行子查询或表连接操作

示例：查询所有比"Java001班"平均分高的成绩信息

1
select * from score as s ,(select avg(score) as avg_score from score where student_id in ( select student_id from student where class_id = 1))  as tmp where s.score > tmp.avg_score;

20251126020

📕6. 合并查询

为了合并多个select操作返回的结果，可以使⽤集合操作符 union，union all

1
2
3
4
5
6
7
8
-- 创建⼀个新表并初始化数据
create table student1 like student;
insert into student1 (name, sno, age, gender, enroll_date, class_id) values
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('刘备', '300001', 18, 1, '1993-09-01', 3),
('张⻜', '300002', 18, 1, '1993-09-01', 3),
('关⽻', '300003', 18, 1, '1993-09-01', 3);
✏️6.1 union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

示例：查询student表中 id < 3 的同学和student1表中的所有同学

1
select * from student where id<3 union select * from student1;

pZAWlGV.png

✏️6.2 union all

该操作符⽤于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

示例：查询student表中 id < 3 的同学和student1表中的所有同学

1
select * from student where id<3 union all select * from student1;

pZAW3xU.png

📕7. 插入查询结果

1
insert into 表名(列名1，列名2) select .....

示例：将student表中C++001班的学生复制到student1表中

1
2
3
insert into student1 (name, sno, age, gender, enroll_date, class_id)
select s.name, s.sno, s.age, s.gender, s.enroll_date, s.class_id
from student s, class c where s.class_id = c.id and c.name = 'C++001班';

pZAWGMF.png

到此这篇关于从入门到精通MySQL联合查询的文章就介绍到这了。

 

 

 

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；

Excelbook.cn Excel技巧 SQL技巧 Python 学习！

你将获得：

1、价值上万元的专业的PPT报告模板。

2、专业案例分析和解读笔记。

3、实用的Excel、Word、PPT技巧。

4、VIP讨论群，共享资源。

5、优惠的会员商品。

6、一次付费只需129元，即可下载本站文章涉及的文件和软件。

文章版权声明 1、本网站名称：Excelbook
2、本站永久网址：http://www.excelbook.cn
3、本网站的文章部分内容可能来源于网络，仅供大家学习与参考，如有侵权，请联系站长王小琥进行删除处理。
4、本站一切资源不代表本站立场，并不代表本站赞同其观点和对其真实性负责。
5、本站一律禁止以任何方式发布或转载任何违法的相关信息，访客发现请向站长举报。
6、本站资源大多存储在云盘，如发现链接失效，请联系我们我们会第一时间更新。

THE END
分享
二维码
< <上一篇
下一篇>>