MySQL

练习题

与同学闲聊之余，知道了这一次的 MySQL 练习题，闲暇之余将前 18 道题完成了（按照作者的说法难度是依次递增的）。我使用的是 MySQL 8.0.33，练习题中用到了一些 8.0 的新特性

https://www.jianshu.com/p/476b52ee4f1b
https://www.jianshu.com/p/476b52ee4f1b

  1. 查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数

    1
    2
    3
    4
    5
    6
    7
    select t3.*, t1.score, t2.score
    from sc t1
             join sc t2 on t1.SId = t2.SId
             join student t3 on t1.SId = t3.SId
    where t1.CId = '01'
      and t2.CId = '02'
      and t1.score > t2.score;

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

      1
      2
      3
      4
      5
      select *
      from sc t1
               join sc t2 on t1.SId = t2.SId
      where t1.CId = '01'
        and t2.CId = '02';

    • 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )

      1
      2
      3
      4
      select *
      from sc t1
               left join sc t2 on t1.SId = t2.SId and t2.CId = '02'
      where t1.CId = '01';

    • 查询不存在” 01 “课程但存在” 02 “课程的情况

      1
      2
      3
      4
      5
      6
      select *
      from sc
      where sc.SId not in (select SId
                           from sc
                           where sc.CId = '01')
        and sc.CId = '02';

  2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

    1
    2
    3
    4
    5
    select avg(t1.score), t1.SId, t2.Sname
    from sc t1
             join student t2 on t1.SId = t2.SId
    group by t1.SId
    having avg(t1.score) >= 60;

  3. 查询在 SC 表存在成绩的学生信息

    1
    2
    3
    4
    select t2.*
    from sc t1
             join student t2 on t1.SId = t2.SId
    group by t1.SId;

  4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

    1
    2
    3
    4
    select t1.SId, t1.Sname, count(t2.SId), sum(t2.score)
    from student t1
             left join sc t2 on t1.SId = t2.SId
    group by t1.SId, t1.Sname;

    • 查有成绩的学生信息

      1
      2
      3
      4
      5
      select t1.*
      from student t1
               left join sc t2 on t1.SId = t2.SId
      where t2.SId is not null
      group by t1.SId;

  5. 查询「李」姓老师的数量

    1
    2
    3
    select count(1)
    from teacher
    where Tname like '李%';

  6. 查询学过「张三」老师授课的同学的信息

    1
    2
    3
    4
    5
    6
    select t1.*
    from student t1
             join sc t2 on t1.SId = t2.SId
             join course t3 on t2.CId = t3.CId
             join teacher t4 on t3.TId = t4.TId and t4.Tname = '张三'
    group by t2.SId;

  7. 查询没有学全所有课程的同学的信息

    1
    2
    3
    4
    5
    select t1.*, count(t2.sid)
    from student t1
             left join sc t2 on t1.SId = t2.SId
    group by t1.SId
    having count(t2.SId) < (select count(1) from course);

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

    1
    2
    3
    4
    5
    select t3.*
    from sc t2
             join student t3 on t2.SId = t3.SId
    where t2.cid in (select t1.CId from sc t1 where t1.SId = '01')
    group by t3.SId;

  9. 查询和” 01 “号的同学学习的课程 完全相同的其他同学的信息

    1
    2
    3
    4
    5
    6
    select group_concat(t1.CId order by t1.CId) as concat, t2.*
    from sc t1
             join student t2 on t1.SId = t2.SId
    where t1.SId <> '01'
    group by t1.SId
    having concat = (select group_concat(CId order by CId) from sc where SId = '01');

  10. 查询没学过”张三”老师讲授的任一门课程的学生姓名

    1
    2
    3
    4
    5
    6
    7
    8
    select *
    from student
    where SId not in (select t1.SId
                      from sc t1
                               join course t2 on t1.CId = t2.CId
                               join teacher t3 on t2.TId = t3.TId
                      where t3.Tname = '张三'
                      group by t1.SId);

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

    1
    2
    3
    4
    5
    6
    7
    8
    9
    select t1.SId, t2.Sname, avg(t1.score)
    from sc t1
             join student t2 on t1.SId = t2.SId
    where t1.SId in (select SId
                     from sc
                     where score < 60
                     group by SId
                     having count(SId) >= 2)
    group by t1.SId;

  12. 检索” 01 “课程分数小于 60，按分数降序排列的学生信息

    1
    2
    3
    4
    5
    6
    select t2.*, t1.score
    from sc t1
             join student t2 on t1.SId = t2.SId
    where t1.CId = '01'
      and t1.score < 60
    order by t1.score desc;

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

    1
    2
    3
    select t1.*, avg(t1.score) over (PARTITION BY t1.SId) as a
    from sc t1
    order by a desc;

  14. 查询各科成绩最高分、最低分和平均分：
    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    select t1.CId,
           t2.Cname,
           max(t1.score),
           min(t1.score),
           avg(t1.score),
           sum(if(t1.score >= 60, 1, 0)) / count(*)                   as 及格率,
           sum(if(t1.score >= 70 and t1.score < 80, 1, 0)) / count(*) as 中等率,
           sum(if(t1.score >= 80 and t1.score < 90, 1, 0)) / count(*) as 优良率,
           sum(if(t1.score >= 90, 1, 0)) / count(*)                   as 优秀率
    from sc t1
             join course t2 on t1.CId = t2.CId
    group by t1.CId
    order by count(*) desc, t1.CId;

  15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

    1
    2
    3
    select CID, SID, score, rank() over (PARTITION BY CId order by score desc ) as rank1
    from sc
    order by CId, rank1;

    没看懂他的写法

    1
    2
    3
    4
    5
    6
    select a.cid, a.sid, a.score, count(b.score) + 1 as rank1
    from sc as a
             left join sc as b
                       on a.score < b.score and a.cid = b.cid
    group by a.cid, a.sid, a.score
    order by a.cid, rank1;

    • 按各科成绩进行排序，并显示排名， Score 重复时合并名次

      1
      2
      3
      select CID, SID, score, dense_rank() over (PARTITION BY CId order by score desc ) as rank1
      from sc
      order by CId, rank1;

  16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

    1
    2
    3
    select SID, sum(score), rank() over (order by sum(score) desc)
    from sc
    group by SId;

    • 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

      1
      2
      3
      select SID, sum(score), dense_rank() over (order by sum(score) desc)
      from sc
      group by SId;

  17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    select t1.CId,
           t2.Cname,
           sum(if(t1.score <= 100 and t1.score > 85, 1, 0)) as '100-85',
           sum(if(t1.score <= 85 and t1.score > 70, 1, 0))  as '85-70',
           sum(if(t1.score <= 70 and t1.score > 60, 1, 0))  as '70-60',
           sum(if(t1.score <= 60 and t1.score > 0, 1, 0))   as '60-0',
           count(1)
    from sc t1
             join course t2 on t1.CId = t2.CId
    group by t1.CId, t2.Cname;

  18. 查询各科成绩前三名的记录

    1
    2
    3
    4
    5
    with a as (select SId, CId, score, rank() over (PARTITION BY CId order by score desc ) as rank1
               from sc)
    select *
    from a
    where a.rank1 <= 3;

