union的用法

select a.s_id,a.s_name,round(avg(b.s_score),2) avg_score from
student a 
join score b on a.s_id = b.s_id 
group by a.s_id
having avg_score < 60
union
select a.s_id,a.s_name, 0 avg_score from
student a 
where a.s_id not in (select distinct s_id from score)

利用多张表联系之后筛选

select a.* from student a
where a.s_id not in(select s_id from score where c_id in(select c_id from course where t_id in (select t_id from teacher where t_name = '张三')))

结果：

s_id	s_name	s_birth	s_sex
06	吴兰	1992-03-01	女
08	王菊	1990-01-20	女

抽取前三名

SELECT a.s_id,a.c_id,a.s_score,b.s_score FROM score a
LEFT JOIN score b on a.c_id = b.c_id and a.s_score<b.s_score
GROUP BY a.s_id,a.c_id,a.s_score HAVING COUNT(b.s_id)<3
ORDER BY a.c_id,a.s_score DESC