MySQL经典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的全部信息