0
0

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 分数

建表语句

  • 学⽣表 Studentcreatetable Student( SId varchar(10), Sname varchar(10), Sage datetime, Ssex varchar(10));
  • 课程表 Coursecreatetable Course( CId varchar(10), Cname nvarchar(10), TId varchar(10));
  • 教师表 Teachercreatetable Teacher( TId varchar(10), Tname varchar(10));
  • 成绩表 SCcreatetable SC( SId varchar(10), CId varchar(10), score decimal(18,1));

插入数据

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

  • 学⽣表 Student-- 学生表 Student-- 学生表 Studentinsertinto Student values('01','赵雷','1990-01-01','男');insertinto Student values('02','钱电','1990-12-21','男');insertinto Student values('03','孙风','1990-12-20','男');insertinto Student values('04','李云','1990-12-06','男');insertinto Student values('05','周梅','1991-12-01','女');insertinto Student values('06','吴兰','1992-01-01','女');insertinto Student values('07','郑竹','1989-01-01','女');insertinto Student values('09','张三','2017-12-20','女');insertinto Student values('10','李四','2017-12-25','女');insertinto Student values('11','李四','2012-06-06','女');insertinto Student values('12','赵六','2013-06-13','女');insertinto Student values('13','孙七','2014-06-01','女');
  • 课程表 Course-- 科⽬表 Course insertinto Course values('01','语文','02');insertinto Course values('02','数学','01');insertinto Course values('03','英语','03');
  • 教师表 Teacher-- 教师表 Teacher insertinto Teacher values('01','张三');insertinto Teacher values('02','李四');insertinto Teacher values('03','王五');
  • 成绩表 SC-- 成绩表 SC insertinto SC values('01','01',80);insertinto SC values('01','02',90);insertinto SC values('01','03',99);insertinto SC values('02','01',70);insertinto SC values('02','02',60);insertinto SC values('02','03',80);insertinto SC values('03','01',80);insertinto SC values('03','02',80);insertinto SC values('03','03',80);insertinto SC values('04','01',50);insertinto SC values('04','02',30);insertinto SC values('04','03',20);insertinto SC values('05','01',76);insertinto SC values('05','02',87);insertinto SC values('06','01',31);insertinto SC values('06','03',34);insertinto SC values('07','02',89);insertinto 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
