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

    MySQL练习

    文章目录

    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)
); 

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    • 课程表 Course

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

      • 1
      • 2
      • 3
      • 4
      • 5

    • 教师表 Teacher

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

      • 1
      • 2
      • 3
      • 4

    • 成绩表 SC

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

      • 1
      • 2
      • 3
      • 4
      • 5

    插入数据

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

    • 学表 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' , '女'); 

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14

    • 课程表 Course

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

      • 1
      • 2
      • 3
      • 4

    • 教师表 Teacher

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

      • 1
      • 2
      • 3
      • 4

    • 成绩表 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
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19

    练习题目

    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

    • 1
    • 2
    • 3
    • 4
    • 5

    • 筛选出课程号为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、左连接

    • 1
    • 2
    • 3
    • 4
    • 5

    • 筛选出课程号为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、右连接

    • 1
    • 2
    • 3
    • 4
    • 5

    • 筛选出课程号为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(*) FROM Course);

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

    分析：
    1、首先查询学号为01同学所学的课程号
    2、然后查询至少有一门课与01同学所学相同的学生编号
    3、将上表的结果作为tt2与Student表tt2进行左连接查询，找到学生信息

    • 首先查询学号为01同学所学的课程号

      SELECT CId
      FROM SC
      WHERE SId = ‘01’;

    • 然后查询至少有一门课与01同学所学相同的学生编号

      SELECT t2.SId
      ,COUNT(t2.SId)
      FROM(
      SELECT CId
      FROM SC
      WHERE SId = ‘01’
      )t1 JOIN SC t2
      ON t1.CId = t2.CId
      GROUP BY t2.SId;

    • 将上表的结果作为tt2与Student表tt2进行左连接查询，找到学生信息

      SELECT tt2.SId
      ,tt2.Sname
      FROM(
      SELECT t2.SId
      ,COUNT(t2.SId)
      FROM(
      SELECT CId
      FROM SC
      WHERE SId = ‘01’
      )t1 JOIN SC t2
      ON t1.CId = t2.CId
      GROUP BY t2.SId
      ) tt1 LEFT JOIN Student tt2
      ON tt1.SId = tt2.SId;

    解法2

    分析：
    1、首先查询学号为01同学所学的课程号
    2、然后再对课程号不是01的所有学生的课程信息进行筛选
    3、最后将SC表作为t1表与Student表t2表进行关联，找到学生信息

    • 首先查询学号为01同学所学的课程号

      SELECT CId
      FROM SC
      WHERE SId = ‘01’;

    • 然后再对课程号不是01的所有学生的课程信息进行筛选

      SELECT DISTINCT SId
      FROM SC
      WHERE SId != ‘01’
      AND CId IN(
      SELECT CId
      FROM SC
      WHERE SId = ‘01’
      );

    • 最后将SC表作为t1表与Student表t2表进行关联，找到学生信息

      SELECT DISTINCT t1.SId
      ,t2.Sname
      FROM
      SC t1 JOIN Student t2
      ON t1.SId = t2.SId
      WHERE t1.SId != ‘01’
      AND CId IN(
      SELECT CId
      FROM SC
      WHERE SId = ‘01’
      );

    12.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息

    分析：
    1、首先查询出学号为01的课程信息
    2、然后查询出学号不为01的课程信息
    3、再将上面两个表分别作为t1、t2进行关联，得出01同学学习的所有课程信息和其他同学的所有课程信息
    4、然后将SC表作为t1表与Student表t2表进行关联、表tt1分组对学号为01的学生进行筛选

    • 首先查询出学号为01的课程信息

      SELECT SId
      ,CId
      FROM SC
      WHERE SId=‘01’;

    • 然后查询出学号不为01的课程信息

      SELECT SId
      ,CId
      FROM SC
      WHERE SId!=‘01’;

    • 再将上面两个表分别作为t1、t2进行关联，得出01同学学习的所有课程信息和其他同学的所有课程信息

      SELECT t2.SId
      ,t1.CId AS t1CId
      ,t2.CId AS t2CId
      FROM(
      SELECT SId
      ,CId
      FROM SC
      WHERE SId=‘01’
      ) t1 JOIN(
      SELECT SId
      ,CId
      FROM SC
      WHERE SId!=‘01’) t2
      ON t1.CId = t2.CId;

    • 然后将SC表作为t1表与Student表t2表进行关联、表tt1分组对学号为01的学生进行筛选

      SELECT tt1.SId
      ,COUNT(tt1.t1CId) AS t1Cnt
      ,COUNT(tt1.t2CId) as t2Cnt
      FROM(
      SELECT t2.SId
      ,t1.CId AS t1CId
      ,t2.CId AS t2CId
      FROM(
      SELECT SId
      ,CId
      FROM SC
      WHERE SId=‘01’
      ) t1 JOIN(
      SELECT SId
      ,CId
      FROM SC
      WHERE SId!=‘01’) t2
      ON t1.CId = t2.CId) tt1 GROUP BY tt1.SId
      HAVING t1Cnt AND t2Cnt = (SELECT COUNT(*)
      FROM SC
      WHERE SId = ‘01’);

    • 最后将上表作为ttt1与Student表ttt2进行关联，找到学生信息

      SELECT ttt1.SId
      ,ttt2.Sname
      FROM(
      SELECT tt1.SId
      ,COUNT(tt1.t1CId) AS t1Cnt
      ,COUNT(tt1.t2CId) as t2Cnt
      FROM(
      SELECT t2.SId
      ,t1.CId AS t1CId
      ,t2.CId AS t2CId
      FROM(
      SELECT SId
      ,CId
      FROM SC
      WHERE SId=‘01’
      ) t1 JOIN(
      SELECT SId
      ,CId
      FROM SC
      WHERE SId!=‘01’) t2
      ON t1.CId = t2.CId) tt1 GROUP BY tt1.SId
      HAVING t1Cnt AND t2Cnt = (SELECT COUNT(*)
      FROM SC
      WHERE SId = ‘01’)
      ) ttt1 JOIN Student ttt2
      ON ttt1.SId = ttt2.SId;

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

    1、首先查询出张三老师教授的课程
    2、然后查询出张三老师讲授的任意一门的课程号
    3、再查询出张三老师讲授的任意一门的课程号对应的SId
    4、最后查询出没学过"张三"老师讲授的任一门课程的学生姓名

    • 首先查询出张三老师教授的课程

      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’;

    • 然后查询出张三老师讲授的任意一门的课程号

      SELECT CId
      FROM Course
      WHERE TId = (
      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’
      );

    • 再查询出张三老师讲授的任意一门的课程号对应的SId

      SELECT SId
      FROM SC
      WHERE CId in(
      SELECT CId
      FROM Course
      WHERE TId = (
      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’)
      );

    • 最后查询出没学过"张三"老师讲授的任一门课程的学生姓名

      SELECT SId
      ,Sname
      FROM Student
      WHERE SId
      NOT IN (
      SELECT SId
      FROM SC
      WHERE CId in(
      SELECT CId
      FROM Course
      WHERE TId = (
      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’)
      )
      );

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

    分析：
    1、首先查询不及格同学的课程的学号和课程号
    2、再以SId进行分组将两门及其以上不及格课程的学号和平均成绩查询出来
    3、最后将上表作为表tt1与Student表tt2做关联，取出要查询的信息

    • 首先查询不及格同学的课程的学号和课程号

      SELECT SId
      ,CId
      ,score
      FROM SC
      WHERE score < 60;

    • 再以SId进行分组将两门及其以上不及格课程的学号和平均成绩查询出来

      SELECT t1.SId
      ,COUNT(t1.CId) AS cnt
      ,AVG(t1.score) AS avg_score
      FROM(
      SELECT SId
      ,CId
      ,score
      FROM SC
      WHERE score < 60) t1
      GROUP BY SId HAVING cnt >=2;

    • 最后将上表作为表tt1与Student表tt2做关联，取出要查询的信息

      SELECT tt2.SId,tt2.Sname,tt1.avg_score FROM
      (SELECT t1.SId
      ,COUNT(t1.CId) AS cnt
      ,AVG(t1.score) AS avg_score
      FROM(
      SELECT SId
      ,CId
      ,score
      FROM SC
      WHERE score < 60) t1
      GROUP BY SId HAVING cnt >=2) tt1 JOIN Student tt2
      ON tt1.SId = tt2.SId;

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

    分析：
    1、首先先将SC表t1与Student表t2进行关联
    2、最后检索出01课程分数小于60，按分数降序排列的学生信息

    • 首先先将SC表t1与Student表t2进行关联

      SELECT t2.SId
      ,t2.Sname
      ,t1.score
      FROM
      SC t1 JOIN Student t2
      ON t1.SId = t2.SId;

    • 最后检索出01课程分数小于60，按分数降序排列的学生信息

      SELECT t2.SId
      ,t2.Sname
      ,t1.score
      FROM
      SC t1 JOIN Student t2
      ON t1.SId = t2.SId
      AND t1.CId = ‘01’ AND t1.score < 60
      ORDER BY t1.score DESC;

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

    分析：
    1、首先先查出’01’语文、'02’数学、'03’英语课程号所对应的成绩
    2、然后查出所有课程的平均成绩
    3、因为有语文、数学、英语三门课程所有要对平均分进行分组
    4、最后将分组后的表作为tt1表分别与’01’语文、'02’数学、'03’英语进行左连接，找出所有学生的每门课的平均成绩，然后按平均分进行升序

    • 首先先查出’01’语文、'02’数学、'03’英语课程号所对应的成绩

      SELECT SId,score FROM SC WHERE SId = ‘01’;

    SELECT SId,score FROM SC WHERE SId = '02';

    • 1

    SELECT SId,score FROM SC WHERE SId = '03';

    • 1

    • 然后查出所有课程的平均成绩

      SELECT SId
      ,AVG(score) as avg_socre
      FROM SC
      GROUP BY SId;

    • 因为有语文、数学、英语三门课程所有要对平均分进行分组

      SELECT t1.SId
      ,AVG(t1.score) as avg_score
      FROM
      SC t1 GROUP BY t1.SId;

    • 最后将分组后的表作为tt1表分别与’01’语文、'02’数学、'03’英语进行左连接，找出所有学生的每门课的平均成绩，然后按平均分进行升序

      SELECT tt1.SId
   ,tt1.avg_score AS '平均分'
   ,tt2.score AS '语文'
   ,tt3.score AS '数学'
   ,tt4.score AS '英语'

      • 1
      • 2
      • 3
      • 4
      • 5

      FROM(
      SELECT t1.SId
      ,AVG(t1.score) as avg_score
      FROM
      SC t1 GROUP BY t1.SId
      ) tt1
      LEFT JOIN (select SId,score from SC where CId = ‘01’) tt2 on tt1.SId = tt2.SId
      LEFT JOIN (select SId,score from SC where CId = ‘02’) tt3 on tt1.SId = tt3.SId
      LEFT JOIN (select SId,score from SC where CId = ‘03’) tt4 on tt1.SId = tt4.SId
      ORDER BY tt1.avg_score DESC;

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

    • 首先先将SC表和Course进行关联并分组

      SELECT t1.CId
      ,t2.Cname
      FROM
      SC t1 JOIN Course t2
      ON t1.CId = t2.CId
      GROUP BY t1.CId,t2.Cname;

    • 然后分别计算出最高分、最低分、平均分、及格率、中等率、优良率、优秀率，并将查询结果按人数降序排列，若人数相同，按课程号升序排列

      SELECT t1.CId
      ,t2.Cname
      ,MAX(score) AS ‘最高分’
      ,MIN(score) AS ‘最低分’
      ,AVG(score) AS ‘平均分’
      ,CONCAT(ROUND(SUM(IF(score >= 60,1,0))*100/COUNT(score),2),“%”) AS ‘及格率’
      ,CONCAT(ROUND(SUM(IF(score >= 70,1,0))*100/COUNT(score),2),“%”) AS ‘中等率’
      ,CONCAT(ROUND(SUM(IF(score >= 80,1,0))*100/COUNT(score),2),“%”) AS ‘优良率’
      ,CONCAT(ROUND(SUM(IF(score >= 90,1,0))*100/COUNT(score),2),“%”) AS ‘优秀率’
      ,COUNT(score) AS ‘人数’
      FROM
      SC t1 JOIN Course t2
      ON t1.CId = t2.CId
      GROUP BY t1.CId,t2.Cname;
      ORDER BY ‘人数’ DESC,CId ASC;

    18.按各科平均成绩进行排序，并显示排名

    分析：
    1、首先查询出各科的平均分并进行排序
    2、最后定义一个变量@i然后将上表作为t1表进行排名计算，查询出结果

    • 首先查询出各科的平均分并进行排序

      SELECT CId
      ,AVG(score) avg_score
      FROM SC
      GROUP BY CId
      ORDER BY avg_score DESC;

    • 最后定义一个变量@i然后将上表作为t1表进行排名计算，查询出结果

      SET @i :=0;-- 定义一个变量
      SELECT t1.CId
      ,t1.avg_score
      ,@i := @i + 1 AS ‘排名’
      FROM(
      SELECT CId
      ,AVG(score) avg_score
      FROM SC
      GROUP BY CId
      ORDER BY avg_score DESC
      ) t1;

    19.按各科平均成绩进行排序，并显示排名，重复时不保留名次空缺

    分析：
    1、首先查询出各科的平均分并进行排序
    2、最后定义一个变量@i然后将上表作为t1表进行排名计算，查询出结果

    • 首先查询出各科的平均分并进行排序

      SELECT CId
      ,AVG(score) avg_score
      FROM SC
      GROUP BY CId
      ORDER BY avg_score DESC;

    • 最后定义一个变量@i然后将上表作为t1表进行排名计算，查询出结果

      SET @i :=0;-- 定义一个变量
      SELECT t1.CId
      ,t1.avg_score
      ,@i := @i + 1 AS ‘排名’
      FROM(
      SELECT CId
      ,AVG(score) avg_score
      FROM SC
      GROUP BY CId
      ORDER BY avg_score DESC
      ) t1;

    三种常见的排名

    row_number、dense_rank、rank在MySQL 5.7中的实现
    对SC中的学生score进行整体排名

    ROW_NUMBER

    1 2 3 4 5 6 7 没有重复排名，依次递增

    SET @i := 0;
