从入门到精通MySQL联合查询

2025-07-02 17:50
文章标签 mysql 联合 入门 精通 查询 database

本文主要是介绍从入门到精通MySQL联合查询，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

《从入门到精通MySQL联合查询》：本文主要介绍从入门到精通MySQL联合查询,本文通过实例代码给大家介绍的非常详细,需要的朋友可以参考下...

摘要

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

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

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

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

从入门到精通MySQL联合查询

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

从入门到精通MySQL联合查询

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

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可视化图：

班级表

从入门到精通MySQL联合查询

课程表

从入门到精通MySQL联合查询

分数表

从入门到精通MySQL联合查询

学生表

从入门到精通MySQL联合查询

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

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

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

从入门到精通MySQL联合查询

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

从入门到精通MySQL联合查询

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

从入门到精通MySQL联合查询

  1. 精减查询结果字段
select
 student.id,
 student.name,
 class.name
from 
  student,class 
where
  student.class_id = class.id 
and 
  student.`name` = '孙悟空';

从入门到精通MySQL联合查询

  1. 可以为表名指定别名
select
 stu.id,
 stu.name,
 c.name
from 
  student as stu,class as c
where
  stu.class_id =c.id 
and 
  stu.`name` = '孙悟空';

2. 内连接

select * from 表名1 as 别名1 , 表名2 as 别名2 where 连接条件 and 其他条件;
  1. 查询"唐三藏"同学的成绩
-- 查询唐三藏同学的成绩
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` = '唐三藏';

从入门到精通MySQL联合查询

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

从入门到精通MySQL联合查询

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

从入门到精通MySQL联合查询

3. 外连接

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

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

从入门到精通MySQL联合查询

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

从入门到精通MySQL联合查询

4. 自连接

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

--不为表指定别名
mysql> select * from score, score;
ERROR 1066 (42000): Not unique table/alias: 'score'
--指定别名
mysql> select * from score s1, score s2;
  1. 显示所有"MySQL"成绩比"JAVA"成绩高的成绩信息
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;

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

从入门到精通MySQL联合查询

5. 子查询

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

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

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

select student.`name`,student.class_id 
  from 
    student  
  where 
    class_id   = (select class_id from student where `name` = '不想毕业' ) 
  and 
    `name` != '不想毕业';

从入门到精通MySQL联合查询

✏️5.2 多行子查询

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

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

从入门到精通MySQL联合查询

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

✏️5.3 多列子查询

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

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

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);

从入门到精通MySQL联合查询

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

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

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

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;

从入门到精通MySQL联合查询

6. 合并查询

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

-- 创建⼀个新表并初始化数据
 create table student1 like student;
 
insert into student1 (name, sno, age, gender, enroll_date, class_id) values
('唐三藏', '100001', 18, 1, '1986-09-01', 1),
('刘备', '300001js', 18, 1, China编程'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表中的所有同学

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

从入门到精通MySQL联合查询

✏️6.2 union all

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

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

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

从入门到精通MySQL联合查询

7. 插入查询结果

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

示例：将student表中C++001班的学生复制到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 = 'C++001班';

从入门到精通MySQL联合查询

到此这篇关于从入门到精通MySQL联合查询的文章就介绍到这了,更多相关mysql联合查询内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)！

这篇关于从入门到精通MySQL联合查询的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1155275。

相关文章

深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
MySQL中C接口的实现

MySQL中C接口的实现

《MySQL中C接口的实现》本节内容介绍使用C/C++访问数据库,包括对数据库的增删查改操作,主要是学习一些接口的调用,具有一定的参考价值,感兴趣的可以了解一下... 目录准备mysql库使用mysql库编译文件官方API文档对象的创建和关闭链接数据库下达sql指令select语句前言：本节内容介绍使用C/
阅读更多...
mybatis直接执行完整sql及踩坑解决

mybatis直接执行完整sql及踩坑解决

《mybatis直接执行完整sql及踩坑解决》MyBatis可通过select标签执行动态SQL,DQL用ListLinkedHashMap接收结果,DML用int处理,注意防御SQL注入,优先使用#... 目录myBATiFBNZQs直接执行完整sql及踩坑select语句采用count、insert、u
阅读更多...
MySQL之搜索引擎使用解读

MySQL之搜索引擎使用解读

《MySQL之搜索引擎使用解读》MySQL存储引擎是数据存储和管理的核心组件,不同引擎（如InnoDB、MyISAM）采用不同机制,InnoDB支持事务与行锁,适合高并发场景；MyISAM不支持事务,... 目录mysql的存储引擎是什么MySQL存储引擎的功能MySQL的存储引擎的分类查看存储引擎1.命令
阅读更多...
MyBatis Plus大数据量查询慢原因分析及解决

MyBatis Plus大数据量查询慢原因分析及解决

《MyBatisPlus大数据量查询慢原因分析及解决》大数据量查询慢常因全表扫描、分页不当、索引缺失、内存占用高及ORM开销,优化措施包括分页查询、流式读取、SQL优化、批处理、多数据源、结果集二次... 目录大数据量查询慢的常见原因优化方案高级方案配置调优监控与诊断总结大数据量查询慢的常见原因MyBAT
阅读更多...
一文详解MySQL索引(六张图彻底搞懂)

一文详解MySQL索引(六张图彻底搞懂)

《一文详解MySQL索引(六张图彻底搞懂)》MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度,：本文主要介绍MySQL索引的相关资料,文中通过代码介绍的... 目录一、什么是索引？为什么需要索引？二、索引该用哪种数据结构？1. 哈希表2. 跳表3. 二叉排序树4.
阅读更多...
MySQL批量替换数据库字符集的实用方法(附详细代码)

MySQL批量替换数据库字符集的实用方法(附详细代码)

《MySQL批量替换数据库字符集的实用方法(附详细代码)》当需要修改数据库编码和字符集时,通常需要对其下属的所有表及表中所有字段进行修改,下面：本文主要介绍MySQL批量替换数据库字符集的实用方法... 目录前言为什么要批量修改字符集？整体脚本脚本逻辑解析1. 设置目标参数2. 生成修改表默认字符集的语句3
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2