LEFTJOIN(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
LEFTJOIN(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
    LEFTJOIN(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 LEFTJOIN(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 RIGHTJOIN(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 
 GROUPBY 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 
 GROUPBY 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的数据
SELECTDISTINCT SId 
 FROM SC;

  • 在将上表作为表t1与Student表t2连接查询出学生信息
SELECT t1.SId
       ,t2.Sname 
FROM(SELECTDISTINCT 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 
GROUPBY 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 
    GROUPBY SId
)t1 RIGHTJOIN Student t2
ON t1.SId = t2.SId;

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

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

  • 直接使用COUNT(*)统计LIKE模糊查询查询「李」姓⽼师的数量
SELECTCOUNT(*)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;

  • 查询所有课程信息的数量
SELECTcount(*)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 GROUPBY tt1.SId,tt1.SName
Having cnt <(SELECTcount(*)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
GROUPBY 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
GROUPBY t2.SId
) tt1 LEFTJOIN 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的所有学生的课程信息进行筛选
SELECTDISTINCT SId
FROM SC 
WHERE SId !='01'AND CId IN(SELECT CId 
        FROM SC 
        WHERE SId ='01');

  • 最后将SC表作为t1表与Student表t2表进行关联，找到学生信息
SELECTDISTINCT 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 GROUPBY tt1.SId
   HAVING t1Cnt AND t2Cnt =(SELECTCOUNT(*)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 GROUPBY tt1.SId
   HAVING t1Cnt AND t2Cnt =(SELECTCOUNT(*)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 
NOTIN(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 
     GROUPBY 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 
     GROUPBY 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 <60ORDERBY 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';


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

  • 然后查出所有课程的平均成绩
SELECT SId
       ,AVG(score)as avg_socre 
FROM SC 
GROUPBY SId;

  • 因为有语文、数学、英语三门课程所有要对平均分进行分组
SELECT t1.SId
       ,AVG(t1.score)as avg_score 
FROM
SC t1 GROUPBY t1.SId;

  • 最后将分组后的表作为tt1表分别与’01’语文、'02’数学、'03’英语进行左连接，找出所有学生的每门课的平均成绩，然后按平均分进行升序
SELECT tt1.SId
       ,tt1.avg_score AS'平均分',tt2.score AS'语文',tt3.score AS'数学',tt4.score AS'英语'FROM(SELECT t1.SId
          ,AVG(t1.score)as avg_score 
   FROM
   SC t1 GROUPBY t1.SId
) tt1
LEFTJOIN(select SId,score from SC where CId ='01') tt2 on tt1.SId = tt2.SId
LEFTJOIN(select SId,score from SC where CId ='02') tt3 on tt1.SId = tt3.SId
LEFTJOIN(select SId,score from SC where CId ='03') tt4 on tt1.SId = tt4.SId
ORDERBY 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
GROUPBY 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
GROUPBY t1.CId,t2.Cname;ORDERBY'人数'DESC,CId ASC;

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

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

  • 首先查询出各科的平均分并进行排序
SELECT CId
       ,AVG(score) avg_score 
FROM SC 
GROUPBY CId 
ORDERBY avg_score DESC;

  • 最后定义一个变量@i然后将上表作为t1表进行排名计算，查询出结果
SET@i :=0;-- 定义一个变量SELECT t1.CId
       ,t1.avg_score
       ,@i :=@i+1AS'排名'FROM(SELECT CId
          ,AVG(score) avg_score 
FROM SC 
GROUPBY CId 
ORDERBY avg_score DESC) t1;

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

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

  • 首先查询出各科的平均分并进行排序
SELECT CId
       ,AVG(score) avg_score 
FROM SC 
GROUPBY CId 
ORDERBY avg_score DESC;

  • 最后定义一个变量@i然后将上表作为t1表进行排名计算，查询出结果
SET@i :=0;-- 定义一个变量SELECT t1.CId
       ,t1.avg_score
       ,@i :=@i+1AS'排名'FROM(SELECT CId
          ,AVG(score) avg_score 
FROM SC 
GROUPBY CId 
ORDERBY 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+1as row_number
from(SELECT  SId
            ,CId
            ,score
    from SC
    orderby score desc) t1;

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 :=@pfrom(SELECT  SId
            ,CId
            ,score
    from SC
    orderby score desc) t1;

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 :=@pfrom(SELECT  SId
            ,CId
            ,score
    from SC
    orderby score desc) t1;

进行分组排名

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 :=@pfrom(select  SId
                ,CId
                ,score
        from SC
        orderby CId,score DESC) t1
) tt1 join Student tt2 on tt1.rn<=3and tt1.SId = tt2.SId
orderby tt1.CId,tt1.rn;

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 :=@jfrom(select  SId
                ,CId
                ,score
        from SC
        orderby CId,score DESC) t1
) tt1 join Student tt2 on tt1.rn<=3and tt1.SId = tt2.SId
orderby tt1.CId,tt1.rn;

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 :=@jfrom(select  SId
                ,CId
                ,score
        from SC
        orderby CId,score DESC) t1
) tt1 join Student tt2 on tt1.rn<=3and tt1.SId = tt2.SId
orderby tt1.CId,tt1.rn;
20.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

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

  • 首先先查询总成绩，以SId进行分组，并进行排序
SELECT SId
       ,SUM(score)AS sum_score 
FROM SC 
GROUPBY SId 
ORDERBY 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 :=@pFROM(SELECT SId
         ,SUM(score)AS sum_score 
  FROM SC 
  GROUPBY SId 
  ORDERBY sum_score DESC) t1;

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

122456

  • 首先先查询总成绩，以SId进行分组，并进行排序
SELECT SId
       ,SUM(score)AS sum_score 
FROM SC 
GROUPBY SId 
ORDERBY 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 :=@pFROM(SELECT SId
         ,SUM(score)AS sum_score 
  FROM SC 
  GROUPBY SId 
  ORDERBY sum_score DESC) t1;

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 
GROUPBY t1.CId,t2.Cname;

  • 最后计算出各分段分数，显示出来
SELECT t1.CId
       ,t2.Cname
       ,CONCAT(ROUND(SUM(IF(score<=100AND score>85,1,0)),2),"%")AS'[100-85)',CONCAT(ROUND(SUM(IF(score<=85AND score>70,1,0)),2),"%")AS'[85-70)',CONCAT(ROUND(SUM(IF(score<=70AND score>60,1,0)),2),"%")AS'[70-60)',CONCAT(ROUND(SUM(IF(score<=60AND score>0,1,0)),2),"%")AS'[60-0)'FROM 
   SC t1 JOIN Course t2 
   ON t1.CId = t2.CId 
GROUPBY t1.CId,t2.Cname;

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

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

  • 首先查出各科成绩并进行排序
SELECT SId
       ,CId
       ,score 
FROM SC 
ORDERBY 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 :=@pFROM(SELECT  SId
                ,CId
                ,score
        FROM SC
        ORDERBY 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 :=@pFROM(SELECT  SId
                ,CId
                ,score
        FROM SC
        ORDERBY CId,score DESC) t1) tt1 JOIN Student tt2
ON tt1.rn<=3AND tt1.SId = tt2.SId
ORDERBY tt1.CId,rn;

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

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

  • 以CId进行分组查询出每门课程被选修的学生数
SELECT CId
       ,COUNT(CId)AS cnt 
FROM SC 
GROUPBY CId;

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

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

  • 首先查询出选修两门课程的学生课程和学号
SELECT SId
       ,COUNT(CId)AS cnt 
FROM SC 
GROUPBY SId 
HAVING cnt =2;

  • 然后将上表作为t1表与Studen表t2关联取出学生姓名，找出结果
SELECT t1.SId
       ,t2.Sname 
FROM(SELECT SId
          ,COUNT(CId)AS cnt 
   FROM SC 
   GROUPBY SId 
   HAVING cnt =2) t1 JOIN Student t2
ON t1.SId = t2.SId;

26.查询男生、女生人数

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

  • 以性别分组分别求出男生、女生的人数
SELECT Ssex
,COUNT(1)FROM Student 
GROUPBY Ssex;

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

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

  • 使用 LIKE 模糊查询名字中含有「风」字的学生信息
SELECT*FROM Student WHERE Sname LIKE'%风%';

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

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

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

  • 以学生姓名，性别分组，然后count出同名同性的人数
SELECT Sname
       ,Ssex
       ,COUNT(1)AS cnt 
FROM Student 
GROUPBY 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 
GROUPBY SC.CId,Course.Cname;

  • 最后将上表按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT  SC.CId
        ,Course.Cname
        ,AVG(SC.score)AS avg_score
FROM SC 
JOIN Course 
ON SC.CId = Course.CId 
GROUPBY SC.CId,Course.Cname
ORDERBY 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 
GROUPBY 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 
   GROUPBY 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 ELSE0END)AS'语文成绩',SUM(CASE CId WHEN'02'THEN score ELSE0END)AS'数学成绩',SUM(CASE CId WHEN'03'THEN score ELSE0END)AS'英语成绩'FROM SC
GROUPBY SId;

  • 然后将Student表t1与上表作为表t2做左连接，取出学生姓名
SELECT t1.Sname
       ,t2.语文成绩
       ,t2.数学成绩
       ,t2.英语成绩 
FROM
Student t1 LEFTJOIN(SELECT  SId
           ,SUM(CASE CId WHEN'01'THEN score ELSE0END)AS'语文成绩',SUM(CASE CId WHEN'02'THEN score ELSE0END)AS'数学成绩',SUM(CASE CId WHEN'03'THEN score ELSE0END)AS'英语成绩'FROM SC
        GROUPBY 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分的课程信息
SELECTDISTINCT CId
                ,score 
FROM SC 
WHERE score <60;

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

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

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
GROUPBY 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 ='张三'))ORDERBY score
LIMIT1;

  • 最后将上表作为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 ='张三'))ORDERBY score
LIMIT1) 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 ='张三'))ORDERBY 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 :=@pFROM(SELECT SId
         ,CId
         ,score 
  FROM SC
  WHERE CId IN(SELECT CId 
   FROM Course 
   WHERE TId =(SELECT TId 
     FROM Teacher 
     WHERE Tname ='张三'))ORDERBY 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 :=@pFROM(SELECT SId
         ,CId
         ,score 
  FROM SC
  WHERE CId IN(SELECT CId 
   FROM Course 
   WHERE TId =(SELECT TId 
     FROM Teacher 
     WHERE Tname ='张三'))ORDERBY score DESC) t1
) tt1 JOIN Student tt2
ON tt1.dense_rank =1AND 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
ORDERBY 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 :=@pFROM(SELECT  SId
                ,CId
                ,score
        FROM SC
        ORDERBY CId,score DESC) t1
) tt1 JOIN Student tt2 ON tt1.rn<=2AND tt1.SId = tt2.SId
ORDERBY tt1.CId,rn;

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

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

  • 以CId进行分组，统计出超过5人的课程信息
