MySQL-多对多查询

前面学习了两张表的联合查询，下面开始多对多表的联合查询。

准备

1. 创建students表

CREATE TABLE `students` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `age` int DEFAULT '0',
  `tel_num` varchar(20) NOT NULL,
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `modify_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phoneNum` (`tel_num`),
  UNIQUE KEY `tel_num` (`tel_num`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb3

插入模拟数据：

INSERT INTO students(name, age, tel_num) VALUES ('张三', 18, '11111111111');
INSERT INTO students(name, age, tel_num) VALUES ('zipp', 18, '11111111112');
INSERT INTO students(name, age, tel_num) VALUES ('li', 18, '11111111114');
INSERT INTO students(name, age, tel_num) VALUES ('joy', 18, '11111111115');
INSERT INTO students(name, age, tel_num) VALUES ('wang', 18, '11111111116');
INSERT INTO students(name, age, tel_num) VALUES ('xuan', 18, '11111111117');

1. 创建课程表

CREATE TABLE `courses` (

  `id` int NOT NULL AUTO_INCREMENT,

  `name` varchar(20) NOT NULL DEFAULT '',

  `teacher` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

插入模拟数据：

INSERT INTO courses(name, teacher) VALUES ('化学', 'teacher1');
INSERT INTO courses(name, teacher) VALUES ('语文', 'teacher2');
INSERT INTO courses(name, teacher) VALUES ('英语', 'teacher3');
INSERT INTO courses(name, teacher) VALUES ('数学', 'teacher4');
INSERT INTO courses(name, teacher) VALUES ('物理', 'teacher5');
INSERT INTO courses(name, teacher) VALUES ('生物', 'teacher6');

1. 创建课程表和学生表的关系表

CREATE TABLE `student_select_course` (

  `id` int NOT NULL AUTO_INCREMENT,

  `student_id` int NOT NULL,

  `course_id` int NOT NULL,

  PRIMARY KEY (`id`),

  KEY `student_id` (`student_id`),

  KEY `course_id` (`course_id`),

  CONSTRAINT `student_select_course_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON UPDATE CASCADE,

  CONSTRAINT `student_select_course_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

插入模拟数据：

INSERT INTO student_select_course(student_id, course_id) VALUES (17, 1);
INSERT INTO student_select_course(student_id, course_id) VALUES (18, 2);
INSERT INTO student_select_course(student_id, course_id) VALUES (19, 3);
INSERT INTO student_select_course(student_id, course_id) VALUES (20, 4);
INSERT INTO student_select_course(student_id, course_id) VALUES (21, 1);
INSERT INTO student_select_course(student_id, course_id) VALUES (21, 5);

说明： 学生和课程是多对多的关系，一个学生可选多个课程，一个课程也可被多个学生选择，student_select_course是学生和课程的关系表。一般多对多查询都会在中间建立一张关系表。

实战练习

1. 查询所有选择了课程的学生及对应的课程信息

tip：内连接

SELECT * FROM student_select_course AS ssc JOIN students ON ssc.student_id = students.id
    JOIN courses ON ssc.course_id = courses.id;

1. 查询所有学生的选课情况

tip：左连接

SELECT * FROM students LEFT JOIN student_select_course AS ssc ON ssc.student_id = students.id 
    LEFT JOIN courses ON ssc.course_id = courses.id;

补充-将学生对应的选课信息放到一个数组中：

SELECT students.id id, students.name name, students.tel_num tel_num,
    JSON_ARRAYAGG(JSON_OBJECT('id', courses.id, 'name', courses.name, 'teacher', courses.teacher))
        FROM students LEFT JOIN student_select_course AS ssc 
        ON ssc.student_id = students.id 
        LEFT JOIN courses ON ssc.course_id = courses.id GROUP BY students.id;

1. 查询哪些学生没有选课

tip：左内连接

SELECT * FROM students LEFT JOIN student_select_course AS ssc ON ssc.student_id = students.id 
    LEFT JOIN courses ON ssc.course_id = courses.id 
        WHERE courses.id IS NULL;

1. 哪些课程是没有被选择的

SELECT * FROM courses LEFT JOIN student_select_course AS ssc ON ssc.course_id = courses.id 
    LEFT JOIN students ON ssc.student_id = students.id
        WHERE students.id IS NULL;