MySQL经典50题
笔者最近将网上流传的MySQL数据库经典50题进行了练习,梳理了一份自己的练习成果。下图是MySQL练习题中涉及到的4张表和它们的具体字段:
- 学生表
- 课程表
- 成绩表
- 教师表
所有的题目都是根据4张表来进行出题,涉及到了很多的MySQL/SQL
的知识点,希望对想提升SQL
的读者朋友有所帮助。
如果有不对或者还可以优化的地方欢迎提出来
建表语句
4张表是自己手动创建的,具体语句如下:
插入数据
在建表之后,我们需要往每个表中插入模拟数据:
时间相关
时间相关的问题中涉及到年月日、星期、季度等的求解,同时需要注意边界问题。下面是整理的几个常见的时间处理函数:
year()
:函数返回的是年份date_format(now(), '%Y%m%d')
:返回的是当前日期的年月日dayofyear()
:该函数返回的是当前日期处于一年中的第几天weekofyear()
:该函数返回的是该日期处于一年中的第几周week()
:同样也是返回当前日期处于一年中的第几周month()
:该函数返回的是月份,1-12dayofweek()
:该函数返回的是星期索引,1代表星期1,国内的惯例weekday()
:星期索引,0代表星期1,一般是国外的惯例
1 | -- 46、查询各学生的年龄:按照出生日期来算,当前月日 < 出生年月的月日则,年龄减1 |
having子句使用
having子句的作用是将数据筛选出来之后在加上条件进行二次筛选,通常是最后执行过滤条件
1 | -- 45、查询选修了全部课程的学生信息 |
多表连接查询
有时候我们需要的信息要通过几个表关联起来进行查询,这个时候就要用到SQL
的关联查询,主要是LEFT JOIN
方法比较多。
1 | -- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 |
排名问题(重点)
在SQL的提数需求中经常会遇到求解前几名,或者前几个数据的需要。由于MySQL 5.X中没有开窗函数,所以我们只能通过自己写SQL脚本来解决排名问题。
在MySQL8.0中则可以直接使用窗口函数来求解。
1 | -- 42、查询每门功课成绩最好的前3名 |
上面第19题的结果如下图:
最值问题
经常会在实际的工作需求中遇到最值问题的求解。
1 | -- 40、查询选修"张三"老师所授课程的学生中,成绩最高的学生信息及其成绩 |
统计count
在实际需求中我们需要统计 一些个数或者人数,用到的是count函数
1 | -- 39:每门课程的学生人数 |
where语句
where语句的功能是加入条件进行过滤选择。where是先过滤再进行选择筛选,having是完成分组聚合之后再进行过滤。
1 | -- 38、查询课程编号为02且课程成绩大于等于75的学生的学号和姓名 |
分组聚合
分组聚合统计在SQL中也是很常见的,需要用到group by和sum、max、min、count等聚合函数
1 | -- 35、所有学生的课程及分数情况 |
模糊匹配
在SQL
中模糊匹配使用的关键字是like
,符号是%
1 | -- 31、1991年出生的学生信息 |
case语句
case语句用来进行条件判断,下图中介绍了SQL中的两种case表达式的写法,来自《SQL进阶教程》一书:
1 | -- 23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比 |