软件系统开发定制MySQL经典50道练习题及全网最详细解析

MySQL练习

文章目录

50道经典SQL软件系统开发定制练习题全网最详细解析

软件系统开发定制数据表介绍

  • 1.学⽣表 Student(SId,Sname,Sage,Ssex)

    • SId 学⽣编号

    • Sname 学⽣姓名

    • Sage 出⽣年⽉

    • Ssex 学⽣性别

  • 2.课程表 Course(CId,Cname,TId)

    • CId 课程编号

    • Cname 课程名称

    • TId 教师编号

  • 3.教师表 Teacher(TId,Tname)

    • TId 教师编号

    • Tname 教师姓名

  • 4.成绩表 SC(SId,CId,score)

    • SId 学⽣编号

    • CId 课程编号

    • score 分数

建表语句

  • 学⽣表 Student

    create table Student(    SId varchar(10),    Sname varchar(10),    Sage datetime,    Ssex varchar(10)); 
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

  • 课程表 Course

    create table Course(    CId varchar(10),    Cname nvarchar(10),    TId varchar(10));
    • 1
    • 2
    • 3
    • 4
    • 5

  • 教师表 Teacher

    create table Teacher(    TId varchar(10),    Tname varchar(10));
    • 1
    • 2
    • 3
    • 4

  • 成绩表 SC

    create table SC(    SId varchar(10),    CId varchar(10),    score decimal(18,1));
    • 1
    • 2
    • 3
    • 4
    • 5

插入数据

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

  • 学⽣表 Student

    -- 学生表 Student-- 学生表 Studentinsert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'); insert into Student values('03' , '孙风' , '1990-12-20' , '男'); insert into Student values('04' , '李云' , '1990-12-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '女'); insert into Student values('06' , '吴兰' , '1992-01-01' , '女'); insert into Student values('07' , '郑竹' , '1989-01-01' , '女'); insert into Student values('09' , '张三' , '2017-12-20' , '女'); insert into Student values('10' , '李四' , '2017-12-25' , '女'); insert into Student values('11' , '李四' , '2012-06-06' , '女'); insert into Student values('12' , '赵六' , '2013-06-13' , '女'); insert into Student values('13' , '孙七' , '2014-06-01' , '女'); 
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

  • 课程表 Course

    -- 科⽬表 Course insert into Course values('01' , '语文' , '02'); insert into Course values('02' , '数学' , '01'); insert into Course values('03' , '英语' , '03');
    • 1
    • 2
    • 3
    • 4

  • 教师表 Teacher

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

  • 成绩表 SC

    -- 成绩表 SC insert into SC values('01' , '01' , 80); insert into SC values('01' , '02' , 90); insert into SC values('01' , '03' , 99); insert into SC values('02' , '01' , 70); insert into SC values('02' , '02' , 60); insert into SC values('02' , '03' , 80); insert into SC values('03' , '01' , 80); insert into SC values('03' , '02' , 80); insert into SC values('03' , '03' , 80); insert into SC values('04' , '01' , 50); insert into SC values('04' , '02' , 30); insert into SC values('04' , '03' , 20); insert into SC values('05' , '01' , 76); insert into SC values('05' , '02' , 87); insert into SC values('06' , '01' , 31); insert into SC values('06' , '03' , 34); insert into SC values('07' , '02' , 89); insert into SC values('07' , '03' , 98);
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

练习题目

1.查询" 01 “课程比” 02 "软件系统开发定制课程成绩高的学生的信软件系统开发定制息及课程分数

分析：
1、找出有01软件系统开发定制软件系统开发定制软件系统开发定制软件系统开发定制成绩的学生成绩信息
2、找出有02成绩的学生成绩信息
3、通过SId软件系统开发定制软件系统开发定制将两表取别名t1、t2软件系统开发定制软件系统开发定制进行左连接
4、加上满足01‘语文’ > 02’数学’的条件

  • 找出有01成绩的学生成绩信息
SELECT * FROM SC WHERE CId='01';
  • 1
  • 找出有02成绩的学生成绩信息
SELECT * FROM SC WHERE CId='02';
  • 1
  • 通过SId将两表取别名t1、t2进行左连接
