MySQL经典50题

偏执的太偏执、 2024-03-31 15:58 77阅读 0赞

**目录**

建表&添加数据

表结构分析图

题目

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

6、查询"李"姓老师的数量

7、查询学过"张三"老师授课的同学的信息

8、查询没学过"张三"老师授课的同学的信息

9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

11、查询没有学全所有课程的同学的信息

12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

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

14、查询没学过"张三"老师讲授的任一门课程的学生姓名

15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

16、检索"01"课程分数小于60，按分数降序排列的学生信息

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

18、查询各科成绩最高分、最低分和平均分，及格率，中等率，优良率，优秀率

19、按各科成绩进行排序，并显示排名

20、查询学生的总成绩并进行排名

21、查询不同老师所教不同课程平均分从高到低显示

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

23、统计各科成绩各分数段人数：课程编号,课程名称, \[100-85\] , \[85-70\] , \[70-60\] , \[0-60\] 及所占百分比

24、查询学生平均成绩及其名次

25、查询各科成绩前三名的记录

26、查询每门课程被选修的学生数

27、查询出只有两门课程的全部学生的学号和姓名

28、查询男生、女生人数

29、查询名字中含有"风"字的学生信息

30、查询同名同性学生名单，并统计同名人数

31、查询1990年出生的学生名单

32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

34、查询课程名称为"数学"，且分数低于60的学生姓名和分数

35、查询所有学生的课程及分数情况

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

37、查询不及格的课程

38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

39、求每门课程的学生人数

40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

42、查询每门功课成绩最好的前两名

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

44、检索至少选修两门课程的学生学号

45、查询选修了全部课程的学生信息

46、查询各学生的年龄

47、查询本周过生日的学生

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

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

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

--------------------

### 建表&添加数据 ###

/*
    创建表
    --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 分数
    */
    
    
    CREATE TABLE student 
    (SID VARCHAR(10),Sname VARCHAR(20),Sage DATETIME,Ssex VARCHAR(10));
    # 插入数据
    INSERT INTO Student VALUES('01' , '赵雷' , '1990-01-01' , '男');
    INSERT INTO Student VALUES('02' , '钱电' , '1990-12-21' , '男');
    INSERT INTO Student VALUES('03' , '孙风' , '1990-05-20' , '男');
    INSERT INTO Student VALUES('04' , '李云' , '1990-08-06' , '男');
    INSERT INTO Student VALUES('05' , '周梅' , '1991-12-01' , '女');
    INSERT INTO Student VALUES('06' , '吴兰' , '1992-03-01' , '女');
    INSERT INTO Student VALUES('07' , '郑竹' , '1989-07-01' , '女');
    INSERT INTO Student VALUES('08' , '王菊' , '1990-01-20' , '女');
    
    CREATE TABLE Course(CID VARCHAR(10),Cname VARCHAR(10),TID VARCHAR(10));
    INSERT INTO Course VALUES('01' , '语文' , '02');
    INSERT INTO Course VALUES('02' , '数学' , '01');
    INSERT INTO Course VALUES('03' , '英语' , '03');
    
    CREATE TABLE Teacher(TID VARCHAR(10),Tname VARCHAR(10));
    INSERT INTO Teacher VALUES('01' , '张三');
    INSERT INTO Teacher VALUES('02' , '李四');
    INSERT INTO Teacher VALUES('03' , '王五');
    
    CREATE TABLE SC(SID VARCHAR(10),CID VARCHAR(10),score DECIMAL(18,1));
    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);

### 表结构分析图 ###

![6639c19ce8a644cea883af384e23545b.png][]

### 题目 ###

#### 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 ####

#方法一
    select student.*, s1.score, s2.score
    from student
             join sc s1 on s1.sid = student.sid and s1.cid = '01'
             join sc s2 on s2.sid = student.sid and s2.cid = '02'
    where s1.score > s2.score;
    
    #方法二
    select s.*, sc1.score, sc2.score
    from sc sc1
             join sc sc2 on sc1.SID = sc2.SID and sc1.CID = '01' and sc2.CID = '02'
             left join student s on sc1.SID = s.SID
    having sc1.score > sc2.score;

输出结果：

![da80265108544e5cbe951ea4b117b850.png][]

#### 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 ####

