学生表student（id，name） 老师表teacher（id，name） 课程表lesson（id，name） 老师和课程关联表（id，teacher_id，lesson_id） 学生和课程关联表（id，student_id，lesson_id）
查询王老师的课程 SELECT t.name AS '老师',l.name AS '课程' FROM teacher t LEFT JOIN teacher_lesson teal ON t.id = teal.teacher_id LEFT JOIN lesson l ON l.id = teal.lesson_id WHERE t.name = '王老师'
查询上了王老师或张老师课的学生
SELECT s.name AS '学生',l.name AS '课程',t.name AS '老师' FROM student s LEFT JOIN student_lesson stul ON s.id = stul.student_id LEFT JOIN lesson l ON l.id = stul.lesson_id LEFT JOIN teacher_lesson teal ON teal.lesson_id = l.id LEFT JOIN teacher t ON t.id = teal.teacher_id WHERE t.name = '王老师' OR t.name = '张老师'
查询即上王老师又上张老师课的学生
SELECT * FROM student s WHERE EXISTS (
SELECT 1 FROM teacher_lesson tl JOIN teacher t ON tl.teacher_id = t.id
JOIN student_lesson sl ON sl.lesson_id = tl.lesson_id WHERE sl.student_id = s.id AND t.name = '王老师'
) AND EXISTS (
SELECT 1 FROM teacher_lesson tl JOIN teacher t ON tl.teacher_id = t.id
JOIN student_lesson sl ON sl.lesson_id = tl.lesson_id WHERE sl.student_id = s.id AND t.name = '张老师'
);