SELECT t1.SId,       t1.CId,       t1.score as '语文',       t2.score as '数学' FROM (SELECT        SId,       CId,       score   FROM SC    WHERE CId='01') t1LEFT JOIN(SELECT        SId,       CId,       score   FROM SC    WHERE CId='02') t2ON t1.SId=t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 加上满足01‘语文’ > 02’数学’的条件
SELECT t1.SId,       t1.CId,       t1.score as '语文',       t2.score as '数学' FROM (SELECT        SId,       CId,       score    FROM SC    WHERE CId='01') t1LEFT JOIN(SELECT        SId,       CId,       score    FROM SC    WHERE CId='02') t2ON t1.SId=t2.SIdWHERE t1.score > t2.score;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 软件系统开发定制最后将上面的表作为一个子表tt1软件系统开发定制将我们想要查询的表关联起来，软件系统开发定制取出想要查询的字段
SELECT  tt1.SId        ,tt2.Sname        ,tt3.CId        ,tt3.scoreFROM (    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.SIdJOIN SC tt3 ON tt1.SId = tt3.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
2.软件系统开发定制查询同时存在" 01 “课程和” 02 "软件系统开发定制软件系统开发定制课程的情况
分析：     软件系统开发定制软件系统开发定制满足条件的SC表中：       1、软件系统开发定制软件系统开发定制软件系统开发定制软件系统开发定制软件系统开发定制软件系统开发定制筛选出课程号为01软件系统开发定制软件系统开发定制软件系统开发定制软件系统开发定制软件系统开发定制软件系统开发定制的全部信息 AS命名为 t1       2、筛选出课程号为02的全部信息 AS命名为 t2       3、使用join软件系统开发定制软件系统开发定制连接取出同时存在01课程和02课程的SId
  • 1
  • 2
  • 3
  • 4
  • 5
  • 筛选出课程号为01的全部信息
SELECT SId FROM SC WHERE CId = '01';
  • 1
  • 筛选出课程号为02的全部信息
SELECT SId FROM SC WHERE CId = '02';
  • 1
  • 使用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 t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
3.查询存在" 01 “软件系统开发定制课程但可能不存在” 02 "课程的情况(软件系统开发定制不存在时显示为 null )
分析：     满足条件的SC表中：       1、筛选出课程号为01的全部信息 AS命名为 t1       2、筛选出课程号为02的全部信息 AS命名为 t2       3、左连接
  • 1
  • 2
  • 3
  • 4
  • 5
  • 筛选出课程号为01的全部信息
SELECT SId,CId,score FROM SC WHERE CId = '01';
  • 1
  • 筛选出课程号为02的全部信息
SELECT SId,CId,score FROM SC WHERE CId = '02';
  • 1
  • 左连接
SELECT t1.SId       ,t1.CId       ,t1.score       ,t2.CId AS t2CId       ,t2.score AS t2ScoreFROM(    SELECT SId           ,CId           ,score     FROM SC     WHERE CId = '01') t1 LEFT JOIN(    SELECT SId           ,CId           ,score     FROM SC     WHERE CId = '02') t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
4.查询不存在" 01 “课程但存在” 02 "课程的情况
分析：     满足条件的SC表中：       1、筛选出课程号为01的全部信息 AS命名为 t1       2、筛选出课程号为02的全部信息 AS命名为 t2       3、右连接
  • 1
  • 2
  • 3
  • 4
  • 5
  • 筛选出课程号为01的全部信息
SELECT SId,CId,score FROM SC WHERE CId = '01';
  • 1
  • 筛选出课程号为02的全部信息
SELECT SId,CId,score FROM SC WHERE CId = '02';
  • 1
  • 右连接
SELECT t1.SId       ,t1.CId       ,t1.score       ,t2.CId AS t2CId       ,t2.score AS t2ScoreFROM(    SELECT SId           ,CId           ,score     FROM SC     WHERE CId = '01') t1 RIGHT JOIN(    SELECT SId           ,CId           ,score     FROM SC     WHERE CId = '02') t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 再将平均分作为表t1与Student表t2做连接得出结果
SELECT t1.SId       ,t2.Sname       ,t1.avg_scoreFROM(SELECT SId       ,ROUND(AVG(score),2)AS avg_score  FROM SC  GROUP BY SId  HAVING avg_score>=60)t1 JOIN Student t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
6.查询在 SC 表存在成绩的学生信息

分析：
1、首先DISTINCTSC表的SId的数据
2、在将上表作为表t1与Student表t2连接查询出学生信息

  • 首先DISTINCTSC表的SId的数据
SELECT  DISTINCT SId  FROM SC;
  • 1
  • 2
  • 3
  • 在将上表作为表t1与Student表t2连接查询出学生信息
SELECT t1.SId       ,t2.Sname FROM(    SELECT     DISTINCT SId     FROM SC)t1 JOIN Student t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 在将上表作为表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 t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
8.查询「李」姓老师的数量

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

  • 直接使用COUNT(*)统计LIKE模糊查询查询「李」姓⽼师的数量
SELECT COUNT(*) FROM Teacher WHERE Teacher.Tname LIKE ('李%');
  • 1
  • 2
  • 3
  • 4
9.查询学过「张三」老师授课的同学的信息

分析：
1、首先查询张三老师的TId
2、然后查询张三老师的授课信息CId
3、再查询学过张三老师授课的学生SId
4、最后将学过张三老师授课的学生SId作为表t1和Student表t2连接查询出学生信息

  • 首先查询张三老师的TId
SELECT TId FROM Teacher WHERE Tname = '张三';
  • 1
  • 2
  • 3
  • 然后查询张三老师的授课信息CId
SELECT CId FROM Course WHERE TId =(           SELECT TId            FROM Teacher            WHERE Tname = '张三');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 再查询学过张三老师授课的学生SId
SELECT SId FROM SC WHERE CId = (      SELECT CId       FROM Course       WHERE TId =(            SELECT TId                 FROM Teacher                 WHERE Tname = '张三'      ));
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 最后将学过张三老师授课的学生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 t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
10.查询没有学全所有课程的同学的信息

分析：
1、先查询所有课程信息
2、然后将所有课程信息作为表t1与Student表t2内连接查询出同学的信息
3、查询所有课程信息的数量
4、最后将上表作为tt1表对课程信息进行筛选小于课程总数的查询出没有学全所有课程的同学的信息

  • 先查询所有课程信息
SELECT SId       ,CId FROM SC;
  • 1
  • 2
  • 3
  • 然后将所有课程信息作为表t1与Student表t2内连接查询出同学的信息
SELECT t1.SId       ,t1.SId       ,t2.SnameFROM(    SELECT SId          ,CId FROM SC) t1 JOIN Student t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 查询所有课程信息的数量
SELECT count(*) FROM Course;
  • 1
  • 2
  • 最后将上表作为tt1表对课程信息进行筛选小于课程总数的查询出没有学全所有课程的同学的信息
SELECT  tt1.SId        ,tt1.SName        ,count(tt1.CId) as cntFROM (    SELECT  t1.SId            ,t1.CId            ,t2.SName    FROM SC t1 JOIN Student t2    ON t1.SId = t2.SID) tt1 GROUP BY tt1.SId,tt1.SNameHaving cnt < (SELECT  count(*) FROM Course);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
11.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

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

  • 首先查询学号为01同学所学的课程号
SELECT CId FROM SC WHERE SId = '01';
  • 1
  • 2
  • 3
  • 然后查询至少有一门课与01同学所学相同的学生编号
SELECT t2.SId       ,COUNT(t2.SId) FROM(    SELECT CId     FROM SC     WHERE SId = '01')t1 JOIN SC t2ON t1.CId = t2.CIdGROUP BY t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 将上表的结果作为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 t2ON t1.CId = t2.CIdGROUP BY t2.SId) tt1 LEFT JOIN Student tt2ON tt1.SId = tt2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
解法2

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

  • 首先查询学号为01同学所学的课程号
