MySQL经典45题
2019-04-02 本文已影响0人
LucasOoo
记录练习题代码，与原答案对比参考；
练习题来源：
http://note.youdao.com/noteshare?id=45d4298f42397bd52ccf6fc716e27ee9
自写代码以及参考答案：
1、
select *
from (select SId,CId,score from sc where sc.CId='01')as t1 ,(select SId,CId,score from sc where sc.CId='02')as t2
where t1.SId=t2.SId and
t1.score>t2.score;
1.1
select *
from (select * from sc where sc.CId='01')as t1 ,(select * from sc where sc.CId='02')as t2
where t1.SId=t2.SId
1.2
select *
from (select * from sc where CId='01')as t1
left join (select * from sc where CId='02')as t2 on
t1.SId=t2.SId
1.3
select *
from sc
where sc.SId not in (select SId from sc where sc.CId = '01')
and sc.CId='02';
2
select student.SId,Sname,avg_score
from student
left join (select sc.SId,avg(sc.score)as avg_score from sc group by sc.SId)as group_score
on student.SId=group_score.SId
where group_score.avg_score>=60
答案：
image.png
我做的：
image.png
3
~~select *~~
~~from sc~~
~~where score is not null~~
select distinct student.*
from student,sc
where student.SId=sc.SId
4
select student.SId, Sname,sum_course, sum_score
from student
left join (select sc.SId, count(sc.SId)as sum_course,sum(sc.score)as sum_score from sc group by sc.SId) as temp
on student.SId=temp.SId
where sum_score is not null;
答案：
image.png
我做的：bingo
5
select count(*)as count_name
from teacher
where Tname like '李%'
答案：
image.png
我做的：
image.png
6
select student.*, temp.CId,temp.score,temp2.Tname
from student
inner join (select * from sc )as temp
on student.SId=temp.SId
inner join (select Tname,TId from teacher where Tname='张三') as temp2
on temp.CId=temp2.TId;
答案：
image.png
我做的：错误
image.png
改正后：
select student.*, temp.CId,temp.score,temp2.Tname
from student
inner join (select * from sc )as temp
on student.SId=temp.SId
inner join course
on temp.CId=course.CId
inner join (select Tname,TId from teacher where Tname='张三') as temp2
on course.TId=temp2.TId;
image.png
7
select student.*,temp.count_course
from student
left join (select sc.SId,count(sc.SId)as count_course from sc group by sc.SId) #as temp 内嵌的表格可作为新表
on student.SId=temp.SId
where temp.count_course<3 or temp.count_course is null
答案：
image.png
我做的：
image.png
8
select distinct student.*
from student,sc
where student.SId=sc.SId
and sc.CId in (select CId from sc where sc.SId='01');
答案：
image.png
我做的：
image.png
9
select student.*
from student
where student.SId in (select sc.SId
from sc,(select SId,CId
from sc
where sc.SId='01') as s01
where sc.CId=s01.CId and sc.SId <> s01.SId
group by sc.SId
having count(sc.CId) = (select count(1) from sc where sc.SId='01')
order by sc.SId)
答案错误。
我做的：
image.png
10
select student.*
from student
left join (select sc.SId,sc.CId from sc
left join course
on sc.CId=course.CId
left join teacher
on course.TId=teacher.TId
where teacher.Tname='张三') as temp
on student.SId=temp.SId
where temp.SId is null
答案：
image.png
我做的：
image.png
11
select student.*,temp1.unqualified_count
from student
inner join (
select temp.SId, count(score) as unqualified_count
from (select * from sc where score<60) as temp
group by temp.SId
) as temp1
on student.SId=temp1.SId
答案：
image.png
我做的：
image.png
12
select student.*,sc.score
from student,sc
where student.SId=sc.SId
and sc.score<60
and sc.CId='01'
order by sc.score desc
答案：
image.png
我做的：
image.png
13
select student.*,temp1.avg_score,sc.CId,sc.score
from student
left join
(select sc.SId,avg(score)as avg_score
from sc
group by SId) as temp1
on student.SId=temp1.SId
left join sc
on temp1.SId=sc.SId
order by temp1.avg_score desc
答案：
image.png
我做的：
image.png
14
#解法1：
select course.CId,course.Cname,temp.maxscore,minscore,avgscore,qualified_rate,mid_score_rate,good_rate,excellent_rate,rate.headcount
from course ,
(select sc.CId,max(sc.score)as maxscore,min(sc.score)as minscore,avg(score)as avgscore from sc group by sc.CId)as temp,
(select temp1.CId,ifnull(temp2.qualified/temp1.headcount,0)as qualified_rate,
ifnull(temp3.mid_score/temp1.headcount,0)as mid_score_rate,
ifnull(temp4.good_score/temp1.headcount,0)as good_rate,
ifnull(temp5.excellent_score/temp1.headcount,0)as excellent_rate,
temp1.headcount as headcount
from (select sc.CId,count(SId)as headcount
from sc
group by sc.CId)as temp1
left join
(select sc.CId,count(SId)as qualified
from sc
where sc.score>=60
group by sc.CId) as temp2
on temp1.CId=temp2.CId
left join
(select sc.CId,count(SId)as mid_score
from sc
where sc.score between 70 and 80
group by sc.CId)as temp3
on temp2.CId=temp3.CId
left join
(select sc.CId,count(SId)as good_score
from sc
where sc.score > 80 and sc.score< 90
group by sc.CId)as temp4
on temp3.CId=temp4.CId
left join
(select sc.CId,count(SId)as excellent_score
from sc
where sc.score >=90
group by sc.CId)as temp5
on temp4.CId=temp5.CId
)as rate
where course.CId=temp.CId
and course.CId=rate.CId
order by headcount desc,CId asc
#解法2：
select sc.CId,course.CName,max(sc.score)as 最高分,min(sc.score)as 最低分,avg(score)as 平均分,
sum(case when sc.score>=60 then 1 else 0 end)/count(sc.SId) as 及格率,
sum(case when sc.score>=70 and sc.score<80 then 1 else 0 end)/count(sc.SId) as 中等率,
sum(case when sc.score>=80 and sc.score<90 then 1 else 0 end)/count(sc.SId) as 优良率,
sum(case when sc.score>=90 then 1 else 0 end)/count(sc.SId) as 优秀率,
count(sc.CId)as 选修人数
from sc,course
where sc.CId=course.CId
group by sc.CId
order by 选修人数 desc,sc.CId asc
答案：
我做的1：
image.png
我做的2：
image.png
15
select sc.CId,sc.SId,sc.score,
case when @curCId=sc.CId then @currank:=@currank+1 when @curCId:=sc.CId then @currank:=1 end as 排名
from sc,(select @currank:=0,@curCId:=null) as t
order by sc.CId asc,sc.score desc
答案：
image.png
我做的：
image.png
15.1
select sc.CId,sc.SId,sc.score,
case when @curCId=sc.CId then
(case when @curscore=sc.score then @currank when @curscore:=sc.score then @currank:=@currank+1 end)
when @curCId:=sc.CId then @currank:=1 end as 排名
from sc,(select @currank:=0,@curCId:=null,@curscore:=null) as t
order by sc.CId asc,sc.score desc
答案：
image.png
我做的：
image.png
16
select t2.*, @currank:=@currank+1 as 排名
from (select @currank:=0) as t1,
(select sc.SId,sum(sc.score)as 总分
from sc
group by sc.SId
order by sum(sc.score) desc)as t2
答案：
image.png
我做的：
image.png
16.1
select t2.*, case when @curscore=t2.总分 then @currank when @curscore:=t2.总分 then @currank:=@currank+1 end as 排名
from (select @currank:=0) as t1,
(select sc.SId,sum(sc.score)as 总分
from sc
group by sc.SId
order by sum(sc.score) desc)as t2
答案：
image.png
我做的：
image.png
17
select course.Cname,t.*
from course,
(select sc.CId,concat(sum(case when sc.score<=100 and sc.score>=85 then 1 else 0 end)/count(sc.SId)*100,'%') as '[100-85]',
concat(sum(case when sc.score<85 and sc.score>=70 then 1 else 0 end)/count(sc.SId)*100,'%')as '[85-70]',
concat(sum(case when sc.score<70 and sc.score>=60 then 1 else 0 end)/count(sc.SId)*100,'%') as '[70-60]',
concat(sum(case when sc.score<60 and sc.score>=0 then 1 else 0 end)/count(sc.SId)*100,'%') as '[60-0]'
from sc
group by sc.CId)as t
where course.CId=t.CId
答案：
image.png
我做的：
image.png
18
答案：
我做的：
19
select CId,count(SId) as 选修人数
from sc
group by CId
答案：
image.png
我做的：
image.png
20
select student.SId ,student.Sname
from sc,student
where sc.SId=student.SId
group by sc.SId
having count(*)=2
答案：
image.png
我做的：
image.png
21
select Ssex,count(*)as 人数
from student
group by Ssex
答案：
image.png
我做的：
image.png
22
select student.*
from student
where student.Sname like '%风%'
答案：
image.png
我做的：
image.png
23
select *
from student
inner join (select student.Sname,student.Ssex,count(*) from student group by student.Sname, student.Ssex having count(*)>1) as t1
on student.Sname=t1.sname
and student.Ssex=t1.Ssex
答案：
image.png
我做的：
image.png
24
select *
from student
where DATE_FORMAT(Sage,'%Y')='1990'
答案：
image.png
我做的：
image.png
25
select sc.CId,avg(sc.score)as avg_score
from sc
group by sc.CId
order by avg_score desc,sc.CId asc
答案：
image.png
我做的：
image.png
26
select sc.SId,student.Sname,AVG(sc.score)as 平均成绩
from sc ,student
where sc.SId=student.SId
group by sc.SId
having 平均成绩>=85
分组条件下必须通过having对分组进行限制
答案：
image.png
我做的：
image.png
27
select student.Sname,course.Cname,sc.score
from student,course,sc
where student.SId=sc.SId
and course.CId=sc.CId
and course.Cname='数学'
and sc.score<60
答案：
image.png
我做的：
image.png
28
select student.Sname,student.SId,sc.CId,course.Cname,sc.score
from student
left join sc
on student.SId=sc.SId
left join course
on sc.CId=course.CId
order by SId
答案：
image.png
我做的：
image.png
29
select student.Sname,course.Cname,sc.score
from student
left join sc
on student.SId=sc.SId
left join course
on sc.CId=course.CId
where sc.score>70
答案：
image.png
我做的：
image.png
30
select distinct course.Cname
from sc
left join course
on sc.CId=course.CId
where sc.score<60
答案：
image.png
我做的：
image.png
31
select student.Sname,student.SId,course.Cname,sc.score
from student
left join sc
on student.SId=sc.SId
left join course
on sc.CId=course.CId
where sc.score>=80
and sc.CId='01'
答案：
image.png
我做的：
image.png
32
参考第19题
33
select student.*,sc.score
from student ,sc,course,teacher
where student.SId=sc.SId
and sc.CId=course.CId
and course.TId=teacher.TId
and teacher.Tname='张三'
order by sc.score desc limit 1
答案：
image.png
我做的：
image.png
34
select t1.*, case when @curscore=t1.score then @currank when @curscore:=t1.score then @currank:=@currank+1 end as ranka
from
(select student.*,sc.score
from student ,sc,course,teacher
where student.SId=sc.SId
and sc.CId=course.CId
and course.TId=teacher.TId
and teacher.Tname='张三'
order by sc.score desc)as t1
,(select @curscore:=null,@currank:=0) as t2
where case when @curscore=t1.score then @currank when @curscore:=t1.score then @currank:=@currank+1 end=1
MySQL中不允许使用列别名作为查询条件，据说是因为MySql中列的别名本来是返回结果的时候才显示的，不在SQL解析时候使用。
答案：
代码有误
我做的：
35
select *
from sc
where exists(select * from sc as t2 where sc.SId=t2.SId and sc.CId!=t2.CId and sc.score=t2.score)
答案：
image.png
我做的：
image.png
36
#解法1：
select *
from
(select sc.*,@currank:=@currank+1 as 排名
from sc ,(select @currank:=0) as t
order by sc.score desc) as t2
where t2.排名<=2
#解法2：
select *
from sc
where (select count(*) from sc as t1 where t1.score>sc.score)<2
思路：大于此成绩的数量少于2就即为前两名
答案：
image.png
我做的：
image.png
37
select sc.Cid,count(*)
from sc
group by sc.CId
having count(*)>5
答案：
image.png
我做的：
image.png
38
select sc.SId ,count(*) as 选修课程
from sc
GROUP BY sc.SId
HAVING count(*)>=2
答案：
image.png
我做的：
image.png
39
select student.*,count(sc.CId)
from student,sc
where student.SId=sc.SId
group by sc.SId
having count(sc.CId)=3
答案：
image.png
我做的：
image.png
40
select student.*,(year(now())-year(student.Sage))as 年龄
from student
答案：
image.png
我做的：
image.png
41
select student.*,
timestampdiff(year,sage,curdate())-if(date_format(curdate(),'%c%e')<date_format(sage,'%c%e'),1,0) as 学生年龄
from student
答案：
image.png
我做的：
image.png
42
select student.*
from student
where weekofyear(concat(year(curdate()),right(date(sage),6)))=weekofyear(curdate())
答案：错误
我做的：
43
select student.*
from student
where weekofyear(concat(year(curdate()),right(date(sage),6)))=weekofyear(curdate())+1
答案：
我做的：
44
select student.*
from student
where date_format(sage,'%c')=date_format(curdate(),'%c')
答案：
我做的：
image.png
45
select student.*
from student
where date_format(sage,'%c')=date_format(curdate(),'%c')+1
答案：
我做的：
