网上广为流传的50道经典SQL
- 为了入职做准备，在网上找SQL题，最经典的是这50道。
- 花费两天时间搂完了，实在想不出来的就查，看别人写的就是不如自己写的得劲。
- 很多地方理解的不是很到位，勉强能懂。有需要优化或者不妥的地方希望得到广大大哥的批评指正，在评论区留言。
- 为了写这些题把不小心把电脑摔了，我TM…、、、、、
1 表结构介绍
数据表介绍
1.1 学生表
Student(SId,Sname,Sage,Ssex)
–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
1.2 课程表
Course(CId,Cname,TId)
–CId 课程编号,Cname 课程名称,TId 教师编号
1.3 教师表
Teacher(TId,Tname)
–TId 教师编号,Tname 教师姓名
1.4 成绩表
SC(SId,CId,score)
–SId 学生编号,CId 课程编号,score 分数
2 建表
2.1 学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');
2.2 课程表 course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
2.3 教师表 teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
2.4 成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);
- 注：以decimal(18,0)为例：18是定点精度，0是小数位数。
decimal(a,b)
a指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。
b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0。、
3 题目开搂
1. 查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
- 把所有的表关联起来，用到的字段信息查出来，然后条件加以限制即可。
SELECT student.*,c1.cname,s1.score,c2.cname,s2.score
FROM sc s1
JOIN sc s2 ON s1.SId = s2.SId
JOIN student ON s1.SId = student.SId
JOIN course c1 ON s1.CId = c1.CId
JOIN course c2 ON s2.CId = c2.CId
where s1.CId = '01' AND S2.CId = '02' AND s1.score > s2.score
a) 查询同时存在" 01 “课程和” 02 "课程的情况。
把所有的表关联起来，用到的字段信息查出来，然后条件加以限制。
SELECT student.*,c1.cname,s1.score,c2.cname,s2.score
FROM sc s1
JOIN sc s2 ON s1.SId = s2.SId
JOIN student ON s1.SId = student.SId
JOIN course c1 ON s1.CId = c1.CId
JOIN course c2 ON s2.CId = c2.CId
WHERE s1.CId = '01' AND s2.CId = '02'
b) 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
这里用到了子查询、左外连接的特点：
1. 在sc中查询出CID是01的数据单独作为表s1
2. 在sc中查询出CID是01的数据单独作为表s2
3. 使用左外连接left join s1->s2;
关联条件为学生编号SID相等；
这样左面的s1中的数据全部显示（即01的）；
右面的s2中存放的是CID为02的数据；
左面的全显示，右面的没有则为null
4. 关联课程表的时候也要使用left join原理如上述。目的是让02不存在的在课程名称（cname）一列也显示null。
SELECT student.*,c1.cname,s1.score,c2.cname,s2.score
FROM
(select * FROM sc where CId = '01') s1
left JOIN
(select * FROM sc where CId = '02') s2
ON s1.SId = s2.SId
JOIN student ON s1.SId = student.SId
left JOIN course c1 ON s1.CId = c1.CId
left JOIN course c2 ON s2.CId = c2.Cid
c) 查询不存在" 01 “课程但存在” 02 "课程的情况
不解释
SELECT student.*,c1.cname,s1.score,c2.cname,s2.score
FROM
(select * FROM sc where CId = '02') s1
left JOIN
(select * FROM sc where CId = '01') s2
ON s1.SId = s2.SId
JOIN student ON s1.SId = student.SId
left JOIN course c1 ON s1.CId = c1.CId
left JOIN course c2 ON s2.CId = c2.Cid
2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
需要注意的是：我们需要的查询得到的结果比如下面的avg(sc.score)是不可以作为查询条件放在where后面的，但是很好解决一个子查询取个别名搞定！
select * FROM
(SELECT student.SId,student.sname,avg(sc.score) a
from student
join sc on student.sid = sc.sid
GROUP BY sc.sid) t
where t.a >= 60
3. 查询在 SC 表存在成绩的学生信息
字段sid如果在子查询中出现过则返回1，作为where后的查询条件。
用到关键字in。
SELECT * from student where student.sid in (select sid from sc)
4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
1. 以后写SQL，一看到没有的显示为null，直接想到左外连接。
2. 以后用到什么特殊的可以先用子查询查出来（子查询是万能的）
select student.sid,student.sname,s.c,s.ss from student
LEFT JOIN
(select sid,count(sid) c,sum(score) ss from sc GROUP BY sid) s
on student.sid = s.sid
a) 查看有成绩的学生信息
感觉这个和第三题没什么区别，换种写法吧
select DISTINCT student.* from student RIGHT JOIN sc ON student.sid = sc.sid
5. 查询「李」姓老师的数量
关键词 like 用来表示模糊匹配：%（任意多个字符）_（任意单个字符）
SELECT count(*) 「李」姓老师的数量 from teacher where tname like '李%'
6. 查询学过「张三」老师授课的同学的信息
select student.* from student
join sc on student.sid = sc.sid
join (select * from teacher where tname = '张三') t on sc.cid = t.tid
7. 查询没有学全所有课程的同学的信息
a) 排除法 找出选全部课程的同学 用not in取补集。
b) 按照（学生编号）sid分组后将数目与课程所有数目(select count() from course)相等作为条件*
SELECT * FROM student where sid not IN
(
select sid from sc GROUP BY sid
having count(*) = (select count(*) from course)
)
8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
a) 表和表的关联也可以不用Join on 还可以多表查询然后关联
b) 至少有一门课相同；in存在即为TRUE
SELECT DISTINCT student.* from student,sc
where
student.sid = sc.sid
and
sc.cid in (select cid from sc where sid = '01')
9. 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
a) 用到了窗口函数 group_contact()。
i. 函数是为了通过分组把属于同一组的列的字段都列出来
ii. 这个先来个测试用例吧，看上面那话很难理解
iii. SELECT sid,GROUP_CONCAT(cid) from sc GROUP BY sid
b) 根据group_concat()的结果进行匹配，判断遍历得到的结果与01得到的结果进行判断。
c) 但是这里group_concat()里面我们又加了order by原因是：
i. 如果直接执行SELECT sid,GROUP_CONCAT(cid ) g from sc GROUP BY sid
ii. 返回结果为
SELECT student.* from student
JOIN
(SELECT sid,GROUP_CONCAT(cid order BY(cid)) g from sc GROUP BY sid) s
on student.sid = s.sid
where
s.g = (SELECT GROUP_CONCAT(cid order BY(cid)) from sc GROUP BY sid HAVING sid = '01')
AND
student.sid != '01'
10. 查询没学过"张三"老师讲授的任一门课程的学生姓名
a) 用一个排除法，找出学过张三教的课的人，然后not in
SELECT sname FROM student where sname not in
(
SELECT sname from student
join sc on student.sid = sc.sid
where sc.cid in
(SELECT DISTINCT sc.cid from sc,course,teacher
where
sc.cid = course.cid and
course.tid = teacher.tid AND
teacher.tname = '张三')
)
11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT student.sid,student.sname,avg(sc.score) FROM student
join sc on student.sid = sc.sid
GROUP BY sc.sid HAVING sc.sid in
(
SELECT s1.sid from sc s1
JOIN
(SELECT * FROM sc where score < 60) s2
ON
s1.sid = s2.sid and s1.cid = s2.cid
group by s1.sid HAVING count(s1.sid) >= 2
)
a) 黑色部分是核心，将sc表视为两个表，一个表记录着不及格成绩的字段
b) 另一个表只需要与这个表关联，条件为sid和cid都相等（保证了一致性）
c) 为了计数又用了group by。
12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
a) 关联后限制条件、根据成绩排序
select student.* from student
join sc on
student.sid = sc.sid and sc.score < 60 and sc.cid = '01'
order by sc.score desc
13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
a) 表的关联可以把信息串联起来，不过不用表的关联只使用group by那么每组只能显示一条数据
SELECT student.*,s1.cid,s1.score,s2.a from student
join sc s1 on student.sid = s1.sid
JOIN (SELECT sid,avg(score) a from sc group by sid) s2 on student.sid = s2.sid
order by s2.a desc
14. 查询各科成绩最高分、最低分和平均分：
SELECT cname,max(score),min(score),avg(score) from sc
join course on sc.cid = course.cid
GROUP BY sc.cid
15. 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
16. 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
a) 为了表示出百分数，使用concat连接两个字符串，一个是小数乘100倍，用left()函数截取一段；第二个字符串是‘%’
b) 算百分率我们可以使用JOIN使得在最后的结果中连起来
SELECT sc.cid 课程ID,cname 课程name,max(sc.score) 最高分,min(sc.score) 最低分,avg(sc.score) 平均分,
CONCAT(left(s2.c/s1.c*100,5),'%') 及格率,CONCAT(left(s3.c/s1.c*100,5),'%') 中等率,CONCAT(left(s4.c/s1.c*100,5),'%') 优良率,CONCAT(left(s5.c/s1.c*100,5),'%') 优秀率
from sc
join course on sc.cid = course.cid
JOIN (SELECT cid,count(*) c from sc GROUP BY cid) s1 on s1.cid = sc.cid
left join (SELECT cid,count(*) c from sc where score >= 60 GROUP BY cid ) s2 on s2.cid = sc.cid
left join (SELECT cid,count(*) c from sc where score >= 70 and score <= 80 GROUP BY cid ) s3 on s3.cid = sc.cid
left join (SELECT cid,count(*) c from sc where score > 80 and score < 90 GROUP BY cid) s4 on s4.cid = sc.cid
left join (SELECT cid,count(*) c from sc where score >= 90 GROUP BY cid) s5 on s5.cid = sc.cid
group by sc.cid
17. 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT cid,count(cid) from sc GROUP BY cid ORDER BY count(*) desc,cid asc
18. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select s1.cid,course.cname, s1.sid,student.sname, s1.score, count(s2.score)+1 as rank
from sc s1
left join sc s2
on s1.score<s2.score and s1.cid = s2.cid
join student on student.sid = s1.sid
join course on s1.cid = course.cid
group by s1.cid, s1.sid,s1.score
order by s1.cid, rank ASC;
a) 两个表，每次用s2中的score去比，计算比自己小的有多少个
b) 如果是排名第一那么比他小的有 0个所以前面那个count 需要加1
19. 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select s1.cid,course.cname, s1.sid,student.sname, s1.score, count(DISTINCT s2.score) as rank
from sc s1
left join sc s2
on s1.score<=s2.score and s1.cid = s2.cid
join student on student.sid = s1.sid
join course on s1.cid = course.cid
group by s1.cid, s1.sid,s1.score
order by s1.cid, rank ASC;
a) 这里用到的on后面的限制条件变成了小于等于，
b) 同时与关键词distinct去重相配合
c) 这样就可以把分数相同和比自大的都算到前面；同时还能保证相同的只占用一个名次
20. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT student.sname 姓名 , s1.*,count(s1.sumscore < s2.sumscore) rank from
(SELECT sid,sum(score) sumscore from sc GROUP BY sid) s1
JOIN student on student.sid = s1.sid
jOIN (SELECT sid,sum(score) sumscore from sc GROUP BY sid) s2 on s1.sumscore < s2.sumscore
GROUP BY s1.sid
ORDER BY rank asc
a) 最后别忘了分组 GROUP BY s1.sid
b) 因为主要用到的是sumscore 这个字段要用到两次；因此要写两次查出sum(score)的语句，并分别作为两张表
21. 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT student.sname 姓名 , s1.*,count(DISTINCT s2.sumscore) rank from
(SELECT sid,sum(score) sumscore from sc GROUP BY sid) s1
JOIN student on student.sid = s1.sid
jOIN (SELECT sid,sum(score) sumscore from sc GROUP BY sid) s2 on s1.sumscore <= s2.sumscore
GROUP BY s1.sid
ORDER BY rank asc
22. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
SELECT s0.cid 课程编号,cname 课程名称,
concat(left(s1.c/s0.c*100,5),'%') 100到85所占百分比,
concat(left(s2.c/s0.c*100,5),'%') 85到70所占百分比,
concat(left(s3.c/s0.c*100,5),'%') 70到60所占百分比,
concat(left(s4.c/s0.c*100,5),'%') 60到0所占百分比
from (SELECT cid ,count(cid) c from sc GROUP BY cid) s0
join course on s0.cid = course.cid
LEFT join (SELECT cid,count(cid) c from sc where score >= 85 and score <= 100 GROUP BY cid) s1 on s0.cid = s1.cid
LEFT join (SELECT cid,count(cid) c from sc where score >= 70 and score <= 85 GROUP BY cid) s2 on s0.cid = s2.cid
LEFT join (SELECT cid,count(cid) c from sc where score >= 60 and score <= 70 GROUP BY cid) s3 on s0.cid = s3.cid
LEFT join (SELECT cid,count(cid) c from sc where score <= 60 GROUP BY cid) s4 on s0.cid = s4.cid
GROUP BY s0.cid
a) 把每个区段的人数和总人数分别统计出来，分别用表来存
b) 需要的字段直接拼出来
23. 查询各科成绩前三名的记录
SELECT s0.* from (
SELECT course.cname cc ,s1.cid,student.sname,s1.sid,s1.score,COUNT(s2.score) +1 rank from sc s1
join student on student.sid = s1.sid
join course on course.cid = s1.cid
LEFT JOIN sc s2 on s1.cid = s2.cid and s2.score > s1.score
GROUP BY s1.sid,s1.cid ORDER BY s1.cid,rank
) s0
WHERE s0.rank <= 3
借鉴一下第18题
24. 查询每门课程被选修的学生数
SELECT sc.cid,cname,count(*) from sc join course on sc.cid = course.cid GROUP BY sc.cid
25. 查询出只选修两门课程的学生学号和姓名
SELECT s.id,s.sn from
(SELECT student.sid id,student.sname sn,count(*) c from student join sc on student.sid = sc.sid GROUP BY student.sid) s
where s.c = 2
26. 查询男生、女生人数
SELECT ssex,count(*) from student GROUP BY ssex
27. 查询名字中含有「风」字的学生信息
SELECT * from student where sname Like '%风%'
28. 查询同名同性学生名单，并统计同名人数
SELECT * from
(SELECT sname,count(*) c from student GROUP BY sname) s1
WHERE s1.c >= 2
29. 查询 1990 年出生的学生名单
SELECT sname from student where Sage like '1990%'
30. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT sc.cid,course.cname,avg(sc.score) a from sc join course on sc.cid = course.cid
GROUP BY sc.cid ORDER BY a desc,sc.cid asc
31. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
SELECT sc.sid, student.sname,avg(score) a from sc JOIN student on student.sid = sc.sid
GROUP BY sid HAVING a >= 85
32. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT student.sname , score from sc
join student on sc.sid = student.sid
JOIN course on course.cid = sc.cid
33. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT student.sid,student.sname,sc.cid,course.cname,score from student
LEFT JOIN sc on student.sid = sc.sid
LEFT JOIN course on course.cid = sc.cid
34. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT student.sname,course.cname,score from sc
join student on sc.sid = student.sid
JOIN course on course.cid = sc.cid
WHERE score > 70
35. 查询不及格的课程
SELECT student.sname,course.cname,score from sc
join student on sc.sid = student.sid
JOIN course on course.cid = sc.cid
WHERE score < 60
36. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT student.sid,student.sname from student
JOIN sc on student.sid = sc.sid
where sc.cid = '01' and score > 80
37. 求每门课程的学生人数
SELECT sc.cid,course.cname,COUNT(*) 学生人数 from sc
JOIN course on course.cid = sc.cid
GROUP BY sc.cid
38. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT student.*,max(score) from sc
JOIN student on sc.sid = student.sid
JOIN course on course.cid = sc.cid
join teacher on teacher.tid = course.tid
where tname = '张三'
39. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT student.*,score from sc
JOIN student on sc.sid = student.sid
JOIN course on course.cid = sc.cid
join teacher on teacher.tid = course.tid
where score in
(
SELECT max(score) from sc
JOIN student on sc.sid = student.sid
JOIN course on course.cid = sc.cid
join teacher on teacher.tid = course.tid
where tname = '张三'
)
40. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT st1.sid , s1.cid, s1.score,st2.sid , s2.cid, s2.score from sc s1
JOIN student st1 on st1.sid = s1.sid
JOIN sc s2 on s1.score = s2.score and s1.cid != s2.cid
JOIN student st2 on st2.sid = s2.sid
41. 查询每门功成绩最好的前两名
SELECT s0.* from (
SELECT course.cname,s1.cid,student.sname,s1.sid,s1.score,COUNT(s2.score) +1 rank from sc s1
join student on student.sid = s1.sid
join course on course.cid = s1.cid
LEFT JOIN sc s2 on s1.cid = s2.cid and s2.score > s1.score
GROUP BY s1.sid,s1.cid ORDER BY s1.cid,rank
) s0
WHERE s0.rank <= 2
42. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
SELECT sc.cid,course.cname,COUNT(*) 学生人数 from sc
JOIN course on course.cid = sc.cid
GROUP BY sc.cid
HAVING 学生人数 > 5
43. 检索至少选修两门课程的学生学号
SELECT s.sid from
(
select sid , count(*) c from sc
GROUP BY sid HAVING c >= 2
) s
44. 查询选修了全部课程的学生信息
a) 用到了第九题的方法 主要还是group_concat函数，是分组将同属于一个组的某个字段全部列出来
b) 参数中的参数函数order by是为了使得列出来的字段有序，保证一致性
SELECT * from
(SELECT student.* ,GROUP_CONCAT(DISTINCT sc.cid ORDER BY(sc.cid)) g from sc
JOIN student on student.sid = sc.sid GROUP BY sc.sid ) s
where s.g = (
SELECT GROUP_CONCAT(sc.cid ORDER BY(sc.cid)) FROM sc GROUP BY sid HAVING sid = '01'
)
45. 查询各学生的年龄，只按年份来算
*c) 上网查的资料，显示当前年份的sql为
d)
SELECT DATE_FORMAT(NOW(), '%Y')
e) 又用了字符串截取函数left
f) 又学了一个将字符串转成数字的好方法 直接后面加0*
SELECT *,(
SELECT DATE_FORMAT(NOW(), '%Y') - (LEFT(student.sage,4) + 0)
) 年龄 FROM student
46. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select student.SId as 学生编号,student.Sname as 学生姓名,
TIMESTAMPDIFF(YEAR,student.Sage,CURDATE()) as 学生年龄
from student
47. 查询本周过生日的学生
select *
from student
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())
48. 查询下周过生日的学生
select *
from student
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE()) + 1
49. 查询本月过生日的学生
select *
from student
where MONTH(student.Sage)=MONTH(CURDATE())
50. 查询下月过生日的学生
select *
from student
where MONTH(student.Sage)=MONTH(CURDATE()) + 1
153
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