SELECT CId FROM SC WHERE SId = '01';
  • 1
  • 2
  • 3
  • 然后再对课程号不是01的所有学生的课程信息进行筛选
SELECT DISTINCT SIdFROM SC WHERE SId != '01' AND CId IN(        SELECT CId         FROM SC         WHERE SId = '01');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 最后将SC表作为t1表与Student表t2表进行关联，找到学生信息
SELECT DISTINCT t1.SId                ,t2.SnameFROM     SC t1 JOIN Student t2    ON t1.SId = t2.SId    WHERE t1.SId != '01'     AND CId IN(              SELECT CId               FROM SC               WHERE SId = '01'         );
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
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';
  • 1
  • 2
  • 3
  • 4
  • 然后查询出学号不为01的课程信息
SELECT SId       ,CId FROM SC WHERE SId!='01';
  • 1
  • 2
  • 3
  • 4
  • 再将上面两个表分别作为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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 然后将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');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 最后将上表作为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 ttt2ON ttt1.SId = ttt2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
13.查询没学过"张三"老师讲授的任一门课程的学生姓名

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

  • 首先查询出张三老师教授的课程
SELECT TId FROM Teacher WHERE Tname = '张三';
  • 1
  • 2
  • 3
  • 然后查询出张三老师讲授的任意一门的课程号
