如何在MySQL中进行关联表查询？

MySQL 关联表查询

在数据库中，数据通常被组织成表格的形式，为了高效地存储和检索数据，这些表格之间常常存在某种关联关系，这种关联关系可以是一对一、一对多或多对多，本文将详细探讨如何在 MySQL 中进行关联表查询，并介绍一些常见的操作示例。

一、什么是关联表？

关联表指的是两个或多个表之间通过某种方式相互关联，最常见的关联方式是通过外键实现。

一对一关联：每个表中的每一行都与另一个表中的一行相关联。

一对多关联：一个表中的一行可以与另一个表中的多行相关联。

多对多关联：两个表中的多行可以互相关联。

二、创建示例数据库

我们创建一个示例数据库school，其中包含三个表：students,courses, 和enrollments。

CREATE DATABASE school;
USE school;
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50)
);
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    title VARCHAR(100)
);
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

三、插入示例数据

插入一些示例数据以便后续查询演示。

INSERT INTO students (student_id, name) VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO courses (course_id, title) VALUES (101, 'Math'), (102, 'Science'), (103, 'History');
INSERT INTO enrollments (student_id, course_id) VALUES
(1, 101), (1, 102), (2, 101), (3, 103), (3, 101);

四、关联表查询示例

4.1 简单内连接（INNER JOIN）

内连接返回同时出现在两个表中的记录，以下查询将返回所有学生及其选修的课程。

SELECT students.name, courses.title
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.course_id;

结果：

4.2 左连接（LEFT JOIN）

左连接返回左表中的所有记录以及右表中匹配的记录，如果右表中没有匹配项，则结果为 NULL，以下查询将返回所有学生及其选修的课程，即使某些学生没有选课。

SELECT students.name, courses.title
FROM students
LEFT JOIN enrollments ON students.student_id = enrollments.student_id
LEFT JOIN courses ON enrollments.course_id = courses.course_id;

结果：

4.3 右连接（RIGHT JOIN）

右连接返回右表中的所有记录以及左表中匹配的记录，如果左表中没有匹配项，则结果为 NULL，以下查询将返回所有课程及其选修的学生，即使某些课程没有被选修。

SELECT students.name, courses.title
FROM students
RIGHT JOIN enrollments ON students.student_id = enrollments.student_id
RIGHT JOIN courses ON enrollments.course_id = courses.course_id;

结果：

4.4 全连接（FULL JOIN）

全连接返回两个表中的所有记录，以下查询将返回所有学生和所有课程，即使某些学生没有选课或某些课程没有被选修。

SELECT students.name, courses.title
FROM students
FULL OUTER JOIN enrollments ON students.student_id = enrollments.student_id
FULL OUTER JOIN courses ON enrollments.course_id = courses.course_id;

结果：

五、复杂查询示例

5.1 使用子查询

子查询是一种嵌套在其他 SQL 语句中的查询，以下查询将返回选修了“Math”课程的所有学生的名字。

SELECT name FROM students
WHERE student_id IN (SELECT student_id FROM enrollments WHERE course_id = 101);

结果：

5.2 使用聚合函数和分组

以下查询将返回每门课程的选修人数。

SELECT courses.title, COUNT(enrollments.student_id) AS student_count
FROM courses
LEFT JOIN enrollments ON courses.course_id = enrollments.course_id
GROUP BY courses.course_id;

结果：

六、相关问题与解答

问题1：如何在查询结果中显示没有选修任何课程的学生？

解答：

可以使用左连接来实现这一点，以下查询将返回所有没有选修任何课程的学生。

SELECT students.name
FROM students
LEFT JOIN enrollments ON students.student_id = enrollments.student_id
WHERE enrollments.course_id IS NULL;

结果：

问题2：如何查找选修了所有课程的学生？

解答：

要查找选修了所有课程的学生，可以使用子查询和聚合函数，以下查询将返回选修了所有课程的学生名字。

SELECT students.name
FROM students
WHERE NOT EXISTS (
    SELECT courses.course_id
    FROM courses
    WHERE courses.course_id NOT IN (SELECT enrollments.course_id FROM enrollments WHERE enrollments.student_id = students.student_id)
);

结果：