# 方法一
    select student.*, s1.score, s2.score
    from student
             join sc s1 on s1.sid = student.sid and s1.cid = '01'
             join sc s2 on s2.sid = student.sid and s2.cid = '02'
    where s1.score < s2.score;
    # 方法二
    select s.*, sc1.score, sc2.score
    from sc sc1
             join sc sc2 on sc1.SID = sc2.SID and sc1.CID = '01' and sc2.CID = '02'
             left join student s on sc1.SID = s.SID
    having sc1.score < sc2.score;

输出结果：

![315fadb84ebc4a74a26278ad7ca4dd8a.png][]

#### 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 ####

select a.SID, a.Sname, avg(b.score) 平均成绩
    from Student a,
         sc b
    where a.SID = b.SID
    group by a.SID, a.Sname
    having 平均成绩 >= 60;

输出结果：

![7d0791c9dc144f62a97cd699552443ee.png][]

#### 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 ####

select a.SID, a.Sname, avg(b.score) 平均成绩
    from Student a
             left join sc b on a.SID = b.SID
    group by a.SID, a.Sname
    having 平均成绩 < 60;

输出结果：

![e21b2f70cdc440c8ae03ab29af7b1034.png][]

#### 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 ####

select a.SID 学生编号, a.Sname 学生姓名, count(b.CID) 选课总数, sum(score) 所有课程的总成绩
    from Student a
             left join SC b
                       on a.SID = b.SID
    group by a.SID, a.Sname;

输出结果：

![bccd1cf7a8654e02b3349342c00bbbfa.png][]

#### 6、查询"李"姓老师的数量 ####

select count(Tname) 李姓老师数量
    from teacher
    where Tname like '李%';

输出结果：

![34ba903f8bf5418c842cebb05ec446b1.png][]

#### 7、查询学过"张三"老师授课的同学的信息 ####

# 方法一
    select a.*
    from student a
             left join SC S on a.SID = S.SID
             left join Course C on S.CID = C.CID
             left join teacher t on C.TID = t.TID
    where t.Tname = '张三';
    
    # 方法二
    select *
    from student
    where SID in (select sid
                  from sc
                  where cid in (select distinct CID from course where TID = (select tid from teacher where Tname = '张三')));

输出结果：

![ecddd4ad49654467bf4cca5ab49ae8e5.png][]

#### 8、查询没学过"张三"老师授课的同学的信息 ####

select s.*
    from student s
    where s.SID not in (select s.SID
                        from student
                                 left join SC S on s.SID = S.SID
                                 left join Course C on S.CID = C.CID
                                 left join teacher t on C.TID = t.TID
                        where t.Tname = '张三');
    
    
    #方法二
    select s.*
    from student s
    where s.SID not in (select sid
                        from sc
                        where cid in
                              (select distinct CID from course where TID = (select tid from teacher where Tname = '张三')));

输出结果：

![2379b2921113421aa1f520e4a132dbbe.png][]

#### 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 ####

# 方法一
    select s.*
    from sc sc1
             join sc sc2 on sc1.sid = sc2.sid and sc1.CID = 01 and sc2.CID = 02
             left join student s on sc1.SID = s.SID;
    # 方法二
    select a.*
    from student a
    where a.SID in (select SID from SC where CID = '01')
      and a.SID in (select SID from SC where CID = '02');

输出结果：

![4fb904c9ce5643c489351068dc7f2791.png][]

#### 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 ####

# 方法一
    select a.*
    from student a
    where a.SID in (select SID from SC where CID = '01')
      and a.SID not in (select SID from SC where CID = '02');
    
    # 方法二
    select sc2.SID,sc1.CID,sc2.CID,s.*
    from sc sc1
              left join sc sc2 on sc1.sid = sc2.sid and sc1.CID = 01 and sc2.CID = 02
             left join student s on s.SID = sc1.SID
    having sc1.CID = 01 and sc2.CID is null;

输出结果：

![cd06cbba3b904e928c6d4ca70b7d2bd8.png][]

#### 11、查询没有学全所有课程的同学的信息 ####

#方法一
    select *
    from student
    where SID not in (
        select SID
        from SC
        group by SID
        having count(*) = (select count(*) from course)
    );
    
    #方法二
    select s.*, count(sc.CID) snum
    from sc
             right join student s on SC.SID = s.SID
    group by s.Sname,s.SID,s.Sage,s.Ssex
    having snum < (select COUNT(CID) num from course);

