Mysql 多表查询

现有三张表 student(s_id, name), lesson(l_id, name), stu_les(s_id, l_id)

1.求与"Zhangsan" 选了至少1门相同课程的所有学生的名字

并不需要使用到第三张表

SELECT 	student.name  FROM student JOIN stu_les ON student.`s_id` = stu_les.`s_id`
WHERE stu_les.`l_id` IN ( 
SELECT stu_les.`l_id` FROM student JOIN stu_les ON student.`s_id` = stu_les.`s_id` 
WHERE student.`name`="Zhangsan" 
)  AND student.name != "Zhangsan"

 

2.求选择了课程名为"lesson1"的学生的名字

将三张表使用join连接起来查询

SELECT s.name FROM student s 
JOIN stu_les sl ON s.`s_id`=sl.`s_id` 
JOIN lesson l ON l.`l_id`=sl.`l_id`
WHERE l.`name`="lesson1"