当前位置：首页 > article >正文

【MySQL — 数据库基础】深入解析 MySQL 的联合查询

article 2025/2/19 13:37:44

在这里插入图片描述

1. 插入查询结果

语法

insert into table_name1 select* from table_name2 where restrictions ;

注意：查询的结果集合，列数 / 类型 / 顺序要和 insert into 后面的表相匹配；列的名字不要求相同；

create table student1(id int , name varchar(20));

create table student2(id int , name varchar(20));

要点讲解

1. 查询的结果集合，列数 / 类型 / 顺序要和 insert into 后面的表相匹配

insert into student1 values(1, '张三'), (2, '李四'), (100, '赵六');

insert into student2 select* from student1 where id < 50;  -- 插入查询结果

在这里插入图片描述

2. 插入查询的表的列名，与插入的表列名不要求相同

drop table student2;

create table student2(StudentId int , StudentName varchar(20));  

-- 新创建的 student2 的列名和 student1 不同

insert into student2 select* from student1 where id < 50;

在这里插入图片描述

3. 查询的结果集合，列数 / 类型 / 顺序和 insert into 后面的表不匹配，会报错

drop table student2;

create table student2( StudentName varchar(20), StudentId int); -- 类型和 student1 不匹配

insert into student2 select* from student1 where id < 50;

在这里插入图片描述

4. 两个表的列类型不匹配，可以指定插入顺序，也可以指定查询顺序

insert into student2(StudentId, StudentName) select* from student1;  -- 指定插入顺序

insert into student2 select name , id from student1;                 -- 指定查询顺序

2. 笛卡尔积

概念

笛卡尔积就像是把两个集合中的每一项都“配对”起来。

比如你有两个表，一个是人员名单，一个是产品清单：

人员名单：Alice 和 Bob
产品清单：Apple 和 Banana

如果你把每个人和每个产品都配对一次，就得到以下组合：

Alice 和 Apple
Alice 和 Banana
Bob 和 Apple
Bob 和 Banana

这就是笛卡尔积的结果。

简单来说，就是把一个表的每一行和另一个表的每一行都组合一下：笛卡尔积的列数，就是刚才两个表的列数之和；笛卡尔积的行数，就是两张表行数的乘积。

所谓的 “多表联合查询”，是基于笛卡尔积这样的运算展开的；但注意，笛卡尔积很容易产生大量不需要的数据，所以一般要避免在查询中直接用它，除非有特别的需要。

在这里插入图片描述

通过SQL计算笛卡尔积

create table class(classId int, className varchar(20)); 

insert into class values
(1, '一班'), 
(2, '2班');

create table student(id int , name varchar(20) , classId int );

insert into student values
(1, '张三', 1) , 
(2, '李四', 1) , 
(3, '王五', 2) , 
(4, '赵六', 2) ;

select* from student, class;  -- 通过 SQL 计算笛卡尔积，将两张表综合在一起进行查询

在这里插入图片描述

要想进行一些更有实际意义的查询，就需要指定一些额外的条件：

在笛卡尔积查询 student 和 class 时，我们期望进行笛卡尔积的记录是 classId 相同的记录

select* from student , class  where classId = classId ;

在这里插入图片描述

为了解决" classId 是哪张表的 classId " 这个歧义，我们需要显式指定 classId 是属于哪张表的

select* from student , class  where student.classId = class.classId ; 

-- 使用成员访问运算符. 来指定 classId 是属于哪张表的

在这里插入图片描述

3. 一次完整的联合查询过程

构造数据

drop table if exists classes ;
drop table if exists student ;
drop table if exists course ;
drop table if exists score ;

create table classes( 
    id int primary key auto_increment , 
    name varchar(20) , 
    `desc` varchar(100) 
);

create table student( 
    id int primary key auto_increment, 
    sn varchar(20) , 
    name varchar(20) , 
    qq_mail varchar(20) , 
    class_id int 
) ;

create table course( id int primary key auto_increment , name varchar(20) );

create table score ( score decimal(3,1) , student_id int , course_id int ) ;

-- 插入班级信息
insert into classes (name, `desc`) VALUES
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班', '学习了中国传统文学'),
('自动化2019级5班', '学习了机械自动化');


-- 插入学生信息
insert into student (sn, name, qq_mail, class_id) VALUES
('09982', '黑旋风李逵', 'xuanfengaqq.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);


-- 插入课程信息
insert into course (name) VALUES
('Java'), ('中国传统文化'), ('计算机原理'), ('语文'), ('高阶数学'), ('英文');


-- 插入各个同学的课程相关的成绩信息
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);

测试数据主要包含三个实体：学生，班级，课程；

学生 - 班级属于一对多的关系，学生 - 课程属于多对多的关系，所以我们需要通过一个关联表 score ，来体现课程和学生两个实体的联系；

内连接

语法

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

在这里插入图片描述

案例

查询“许仙”同学的成绩

在这里插入图片描述

初学多表查询阶段，不建议一次写出最终的SQL语句，可以一步步的优化查询，根据规律写出最终SQL;