输出结果：![72182d470c414e39a3afe1da14b2fddc.png][]

#### 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息 ####

select distinct s.*
    from student s
             join SC a on s.SID = a.SID
    where s.SID != 01
      and a.CID in (
        select CID
        from student s
                 join SC a on s.SID = a.SID
        where s.SID = '01'
    );

输出结果：

![ada6494f497a4ac5b37ea223e2177497.png][]

#### 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 ####

#方法一
    select sc1.SID, s.*, count(sc1.CID) snum
    from sc sc1
             left join sc sc2 on sc1.CID = sc2.CID and sc1.SID != 01 and sc2.SID = 01
             left join student s on sc1.SID = s.SID
    where sc1.SID != 01
      and sc2.SID is not null
    group by sc1.SID, s.SID, s.Sname, s.Sage, s.Ssex
    having snum != (select count(CID) from sc where sid = '01');
    
    #方法二
    select s2.sid, student.*
    from sc s1
             join sc s2
                  on s1.cid = s2.cid and s1.sid = '01' and s2.sid != '01'
             join student on student.sid = s2.sid
    group by s2.sid, student.sname, student.Sage, student.Ssex
    having count(s2.sid) = (select count(*) from sc where sid = '01');

输出结果：

![55a69451f34347aba14f5bf690bee275.png][]

#### 14、查询没学过"张三"老师讲授的任一门课程的学生姓名 ####

输出结果：

![87483e76dd6443c093ac0885e815054b.png][]

#### 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 ####

select stu.sid, stu.sname, round(avg(sc.score), 2) as 平均分
    from student as stu
             join sc on stu.sid = sc.sid
    where sc.score < 60
    group by stu.sid, stu.sname
    having count(sc.cid) >= 2;

输出结果：

![e5f3cfe7954549908c95b25e7e7110e4.png][]

#### 16、检索"01"课程分数小于60，按分数降序排列的学生信息 ####

select distinct student.*, sc.score
    from student
             left join
         sc on student.SID = sc.SID
    where score < 60
      and sc.CID = '01'
    order by score desc;

输出结果：

![8c5a97eada6f405286f366ba58f73b95.png][]

#### 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 ####

select s.Sname,s.SID,
           ifnull(t.avgscpre,0) 平均分,
           sum(case when sc.CID = '01' then sc.score else 0 end) as 语文,
           sum(case when sc.CID = '02' then sc.score else 0 end) as 数学,
           sum(case when sc.CID = '03' then sc.score else 0 end) as 英语
    from (select sc.sid, avg(score) avgscpre from sc group by sc.sid order by avgscpre desc) t
             left join sc on t.SID = sc.SID
             right join student s on SC.SID = s.SID
    group by s.SID, t.avgscpre, s.Sname;

输出结果：

![31e04f85dd164fbc88f083073f1c004f.png][]

#### 18、查询各科成绩最高分、最低分和平均分，及格率，中等率，优良率，优秀率 ####

以如下形式显示：

课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select a.Cname,
           a.CID,
           max(b.score)                                                                   最高分,
           min(b.score)                                                                   最低分,
           avg(b.score)                                                                   平均分,
           sum(case when b.score >= 60 then 1 else 0 end) / count(b.cid)                  及格率,
           sum(case when b.score >= 70 and b.score < 80 then 1 else 0 end) / count(b.cid) 中等率,
           sum(case when b.score >= 80 and b.score < 90 then 1 else 0 end) / count(b.cid) 优良率,
           sum(case when b.score >= 90 then 1 else 0 end) / count(b.cid)                  优秀率
    from course a
       , sc b
    where a.CID = b.CID
    group by a.Cname, b.CID;

输出结果：

![38c922f356644bde9a3b9b344b4fbd6d.png][]

#### 19、按各科成绩进行排序，并显示排名 ####

select s.Sname, c.Cname, sc.score, row_number() over (partition by sc.cid order by score desc,sc.SID) as '排名'
    from sc
             left join student s on SC.SID = s.SID
             left join course c on SC.CID = c.CID;

输出结果：

![3a45755804004eeba259fc6e8c7d8f94.png][]