SELECT  t1.SId
        ,t1.CId
        ,t1.score
        ,@i := @i + 1 as row_number
from (
    SELECT  SId
            ,CId
            ,score
    from SC
    order by score desc
) t1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    DENSE_RANK

    1 2 3 3 3 4 5 6 7 有重复时并列排名，最终排名也是连续的

    SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECT  t1.SId
        ,t1.CId
        ,t1.score
        ,@p := t1.score
        ,if(@p=@q,@i,@i := @i+1) as dense_rank
        ,@q :=@p
from (
    SELECT  SId
            ,CId
            ,score
    from SC
    order by score desc
) t1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    RANK

    1 2 3 3 3 6 7 8 有重复时并列排名，最终排名不连续

    SET @i := 0;
SET @j := 0;
SET @p := 0;
SET @q := 0;
SELECT  t1.SId
        ,t1.CId
        ,t1.score
        ,@j := @j + 1
        ,@p := t1.score
        ,if(@p=@q,@i,@i := @j) as rank
        ,@q :=@p
from (
    SELECT  SId
            ,CId
            ,score
    from SC
    order by score desc
) t1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    进行分组排名

    ROW_NUMBER

    查询每课程成绩最好的前三名 1 2 3 4 5 6 7 没有重复排名，依次递增

    SET @i := 0;
