MySQL之联合查询

🕗 发布于 2024-11-18 04:05 mysql java

前文我们了解到了数据库设计的范式要求，故生活中很多相互关联的数据被拆分开来，但彼此之间通过某种条件链接，此文联合查询就是通过多表之间的连接关系，来查询我们想要的数据，即 《联合查询》

1. 联合查询简介

1.1 为什么要使用联合查询

在数据设计时由于范式的要求，数据被拆分到多个表中，那么要查询⼀个条数据的完整信息，就要从多个表中获取数据，如下图所⽰：要获取学⽣的基本信息和班级信息就要从学⽣表和班级表中获取，这时就需要使⽤联合查询，这⾥的联合指的是多个表的组合。

在这里插入图片描述

1.2 多表联合查询时MYSQL内部是如何进行计算的

首先我们创建两个表，用于我们探讨问题

# 创建学生表
create table student (
id bigint,
name varchar(20),
sex char,
class_id bigint
);
insert into student  value (1,'张三','男',1),(2,'李四','女',1),(3,'王五','男',2);

#创建班级表
create table class (
id bigint,
name varchar(20)
);
insert into class  value (1,'Java'),(2,'C++'),(3,'Python');

参与查询的所有表取笛卡尔积，结果集在临时表中
观察哪些记录是有效数据，根据两个表的关联关系过滤掉无效数据

如果联合查询表的个数越多，表中的数据量越大，临时表就会越大，所以建议根据实际情况确定联合查询表的个数

1.3 一个完整的联合查询过程

首先我们创建多个表，用于探讨联合查询过程

#课程表
create table course(
id bigint PRIMARY KEY auto_increment,
name varchar(10)
);
insert into course (name) values ('Java'), ('C++'), ('MySQL'), ('操作系统'), ('计算机⽹络'), ('数据结构');

# 班级表
create table class (
id bigint,
name varchar(20)
);
insert into class  value (1,'软件1班'),(2,'软件2班'),(3,'软件3班');