#### 20、查询学生的总成绩并进行排名 ####

select a.SID, a.Sname, sum(b.score) as zf
    from student a
             left join sc b on a.SID = b.SID
    group by a.SID, a.Sname
    order by zf;

输出结果：

![b4f2bb6d64f24a2a8873090edf52dd02.png][]

#### 21、查询不同老师所教不同课程平均分从高到低显示 ####

select t.Tname, c.Cname, avg(score) 平均分
    from sc
             left join course c on SC.CID = c.CID
             left join teacher t on c.TID = t.TID
    group by sc.CID, c.Cname, t.Tname
    order by 平均分 desc;

输出结果：

![0f9ad4bcf0544c6088049ef7b109a9e6.png][]

#### 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩 ####

select stu.*, cou.cname, score
    from student stu
             inner join
         ((select sid, cid, score from sc where cid = 01 order by score desc limit 1,2)
          UNION
          (select sid, cid, score from sc where cid = 02 order by score desc limit 1,2)
          UNION
          (select sid, cid, score from sc where cid = 03 order by score desc limit 1,2)) p on stu.sid = p.sid
             inner join course cou on p.cid = cou.cid
    order by cname, score desc;

输出结果：

![d4e4a9f5ac494a4b99b48d632c9265c4.png][]

#### 23、统计各科成绩各分数段人数：课程编号,课程名称, \[100-85\] , \[85-70\] , \[70-60\] , \[0-60\] 及所占百分比 ####

select a.CID,
           a.Cname,
           sum(case when b.score <= 60 then 1 else 0 end)                                       '60↓人数',
           sum(case when b.score >= 60 and b.score < 70 then 1 else 0 end)                      '70-60人数',
           sum(case when b.score >= 70 and b.score < 85 then 1 else 0 end)                      '85-70人数',
           sum(case when b.score >= 85 then 1 else 0 end)                                       '85↑人数',
           sum(case when b.score <= 60 then 1 else 0 end) / count(b.cid) * 100                  '60↓占比',
           sum(case when b.score >= 60 and b.score < 70 then 1 else 0 end) / count(b.cid) * 100 '70-60占比',
           sum(case when b.score >= 70 and b.score < 85 then 1 else 0 end) / count(b.cid) * 100 '85-70占比',
           sum(case when b.score >= 85 then 1 else 0 end) / count(b.cid) * 100                  '85↑占比'
    from course a
       , sc b
    where a.CID = b.CID
    group by a.Cname, b.CID;

输出结果：

![75940de022fd4481ae47a6795513db3e.png][]

#### 24、查询学生平均成绩及其名次 ####

select a.SID,
           a.Sname,
           round(ifnull(avg(b.score), 0), 2)               平均分,
           ROW_NUMBER() over (order by avg(b.score) desc ) 排名
    from student a
             left join sc b on a.SID = b.SID
    group by a.SID, a.Sname
    order by 平均分 desc;

输出结果：

![a3e418eb6dc04571a8901819e203b752.png][]

#### 25、查询各科成绩前三名的记录 ####

select stu.*, cou.cname, score
    from student stu
             inner join
         ((select sid, cid, score from sc where cid = 01 order by score desc limit 3)
          UNION
          (select sid, cid, score from sc where cid = 02 order by score desc limit 3)
          UNION
          (select sid, cid, score from sc where cid = 03 order by score desc limit 3)) p on stu.sid = p.sid
             inner join course cou on p.cid = cou.cid
    order by cname, score desc;

输出结果：

![0c5972702eb7459a9f0491e036c72aa1.png][]

#### 26、查询每门课程被选修的学生数 ####

select sc.cid, c.Cname, count(sc.cid) '选修人数'
    from sc
             left join course c on SC.CID = c.CID
    group by sc.cid, c.Cname;

输出结果：

![f5f85dd5d7ad4a6a836fbdd8dea5a8bc.png][]

#### 27、查询出只有两门课程的全部学生的学号和姓名 ####

select student.SID, student.Sname, count(s.SID) count
    from student
             left join SC S on student.SID = S.SID
    group by student.SID, student.Sname
    having count = 2;

输出结果：

![4d800843299b4cb089830cedad776fe9.png][]

#### 28、查询男生、女生人数 ####