SELECT CId FROM Course WHERE TId = (      SELECT TId       FROM Teacher       WHERE Tname = '张三');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 再查询出张三老师讲授的任意一门的课程号对应的SId
SELECT SId FROM SC WHERE CId in(  SELECT CId   FROM Course   WHERE TId = (    SELECT TId     FROM Teacher     WHERE Tname = '张三'));
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 最后查询出没学过"张三"老师讲授的任一门课程的学生姓名
SELECT SId      ,Sname FROM StudentWHERE SId NOT IN (    SELECT SId     FROM SC     WHERE CId in(      SELECT CId       FROM Course       WHERE TId = (        SELECT TId         FROM Teacher         WHERE Tname = '张三')    ));
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

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

  • 首先查询不及格同学的课程的学号和课程号
SELECT SId       ,CId       ,scoreFROM SC WHERE score < 60;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 再以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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 最后将上表作为表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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
15.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

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

  • 首先先将SC表t1与Student表t2进行关联
SELECT t2.SId       ,t2.Sname       ,t1.score FROMSC t1 JOIN Student t2 ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 最后检索出01课程分数小于60，按分数降序排列的学生信息
SELECT t2.SId       ,t2.Sname       ,t1.score FROMSC t1 JOIN Student t2 ON t1.SId = t2.SIdAND t1.CId = '01' AND t1.score < 60ORDER BY t1.score DESC;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
16.按平均成绩从⾼到低显示所有学生的所有课程的成绩以及平均成绩

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

  • 首先先查出’01’语文、'02’数学、'03’英语课程号所对应的成绩
SELECT SId,score FROM SC WHERE SId = '01';
  • 1
SELECT SId,score FROM SC WHERE SId = '02';
  • 1
SELECT SId,score FROM SC WHERE SId = '03';
  • 1
  • 然后查出所有课程的平均成绩
SELECT SId       ,AVG(score) as avg_socre FROM SC GROUP BY SId;
  • 1
  • 2
  • 3
  • 4
  • 因为有语文、数学、英语三门课程所有要对平均分进行分组
SELECT t1.SId       ,AVG(t1.score) as avg_score FROMSC t1 GROUP BY t1.SId;
  • 1
  • 2
  • 3
  • 4
  • 最后将分组后的表作为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) tt1LEFT JOIN (select SId,score from SC where CId = '01') tt2 on tt1.SId = tt2.SIdLEFT JOIN (select SId,score from SC where CId = '02') tt3 on tt1.SId = tt3.SIdLEFT JOIN (select SId,score from SC where CId = '03') tt4 on tt1.SId = tt4.SIdORDER BY tt1.avg_score DESC;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
17.查询各科成绩最⾼分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最⾼分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
  • 首先先将SC表和Course进行关联并分组
