MySQL- Many-to-many query

Now that we’ve learned about a joint query for two tables, let’s start with a joint query for many-to-many tables.

To prepare

  1. Create students table
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
Copy the code

Insert simulated data:

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');
Copy the code
  1. Create a Class schedule
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
Copy the code

Insert simulated data:

INSERT INTO VALUES (' teacher1', 'teacher1'); INSERT INTO courses(name, teacher) VALUES (' teacher2', 'teacher2'); INSERT INTO courses(name, teacher) VALUES (' English ', 'teacher3'); INSERT INTO courses(name, teacher) VALUES (' teacher4', 'teacher4'); INSERT INTO courses(name, teacher) VALUES (' teacher5', 'teacher5'); INSERT INTO courses(name, teacher) VALUES (' teacher6', 'teacher6');Copy the code
  1. Create a relational table between the class schedule and the student table
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
Copy the code

Insert simulated data:

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);
Copy the code

Student and course is many-to-many relationship, a student can select multiple courses, a course can also be multiple students, student_select_course is a student and course relationship table. Many-to-many queries typically create a relational table in the middle.

Actual practice

  1. Query information about all students who have selected a course

Tip: Internal connection

SELECT * FROM student_select_course AS ssc JOIN students ON ssc.student_id = students.id JOIN courses ON ssc.course_id =  courses.id;Copy the code
  1. Query the course selection of all students

Tip: Left connect

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;
Copy the code

Supplement – Put the student’s course selection information into an array:

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;Copy the code
  1. Query which students are not enrolled in classes

Tip: Left inner join

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;
Copy the code
  1. Which courses are not chosen
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;
Copy the code