回顾MySQL查询经典实例3

谁借莪１个温暖的怀抱￠ 2022-10-26 04:58 139阅读 0赞

\#查询姓氏是李的老师的数量

select count(*) as nameNum
    from teacher
    where tname like '李%';

![20210201222913674.png][]

\#查询学习过张三老师课程的学生信息  
\#多表联合查询

select student.* from student,teacher,course,score
    where 
        student.sid = score.sid 
        and course.cid=score.cid 
        and course.tid = teacher.tid 
        and tname = '张三';

![20210201223152995.png][]

\#查询没有学习所有课程的同学信息  
\#因为有学生什么课都没有选，反向思考，先查询选了所有课的学生，再选择这些人之外的学生.

select * from student
    where student.sid not in (
      select score.sid from score
      group by score.sid
      having count(score.cid)= (select count(cid) from course)
    );

![20210201223435751.png][]

\#查询至少有一门课程与学号为“01”的同学所学相同的同学的信息

select * from student 
    where student.sid in (
        select score.sid from score 
        where score.cid in(
            select score.cid from score 
            where score.sid = '01'
        )

![20210201224447551.png][]

\#查询没有学过张三老师的任一门课的学生姓名  
\#嵌套查询

select * from student
        where student.sid not in(
            select score.sid from score where score.cid in(
                select course.cid from course where course.tid in(
                    select teacher.tid from teacher where tname = "张三"
                )
            )
        );

![2021020122505163.png][]

\#查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select student.sid, student.sname,b.avgScore
    from student RIGHT JOIN
    (select sid, AVG(score) as avgScore from score
        where sid in (
                  select sid from score 
                  where score<60 
                  GROUP BY sid 
                  HAVING count(score)>1)
        GROUP BY sid) b on student.sid=b.sid;

![20210201225618760.png][]

\#检索" 01 "课程分数小于 60，按分数降序排列的学生信息

select student.*, score.score from student, score
    where student.sid = score.sid
    and score.score < 60
    and cid = "01"
    ORDER BY score.score DESC;

![20210201225946138.png][]

[20210201222913674.png]: /images/20221024/a41a26559bde42cfa3af2a3432b54fc9.png
[20210201223152995.png]: /images/20221024/6ec82bb95c814b4189542a9ecfd1786b.png
[20210201223435751.png]: /images/20221024/a33df7ff1b3848ba9db3bb9be3fe0771.png
[20210201224447551.png]: /images/20221024/a30743a7710449c59886e6850185308c.png
[2021020122505163.png]: /images/20221024/a4d6e109465c47e2a83c92352e005bcc.png
[20210201225618760.png]: /images/20221024/f99b6453513e4f3b8f493b8e00f1a2da.png
[20210201225946138.png]: /images/20221024/e9723c141b864ed9a1e4749344529340.png