如何在MySQL中高效执行多对多关系的查询操作？

在MySQL数据库中，多对多查询是一种常见的操作，用于处理两个实体之间的复杂关系，这种关系通常涉及一个中间表来实现，该中间表记录了两个实体之间的所有关联信息。

创建表结构

为了实现多对多查询，需要创建三个表：学生表（student）、课程表（class）和中间表（cls_stu_tab），以下是创建这些表的SQL语句：

1、创建课程表：

“`sql

CREATE TABLE class (

id INT PRIMARY KEY AUTO_INCREMENT,

cname VARCHAR(32) NOT NULL

);

“`

2、创建学生表：

“`sql

CREATE TABLE student (

id INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(32) NOT NULL,

age INT NOT NULL

);

“`

3、创建中间表：

“`sql

CREATE TABLE cls_stu_tab (

id INT PRIMARY KEY AUTO_INCREMENT,

cid INT NOT NULL,

sid INT NOT NULL,

FOREIGN KEY (cid) REFERENCES class(id),

FOREIGN KEY (sid) REFERENCES student(id)

);

“`

插入数据

向这些表中插入一些示例数据：

1、向课程表插入数据：

“`sql

INSERT INTO class (cname) VALUES (‘打野’), (‘上单’), (‘辅助’), (‘射手’), (‘法师’);

“`

2、向学生表插入数据：

“`sql

INSERT INTO student (sname, age) VALUES (‘韩信’, 22), (‘李白’, 23), (‘孙悟空’, 54), (‘兰陵王’, 19), (‘小乔’, 22), (‘大桥’, 23), (‘曹操’, 33), (‘太乙真人’, 28), (‘鲁班七号’, 33), (‘狄仁杰’, 88), (‘阿珂’, 18), (‘关羽’, 25), (‘刘备’, 29), (‘张飞’, 23), (‘哪吒’, 3), (‘黄忠’, 56);

“`

3、向中间表插入数据：

“`sql

INSERT INTO cls_stu_tab (sid, cid) VALUES (1, 1), (2, 1), (2, 3), (3, 3), (4, 1), (4, 2), (5, 3), (5, 5), (6, 3), (6, 5), (7, 1), (7, 2), (7, 3), (7, 5);

“`

多对多查询示例

查询某个学生选择的课程

要查询某个学生选择的所有课程，可以使用以下SQL语句：

SELECT s.sname, c.cname
FROM student s
JOIN cls_stu_tab cst ON s.id = cst.sid
JOIN class c ON c.id = cst.cid
WHERE s.sname = '曹操';

结果如下：

查询某门课程被哪些学生选择

要查询某门课程被哪些学生选择了，可以使用以下SQL语句：

SELECT s.sname, c.cname
FROM student s
JOIN cls_stu_tab cst ON s.id = cst.sid
JOIN class c ON c.id = cst.cid
WHERE c.cname = '打野';

结果如下：

FAQs**:

1、如何删除不再需要的学生或课程？：在删除学生或课程之前，应先从中间表中删除相关的关联记录，要删除学生“曹操”，应使用以下SQL语句：

“`sql

DELETE FROM student WHERE sname = ‘曹操’;

DELETE FROM cls_stu_tab WHERE sid = (SELECT id FROM student WHERE sname = ‘曹操’);

“`

2、如何查询没有选择任何课程的学生？：要查询没有选择任何课程的学生，可以使用LEFT JOIN和IS NULL条件，以下是相应的SQL语句：

“`sql

SELECT s.sname

FROM student s

LEFT JOIN cls_stu_tab cst ON s.id = cst.sid

WHERE cst.sid IS NULL;

“`