前言

书接上回写给前端的MySQL数据库入门（一），基于关系型数据库的优势，日常开发中，常见的是多个表建立关系进行关联操作（更删改查），本文介绍MYSQL多表连接查询。

子查询

先记住一句话：子查询就是将多个查询动作合并为一个。

主要有四种子查询方案：标量子查询，列子查询，行子查询和表子查询。

这些概念对于我们不熟悉数据库概念的前端来说也太晦涩难懂了吧，所以不妨用我们能理解的方式给他们分别取个别名。

这里我们定义从数据库中查出来的一行数据为一条记录，这条记录上的一个数据为一个字段，那么以下面这种方式就很好理解了：

数据库 前端
标量子查询 单条记录单个字段子查询
列子查询 多条记录单个字段子查询
行子查询 单条记录多个字段子查询
表子查询 多条记录多个字段子查询

数据准备

先新建两张表进行查询，这次我们以学生选课为例建立一个多对多表案例，顺便复习一下上一篇建表和建立多对多关系的知识。

  • 建立学生表
    1
    2
    3
    4
    5
    6
    CREATE TABLE `student` (
     id INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(4) NOT NULL,
     number DOUBLE INT NOT NULL UNIQUE,
     age INT
    )
  • 建立课程表
    1
    2
    3
    4
    5
    CREATE TABLE `course`(
     id INT PRIMARY KEY AUTO_INCREMNT,
     name VARCHAR(20) NOT NULL,
     price DOUBLE
    )
  • 建立（多对多）关系表
    1
    2
    3
    4
    5
    6
    7
    CREATE TABLE `student_select_course`(
      id INT PRIMARY KEY AUTO_INCREMENT,
      student_id INT NOT NULL,
      course_id INT NOT NULL,
      FOREIGN KEY (student_id) REFERENCES students(id) ON UPDATE CASCADE,
      FOREIGN KEY (course_id) REFERENCES courses(id) ON UPDATE CASCADE
    );

插入数据

1
2
3
4
5
# 学生表插入数据
INSERT INTO `student` (name, number, age) VALUES ('李华', 20210001, 20), ('张三', 20210002, 23), ('王二炮', 20210003, 40), ('胖虎', 20210004, NULL);

# 课程表插入数据
INSERT INTO `course` (name, price) VALUES ('语文', 888), ('数学', 999), ('法律', 10000), ('历史')

目前数据情况：

  • students表：
  • course表
  • 建立多对多关联
    1
    2
    3
    4
    5
    # 李华选修了语文和数学
    # 王二炮选修了语文，法律和历史
    # 胖虎选修了法律
    # 张三没有选择任何一门课
    INSERT INTO student_select_course (student_id, course_id) VALUES (1, 1), (1, 2), (3,1), (3,3), (3,4), (4, 3);

子查询演示

  • 需求：查询名叫“王二炮”的同学选修了几门课
    • 先在学生表中查询到王二炮同学的id
    • 再用该id在关联表中查询到所选修课程的id集合
    • 再在课程表中查出课程id在课程id集合中的所有课程
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      # 查询王二炮同学的学生id -> 3
      SELECT id FROM student WHERE name = '王二炮'
      # 根据 id:3 在关联表中查询所有关联课程id集合 -> (1,3,4)
      SELECT course_id FROM student_select_course WHERE student_id = 3;
      # 再从课程表中查询课程id集合[1,3,4]中包含的课程 -> (语文，法律，历史)
      SELECT name FROM course WHERE id IN (1,3,4);

      # 通过子查询可以一行语句查询完毕 -> (语文，法律，历史)
      # 以下语句分别包含了标量子查询（一条记录的一个字段：姓名 王二炮 同学的student.id）；列子查询（多条记录的一个字段：student.id 同学的所有选修课id）
      SELECT name FROM course WHERE id IN (SELECT course_id FROM student_select_course WHERE student_id = (SELECT id FROM student WHERE name = '王二炮'));

思考

  • 子查询面对一些简单的临时场景确实挺好用的

  • 但是如果需求升级：查询结果中不仅返回“王二炮”同学选修课程的名称，还要加上课程id，还要加上学号，还要加上姓名

    • 这种场景下子查询就无能为力了
    • 又不想多次查询拼接，有没有一种方式可以像子查询一样一次查出来？

  • 接下来介绍的是业务中高频使用的连接查询

连接查询

一个简单的案例

先说答案，这种需求很明显是从多个表中获取关联数据，那么连接查询就能一次查出来

