第二周mysql经典45题（2020/09/14-2020/09/20）

中间15、16四题看不懂视频内容，变量之前没有接触过，看不懂，之前用的oracle的dense_rank函数可以写出来的，这里用mysql没有写出来。。

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

#第一种解法：

select *

from Student s

join SC sc

on s.sid=sc.sid

join SC c

on s.sid=c.sid

and sc.cid=01 and c.cid=02

where sc.score>c.score

#第二种解法：

select *

from (select * from SC where cid=01) a

join (

select *

from SC where cid=02) b

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

join Student s

on a.sid=s.sid

#1,1.查询同时存在01课程和02课程的情况

#第一种解法

select *

from (select * from SC where cid=01) a

join (

select *

from SC where cid=02) b

on a.sid=b.sid

#第二种解法

select *

from SC  a

join  SC  b

on a.sid=b.sid

where a.cid=01 and b.cid=02

#1.2.查询存在01课程可能不存在02课程情况

select *

from (select * from SC where cid=01) c

left join (select * from SC where cid=02)b

on c.sid=b.sid

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

select *

from SC

where cid=02 and sid not in (select sid from SC where cid=01)

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

select s.sid,s.sname,avg(score)

from Student s

join SC c

on s.sid=c.sid

group by s.sid,s.sname

having avg(score)>=60

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

select *

from Student

where sid in(select sid from SC )

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

select s.sid,s.sname,count(cid),sum(score)

FROM Student s

left join SC c

on s.sid=c.sid

group by s.sid,s.sname

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

select count(tid)

from Teacher

where tname like "李%"

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

SELECT s.*

from Student s

join SC c

on s.sid=c.sid

join Course a

on c.cid=a.cid

join Teacher t

on t.tid=a.tid

and t.tname="张三"

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

select s.sid,s.sname,count(score)

from Student s

join SC c

on s.sid=c.sid

GROUP BY s.sid,s.sname

HAVING count(score)< (select count(cid) from Course)

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

SELECT distinct s.*

FROM Student s

join SC a

on s.sid=a.sid

where cid in (select cid from SC where sid=01)

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

SELECT a.sid,b.sname,b.ssex,b.sage

from SC a

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 cid is not null))

and a.sid!=01

group by a.sid,b.sname,b.ssex,b.sage