SET @p := 0;
SET @q := 0;
SELECT  tt1.SId
        ,tt2.SName
        ,tt1.CId
        ,tt1.score
        ,tt1.rn
from (
    select  t1.SId
            ,t1.CId
            ,t1.score
            ,@p := t1.CId
            ,if(@p=@q,@i := @i + 1,@i :=1) as rn
            ,@q := @p
    from (
        select  SId
                ,CId
                ,score
        from SC
        order by CId,score DESC
    ) t1
) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId
order by tt1.CId,tt1.rn;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    DENSE_RANK

    查询每课程成绩最好的前三名 1 2 3 3 3 4 5 6 7 有重复时并列排名，最终排名也是连续的

    SET @i := 0;
SET @p := 0;
SET @q := 0;
SET @j := 0;
SET @k := 0;
SELECT  tt1.SId
        ,tt2.SName
        ,tt1.CId
        ,tt1.score
        ,tt1.rn
from (
    select  t1.SId
            ,t1.CId
            ,t1.score
            ,@p := t1.CId
            ,@j := t1.score
            ,if(@p=@q,if(@j=@k,@i,@i := @i + 1),@i :=1) as rn
            ,@q := @p
            ,@k := @j
    from (
        select  SId
                ,CId
                ,score
        from SC
        order by CId,score DESC
    ) t1
) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId
order by tt1.CId,tt1.rn;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    RANK

    查询每课程成绩最好的前三名 1 2 3 3 3 6 7 8 有重复时并列排名，最终排名不连续

    SET @i := 0;
