MySQL经典50题
MySQL经典50题的习题及参考答案💪
题目1
题目要求
查询"01"课程比"02"课程成绩高的学生的信息及课程分数
SQL实现
1 | -- 方法1 |
第二种方法实现:
题目2
题目要求
查询"01"课程比"02"课程成绩低的学生的信息及课程分数(题目1是成绩高)
SQL实现
类比题目1的实现过程
1 | -- 方法1:通过连接方式实现 |
题目3
题目需求
查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
SQL实现
1 | -- 执行顺序:先执行分组,再执行avg平均操作 |
附加题:总分超过200分的同学
题目4
题目要求
查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)
SQL实现1-两种情况连接
平均分小于60
1 | select |
结果为:
没有成绩的同学:
1 | select |
最后将两个部分的结果连起来即可:通过union
方法
SQL实现2-ifnull函数判断
使用ifnull函数
1 | select |
使用null判断
1 | select |
题目5
题目需求
查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
SQL实现
1 | select |
题目6
题目需求
查询“李”姓老师的数量
SQL实现
1 | select count(t_name) from Teacher where t_name like "李%"; -- 通配符 |
这题怕是最简单的吧😭
题目7
题目需求
查询学过张三老师授课的同学的信息
SQL实现
1 | -- 方法1:通过张三老师的课程的学生来查找;自己的方法 |
自己的方法:
方法2来实现:
方法3实现:
题目8
题目需求
找出没有学过张三老师课程的学生
SQL实现
1 | select * -- 3. 通过学号找出全部学生信息 |
方法2:
题目9
题目需求
查询学过编号为01,并且学过编号为02课程的学生信息
SQL实现
1 | -- 自己的方法:通过自连接实现 |
- 先从Score表中看看哪些人是满足要求的:01-05同学是满足的
通过自连接查询的语句如下:
查询出学号后再匹配出学生信息:
通过where语句实现:
方法3的实现:
题目10
题目需求
查询学过01课程,但是没有学过02课程的学生信息(注意和上面👆题目的区别)
SQL实现
首先看看哪些同学是满足要求的:只有06号同学是满足的
错误思路1
直接将上面一题的结果全部排出,导致那些没有学过01课程的学生也出现了:07,08
1 | select s1.* |
错误思路2
将上面题目中的02课程直接取反,导致同时修过01,02,03或者只修01,03的同学也会出现
1 | select s1.* |
正确思路
https://www.jianshu.com/p/9abffdd334fa
1 | -- 方法1:根据两种修课情况来判断 |
!!!!!方法2:先把06号学生找出来
1 | select * from Student where s_id in ( |
题目11
题目需求
查询没有学完全部课程的同学的信息
SQL实现
1 | -- 自己的方法 |
自己的方法一开始在课程的最大数中没有使用Course表,导致多使用了一个临时表的结果,现在改成使用Course表的统计值(3)作为课程的总数:
1 | select s.* |
1 | -- 方法2:having |
题目12
题目需求
查询至少有一门课与学号为01的同学所学相同的同学的信息
SQL实现
首先看看结果的:因为01号同学修了全部课程,所以其他的同学都是满足要求,除了08号同学没有任何成绩,不符合。
具体实现过程为:
1 | select * -- 3、求出学生信息 |
1 | -- 方法2 |
题目13
题目需求
查询和01同学学习的课程完全相同的同学的信息
SQL实现
- 自己的方法
1 | select * |
我们在上面的步骤2中不考虑直接指定3(where number=3),而是用01学生所修的课程数(虽然也是3)来代替:
1 | select * |
- 使用
group_concat
函数
group_concat
的使用方法为:
1 | group_concat([DISTINCT] 字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符']) |
我们将Score
表中每个s_id
的c_id
进行分组合并,实际的效果如下:
1 | select |
需要进行排序的原因是防止出现这种情况:01修的课程顺序是:01,02,03;如果有同学修课的顺序是02,03,01,虽然顺序不同,但是本质上他们修的课程是相同的
使用排序后都会变成:01,02,03,保证结果相同
那么之后,我们只需要判断合并后和01号同学相同的结果即可,取出学号:
1 | select * -- 3、查询信息 |
题目14
题目需求
查询没有修过张三老师讲授的任何一门课程的学生姓名
SQL实现
自己的方法,具体过程如下:
1 | select s_name -- 4、学号取反找到学生姓名 |
1 | -- 修过张老师课程的学生的学号 |
题目15
题目需求
查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
SQL实现
首先看看哪些同学是满足两门或者两门以上是及格的
1 | -- 2门及以上不及格的 |
说明04,06是我们最终想要的结果
1 | -- 自己的方法 |
1 | -- 参考方法1 |
改进点
上面的两种方法都没有考虑都08学生,3门都没有成绩,这个本题需要改进的地方。
题目16
题目需求
检索01课程分数小于60,按分数降序排列的学生信息
SQL实现
自己的方法如下:
首先从Score表中找出哪些学生是满足这个要求:
1 | select |
然后直接将上面的结果和Student表查询:
1 | select s.* |
1 | select |
1 | -- 自己的方法2:两个表的直接连接查询+where条件 |
题目17
题目需求
按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩
SQL实现
下面是自己的解法:
1、先求出每个同学的平均分,并降序排列
1 | select |
1 | -- 自己的方法 |
1 | -- 参考方法1 |
1 | select |
严格模式的报错:
ERROR 1055 (42000): Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.b.s_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中,也就是说查出来的列必须在group by后面出现否则就会报错,或者这个字段出现在聚合函数里面。
1 | -- 参考方法2:将上面的b.s_name去掉 |
题目18
题目需求
查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率;及格:>=60,中等为:70-80,优良为:80-90,优秀为:>=90
SQL实现
思路清晰:统计每个阶段的总人数,再除以总共的人数即可
将成绩表和课程表联合起来进行查询:
- case 语句用于对每个分数贴标签
- sum 语句对相应的语句中的1进行求和
1 | select |
题目19
题目需求
按照各科成绩进行排序,并且显示排名
分析过程
题目的意思是:将每科的成绩单独进行排名,类似如下的效果:
课程名 | 分数 | 排名 |
---|---|---|
英语 | 99 | 1 |
英语 | 92 | 2 |
英语 | 89 | 3 |
数学 | 88 | 1 |
数学 | 85 | 2 |
…… | …… | …… |
SQL实现
第一步:我们对Score表中的一门课程进行排名,比如01课程
1 | select * from( |
上面是针对01课程,结果为:
第二步:我们将01、02、03课程全部连接起来,通过union实现
- 表的自连接
- SQL实现排序
1 | -- 自己的方法 |
1 | -- 参考代码 |
题目20
题目需求
查询学生的总成绩,并进行排名
SQL实现
1、先查询每个学生的总成绩
1 | select |
将上面的结果和学生信息表进行关联查询:
1 | -- |
1 | -- 不使用中间表查询 |
如果想给排名加上一个排序号,参考之前的文章
1 | -- 加上排序号 |
题目21
题目需求
查询不同老师所教不同课程平均分从高到低显示
SQL实现
先找出每个老师教授了哪些课程:
1 | select |
将上面的结果和成绩表连接起来:
1 | select |
题目22
题目需求
查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SQL实现
自己的方法
1、课程表和成绩表连接起来,显示所有的课程和成绩信息
1 | select |
2、查出全部的语文成绩
1 | select s.s_id, s.s_score, c.c_name |
3、我们找出语文的第2、3的学生
1 | select s.s_id, s.s_score, c.c_name |
4、同时求出语文、数学、英语的分数,并且通过union
拼接
1 | -- union连接 |
5、将上面的结果学生信息表进行连接即可
好歹是实现了😭
1 | -- 最终脚本 |
和第25题相同的方法
1、以语文为例,首先我们找出前3名的成绩(包含相同的成绩)
1 | -- 语文 |
1 | -- 语文 |
在通过数学和英语的类似操作得到2、3名的成绩,再进行拼接即可
题目23
题目需求
统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SQL实现
自己的方法
1、如何对每个成绩进行分组展示:ABCD代表相应的等级
1 | select |
2、将两个表关联起来展示数据
1 | -- 1、查看全部课程和成绩信息 |
3、完整代码
1 | select |
参考方法
1、先统计每个阶段的人数和占比
1 | select |
注意对比:
2、我们将4种情况同时查出来
1 | select |
3、将科目名称连接起来
1 | -- 整体和自己的方法是类似的 |
题目24
题目需求
查询学生的平均成绩及名次
SQL实现
自己的方法
1、先求出每个人的平均分
1 | -- 自己的方法 |
2、我们对上面的结果进行排序
!!!MySQL5中是没有rank函数的,需要自己实现排序功能
1 | -- MYSQL5.7中没有rank函数,所以通过自连接实现 |
参考方法
1 | select |
实现rank函数
1 | select |
举例子来说明这个脚本:
姓名 | 成绩 |
---|---|
张三 | 89 |
李四 | 90 |
王五 | 78 |
小明 | 98 |
小红 | 60 |
- 当t1.s_score=89,满足t2.s_score > = t1.s_score的有98,90和89,此时count(distinct t2.s_score) 的个数就是3
- 当t1.s_score=90,满足t2.s_score > = t1.s_score的有98和90,此时count(distinct t2.s_score) 的个数就是2
- 当t1.s_score=78,满足t2.s_score > = t1.s_score的有98、90、89和78,此时count(distinct t2.s_score) 的个数就是4
- 当t1.s_score=98,满足t2.s_score > = t1.s_score的只有98,此时count(distinct t2.s_score) 的个数就是1
- 当t1.s_score=60,满足t2.s_score > = t1.s_score的有89、90、78、98、60,此时count(distinct t2.s_score) 的个数就是5
通过上面的步骤,我们发现:t1中每个分数对应的个数就是它的排名
题目25
题目需求
查询各科成绩前三名的记录
SQL实现
自己的方法
1、首先我们找出语文的前3名
1 | select s.s_id, s.s_score, c.c_name |
2、通过同样的方法我们可以求出数学和英语的前3条记录,然后通过union进行联结,有待优化😭
1 | -- 自己的脚本 |
参考方法
通过Score表的自连接,表a中的值小于表b中的值,排序之后我们取前3
1 | select |
我们通过语文
这个科目来理解上面的代码:前3名是80,80,76
1 | -- 语文 |
1 | -- 语文 |
题目26
题目需求
查询每门课被选修的学生数
SQL实现
1 | select |
题目27
题目需求
查询出只有两门课程的全部学生的学号和姓名
SQL实现
having条件是分组之后在执行的,where语句是分组前先执行的
1 | select |
题目28
题目需求
查询男女生人数
SQL实现
先看看数据:男女人数都是4
1 | -- 自己的方法 |
题目29
题目需求
查询名字中含有风
字的学生信息
SQL实现
先看看哪些同学的名字中有风
1 | -- 模糊匹配:我们在两边都加上了%,考虑的是姓或者名字含有风,虽然风姓很少见 |
题目30
题目需求
查询同名同性的学生名单,并统计同名人数
SQL实现
1、先看看班级的学生信息
现有的数据中没有同名的学生,但是当班级人数增多的时候很有可能在班级上出现同名的学生
2、假设有同名同性的学生
1 | select |
题目31
题目需求
查询1990年出生的学生信息
SQL实现
1 | select * |
题目32
题目需求
查询每门课程的平均成绩,结果按平均成绩降序排列;平均成绩相同时,按课程编号c_id升序排列
SQL实现
1 | -- 自己的方法 |
如果想带上课程的名称,需要和Course表进行联结
1 | -- 自己的方法 |
题目33
题目需求
查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SQL实现
1 | -- 自己的方法 |
题目34
题目需求
查询课程名称为数学,且分数低于60的学生姓名和分数
SQL实现
1 | select |
看看真正的数据,的确只有一个人满足
题目35
题目需求
查询所有学生的课程及分数情况
SQL实现
1 | select |
题目36
题目需求
查询任何一门课程成绩在70分以上的姓名、课程名称和分数
SQL实现
1 | select |
题目37
题目需求
查询不及格的课程
SQL实现
1 | select |
题目38
题目需求
查询课程编号为01且课程成绩大于等于80的学生的学号和姓名
SQL实现
1 | select |
题目39
题目需求
每门课程的学生人数
SQL实现
1 | select |
如果想连接到课程名称:
1 | -- 报错!!! |
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.c.c_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决的方法是将我们之前的结果作为临时表和Course表来连接查询:
1 | select |
题目40
题目需求
查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩
SQL实现
1、我们先找出张三老师教了哪些课程
1 | select |
2、找出哪些人修了数学
1 | select |
3、通过max函数找出成绩的最高分
1 | select |
4、连接Student表,找出学生信息
1 | select |
题目41
题目需求
查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SQL实现
1 | select |
我们对学号还需要去重下:
1 | select |
再看看原始的数据中是否符合要求:
题目42
题目需求
查询每门功成绩最好的前两名
SQL实现
自己的方法
还需要好好优化的😭
1 | -- 先找出语文的前2名同学 |
将3门学科的信息进行拼接即可求出答案:
1 | -- 最终脚本 |
参考方法(好方法)
如何解决前几名排序的问题🐂🍺🚗太牛了
1 | select |
首先我们看看真实的数据,我们以01课程来进行解释上面的代码:
符合要求count(1)<=2
的只有两种情况
还需要好好理解下😭
题目43
题目需求
统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SQL实现
1 | select |
题目44
题目需求
检索至少选修两门课程的学生学号
SQL实现
结果显示全部满足要求
1 | select |
题目45
题目需求
查询选修了全部课程的学生信息
SQL实现
自己的方法
1、全部的课程数目num
1 | select count(*) from Course; -- 总共3门 |
2、从Score表分组统计每个人的课程数目,满足是3的学生信息
1 | select |
3、我们找出上面结果中的学生信息即可
1 | select |
参考方法
1 | select * -- 3、s_id对应的学生信息 |
题目46
题目需求
查询各学生的年龄:按照出生日期来算,当前月日 < 出生年月的月日则,年龄减1
SQL实现
自己的方法
1 | -- 自己的方法 |
参考方法
1 | select |
如何返回年份/日期
通过date_format
函数能够指定返回的数据
1 | -- 两个方法 |
返回具体的日期:
题目47
题目需求
查询本周过生日的学生
1 | select week(now()); -- 47 |
- DAYOFWEEK(date)
返回日期date的星期索引(1=星期天,2=星期一, ……7=星期六),符合国内标准
- WEEKDAY(date)
返回date的星期索引(0=星期一,1=星期二, ……6= 星期天),国外标准
SQL实现
自己的方法
1 | select * |
参考方法
1 | select * from Student where week(date_format(now(),'%Y%m%d')) = week(s_birth); -- 方式1 |
题目48
题目需求
查询下周过生日的学生
SQL实现
1 | -- 自己的方法 |
边界问题
如果现在刚好的是今年的最后一个周,那么下周就是明年的第一个周,我们如何解决这个问题呢??改进后的脚本:
1 | -- 自己的方法 |
当现在刚好是第52周,那么mod函数的结果是0,则说明出生的月份刚好是明年的第一周
题目49
题目需求
查询本月过生的同学
SQL实现
1 | -- 自己的方法 |
返回的是空值,是因为数据本身就没有在11月份出生的同学
题目50
题目需求
查询下月过生的同学
SQL实现
1 | -- 自己的方法 |
边界问题
假设现在是12月份,那么下个月就是明年的1月份,我们如何解决???将上面的代码进行改进:
1 | select * from Student |
如果现在是12月份,则mod函数的结果是0,说明生日刚好是1月份