MySQL经典45题

记录练习题代码，与原答案对比参考；
练习题来源：
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';

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

~~select *~~

~~from sc~~

~~where score is not null~~

select distinct student.*

from student,sc

where student.SId=sc.SId

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

select count(*)as count_name

from teacher

where Tname like '李%'

答案：

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 (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

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

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

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

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

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

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

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

#解法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

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

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

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

答案：
我做的：
19

select CId,count(SId) as 选修人数

from sc

group by CId

答案：

image.png

我做的：

image.png

select student.SId ,student.Sname

from sc,student

where sc.SId=student.SId

group by sc.SId

having count(*)=2

答案：

image.png

我做的：

image.png

select Ssex,count(*)as 人数

from student

group by Ssex

答案：

image.png

我做的：

image.png

select student.*

from student

where student.Sname like '%风%'

答案：

image.png

我做的：

image.png


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

select *

from student

where DATE_FORMAT(Sage,'%Y')='1990'

答案：

image.png

我做的：

image.png


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

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

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


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


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

select distinct course.Cname

from sc

left join course

on sc.CId=course.CId

where sc.score<60

答案：

image.png

我做的：

image.png


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题

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

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解析时候使用。
答案：
代码有误
我做的：

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

#解法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

select sc.Cid,count(*)

from sc

group by sc.CId

having count(*)>5

答案：

image.png

我做的：

image.png

select sc.SId ,count(*) as 选修课程

from sc

GROUP BY sc.SId

HAVING count(*)>=2

答案：

image.png

我做的：

image.png


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

select student.*,(year(now())-year(student.Sage))as 年龄

from student

答案：

image.png

我做的：

image.png

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

select student.*

from student

where weekofyear(concat(year(curdate()),right(date(sage),6)))=weekofyear(curdate())

答案：错误

我做的：


select student.*

from student

where weekofyear(concat(year(curdate()),right(date(sage),6)))=weekofyear(curdate())+1

答案：

我做的：


select student.*

from student

where date_format(sage,'%c')=date_format(curdate(),'%c')

答案：

我做的：

image.png

select student.*

from student

where date_format(sage,'%c')=date_format(curdate(),'%c')+1

答案：

我做的：

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 203,456评论 5赞 477
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,370评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 150,337评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,583评论 1赞 273
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,596评论 5赞 365
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,572评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,936评论 3赞 395
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,595评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,850评论 1赞 297
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,601评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,685评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,371评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,951评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,934评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,167评论 1赞 259
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 43,636评论 2赞 349
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,411评论 2赞 342

MySQL经典45题

推荐阅读更多精彩内容