SET @p := 0;
SET @q := 0;
SET @j := 0;
SET @k := 0;
SET @m := 1;
SELECT  tt1.SId
        ,tt2.SName
        ,tt1.CId
        ,tt1.score
        ,tt1.rn
from (
    select  t1.SId
            ,t1.CId
            ,t1.score
            ,@p := t1.CId
            ,@j := t1.score
            ,if(@p=@q,@m := @m + 1,@m := 1)
            ,if(@p=@q,if(@j=@k,@i,@i := @m),@i :=1) as rn
            ,@q := @p
            ,@k := @j
    from (
        select  SId
                ,CId
                ,score
        from SC
        order by CId,score DESC
    ) t1
) tt1 join Student tt2 on tt1.rn<=3 and tt1.SId = tt2.SId
order by tt1.CId,tt1.rn;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    20.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

    122345
    分析：
    1、首先先查询总成绩，以SId进行分组，并进行排序
    2、最后将上表作为t1表然后定义变量@i、@j进行排名、@p、@q用来控制总分重复时 保留名次空缺

    • 首先先查询总成绩，以SId进行分组，并进行排序

      SELECT SId
      ,SUM(score)
      AS sum_score
      FROM SC
      GROUP BY SId
      ORDER BY sum_score DESC;

    *最后将上表作为t1表然后定义变量@i、@j进行排名、@p、@q用来控制总分重复时 保留名次空缺

    SET @i :=0;
SET @j :=0;
SET @p :=0;
SET @q :=0;
SELECT t1.SId
       ,t1.sum_score AS '总分'
       ,@j :=@j + 1
       ,@p :=t1.sum_score
       ,IF(@p=@q,@j,@i :=@j) AS '排名'
       ,@q :=@p 
FROM(
  SELECT SId
         ,SUM(score) 
  AS sum_score 
  FROM SC 
  GROUP BY SId 
  ORDER BY sum_score DESC
) t1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

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

    122456

    • 首先先查询总成绩，以SId进行分组，并进行排序

      SELECT SId
      ,SUM(score)
      AS sum_score
      FROM SC
      GROUP BY SId
      ORDER BY sum_score DESC;

    *最后将上表作为t1表然后定义变量@i进行排名、@p、@q用来控制总分重复时不保留名次空缺

    SET @i :=0;
SET @p :=0;
SET @q :=0;
SELECT  t1.SId
        ,t1.sum_score AS '总分'
        ,@p := t1.sum_score 
        ,if(@p=@q,@i,@i := @i+1) as '排名'
        ,@q :=@p
