回顾MySQL查询语句经典实例4

Bertha 。 2022-10-26 12:48 201阅读 0赞

\#按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select * from score 
    left join (
        select sid,avg(score) as avgScore from score 
        group by sid
        )r 
    on score.sid = r.sid
    order by avgScore desc;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70][]

\#查询各科成绩最高分，最低分和平均分  
\#显示形式：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率  
\#及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90  
\#要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select score.cid,
    max(score.score) as 最高分,
    min(score.score) as 最低分,
    AVG(score.score) as 平均分,
    count(*) as 选修人数,
    sum(case when score.score>=60 then 1 else 0 end )/count(*) as 及格率,
    sum(case when score.score>=70 and score.score<80 then 1 else 0 end )/count(*) as 中等率,
    sum(case when score.score>=80 and score.score<90 then 1 else 0 end )/count(*) as 优良率,
    sum(case when score.score>=90 then 1 else 0 end )/count(*) as 优秀率 
    from score
    GROUP BY score.cid
    ORDER BY count(*) DESC , score.cid ASC;

![20210202093232118.png][]

\#查询学生总成绩，并进行排名，总分重复不保留名次空缺  
\#SQL中的变量

set @crank=0;

\#查询语句

select q.sid, total, @crank := (@crank +1) as 名次 from(
    select score.sid, sum(score.score) as total from score
    group by score.sid
    order by total desc)q;

![2021020210160518.png][]

\#查询学生信息，所选课程信息，所选课程老师信息，所选课程成绩信息

select * from student s,course c,teacher t,score sc where
    s.sid = sc.sid and sc.cid = c.cid and c.tid = t.tid;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70]: /images/20221024/5f38f69fcc094d08b5a834042e737f79.png
[20210202093232118.png]: /images/20221024/4f83a82c23a044549264bf3770ed51c2.png
[2021020210160518.png]: /images/20221024/662aa459439246c6ae5504e47b0f7dda.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhbG1fZW5jb2Rl_size_16_color_FFFFFF_t_70 1]: /images/20221024/99fdf6462df9452d955b683cf45f3f15.png