网络知识 娱乐 MySQL经典50道练习题及全网最详细解析

MySQL经典50道练习题及全网最详细解析

发布: 2023年2月14日 10:17:50

MySQL练习

文章目录

  • MySQL练习
  • 50道经典SQL练习题全网最详细解析
        • 数据表介绍
        • 建表语句
        • 插入数据
        • 练习题目
          • 1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
          • 2.查询同时存在" 01 “课程和” 02 "课程的情况
          • 3.查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
          • 4.查询不存在" 01 “课程但存在” 02 "课程的情况
          • 5.查询平均成绩⼤于等于 60 分的同学的学生编号和学生姓名和平均成绩
          • 6.查询在 SC 表存在成绩的学生信息
          • 7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，没成绩的显示为 null
          • 8.查询「李」姓老师的数量
          • 9.查询学过「张三」老师授课的同学的信息
          • 10.查询没有学全所有课程的同学的信息
          • 11.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
            • 解法2
          • 12.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
          • 13.查询没学过"张三"老师讲授的任一门课程的学生姓名
          • 14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
          • 15.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
          • 16.按平均成绩从⾼到低显示所有学生的所有课程的成绩以及平均成绩
          • 17.查询各科成绩最⾼分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
          • 18.按各科平均成绩进行排序，并显示排名
          • 19.按各科平均成绩进行排序，并显示排名，~~重复时不保留名次空缺~~
        • 三种常见的排名
        • ROW_NUMBER
        • DENSE_RANK
        • RANK
        • ROW_NUMBER
        • DENSE_RANK
        • RANK
          • 20.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
          • 21.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
          • 22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85），[85-70），[70-60），[60-0）及所占百分
          • 23.查询各科成绩前三名的记录
          • 24.查询每门课程被选修的学生数
          • 25.查询出只选修两门课程的学生学号和姓名
          • 26.查询男生、女生人数
          • 27.查询名字中含有「风」字的学生信息
          • 28.查询同名同性学生名单，并统计同名同性人数
          • 29.查询 1990 年出生的学生名单
          • 30.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
          • 31.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
          • 32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
          • 33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
          • 34.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
          • 35.查询不及格的课程
          • 36.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
          • 37.求每门课程的学生人数
          • 38.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
          • 39.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
          • 40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
          • 41.查询每门课程成绩最好的前两名
          • 42.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
          • 43.检索至少选修两门课程的学生学号
          • 44.查询选修了全部课程的学生信息
          • 45.查询各学生的年龄，只按年份来算
          • 46.按照出生日期来算，当前月日 < 出生年月的月日，则年龄减一
          • 47.查询本周过生日的学生
          • 48.查询下周过生日的学生
          • 49.查询本月过生日的学生
          • 50.查询下月过生日的学生

50道经典SQL练习题全网最详细解析

数据表介绍

  • 1.学⽣表 Student(SId,Sname,Sage,Ssex)

    • SId 学⽣编号

    • Sname 学⽣姓名

    • Sage 出⽣年⽉

    • Ssex 学⽣性别

  • 2.课程表 Course(CId,Cname,TId)

    • CId 课程编号

    • Cname 课程名称

    • TId 教师编号

  • 3.教师表 Teacher(TId,Tname)

    • TId 教师编号

    • Tname 教师姓名

  • 4.成绩表 SC(SId,CId,score)

    • SId 学⽣编号

    • CId 课程编号

    • score 分数

建表语句

  • 学⽣表 Student

    create table Student(
    SId varchar(10),
    Sname varchar(10),
    Sage datetime,
    Ssex varchar(10)
);

  • 课程表 Course

    create table Course(
    CId varchar(10),
    Cname nvarchar(10),
    TId varchar(10)
);

  • 教师表 Teacher

    create table Teacher(
    TId varchar(10),
    Tname varchar(10)
);

  • 成绩表 SC

    create table SC(
    SId varchar(10),
    CId varchar(10),
    score decimal(18,1)
);

插入数据

注意这里插入数据的时候，里面可能含有隐藏字符，出现显示不出的数据手动重新打一下再插入即可

  • 学⽣表 Student

    -- 学生表 Student
-- 学生表 Student
insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); 
insert into Student values('02' , '钱电' , '1990-12-21' , '男'); 
insert into Student values('03' , '孙风' , '1990-12-20' , '男'); 
insert into Student values('04' , '李云' , '1990-12-06' , '男'); 
insert into Student values('05' , '周梅' , '1991-12-01' , '女'); 
insert into Student values('06' , '吴兰' , '1992-01-01' , '女'); 
insert into Student values('07' , '郑竹' , '1989-01-01' , '女'); 
insert into Student values('09' , '张三' , '2017-12-20' , '女'); 
insert into Student values('10' , '李四' , '2017-12-25' , '女'); 
insert into Student values('11' , '李四' , '2012-06-06' , '女'); 
insert into Student values('12' , '赵六' , '2013-06-13' , '女'); 
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

  • 课程表 Course

    -- 科⽬表 Course 
insert into Course values('01' , '语文' , '02'); 
insert into Course values('02' , '数学' , '01'); 
insert into Course values('03' , '英语' , '03');

  • 教师表 Teacher

    -- 教师表 Teacher 
insert into Teacher values('01' , '张三'); 
insert into Teacher values('02' , '李四'); 
insert into Teacher values('03' , '王五');

  • 成绩表 SC

    -- 成绩表 SC 
insert into SC values('01' , '01' , 80); 
insert into SC values('01' , '02' , 90); 
insert into SC values('01' , '03' , 99); 
insert into SC values('02' , '01' , 70); 
insert into SC values('02' , '02' , 60); 
insert into SC values('02' , '03' , 80); 
insert into SC values('03' , '01' , 80); 
insert into SC values('03' , '02' , 80); 
insert into SC values('03' , '03' , 80); 
insert into SC values('04' , '01' , 50); 
insert into SC values('04' , '02' , 30); 
insert into SC values('04' , '03' , 20); 
insert into SC values('05' , '01' , 76); 
insert into SC values('05' , '02' , 87); 
insert into SC values('06' , '01' , 31); 
insert into SC values('06' , '03' , 34); 
insert into SC values('07' , '02' , 89); 
insert into SC values('07' , '03' , 98);

练习题目

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

分析：
1、找出有01成绩的学生成绩信息
2、找出有02成绩的学生成绩信息
3、通过SId将两表取别名t1、t2进行左连接
4、加上满足01‘语文’ > 02’数学’的条件

  • 找出有01成绩的学生成绩信息
SELECT * FROM SC WHERE CId='01';
  • 找出有02成绩的学生成绩信息
SELECT * FROM SC WHERE CId='02';
  • 通过SId将两表取别名t1、t2进行左连接
SELECT t1.SId,
       t1.CId,
       t1.score as '语文',
       t2.score as '数学' 
FROM (
SELECT 
       SId,
       CId,
       score 
  FROM SC 
   WHERE CId='01'
) t1
LEFT JOIN
(SELECT 
       SId,
       CId,
       score 
  FROM SC 
   WHERE CId='02'
) t2
ON t1.SId=t2.SId;
  • 加上满足01‘语文’ > 02’数学’的条件
SELECT t1.SId,
       t1.CId,
       t1.score as '语文',
       t2.score as '数学' 
FROM (
SELECT 
       SId,
       CId,
       score 
   FROM SC 
   WHERE CId='01'
) t1
LEFT JOIN(
SELECT 
       SId,
       CId,
       score 
   FROM SC 
   WHERE CId='02'
) t2
ON t1.SId=t2.SId
WHERE t1.score > t2.score;
  • 最后将上面的表作为一个子表tt1将我们想要查询的表关联起来，取出想要查询的字段
SELECT  tt1.SId
        ,tt2.Sname
        ,tt3.CId
        ,tt3.score
FROM (
    SELECT t1.SId
    FROM(
        SELECT SId
               ,CId
               ,score 
        FROM SC 
        where CId = '01'
    ) t1
    LEFT JOIN(
        SELECT	SId
                ,CId
                ,score 
        FROM SC 
        WHERE CId = '02'
    ) t2
    ON t1.SId = t2.SId
    WHERE t1.Score > t2.Score
) tt1 
JOIN Student tt2 ON tt1.SId = tt2.SId
JOIN SC tt3 ON tt1.SId = tt3.SId;
2.查询同时存在" 01 “课程和” 02 "课程的情况
分析：
     满足条件的SC表中：
       1、筛选出课程号为01的全部信息 AS命名为 t1
       2、筛选出课程号为02的全部信息 AS命名为 t2
       3、使用join连接取出同时存在01课程和02课程的SId
  • 筛选出课程号为01的全部信息
SELECT SId FROM SC WHERE CId = '01';
  • 筛选出课程号为02的全部信息
SELECT SId FROM SC WHERE CId = '02';
  • 使用join连接取出同时存在01课程和02课程的SId
SELECT t1.SId 
FROM(
    SELECT SId 
    FROM SC 
    WHERE CId='01'
)AS t1 JOIN (
    SELECT SId 
    FROM SC 
    WHERE CId='01'
)AS t2
ON t1.SId = t2.SId;
3.查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
分析：
     满足条件的SC表中：
       1、筛选出课程号为01的全部信息 AS命名为 t1
       2、筛选出课程号为02的全部信息 AS命名为 t2
       3、左连接
  • 筛选出课程号为01的全部信息
SELECT SId,CId,score FROM SC WHERE CId = '01';
  • 筛选出课程号为02的全部信息
SELECT SId,CId,score FROM SC WHERE CId = '02';
  • 左连接
SELECT t1.SId
       ,t1.CId
       ,t1.score
       ,t2.CId AS t2CId
       ,t2.score AS t2Score
FROM(
    SELECT SId
           ,CId
           ,score 
    FROM SC 
    WHERE CId = '01'
) t1 LEFT JOIN(
    SELECT SId
           ,CId
           ,score 
    FROM SC 
    WHERE CId = '02'
) t2
ON t1.SId = t2.SId;
4.查询不存在" 01 “课程但存在” 02 "课程的情况
分析：
     满足条件的SC表中：
       1、筛选出课程号为01的全部信息 AS命名为 t1
       2、筛选出课程号为02的全部信息 AS命名为 t2
       3、右连接
  • 筛选出课程号为01的全部信息
SELECT SId,CId,score FROM SC WHERE CId = '01';
  • 筛选出课程号为02的全部信息
SELECT SId,CId,score FROM SC WHERE CId = '02';
  • 右连接
SELECT t1.SId
       ,t1.CId
       ,t1.score
       ,t2.CId AS t2CId
       ,t2.score AS t2Score
FROM(
    SELECT SId
           ,CId
           ,score 
    FROM SC 
    WHERE CId = '01'
) t1 RIGHT JOIN(
    SELECT SId
           ,CId
           ,score 
    FROM SC 
    WHERE CId = '02'
) t2
ON t1.SId = t2.SId;
5.查询平均成绩⼤于等于 60 分的同学的学生编号和学生姓名和平均成绩

分析：
1、先查出平均成绩大于60分的SId，并用ROUND(X,D)保留两位小数
2、再将平均分作为表t1与Student表t2做连接得出结果

  • 先查出平均成绩大于60分的SId，并用ROUND(X,D)保留两位小数
SELECT SId
       ,ROUND(AVG(score),2)AS avg_score 
 FROM SC 
 GROUP BY SId 
 HAVING avg_score>=60;
  • 再将平均分作为表t1与Student表t2做连接得出结果
SELECT t1.SId
       ,t2.Sname
       ,t1.avg_score
FROM(
SELECT SId
       ,ROUND(AVG(score),2)AS avg_score 
 FROM SC 
 GROUP BY SId 
 HAVING avg_score>=60
)t1 JOIN Student t2
ON t1.SId = t2.SId;
6.查询在 SC 表存在成绩的学生信息

分析：
1、首先DISTINCTSC表的SId的数据
2、在将上表作为表t1与Student表t2连接查询出学生信息

  • 首先DISTINCTSC表的SId的数据
SELECT 
 DISTINCT SId 
 FROM SC;
  • 在将上表作为表t1与Student表t2连接查询出学生信息
SELECT t1.SId
       ,t2.Sname 
FROM(
    SELECT 
    DISTINCT SId 
    FROM SC
)t1 JOIN Student t2
ON t1.SId = t2.SId;
7.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩，没成绩的显示为 null

分析：
1、首先统计出SC表SId的选课总数和总成绩
2、在将上表作为表t1与Student表t2做右连接查询出所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩，没成绩的显示为 null

  • 首先统计出SC表SId的选课总数和总成绩
SELECT SId
       ,COUNT(CId) AS cnt
       ,SUM(score) AS sum_score 
FROM SC 
GROUP BY SId;
  • 在将上表作为表t1与Student表t2做右连接查询出所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩，没成绩的显示为 null
SELECT t2.SId
       ,t2.Sname
       ,t1.cnt AS '选课总数'
       ,t1.sum_score AS '总成绩'
FROM(
    SELECT SId
           ,COUNT(CId) AS cnt
           ,SUM(score) AS sum_score 
    FROM SC 
    GROUP BY SId
)t1 RIGHT JOIN Student t2
ON t1.SId = t2.SId;
8.查询「李」姓老师的数量

分析：直接使用COUNT(*)统计LIKE模糊查询查询「李」姓⽼师的数量

  • 直接使用COUNT(*)统计LIKE模糊查询查询「李」姓⽼师的数量
SELECT COUNT(*) 
FROM Teacher 
WHERE Teacher.Tname 
LIKE ('李%');
9.查询学过「张三」老师授课的同学的信息

分析：
1、首先查询张三老师的TId
2、然后查询张三老师的授课信息CId
3、再查询学过张三老师授课的学生SId
4、最后将学过张三老师授课的学生SId作为表t1和Student表t2连接查询出学生信息

  • 首先查询张三老师的TId
SELECT TId 
FROM Teacher 
WHERE Tname = '张三';
  • 然后查询张三老师的授课信息CId
SELECT CId 
FROM Course 
WHERE TId =(
           SELECT TId 
           FROM Teacher 
           WHERE Tname = '张三'
);
  • 再查询学过张三老师授课的学生SId
SELECT SId 
FROM SC 
WHERE CId = (
      SELECT CId 
      FROM Course 
      WHERE TId =(
            SELECT TId 
                FROM Teacher 
                WHERE Tname = '张三'
      )
);
  • 最后将学过张三老师授课的学生SId作为表t1和Student表t2连接查询出学生信息
SELECT t2.SId
,t2.Sname 
FROM(
  SELECT SId 
  FROM SC 
  WHERE CId = (
      SELECT CId 
      FROM Course 
      WHERE TId =(
            SELECT TId 
                FROM Teacher 
                WHERE Tname = '张三'
      )
))t1 JOIN Student t2
ON t1.SId = t2.SId;
10.查询没有学全所有课程的同学的信息

分析：
1、先查询所有课程信息
2、然后将所有课程信息作为表t1与Student表t2内连接查询出同学的信息
3、查询所有课程信息的数量
4、最后将上表作为tt1表对课程信息进行筛选小于课程总数的查询出没有学全所有课程的同学的信息

  • 先查询所有课程信息
SELECT SId
       ,CId 
FROM SC;
  • 然后将所有课程信息作为表t1与Student表t2内连接查询出同学的信息
SELECT t1.SId
       ,t1.SId
       ,t2.Sname
FROM(
    SELECT SId
          ,CId 
FROM SC
) t1 JOIN Student t2
ON t1.SId = t2.SId;
  • 查询所有课程信息的数量
SELECT count(*) 
FROM Course;
  • 最后将上表作为tt1表对课程信息进行筛选小于课程总数的查询出没有学全所有课程的同学的信息
SELECT  tt1.SId
        ,tt1.SName
        ,count(tt1.CId) as cnt
FROM (
    SELECT  t1.SId
            ,t1.CId
            ,t2.SName
    FROM SC t1 JOIN Student t2
    ON t1.SId = t2.SID
) tt1 GROUP BY tt1.SId,tt1.SName
Having cnt < (SELECT  count(

相关文章