FROM(
  SELECT SId
         ,SUM(score) 
  AS sum_score 
  FROM SC 
  GROUP BY SId 
  ORDER BY sum_score DESC
) t1;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

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

    分析：
    1、首先将SC表t1与Course表t2进行关联，并以CId进行分组
    2、最后计算出各分段分数，显示出来

    • 首先将SC表t1与Course表t2进行关联，并以CId进行分组

      SELECT t1.CId
      ,t2.Cname
      FROM
      SC t1 JOIN Course t2
      ON t1.CId = t2.CId
      GROUP BY t1.CId,t2.Cname;

    • 最后计算出各分段分数，显示出来

      SELECT t1.CId
      ,t2.Cname
      ,CONCAT(ROUND(SUM(IF(score<=100 AND score>85,1,0)),2),“%”) AS ‘[100-85)’
      ,CONCAT(ROUND(SUM(IF(score<=85 AND score>70,1,0)),2),“%”) AS ‘[85-70)’
      ,CONCAT(ROUND(SUM(IF(score<=70 AND score>60,1,0)),2),“%”) AS ‘[70-60)’
      ,CONCAT(ROUND(SUM(IF(score<=60 AND score>0,1,0)),2),“%”) AS ‘[60-0)’
      FROM
      SC t1 JOIN Course t2
      ON t1.CId = t2.CId
      GROUP BY t1.CId,t2.Cname;

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

    分析：
    1、首先查出各科成绩并进行排序
    2、然后将上表作为t1表定义@i控制排序,@q、@p控制重复时保留空缺
    3、最后将上表作为tt1表与Student表tt2进行关联去除各科成绩排名前三的记录

    • 首先查出各科成绩并进行排序

      SELECT SId
      ,CId
      ,score
      FROM SC
      ORDER BY CId,score DESC;

    • 然后将上表作为t1表定义@i控制排序,@q、@p控制重复时保留空缺

      SET @i := 0;
      SET @p := 0;
      SET @q := 0;
      SELECT t1.SId
      ,t1.CId
      ,t1.score
      ,@p := t1.CId
      ,if(@p=@q,@i := @i + 1,@i :=1) as rn
      ,@q := @p
      FROM (
      SELECT SId
      ,CId
      ,score
      FROM SC
      ORDER BY CId,score DESC
      ) t1

    • 最后将上表作为tt1表与Student表tt2进行关联去除各科成绩排名前三的记录

      SET @i := 0;
      SET @p := 0;
      SET @q := 0;
      SELECT tt1.SId
      ,tt2.Sname
      ,tt1.CId
      ,tt1.score
      ,tt1.rn
      FROM(
      SELECT t1.SId
      ,t1.CId
      ,t1.score
      ,@p := t1.CId
      ,if(@p=@q,@i := @i + 1,@i :=1) as rn
      ,@q := @p
      FROM (
      SELECT SId
      ,CId
      ,score
      FROM SC
      ORDER BY CId,score DESC
      ) t1) tt1 JOIN Student tt2
      ON tt1.rn<=3 AND tt1.SId = tt2.SId
      ORDER BY tt1.CId,rn;

    24.查询每门课程被选修的学生数

    分析：
    以CId进行分组查询出每门课程被选修的学生数

    • 以CId进行分组查询出每门课程被选修的学生数

      SELECT CId
      ,COUNT(CId)
      AS cnt
      FROM SC
      GROUP BY CId;

    25.查询出只选修两门课程的学生学号和姓名

    分析：
    1、首先查询出选修两门课程的学生课程和学号
    2、然后将上表作为t1表与Studen表t2关联取出学生姓名，找出结果

    • 首先查询出选修两门课程的学生课程和学号

      SELECT SId
      ,COUNT(CId) AS cnt
      FROM SC
      GROUP BY SId
      HAVING cnt = 2;

    • 然后将上表作为t1表与Studen表t2关联取出学生姓名，找出结果

      SELECT t1.SId
      ,t2.Sname
      FROM(
      SELECT SId
      ,COUNT(CId) AS cnt
      FROM SC
      GROUP BY SId
      HAVING cnt = 2
      ) t1 JOIN Student t2
      ON t1.SId = t2.SId;

    26.查询男生、女生人数

    分析：
    以性别分组分别求出男生、女生的人数

    • 以性别分组分别求出男生、女生的人数

      SELECT Ssex
      ,COUNT(1)
      FROM Student
      GROUP BY Ssex;

    27.查询名字中含有「风」字的学生信息

    分析：
    使用 LIKE 模糊查询名字中含有「风」字的学生信息

    • 使用 LIKE 模糊查询名字中含有「风」字的学生信息

      SELECT * FROM Student WHERE Sname LIKE ‘%风%’;

    通过观察发现并不存在名字中含有凤字的学生信息

    28.查询同名同性学生名单，并统计同名同性人数

    分析：
    以学生姓名，性别分组，然后count出同名同性的人数

    • 以学生姓名，性别分组，然后count出同名同性的人数

      SELECT Sname
      ,Ssex
      ,COUNT(1)
      AS cnt
      FROM Student
      GROUP BY Sname,Ssex
      HAVING cnt > 1;

    29.查询 1990 年出生的学生名单

    分析：该题有两种解法
    1、使用LIKE模糊查询
    2、使用BETWEEN…AND…查询

    • 使用LIKE模糊查询

      SELECT * FROM Student
      WHERE Sage
      LIKE ‘1990%’;

    • 使用BETWEEN…AND…查询

      SELECT * FROM Student
      WHERE Sage
      BETWEEN ‘1990-1-1’ AND ‘1990-12-31’;

    30.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

    分析：
    1、将SC表与Course表做关联查询出每门课程的平均分
    2、最后将上表按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

    • 将SC表与Course表做关联查询出每门课程的平均分

      SELECT SC.CId
      ,Course.Cname
      ,AVG(SC.score) AS avg_score
      FROM SC
      JOIN Course
      ON SC.CId = Course.CId
      GROUP BY SC.CId,Course.Cname;

    • 最后将上表按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

      SELECT SC.CId
      ,Course.Cname
      ,AVG(SC.score) AS avg_score
      FROM SC
      JOIN Course
      ON SC.CId = Course.CId
      GROUP BY SC.CId,Course.Cname
      ORDER BY avg_score DESC,SC.CId;

    31.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

    分析：
    1、首先查询出成绩大于等于85的平均成绩的课程信息
    2、然后将上表作为t1表与Studen表t2做关联取出学生信息

    • 首先查询出成绩大于等于85的平均成绩的课程信息

      SELECT SId
      ,ROUND(AVG(score),2) AS avg_score
      FROM SC
      GROUP BY SId
      HAVING avg_score>=85;

    • 然后将上表作为t1表与Studen表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>=85
      ) t1 JOIN Student t2
      ON t1.SId = t2.SId;

    32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

    分析：
    1、首先查询出课程名为数学的CId
    2、然后再将上表作为字表查询出课程名为数学，且分数低于60的课程信息
    3、最后将上表作为表t1与Student表t2做关联取出学生信息

    • 首先查询出课程名为数学的CId

      SELECT CId
      FROM Course
      WHERE CName = ‘数学’;

    • 然后再将上表作为字表查询出课程名为数学，且分数低于60的课程信息

      SELECT SId
      ,Score
      FROM SC
      WHERE CId =(
      SELECT CId
      FROM Course
      WHERE CName = ‘数学’
      )AND score<60;

    • 最后将上表作为表t1与Student表t2做关联取出学生信息

      SELECT t2.Sname
      ,t1.Score
      FROM(
      SELECT SId
      ,Score
      FROM SC
      WHERE CId =(
      SELECT CId
      FROM Course
      WHERE CName = ‘数学’
      )AND score<60
      ) t1 JOIN Student t2
      ON t1.SId = t2.SId;

    33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

    分析：
    1、首先查询出所有课程的分数，并以SId分组（存在学生没成绩，没选课的情况为0分）
    2、然后将Student表t1与上表作为表t2做左连接，取出学生姓名

    • 首先查询出所有课程的分数，并以SId分组（存在学生没成绩，没选课的情况为0分）

      SELECT SId
      ,SUM(CASE CId WHEN ‘01’ THEN score ELSE 0 END) AS ‘语文成绩’
      ,SUM(CASE CId WHEN ‘02’ THEN score ELSE 0 END) AS ‘数学成绩’
      ,SUM(CASE CId WHEN ‘03’ THEN score ELSE 0 END) AS ‘英语成绩’
      FROM SC
      GROUP BY SId;

    • 然后将Student表t1与上表作为表t2做左连接，取出学生姓名

      SELECT t1.Sname
      ,t2.语文成绩
      ,t2.数学成绩
      ,t2.英语成绩
      FROM
      Student t1 LEFT JOIN (
      SELECT SId
      ,SUM(CASE CId WHEN ‘01’ THEN score ELSE 0 END) AS ‘语文成绩’
      ,SUM(CASE CId WHEN ‘02’ THEN score ELSE 0 END) AS ‘数学成绩’
      ,SUM(CASE CId WHEN ‘03’ THEN score ELSE 0 END) AS ‘英语成绩’
      FROM SC
      GROUP BY SId
      ) t2
      ON t1.SId = t2.SId;

    34.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

    分析：
    1、首先查询出成绩在70分以上的课程信息
    2、然后再将上表作为t1表与Student表t2关联，再与Course表t3做关联取出任何一门课程成绩在 70 分以上的姓名、课程名称和分数信息

    • 首先查询出成绩在70分以上的课程信息

      SELECT SId
      ,CId
      ,score
      FROM SC
      WHERE score>70;

    • 然后再将上表作为t1表与Student表t2关联，再与Course表t3做关联取出任何一门课程成绩在 70 分以上的姓名、课程名称和分数信息

      SELECT t2.Sname
      ,t3.Cname
      ,t1.Score
      FROM(
      SELECT SId
      ,CId
      ,score
      FROM SC
      WHERE score>70
      ) t1 JOIN Student t2
      ON t1.SId = t2. SId
      JOIN Course t3
      ON t1.CId = t3.CId;

    35.查询不及格的课程

    分析：
    1、首先查询出成绩低于60分的课程信息
    2、然后将上表作为表t1与Student表t2表做连接，再与Course表t3做连接，找出不及格课程的学生、课程编号、课程名称、成绩

    • 首先查询出成绩低于60分的课程信息

      SELECT DISTINCT CId
      ,score
      FROM SC
      WHERE score < 60;

    *然后将上表作为表t1与Student表t2表做连接，再与Course表t3做连接，找出不及格课程的学生、课程编号、课程名称、成绩

    SELECT  DISTINCT 
        t2.Sname
        ,t1.CId
        ,t3.CName
        ,t1.score
