| 超经典MySQL练习50题，做完这些你的SQL就过关了！ | 您所在的位置：网站首页 › mysql数据库题库和答案的区别是什么意思 › 超经典MySQL练习50题，做完这些你的SQL就过关了！ |
超经典MySQL练习50题，做完这些你的SQL就过关了！
|
相信大多学习了 Mysql 数据库语言的同学都会上网找练习来练手，而大部分的人肯定知道有一篇 Mysql 经典练习题50题的帖子，上面的题目基本上涵盖了 Mysql 查询语句的关键知识点。 笔者近期对又将这 50 题进行了练习，同时整理了相关的参考答案，有自己的思路和方法，也有参考大神们的。不得不说，这50题对SQL的提升真的很有帮助！ 笔者使用的 MySQL 版本 是 MySQL 5.7.28 。 鉴于 50 题篇幅太长，本文只展示了其中10题及笔者的思考，50 题完整版练习题以及笔者的答案实践已整理在pdf文件中，共有100多页，在文末提供获取的方法。  建表和插入数据在开始之前，先建立本文所需要的数据表格： 代码语言：javascript复制-- 建表 -- 学生表 CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(`s_id`) ); -- 课程表 CREATE TABLE `Course`( `c_id` VARCHAR(20), `c_name` VARCHAR(20) NOT NULL DEFAULT '', `t_id` VARCHAR(20) NOT NULL, PRIMARY KEY(`c_id`) ); -- 教师表 CREATE TABLE `Teacher`( `t_id` VARCHAR(20), `t_name` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(`t_id`) ); -- 成绩表 CREATE TABLE `Score`( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY(`s_id`,`c_id`) ); -- 插入学生表测试数据 insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'); insert into Student values('03' , '孙风' , '1990-05-20' , '男'); insert into Student values('04' , '李云' , '1990-08-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '女'); insert into Student values('06' , '吴兰' , '1992-03-01' , '女'); insert into Student values('07' , '郑竹' , '1989-07-01' , '女'); insert into Student values('08' , '王菊' , '1990-01-20' , '女'); -- 课程表测试数据 insert into Course values('01' , '语文' , '02'); insert into Course values('02' , '数学' , '01'); insert into Course values('03' , '英语' , '03'); -- 教师表测试数据 insert into Teacher values('01' , '张三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五'); -- 成绩表测试数据 insert into Score values('01' , '01' , 80); insert into Score values('01' , '02' , 90); insert into Score values('01' , '03' , 99); insert into Score values('02' , '01' , 70); insert into Score values('02' , '02' , 60); insert into Score values('02' , '03' , 80); insert into Score values('03' , '01' , 80); insert into Score values('03' , '02' , 80); insert into Score values('03' , '03' , 80); insert into Score values('04' , '01' , 50); insert into Score values('04' , '02' , 30); insert into Score values('04' , '03' , 20); insert into Score values('05' , '01' , 76); insert into Score values('05' , '02' , 87); insert into Score values('06' , '01' , 31); insert into Score values('06' , '03' , 34); insert into Score values('07' , '02' , 89); insert into Score values('07' , '03' , 98); 题目1题目要求查询"01"课程比"02"课程成绩高的学生的信息及课程分数 SQL实现代码语言：javascript复制-- 方法1 select a.* ,b.s_score as 1_score ,c.s_score as 2_score from Student a join Score b on a.s_id = b.s_id and b.c_id = '01' -- 两个表通过学号连接，指定01 left join Score c on a.s_id = c.s_id and c.c_id='02' or c.c_id is NULL -- 指定02，或者c中的c_id直接不存在 -- 为NULL的条件可以不存在，因为左连接中会直接排除c表中不存在的数据，包含NULL where b.s_score > c.s_score; -- 判断条件 -- 方法2：直接使用where语句 select a.* ,b.s_score as 1_score ,c.s_score as 2_score from Student a, Score b, Score c where a.s_id=b.s_id -- 列出全部的条件 and a.s_id=c.s_id and b.c_id='01' and c.c_id='02' and b.s_score > c.s_score; -- 前者成绩高 第二种方法实现：   题目2题目要求查询"01"课程比"02"课程成绩低的学生的信息及课程分数（题目1是成绩高） SQL实现类比题目1的实现过程 代码语言：javascript复制-- 方法1：通过连接方式实现 select a.* ,b.s_score as 1_score ,c.s_score as 2_score from Student a left join Score b on a.s_id=b.s_id and b.c_id='01' or b.c_id=NULL -- 包含NULL的数据 join score c on a.s_id=c.s_id and c.c_id='02' where b.s_score < c.s_score; -- 通过where子句实现 select a.* ,b.s_score as 1_score ,c.s_score as 2_score from Student a, Score b, Score c where a.s_id=b.s_id and a.s_id=c.s_id and b.c_id='01' and c.c_id='02' and b.s_score < c.s_score; -- 前者比较小 题目3题目需求查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩 SQL实现代码语言：javascript复制-- 执行顺序：先执行分组，再执行avg平均操作 select b.s_id ,b.s_name ,round(avg(a.s_score), 2) as avg_score from Student b join Score a on b.s_id = a.s_id group by b.s_id -- 分组之后查询每个人的平均成绩 having avg_score >= 60; -- 附加题：总分超过200分的同学 select b.s_id ,b.s_name ,round(sum(a.s_score),2) as sum_score -- sum求和 from Student b join Score a on b.s_id=a.s_id group by b.s_id having sum_score > 200; 附加题：总分超过200分的同学  题目4题目要求查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩（包括有成绩的和无成绩的） SQL实现1-两种情况连接平均分小于60 代码语言：javascript复制select b.s_id ,b.s_name ,round(avg(a.s_score), 2) as avg_score -- round四舍五入函数 from Student b join Score a on b.s_id = a.s_id group by b.s_id -- 分组之后查询每个人的平均成绩 having avg_score < 60;结果为：  没有成绩的同学： 代码语言：javascript复制select a.s_id ,a.s_name ,0 as avg_score from Student a where a.s_id not in ( -- 学生的学号不在给给定表的学号中 select distinct s_id -- 查询出全部的学号 from Score );  最后将两个部分的结果连起来即可：通过union方法  SQL实现2-ifnull函数判断使用ifnull函数 代码语言：javascript复制select S.s_id ,S.s_name ,round(avg(ifnull(C.s_score,0)), 2) as avg_score -- ifnull 函数：第一个参数存在则取它本身，不存在取第二个值0 from Student S left join Score C on S.s_id = C.s_id group by s_id having avg_score < 60;使用null判断 代码语言：javascript复制select a.s_id ,a.s_name ,ROUND(AVG(b.s_score), 2) as avg_score from Student a left join Score b on a.s_id = b.s_id GROUP BY a.s_id HAVING avg_score < 60 or avg_score is null; -- 最后的NULL判断 题目5题目需求查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 SQL实现代码语言：javascript复制select a.s_id ,a.s_name ,count(b.c_id) as course_number -- 课程个数 ,sum(b.s_score) as scores_sum -- 成绩总和 from Student a left join Score b on a.s_id = b.s_id group by a.s_id,a.s_name; 题目6题目需求查询“李”姓老师的数量 SQL实现代码语言：javascript复制select count(t_name) from Teacher where t_name like "李%"; -- 通配符 这题怕是最简单的吧? 题目7题目需求查询学过张三老师授课的同学的信息 SQL实现代码语言：javascript复制-- 方法1：通过张三老师的课程的学生来查找；自己的方法 select * -- 3. 通过学号找出全部学生信息 from Student where s_id in ( select s_id -- 2.通过课程找出对应的学号 from Score S join Course C on S.c_id = C.c_id -- 课程表和成绩表 where C.t_id=(select t_id from Teacher where t_name="张三") -- 1.查询张三老师的课程 ); -- 方法2：通过张三老师的课程来查询 select s1.* from Student s1 join Score s2 on s1.s_id=s2.s_id where s2.c_id in ( select c_id from Course c where t_id=( -- 1. 通过老师找出其对应的课程 select t_id from Teacher t where t_name="张三" ) ) -- 方法3 select s.* from Teacher t left join Course c on t.t_id=c.t_id -- 教师表和课程表 left join Score sc on c.c_id=sc.c_id -- 课程表和成绩表 left join Student s on s.s_id=sc.s_id -- 成绩表和学生信息表 where t.t_name='张三';自己的方法：  方法2来实现：  方法3实现：  题目8题目需求找出没有学过张三老师课程的学生 SQL实现代码语言：javascript复制select * -- 3. 通过学号找出全部学生信息 from Student where s_id not in ( -- 2.通过学号取反：学号不在张三老师授课的学生的学号中 select s_id from Score S join Course C on S.c_id = C.c_id where C.t_id=(select t_id from Teacher where t_name ="张三") -- 1.查询张三老师的课程 ); -- 方法2： select * from Student s1 where s1.s_id not in ( select s2.s_id from Student s2 join Score s3 on s2.s_id=s3.s_id where s3.c_id in( select c.c_id from Course c join Teacher t on c.t_id=t.t_id where t_name="张三" ) ); -- 方法3 select s1.* from Student s1 join Score s2 on s1.s_id=s2.s_id where s2.c_id not in ( select c_id from Course c where t_id=( -- 1. 通过老师找出其对应的课程 select t_id from Teacher t where t_name="张三" ) ); 方法2：  题目9题目需求查询学过编号为01，并且学过编号为02课程的学生信息 SQL实现代码语言：javascript复制-- 自己的方法：通过自连接实现 select s1.* from Student s1 where s_id in ( select s2.s_id from Score s2 join Score s3 on s2.s_id=s3.s_id where s2.c_id='01' and s3.c_id='02' ); -- 方法2：直接通过where语句实现 select s1.* from Student s1, Score s2, Score s3 where s1.s_id=s2.s_id and s1.s_id=s3.s_id and s2.c_id=01 and s3.c_id=02; -- 方法3：两个子查询 -- 1. 先查出学号 select sc1.s_id from (select * from Score s1 where s1.c_id='01') sc1, (select * from Score s1 where s1.c_id='02') sc2 where sc1.s_id=sc2.s_id; -- 2.找出学生信息 select * from Student where s_id in (select sc1.s_id -- 指定学号是符合要求的 from (select * from Score s1 where s1.c_id='01') sc1, (select * from Score s1 where s1.c_id='02') sc2 where sc1.s_id=sc2.s_id); 先从Score表中看看哪些人是满足要求的：01-05同学是满足的 通过自连接查询的语句如下：  查询出学号后再匹配出学生信息：  通过where语句实现：  方法3的实现：  题目10题目需求查询学过01课程，但是没有学过02课程的学生信息（注意和上面?题目的区别） SQL实现首先看看哪些同学是满足要求的：只有06号同学是满足的  错误思路1直接将上面一题的结果全部排出，导致那些没有学过01课程的学生也出现了：07，08 代码语言：javascript复制select s1.* from Student s1 where s_id not in ( -- 直接将上面一题的结果全部排出，导致那些没有学过01课程的学生也出现了：07，08 select s2.s_id from Score s2 join Score s3 on s2.s_id=s3.s_id where s2.c_id='01' and s3.c_id ='02' ); 错误思路2将上面题目中的02课程直接取反，导致同时修过01，02，03或者只修01，03的同学也会出现 代码语言：javascript复制select s1.* from Student s1 where s_id in ( select s2.s_id from Score s2 join Score s3 on s2.s_id=s3.s_id where s2.c_id='01' and s3.c_id !='02' -- 直接取反是不行的，因为修改(01,02,03)的同学也会出现 ); 正确思路https://www.jianshu.com/p/9abffdd334fa 代码语言：javascript复制-- 方法1：根据两种修课情况来判断 select s1.* from Student s1 where s1.s_id in (select s_id from Score where c_id='01') -- 修过01课程，要保留 and s1.s_id not in (select s_id from Score where c_id='02'); -- 哪些人修过02，需要排除  ！！！！！方法2：先把06号学生找出来 代码语言：javascript复制select * from Student where s_id in ( select s_id from Score where c_id='01' -- 修过01课程的学号 and s_id not in (select s_id -- 同时学号不能在修过02课程中出现 from Score where c_id='02') );  鉴于篇幅，本文只展示了50题中的10道题的答案以及笔者的实践心得。 50道题的标题整理如下： 
|
【本文地址】
公司简介
联系我们