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

最新推荐文章于 2024-03-13 10:15:46 发布
最新推荐文章于 2024-03-13 10:15:46 发布
阅读量1w 收藏 129
点赞数 15
分类专栏： 面试 学习路线 阿里巴巴 文章标签： android 前端 后端
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_67392182/article/details/126037354
版权
面试 同时被 3 个专栏收录
82 篇文章 7 订阅
订阅专栏
阿里巴巴
82 篇文章 1 订阅
订阅专栏
学习路线
79 篇文章 1 订阅
订阅专栏

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

  • 课程表 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字，靓仔不容易，关注一下吧！**??

bp粉
关注
  • 15
    点赞
  • 踩
  • 129
    收藏
    觉得还不错? 一键收藏
  • 5
    评论
专栏目录
ysql:YSQL - YAML 到 SQL
07-03
YSQL YSQL 是一种基于 YAML 或 JSON 的可转换为 SQL 的语言。 用法 var Query = require('ysql').Query; var query = new Query(); var ysql = fs.readFileSync('format.yml', 'utf8'); query.parse(ysql).toSql(); // output: { text: 'SELECT id, name AS "title" FROM "docs"', params: [] } 其中文件format.yml内容是： SELECT: COLUMNS: - COLUMN: name: id - COLUMN: name: name as: title FROM: TABLE:
Mysql经典面试题答案
09-18
Mysql经典面试题答案，富含很多bat面试题，经典实用。
50道经典MySQL练习题（含解答）
孙不坚1208
05-30 9324
本文摘要：本篇主要分享50道经典MySQL练习题（含解答）。
160道MySQL数据库精选面试题+答案解析
最新发布
m0_61568580的博客
03-13 1226
当我们使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。而且如果分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用像上述那取出所有数据进行排序再返回某个范围内的数据。如果两个事务没有更新相同的⾏，它们在备库上可以并⾏执⾏。
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做出来才能达到最好的训练效果。（感觉做这种题是会上瘾的……） 我使用的Mysql版本是5.7.19。SQL语句可能会因数据库系统的不同会有少许差异。 这里放上MySQL语法执行的先后顺序： 7:SELECT 查询列表 1:FROM 表 2:连接类型 JOIN 表2 3:ON 连接条件 4:WHERE 筛选条件 5:GROUP BY 分组 6:HAV
ysql-646857.pdf
03-20
ysql-646857.pdf
MySQL练习题及答案-11页
06-01
MySQL练习题及答案
sql语句练习50题(Mysql版)
Coder-Wf
09-10 3684
表名和字段 –1.学生表 Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 –2.课程表 Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 –3.教师表 Teacher(t_id,t_name) –教师编号,教师姓名 –4.成绩表 Score(s_id,c_id,s_scor...
MySQL学习：50道经典题目（含参考答案）(更新中）
weixin_42661635的博客
04-27 7509
首先根据course_id将成绩表分为两张表，筛选出" 1 "课程比" 2 "课程成绩高的学生，再与学生表关联。1. 查询" 1 "课程比" 2 "课程成绩高的学生的信息（学号、姓名、性别、出生日期）及课程分数。利用自连接，统计比当前分数高的分数有几个，排名就是几加1。使用case when语句，多个排序条件时用逗号分隔。利用课程数量，计算出所有课程数和每个学生的选课数量。先筛选与" 1 "号同学选课数量相同的同学，再剔除。10. 查询没有学全所有课程的同学的信息。选了" 1 "号同学没选的课的同学。
MySQL基础篇 | 经典三十四道练习题
m0_61933976的博客
09-18 5618
关于MySQL的三十四道练习题，大佬们都会做吗
MySQL经典练习50题
08-08
MySQL经典题库50题，无答案。MySQL经典题库50题，无答案。
eclipse连接mysql全网最详细教程
05-30
java
MySQL55题答案.pdf
12-26
MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf MySQL55题答案.pdf
全网最详细的mysql安装配置教程.zip
01-30
mysql全网最详细的mysql安装配置教程.zip
论文研究-基于GTK 库和M ySQL数据库应用详解 .pdf
08-16
基于GTK 库和M ySQL数据库应用详解，张绪兵，陈今润，本文在介绍gtk 编程框架后，简略讲述glade 配合gtk 的使用方法，然后重点讨论使用gtk 的GtkTreeView显示控件和MySQL数据库结合，开发数据保��
MySQL50道练习题.zip
04-13
MySQL50道练习题
mysql经典50题_大数据_mysql经典50题_mysql经典五十题_hive_
10-01
用于后端开发者sql语言练习，题目非常经典，部分题目含答案，均为本人填写。学习hadoop的大数据同学们也可也用来练习hive，同含部分hive答案。
Mysql经典例题练习与总结
qq_42265608的博客
06-14 3155
Mysql练习题
mysql经典50道练习题pdf
08-17
MySQL经典50道练习题pdf是一本经典的MySQL数据库练习题集合，对于想要提高MySQL数据库操作能力的人士来说是非常有价值的。 这本练习题集合涵盖了各种MySQL数据库操作方面的题目，包括数据库创建、表创建、数据插入、查询语句编写等等。练习题的难度从简单到复杂逐渐增加，旨在帮助读者逐步提升对MySQL数据库的理解和应用能力。 通过这本练习题集合，读者可以通过实际的操作来巩固和应用之前学到的理论知识，培养自己的解决问题的能力。此外，这本练习题集合还提供了详细的答案和解析，读者可以通过对比自己的答案来检验和纠正自己的错误。 通过完成这些练习题，读者可以更好地掌握MySQL数据库的各种操作技巧和用法。同时，通过与他人的交流和讨论，读者还可以学习到其他人解决问题的思路和方法。 总之，MySQL经典50道练习题pdf对于想要提高MySQL数据库操作能力的人来说是一本非常有价值的书籍。读者通过完成这些练习题，可以巩固自己的理论知识，培养解决问题的能力，并且提高对MySQL数据库的应用能力。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 5
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值