FROM(
    SELECT SId
           ,CId
           ,score
    FROM SC
    WHERE score<60
) t1 JOIN Student t2
ON t1.SId = t2.SId
JOIN Course t3
ON t1.CId = t3.CId;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    36.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

    分析：
    1、首先查询出课程编号为01且课程成绩在80分以上的课程信息
    2、然后将上表作为表t1与Student表t2做关联，取出学生的学号和姓名

    • 首先查询出课程编号为01且课程成绩在80分以上的课程信息

      SELECT SId
      ,score
      FROM SC
      WHERE CId = ‘01’
      AND score >= 80;

    • 然后将上表作为表t1与Student表t2做关联，取出学生的学号和姓名

      SELECT t1.SId
      ,t2.Sname
      FROM(
      SELECT SId
      ,score
      FROM SC
      WHERE CId = ‘01’
      AND score >= 80
      ) t1 JOIN Student t2
      ON t1.SId = t2.SId;

    37.求每门课程的学生人数

    • 将SC表t1与Course表t2做关联，并按CId分组，取出课程号、课程名称、每门课程

      SELECT t1.CId
      ,t2.cname
      ,COUNT(t1.CId) AS ‘人数’
      FROM SC t1 JOIN Course t2
      ON t1.CId = t2.CId
      GROUP BY t1.CId,t2.Cname;

    38.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

    分析：
    1、首先查询张三老师所授的课程TId
    2、然后在Course表中查询张三老师所授课程的课程编号CId
    3、再在SC表查询，成绩不重复的课程信息
    4、然后将成绩降序排列取出第一个成绩LIMIT 1，即是最高成绩
    5、最后将上表作为t1表与Student表t2连接，取出成绩最高的学生信息

    • 首先查询张三老师所授的课程TId

      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’;

    • 然后在Course表中查询张三老师所授课程的课程编号CId

      SELECT CId
      FROM Course
      WHERE TId = (
      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’
      );

    • 再在SC表查询，成绩不重复的课程信息

      SELECT SId
      ,CId
      ,score
      FROM SC
      WHERE CId IN (
      SELECT CId
      FROM Course
      WHERE TId = (
      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’
      )
      );

    • 然后将成绩降序排列取出第一个成绩LIMIT 1，即是最高成绩

      SELECT SId
      ,CId
      ,score
      FROM SC
      WHERE CId IN (
      SELECT CId
      FROM Course
      WHERE TId = (
      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’
      )
      ) ORDER BY score
      LIMIT 1;

    • 最后将上表作为t1表与Student表t2连接，取出成绩最高的学生信息

      SELECT t1.SId
      ,t2.Sname
      ,t1.CId
      ,t1.score
      FROM(
      SELECT SId
      ,CId
      ,score
      FROM SC
      WHERE CId IN (
      SELECT CId
      FROM Course
      WHERE TId = (
      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’
      )
      ) ORDER BY score
      LIMIT 1) t1 JOIN Student t2
      ON t1.SId = t2.SId;

    39.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

    分析：
    1、首先查询张三老师所授的课程TId
    2、然后在Course表中查询张三老师所授课程的课程编号CId
    3、再在SC表查询，成绩不重复的课程信息，并降序排列
    4、将上表作为表t1再定义@i、@p、@q三个变量@i控制排序，@p、@q控制分数重复保留名次
    5、然后将上表作为tt1表与Student表tt2做连接，取出最高分成绩，以及学生信息

    • 首先查询张三老师所授的课程TId

      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’;

    • 然后在Course表中查询张三老师所授课程的课程编号CId

      SELECT CId
      FROM Course
      WHERE TId = (
      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’
      );

    • 再在SC表查询，成绩不重复的课程信息，并降序排列

      SELECT SId
      ,CId
      ,score
      FROM SC
      WHERE CId IN (
      SELECT CId
      FROM Course
      WHERE TId = (
      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’
      )
      ) ORDER BY score DESC;

    • 将上表作为表t1再定义@i、@p、@q三个变量@i控制排序，@p、@q控制分数重复保留名次

      SET @i := 0;
      SET @p := 0;
      SET @q := 0;
      SELECT t1.SId
      ,t1.CId
      ,t1.score
      ,@p := t1.score
      ,IF(@p=@q,@i,@i := @i+1) AS dense_rank
      ,@q :=@p
      FROM (
      SELECT SId
      ,CId
      ,score
      FROM SC
      WHERE CId IN
      (
      SELECT CId
      FROM Course
      WHERE TId = (
      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’
      )
      ) ORDER BY score DESC) t1;

    • 然后将上表作为tt1表与Student表tt2做连接，取出最高分成绩，以及学生信息

      SET @i := 0;
      SET @p := 0;
      SET @q := 0;
      SELECT tt2.Sname
      ,tt1.SId
      ,tt1.CId
      ,tt1.score
      FROM(
      SELECT t1.SId
      ,t1.CId
      ,t1.score
      ,@p := t1.score
      ,IF(@p=@q,@i,@i := @i+1) AS dense_rank
      ,@q :=@p
      FROM (
      SELECT SId
      ,CId
      ,score
      FROM SC
      WHERE CId IN
      (
      SELECT CId
      FROM Course
      WHERE TId = (
      SELECT TId
      FROM Teacher
      WHERE Tname = ‘张三’
      )
      ) ORDER BY score DESC) t1
      ) tt1 JOIN Student tt2
      ON tt1.dense_rank = 1
      AND tt1.SId = tt2.SId;

    40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

    分析：
    1、首先现将SC表t1与自己SC表t2进行关联，取出成绩相同的学生编号、课程编号、学生成绩
    2、然后再与Student表t3进行关联取出学生信息

    • 首先现将SC表t1与自己SC表t2进行关联，取出成绩相同的学生编号、课程编号、学生成绩

      SELECT t1.CId
      ,t2.score
      FROM SC t1 JOIN SC t2
      ON t1.score = t2.score
      AND t1.CId != t2.CId;

    • 然后再与Student表t3进行关联取出学生信息

      SELECT t3.Sname
      ,t1.CId
      ,t2.score
      FROM SC t1 JOIN SC t2
      ON t1.score = t2.score
      AND t1.CId != t2.CId
      JOIN Student t3
      ON t1.SId = t3.SId;

    41.查询每门课程成绩最好的前两名

    分析：
    1、首先在SC表中查询出每门课程的成绩并按CId分数降序排列
    2、定义函数@i、@p、@q，按每门课程的成绩进行排序，并将上表作为t1表，然后再作为tt1表与Student表tt2进行连接取出每名课程成绩最好的前两名

    • 首先在SC表中查询出每门课程的成绩并按CId分数降序排列

      SELECT SId
      ,CId
      ,score
      FROM SC
      ORDER BY CId,score DESC;

    • 定义函数@i、@p、@q，按每门课程的成绩进行排序，并将上表作为t1表，然后再作为tt1表与Student表tt2进行连接取出每名课程成绩最好的前两名

      SET @i := 0;
      SET @p := 0;
      SET @q := 0;
      SELECT tt1.SId
      ,tt2.SName
      ,tt1.CId
      ,tt1.score
      ,tt1.rn
      FROM (
      SELECT t1.SId
      ,t1.CId
      ,t1.score
      ,@p := t1.CId
      ,IF(@p=@q,@i := @i + 1,@i :=1) AS rn
      ,@q := @p
      FROM (
      SELECT SId
      ,CId
      ,score
      FROM SC
      ORDER BY CId,score DESC
      ) t1
      ) tt1 JOIN Student tt2 ON tt1.rn<=2 AND tt1.SId = tt2.SId
      ORDER BY tt1.CId,rn;

    42.统计每门课程的学生选修人数（超过 5 人的课程才统计）。

    分析：
    1、以CId进行分组，统计出超过5人的课程信息

    • 以CId进行分组，统计出超过5人的课程信息

      SELECT CId
      ,COUNT(SId) AS ‘选课人数’
      FROM SC
      GROUP BY CId
      HAVING 选课人数 > 5;

    43.检索至少选修两门课程的学生学号

    分析：
    1、以SId进行分组，统计出至少选修两门课程的学生学号

    • 以SId进行分组，统计出至少选修两门课程的学生学号

      SELECT SId
      ,COUNT(CID) AS ‘选课数量’
      FROM SC
      GROUP BY SId
      HAVING 选课数量 >= 2;

    44.查询选修了全部课程的学生信息

    分析：
    1、首先查询出总课程属性作为表t3
    2、然后将Student表t1与SC表t2进行关联，以SId、Sname进行分组，用表t3筛选出选修了全部课程的学生信息

    • 首先查询出总课程属性作为表t3

      SELECT COUNT(t3.CId)
      FROM Course t3;

    • 然后将Student表t1与SC表t2进行关联，以SId、Sname进行分组，用表t3筛选出选修了全部课程的学生信息

      SELECT t1.SId
      ,t1.Sname
      ,COUNT(t2.CId) AS ‘选课数量’
      FROM Student t1 JOIN SC t2
      ON t2.SId = t1.SId
      GROUP BY t1.SId,t1.Sname
      HAVING COUNT(t2.CId) = (
      SELECT COUNT(t3.CId)
      FROM Course t3
      );

    45.查询各学生的年龄，只按年份来算

    分析：拿当前日期减去学生出生日期

    SELECT YEAR(now()) - date_format(Sage,'%Y') FROM Student;

    • 1

    46.按照出生日期来算，当前月日 < 出生年月的月日，则年龄减一

    分析：
    1、首先先在Student表中查询出出生日期、当前月日、出生年月的月日
    2、然后将上表作为表t1，计算出当前月日 < 出生年月的月日，则年龄减一

    • 首先先在Student表中查询出出生日期、当前月日、出生年月的月日

      SELECT SId
      ,Sname
      ,(YEAR(now()) - date_format(Sage,‘%Y’) ) AS age
      ,date_format(Sage,‘%m-%d’) AS month_day
      ,date_format(now(),‘%m-%d’) AS now_month_day
      FROM Student;

    • 然后将上表作为表t1，计算出当前月日 < 出生年月的月日，则年龄减一

      SELECT SId
      ,SName
      ,CASE WHEN now_month_day ,age
      FROM(
      SELECT SId
      ,Sname
      ,(YEAR(now()) - date_format(Sage,‘%Y’) ) AS age
      ,date_format(Sage,‘%m-%d’) AS month_day
      ,date_format(now(),‘%m-%d’) AS now_month_day
      FROM Student
      ) t1;

    47.查询本周过生日的学生

    分析：查询当前周与表中的周数是否相等即可

    SELECT SId
       ,SName
       ,Sage
       ,WEEK(Sage)
