mysql50题_经典MySQL50题

最近花了三四天把入门经典《SQL必知必会》看完了，然后开始做网上的50道经典练习题，部分习题比较难，大部分比较简单。下面，我po出自己的解答思路与结果：(如有错误或改进的地方还望留言指正~)

#1.1查询01课程比02课程成绩高的学生信息及课程分数 01 02都存在

select student.*,

max(case when sc.cid='01' then score end) as C1,

max(case when sc.cid='02' then score  end)as C2

from student left join sc

on student.sid=sc.sid

group by sid

having c1 is not null

and c2 is not null

and c1>c2;

select * from student right join(

select t1.sid, C1,C2 from

(select sid,score as C1 from sc where sc.cid='01') as t1,

(select sid,score as C2 from sc where sc.cid='02') as t2

where t1.sid=t2.sid and t1.c1>t2.c2

)r

on student.sid=r.sid;

#1.2查询01课程比02课程成绩高的学生信息及课程分数 01存在 02可能不存在

select student.*,

max(case when cid='01' then score end) as C1,

max(case when cid='02' then score end) as C2

from student left join sc

on student.sid=sc.sid

group by sid

having c1 is not null

and c1>c2

or c2 is null;

#1.3查询01课程分数不存在但02存在

select student.*,

max(case when cid='01' then score end) as C1,

max(case when cid='02' then score end) as C2

from student left join sc

on student.sid=sc.sid

group by sid

having c1 is null

and c2 is not null;

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

select Sname,SId,(select avg(score)

from sc

where sc.SId=student.SId

group by SId

having AVG(score)>=60)as avg_score

from student;

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

select Sname,Sage,Ssex

from student

where SId IN(select SId from sc where score IS NOT NULL);

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

select student.SId,student.Sname,count(*) as num_course,sum(score) as total_score

from student left join sc

on sc.SId=student.SId

group by SId;

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

select student.SId,student.Sname,count(*) as num_course,sum(score) as total_score

from student inner join sc

on sc.SId=student.SId

group by SId;

#5.查询李姓老师的数量

select count(*) as num_li

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=(select TId

from teacher

where Tname='张三'))); #子查询

select student.SId,Sname,Sage,Ssex

from student,sc,course,teacher

where student.SId=sc.SId

and sc.CId=course.CId

and teacher.TId=course.TId

and teacher.Tname='张三'; #多表联结

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

select student.*,count(sc.SId) as num_course

from student left join sc

on student.SId=sc.SId

group by student.SId

having num_course

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

select *

from student

where SId in (

select distinct SId from sc

where CId in (select CId from sc where SId='01'));#课程总数

#9.查询和01同学课程完全相同的学生的信息

select SId,Count(SId) as num_course

from sc

where CId in('01','02','03')

group by SId

having num_course=3;

select *

from student

where SId in(select SId

from sc

where CId in(select CId from sc where SId='01')

group by SId

having Count(SId)=(select count(CId)from sc where SId='01'));

#10.查询没学过‘张三’老师教授的任一门课程的学生姓名

select CId,teacher.TId,Tname

from course inner join teacher

on course.Tid=teacher.tid

where Tname='张三'; #教授的课程编号

select student.*

from student

where SId not in (select distinct student.SId

from student left join sc

on student.sid=sc.sid

where cid  in (select CId

from course inner join teacher

on course.Tid=teacher.tid

where Tname='张三')); #学过张三老师任一门课的同学)

select student.*

from student

where SId not in (select distinct student.SId

from student,sc,course,teacher

where student.sid=sc.sid

and course.cid=sc.cid

and course.Tid=teacher.tid

and  Tname='张三');#学过张三老师任一门课的同学

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

select student.SId,Sname,avg(score) as avg_score

from sc inner join student

on sc.SId=student.SId

group by sc.SId

having sc.SId in (select sid

from sc

where score<60

group by SId

having count(CId)>=2);    #2门及以上课程不及格同学的ID

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

select student.*

from student,sc

where student.sid=sc.sid

and cid='01'

and score<60

order by score desc;

#13.查询平均成绩由高到低显示所有学生的所有课程的成绩及平均成绩

select * from sc

left join (select sid,avg(score) as avg_score from sc

group by sid)r

on r.sid=sc.sid

order by avg_score desc;

#14.查询各科成绩的最高分、最低分和平均分

/*create view course_score as

select course.CId,course.Cname,sc.score

from course left join sc

on course.cid=sc.cid;*/

select cid,cname,max(score) as max_score,

min(score) as min_score,

avg(score) as avg_score,

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

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

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

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

count(*) as num

from course_score

group by cid

order by num desc,cid asc;

#15.按各科成绩进行排序，并显示排名，成绩重复时保留名次空缺

select A.CID,A.sid,A.score,count(B.score)+1 as rank

from sc as A

left join sc as B

on B.score>A.score and B.cid=A.cid

group by A.cid,A.sid

order by A.cid,rank asc;

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

select a.*,count(distinct b.score)+1 as rank

from sc as a

left join sc as b

on b.score>a.score and b.cid=a.cid

group by a.cid,a.sid

order by a.cid,rank;

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

select a.sid,a.total,count(b.total)+1 as rank

from (select sid,sum(score) as total

from sc

group by sid )a

left join (select sid,sum(score) as total

from sc

group by sid )b

on b.total>a.total

group by a.sid;

#16.1查询学生的总成绩，并进行排名，总分重复时合并名次