1
2
3
4
5
SELECT stu.id studentId, stu.name studentName, stu.number studentNumber, cs.id courseId, cs.name courseName, cs.price coursePrice 
FROM student stu 
JOIN student_select_course ssc ON stu.id = ssc.student_id
JOIN course cs ON ssc.course_id = cs.id 
WHERE stu.name = "王二炮";


不要一看到这么长的查询语句就发懵，其实连接查询很简单；

连接查询种类

连接查询分为四种，分别为左连接，右连接，内连接，全连接

  • 一张图看懂各种连接区别

1.左连接LEFT JOIN

  • 记住一句话，左连接以左表为主
    • 意思是条件之下，即使左表某一条数据对应右表的值为空，该数据也会被查出来
    • 这是在工作场景下用得最多的一种连接
      以上面的情景来举例，观察普通连接和左连接的不同，需求：查询所有学生的选课情况
  • 如果不是左连接
    由于张三没有选课，只查到了6条记录

    那显示不符合我们的要求，我们要看的是每一位同学的选课情况，即使这位同学没有选课（以同学表student为主）
  • 改成左连接
  • 可以看到现在查到了7条记录，学生表中的张三即使在关联表中没有记录也会被查出来

2.右连接

  • 顾名思义：右连接就是以右表（被驱动表）为主
    • 意思就是，右表中的某一条数据即使在左表中没有记录与之关联，也会被查出来
    • 工作中用得并不多
      需求：微调上面的查询语句查询所有课程的学生选课情况
  • 上面查询语句以student表为驱动表，现在要以course表为主，那么就需要RIGHT JOIN右连接
    开始之前先新增一门没人选的课程
    1
    INSERT INTO course (name) VALUES ('艺术品鉴')
    如果仍然是左连接会发现依然是7条记录没有艺术品鉴这门课的选课信息

    改成右连接会发现艺术品鉴这门课被查出来了，即使没有同学选这门课

3.内连接

  • 事实上内连接是表示左边的表和右边的表都有对应的数据关联
    • 内连接就是上面例子中的默认JOIN
    • 内连接不以任何表为主，只要没有在两个表中建立关系的记录都不会被查出来
    • 工作中偶尔会用到

4.全连接

SQL规范中全连接是使用FULL JOIN，但是MySQL中并没有对它的支持，我们需要使用 UNION 来实现

  • 全连接照顾到两个表中的所有记录，即使没有建立关系的记录也都会被查询到
    1
    2
    3
    4
    5
    6
    7
    8
    9
    (SELECT stu.id studentId, stu.name studentName, stu.number studentNumber, cs.id courseId, cs.name courseName, cs.price coursePrice 
    FROM student stu 
    LEFT JOIN student_select_course ssc ON stu.id = ssc.student_id
    LEFT JOIN course cs ON ssc.course_id = cs.id) 
    UNION 
    (SELECT stu.id studentId, stu.name studentName, stu.number studentNumber, cs.id courseId, cs.name courseName, cs.price coursePrice 
    FROM student stu 
    RIGHT JOIN student_select_course ssc ON stu.id = ssc.student_id
    RIGHT JOIN course cs ON ssc.course_id = cs.id)

实践

  1. 查询没有选择任何课程的学生
    1
    2
    3
    4
    5
    SELECT stu.id studentId, stu.name studentName 
    FROM student 
    LEFT JOIN student_select_course ssc ON stu.id = ssc.student_id 
    LEFT JOIN course cs ON ssc.course_id = cs.id 
    WHERE cs.id IS NULL
  • 以学生表为主，如果以学生表为驱动表就是左连接
  1. 查询哪些课程没人选
    1
    2
    3
    4
    5
    SELECT cs.id courseId, cs.name courseName 
    FROM student stu
    LEFT JOIN student_select_course ssc ON stu.id = ssc.student_id 
    LEFT JOIN course cs ON ssc.course_id = cs.id 
    WHERE stu.id IS NULL
  • 如果仍然学生表为驱动表，则需要使用RIGHT JOIN，正常工作场景中此时会将course表作为驱动表
    1
    2
    3
    4
    5
    SELECT cs.id courseId, cs.name courseName 
    FROM student stu
    RIGHT JOIN student_select_course ssc ON stu.id = ssc.student_id 
    RIGHT JOIN course cs ON ssc.course_id = cs.id 
    WHERE stu.id IS NULL

欢迎交流指正~