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

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

liangzai2048

18008人浏览 · 2022-01-16 01:20:58

liangzai2048 · 2022-01-16 01:20:58 发布

MySQL练习

文章目录

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

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(*) 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';

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

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

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 '英语'
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;

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;

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;

进行分组排名

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;

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;

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;

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;

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;

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;

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;

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<month_day THEN age-1 ELSE age END AS 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(),INTERVAL 1 WEEK));

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

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

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

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

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

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

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

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

week()函数参见下列网址

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

date_format()函数参见下列网址

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

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

开放原子开发者工作坊

开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！

更多推荐

新华网：开源盛会在江城——2024开放原子开发者大会侧记

开源盛会在江城——2024开放原子开发者大会侧记

开放原子开发者工作坊

新华社：释放开源潜能，加快构筑软件创新“朋友圈”

释放开源潜能，加快构筑软件创新“朋友圈”

开放原子开发者工作坊

开源鸿蒙：引领万物智联，加速生态崛起

开放原子开发者工作坊

所有评论(0)

查看更多评论

liangzai2048

@hujieliang123

已为社区贡献1条内容

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

liangzai2048

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.按平均成绩从⾼到低显示所有学生的所有课程的成绩以及平均成绩

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.查询下月过生日的学生

所有评论(0)

liangzai2048

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