select a.sid,a.total,count(distinct b.total)+1 as rank

from (select sid,sum(score) as total

from sc

group by sid )a

left join (select sid,sum(score) as total

from sc

group by sid )b

on b.total>a.total

group by a.sid;

#17.统计各科成绩各分数段的人数：课程编号，课程名称，[100-85],[85-70],[70-60],[60-0]

select sc.cid,cname,

sum(case when score<=100 and score>=85 then 1 else 0 end)/count(*) as s1,

sum(case when score<85 and score>=70 then 1 else 0 end)/count(*) as s2,

sum(case when score<70 and score>=60 then 1 else 0 end)/count(*) as s3,

sum(case when score<60 then 1 else 0 end)/count(*) as s4

from sc,course

where sc.cid=course.cid

group by sc.cid;

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

select a.*,count(b.sid)+1 as rank

from sc as a

left join sc as b

on a.cid=b.cid and b.score>a.score

group by a.cid,a.sid

having rank<=3

order by a.cid,a.score desc;

#19.查询每门课选修的学生人数

select cid,count(sid)

from sc

group by cid;

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

select student.sid,student.Sname

from student inner join sc

on sc.sid=student.sid

group by sc.sid

having count(cid)=2;

#21.查询男生、女生人数

select Ssex,count(*) as num

from student

group by Ssex;

#22.查询名字中带有风字的学生信息

select *

from student

where Sname like '%风%';

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

select *

from student

where year(sage)=1990;

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

select cid,avg(score) as avg_score

from sc

group by cid

order by avg_score desc,cid asc;

#26.查询平均成绩≥85的所有学生的学号、姓名和平均成绩

select student.sid,student.sname,avg(score) as avg_score

from student,sc

where student.sid=sc.sid

group by student.sid

having avg_score>=85;

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

select student.sname,sc.score

from student,sc,course

where student.sid=sc.sid

and course.cid=sc.cid

and course.Cname='数学'

and sc.score<60;

#28.查询所有学生的课程及分数情况

select student.sid,student.sname,cid,score

from student left join sc

on student.sid=sc.sid;

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

select student.sname,course.cname,sc.score

from student,sc,course

where student.sid=sc.sid

and course.cid=sc.cid

and student.sid not in (select sid from sc group by sid having max(score)<=70);

#30.查询存在不及格的课程

select distinct course.cname,sc.cid

from course,sc

where course.cid=sc.cid

having sc.cid not in (select sid from sc group by cid having min(score)>=60);

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

select student.sid,sname

from student,sc

where sc.sid=student.sid

and sc.score>80

and sc.cid='01';

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

select cid,count(sid) as num

from sc

group by cid;

#33.成绩不重复，查询选张三老师授课的学生中，成绩最高的学生信息及其成绩

select student.*,score

from sc,student,course,teacher

where sc.sid=student.sid

and course.cid=sc.cid

and course.tid=teacher.tid

and tname='张三'

order by score desc

limit 0,1; #降序排列取第一名

#34.成绩重复，查询选李四老师授课的学生中，成绩最高的学生信息及其成绩

select student.*,score

from sc,student,course,teacher

where sc.sid=student.sid

and course.cid=sc.cid

and course.tid=teacher.tid

and tname='李四'

and sc.score=(select max(score)from sc,student,course,teacher

where sc.sid=student.sid

and course.cid=sc.cid

and course.tid=teacher.tid

and tname='李四') #找出最高分;

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

select *

from sc as a

left join sc as b

on a.sid=b.sid and b.cid<>a.cid

where a.score=b.score

group by a.cid;

#36.查询每门课程成绩最好的前两名

select a.*,count(b.sid)+1 as rank

from sc as a

left join sc as b

on b.score>a.score and a.cid=b.cid #左连结b表

group by a.cid,a.sid

having rank<=2

order by cid;

#37.统计每门课程的选修人数，超过5人

select cid,count(sid) as num

from sc

group by cid

having num>5;

#38.查询至少选修了两门课程的学生学号

select sid

from sc

group by sid

having count(sid)>=2;

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

select student.*

from student,sc

where student.sid=sc.sid

and sc.cid in (select cid from course)

group by sc.sid

having count(sc.cid)=(select count(distinct cid) from course);

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

select sname,year(now())-year(sage) as Age

from student;

#41.按出生日期来算，当前月日

select sid,case when month(now())

( month(now())=month(sage) and day(now())

then year(now())-year(sage)-1  else  year(now())-year(sage) end as Age

from student;

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

select date_format(now(),'%w'); #查询今天星期几

set @mon=(select subdate(curdate(),date_format(curdate(),'%w')-1)); #本周一的日期

set @sun=(select adddate(curdate(),7-date_format(now(),'%w'))); #本周日的日期

select sid

from student

where

date_format(sage,'%m-%d')<=date_format(@sun,'%m-%d')

and date_format(sage,'%m-%d')>=date_format(@mon,'%m-%d');

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

set @Nextmon=(select adddate(curdate(),7-date_format(curdate(),'%w')+1)); #下周一的日期

set @Nextsun=(select adddate(curdate(),7-date_format(now(),'%w')+7)); #下周日的日期

select sid

from student

where

date_format(sage,'%m-%d')<=date_format(@Nextsun,'%m-%d')

and date_format(sage,'%m-%d')>=date_format(@Nextmon,'%m-%d');

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

select sid

from student

where month(sage)=month(now());

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

select sid

from student

where month(sage)=month(now())+1;