MySQL经典45道练习题&答案

数据表

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

SId 学生编号,CId 课程编号,score 分数

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

select *

from SC a

left join Student d

on a.Sid=d.Sid

inner join SC b

on a.SID=b.Sid and a.Cid='01' and b.Cid='02'

where a.score>b.score;

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

select *

from SC a

inner join SC b

on a.Sid=b.Sid and a.Cid='01' and b.Cid='02'

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

select *

from (select * from SC where Cid='01') a

left join SC b

on a.Sid=b.Sid and b.Cid='02';

写法2：

select *

from SC a

left join SC b

on a.Sid=b.Sid and b.Cid='02'

where a.Cid='01';

1.3 查询不存在"01 "课程但存在" 02 "课程的情况

select *

from SC a

inner join SC b

on a.Sid=b.Sid and b.Cid='02'

where a.Sid not in(select Sid from SC where Cid='01');

写法2：

select *

from SC

where Sid not in(select Sid from SC where Cid='01')

and Cid='02';

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

select a.Sid,a.Sname,b.avg_score

from Student a

inner join

(select Sid,avg(score) as avg_score

from SC

group by Sid having avg(score)>=60) b

on a.Sid=b.Sid;

3.查询在 SC 表存在成绩的学生信息

select a.*

from Student a

inner join

(select distinct Sid from SC) b

on a.Sid=b.Sid;

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

select a.Sid,a.Sname,b.count_c,b.sum_score

from Student a

left join

(select Sid,count(Cid) as count_c,sum(score) as sum_score

from SC

group by Sid ) b

on a.Sid=b.Sid;

5.查询「李」姓老师的数量

select count(*)

from Teacher

where Tname like'李%';

6.查询学过「张三」老师授课的同学的信息

select *

from Student

where Sid in

(select Sid from SC where Cid in

(select Cid from Course where Tid in

(select Tid from Teacher where Tname='张三')));

写法2：

select Student.*

from Student,SC,Course,Teacher

where Teacher.Tname='张三'

and Teacher.Tid=Course.Tid

and Course.Cid=SC.Cid

and SC.Sid=Student.Sid;

7.查询没有学全所有课程的同学的信息

select a.*,cons

from Student a

inner join

(select Sid,count(Cid) as cons

from SC

group by Sid having cons<(select count(Cid) from Course))b

on a.Sid=b.Sid;

写法2：

select a.*,cons

from Student a,

(select Sid,count(Cid) as cons

from SC

group by Sid having cons<(select count(Cid) from Course))b

where a.Sid=b.Sid;

算上未学过课程的同学的情况：

select *

from Student

where Sid not in

(select Sid

from SC

group by Sid having count(Cid)=(select count(Cid) from Course));

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

select b.*

from (select distinct Sid

from SC

where Cid in(select Cid from SC where Sid='01')) a,

Student b

where a.Sid=b.Sid;

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

select b.*

from SC a

inner join Student b

on a.Sid=b.Sid

where a.Sid not in(select Sid from SC where Cid not in(select Cid from SC where Sid='01'))

and a.Sid!='01'

group by a.Sid

having count(*)=(select count(*) from SC where Sid='01')

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

select Sname

from Student

where Sid not in

(select Sid from SC

where Cid in

(select Cid from Course

where Tid in

(select Tid from Teacher

where Tname='张三')));

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

select a.*,avg(Score)

from Student a

inner join SC b

on a.Sid=b.Sid

where b.score<60

group by a.Sid

having count(*)>=2;

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

select a.*,Score

from Student a

right join

(select Sid, Score from SC where Cid='01' and Score<60) b

on a.Sid=b.Sid

order by Score desc;

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

select a.*,avg_score

from SC a

left join (select Sid,avg(Score) as avg_score from SC group by Sid) b

on a.Sid=b.Sid

order by avg_score desc;

14.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select Cid,max(Score) as 最高分,min(Score) as 最低分,avg(Score) as 平均分,

sum(case when sc.score>=60 then 1 else 0 end)/count(*) as 及格率,

sum(case when sc.score>=70 and score<80 then 1 else 0 end)/count(*) as 中等率,

sum(case when sc.score>=80 and score<90 then 1 else 0 end)/count(*) as 优良率,

sum(case when sc.score>=90 then 1 else 0 end)/count(*) as 优秀率,

count(*) as 选修人数

from SC group by Cid

order by 选修人数 desc,Cid asc;

15.按各科成绩进行排序，并显示排名， Score重复时继续排名

select Sid,Cid,Score,@rank:=@rank+1 as rn

from SC,(select @rank:=0)t

order by Score desc;

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

select Sid,Cid,Score,

case when @sco=Score then@rank

else @rank:=@rank+1 end as rn,

@sco:=Score

from SC,(select @rank:=0,@sco:=null)t

order by Score desc;

写法2：（可以去掉上面写法中多出来的@sco:=Score这一列）

select Sid,Cid,Score,

case when @sco=Score then@rank

when @sco:=Score then @rank:=@rank+1 end as rn

from SC,(select @rank:=0,@sco:=null)t

order by Score desc;

16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

select a.*,@rank:=if(@sco=scos,'',@rank+1) as rn,

@sco:=scos

from(select sid,sum(Score) as scos

from SC

group by Sid

order by scos desc)a,

