MySQL ——多表连接查询

野性酷女 2023-09-27 17:07 129阅读 0赞

## 一、（左、右和全）连接概念 ##

**内连接**： 假设A和B表进行连接，使用内连接的话，凡是A表和B表能够匹配上的记录查询出来。A和B两张表没有主付之分，两张表是平等的。

![6532d31fb28941a692f8d18402ac8891.png][]

关键字：inner join on  
语句：select \* from a\_table a inner join b\_table b on a.a\_id = b.b\_id;

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

**左连接（左外连接，**表示左边的这张表是主表**）：** 假设A和B表进行连接，使用外连接的话，A，B两张表中有一张主表，一张副表，主要查询主表中数据，捎带着查询副表。当副表中数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。外连接主要特点： 主表中的数据无条件全部查询出来。

![60814d4981aa46308986cf58726c95a3.png][]

关键字：left join on / left outer join on  
语句：select \* from a\_table a left join b\_table b on a.a\_id = b.b\_id;

说明：left join 是left outer join的简写，称是左[外连接][Link 1]，是外连接中的一种。  
左(外)连接，左表(a\_table)的记录将会全部表示出来，而右表(b\_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

**右连接（右外连接，**表示右边的这张表是主表**）**

![ebe21fce48f0431d8f29ad50fdfcecfc.png][]

关键字：right join on / right outer join on  
语句：select \* from a\_table a right outer join b\_table b on a.a\_id = b.b\_id;  
说明：right join是right outer join的简写，全称是右外连接，是外连接中的一种。  
与左(外)连接相反，右(外)连接，左表(a\_table)只会显示符合搜索条件记录，而右表(b\_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

**全连接（全外连接）**MySQL目前不支持此种方式，可以用其他方式替代解决。

![dc8eaaefd1c24eaf8e4e175c7d187594.png][]

全外连接：左表和右表都不做限制，所有记录都显示，两表不足地方用null 填充，也就是：  
左外连接=左表全部记录+相关联结果 ；右外连接=右表全部记录+相关联结果

**综上：**

*  内连接：只返回两个表中匹配的行，即两个表中连接字段相等的行。
 *  全连接：返回两个表中所有的行，无论是否有匹配的行。如果某个表中没有匹配的行，对应的结果集中该表的部分会使用NULL填充。

## 二、连表查询SQL实例 ##

**问题：根据下列的三张表，求出总分最高的学生。**

![d6b22423fe12420b8f3e3c3fd745ed44.png][]![d6a6007085914723ba5d79fd3e0994c2.png][]

![1c1994728672498da48444e0fccb1608.png][]

-- create
    CREATE TABLE course(
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
    );
    CREATE TABLE student (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
    );
    CREATE TABLE score(
    id INTEGER PRIMARY KEY,
    course_id INTEGER NOT NULL,
    student_id INTEGER NOT NULL,
    score INTEGER NOT NULL
    );
    
    INSERT INTO course VALUES(1, "语文"), (2, "数学"), (3, "外语");
    INSERT INTO student VALUES(1, "小张"), (2, "小王"), (3, "小马");
    INSERT INTO score VALUES(1, 1, 1, 80), (2, 2, 1, 90), (3, 3, 1, 70);
    INSERT INTO score VALUES(4, 1, 2, 70), (5, 2, 2, 90), (6, 3, 2, 80);
    INSERT INTO score VALUES(7, 1, 3, 80), (8, 2, 3, 60), (9, 3, 3, 70);
    
    SELECT *FROM course;
    SELECT *FROM student;
    SELECT *FROM score;

**当然这里面包含两种情况，到底是单科总分最高，还是所有科总分加起来最高，如果面试官没有讲清楚，还是先别急于回答，或者把这两个情况都分析一遍：**

**①、求所有科目总分最高的学生**

SELECT s.name, t.total_score 
    FROM student s 
    RIGHT JOIN 
        (SELECT student_id, SUM(score) AS total_score 
        FROM score 
        GROUP BY student_id 
        HAVING SUM(score) = (
            SELECT SUM(score) 
            FROM score 
            GROUP BY student_id 
            ORDER BY SUM(score) DESC 
            LIMIT 1)
         ) t ON s.id = t.student_id;

![3236017694e44ec1a82308ca59bc16b4.png][]

具体SQL的解释如下：

这个查询的目的是找到总成绩排名第一的学生，并返回该学生的姓名和总成绩。

1.  子查询 `t`：首先，我们执行一个子查询来计算每个学生的总成绩。
    
    ![dca18ba126e440a5a3dad22778e7dfe9.png][]
    
    子查询从 `score` 表中获取每个学生的学生 ID (`student_id`) 和对应的成绩总和 (`SUM(score) AS total_score`)。使用 `GROUP BY student_id` 对成绩进行分组，以便计算每个学生的总成绩。然后，通过 `HAVING SUM(score) = (SELECT SUM(score) FROM score GROUP BY student_id ORDER BY SUM(score) DESC LIMIT 1)` 这一行筛选出总成绩最高的学生，确保只选择总成绩等于所有学生中最高总成绩的学生。
2.  主查询：在主查询中，我们使用 `RIGHT JOIN` 将学生表 (`student`) 和子查询 `t` 关联起来。这样，我们可以获取到总成绩最高的学生以及他们的总成绩。通过 `ON s.id = t.student_id` 来建立关联条件，确保学生 ID 匹配。
3.  结果过滤：在最终结果中，我们选择了学生的姓名 (`s.name`) 和对应的总成绩 (`t.total_score`)。

**②、求单科科目总分最高的学生**

SELECT s.name, c.name , s2.max_score FROM score s1
    RIGHT JOIN 
        (SELECT MAX(score) max_score, course_id 
        FROM score 
        GROUP BY course_id) s2
    ON s1.course_id = s2.course_id AND s1.score = s2.max_score
    LEFT JOIN course c ON c.id = s1.course_id
    LEFT JOIN student s ON s1.student_id = s.id

![586e7cbc5df749e990b12204e2cd4ca9.png][]

具体SQL的解释如下：

*  `SELECT s.name, c.name AS course_name, s2.max_score`: 这部分定义了要选择的列。`s.name` 表示学生的姓名，`c.name AS course_name` 表示课程的名称（使用别名 `course_name`），`s2.max_score` 表示最高分数。
 *  `FROM score s1`: 这表示从 `score` 表中查询数据，并为其创建别名 `s1`。
 *  `(SELECT MAX(score) max_score, course_id FROM score GROUP BY course_id) s2`: 这是一个子查询，它计算每门课程的最高分，并将结果存储在 `s2` 中。它选择了每个课程的最高分数（使用别名 `max_score`）和课程ID。

![ae441b02c8c04b3a9dd88565677f4945.png][]

*  `RIGHT JOIN`: 这是一个右连接，将 `s1` 和 `s2` 进行连接。它基于课程ID和最高分数匹配。
 *  `ON s1.course_id = s2.course_id AND s1.score = s2.max_score`: 这是连接条件，用于将 `s1` 和 `s2` 进行连接，使得课程ID和最高分数相匹配。
 *  `JOIN student s ON s1.student_id = s.id`: 这是一个内连接，将 `s1` 和 `student` 表连接起来。它基于学生ID匹配 （可以展示出 学生的姓名）。
 *  `LEFT JOIN course c ON c.id = s1.course_id`: 这是一个左连接，将 `s1` 和 `course` 表连接起来。它基于课程ID匹配（可以展示出 课程的名称）。
 *  最后的查询结果将包含学生的姓名、课程名称和最高分。

如果将原来的 `LEFT JOIN`连接操作更改为 `RIGHT JOIN`，则结果中将显示右表（即子查询 `s2`）的所有记录，而左表（即 `score s1`）在右表中没有匹配的记录将被包含为 NULL 值。具体来说，右连接（`RIGHT JOIN`）会返回右表中满足连接条件的记录，并且左表中不满足连接条件或没有匹配的记录将被包含为 NULL 值。

在这种情况下，由于 `s2` 是一个子查询，它计算了每门课程的最高分，并且只包含具有最高分的信息，所以使用 `RIGHT JOIN` 可能不会得到预期的结果。因为右表中的记录数量较少，而左表中的记录数量较多。

如果我们想要获取所有的学生姓名和对应课程的最高分，并将它们与课程名称进行匹配，那么使用 `LEFT JOIN` 是更常见和合适的选择。

[6532d31fb28941a692f8d18402ac8891.png]: https://img-blog.csdnimg.cn/6532d31fb28941a692f8d18402ac8891.png
[60814d4981aa46308986cf58726c95a3.png]: https://img-blog.csdnimg.cn/60814d4981aa46308986cf58726c95a3.png
[Link 1]: https://so.csdn.net/so/search?q=%E5%A4%96%E8%BF%9E%E6%8E%A5&spm=1001.2101.3001.7020
[ebe21fce48f0431d8f29ad50fdfcecfc.png]: https://img-blog.csdnimg.cn/ebe21fce48f0431d8f29ad50fdfcecfc.png
[dc8eaaefd1c24eaf8e4e175c7d187594.png]: https://img-blog.csdnimg.cn/dc8eaaefd1c24eaf8e4e175c7d187594.png
[d6b22423fe12420b8f3e3c3fd745ed44.png]: https://img-blog.csdnimg.cn/d6b22423fe12420b8f3e3c3fd745ed44.png
[d6a6007085914723ba5d79fd3e0994c2.png]: https://img-blog.csdnimg.cn/d6a6007085914723ba5d79fd3e0994c2.png
[1c1994728672498da48444e0fccb1608.png]: https://img-blog.csdnimg.cn/1c1994728672498da48444e0fccb1608.png
[3236017694e44ec1a82308ca59bc16b4.png]: https://img-blog.csdnimg.cn/3236017694e44ec1a82308ca59bc16b4.png
[dca18ba126e440a5a3dad22778e7dfe9.png]: https://img-blog.csdnimg.cn/dca18ba126e440a5a3dad22778e7dfe9.png
[586e7cbc5df749e990b12204e2cd4ca9.png]: https://img-blog.csdnimg.cn/586e7cbc5df749e990b12204e2cd4ca9.png
[ae441b02c8c04b3a9dd88565677f4945.png]: https://img-blog.csdnimg.cn/ae441b02c8c04b3a9dd88565677f4945.png