回顾MySQL查询经典实例1-优快云博客

本文介绍如何使用SQL查询特定条件下的学生成绩，包括成绩对比、课程存在性检查等技巧，并展示了左连接、右连接的不同应用场景。

#1.查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
#分析：由于需要查询学生的全部信息，所以需要从分数表中查询到符合条件的分数,然后通过left join/right join进行组合
#右连接

select * from student RIGHT JOIN (
    select t1.sid, courseA, courseB 
    from
        (SELECT sid, score as courseA FROM score WHERE score.cid = '01') as t1, 
        (SELECT sid, score as courseA FROM score WHERE score.cid = '02') as t2
    where t1.sid = t2.sid and t1.coureA > t2.courseB
)r 
on student.sid = r.sid;

#左连接

select * from  (
    select t1.sid, courseA, courseB
    from
        (SELECT sid, score as courseA FROM score WHERE score.cid = '01') as t1, 
        (SELECT sid, score as courseA FROM score WHERE score.cid = '02') as t2
    where t1.sid = t2.sid and t1.coureA > t2.courseB
) r 
LEFT JOIN student
ON student.sid = r.sid;

#查询同时存在“01”课程和“02”课程的情况

select * from 
    (select * from score where score.cid = '01') as t1, 
    (select * from score where score.cid = '02') as t2
where t1.sid = t2.sid;

#查询存在"01"课程但可能不存在"02"课程的情况(不存在显示null)
#这一道就是明显需要使用join的情况了，02可能不存在，即为left join的右侧或right join 的左侧即可
#左连接

select * from 
(select * from score where score.cid = '01') as t1
left join 
(select * from score where score.cid = '02') as t2
on t1.sid = t2.sid;

#右连接

select * from 
(select * from score where score.cid = '02') as t2
right join 
(select * from score where score.cid = '01') as t1
on t1.sid = t2.sid;

#查询不存在" 01 "课程但存在" 02 "课程的情况

select * from score
where score.sid not in (
    select sid from score 
    where score.cid = '01'
) 
AND score.cid= '02';

暂时先总结到这里，待续......