(select @sco:=null,@rank:=0)b;

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

select a.*,@rank:=if(@sco=scos,@rank,@rank+1) as rn,

@sco:=scos

from(select sid,sum(Score) as scos

from SC

group by Sid

order by scos desc)a,

(select @sco:=null,@rank:=0)b;

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

select SC.Cid,Course.Cname,

concat(sum(case when score>=85 and score<=100 then 1 else 0 end)/count(*)*100,'%') as '[85-100]',

concat(sum(case when score>=70 and score<85 then 1 else 0 end)/count(*)*100,'%') as '[70-85)',

concat(sum(case when score>=60 and score<70 then 1 else 0 end)/count(*)*100,'%') as '[60-70)',

concat(sum(case when score>=0 and score<60 then 1 else 0 end)/count(*)*100,'%') as '[0-60)'

from SC

inner join Course

on SC.Cid=Course.Cid

group by SC.Cid;

18.查询各科成绩前三名的记录

不考虑Score重复的情况：

select a.*

from SC a

where (select count(*) from SC b

where b.Cid=a.Cid and b.Score>a.Score)<3

order by Cid asc,score desc;

Score 重复时合并名次的情况：

select Sid,Cid,Score,rn from

(select SC.*,@rank:=if(@ci=Cid,if(@sco=Score,@rank,@rank+1),1) as rn,

@sco:=Score,@ci:=Cid

from SC,(select @rank:=0,@sco:=null,@ci:=null)b

order by Cid asc,Score desc)a

where rn<=3;

19.查询每门课程被选修的学生数

select Cid,count(*) as cons from SC

group by Cid

order by Cid asc

20.查询出只选修两门课程的学生学号和姓名

select a.Sid,Sname

from Student a,SC b

where a.Sid=b.Sid

group by Sid

having count(*)=2;

21.查询男生、女生人数

select Ssex,count(*) as cons

from Student

group by Ssex;

22.查询名字中含有「风」字的学生信息

select Student.* from Student where Sname like"%风%";

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

select a.*,cons

from Student a

left join (select Sname,Ssex,count(*) as cons from Student group by Sname,Ssex)b

on a.Sname=b.Sname and a.Ssex=b.Ssex

where cons>1;

24.查询 1990 年出生的学生名单

select Student.* from Student where year(Sage)=1990;

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

select Cid,avg(Score)

from SC

group by Cid

order by Score desc,Cid asc;

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

select a.Sid,Sname,avg_score

from Student a

right join (select Sid,avg(Score) as avg_score

from SC

group by Sid

having avg_score>=85) b

on a.Sid=b.Sid;

27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

select Sname,Score

from Student a,SC b

where a.Sid=b.Sid and Score<60

and b.Cid =(select Cid from Course where Cname='数学');

28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

select * from Student a left join SC b on a.Sid=b.Sid;

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

select Sname,Cname,Score

from Student a,SC b,Course c

where a.Sid=b.Sid and b.Cid=c.Cid and Score>70;

30.查询不及格的课程

select distinct Cid from SC where Score<60;

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

select Sid,Sname

from Student

where Sid in(select Sid from SC where Cid='01' and Score>=80);

32.求每门课程的学生人数

select Cid,count(*) from SC group by Cid;

33.假设成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select a.*,max(Score)

from Student a,SC b,Course c,Teacher d

where a.Sid=b.Sid and b.Cid=c.Cid and c.Tid=d.Tid and Tname='张三';

34.假设成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

select * from

(select e.*,

case when @sco=Score then @rank

when @sco:=Score then @rank:=@rank+1 end as rn

from (select a.*,Score

from Student a,SC b,Course c,Teacher d

where a.Sid=b.Sid and b.Cid=c.Cid and c.Tid=d.Tid and Tname="张三"

order by Score desc)e,(select @rank:=0,@sco:=null)f)g

where rn=1;

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

select * from

SC a

where a.Sid in(select Sid from SC b

where a.Sid=b.Sid and a.Cid!=b.Cid and a.Score=b.Score);

36.查询每门功成绩最好的前两名

select * from

(select a.*,

@rank:=if(@ci=Cid,if(@sco=Score,@rank,@rank+1),1) as rn,

@sco:=Score,@ci:=Cid

from (select SC.* from SC order by Cid asc,Score desc)a,

(select @rank:=0,@sco:=null)b)c

where rn<=2;

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

select Cid,count(*) from SC group by Cid having count(*)>5;

38.检索至少选修两门课程的学生学号

select Sid,count(*) from SC group by Sid having count(*)>=2;

39.查询选修了全部课程的学生信息

select a.* from Student a

where Sid in(select Sid from SC

group by Sid having count(*)=(select count(Cid) from Course));

40.查询各学生的年龄，只按年份来算

select *,year(now())-year(Sage) as age from Student;

41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

select *,timestampdiff(year,Sage,now()) as age from Student;

42.查询本周过生日的学生

select *,week(Sage),week(now()) from Student where week(Sage)=week(now());

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

select *,week(Sage),week(now()) from Student where week(Sage)=week(now())+1;

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

select *,month(Sage),month(now()) from Student where month(Sage)=month(now());

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

select *,month(Sage),month(now()) from Student where month(Sage)=month(now())+1;