回顾MySQL查询经典实例1

短命女 2022-10-26 04:57 179阅读 0赞

\#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;

![20210201214450275.png][]

\#左连接

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;

![20210201214512843.png][]

\#查询同时存在“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;

![20210201214530296.png][]

\#查询存在"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;

![20210201214546351.png][]

\#右连接

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;

![20210201214609857.png][]

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

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

![20210201214622936.png][]

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

[20210201214450275.png]: /images/20221024/ee099764960b401f8444baf031207b74.png
[20210201214512843.png]: /images/20221024/98c9aff90dde4d89b05ee59734954384.png
[20210201214530296.png]: /images/20221024/4bb34b91b99b41458ad23a2f22ab7b25.png
[20210201214546351.png]: /images/20221024/b54a7ac814f54f1fab8a263977c5a942.png
[20210201214609857.png]: /images/20221024/ae8a523774284edc8236e91db47c20a8.png
[20210201214622936.png]: /images/20221024/a6c95550509748b9a30c8a0eda2ff6f7.png