【MYSQL】MYSQL 的学习教程(二)之 MYSQL 的七种连接

2023-12-13 03:54:59

在这里插入图片描述

1. 概述

在实际开发中,大部分的情况下都不是从单表中查询数据,一般都是多张表联合查询取出最终的结果。一个业务都会对应多张表,比如:学生和班级,起码两张表

①:连接分类(功能划分):

  1. 交叉连接:笛卡尔积
  2. 内连接:等值连接、非等值连接、自连接
  3. 外连接:左(外)连接、左(外)连接特殊情况、右(外)连接、右(外)连接特殊情况、全连接、全外连接

②:连接分类(年代划分):

  • SQL 92 年语法(仅仅支持内连接)
SELECT *
FROM TEST1, TEST2
WHERE TEST1.SId = TEST2.sId
  • SQL 99 年语法 (推荐使用)
SELECT *
FROM TAB_TEST1 test1
INNER JOIN TAB_TEST2 test2
ON test1.sId = test2.sId

③:内连接和外连接的区别:

假设 A 和 B 表进行连接

  • 内连接:凡是 A 表和 B 表能够匹配上的记录,都查询出来。A、B 两张表没有主副之分,两张表是平等的
  • 外连接:A、B 两张表中有一张表是主表,一张表是副表,主要查询主表中的数据,捎带着查询副表。当副表中的数据没有和主表中的数据匹配上,副表自动模拟出 NULL 与之匹配

测试数据

# 构建表
CREATE TABLE `student`  (
  `id` int NOT NULL,
  `name` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `age` int NOT NULL,
  `teacher_id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `teacher`  (
  `id` int NOT NULL,
  `name` char(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `age` int NOT NULL,
  `class_id` int NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

# 插入数据
INSERT INTO `student` VALUES (1, '刘峰', 20, 1);
INSERT INTO `student` VALUES (2, '李福', 22, 2);
INSERT INTO `student` VALUES (3, '王紫', 21, 3);
INSERT INTO `student` VALUES (4, '赵兰', 24, 3);

INSERT INTO `teacher` VALUES (1, '夏晴', 36, 1);
INSERT INTO `teacher` VALUES (2, '李淳', 32, 2);
INSERT INTO `teacher` VALUES (3, '张叶', 34, 3);

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

2. 交叉连接(笛卡尔积):CROSS JOIN

假设集合 A = {a,b},集合 B = {0,1,2},则两个集合的笛卡尔积为 {(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。如果 A 表示某学校学生的集合,B 表示该学校所有教师的集合,则 A 与 B 的笛卡尔积表示学生选择老师所有可能的情况

笛卡尔积特点:它不使用任何匹配或者选取条件,而是直接将一个数据源中的每个行与另一个数据源的每个行 一 一 匹配

案例:

查询学生对应的老师

对应的 SQL 语句如下:

SELECT * FROM student CROSS JOIN teacher;

或者:

SELECT * FROM student, teacher;

结果如下:学生表 中数据每 1 个学生都和 教师表 中的 所有教师 都匹配一次

在这里插入图片描述

问题:

当两张表进行连接查询的时候,没有任何条件进行限制,最终的查询结果条数是两张表记录条数的乘积。这就是笛卡尔积现象。 查询出来的结果是两张表的记录的乘积 4 * 3 = 12,许多数据是无效数据。如何避免笛卡尔积现象?

解决方案:

增加条件进行过滤,但只会显示有效记录

案例:

根据教师 id 查询学生对应的选课老师

对应的 SQL 语句如下:

SELECT st.*,th.* FROM student st ,teacher th WHERE st.teacher_id = th.id

结果如下:

在这里插入图片描述

3. 内连接:INNER JOIN(INNER 可以省略)

3.1 等值连接

等值连接:条件是等量关系

案例:

根据教师 id 查询学生对应的选课老师

对应的 SQL 语句如下:

SELECT st.*,th.* FROM student st JOIN teacher th ON st.teacher_id = th.id;
等值连接与自然连接的区别

等值连接:当条件为“=”的连接为等值连接,是连接属性值相等的那些元组

在这里插入图片描述

自然连接:自然连接不仅要求连接的两个字段必须同名,还要求将结果中重复的属性列去掉

在这里插入图片描述

上面的等值连接进行比较的都是 B 属性, 但还可以进行 R.B = S.E 等值连接, 而自然连接只能是同名属性组

3.2 非等值连接

非等值连接:条件不是等量关系

案例:

查询教师 id 在 1-2 之间所教的学生和老师信息

对应的 SQL 语句如下:

SELECT st.*,th.* FROM student st JOIN teacher th ON st.teacher_id = th.id AND th.id BETWEEN 1 AND 2;

3.3 自连接

自连接:就是一张表看做两张表,自己连接自己

案例:

查询学生 id 和教师 id 相同的学生

对应的 SQL 语句如下:

 SELECT s.*,st.teacher_id FROM student s, student st WHERE s.id = st.teacher_id;

4. 外连接:OUTER JOIN(OUTER 可以省略)

4.1 左外连接:LEFT JOIN

①:左外连接:左边的是主表,左表数据全部显示,右表显示符合 ON 后的条件的数据,不符合的用 NULL 代替

在这里插入图片描述

SELECT * FROM student st LEFT JOIN teacher th ON st.teacher_id = th.id;

②:左外特殊情况:返回没有匹配的记录

案例:

查询没有教师的学生信息

对应的 SQL 语句如下:

SELECT * FROM student st LEFT JOIN teacher th ON st.teacher_id = th.id WHERE th.id IS NULL;

4.2 右外连接:RIGHT JOIN

①:右外连接:右边的是主表,右表数据全部显示,左表显示符合 ON 后的条件的数据,不符合的用 NULL 代替
在这里插入图片描述

SELECT * FROM student st RIGHT JOIN teacher th ON st.teacher_id = th.id;

②:右外特殊情况:

在这里插入图片描述

SELECT * FROM student st RIGHT JOIN teacher th ON st.teacher_id = th.id WHERE st.teacher_id IS NULL;

4.3 全外连接: FULL OUT JOIN(OUT 可以省略)

全外连接:Mysql 不支持,可以使用 union 组合并去重实现

全外接查询:就是 左表独有的数据 加上 右表独有的数据
在这里插入图片描述

SELECT * FROM student st LEFT JOIN teacher th ON st.teacher_id = th.id WHERE th.id IS NULL
UNION
SELECT * FROM student st RIGHT JOIN teacher th ON st.teacher_id = th.id WHERE st.teacher_id IS NULL

4.4 全连接

全连接查询的是 左表所有的数据 加上 右表所有的数据 并去重。

在这里插入图片描述

SELECT * FROM student st LEFT JOIN teacher th ON st.teacher_id = th.id
UNION
SELECT * FROM student st RIGHT JOIN teacher th ON st.teacher_id = th.id

Mysql中的七种常用查询连接详解

文章来源:https://blog.csdn.net/sco5282/article/details/134810848
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。