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

最新推荐文章于 2024-04-30 16:27:04 发布
最新推荐文章于 2024-04-30 16:27:04 发布
阅读量1.6w 收藏 514
点赞数 83
分类专栏： MySQL shell 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/hujieliang123/article/details/122443231
版权

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字，靓仔不容易，关注一下吧！**💖

liangzai2048
关注
  • 83
    点赞
  • 踩
  • 514
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 23
    评论
专栏目录
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
MySQL50道练习题.zip
04-13
MySQL50道练习题
mysql经典50题_大数据_mysql经典50题_mysql经典五十题_hive_
10-01
用于后端开发者sql语言练习，题目非常经典，部分题目含答案，均为本人填写。学习hadoop的大数据同学们也可也用来练习hive，同含部分hive答案。
MySQL经典33题，DQL语句硬核专项练习！，半路出家Java程序员看我轻松逆袭
eegrfw的博客
04-01 374
20、列出在部门"SALES"工作的员工的姓名,假定 不知道销售部的部门编号.24、列出薪金高于在部门 30 工作的所有员工的薪金的员工 姓名和薪金.部门名称.21、列出薪金高于公司平均薪金的所有员工,所在部门,上级 领导,雇员的工资等级.23、列出薪金等于部门 30 中员工的薪金的其他员工的姓名 和薪金.部门名称。18、列出所有"CLERK"(办事员)的姓名及其部门名称,部门 的人数.15、列出部门名称和这些部门的员工信息,同时列出那些没有 员工的部门.
MySQL练习题及答案-11页
06-01
MySQL练习题及答案
MySQL经典练习题+解题思路（一）
BoBooY的Java学习笔记
10-03 1299
在上一期中我们讲解了MySQL的入门知识点，但理论总还是要通过实践来印证，学了再多不练也是白费，这一期我们讲解MySQL的一些经典查询例题帮助大家巩固已学知识点
sql语句练习50题(Mysql版)
Coder-Wf
09-10 3679
表名和字段 –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基础篇 | 经典三十四道练习题
m0_61933976的博客
09-18 5613
关于MySQL的三十四道练习题，大佬们都会做吗
Mysql经典例题练习与总结
qq_42265608的博客
06-14 3122
Mysql练习题
MySQL之经典50道题
热门推荐
qq_40216188的博客
08-09 1万+
MySQL之经典50道题目一、创建数据表并插入数据 一、创建数据表并插入数据 1、学生表 Student(s_id,s_name,s_birth,s_sex) ：学生编号、姓名、年月、性别 -- 1、学生表 -- Student(s_id,s_name,s_birth,s_sex) ：学生编号、姓名、年月、性别 CREATE TABLE IF NOT EXISTS `Student` ( `s_id` VARCHAR (20), `s_name` VARCHAR (20) NOT NULL DEFA
MySQL学习：50道经典题目（含参考答案）(更新中）
weixin_42661635的博客
04-27 7427
首先根据course_id将成绩表分为两张表，筛选出" 1 "课程比" 2 "课程成绩高的学生，再与学生表关联。1. 查询" 1 "课程比" 2 "课程成绩高的学生的信息（学号、姓名、性别、出生日期）及课程分数。利用自连接，统计比当前分数高的分数有几个，排名就是几加1。使用case when语句，多个排序条件时用逗号分隔。利用课程数量，计算出所有课程数和每个学生的选课数量。先筛选与" 1 "号同学选课数量相同的同学，再剔除。10. 查询没有学全所有课程的同学的信息。选了" 1 "号同学没选的课的同学。
MySQL经典练习50题
08-08
MySQL经典题库50题，无答案。MySQL经典题库50题，无答案。
MySQL数据库练习题(50道).pdf
04-25
MySQL数据库练习题(50道).pdf
全网最详细的mysql安装配置教程.zip
01-30
mysql全网最详细的mysql安装配置教程.zip
eclipse连接mysql全网最详细教程
05-30
java
MySQL —— 表的基本操作
最新发布
学习C++的小陈同学
04-30 534
本篇整理了和表结构相关的各种基本操作（增删查改等等）
【MySQL】1.安装与配置
2301_79719531的博客
04-28 874
文件的安全性问题。文件不利于数据查询和管理。文件不利于存储海量数据。文件在程序中控制不方便。数据库存储介质：磁盘+内存。为了解决上述问题，专家们设计出更加利于管理数据的软件——数据库，他能更加有效的管理数据。数据库可以提供远程服务，即通过远程连接来使用数据库，因此也称为数据库服务器。
MySQL的数据备份和恢复
本科在读，致力于Linux/C++方向的学习，同时涉及数据结构与算法，操作系统，数据库mysql等等
04-28 695
本博客主要内容涉及到mysql数据库的备份和恢复
基于springboot+vue+Mysql的生鲜交易系统
老卓爱爪哇 的博客
04-29 1061
理员使用本系统涉到的功能主要有：首页，个人中心，用户管理，商家管理，生鲜信息管理，生鲜分类管理，广告信息管理，生鲜仓库管理，生鲜出库管理，管理等功能。商家使用本系统涉到的功能主要有：首页，个人中心，生鲜信息管理，广告信息管理，生鲜仓库管理，生鲜出库管理，订单管理等功能。用户主要包括首页、个人中心功能。
mysql经典50道练习题pdf
08-17
MySQL经典50道练习题pdf是一本经典的MySQL数据库练习题集合，对于想要提高MySQL数据库操作能力的人士来说是非常有价值的。 这本练习题集合涵盖了各种MySQL数据库操作方面的题目，包括数据库创建、表创建、数据插入、查询语句编写等等。练习题的难度从简单到复杂逐渐增加，旨在帮助读者逐步提升对MySQL数据库的理解和应用能力。 通过这本练习题集合，读者可以通过实际的操作来巩固和应用之前学到的理论知识，培养自己的解决问题的能力。此外，这本练习题集合还提供了详细的答案和解析，读者可以通过对比自己的答案来检验和纠正自己的错误。 通过完成这些练习题，读者可以更好地掌握MySQL数据库的各种操作技巧和用法。同时，通过与他人的交流和讨论，读者还可以学习到其他人解决问题的思路和方法。 总之，MySQL经典50道练习题pdf对于想要提高MySQL数据库操作能力的人来说是一本非常有价值的书籍。读者通过完成这些练习题，可以巩固自己的理论知识，培养解决问题的能力，并且提高对MySQL数据库的应用能力。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

liangzai2048

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值