Loading... ## 知乎sql50题 [哔哩哔哩题目讲解](https://www.bilibili.com/video/av55971363?from=search&seid=17579256447436549270) [知乎题目](https://zhuanlan.zhihu.com/p/43289968) --- ## 表之间的联系 - 学生表:student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别 - 课程表:course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号 - 教师表:teacher(t_id,t_name) –教师编号,教师姓名 - 成绩表:score(s_id,c_id,s_s_score) –学生编号,课程编号,分数 ## 建表语句 ```sql -- 测试数据 --建表 --学生表 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 # 先把学生01和02的课程成绩找出来,01和02的成绩拼接成一张表,前提是学生id一样 select a.s_id "学号",c.s_name,a.s_score "01",b.s_score "02" from ( select * from score where c_id='01' ) as a INNER JOIN ( select * from score where c_id='02' ) as b on a.s_id=b.s_id INNER JOIN student as c on c.s_id=a.s_id WHERE a.s_score>b.s_score ``` 2. 查询平均成绩大于60分的学生的学号和平均成绩(简单,第二道重点) ```sql # 使用 groupby 是以having作为过滤条件的 select s_id,avg(s_score) from score GROUP BY s_id HAVING avg(s_score)>60 ``` 3. 查询所有学生的学号、姓名、选课数、总成绩(不重要) 统计一般用到 groupby,求个数 count,求和sum,使用groupby后,能出现在select中的只有统计值 ```sql select a.s_id,a.s_name,count(b.c_id),SUM(case when b.s_score is NULL then 0 else B.s_score END) from student as a LEFT JOIN score as b on a.s_id = b.s_id GROUP BY s_id,a.s_name ``` 4. 查询姓“张”的老师的个数(不重要) 主要考察like的使用,%的使用 ```sql select count(t_id) from teacher where t_name like '张%' #根据名字查询,除去同名的,去重 select count(DISTINCT t_name) from teacher where t_name like '张%' ``` 5. 查询没学过“张三”老师课的学生的学号、姓名(重点) 先查询出张三老师的id,查询成绩表中有张三老师教的课程编号,根据课程编号查询出选过这门课的学生id(学号),然后再从学生表中查根据学生id询出没有选过这门课的学生信息 ```sql select s_id,s_name from student WHERE s_id NOT IN( SELECT s_id from score WHERE c_id=( SELECT c_id from course WHERE t_id=( select t_id from teacher WHERE t_name='张三'))) #根据链接查询查询 select s_id,s_name from student where s_id not in( select s_id from score as s INNER JOIN course as c on s.c_id=c.c_id INNER JOIN teacher as t on c.t_id=t.t_id WHERE t.t_name='张三') ``` 6. 查询学过“张三”老师所教的所有课的同学的学号、姓名(重点) ```sql SELECT st.s_id,st.s_name,s.c_id,c.c_name,c.t_id,t.t_name FROM student as st INNER JOIN score as s on s.s_id=st.s_id INNER JOIN course as c on s.c_id=c.c_id INNER JOIN teacher as t on t.t_id=c.t_id WHERE t.t_name='张三' ORDER BY st.s_id ``` 7. 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(重点) 运用子查询,先用子查询查询出需要的数据,然后作为查询条件继续查询,工作中把子查询换为临时表即可 innerjoin很大程度上是求交集 ```sql select s_id,s_name FROM student WHERE s_id in ( select a.s_id FROM (select s_id FROM score WHERE c_id='01' ) as a INNER JOIN (select s_id FROM score WHERE c_id='02' )as b on a.s_id=b.s_id ) SELECT LENGTH('123') 结果为3 SELECT LENGTH(NULL) 结果为 NULL ``` 8. 查询课程编号为“02”的总成绩(不重点),其实很多统计都是直接在sql中完成的 ```sql select c_id,SUM(s_score),AVG(s_score),COUNT(DISTINCT s_id) FROM score GROUP BY c_id HAVING c_id='02' 第二种方法: select c_id,SUM(s_score),AVG(s_score),COUNT(DISTINCT s_id) FROM score WHERE c_id='02' ``` 9. 查询所有课程成绩小于60分的学生的学号、姓名 先查询小于60分的课程数 ,在统计一共学了几门课,做交集,有一样的说明都小于60分 ```sql select a.s_id,t.s_name from ( select s_id,COUNT(c_id) as cnt from score WHERE s_score<60 GROUP BY s_id ) as a INNER JOIN ( select s_id,COUNT(c_id) as cnt from score GROUP BY s_id )as b on a.s_id=b.s_id INNER JOIN student as t on a.s_id=t.s_id WHERE a.cnt=b.cnt ``` 10. 查询没有学全所有课的学生的学号、姓名(重点) 考生科目没有三门的说明没有学全,用分组查询即可,根据一共有几门课,小于这几门课分组说明没有学完 左连接左边的数据肯定会全部出现, ```sql select st.s_id,st.s_name from student as st LEFT JOIN score as sc on st.s_id=sc.s_id GROUP BY st.s_id HAVING COUNT(DISTINCT sc.c_id< (select COUNT(DISTINCT c_id) from course)) ``` 11. 查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名(重点) 先查询01学了什么课,然后查询选的课一样的学生id去掉重复的,最后在关联学生表,查询粗名称 ```sql select s_id,s_name from student where s_id in( select DISTINCT s_id from score where c_id in( select c_id from score where s_id='01') and s_id!='01') #改用 innerjoin 查询 select st.s_id,st.s_name from student as st INNER JOIN( select DISTINCT s_id from score where c_id in( select c_id from score where s_id='01') and s_id!='01') as b on st.s_id=b.s_id ``` 12. 查询和“01”号同学所学课程完全相同的其他同学的学号(重点) 01号学了01 02 03 ,选出所学的课不在这三门课中的人,判断剩下的人选的课程不在三门中的人, ```sql select * FROM student WHERE s_id in( select s_id FROM score WHERE s_id!='01' GROUP BY s_id HAVING COUNT(DISTINCT c_id)=(SELECT count(DISTINCT c_id) from score WHERE s_id='01') ) and s_id NOT in( select DISTINCT s_id FROM score WHERE c_id not in (select c_id from score WHERE s_id='01')) ``` 15. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩(重点) ```sql select a.s_id,a.s_name,AVG(b.s_score) FROM student as a INNER JOIN score as b on a.s_id=b.s_id WHERE a.s_id in( select s_id from score WHERE s_score<60 GROUP BY s_id HAVING COUNT(DISTINCT c_id)>=2) GROUP BY s_id,s_name ``` 16. 检索"01"课程分数小于60,按分数降序排列的学生信息(和34题重复,不重点)考察 orderby ```sql select t.*,s.s_score,s.c_id from student as t INNER JOIN score as s on t.s_id=s.s_id WHERE s.c_id='01' AND s.s_score<60 ORDER BY s.s_score DESC ``` 17. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩(重重点与35一样) ```sql SELECT s.s_id,s.s_score,s.c_id,b.avg_s_score FROM score as s INNER JOIN( select s_id,AVG(s_score) as avg_s_score FROM score GROUP BY s_id ) as b on s.s_id=b.s_id ORDER BY b.avg_s_score DESC #第二种 select s_id "学号" ,MAX(case WHEN c_id='01' THEN s_score ELSE NULL END) "语文" ,MAX(case WHEN c_id='02' THEN s_score ELSE NULL END) "数学" ,MAX(case WHEN c_id='03' THEN s_score ELSE NULL END) "英文" ,AVG(s_score) "平均成绩" FROM score GROUP BY s_id ORDER BY AVG(s_score) DESC ``` 18. 查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率--及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 (超级重点) ```sql select s.c_id, c.c_name, MAX(s.s_score), min(s.s_score), AVG(s.s_score), SUM(case when s.s_score>=60 then 1 else 0 END)/COUNT(s_id) "及格", SUM(case when s.s_score>=70 and s.s_score<80 then 1 else 0 END)/COUNT(s_id) "中等", SUM(case when s.s_score>=80 and s.s_score<90 then 1 else 0 END)/COUNT(s_id) "优良", SUM(case when s.s_score>=90 then 1 else 0 END)/COUNT(s_id) "优秀" FROM score as s INNER JOIN course as c on s.c_id=c.c_id GROUP BY c_id ``` 20. 查询学生的总成绩并进行排名(不重点) 字段别名就相当于是 as xx ,字段别名可以作为查询条件 ```sql select s_id "学号",sum(s_score) "总成绩" from score GROUP BY s_id ORDER BY 总成绩 DESC ``` 21. 查询不同老师所教不同课程平均分从高到低显示(不重点) ```sql # 以课程为主题,求平均分 select sc.c_id,avg(sc.s_score) as avg_score,c.c_name FROM score as sc INNER JOIN course as c on sc.c_id=c.c_id GROUP BY sc.c_id ORDER BY avg_score #以老师为主题,求平均分 SELECT t.t_id,t.t_name,AVG(sc.s_score) as avg_score from score as sc INNER JOIN course as c on sc.c_id=c.c_id INNER JOIN teacher as t on t.t_id=c.t_id GROUP BY t.t_id,t.t_name ORDER BY avg_score DESC ``` 23. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分数段人数:课程ID和课程名称(重点和18题类似) case when ```sql select c.c_id,c.c_name, sum(case when sc.s_score<=100 and sc.s_score >85 then 1 else 0 END) as "[100-85]", count(case when sc.s_score<=85 and sc.s_score >70 then 1 else NULL END) as "[85-70]", sum(case when sc.s_score<=70 and sc.s_score >=60 then 1 else 0 END) as "[70-60]", sum(case when sc.s_score<60 then 1 else 0 END) as "[<60]" FROM score as sc INNER JOIN course as c on sc.c_id=c.c_id GROUP BY c.c_id,c.c_name ``` 26. 查询每门课程被选修的学生数(不重点) ```sql select c.c_id,c.c_name,COUNT(DISTINCT sc.s_id) from score as sc INNER JOIN course as c on sc.c_id=c.c_id GROUP BY c.c_id,c.c_name ``` 27. 查询出只有两门课程的全部学生的学号和姓名(不重点) innerjoin 要比 in 效率高点 ```sql SELECT s_id,s_name FROM student WHERE s_id in( SELECT s_id FROM score GROUP BY s_id HAVING count( DISTINCT c_id)=2) #第二种 SELECT sc.s_id,st.s_name FROM score as sc INNER JOIN student as st on sc.s_id=st.s_id GROUP BY sc.s_id HAVING COUNT(DISTINCT sc.c_id)=2 ``` 28. 查询男生、女生人数(不重点) ```sql SELECT s_sex,count(s_id) from student GROUP BY s_sex # 第二种 SELECT s_sex,count(s_sex) from student GROUP BY s_sex # 第三种 select SUM(CASE when s_sex='男' then 1 else 0 END) "男生人数", SUM(CASE when s_sex='女' then 1 else 0 END) "女生人数" from student # 第四种 select count(CASE when s_sex='男' then 1 else NULL END) "男生人数", COUNT(CASE when s_sex='女' then 1 else NULL END) "女生人数" from student ``` 29. 查询名字中含有"风"字的学生信息(不重点) like 模糊查询, %为通配符 ```sql select * FROM student WHERE s_name like '%风%' ``` 31. 查询1990年出生的学生名单(重点year)year函数 ```sql select * FROM student WHERE YEAR(s_birth)=1990 # select MONTH(CURRENT_DATE()) #select MONTH('2020-1-16') #时间的几个格斯 YYYY-MM-DD YYYYMMDD YYY/MM/DD YYMMDD ``` 32. 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩(不重要) ```sql #统计函数需要用having,不是统计函数直接先用where过滤条件之后使用groupby分组 select a.s_id,a.s_name,b.avg_score FROM student as a INNER JOIN( select s_id,AVG(s_score) as avg_score FROM score GROUP BY s_id HAVING avg_score>=85)as b on a.s_id=b.s_id ``` 33. 查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列(不重要) ```sql select s.c_id,c.c_name,AVG(s_score) as avg_score from score as s INNER JOIN course as c on s.c_id=c.c_id GROUP BY s.c_id ORDER BY avg_score asc ,s.c_id DESC ``` 34. 查询课程名称为"数学",且分数低于60的学生姓名和分数(不重点) ```sql select sc.s_id,sc.s_score,st.s_name from score as sc INNER JOIN course as c on sc.c_id=c.c_id INNER JOIN student as st on st.s_id=sc.s_id where c.c_name='数学' and sc.s_score<60 ``` 35. 查询所有学生的课程及分数情况(重点) 用case when 之后,往往都会和统计函数在一起使用 ```sql select st.s_id,st.s_name, max(case when c.c_name='语文' then s.s_score else NULL END) as '语文', max(case when c.c_name='数学' then s.s_score else NULL END) as '数学', max(case when c.c_name='英语' then s.s_score else NULL END) as '英语' from score as s INNER JOIN course as c on s.c_id=c.c_id INNER JOIN student as st on st.s_id=s.s_id GROUP BY st.s_id,st.s_name ``` 36. 查询课程成绩在70分以上的课程名称,分数和学生姓名 ```sql #一对一的操作,innerjoin不会产生笛卡尔积的结果,如果是多对多,可能会产生笛卡尔积的结果 select s.s_id,st.s_name,c.c_name,s.s_score from score as s INNER JOIN course as c on s.c_id=c.c_id INNER JOIN student as st on st.s_id=s.s_id where s.s_score>70 ``` 37. 查询不及格的课程并按课程号从大到小排列(不重点) ```sql select s.s_id,st.s_name,c.c_id,c.c_name,s.s_score from score as s INNER JOIN course as c on s.c_id=c.c_id INNER JOIN student as st on st.s_id=s.s_id where s.s_score<60 ORDER BY c.c_id DESC ``` 38. 查询课程编号为03且课程成绩在80分以上的学生的学号和姓名(不重要) ```sql SELECT st.s_id,st.s_name,sc.c_id,c.c_name,sc.s_score FROM score as sc INNER JOIN student as st on sc.s_id=st.s_id INNER JOIN course as c on c.c_id=sc.c_id WHERE sc.c_id='03' and sc.s_score>80 ``` 39. 求每门课程的学生人数(不重要) ```sql select c.c_id,c.c_name,COUNT(DISTINCT s_id) FROM score as sc INNER join course as c on c.c_id=sc.c_id GROUP BY c_id,c.c_name ``` 40. 查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩(重要top) ```sql select sc.s_id,st.s_name,sc.s_score,c.c_id,c.c_name,t.t_name FROM score as sc INNER JOIN course as c on sc.c_id=c.c_id INNER JOIN teacher as t on c.t_id=t.t_id INNER JOIN student as st on st.s_id=sc.s_id WHERE t.t_name='张三' ORDER BY sc.s_score DESC LIMIT 0,1 ``` 41. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 (重点) ```sql select s_id from (SELECT b.s_id,b.s_score from score as b INNER JOIN(SELECT s_id FROM score GROUP BY s_id having count(DISTINCT c_id)>1) as c on b.s_id=c.s_id GROUP BY b.s_id,b.s_score ) as a GROUP BY s_id HAVING count(s_id)=1 ``` 42. 查询每门功成绩最好的前两名(同22和25题) ```sql ``` 43. 统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列(不重要) ```sql select c_id,COUNT(DISTINCT s_id) as cnt FROM score GROUP BY c_id HAVING cnt>5 ORDER BY cnt DESC,c_id ASC ``` 44. 检索至少选修两门课程的学生学号(不重要) ```sql SELECT s_id,COUNT(DISTINCT c_id) as cnt FROM score GROUP BY s_id HAVING cnt>=2 ``` 45. 查询选修了全部课程的学生信息(重点划红线地方) ```sql select * from student WHERE s_id in ( select s_id FROM score GROUP BY s_id HAVING COUNT(DISTINCT c_id)=(select count(c_id) from course)) ``` 46. 查询各学生的年龄(精确到月份)DATEDIFF()函数的运用,floor向下取整 ```sql SELECT s_id,s_birth,floor( DATEDIFF('2020-1-16',s_birth)/365) from student ``` 47. 查询没学过“张三”老师讲授的任一门课程的学生姓名(还可以,自己写的,答案中没有) ```sql SELECT * FROM student WHERE s_id not in( SELECT sc.s_id FROM score as sc INNER JOIN course as c on sc.c_id=c.c_id INNER JOIN teacher as t on t.t_id=c.t_id WHERE t.t_name='张三') ``` 48. 查询下周过生日的同学 ```sql #有一定的漏洞,问题 select * FROM student where week(s_birth,1)=WEEK(date(now()),1)+1 ``` 49. 查询本月过生日的人 ```sql #主要用到了month函数 select * from student where MONTH(s_birth)=MONTH(now()) ``` 50. 查询下个月过生日的同学 ```sql #select now(); 当前时间包括年月日 #select MONTH(now()) 当前日期月份; #select date(now()); 当前日期 select * from student where case when MONTH(s_birth)=12 then MONTH(s_birth)+1 else MONTH(s_birth)=MONTH(now())+1 END ``` 最后修改:2020 年 08 月 27 日 © 允许规范转载 打赏 赞赏作者 支付宝微信 赞 哇卡哇卡
此处评论已关闭