select Ssex, count(*) 数量
    from student
    group by Ssex;

输出结果：

![eb0b263a4adf46a28f6e6d5e4fec9dee.png][]

#### 29、查询名字中含有"风"字的学生信息 ####

select  student.*
    from student 
    where Sname like '%风%';

输出结果：

![ab0c42c83a604d6bb1c0201c349d12c7.png][]

#### 30、查询同名同性学生名单，并统计同名人数 ####

select count(*) 同名同性人数
    from student s1
             join student s2
    where s1.Sname = s2.Sname
      and s1.Ssex = s2.Ssex
      and s1.SID != s2.SID;

输出结果：

**没有同名同性学生**

#### 31、查询1990年出生的学生名单 ####

**(注：Student表中Sage列的类型是datetime)**

select *
    from student
    where Sage like '1990%';

输出结果：

![b9d6fb924e294583b57bc726b3da33e4.png][]

#### 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 ####

select c.CID, c.Cname, avg(score) 平均分
    from sc
             left join course c on SC.CID = c.CID
    group by sc.CID, c.Cname
    order by 平均分 desc ,sc.CID asc ;

输出结果：

![f09cc32ac17248818181900fb9c34683.png][]

#### 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩 ####

select a.SID, a.Sname, avg(b.score) as 平均分
    from student a
             left join sc b on a.SID = b.SID
    group by a.SID, a.Sname
    having 平均分>=85
    order by 平均分;

输出结果：

![93e674be10674c5fbbe202d091163fda.png][]

#### 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数 ####

select c.Sname,b.score
    from course a
             left join sc b on a.CID = b.CID
             left join student c on b.SID = c.SID
    where a.Cname = '数学' and b.score<60;

输出结果：

![ccf0d933c0e7443a9faed080c8cc420e.png][]

#### 35、查询所有学生的课程及分数情况 ####

select a.Sname,c.Cname,b.score
    from student a
             left join SC b on a.SID = b.SID
             left join Course c on b.CID = c.CID;

输出结果：

![8b72ac4ca6904155b49582e5fde44a31.png][]

#### 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数 ####

select s.Sname,
           s.SID,
           sum(case when sc.CID = '01' then sc.score else 0 end) as 语文,
           sum(case when sc.CID = '02' then sc.score else 0 end) as 数学,
           sum(case when sc.CID = '03' then sc.score else 0 end) as 英语
    from (select sc.sid from sc group by sc.sid) t
             left join sc on t.SID = sc.SID
             right join student s on SC.SID = s.SID
    group by s.SID, s.Sname
    having 语文 >= 70
       and 数学 >= 70
       and 英语 >= 70;

输出结果：

![ce61e7a0f94f43e3a316783dcddbdf7e.png][]

#### 37、查询不及格的课程 ####

select a.*,c.Cname,b.score
    from student a
             left join SC b on a.SID = b.SID
             left join Course c on b.CID = c.CID
    where b.score<60;

输出结果：

![6fc9d0c7be294c979f062ffd1a7e9dc1.png][]

#### 38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名 ####

select a.SID, a.Sname, s.score
    from student a
             join SC S on a.SID = S.SID
        where s.CID = '01'
        and s.score >= 80;

输出结果：

![5c62b856135047d0ad9954b13724ddcd.png][]

#### 39、求每门课程的学生人数 ####

select c.Cname, count(sc.CID) 学生人数
    from sc
             left join course c on SC.CID = c.CID
    group by sc.CID, c.Cname;

输出结果：

![ddbebd5400d3474e80038b0b6b94899d.png][]

#### 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩 ####

select student.*, s.score
    from student
             inner join sc s on student.SID = s.SID and s.score = (
        select max(score)
        from teacher
                 left join Course C on Teacher.TID = C.TID
                 left join sc s on C.CID = s.CID
                 left join student s2 on s.SID = s2.SID
        where teacher.Tname = '张三');

输出结果：

![67620b5eeba649e89537346dfa19e0b9.png][]

#### 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 ####

select sc1.*, sc2.*
    from sc sc1
             join sc sc2
    
    where sc1.CID != sc2.CID
      and sc1.score = sc2.score;

输出结果：

![177e506dc6914e73a0cc4529dbdfe560.png][]

#### 42、查询每门功课成绩最好的前两名 ####