#学生表
create table student (
name varchar(20),
sno varchar(10),
age int,
gender boolean,
enroll_date date,
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 score (
score double,
student_id varchar(10),
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);

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

确定参与查询的表，为学⽣表和班级表

# 在from后同时写所有参与查询的表，并⽤逗号隔开
select * from student , class ;

在这里插入图片描述

确定连接条件，student表中的class_id与class表中id列的值相等

 #在where⼦句中加⼊连接条件
 select * from student , class where student.class_id=class.id;

在这里插入图片描述

加⼊查询条件

我们发现直接查询name时，导致MySQL不清楚到底是student的name还是class的name，此时我们要明确指定

在这里插入图片描述

# 明确指定——》这时可以⽤“表名.列号”的⽅式指定具体的列
select * from student , class where student.class_id=class.id and student.name ='孙悟空';

在这里插入图片描述

精减查询结果字段

select student.sno,student.name,student.age,class.name 
from student,class 
where student.class_id=class.id and student.name='孙悟空';

在这里插入图片描述

可以为表名指定别名

select s.sno,s.name,s.age,c.name 
from student s,class c 
where s.class_id=c.id and s.name='孙悟空';

在这里插入图片描述

2. 内连接

2.1 语法

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

2.2 示例

查询"唐三藏"同学的成绩

select s.name, sc.score from student s join score sc on sc.student_id = s.sno where s.name = '唐三藏';

在这里插入图片描述

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

# 内连接语法格式 1 
select s.* ,sum(sc.score) from student s,score sc where s.id=sc.student_id group by s.id;

# 内连接语法格式 2
select s.* ,sum(sc.score) from student s join score sc on s.id=sc.student_id group by s.id;

在这里插入图片描述

查询所有同学每⻔课的成绩，及同学的个⼈信息

select s.* , sc.score,c.name from student s , score sc ,course c 
where s.id=sc.student_id and sc.course_id=c.id;

在这里插入图片描述

3. 外连接

外连接分为左外连接、右外连接和全外连接三种类型，MySQL不支持全外连接
左外连接：返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对应字段会显⽰为NULL
右外连接：与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录，则结果集中对应字段会显示为NULL
（MySQL中无论左外连接还是右外连接，实质都是先左外连接）
全外连接：结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某一边表中没有匹配的记录，则结果集中对应字段会显示为NULL

3.1 语法

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

3.2 示例

查询没有参加考试的同学信息

# 左连接以JOIN左边的表为基准，左表显示全部记录，右表中没有匹配的记录NULL填充
select s.id,s.name,s.sno,s.age,sc.* from 
student s left join score sc on s.id=sc.student_id 
where sc.score is null;

在这里插入图片描述

查询没有学⽣的班级

# 查询没有学生的班级
select c.*,s.id,s.name,s.age from  
student s right join class c  on s.class_id=c.id 
where s.id is null;

在这里插入图片描述

4. 自连接

4.1 应用场景

==自连接是自己与自己取笛卡尔积，可以把行转化成列，在查询的时候可以使⽤where条件对结果进行过滤，或者说实现行与行之间的⽐较，在做表连接时为表起不同的别名。

 # 不为表指定别名
 select * from score,score;

在这里插入图片描述

# 指定别名
select * from score s1, score s2;

在这里插入图片描述

4.2 示例

显示所有"MySQL"成绩比"JAVA"成绩高的成绩信息

select s1.* from score s1, score s2, 
course c1,course c2
# 首先自连接中学生id要一样确保是同一个学生
where s1.student_id = s2.student_id 
# MySQL课程
and s1.course_id = c1.id  
# Java 课程
and s2.course_id = c2.id 
# MySQL成绩大于Java成绩
and s1.score > s2.score
# 指定c1是MySQL，c2是Java
and c1.`name` = 'MySQL'
and c2.`name` = 'Java';

在这里插入图片描述

显⽰所有"MySQL"成绩比"JAVA"成绩⾼的学生信息和班级以及成绩信息

# 显示所有"MySQL"成绩比"JAVA"成绩高的成绩信息
select s1.* ,student.name,class.name from score s1, score s2, 
course c1,course c2 ,student,class
# 首先自连接中学生id要一样确保是同一个学生
where s1.student_id = s2.student_id 
# MySQL课程
and s1.course_id = c1.id  
# Java 课程
and s2.course_id = c2.id 
# MySQL成绩大于Java成绩
and s1.score > s2.score
# 指定c1是MySQL，c2是Java
and c1.`name` = 'MySQL'
and c2.`name` = 'Java'
# 学生的id对应相应的课程
and student.id=s1.student_id
# 学生的id对应相应的班级
and student.class_id=class.id;

在这里插入图片描述

5.子查询

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

5.1 语法

 select * from table1 where col_name1 {= | IN} (
 select col_name1  from table2 where col_name2 {= | IN} [(
 select ...)
 ] ... )

5.2 单行子查询

嵌套的查询中只返回⼀行数据

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

select * from student where class_id = (select class_id from student 
where name = '不想毕业');

在这里插入图片描述

5.3 多行子查询

嵌套的查询中返回多⾏数据，使⽤[NOT]IN关键字

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

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

在这里插入图片描述

6.合并查询

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

6.1 创建新表并初始化数据

# 创建⼀个新表并初始化数据
 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.2 Union

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

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

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

在这里插入图片描述

6.3 Union all

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

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

在这里插入图片描述

7.插入查询

7.1 语法

INSERT INTO table_name [(column [, column ...])] SELECT ...

7.2 示例

将student表中软件1班的学生复制到student1表中

# 插入查询结果
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 = '软件1班';
# 验证是否查询结果插入成功
select * from student1;

在这里插入图片描述

原文地址：https://blog.csdn.net/2302_81707171/article/details/143625349

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：Linux系统性能调优技巧详解
下一篇：docker网络

MoneyPrinterTurbo - AI自动生成高清短视频
MoneyPrinterTurbo是一款基于AI大模型的开源软件，旨在通过一键操作帮助用户自动生成高清短视频。只需提供一个视频主题或 **关键词** ，就可以全自动生成视频文案、视频素材、视频字幕、
阅读更多2024-11-18
跨平台WPF框架Avalonia教程五
在 Avalonia 11 之前，控件主题是使用标准样式创建的。然而，这种方法存在一个根本性的问题：一旦样式被应用到控件上，就没有办法移除它。因此，如果你想为特定的控件实例或用户界面（UI）部分更改
阅读更多2024-11-18
Linux 下网络套接字(Socket) 与udp和tcp 相关接口
Linux下套接字的介绍与使用
阅读更多2024-11-18
react 中 useReducer Hook 作用
useState`是最基本的状态管理钩子。`useReducer`提供了一种更可预测的状态管理方式。
阅读更多2024-11-18
【自学笔记】推荐系统
（Retrieval）步骤的目的是从庞大的候选集合中快速筛选出一小部分最有可能满足用户需求的项目。这样做可以简化梯度计算，更显著的变化是，如果人没有为任何样本打分，那代入TA的初始参数。我们需要做的是
阅读更多2024-11-18
【jvm】方法区是否存在GC
6.在Hotspot虚拟机中，方法区对应的是持久代（PermGen space，在JDK 8及之后被元空间Metaspace取代），尽管持久代上的GC较少，但并不代表完全没有。2.在JVM（Java虚
阅读更多2024-11-18
2411rust,异步函数
2411rust,异步函数
阅读更多2024-11-18
用 Python 从零开始创建神经网络（八）：梯度、偏导数和链式法则
在我们继续编写我们的神经网络代码之前，最后两个需要解决的难题是梯度和偏导数的相关概念。我们到目前为止解决的导数案例都是函数中只有一个独立变量的情况——也就是说，结果完全依赖于xx（在我们的案例中）。然
阅读更多2024-11-18
深度学习之One Stage目标检测算法2
YOLO（You Only Look Once: Unified, Real-Time Object Detection）是one-stage detection的开山之作。之前的物体检测方法首先需要
阅读更多2024-11-18
TCP协议的代理IP是什么？
对于需要大量数据传输和高效通信的场景，代理IP技术提供了至关重要的支持。TCP协议作为互联网通信中最基础和最常用的协议之一，与代理IP技术的结合成为了许多企业和开发者进行网络操作时不可或缺的一部分。那
阅读更多2024-11-18

MySQL之联合查询

1. 联合查询简介

1.1 为什么要使用联合查询

1.2 多表联合查询时MYSQL内部是如何进行计算的

1.3 一个完整的联合查询过程

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

2. 内连接

2.1 语法

2.2 示例

3. 外连接

3.1 语法

3.2 示例

4. 自连接

4.1 应用场景

4.2 示例

5.子查询

5.1 语法

5.2 单行子查询

5.3 多行子查询

6.合并查询

6.1 创建新表并初始化数据

6.2 Union

6.3 Union all

7.插入查询

7.1 语法

7.2 示例

相关文章