多表查詢sql語句（5表）

學(xué)生表student（id，name）
老師表teacher（id，name）
課程表lesson（id，name）
老師和課程關(guān)聯(lián)表（id，teacher_id，lesson_id）
學(xué)生和課程關(guān)聯(lián)表（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 = '王老師'

查詢上了王老師或張老師課的學(xué)生

SELECT s.name AS '學(xué)生',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 = '張老師'

查詢即上王老師又上張老師課的學(xué)生
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 = '張老師'
);