SQL 日期区间查询

  • 日期范围获取每天（截止结束日期）

    1
    2
    3
    SELECT DATE_ADD('2021-04-04', INTERVAL CAST(help_topic_id AS signed INTEGER) DAY) AS `date`
    FROM mysql.help_topic
    WHERE help_topic_id < DATEDIFF('2021-07-04', '2021-04-04') + 1;

  • 日期范围获取每月（截止结束日期所在月）

    1
    2
    3
    SELECT DATE_FORMAT(DATE_ADD('2021-04-04', INTERVAL CAST(help_topic_id AS signed INTEGER) MONTH), '%Y-%m') AS `month`
    FROM mysql.help_topic
    WHERE help_topic_id < TIMESTAMPDIFF(MONTH, '2021-04-04', '2021-07-04') + 1;

  • 本周每天（截止今日）

    1
    2
    3
    4
    5
    SELECT DATE_FORMAT(
                   DATE_ADD(DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) + 0 DAY), INTERVAL CAST(help_topic_id AS signed INTEGER)
                            DAY), '%Y-%m-%d') AS `date`
    FROM mysql.help_topic
    WHERE help_topic_id <= WEEKDAY(NOW());

  • 近7日每天（截止今日）

    1
    2
    3
    4
    SELECT DATE_FORMAT(DATE_ADD(DATE_ADD(NOW(), INTERVAL - 6 DAY), INTERVAL CAST(help_topic_id AS SIGNED INTEGER) DAY),
                       '%Y-%m-%d') AS `date`
    FROM mysql.help_topic
    WHERE help_topic_id < 7;

  • 本月每天（截止今日）

    1
    2
    3
    4
    SELECT DATE_ADD(DATE_ADD(NOW(), INTERVAL - DAY(NOW()) + 1 DAY), INTERVAL CAST(help_topic_id AS signed INTEGER)
                    DAY) AS `date`
    FROM mysql.help_topic
    WHERE help_topic_id < DAY(NOW());

  • 近30日每天（截止今日）

    1
    2
    3
    4
    SELECT DATE_FORMAT(DATE_ADD(DATE_ADD(NOW(), INTERVAL - 29 DAY), INTERVAL CAST(help_topic_id AS SIGNED INTEGER) DAY),
                       '%Y-%m-%d') AS `date`
    FROM mysql.help_topic
    WHERE help_topic_id < 30;

  • 本季度每月（截止所在月）

    1
    2
    3
    4
    SELECT DATE_FORMAT(DATE_ADD(CONCAT(YEAR(NOW()), '-0', ((QUARTER(NOW()) - 1) * 3 + 1), '-01'), INTERVAL
                                CAST(help_topic_id AS SIGNED INTEGER) MONTH), '%Y-%m') as `month`
    FROM mysql.help_topic
    WHERE help_topic_id < MONTH(NOW()) % 3;

  • 近半年每月（截止所在月）

    1
    2
    3
    4
    SELECT DATE_FORMAT(DATE_ADD(DATE_ADD(NOW(), INTERVAL - 5 MONTH), INTERVAL CAST(help_topic_id AS SIGNED INTEGER) MONTH),
                       '%Y-%m') as `month`
    FROM mysql.help_topic
    WHERE help_topic_id < 6;

  • 本年每月（截止所在月）

    1
    2
    3
    4
    SELECT DATE_FORMAT(DATE_ADD(CONCAT(YEAR(NOW()), '-01-01'), INTERVAL CAST(help_topic_id AS SIGNED INTEGER) MONTH),
                       '%Y-%m') as `month`
    FROM mysql.help_topic
    WHERE help_topic_id < MONTH(NOW());

  • 近1年每月（截止所在月）

    1
    2
    3
    4
    SELECT DATE_FORMAT(DATE_ADD(DATE_ADD(NOW(), INTERVAL - 11 MONTH), INTERVAL CAST(help_topic_id AS SIGNED INTEGER) MONTH),
                       '%Y-%m') as `month`
    FROM mysql.help_topic
    WHERE help_topic_id < 12;
参考资料
许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。