【MYSQL】MYSQL 的学习教程（二）之 MYSQL 的七种连接

阳光穿透心脏的1/2处 2024-02-05 12:17 41阅读 0赞

![在这里插入图片描述][4604384c4ba247e39e2e84089399e72a.png]

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

![在这里插入图片描述][89d1f1acb44f4cdda5aefcebeca4a915.png]![在这里插入图片描述][dd76f8621dd642d994c01af953436d63.png]

## 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 个学生都和 教师表 中的 所有教师 都匹配一次

![在这里插入图片描述][863ffeb417d64f0ab0edc2e8c772d08b.png]

**问题：**

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

**解决方案：**

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

案例：

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

对应的 SQL 语句如下：

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

结果如下：

![在这里插入图片描述][1d538ef61f63499db2efaccd97fb9821.png]

## 3. 内连接：`INNER JOIN`（INNER 可以省略） ##

### 3.1 等值连接 ###

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

案例：

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

对应的 SQL 语句如下：

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

##### 等值连接与自然连接的区别 #####

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

![在这里插入图片描述][b0af27b0481d4ab7a1b9802f3ef88878.png]

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

![在这里插入图片描述][a6acd114aaef4301b156ea58bf655c32.png]

上面的等值连接进行比较的都是 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 代替

![在这里插入图片描述][c5c925fca4c94442a7760ed4fcf64dc8.png]

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 代替  
![在这里插入图片描述][ca0da203c3e649fe80f11b65b34e595a.png]

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

②：右外特殊情况：

![在这里插入图片描述][ca469763c8b94f85ace19119a0aa939a.png]

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 组合并去重实现

全外接查询：就是 左表独有的数据 加上 右表独有的数据  
![在这里插入图片描述][9e29bcaa765c4f7db91a00d50b8b2de4.png]

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 全连接 ###

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

![在这里插入图片描述][bde0ca62cea14a59b12a2a56b3661aac.png]

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中的七种常用查询连接详解][Mysql]

[4604384c4ba247e39e2e84089399e72a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/4ba4c594dbef415ca7f64fbae88fe1f1.png
[89d1f1acb44f4cdda5aefcebeca4a915.png]: https://img-blog.csdnimg.cn/direct/89d1f1acb44f4cdda5aefcebeca4a915.png
[dd76f8621dd642d994c01af953436d63.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/0d08c360f58d46739e5e62ecc714893f.png
[863ffeb417d64f0ab0edc2e8c772d08b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/5559323e35bd421da4be5bc851002ed9.png
[1d538ef61f63499db2efaccd97fb9821.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/8e1ca150379c4caeb286101d94ca2580.png
[b0af27b0481d4ab7a1b9802f3ef88878.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/995769138d064a25bfda413dd57f5ba8.png
[a6acd114aaef4301b156ea58bf655c32.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/a0ae823f73314ad3bfa2ca19183fb417.png
[c5c925fca4c94442a7760ed4fcf64dc8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/df0a4712124d4d66a62aca7d55e82816.png
[ca0da203c3e649fe80f11b65b34e595a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/d2dc6f2b85ce441782db7328fe564583.png
[ca469763c8b94f85ace19119a0aa939a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/aca61e4478174604b6b9dd44ac56de5f.png
[9e29bcaa765c4f7db91a00d50b8b2de4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/1bf60895fcc34b5fab20e0e2a43800fa.png
[bde0ca62cea14a59b12a2a56b3661aac.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/05/c5ea53dec55145fdb6dd99999403d73f.png
[Mysql]: https://blog.csdn.net/qq_41946216/article/details/124338044?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_baidulandingword~default-1-124338044-blog-131179083.235%5Ev38%5Epc_relevant_anti_t3_base&spm=1001.2101.3001.4242.2&utm_relevant_index=4