select stu.*, cou.cname, score
    from student stu
             inner join
         ((select sid, cid, score from sc where cid = 01 order by score desc limit 2)
          UNION
          (select sid, cid, score from sc where cid = 02 order by score desc limit 2)
          UNION
          (select sid, cid, score from sc where cid = 03 order by score desc limit 2)) p on stu.sid = p.sid
             inner join course cou on p.cid = cou.cid
    order by cname, score desc;

输出结果：

![762111a7a18d4fe0a5335ac20bbb9784.png][]

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

**要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列**

select c.CID, count(sc.CID) 选修人数
    from sc
             left join course c on SC.CID = c.CID
    group by c.CID
    having 选修人数>5
    order by 选修人数 desc, c.CID asc;

输出结果：

![a22d4f5fcb334bf19bb66b7523057fa7.png][]

#### 44、检索至少选修两门课程的学生学号 ####

select SID
    from student
    where SID in (
        select SID
        from SC
        group by SID
        having count(*) >= 2
    );

输出结果：

#### ![b465ad4bfa624dd195238de68558c03f.png][]   45、查询选修了全部课程的学生信息 ####

select *
    from student
    where SID in (
        select SID
        from SC
        group by SID
        having count(*) = (select count(*) from course)
    );

输出结果：

![9d1635f7943343cb8bc5892c9bbd5a7d.png][]

#### 46、查询各学生的年龄 ####

\#1.只按年份来算

select student.*, year(now()) - year(Sage) '年龄'
    from student;

\#2.按出生日期来算，过了生日那一天肯定就是大一岁了

select student.*, round(datediff(curdate(), Sage) / 365.2422) as '年龄'
    FROM student;

输出结果：

![be7842fcff3c43b9aa9705623f1d70ac.png][]

#### 47、查询本周过生日的学生 ####

select *
    from student
    where week(Sage) = week(now());

输出结果：

**数据中没有本周（12.5---12.11）过生日的学生**

#### 48、查询下周过生日的学生 ####

select *
    from student
    where week(Sage) = week(date_add(now(),interval 1 week ));

输出结果：

![815a902a6cdb4beab0d93b8a6749316f.png][]

#### 49、查询本月过生日的学生 ####

select *
    from student
    where month(Sage) = month(now());

输出结果：

![ae2fbacd69964a7e870915c281c696a3.png][]

#### 50、查询下月过生日的学生 ####

select *
    from student
    where month(Sage) = month(date_add(now(),interval 1 month ));

输出结果：

![88c8de29ac0148cfb2822a3e0951ffc7.png][]