SELECT CId
       ,COUNT(SId)AS'选课人数'FROM SC 
GROUPBY CId
HAVING 选课人数 >5;

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

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

  • 以SId进行分组，统计出至少选修两门课程的学生学号
SELECT SId
       ,COUNT(CID)AS'选课数量'FROM SC 
GROUPBY SId
HAVING 选课数量 >=2;

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

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

  • 首先查询出总课程属性作为表t3
SELECTCOUNT(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
GROUPBY t1.SId,t1.Sname
HAVINGCOUNT(t2.CId)=(SELECTCOUNT(t3.CId)FROM Course t3
);

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

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

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

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
       ,CASEWHEN now_month_day<month_day THEN age-1ELSE age ENDAS new_age
       ,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());

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

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

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

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

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

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

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

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

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

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

SELECT SId
       ,SName
       ,Sage
       ,MONTH(Sage)FROM Student
WHEREMONTH(Sage)=MONTH(date_add(now(),INTERVAL1MONTH));

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

week()函数参见下列网址

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

date_format()函数参见下列网址

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

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

标签： mysql 数据库 sql
本文转载自: https://blog.csdn.net/hujieliang123/article/details/122443231
版权归原作者 liangzai2048 所有， 如有侵权，请联系我们删除。
登录后发布评论

“MySQL经典50道练习题及全网最详细解析”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

Zabbix 配置端口监控

Python 单元测试详解：Unittest 框架的应用与最佳实践

将文件从Mac传输到 iPhone 的 6 种简单方法

Macbook Pro 的 Type-C 接口突然不工作的解决方案

Unity(2022.3.41LTS) - UI详细介绍- Button(按钮)TMP

探索高效消息处理：scala-kafka开源项目推荐

人工智能两个要素：机器学习算法+大数据

文章导航