链接
1、将老师的姓名和所教课程罗列出来
SELECT teacher_id,tname,cname FROM course
LEFT JOIN teacher ON course.`teacher_id`=teacher.`tid`;
2、查询所有的学生姓名及所在班级信息
SELECT * FROM student
LEFT JOIN class ON student.`class_id` = class.`cid`;
3、临时表
SELECT * FROM (SELECT * FROM student) AS B
4、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
思路：
先查到既选择001又选择002课程的所有同学
根据学生进行分组，如果学生数量等于2表示，两门均已选择
SELECT score.`student_id`,student.`sname` FROM score # 拿到了学过1或者2课程的学生情况
LEFT JOIN student ON score.`student_id` = student.`sid` # 连表可以拿到学生姓名
WHERE course_id IN (1,2) GROUP BY student_id HAVING COUNT(1) >1 # 条件1:学过1，2 条件2：学过课程大于1
5、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
同上，只不过将001和002变成 in (叶平老师的所有课)
SELECT score.`student_id`,student.`sname` FROM score
LEFT JOIN student ON score.`student_id` = student.`sid`
WHERE score.`course_id` IN (
SELECT course.`cid` FROM course LEFT JOIN teacher ON course.`teacher_id` = teacher.`tid` WHERE teacher.`tname` = '李平老师' # 李平老师教授的课程
) GROUP BY score.`student_id` HAVING COUNT(1) = (SELECT COUNT(1) FROM course LEFT JOIN teacher ON course.`teacher_id` = teacher.`tid` WHERE teacher.`tname` = '李平老师'# 李平老师教授课程的总数)