[6639c19ce8a644cea883af384e23545b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/41cb5e9ffba84028921b8a215bc2ed95.png
[da80265108544e5cbe951ea4b117b850.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/943e61618d774594a34db2eb82a10993.png
[315fadb84ebc4a74a26278ad7ca4dd8a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a597ed9ed94b4f5e947a0df71755f21d.png
[7d0791c9dc144f62a97cd699552443ee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/46c2706a4a3346be9c3f8d9929ad746d.png
[e21b2f70cdc440c8ae03ab29af7b1034.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/fb832fe3c4c046df997013102c16884f.png
[bccd1cf7a8654e02b3349342c00bbbfa.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/2396525cbc424f979d6989c814ef5483.png
[34ba903f8bf5418c842cebb05ec446b1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/07c3eb18914f4fa099b1b408703197df.png
[ecddd4ad49654467bf4cca5ab49ae8e5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/76984a68fce24e56a150216e1212604a.png
[2379b2921113421aa1f520e4a132dbbe.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c837e66417a24453a1b59be8efc4b680.png
[4fb904c9ce5643c489351068dc7f2791.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c5aa91ebf0884251bffe905408de7691.png
[cd06cbba3b904e928c6d4ca70b7d2bd8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/4be05b9d3d7b466dbc0d67a58231c604.png
[72182d470c414e39a3afe1da14b2fddc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/924109c1f7ca4a3fabd4672ad135a8cc.png
[ada6494f497a4ac5b37ea223e2177497.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/ed9de024978a40e7a62798459cbab648.png
[55a69451f34347aba14f5bf690bee275.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/06244f112a124b7ab11d295a4e0ab6bd.png
[87483e76dd6443c093ac0885e815054b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/02e61dd786a44501b835e5d5a0165863.png
[e5f3cfe7954549908c95b25e7e7110e4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/d284201602514b75a20f2817ec54747e.png
[8c5a97eada6f405286f366ba58f73b95.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/ab9528878a874a878e1c87aa0a1168f3.png
[31e04f85dd164fbc88f083073f1c004f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/e53e8477db044e659a4387a78fe0240a.png
[38c922f356644bde9a3b9b344b4fbd6d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/5b050fcd5841432aa8ca83f15e95909d.png
[3a45755804004eeba259fc6e8c7d8f94.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/81fbcdab48ea4fbf91da9653ebc195cd.png
[b4f2bb6d64f24a2a8873090edf52dd02.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/4f12eb5c559d4fdea340ba897fe37b12.png
[0f9ad4bcf0544c6088049ef7b109a9e6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/4698603c318f4a70aa94a95d216917a4.png
[d4e4a9f5ac494a4b99b48d632c9265c4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/f63e68947652432ebef528650b5a95a4.png
[75940de022fd4481ae47a6795513db3e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/f283a77407ca4c7383db86756c506298.png
[a3e418eb6dc04571a8901819e203b752.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/4462958533c1433e93df9c4fc83a9b4f.png
[0c5972702eb7459a9f0491e036c72aa1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/6881a8336cc44384acc27bf84683bf16.png
[f5f85dd5d7ad4a6a836fbdd8dea5a8bc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/0a926929a55a4125a69a6c5c000bc681.png
[4d800843299b4cb089830cedad776fe9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/1550808ebb934f499b2344aed1ac5fb3.png
[eb0b263a4adf46a28f6e6d5e4fec9dee.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/d2c7e0d07b7b43559abf8ee0534fa811.png
[ab0c42c83a604d6bb1c0201c349d12c7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/ac4e639cb9ea42ab92e3ed2c8294d49c.png
[b9d6fb924e294583b57bc726b3da33e4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/6ff767b765934bf1871a7a3f6e8a637c.png
[f09cc32ac17248818181900fb9c34683.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/2854011f597d49fc9aed86cf11f0b203.png
[93e674be10674c5fbbe202d091163fda.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c48e6b889cff47b7858572a68a361c28.png
[ccf0d933c0e7443a9faed080c8cc420e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a2e4b455c76848cb913bbd3f47866713.png
[8b72ac4ca6904155b49582e5fde44a31.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/ae6e8d1f459b464cac27fa9bd28218b8.png
[ce61e7a0f94f43e3a316783dcddbdf7e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/5156f8f72958417b92961833ea0b05de.png
[6fc9d0c7be294c979f062ffd1a7e9dc1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a4439862b38241c0bfdb7e13138e3d1e.png
[5c62b856135047d0ad9954b13724ddcd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/ce5f4af1ebbb49c595b0dbff79d53174.png
[ddbebd5400d3474e80038b0b6b94899d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/c195b802201f42fdac4f10b2d6ff4bcb.png
[67620b5eeba649e89537346dfa19e0b9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/d282aa4a102545b1b5f84fc8c2e142e5.png
[177e506dc6914e73a0cc4529dbdfe560.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/53db295ee7e34e43a5b14677a35d8b8d.png
[762111a7a18d4fe0a5335ac20bbb9784.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/e5ea5ce098ec4f228f19da9fbde97c10.png
[a22d4f5fcb334bf19bb66b7523057fa7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/fb8f169a9f054014832495c6025ea10b.png
[b465ad4bfa624dd195238de68558c03f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a1d0839ac01d477b9e3f2e0d2aa624a4.png
[9d1635f7943343cb8bc5892c9bbd5a7d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/e8468a0e5a094358891585dde0984a2e.png
[be7842fcff3c43b9aa9705623f1d70ac.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/847afa7a9051488487267845e7f1275f.png
[815a902a6cdb4beab0d93b8a6749316f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/2f4ace61de2547fdb38c327d5f6511f0.png
[ae2fbacd69964a7e870915c281c696a3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/e7b83778d68f4009b0eea1c0eb3b0073.png
[88c8de29ac0148cfb2822a3e0951ffc7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/5828ed5740a34ef593d40eab17a7b990.png