回顾MySQL查询经典实例2

#查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
#分析，我们可以根据学生编号将成绩分组，对分组的score求平均值，最后在选取结果中利用AVG函数进行求平均值并判断是否大于等于60即可
#一般的联合搜索

select student.sid,sname,avgScore from student,(
    select sid, AVG(score) as avgScore from score 
    GROUP BY sid
    HAVING AVG(score)> 60
    )r
where student.sid = r.sid;

#左连接

select s.sid,avgSocre,sname from(
select sid, AVG(score) as avgSocre from score  
GROUP BY sid 
HAVING AVG(score)> 60
)r left join 
(select student.sid, student.sname from
student)s on s.sid = r.sid;

#右连接

select student.sid, student.sname, r.avgScore from student right join(
      select sid, AVG(score) AS avgScore from score
      GROUP BY sid
      HAVING AVG(score)> 60
)r on student.sid = r.sid;

#查询在socre表存在成绩的学生信息

select DISTINCT student.*
from student,score
where student.sid=score.sid

#查询所有同学的学生编号，学生姓名，选课总数，所有课程的成绩综合
#联合查询（不会显示没有选课的学生）

#通过join查询可以显示没有选课的学生（没有选课的学生为NULL）

#查有成绩的学生信息
#exists

select * from student 
where exists (select score.sid from score where student.sid = score.sid);

#in

select * from student
where student.sid in (select score.sid from score);