SELECT t1.CId       ,t2.Cname FROMSC t1 JOIN Course t2ON t1.CId = t2.CIdGROUP BY t1.CId,t2.Cname;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 然后分别计算出最高分、最低分、平均分、及格率、中等率、优良率、优秀率，并将查询结果按人数降序排列，若人数相同，按课程号升序排列
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 '人数'FROMSC t1 JOIN Course t2ON t1.CId = t2.CIdGROUP BY t1.CId,t2.Cname;ORDER BY '人数' DESC,CId ASC;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
18.按各科平均成绩进行排序，并显示排名

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

  • 首先查询出各科的平均分并进行排序
SELECT CId       ,AVG(score) avg_score FROM SC GROUP BY CId ORDER BY avg_score DESC;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 最后定义一个变量@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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
19.按各科平均成绩进行排序，并显示排名，重复时不保留名次空缺

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

  • 首先查询出各科的平均分并进行排序
SELECT CId       ,AVG(score) avg_score FROM SC GROUP BY CId ORDER BY avg_score DESC;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 最后定义一个变量@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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

三种常见的排名

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_numberfrom (    SELECT  SId            ,CId            ,score    from SC    order by score desc) t1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

DENSE_RANK

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

SET @i := 0;SET @p := 0;SET @q := 0;SELECT  t1.SId        ,t1.CId        ,t1.score        ,@p := t1.score        ,if(@p=@q,@i,@i := @i+1) as dense_rank        ,@q :=@pfrom (    SELECT  SId            ,CId            ,score    from SC    order by score desc) t1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16

RANK

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

SET @i := 0;SET @j := 0;SET @p := 0;SET @q := 0;SELECT  t1.SId        ,t1.CId        ,t1.score        ,@j := @j + 1        ,@p := t1.score        ,if(@p=@q,@i,@i := @j) as rank        ,@q :=@pfrom (    SELECT  SId            ,CId            ,score    from SC    order by score desc) t1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

进行分组排名

ROW_NUMBER

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