HAVING count(cid)=(SELECT count(cid) 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 s.sid,s.sname,a.avg

from Student s

join (

select sid,count(1),avg(score) avg

from SC

where score<60

group by sid

HAVING count(1)>=2) a

on s.sid=a.sid

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

SELECT b.*,a.score

FROM SC a

join Student b

on a.sid=b.sid

where cid=01 and score<60

order by a.score desc

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

select a.sid,score,b.avg

from SC a

join(

select sid,avg(score) avg

from SC

group by sid) b

on a.sid=b.sid

order by b.avg desc

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

SELECT c.cid,a.cname,count(1) 选修人数,max(score) 最高分,min(score) 最低分,avg(score) 平均分,sum(case when score >=60 then 1 else 0 end) /count(1)*100 及格率,sum(case when score >=70 and score <80 then 1 else 0 end) /count(1)*100 中等率,sum(case when score >=80 and score <90 then 1 else 0 end) /count(1)*100 优良率,sum(case when score >=90 then 1 else 0 end) /count(1)*100 优秀率

FROM SC c

join Course a

on c.cid=a.cid

group by c.cid,a.cname

order by 选修人数 desc,c.cid

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

SELECT sid,cid,score,@rank:=@rank+1

FROM SC,(SELECT @rank:=0) t

order by score desc

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

SELECT *

FROM

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

SELECT sid,sum(score) sco,@rank:=@rank+1

FROM SC,(SELECT @rank:=0,@sco:=null) t

GROUP BY sid

order by sco desc

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

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

SELECT a.cid,cname,sum(case when score >85 and score <=100 then 1 else 0 end) "85-100人数",

sum(case when score >70 and score <=85 then 1 else 0 end) "70-85人数",

sum(case when score >60 and score <=70 then 1 else 0 end) "60-70人数",

sum(case when score >0 and score <=60 then 1 else 0 end) "0-60人数",

concat(sum(case when score >85 and score <=100 then 1 else 0 end) /count(1)*100,"%") "85-100占比",

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

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

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

FROM SC a

join Course b

on a.cid=b.cid

group by a.cid,cname

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

SELECT sid,cid,score

FROM SC a

where (select count(1) from SC b where a.cid=b.cid and b.score>a.score)<3

order by cid

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

SELECT cid,count(1)

from SC

GROUP BY cid

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

SELECT a.sid,sname,count(1)

FROM SC a

join Student b

on a.sid=b.sid

GROUP BY a.sid,sname

HAVING count(1)=2

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

SELECT Ssex,count(1)

FROM Student

group by Ssex

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

SELECT *

FROM Student

where sname like "%风%"

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

SELECT a.*

FROM Student a

join Student b

on a.sname=b.sname and a.ssex=b.Ssex and a.sid!=b.sid

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

SELECT *

FROM Student

where Sage like"1990%"

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

SELECT cid,avg(score)

FROM SC

group by cid

ORDER BY avg(score) desc,cid

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

SELECT a.sid,sname,avg(score)

FROM SC a

join Student b

on a.sid=b.sid

GROUP BY a.sid,sname

HAVING AVG(SCORE)>=85

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

SELECT sname,score

FROM SC a

join Student b

on a.sid=b.sid

join Course c

on a.cid=c.cid

where cname ="数学" and score <60

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

SELECT a.sid,cid,score

FROM Student a

left join SC b

on a.sid=b.sid

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

SELECT sname,cname,score

FROM Student a

join SC b

on a.sid=b.sid

join Course c

on b.cid=c.cid

where score>70

#30.查询不及格的课程

SELECT distinct cid

FROM SC a

-- join Course b

-- on a.cid=b.cid

where score<60

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

SELECT a.sid,sname

FROM SC a

join Student b

on a.sid=b.sid

where cid=01 and score>80

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

SELECT cid,count(1)

FROM SC

GROUP BY cid

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

SELECT d.*,score

from Course a

join Teacher b

on a.tid=b.tid

join SC c

on a.cid=c.cid

join Student d

on d.sid=c.sid

where Tname ="张三"

ORDER BY score desc

limit 1

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

SELECT a.* FROM SC join (

SELECT d.sid,d.sname,d.ssex,d.Sage,score

from Course a

join Teacher b

on a.tid=b.tid

join SC c

on a.cid=c.cid

join Student d

on d.sid=c.sid

where Tname ="张三"

ORDER BY score desc

limit 1) a

on SC.sid=a.sid

where SC.score=a.score

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

SELECT a.sid,a.cid,a.score

FROM SC a

join SC b

on a.sid=b.sid and a.score=b.score and a.cid!=b.cid

GROUP BY a.sid,a.cid,a.score

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

SELECT sid,cid,score

FROM SC a

where (SELECT count(1) from SC b where a.cid=b.cid and a.score<b.score)<2

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

SELECT cid,count(1)

FROM SC

GROUP BY cid

HAVING count(1)>5

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

SELECT sid,count(1)

FROM SC

GROUP BY sid

HAVING count(1)>=2

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

SELECT sid,count(1)

FROM SC

GROUP BY sid

HAVING count(1)=(select count(1) FROM Course)

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

SELECT sid,2020-year(Sage) age

from Student

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

SELECT sid,TIMESTAMPDIFF(year,Sage,now())

FROM Student

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

SELECT WEEK(Sage),WEEK(now())

FROM Student

where WEEK(Sage)=WEEK(now())

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

SELECT sid, WEEK(Sage),WEEK(now())

FROM Student

where WEEK(Sage)=WEEK(now())+1

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

SELECT sid,MONTH(Sage),MONTH(now())

FROM Student

where MONTH(Sage)=MONTH(now())

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

SELECT sid,MONTH(Sage),MONTH(now())

FROM Student

where MONTH(Sage)=MONTH(now())+1