teacher老师表：
| t_id |
t_name |
| 01 |
张三 |
| 02 |
李四 |
| 03 |
王五 |
--准备条件,去掉sql_mode的ONLY_FULL_GROUP_BY否则此种情况下会报错:
--Expression#1ofselectlistisnotingroupbyclauseandcontainsnonaggregatedcolumn'userinfo.
--原因：
--MySQL5.7.5和up实现了对功能依赖的检测。如果启用了only_full_group_bySQL模式(在默认情况下是这样)，
--那么MySQL就会拒绝选择列表、条件或顺序列表引用的查询，这些查询将引用组中未命名的非聚合列，而不是在功能上依赖于它们。
--(在5.7.5之前，MySQL没有检测到功能依赖项，only_full_group_by在默认情况下是不启用的。关于前5.7.5行为的描述，请参阅MySQL5.6参考手册。)
--执行以下个命令，可以查看sql_mode的内容。
SHOWSESSIONVARIABLES;
SHOWGLOBALVARIABLES;
select@@sql_mode;
--更改
setglobalsql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
setsessionsql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
练习题和sql
--1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
selectst.*,sc.s_scoreas'语文',sc2.s_score'数学'
fromstudentst
leftjoinscoresconsc.s_id=st.s_idandsc.c_id='01'
leftjoinscoresc2onsc2.s_id=st.s_idandsc2.c_id='02'
wheresc.s_score>sc2.s_score
--2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
selectst.*,sc.s_score'语文',sc2.s_score'数学'fromstudentst
leftjoinscoresconsc.s_id=st.s_idandsc.c_id='01'
leftjoinscoresc2onsc2.s_id=st.s_idandsc2.c_id='02'
wheresc.s_score=60
--4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
--(包括有成绩的和无成绩的)
selectst.s_id,st.s_name,(casewhenROUND(AVG(sc.s_score),2)isnullthen0elseROUND(AVG(sc.s_score))end)cjScorefromstudentst
leftjoinscoresconsc.s_id=st.s_id
groupbyst.s_idhavingAVG(sc.s_score)<60orAVG(sc.s_score)isNULL
--5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
selectst.s_id,st.s_name,count(c.c_id),(casewhenSUM(sc.s_score)isnullorsum(sc.s_score)=""then0elseSUM(sc.s_score)end)fromstudentst
leftjoinscoresconsc.s_id=st.s_id
leftjoincourseconc.c_id=sc.c_id
groupbyst.s_id
--6、查询"李"姓老师的数量
selectt.t_name,count(t.t_id)fromteachert
groupbyt.t_idhavingt.t_namelike"李%";
--7、查询学过"张三"老师授课的同学的信息
selectst.*fromstudentst
leftjoinscoresconsc.s_id=st.s_id
leftjoincourseconc.c_id=sc.c_id
leftjointeachertont.t_id=c.t_id
wheret.t_name="张三"
--8、查询没学过"张三"老师授课的同学的信息
--张三老师教的课
selectc.*fromcoursecleftjointeachertont.t_id=c.t_idwheret.t_name="张三"
--有张三老师课成绩的st.s_id
selectsc.s_idfromscorescwheresc.c_idin(selectc.c_idfromcoursecleftjointeachertont.t_id=c.t_idwheret.t_name="张三")
--不在上面查到的st.s_id的学生信息,即没学过张三老师授课的同学信息
selectst.*fromstudentstwherest.s_idnotin(
selectsc.s_idfromscorescwheresc.c_idin(selectc.c_idfromcoursecleftjointeachertont.t_id=c.t_idwheret.t_name="张三")
)
--9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
selectst.*fromstudentst
innerjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_idandc.c_id="01"
wherest.s_idin(
selectst2.s_idfromstudentst2
innerjoinscoresc2onsc2.s_id=st2.s_id
innerjoincoursec2onc2.c_id=sc2.c_idandc2.c_id="02"
)
网友提供的思路(厉害呦~):
SELECTst.*
FROMstudentst
INNERJOINscorescONsc.`s_id`=st.`s_id`
GROUPBYst.`s_id`
HAVINGSUM(IF(sc.`c_id`="01"ORsc.`c_id`="02",1,0))>1
--10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
selectst.*fromstudentst
innerjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_idandc.c_id="01"
wherest.s_idnotin(
selectst2.s_idfromstudentst2
innerjoinscoresc2onsc2.s_id=st2.s_id
innerjoincoursec2onc2.c_id=sc2.c_idandc2.c_id="02"
)
--11、查询没有学全所有课程的同学的信息
--太复杂,下次换一种思路,看有没有简单点方法
--此处思路为查学全所有课程的学生id,再内联取反面
select*fromstudentwheres_idnotin(
selectst.s_idfromstudentst
innerjoinscoresconsc.s_id=st.s_idandsc.c_id="01"
wherest.s_idin(
selectst2.s_idfromstudentst2
innerjoinscoresc2onsc2.s_id=st2.s_idandsc2.c_id="02"
)andst.s_idin(
selectst2.s_idfromstudentst2
innerjoinscoresc2onsc2.s_id=st2.s_idandsc2.c_id="03"
))
--来自一楼网友的思路，左连接，根据学生id分组过滤掉数量小于课程表中总课程数量的结果(showmehiscode),简洁不少。
selectst.*fromStudentst
leftjoinScoreS
onst.s_id=S.s_id
groupbyst.s_id
havingcount(c_id)<(selectcount(c_id)fromCourse)
--12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
selectdistinctst.*fromstudentst
leftjoinscoresconsc.s_id=st.s_id
wheresc.c_idin(
selectsc2.c_idfromstudentst2
leftjoinscoresc2onsc2.s_id=st2.s_id
wherest2.s_id='01'
)
--13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
selectst.*fromstudentst
leftjoinscoresconsc.s_id=st.s_id
groupbyst.s_id
havinggroup_concat(sc.c_id)=
(
selectgroup_concat(sc2.c_id)fromstudentst2
leftjoinscoresc2onsc2.s_id=st2.s_id
wherest2.s_id='01'
)
--14、查询没学过"张三"老师讲授的任一门课程的学生姓名
selectst.s_namefromstudentst
wherest.s_idnotin(
selectsc.s_idfromscoresc
innerjoincourseconc.c_id=sc.c_id
innerjointeachertont.t_id=c.t_idandt.t_name="张三"
)
--15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
selectst.s_id,st.s_name,avg(sc.s_score)fromstudentst
leftjoinscoresconsc.s_id=st.s_id
wheresc.s_idin(
selectsc.s_idfromscoresc
wheresc.s_score<60orsc.s_scoreisNULL
groupbysc.s_idhavingCOUNT(sc.s_id)>=2
)
groupbyst.s_id
--16、检索"01"课程分数小于60，按分数降序排列的学生信息
selectst.*,sc.s_scorefromstudentst
innerjoinscoresconsc.s_id=st.s_idandsc.c_id="01"andsc.s_score<60
orderbysc.s_scoredesc
--17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
--可加round,casewhenthenelseend使显示更完美
selectst.s_id,st.s_name,avg(sc4.s_score)"平均分",sc.s_score"语文",sc2.s_score"数学",sc3.s_score"英语"fromstudentst
leftjoinscoresconsc.s_id=st.s_idandsc.c_id="01"
leftjoinscoresc2onsc2.s_id=st.s_idandsc2.c_id="02"
leftjoinscoresc3onsc3.s_id=st.s_idandsc3.c_id="03"
leftjoinscoresc4onsc4.s_id=st.s_id
groupbyst.s_id
orderbySUM(sc4.s_score)desc
--18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
selectc.c_id,c.c_name,max(sc.s_score)"最高分",MIN(sc2.s_score)"最低分",avg(sc3.s_score)"平均分"
,((selectcount(s_id)fromscorewheres_score>=60andc_id=c.c_id)/(selectcount(s_id)fromscorewherec_id=c.c_id))"及格率"
,((selectcount(s_id)fromscorewheres_score>=70ands_score<80andc_id=c.c_id)/(selectcount(s_id)fromscorewherec_id=c.c_id))"中等率"
,((selectcount(s_id)fromscorewheres_score>=80ands_score<90andc_id=c.c_id)/(selectcount(s_id)fromscorewherec_id=c.c_id))"优良率"
,((selectcount(s_id)fromscorewheres_score>=90andc_id=c.c_id)/(selectcount(s_id)fromscorewherec_id=c.c_id))"优秀率"
fromcoursec
leftjoinscoresconsc.c_id=c.c_id
leftjoinscoresc2onsc2.c_id=c.c_id
leftjoinscoresc3onsc3.c_id=c.c_id
groupbyc.c_id
--19、按各科成绩进行排序，并显示排名(实现不完全)
--mysql没有rank函数
--加@score是为了防止用unionall后打乱了顺序
selectc1.s_id,c1.c_id,c1.c_name,@score:=c1.s_score,@i:=@i+1from(selectc.c_name,sc.*fromcoursec
leftjoinscoresconsc.c_id=c.c_id
wherec.c_id="01"orderbysc.s_scoredesc)c1,
(select@i:=0)a
unionall
selectc2.s_id,c2.c_id,c2.c_name,c2.s_score,@ii:=@ii+1from(selectc.c_name,sc.*fromcoursec
leftjoinscoresconsc.c_id=c.c_id
wherec.c_id="02"orderbysc.s_scoredesc)c2,
(select@ii:=0)aa
unionall
selectc3.s_id,c3.c_id,c3.c_name,c3.s_score,@iii:=@iii+1from(selectc.c_name,sc.*fromcoursec
leftjoinscoresconsc.c_id=c.c_id
wherec.c_id="03"orderbysc.s_scoredesc)c3;
set@iii=0;
--20、查询学生的总成绩并进行排名
selectst.s_id,st.s_name
,(casewhensum(sc.s_score)isnullthen0elsesum(sc.s_score)end)
fromstudentst
leftjoinscoresconsc.s_id=st.s_id
groupbyst.s_idorderbysum(sc.s_score)desc
--21、查询不同老师所教不同课程平均分从高到低显示
selectt.t_id,t.t_name,c.c_name,avg(sc.s_score)fromteachert
leftjoincourseconc.t_id=t.t_id
leftjoinscoresconsc.c_id=c.c_id
groupbyt.t_id
orderbyavg(sc.s_score)desc
--22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
selecta.*from(
selectst.*,c.c_id,c.c_name,sc.s_scorefromstudentst
leftjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_idandc.c_id="01"
orderbysc.s_scoredescLIMIT1,2)a
unionall
selectb.*from(
selectst.*,c.c_id,c.c_name,sc.s_scorefromstudentst
leftjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_idandc.c_id="02"
orderbysc.s_scoredescLIMIT1,2)b
unionall
selectc.*from(
selectst.*,c.c_id,c.c_name,sc.s_scorefromstudentst
leftjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_idandc.c_id="03"
orderbysc.s_scoredescLIMIT1,2)c
--23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
selectc.c_id,c.c_name
,((selectcount(1)fromscorescwheresc.c_id=c.c_idandsc.s_score<=100andsc.s_score>80)/(selectcount(1)fromscorescwheresc.c_id=c.c_id))"100-85"
,((selectcount(1)fromscorescwheresc.c_id=c.c_idandsc.s_score<=85andsc.s_score>70)/(selectcount(1)fromscorescwheresc.c_id=c.c_id))"85-70"
,((selectcount(1)fromscorescwheresc.c_id=c.c_idandsc.s_score<=70andsc.s_score>60)/(selectcount(1)fromscorescwheresc.c_id=c.c_id))"70-60"
,((selectcount(1)fromscorescwheresc.c_id=c.c_idandsc.s_score<=60andsc.s_score>=0)/(selectcount(1)fromscorescwheresc.c_id=c.c_id))"60-0"
fromcoursecorderbyc.c_id
--24、查询学生平均成绩及其名次
set@i=0;
selecta.*,@i:=@i+1from(
selectst.s_id,st.s_name,round((casewhenavg(sc.s_score)isnullthen0elseavg(sc.s_score)end),2)"平均分"fromstudentst
leftjoinscoresconsc.s_id=st.s_id
groupbyst.s_idorderbysc.s_scoredesc)a
--25、查询各科成绩前三名的记录
selecta.*from(
selectst.s_id,st.s_name,c.c_id,c.c_name,sc.s_scorefromstudentst
leftjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_idandc.c_id='01'
orderbysc.s_scoredescLIMIT0,3)a
unionall
selectb.*from(
selectst.s_id,st.s_name,c.c_id,c.c_name,sc.s_scorefromstudentst
leftjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_idandc.c_id='02'
orderbysc.s_scoredescLIMIT0,3)b
unionall
selectc.*from(
selectst.s_id,st.s_name,c.c_id,c.c_name,sc.s_scorefromstudentst
leftjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_idandc.c_id='03'
orderbysc.s_scoredescLIMIT0,3)c
--26、查询每门课程被选修的学生数
selectc.c_id,c.c_name,count(1)fromcoursec
leftjoinscoresconsc.c_id=c.c_id
innerjoinstudentstonst.s_id=c.c_id
groupbyst.s_id
--27、查询出只有两门课程的全部学生的学号和姓名
selectst.s_id,st.s_namefromstudentst
leftjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_id
groupbyst.s_idhavingcount(1)=2
--28、查询男生、女生人数
selectst.s_sex,count(1)fromstudentstgroupbyst.s_sex
--29、查询名字中含有"风"字的学生信息
selectst.*fromstudentstwherest.s_namelike"%风%";
--30、查询同名同性学生名单，并统计同名人数
selectst.*,count(1)fromstudentstgroupbyst.s_name,st.s_sexhavingcount(1)>1
--31、查询1990年出生的学生名单
selectst.*fromstudentstwherest.s_birthlike"1990%";
--32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
selectc.c_id,c.c_name,avg(sc.s_score)fromcoursec
innerjoinscoresconsc.c_id=c.c_id
groupbyc.c_idorderbyavg(sc.s_score)desc,c.c_idasc
--33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
selectst.s_id,st.s_name,avg(sc.s_score)fromstudentst
leftjoinscoresconsc.s_id=st.s_id
groupbyst.s_idhavingavg(sc.s_score)>=85
--34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
selectst.s_id,st.s_name,sc.s_scorefromstudentst
innerjoinscoresconsc.s_id=st.s_idandsc.s_score<60
innerjoincourseconc.c_id=sc.c_idandc.c_name="数学"
--35、查询所有学生的课程及分数情况；
selectst.s_id,st.s_name,c.c_name,sc.s_scorefromstudentst
leftjoinscoresconsc.s_id=st.s_id
leftjoincourseconc.c_id=sc.c_id
orderbyst.s_id,c.c_name
--36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
selectst2.s_id,st2.s_name,c2.c_name,sc2.s_scorefromstudentst2
leftjoinscoresc2onsc2.s_id=st2.s_id
leftjoincoursec2onc2.c_id=sc2.c_id
wherest2.s_idin(
selectst.s_idfromstudentst
leftjoinscoresconsc.s_id=st.s_id
groupbyst.s_idhavingmin(sc.s_score)>=70)
orderbys_id
--37、查询不及格的课程
selectst.s_id,c.c_name,st.s_name,sc.s_scorefromstudentst
innerjoinscoresconsc.s_id=st.s_idandsc.s_score<60
innerjoincourseconc.c_id=sc.c_id
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
selectst.s_id,st.s_name,sc.s_scorefromstudentst
innerjoinscoresconsc.s_id=st.s_idandsc.c_id="01"andsc.s_score>=80
--39、求每门课程的学生人数
selectc.c_id,c.c_name,count(1)fromcoursec
innerjoinscoresconsc.c_id=c.c_id
groupbyc.c_id
--40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
selectst.*,c.c_name,sc.s_score,t.t_namefromstudentst
innerjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_id
innerjointeachertont.t_id=c.t_idandt.t_name="张三"
orderbysc.s_scoredesc
limit0,1
--41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
selectst.s_id,st.s_name,sc.c_id,sc.s_scorefromstudentst
leftjoinscoresconsc.s_id=st.s_id
leftjoincourseconc.c_id=sc.c_id
where(
selectcount(1)fromstudentst2
leftjoinscoresc2onsc2.s_id=st2.s_id
leftjoincoursec2onc2.c_id=sc2.c_id
wheresc.s_score=sc2.s_scoreandc.c_id!=c2.c_id
)>1
--42、查询每门功成绩最好的前两名
selecta.*from(selectst.s_id,st.s_name,c.c_name,sc.s_scorefromstudentst
leftjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_idandc.c_id="01"
orderbysc.s_scoredesclimit0,2)a
unionall
selectb.*from(selectst.s_id,st.s_name,c.c_name,sc.s_scorefromstudentst
leftjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_idandc.c_id="02"
orderbysc.s_scoredesclimit0,2)b
unionall
selectc.*from(selectst.s_id,st.s_name,c.c_name,sc.s_scorefromstudentst
leftjoinscoresconsc.s_id=st.s_id
innerjoincourseconc.c_id=sc.c_idandc.c_id="03"
orderbysc.s_scoredesclimit0,2)c
--借鉴(更准确,漂亮):
selecta.s_id,a.c_id,a.s_scorefromscorea
where(selectCOUNT(1)fromscorebwhereb.c_id=a.c_idandb.s_score>=a.s_score)<=2orderbya.c_id
--43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，
--若人数相同，按课程号升序排列
selectsc.c_id,count(1)fromscoresc
leftjoincourseconc.c_id=sc.c_id
groupbyc.c_idhavingcount(1)>5
orderbycount(1)desc,sc.c_idasc
--44、检索至少选修两门课程的学生学号
selectst.s_idfromstudentst
leftjoinscoresconsc.s_id=st.s_id
groupbyst.s_idhavingcount(1)>=2
--45、查询选修了全部课程的学生信息
selectst.*fromstudentst
leftjoinscoresconsc.s_id=st.s_id
groupbyst.s_idhavingcount(1)=(selectcount(1)fromcourse)
--46、查询各学生的年龄
selectst.*,timestampdiff(year,st.s_birth,now())fromstudentst
--47、查询本周过生日的学生
--此处可能有问题,week函数取的为当前年的第几周,2017-12-12是第50周而2018-12-12是第49周,可以取月份,day,星期几(%w),
--再判断本周是否会持续到下一个月进行判断,太麻烦,不会写
selectst.*fromstudentst
whereweek(now())=week(date_format(st.s_birth,'%Y%m%d'))
--48、查询下周过生日的学生
selectst.*fromstudentst
whereweek(now())+1=week(date_format(st.s_birth,'%Y%m%d'))
--49、查询本月过生日的学生
selectst.*fromstudentst
wheremonth(now())=month(date_format(st.s_birth,'%Y%m%d'))
--50、查询下月过生日的学生
--注意:当当前月为12时,用month(now())+1为13而不是1,可用timestampadd()函数或mod取模
selectst.*fromstudentst
wheremonth(timestampadd(month,1,now()))=month(date_format(st.s_birth,'%Y%m%d'))
--或
selectst.*fromstudentstwhere(month(now())+1)mod12=month(date_format(st.s_birth,'%Y%m%d'))
到此这篇关于MysqlSql语句练习题（50道）的文章就介绍到这了,更多相关Mysql练习题内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！