在这里插入图片描述

查询所有同学的总成绩及个人信息

在这里插入图片描述

select student.id , student.name , sum(score.score) 
from student, score 
where student.id = score.student_id 
group by student.id ;

在这里插入图片描述

查询所有同学的总成绩, 列出同学姓名，课程名字，课程分数....

在这里插入图片描述

select 
	student.name as studentName , 
	course.name as courseName , 
	score.score 
from student , course , score 
where student.id = score.student_id and course.id = score.course_id ;

在这里插入图片描述

使用 join on 的方式查询，可以更好的体现出表两两之间的联合查询过程

select* from student 	
join score 
on student.id = score.student_id 
join course 
on score.course_id = course.id ;

在这里插入图片描述

select 
	student.name as studentName , 
	course.name as courseName , 
	score .score 
from student 
join score on student.id = score.student_id 
join course on course.id = score.course_id ;  -- 精简查询

在这里插入图片描述

外连接

语法

外连接也是 join on 这样的写法，但是不支持 from 多个表；

外连接分为左外连接和右外连接；

如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

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

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

案例

create table student( id int , name varchar(20) ) ;

create table score( id int , score int ) ;

insert into student values( 1 , '张三' ), ( 2 , '李四' ), ( 3 , '王五' );

insert into score values(1 , 90) , (2 , 80) , (3 , 70) ;

创建的这张表的数据是一一对应的，进行内连接和外连接，得到的结果完全相同；

但是如果上述的数据不再一一对应，内连接的结果和外连接就会出现差别；

update score set id = 4 where score = 70 ;

-- 修改数据

内连接

select name , score from student  ,   score where student.id = score.id;

-- 这个写法只能表示内连接，不能表示外连接

select name , score from student join score  on   student.id = score.id;

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

-- inner join 表示内连接，inner 关键字可以省略

-- 内连接，查询结果只会包含两个表中同时具备的数据

在这里插入图片描述

外连接

select name , score from student left join score on student.id = score.id ;

-- 左外连接

select name , score from student right join score on student.id = score.id ;

-- 右外连接

在这里插入图片描述

自连接

自连接是指在同一张表连接自身进行查询，这并不是常规操作，而是针对特殊的情况的处理；

案例

查询计算机组成原理分数高于 Java 的同学

在这里插入图片描述

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

如果发现要查询的条件是针对两行，而不是两列，就可以考虑使用自连接进行转换；自连接前要先清楚表的量级，如何表非常大，连接开销也会非常庞大，容易就把数据库搞死了.

子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

单行子查询：返回一行记录的子查询

查询与“不想毕业”同学的同班同学:

在这里插入图片描述

多行子查询：返回多行记录的子查询

使用多行子查询，就不能使用= > <这样的运算符直接比较了，但是可以使用 in

查询“语文”或”“英文”课程的成绩信息：

在这里插入图片描述

合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符union，union all。

使用 UNION 和 UNION ALL 时，前后查询的结果集中，字段需要一致（要求合并双方的类型，个数，顺序要相同，列名不要求相同）。

union

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

案例:查询id小于3，或者名字为“英文”的课程:

select* from course where id < 3 union select* from course where name ='英文' ;

-- 将两条SQL语句的查询结果一次性合在一张表中

select* from course where id < 3 or name = '英文' ;

-- 针对同一张表的查询， union 和 or 的效果相同，但是如果是不同的表，就只能用 union，不能用 or

在这里插入图片描述

union all

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

-- 查询id小于3，或者名字为“Java”的课程

select* from course where id < 3 union all select* from course where name = 'Java' ;

在这里插入图片描述

SQL查询中各个关键字的执行先后顺序

from > on> join > where > group by > with > having > select > distinct > order by > limit

在这里插入图片描述

查看全文

http://www.kler.cn/a/546645.html

支付宝 IoT 设备入门宝典（上）设备管理篇

20250214 随笔 Nginx 负载均衡在数据库中的应用

JavaScript + HTML5 Canvas 实现互动爱心雨

UE5中的快捷键汇总

Java实现MinIO上传PDF文件并配置浏览器在线打开而非下载

智能协同：数据集成平台与DeepSeek驱动的数据分析与智能调度革新

新版电脑通过wepe安装系统

2. 图片性能优化

Vue笔记（十）

NIO 和 AIO 的区别？

elementuiPlus日期范围选择el-date-picker动态禁用时间选择

DeepSeek全生态接入指南：官方通道+三大云平台

初学 mybatis

python_excel批量插入图片

进阶数据结构——树状数组

HTML之JavaScript循环结构

leetcode 1594. 矩阵的最大非负积

什么是Docker多架构容器镜像

消息队列之-RabbitMq 学习

SQL布尔盲注+时间盲注

1. 插入查询结果

语法

要点讲解

2. 笛卡尔积

概念

通过SQL计算笛卡尔积

3. 一次完整的联合查询过程

构造数据

内连接

语法

案例

外连接

语法

案例

自连接

案例

子查询

合并查询

union

union all

SQL查询中各个关键字的执行先后顺序

相关文章：