FROM Student
WHERE WEEK(Sage) = WEEK(now());

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    本周没人过生日所以没有数据

    48.查询下周过生日的学生

    分析：查询当前周+1与表中的周数是否相等即可

    SELECT SId
       ,SName
       ,Sage
       ,WEEK(Sage)
FROM Student
WHERE WEEK(Sage) = WEEK(date_add(now(),INTERVAL 1 WEEK));

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    下一周没人过生日所以没有数据

    49.查询本月过生日的学生

    分析：查询当前月与表中的月数是否相等即可

    SELECT SId
       ,SName
       ,Sage
       ,MONTH(Sage)
FROM Student
WHERE MONTH(Sage) = MONTH(now());

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    50.查询下月过生日的学生

    分析：查询当前月+1与表中的月数是否相等即可

    SELECT SId
       ,SName
       ,Sage
       ,MONTH(Sage)
FROM Student
WHERE MONTH(Sage) = MONTH(date_add(now(),INTERVAL 1 MONTH));

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    下一月没人过生日所以没有数据

    week()函数参见下列网址

    https://blog.csdn.net/moakun/article/details/82528773

    date_format()函数参见下列网址

    https://www.w3school.com.cn/sql/func_date_format.asp

    **终于到底啦！这篇是全网最细最详细解析，编了5w5q字，靓仔不容易，关注一下吧！**??

    先自我介绍一下，小编13年上师交大毕业，曾经在小公司待过，去过华为OPPO等大厂，18年进入阿里，直到现在。深知大多数初中级java工程师，想要升技能，往往是需要自己摸索成长或是报班学习，但对于培训机构动则近万元的学费，着实压力不小。自己不成体系的自学效率很低又漫长，而且容易碰到天花板技术停止不前。因此我收集了一份《java开发全套学习资料》送给大家，初衷也很简单，就是希望帮助到想自学又不知道该从何学起的朋友，同时减轻大家的负担。添加下方名片，即可获取全套学习资料哦

  • 相关阅读:
    Linux学习-41-检测和修复文件系统fsck命令用法
    SCAUoj实验11 链表操作
    css 俩组件放同一行
    redis 从小白到大师系列
    深度学习使用Keras进行多分类
    介绍一个博客图片上传辅助工具
    顾樵 量子力学I 导读（1）
    【rabbitmq】搭建rabbitmq启用插件报错
    强化学习——学习笔记3
    开源低代码框架 ReZero API 正式版本发布 ，界面操作直接生成API
  • 原文地址：https://blog.csdn.net/m0_67401417/article/details/126113619