SET @i := 0;SET @p := 0;SET @q := 0;SELECT  tt1.SId        ,tt2.SName        ,tt1.CId        ,tt1.score        ,tt1.rnfrom (    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.SIdorder by tt1.CId,tt1.rn;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24

DENSE_RANK

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

SET @i := 0;SET @p := 0;SET @q := 0;SET @j := 0;SET @k := 0;SELECT  tt1.SId        ,tt2.SName        ,tt1.CId        ,tt1.score        ,tt1.rnfrom (    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.SIdorder by tt1.CId,tt1.rn;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28

RANK

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

SET @i := 0;SET @p := 0;SET @q := 0;SET @j := 0;SET @k := 0;SET @m := 1;SELECT  tt1.SId        ,tt2.SName        ,tt1.CId        ,tt1.score        ,tt1.rnfrom (    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.SIdorder by tt1.CId,tt1.rn;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
20.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

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

  • 首先先查询总成绩，以SId进行分组，并进行排序
SELECT SId       ,SUM(score) AS sum_score FROM SC GROUP BY SId ORDER BY sum_score DESC;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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

SET @i :=0;SET @j :=0;SET @p :=0;SET @q :=0;SELECT t1.SId       ,t1.sum_score AS '总分'       ,@j :=@j + 1       ,@p :=t1.sum_score       ,IF(@p=@q,@j,@i :=@j) AS '排名'       ,@q :=@p FROM(  SELECT SId         ,SUM(score)   AS sum_score   FROM SC   GROUP BY SId   ORDER BY sum_score DESC) t1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
21.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

122456

  • 首先先查询总成绩，以SId进行分组，并进行排序
SELECT SId       ,SUM(score) AS sum_score FROM SC GROUP BY SId ORDER BY sum_score DESC;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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

SET @i :=0;SET @p :=0;SET @q :=0;SELECT  t1.SId        ,t1.sum_score AS '总分'        ,@p := t1.sum_score         ,if(@p=@q,@i,@i := @i+1) as '排名'        ,@q :=@pFROM(  SELECT SId         ,SUM(score)   AS sum_score   FROM SC   GROUP BY SId   ORDER BY sum_score DESC) t1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85），[85-70），[70-60），[60-0）及所占百分

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

  • 首先将SC表t1与Course表t2进行关联，并以CId进行分组
SELECT t1.CId       ,t2.Cname FROM    SC t1 JOIN Course t2    ON t1.CId = t2.CId GROUP BY t1.CId,t2.Cname;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 最后计算出各分段分数，显示出来
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;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
23.查询各科成绩前三名的记录

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

  • 首先查出各科成绩并进行排序
SELECT SId       ,CId       ,score FROM SC ORDER BY CId,score DESC;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 然后将上表作为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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 最后将上表作为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 tt2ON tt1.rn<=3 AND tt1.SId = tt2.SIdORDER BY tt1.CId,rn;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
24.查询每门课程被选修的学生数

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

  • 以CId进行分组查询出每门课程被选修的学生数
SELECT CId       ,COUNT(CId) AS cnt FROM SC GROUP BY CId;
  • 1
  • 2
  • 3
  • 4
  • 5
25.查询出只选修两门课程的学生学号和姓名

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

  • 首先查询出选修两门课程的学生课程和学号
SELECT SId       ,COUNT(CId) AS cnt FROM SC GROUP BY SId HAVING cnt = 2;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 然后将上表作为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 t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
26.查询男生、女生人数

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

  • 以性别分组分别求出男生、女生的人数
SELECT Ssex,COUNT(1) FROM Student GROUP BY Ssex;
  • 1
  • 2
  • 3
  • 4
27.查询名字中含有「风」字的学生信息

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

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

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

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

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

  • 以学生姓名，性别分组，然后count出同名同性的人数
SELECT Sname       ,Ssex       ,COUNT(1) AS cnt FROM Student GROUP BY Sname,Ssex HAVING cnt > 1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
29.查询 1990 年出生的学生名单

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

  • 使用LIKE模糊查询
SELECT * FROM Student WHERE Sage LIKE '1990%';
  • 1
  • 2
  • 3
  • 使用BETWEEN…AND…查询
SELECT * FROM Student WHERE Sage BETWEEN '1990-1-1' AND '1990-12-31';
  • 1
  • 2
  • 3
30.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

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

  • 将SC表与Course表做关联查询出每门课程的平均分
SELECT  SC.CId        ,Course.Cname        ,AVG(SC.score) AS avg_scoreFROM SC JOIN Course ON SC.CId = Course.CId GROUP BY SC.CId,Course.Cname;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 最后将上表按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT  SC.CId        ,Course.Cname        ,AVG(SC.score) AS avg_scoreFROM SC JOIN Course ON SC.CId = Course.CId GROUP BY SC.CId,Course.CnameORDER BY avg_score DESC,SC.CId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
31.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

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

  • 首先查询出成绩大于等于85的平均成绩的课程信息
SELECT SId       ,ROUND(AVG(score),2) AS avg_score FROM SC GROUP BY SIdHAVING avg_score>=85;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 然后将上表作为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 t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

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

  • 首先查询出课程名为数学的CId
SELECT  CIdFROM Course WHERE CName = '数学';
  • 1
  • 2
  • 3
  • 然后再将上表作为字表查询出课程名为数学，且分数低于60的课程信息
SELECT SId       ,Score FROM SC WHERE CId =(      SELECT  CId      FROM Course       WHERE CName = '数学')AND score<60;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 最后将上表作为表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 t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
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 SCGROUP BY SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 然后将Student表t1与上表作为表t2做左连接，取出学生姓名
SELECT t1.Sname       ,t2.语文成绩       ,t2.数学成绩       ,t2.英语成绩 FROMStudent 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) t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
34.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

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

  • 首先查询出成绩在70分以上的课程信息
SELECT  SId        ,CId        ,scoreFROM SCWHERE score>70;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 然后再将上表作为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 t2ON t1.SId = t2. SIdJOIN Course t3ON t1.CId = t3.CId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
35.查询不及格的课程

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

  • 首先查询出成绩低于60分的课程信息
SELECT DISTINCT CId                ,score FROM SC WHERE score < 60;
  • 1
  • 2
  • 3
  • 4

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

SELECT  DISTINCT         t2.Sname        ,t1.CId        ,t3.CName        ,t1.scoreFROM(    SELECT SId           ,CId           ,score    FROM SC    WHERE score<60) t1 JOIN Student t2ON t1.SId = t2.SIdJOIN Course t3ON t1.CId = t3.CId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
36.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

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

  • 首先查询出课程编号为01且课程成绩在80分以上的课程信息
SELECT SId       ,scoreFROM SCWHERE CId = '01' AND score >= 80;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 然后将上表作为表t1与Student表t2做关联，取出学生的学号和姓名
SELECT t1.SId       ,t2.Sname FROM(  SELECT SId         ,score  FROM SC  WHERE CId = '01'   AND score >= 80) t1 JOIN Student t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
37.求每门课程的学生人数
  • 将SC表t1与Course表t2做关联，并按CId分组，取出课程号、课程名称、每门课程
SELECT t1.CId       ,t2.cname       ,COUNT(t1.CId) AS '人数'FROM SC t1 JOIN Course t2ON t1.CId = t2.CIdGROUP BY t1.CId,t2.Cname;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
38.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

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

  • 首先查询张三老师所授的课程TId
SELECT TId FROM Teacher WHERE Tname = '张三';
  • 1
  • 2
  • 3
  • 然后在Course表中查询张三老师所授课程的课程编号CId
SELECT CId FROM Course WHERE TId = (   SELECT TId    FROM Teacher    WHERE Tname = '张三');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 再在SC表查询，成绩不重复的课程信息
SELECT SId       ,CId       ,score FROM SCWHERE CId IN (   SELECT CId    FROM Course    WHERE TId = (     SELECT TId      FROM Teacher      WHERE Tname = '张三'   ));
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 然后将成绩降序排列取出第一个成绩LIMIT 1，即是最高成绩
SELECT SId       ,CId       ,score FROM SCWHERE CId IN (   SELECT CId    FROM Course    WHERE TId = (     SELECT TId      FROM Teacher      WHERE Tname = '张三'   )) ORDER BY scoreLIMIT 1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 最后将上表作为t1表与Student表t2连接，取出成绩最高的学生信息
SELECT t1.SId       ,t2.Sname       ,t1.CId       ,t1.score FROM(  SELECT SId         ,CId         ,score FROM SCWHERE CId IN (   SELECT CId    FROM Course    WHERE TId = (     SELECT TId      FROM Teacher      WHERE Tname = '张三'   )) ORDER BY scoreLIMIT 1) t1 JOIN Student t2ON t1.SId = t2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
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 = '张三';
  • 1
  • 2
  • 3
  • 然后在Course表中查询张三老师所授课程的课程编号CId
SELECT CId FROM Course WHERE TId = (   SELECT TId    FROM Teacher    WHERE Tname = '张三');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 再在SC表查询，成绩不重复的课程信息，并降序排列
SELECT SId       ,CId       ,score FROM SCWHERE CId IN (   SELECT CId    FROM Course    WHERE TId = (     SELECT TId      FROM Teacher      WHERE Tname = '张三'   )) ORDER BY score DESC;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 将上表作为表t1再定义@i、@p、@q三个变量@i控制排序，@p、@q控制分数重复保留名次
SET @i := 0;SET @p := 0;SET @q := 0;SELECT  t1.SId        ,t1.CId        ,t1.score        ,@p := t1.score        ,IF(@p=@q,@i,@i := @i+1) AS dense_rank        ,@q :=@pFROM (  SELECT SId         ,CId         ,score   FROM SC  WHERE CId IN (   SELECT CId    FROM Course    WHERE TId = (     SELECT TId      FROM Teacher      WHERE Tname = '张三'   )) ORDER BY score DESC) t1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 然后将上表作为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 tt2ON tt1.dense_rank = 1 AND tt1.SId = tt2.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

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

  • 首先现将SC表t1与自己SC表t2进行关联，取出成绩相同的学生编号、课程编号、学生成绩
SELECT t1.CId       ,t2.scoreFROM SC t1 JOIN SC t2ON t1.score = t2.score AND t1.CId != t2.CId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 然后再与Student表t3进行关联取出学生信息
SELECT t3.Sname       ,t1.CId       ,t2.scoreFROM SC t1 JOIN SC t2ON t1.score = t2.score AND t1.CId != t2.CIdJOIN Student t3ON t1.SId = t3.SId;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
41.查询每门课程成绩最好的前两名

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

  • 首先在SC表中查询出每门课程的成绩并按CId分数降序排列
SELECT SId        ,CId        ,scoreFROM SCORDER BY CId,score DESC;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 定义函数@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.rnFROM (    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.SIdORDER BY tt1.CId,rn;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
42.统计每门课程的学生选修人数（超过 5 人的课程才统计）。

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

  • 以CId进行分组，统计出超过5人的课程信息
SELECT CId       ,COUNT(SId) AS '选课人数'FROM SC GROUP BY CIdHAVING 选课人数 > 5;
  • 1
  • 2
  • 3
  • 4
  • 5
43.检索至少选修两门课程的学生学号

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

  • 以SId进行分组，统计出至少选修两门课程的学生学号
SELECT SId       ,COUNT(CID) AS '选课数量'FROM SC GROUP BY SIdHAVING 选课数量 >= 2;
  • 1
  • 2
  • 3
  • 4
  • 5
44.查询选修了全部课程的学生信息

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

  • 首先查询出总课程属性作为表t3
SELECT COUNT(t3.CId) FROM Course t3;
  • 1
  • 2
  • 然后将Student表t1与SC表t2进行关联，以SId、Sname进行分组，用表t3筛选出选修了全部课程的学生信息
SELECT  t1.SId        ,t1.Sname        ,COUNT(t2.CId) AS '选课数量'FROM Student t1 JOIN SC t2ON t2.SId = t1.SIdGROUP BY t1.SId,t1.SnameHAVING COUNT(t2.CId) = (   SELECT COUNT(t3.CId)    FROM Course t3);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
45.查询各学生的年龄，只按年份来算

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

SELECT YEAR(now()) - date_format(Sage,'%Y') FROM Student;
  • 1
46.按照出生日期来算，当前月日 < 出生年月的月日，则年龄减一

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

  • 首先先在Student表中查询出出生日期、当前月日、出生年月的月日
SELECT SId        ,Sname       ,(YEAR(now()) - date_format(Sage,'%Y') ) AS age       ,date_format(Sage,'%m-%d') AS month_day       ,date_format(now(),'%m-%d') AS now_month_dayFROM Student;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 然后将上表作为表t1，计算出当前月日 < 出生年月的月日，则年龄减一
SELECT SId        ,SName       ,CASE WHEN now_month_day<month_day THEN age-1 ELSE age END AS new_age       ,ageFROM(  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_dayFROM Student) t1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
47.查询本周过生日的学生

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

SELECT SId       ,SName       ,Sage       ,WEEK(Sage)FROM StudentWHERE WEEK(Sage) = WEEK(now());
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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

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

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

SELECT SId       ,SName       ,Sage       ,WEEK(Sage)FROM StudentWHERE WEEK(Sage) = WEEK(date_add(now(),INTERVAL 1 WEEK));
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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

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

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

SELECT SId       ,SName       ,Sage       ,MONTH(Sage)FROM StudentWHERE MONTH(Sage) = MONTH(now());
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
50.查询下月过生日的学生

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

SELECT SId       ,SName       ,Sage       ,MONTH(Sage)FROM StudentWHERE MONTH(Sage) = MONTH(date_add(now(),INTERVAL 1 MONTH));
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

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

week()函数参见下列网址

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

date_format()函数参见下列网址

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

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

网站建设定制开发 软件系统开发定制 定制软件开发 软件开发定制 定制app开发 app开发定制 app开发定制公司 电商商城定制开发 定制小程序开发 定制开发小程序 客户管理系统开发定制 定制网站 定制开发 crm开发定制 开发公司 小程序开发定制 定制软件 收款定制开发 企业网站定制开发 定制化开发 android系统定制开发 定制小程序开发费用 定制设计 专注app软件定制开发 软件开发定制定制 知名网站建设定制 软件定制开发供应商 应用系统定制开发 软件系统定制开发 企业管理系统定制开发 系统定制开发