数据库的联合查询

devtools/2024/11/26 11:27:28/

数据库的联合查询

  • 简介
    • 为什么要使⽤联合查询
    • 多表联合查询时MYSQL内部是如何进⾏计算的
    • 构造练习案例数据
    • 案例：⼀个完整的联合查询的过程
  • 内连接
    • 语法
    • ⽰例
  • 外连接
    • 语法
  • ⽰例
  • ⾃连接
    • 应⽤场景
    • 示例
    • 表连接练习
  • ⼦查询
    • 语法
    • 单⾏⼦查询
    • 多⾏⼦查询
    • 多列⼦查询
    • 在from⼦句中使⽤⼦查询
  • 合并查询
    • 创建新表并初始化数据
    • Union
    • Union all
  • 插⼊查询结果
    • 语法
    • 示例

简介

为什么要使⽤联合查询

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

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

• 参与查询的所有表取笛卡尔积，结果集在临时表中
在这里插入图片描述
• 观察哪些记录是有效数据，根据两个表的关联关系过滤掉⽆效数据
在这里插入图片描述
如果联合查询表的个数越多，表中的数据量越⼤，临时表就会越⼤，所以根据实际情况确定联合查询
表的个数

构造练习案例数据

sql">-- ----------------------------
-- Table structure for class
-- ----------------------------CREATE TABLE `class`  (`class_id` bigint NOT NULL AUTO_INCREMENT,`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (`class_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES (1, '计算机系2019级1班');
INSERT INTO `class` VALUES (2, '中文系2019级3班');
INSERT INTO `class` VALUES (3, '自动化2019级5班');
-- ----------------------------
-- Table structure for course
-- ----------------------------CREATE TABLE `course`  (`course_id` bigint NOT NULL AUTO_INCREMENT,`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,PRIMARY KEY (`course_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES (1, 'Java');
INSERT INTO `course` VALUES (2, '中国传统文化');
INSERT INTO `course` VALUES (3, '计算机原理');
INSERT INTO `course` VALUES (4, '语文');
INSERT INTO `course` VALUES (5, '高阶数学');
INSERT INTO `course` VALUES (6, '英文');-- ----------------------------
-- Table structure for student
-- ----------------------------CREATE TABLE `student`  (`student_id` bigint NOT NULL AUTO_INCREMENT,`sn` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,`mail` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,`class_id` bigint NULL DEFAULT NULL,PRIMARY KEY (`student_id`) USING BTREE,UNIQUE INDEX `sn`(`sn` ASC) USING BTREE,INDEX `class_id`(`class_id` ASC) USING BTREE,CONSTRAINT `student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`class_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '09982', '黑旋风李逵', 'xuanfeng@qq.com', 1);
INSERT INTO `student` VALUES (2, '00835', '菩提老祖', NULL, 1);
INSERT INTO `student` VALUES (3, '00391', '白素贞', NULL, 1);
INSERT INTO `student` VALUES (4, '00031', '许仙', 'xuxian@qq.com', 1);
INSERT INTO `student` VALUES (5, '00054', '不想毕业', NULL, 1);
INSERT INTO `student` VALUES (6, '51234', '好好说话', 'say@qq.com', 2);
INSERT INTO `student` VALUES (7, '83223', 'tellme', NULL, 2);
INSERT INTO `student` VALUES (8, '09527', '老外学中文', 'foreigner@qq.com', 2);-- ----------------------------
-- Table structure for score
-- ----------------------------CREATE TABLE `score`  (`score_id` bigint NOT NULL AUTO_INCREMENT,`student_id` bigint NULL DEFAULT NULL,`course_id` bigint NULL DEFAULT NULL,`score` decimal(5, 2) NULL DEFAULT NULL,PRIMARY KEY (`score_id`) USING BTREE,INDEX `student_id`(`student_id` ASC) USING BTREE,INDEX `course_id`(`course_id` ASC) USING BTREE,CONSTRAINT `score_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT `score_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 21 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES (1, 1, 1, 70.50);
INSERT INTO `score` VALUES (2, 1, 3, 98.50);
INSERT INTO `score` VALUES (3, 1, 5, 33.00);
INSERT INTO `score` VALUES (4, 1, 6, 98.00);
INSERT INTO `score` VALUES (5, 2, 1, 60.00);
INSERT INTO `score` VALUES (6, 2, 5, 59.50);
INSERT INTO `score` VALUES (7, 3, 1, 33.00);
INSERT INTO `score` VALUES (8, 3, 3, 68.00);
INSERT INTO `score` VALUES (9, 3, 5, 99.00);
INSERT INTO `score` VALUES (10, 4, 1, 67.00);
INSERT INTO `score` VALUES (11, 4, 3, 23.00);
INSERT INTO `score` VALUES (12, 4, 5, 56.00);
INSERT INTO `score` VALUES (13, 4, 6, 72.00);
INSERT INTO `score` VALUES (14, 5, 1, 81.00);
INSERT INTO `score` VALUES (15, 5, 5, 37.00);
INSERT INTO `score` VALUES (16, 6, 2, 56.00);
INSERT INTO `score` VALUES (17, 6, 4, 43.00);
INSERT INTO `score` VALUES (18, 6, 6, 79.00);
INSERT INTO `score` VALUES (19, 7, 2, 80.00);
INSERT INTO `score` VALUES (20, 7, 6, 92.00);

案例：⼀个完整的联合查询的过程

• 查询学⽣姓名为许仙的详细信息，包括学⽣个⼈信息和班级信息

  1. 确定参与查询的表，学⽣表和班级表
    在这里插入图片描述
  2. 确定连接条件，student表中的class_id与class表中id列的值相等
    class id在两张表中都存在，MYSQL分不清当前语句中的class id应该取自哪张表
    可以通过表名.列名的方式来解决这个问题
sql"># 在where子句中加入连接条件
select * from student, class where student.class_id = class.class_id;

在这里插入图片描述

  1. 加⼊查询条件
    在这里插入图片描述
sql"># 依题意添加where条件
select * from student,class where student.class_id = class.class_id and student.name = '许仙';

在这里插入图片描述

  1. 精减查询结果字段
sql">select student.student_id,student.name,student.mail,class.name 
from student,class 
where student.class_id = class.class_id 
and student.name = '许仙';

在这里插入图片描述

  1. 可以为表名指定别名
sql">select s.student_id,s.name,s.mail,c.name 
from student s,class c 
where s.class_id = c.class_id 
and s.name = '许仙';

在这里插入图片描述

内连接

语法

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

⽰例

• 查询"菩提老祖"的成绩

sql"># 查询"菩提老祖"的成绩
select st.name,sc.score from student st, score sc where st.student_id = sc.student_id and st.name = '菩提老祖';select st.name,sc.score from student st join score sc on st.student_id = sc.student_id where st.name = '菩提老祖';

在这里插入图片描述

• 查询所有人的总成绩，及每个人的个⼈信息

sql"># 查询所有人的总成绩，及每个人的个人信息
select st.student_id,st.name,st.mail,c.name,SUM(sc.score) 
from student st,score sc,class c
where st.student_id = sc.student_id 
and c.class_id = st.class_id
group by st.student_id;

在这里插入图片描述

Group by 使⽤了student.student_id进⾏分组，查询表列表中的student.name、student.mail和class.name没有出现在Group by分组中，也没有包含在聚合函数中，这是因为SQL规定在Group by分组查询时，如果查询列表中的列没有出现在GROUP BY⼦句中，但这些列的值在每个分组内部是相同的，那么它们可以出现在查询结果中。
• 查询所有同学每⻔课的成绩，及同学的个⼈信息

sql"># 查询所有同学每门课的成绩，及同学的个人信息
select st.student_id,st.name,st.mail,cl.name,cr.name,sc.score
fromstudent st,class cl,score sc,course cr
where st.student_id = sc.student_id
and cl.class_id = st.class_id
and cr.course_id = sc.course_id
order byst.student_id;

在这里插入图片描述
结果集中没有"老外学中文"同学的成绩，因为score表中没有这位同学的记录
在这里插入图片描述

外连接

• 外连接分为左外连接、右外连接和全外连接三种类型，MySQL不⽀持全外连接。
• 左外连接：返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对
应字段会显⽰为NULL。
• 右外连接：与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记
录，则结果集中对应字段会显⽰为NULL。
• 全外连接：结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某⼀边表中没有匹
配的记录，则结果集中对应字段会显⽰为NULL。

语法

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

⽰例

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

sql"># 查询没有参加考试的同学信息
# 左连接以JOIN左边的表为基准，左表显示全部记录，右表中没有匹配的记录用NULL填充
select * from student left join score on student.student_id = score.student_id;

在这里插入图片描述

sql"># 过滤参加了考试的同学
select st.* 
from student st 
left join score sc 
on st.student_id = sc.student_id 
where sc.score is NULL;

在这里插入图片描述
• 查询没有学⽣的班级

sql"># 查询没有学生的班级
# 右连接以JOIN右边的表为基准，右表显示全部记录，左表中没有匹配的记录用NULL填充
select * 
fromstudent s
right join  class c
ons.class_id = c.class_id;

在这里插入图片描述

sql"># 过滤有学生的班级
selectc.*
from student s
right join class c
on s.class_id = c.class_id
where student_id is NULL;

在这里插入图片描述

⾃连接

应⽤场景

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

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

在这里插入图片描述

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

示例

• 显示所有“计算机原理”成绩比“java”成绩高的成绩信息

sql"># 显示所有“计算机原理”成绩比“java”成绩高的成绩信息
# 首先分两步进行，先查出计算机原理和java的课程Id，分别为1和3
select * from course where name = '计算机原理' or name = 'java';

在这里插入图片描述

sql"># 再查询成绩表中，计算机原理成绩比java成绩高的信息
select * 
from score s1,score s2
wheres1.student_id = s2.student_id
ands1.course_id = 3 and s2.course_id = 1
and s1.score > s2.score;

在这里插入图片描述

sql"># 结合在一起进行查询
select s.name,s1.*
fromscore s1,score s2,course c1,course c2,student s
where s1.student_id = s2.student_id
ands.student_id = s1.student_id
ands2.course_id = c2.course_id
and c1.name = '计算机原理' and c2.name = 'java'
and c1.course_id = s1.course_id
and s1.score > s2.score;

在这里插入图片描述

表连接练习

• 显示所有“计算机原理”成绩比“java”成绩高的学⽣信息和班级以及成绩信息

sql">select s.name 姓名,c.name 班级,s1.score 计算机原理成绩, s2.score java成绩
fromscore s1,score s2,course c1,course c2,student s,class c
where s1.student_id = s2.student_id
and s1.course_id = c1.course_id
ands2.course_id = c2.course_id
and c1.name = '计算机原理' and c2.name = 'java'
and s1.score > s2.score
ands.student_id = s1.student_id
and c.class_id = s.class_id;

在这里插入图片描述

⼦查询

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

语法

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

单⾏⼦查询

嵌套的查询中只返回⼀⾏数据
• 示例：查询与"不想毕业"同学的同班同学

sql"># 查询与"不想毕业"同学的同班同学 
select * from student where class_id = (select class_id from student where name = '不想毕业') and name != '不想毕业';

在这里插入图片描述

多⾏⼦查询

嵌套的查询中返回多⾏数据，使⽤[NOT] IN关键字
• 示例：查询语文或英文课程的成绩信息

sql"># 查询语文或英文课程的成绩信息 
select * from  score where course_id in (select course_id from course where name = '英文' or name = '语文');

在这里插入图片描述

sql"># 使用NOT IN 可以查询除了语文和英文课程的成绩信息
select * from  score where course_id not in (select course_id from course where name = '英文' or name = '语文');

在这里插入图片描述

多列⼦查询

单⾏⼦查询和多⾏⼦查询都只返回⼀列数据，多列⼦查询中可以返回多个列的数据，外层查询与嵌套
的内层查询的列要匹配
• 示例：查询重复录⼊的分数

sql"># 插入重复的分数：score, student_id, course_id列重复
insert into score(score_id,student_id,course_id,score) values (21,1,1,70.5),(22,1,1,98.5),(23,2,1,60);

sql"># 子查询中返回多个列
select score_id,student_id,course_id,score 
from score 
where (student_id,course_id,score) 
in (select student_id,course_id,score from score group by student_id,course_id,score having count(*) > 1);

在这里插入图片描述

在from⼦句中使⽤⼦查询

当⼀个查询产⽣结果时，MySQL⾃动创建⼀个临时表，然后把结果集放在这个临时表中，最终返回
给⽤户，在from⼦句中也可以使⽤临时表进⾏⼦查询或表连接操作
• 示例：查询所有比“中文系2019级3班”平均分高的成绩信息

sql"># 查询所有比“中文系2019级3班”平均分高的成绩信息
# 首先分步进行，第一步先查出中文系2019级3班的平均分
select AVG(sc.score) 
from class c, score sc, student s 
where c.class_id = s.class_id 
and sc.student_id = s.student_id 
and c.name = '中文系2019级3班'

在这里插入图片描述

sql">select sc.* 
from score sc,(select AVG(sc.score) scorefrom class c, score sc, student s where c.class_id = s.class_id and sc.student_id = s.student_id and c.name = '中文系2019级3班' ) tmp
where sc.score > tmp.score;

在这里插入图片描述
tmp 是临时表的别名

合并查询

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

创建新表并初始化数据

sql">create table student1 like student;insert into student1(sn,name,mail,class_id) values('09982','李鬼','xuanfeng@qq.com',1),('00001','测试用户',null,1),('00391','白素贞',null,1),('00031','许仙','xuxian@qq.com',1);

在这里插入图片描述

在这里插入图片描述

Union

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

sql">select * from student union select * from student1;

在这里插入图片描述

Union all

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

sql">select * from student union all select * from student1;

在这里插入图片描述

插⼊查询结果

语法

sql">INSERT INTO table_name [(column [, column ...])] SELECT ...

示例

• 将student表中‘菩提老祖’和‘不想毕业’的学⽣信息复制到student1表中

sql">insert into student1(sn,name,mail,class_id) (select s.sn,s.name,s.mail,s.class_id from student s where name = '菩提老祖' or name = '不想毕业');select * from student1;

在这里插入图片描述
在这里插入图片描述

关于数据库的联合查询先了解到这里，希望这篇文章对大家有帮助，谢谢大家的阅读！！！


http://www.ppmy.cn/devtools/137109.html

相关文章

更高效的Java 23开发，IntelliJ IDEA助力全面升级

更高效的Java 23开发，IntelliJ IDEA助力全面升级

IntelliJ IDEA，是java编程语言开发的集成环境。IntelliJ在业界被公认为最好的java开发工具，尤其在智能代码助手、代码自动提示、重构、JavaEE支持、各类版本工具(git、svn等)、JUnit、CVS整合、代码分析、 创新的GUI设计等方面的功能可以说是超常的。 随…
阅读更多...
uiautomator案例

uiautomator案例

test下新建类 public class ButtonClickTest {private UiDevice device;Beforepublic void setUp() {// 初始化 UiDevice 实例device UiDevice.getInstance(InstrumentationRegistry.getInstrumentation());try {device.executeShellCommand("am start -n com.yy.test/.…
阅读更多...
C++ 优先算法 —— 长度最小的子数组（滑动窗口）

C++ 优先算法 —— 长度最小的子数组（滑动窗口）

目录 题目：长度最小的子数组 1. 题目解析 2. 算法原理 Ⅰ. 暴力枚举 Ⅱ. 滑动窗口（同向双指针） 滑动窗口正确性 3. 代码实现 Ⅰ. 暴力枚举(会超时） Ⅱ. 滑动窗口（同向双指针） 题目：长…
阅读更多...
【GPT】睡觉时，大脑在做什么

【GPT】睡觉时，大脑在做什么

睡觉时，大脑并不是完全“关闭”的，而是处于高度活跃的状态，进行许多重要的功能。以下是大脑在不同睡眠阶段的主要活动： 1. 修复与恢复 神经元修复：大脑细胞会修复白天受到的损伤，同时清除代谢废物&#xf…
阅读更多...
【ArcGISPro】使用AI提取要素-土地分类（sentinel2）

【ArcGISPro】使用AI提取要素-土地分类（sentinel2）

Sentinel2数据处理 【ArcGISPro】Sentinel-2数据处理-CSDN博客 土地覆盖类型分类 处理结果
阅读更多...
51单片机快速入门之中断的应用 2024/11/23 串口中断

51单片机快速入门之中断的应用 2024/11/23 串口中断

51单片机快速入门之中断的应用 基本函数: void T0(void) interrupt 1 using 1 { 这里放入中断后需要做的操作 } void T0(void)： 这是一个函数声明，表明函数 T0 不接受任何参数，并且不返回任何值。 interrupt 1： 这是关键字和参…
阅读更多...
安全近期汇总

安全近期汇总

奇安信在 2024 年世界互联网大会相关展示与分析展望 一、奇安信展示产品 QAX - GPT 安全机器人 采用 Agent 思路，划分为四个核心专家角色： 全能型安全专家。网络威胁研判专家。终端威胁研判专家。主机威胁研判专家。四大拳头产品 AISOC、天眼、天擎和椒…
阅读更多...
软件工程——面向对象概述

软件工程——面向对象概述

（1）对象 在应用领域中有意义的、与所要解决的问题有关系的任何事物都可以作为对象，它既可以是具体的物理实体的抽象，也可以是人为的概念，或者是任何有明确边界和意义的东西。例如，一名职工、一家公司、一个…
阅读更多...
最新文章

Copyright @ 2022~2023