mysql如何实现教师与学生的关系存储

CREATE TABLE teachers (
    teacher_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    subject VARCHAR(50) -- 教授科目
);

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    grade VARCHAR(10)
);

CREATE TABLE teacher_student (
    teacher_id INT,
    student_id INT,
    PRIMARY KEY (teacher_id, student_id),
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES students(student_id) ON DELETE CASCADE
);

-- 添加教师
INSERT INTO teachers (name, subject) VALUES ('张老师', '数学');
INSERT INTO teachers (name, subject) VALUES ('李老师', '英语');
<p>-- 添加学生
INSERT INTO students (name, age, grade) VALUES ('小明', 15, '高一');
INSERT INTO students (name, age, grade) VALUES ('小红', 16, '高一');</p><p>-- 建立教学关系
INSERT INTO teacher_student (teacher_id, student_id) VALUES (1, 1); -- 张老师教小明
INSERT INTO teacher_student (teacher_id, student_id) VALUES (2, 1); -- 李老师也教小明</p>

SELECT s.name 
FROM students s
JOIN teacher_student ts ON s.student_id = ts.student_id
WHERE ts.teacher_id = 1;

SELECT t.name, t.subject
FROM teachers t
JOIN teacher_student ts ON t.teacher_id = ts.teacher_id
WHERE ts.student_id = 1;