• MySQL ——多表连接查询


    一、(左、右和全)连接概念

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


    关键字: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与之匹配。外连接主要特点: 主表中的数据无条件全部查询出来。

    关键字: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的简写,称是左外连接,是外连接中的一种。
    左(外)连接,左表(a_table)的记录将会全部表示出来,而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

    右连接(右外连接,表示右边的这张表是主表


    关键字: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目前不支持此种方式,可以用其他方式替代解决。


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

    综上:

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

    二、连表查询SQL实例

    问题:根据下列的三张表,求出总分最高的学生。

    1. -- create
    2. CREATE TABLE course(
    3. id INTEGER PRIMARY KEY,
    4. name TEXT NOT NULL
    5. );
    6. CREATE TABLE student (
    7. id INTEGER PRIMARY KEY,
    8. name TEXT NOT NULL
    9. );
    10. CREATE TABLE score(
    11. id INTEGER PRIMARY KEY,
    12. course_id INTEGER NOT NULL,
    13. student_id INTEGER NOT NULL,
    14. score INTEGER NOT NULL
    15. );
    16. INSERT INTO course VALUES(1, "语文"), (2, "数学"), (3, "外语");
    17. INSERT INTO student VALUES(1, "小张"), (2, "小王"), (3, "小马");
    18. INSERT INTO score VALUES(1, 1, 1, 80), (2, 2, 1, 90), (3, 3, 1, 70);
    19. INSERT INTO score VALUES(4, 1, 2, 70), (5, 2, 2, 90), (6, 3, 2, 80);
    20. INSERT INTO score VALUES(7, 1, 3, 80), (8, 2, 3, 60), (9, 3, 3, 70);
    21. SELECT *FROM course;
    22. SELECT *FROM student;
    23. SELECT *FROM score;

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

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

    1. SELECT s.name, t.total_score
    2. FROM student s
    3. RIGHT JOIN
    4. (SELECT student_id, SUM(score) AS total_score
    5. FROM score
    6. GROUP BY student_id
    7. HAVING SUM(score) = (
    8. SELECT SUM(score)
    9. FROM score
    10. GROUP BY student_id
    11. ORDER BY SUM(score) DESC
    12. LIMIT 1)
    13. ) t ON s.id = t.student_id;

    具体SQL的解释如下:

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

    1. 子查询 t:首先,我们执行一个子查询来计算每个学生的总成绩。

      子查询从 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)。

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

    1. SELECT s.name, c.name , s2.max_score FROM score s1
    2. RIGHT JOIN
    3. (SELECT MAX(score) max_score, course_id
    4. FROM score
    5. GROUP BY course_id) s2
    6. ON s1.course_id = s2.course_id AND s1.score = s2.max_score
    7. LEFT JOIN course c ON c.id = s1.course_id
    8. LEFT JOIN student s ON s1.student_id = s.id

    具体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。

    • 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 是更常见和合适的选择。

  • 相关阅读:
    Flask 学习-63.Session 使用
    Elasticsearch:崭新的打分机制 - Learning To Rank (LTR)
    R语言mgcv包广义可加模型对分类曲线进行拟合
    token_to_image
    Mysql 基础命令增删改查
    【TcaplusDB知识库】Tmonitor后台一键安装介绍(二)
    【React】第十部分 redux
    Autocad VBA中的ModelSpace、PaperSpace和Layout
    教师节限定!10场数据科学校内赛与10场数据科学数据科学教学实训工作坊,充实实践教学
    这些在ISIS的代码是多少呀
  • 原文地址:https://blog.csdn.net/weixin_49171365/article/details/129913263