MySQL子查询和连接查询例题

-- 1.查询01课程成绩比02课程成绩高的学生的学号、姓名以及两门课分别的成绩
select student.s_id,student.s_name,t1.01_score,t2.02_score from student 
		left join ( select s.s_id,sc.c_id,sc.s_score 01_score from student s
					left join score sc on s.s_id=sc.s_id 
					where sc.c_id = 01
				  ) as t1
		on student.s_id = t1.s_id
		left join ( select s.s_id,sc.c_id,sc.s_score 02_score from student s
					left join score sc on s.s_id=sc.s_id 
					where sc.c_id = 02
				  ) as t2
		on student.s_id = t2.s_id
where t1.01_score > t2.02_score

-- 2.查询所有学生的学号、姓名、选课门数以及平均成绩
select s.* 
        ,ifnull((select avg(s_score) from score where score.s_id = s.s_id),0) course_avg_score
        ,(select count(c_id) from score where score.s_id = s.s_id) course_count
from student s

	-- 使用group by 的写法
select s.s_id,s.s_name,ifnull(avg(c.s_score),0),count(c.c_id) from student s
		left join score c on s.s_id=c.s_id
group by s.s_id


-- 3.查询没有学过张三老师所教课程的学生的学号和姓名

select * from student s where s.s_id not in (
		select score.s_id from score
		where score.c_id = (select c.c_id from teacher t 
							left join course c on t.t_id = c.t_id
							where t.t_name = '张三'
							)
)

-- 4.查询所有选修的都不及格课程成绩的学生的学号和姓名
select s.s_id,s.s_name from student s 
where (select MAX(score.s_score) from score where s.s_id=score.s_id) < 60


-- 5.查询每个学生的学号、姓名以及每门课的成绩（每个学生对应查询结果中的一条记录，多门课程成绩显示在同一行）
select s.s_id,s.s_name
					,ifnull((select score.s_score from score where score.s_id = s.s_id AND score.c_id = '01'),0) '数学'
					,ifnull((select score.s_score from score where score.s_id = s.s_id AND score.c_id = '02'),0) '语文'
					,ifnull((select score.s_score from score where score.s_id = s.s_id AND score.c_id = '03'),0) '英语'
from student s