mysql 多对多查询|极客笔记

mysql 多对多查询

在关系数据库中，多对多关系指的是两个实体之间存在多对多的关系。在实际应用中，多对多关系是非常常见的，比如一个学生可以选择多门课程，而一门课程也可以被多个学生选修。在这种情况下，我们需要使用中间表来实现多对多的关系。本文将详细介绍在MySQL数据库中如何进行多对多查询。

创建示例数据库

在开始之前，我们需要先创建一个示例数据库，包含学生表、课程表和中间表来模拟多对多关系。我们可以按照以下步骤创建示例数据库：

CREATE DATABASE IF NOT EXISTS university;

USE university;

CREATE TABLE IF NOT EXISTS students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS courses (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

INSERT INTO students (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie');

INSERT INTO courses (id, name) VALUES
(1, 'Math'),
(2, 'Science'),
(3, 'History');

INSERT INTO student_courses (student_id, course_id) VALUES
(1, 1),
(1, 2),
(2, 2),
(3, 1),
(3, 3);

上面的SQL语句创建了一个名为university的数据库，并在其中创建了三张表：students（学生表）、courses（课程表）和student_courses（中间表）。同时插入了一些示例数据。

多对多查询

查询学生选修的课程

要查询学生选修的课程，我们可以使用JOIN语句将students、student_courses和courses三张表连接起来。以下是一个查询学生Alice选修的所有课程的SQL语句：

SELECT students.name, courses.name
FROM students
JOIN student_courses ON students.id = student_courses.student_id
JOIN courses ON student_courses.course_id = courses.id
WHERE students.name = 'Alice';

运行以上SQL语句，将得到以下查询结果：

+-------+---------+
| name  | name    |
+-------+---------+
| Alice | Math    |
| Alice | Science |
+-------+---------+

查询课程被哪些学生选修

要查询课程被哪些学生选修，我们可以使用相同的JOIN语句。以下是一个查询Math课程被选修的所有学生的SQL语句：

SELECT students.name, courses.name
FROM students
JOIN student_courses ON students.id = student_courses.student_id
JOIN courses ON student_courses.course_id = courses.id
WHERE courses.name = 'Math';

运行以上SQL语句，将得到以下查询结果：

+--------+------+
| name   | name |
+--------+------+
| Alice  | Math |
| Charlie| Math |
+--------+------+

查询选修相同课程的学生

要查询选修相同课程的学生，我们可以使用GROUP BY和HAVING子句。以下是一个查询选修相同课程的学生的SQL语句：

SELECT students.name, GROUP_CONCAT(courses.name SEPARATOR ', ') AS courses
FROM students
JOIN student_courses ON students.id = student_courses.student_id
JOIN courses ON student_courses.course_id = courses.id
GROUP BY students.id
HAVING COUNT(*) > 1;

运行以上SQL语句，将得到以下查询结果：

+--------+----------------------+
| name   | courses              |
+--------+----------------------+
| Alice  | Math, Science        |
| Charlie| Math, History        |
+--------+----------------------+