SQL主要操作有增、删、改、查这四类，但在实际工作中，可能遇到“查”是最多的，因此，本文的习题也将集中在查询部分。

本文将对sql面试50题打乱顺序按模块进行重组(保留原标序号码，方便各位童鞋对比答案)，由简入难，并用“★”标注最典型题目，时间不够的童鞋们专就做“★”的题目就好啦！

本文结构如下：一、简单的sql基础知识

1 SQL的结构

2 代码执行顺序

2.1 From作用

2.2 Where作用

2.3 Group by作用

3 聚合函数

3.1 COUNT() 函数

二、创建四张表

1 student表

2 sc成绩表

3 Course表

4 Teacher表

三、习题演练

如果觉得本文有用的话麻烦点赞、收藏哦~

一、 sql基础知识

1 SQL的结构

SQL的结构体现在代码书写完成后的顺序，具体结构如下：Select…from… where…

group by…

hanving…

order by…

limit…

2 代码执行顺序

代码的执行顺序与SQL的结构不尽相同，只有对代码执行顺序熟悉才能写出正确的程序，请大家务必记住按以下顺序进行书写，在刚开始的前几个案例中我也会为大家注释顺序，方便大家理解。From

Where

group by

select

having

order by

limit

2.1 From作用

将表中的数据获取过来，在from里的子查询必须有别名。举例如下：

Select stu.name

From stu join

(select * from class where manager=’蓝’) As c

On stu.classid=c.id;

若采用join的方法，代码区别如下：

Select stu.name

From stu join class

On stu.classid=c.id

Where manager=’蓝 ’

2.2 Where作用

对from表中的数据进行过滤，符合条件的数据留下，不符合的丢弃，可配合and or,!=进行使用。

2.3 Group by作用

Group by classid，按classid进行分组，分组后，遇到select，则代表输出这一组的第一行，Group by还可以和聚合函数放在一起。

3 聚合函数

是一个集合为输入、返回单个值的函数，常见聚合函数如下所示：Count()

Sum()

Max()

Min()

Avg()

Group_concat() 字符串

3.1 COUNT() 函数

COUNT() 函数返回匹配指定条件的行数，具体区别可举例 ：Count(*) 都计入

Count(name) 去除null值

Count(distinct name) 去重去null

二、创建四张表

习题涉及学生表，成绩表，课程表及教师表，具体关键字段联系如下图所示：

1.student表((SId,Sname,Sage,Ssex)创建

表格创建中，一般来说，如果含有中文字符用nvarchar，纯英文和数字，用varchar，具体代码如下：

CREATE TABLE Student(SId VARCHAR(10),Sname nvarchar(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.sc成绩表(SId，CId，score)创建

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);

3.Course表(CId，Cname，TId)创建

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');

4.Teacher表创建--尝试采用navicat软件操作选中“表”→右击→新建表按需求填写表名、类型、长度→保存→输入表名

创建完表格后可以在软件中直接插入数据，当然也可以采用输入代码方式插入数据，代码如下：

INSERT INTO Teacher values('01','张三');

INSERT INTO Teacher values('02','李四');

INSERT INTO Teacher values('03','王五');

运行后发现程序报错：ERROR 1366 (HY000): Incorrect string value，主要是因为格式问题，采用如下代码进行调整格式即可。

ALTER TABLE Teacher CHANGE TId TId VARCHAR(10) CHARACTER SET utf8;

ALTER TABLE Teacher CHANGE Tname Tname VARCHAR(10) CHARACTER SET utf8;

INSERT INTO Teacher values('01','张三');

INSERT INTO Teacher values('02','李四');

INSERT INTO Teacher values('03','王五');

至此，已完成4张表的创建，结果如下：student表 sc表course表 teacher表

三、习题演练

1 GROUP BY

往往与聚合函数配合在一起使用，这里先简单举几个例子，复杂案例可以在聚合函数中感受。

★19. 查询每门课程被选修的学生数

SELECT cid,count(sid) --3 筛选FROM sc --1 确定来源GROUP BY cid --2 分组

★21. 查询男生、女生人数

SELECT ssex,COUNT(1) AS '人数' --3 筛选FROM student stu --1 确定来源GROUP BY ssex --2 分组

32.求每门课程的学生人数--其实和19题是一样的问题

SELECT cid,count(1) as '学生人数' --3 筛选FROM sc --1 确定来源GROUP BY cid --2 分组

2 HAVING

★37. 统计每门课程的学生选修人数(超过 5 人的课程才统计)。

SELECT cid,count(1) --3 筛选FROM sc --1 确定来源GROUP BY cid --2 分组having count(1)>5 --4 筛选超过5人课程

38. 检索至少选修两门课程的学生学号--本质同37题

SELECT sid,count(1)

FROM sc

GROUP BY sid

HAVING count(1)>=2

★39.查询选修了全部课程的学生信息

SELECT s.*

FROM sc JOIN student s ON sc.sid=s.sid

GROUP BY 1,2,3,4

HAVING COUNT(1)=3

20. 查询出只选修两门课程的学生学号和姓名

SELECT stu.sid,stu.sname

FROM student stu JOIN sc ON stu.sid=sc.sid

GROUP BY 1

HAVING COUNT(1)=2

★23. 查询同名同姓学生名单，并统计人数

SELECT stu.sname,count(stu.sname) as cnt_samesname

FROM student stu

GROUP BY sname

HAVING cnt_samesname>1

3 order by

25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编

SELECT sc.cid,avg(score) as average --3 筛选FROM sc --1 确定范围GROUP BY 1 --2 分组ORDER BY average DESC,sc.cid ASC; --4 排序

12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息

法一，用内连接，查01课程和分数小于60的学生的sid,再降序排

SELECT s.*

FROM student s JOIN sc ON s.sid=sc.sid

WHERE cid='01' AND score < 60

ORDER BY score DESC;

法二：先选出01课程和分数小于60的学生的sid，并按分数降序排列，再至student表筛选对应学生信息。

注意：因order by 的执行顺序晚于select语句，因此，本案例的order by是放在子查询内部先处理的，若放到外面则会报错，理由是student表内已无score列可供排序。

SELECT *

FROM student

WHERE sid IN(

SELECT sid FROM sc

WHERE cid='01' AND score <60

ORDER BY score DESC );

13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT sc.*,a.average

FROM sc LEFT JOIN

(SELECT sid,AVG(score) AS average FROM sc

GROUP BY sid) AS a

ON sc.sid=a.sid

ORDER BY a.average DESC;

15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

思路：将sc表进行自连接，对分数进行排序

SELECT s1.cid, s1.sid, s1.score, count(s2.score) + 1 as 排名

FROM sc s1

LEFT JOIN sc s2 ON s1.cid = s2.cid AND s1.score < s2.score

GROUP BY s1.cid,s1.sid, s1.score

ORDER BY s1.cid, count(s2.score) + 1 ASC;

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

SELECT sc.SId, sc.CId, sc.score, tp.ranks

FROM sc LEFT JOIN

(SELECT SId, CId,

(SELECT COUNT(DISTINCT sc2.score) + 1

FROM sc sc2

WHERE sc1.CId = sc2.CId

AND sc2.score > sc1.score) ranks

FROM sc sc1) tp

ON sc.SId = tp.SId AND sc.CId = tp.CId

ORDER BY sc.CId , ranks;

16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺

set @rank=0;

SELECT a.*, @rank := @rank + 1 as rank_no

FROM

(SELECT sid, sum(score) AS 总成绩

FROM sc

GROUP BY sid

ORDER BY sum(score) DESC)a;

4 limit

33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

这题用到了limit和排序来筛选出成绩最高的学生

SELECT s.*,sc.score

FROM course c JOIN sc ON c.cid=sc.cid

JOIN student s ON s.sid=sc.sid

JOIN teacher t ON t.tid=c.tid

WHERE t.tname='张三'

ORDER BY score DESC

LIMIT 1

5.聚集函数应用(SUM/COUNT/AVG等)

★11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT st.SId,sname,AVG(score)

FROM student st,sc

WHERE st.SId=sc.SId

AND sc.score < 60

GROUP BY sc.SId

HAVING count(*)>=2

14.查询各科成绩最高分、最低分和平均分,以如下形式显示：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90，要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。

SELECT c.cid,c.cname,MAX(score) AS 最高分,MIN(score) AS 最低分,AVG(score) AS 平均分,

SUM(CASE WHEN score >=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格率,

SUM(CASE WHEN score >=70 AND score <80 THEN 1 ELSE 0 END)/COUNT(*) AS 中等率,

SUM(CASE WHEN score >=80 AND score <90 THEN 1 ELSE 0 END)/COUNT(*) AS 优良率,

SUM(CASE WHEN score >=90 THEN 1 ELSE 0 END)/COUNT(*) AS 优秀率,

COUNT(1) AS 选修人数

from course c JOIN sc ON c.cid=sc.cid

GROUP BY cid

ORDER BY count(*) DESC,c.cid;

4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

思路：以student表为主，因为要求没成绩的显示为NUll，所以需要用左连接，用COUNT和SUM函数计算成绩。

SELECT s.SId,s.Sname,COUNT(CId) AS '选课总数',sum(score) AS '总成绩' --3选择

FROM student s LEFT JOIN sc ON s.sid = sc.sid --1 连接

GROUP BY sc.SId; --2 分组

17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 所占百分比

求占比思路同14题较为接近。

SELECT sc.cid,c.cname,

SUM(CASE WHEN score>= 85 THEN 1 ELSE 0 END )/count(*) AS z1,

SUM(CASE WHEN score>= 70 AND score <85 THEN 1 ELSE 0 END )/count(*) AS z2,

SUM(CASE WHEN score>= 60 AND score <70 THEN 1 ELSE 0 END )/count(*) AS z3,

SUM(CASE WHEN score< 60 THEN 1 ELSE 0 END )/count(*) AS z4

FROM sc JOIN course c ON sc.cid=c.cid

GROUP BY 1,2

30. 查询不及格的课程

--此题题意感觉不是说得很清楚，我把结果表示成课程名称以及该科目不及格的人数，具体如下：

SELECT c.cname,count(1) as '不及格人数'

FROM sc JOIN course c ON sc.cid=c.cid

WHERE sc.score<60

GROUP BY c.cname

18. 查询各科成绩前三名的记录

SELECT sc1.cid,sc1.sid,sc1.score,COUNT(sc2.sid)+1 AS rank --3筛选

FROM sc sc1 LEFT JOIN sc sc2 ON (sc1.cid=sc2.cid AND sc1.score

GROUP BY 1,2,3 --2 分组

HAVING rank <=3 --4 筛选数量

ORDER BY sc1.cid asc,sc1.score DESC --5 排序

这道题自己还掉过一个坑，我刚开始的代码是这样的，注意差距在于会将sc1.score

SELECT sc1.cid,sc1.sid,sc1.score,COUNT(sc2.sid)+1 AS rank

FROM sc sc1 LEFT JOIN sc sc2 ON sc1.cid=sc2.cid

WHERE sc1.score

GROUP BY 1,2,3

HAVING rank <=3

ORDER BY sc1.cid asc,sc1.score DESC

6 日期函数

主要涉及year(),month(),week(),now(),DATE_FORMAT()用法，如果下面3题标五角星的题目都会做了，剩余的4题代表你也都会了，轻松少做57%的题！

★45.查询下月过生日的学生

SELECT sname

FROM student

WHERE MONTH(now())+1 =MONTH(sage);

★43. 查询下周过生日的学生

SELECT sname

FROM student

WHERE WEEK(now()+1)=WEEK(sage);

★41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一。

本题关键采用DATE_FORMAT函数进行日期的比较。

SELECT sname,

CASE

WHEN DATE_FORMAT(NOW(),'%m-%d')

THEN year(now())-year(sage)-1

ELSE year(now())-year(sage)

END AS '年龄'

FROM student

24.查询 1990 年出生的学生名单

考察时间函数的运用，不难。

SELECT *

FROM student

WHERE year(sage)='1990'

40.查询各学生的年龄，只按年份来算

--同24题，掌握year函数用法即可，不难。

SELECT sname,year(now())-year(sage) as '年龄'

FROM student

42.查询本周过生日的学生

SELECT sname

FROM student

WHERE WEEK(now())=WEEK(sage);

未查到本周过生日学生，为验证正确性，查看所有学生的生日时间，如下：

选“2020-1-3”日进行匹配周，代码如下：

SELECT sname

FROM student

WHERE WEEK('2020-01-03')=WEEK(sage);

和观察结果相同，故本题代码OK。

44.查询本月过生日的学生

SELECT sname

FROM student

WHERE MONTH(now())=MONTH(sage);

7 通配符题

考察知识点：like和%，如姓李名未知，可写为like '李%'；名李姓未知，则写为'%李'

★5.查询「李」姓老师的数量

SELECT count(1) AS '李姓老师数量'

FROM teacher

WHERE tname LIKE '李%';

22. 查询名字中含有 风 字的学生信息

SELECT *

FROM student stu

WHERE sname LIKE '%风%'

8 子查询

4.1 查有成绩的学生信息

SELECT * -- 3筛选字段

FROM student s -- 1从student表中

WHERE s.sid IN(SELECT sid FROM sc); -- 2确定具体范围

7. 查询没有学全所有课程的同学的信息

SELECT *

FROM student s WHERE sid not IN

(SELECT sid FROM sc

GROUP BY sid

HAVING COUNT(*) > 2);

8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息

思路：先查学号01同学所学课程，查找对应课程的同学学号，将学号匹配至同学信息即可。

SELECT *

FROM student

WHERE sid != '01' AND sid IN

(SELECT DISTINCT sid

FROM sc

WHERE cid IN(

SELECT cid FROM sc

WHERE sid='01'));

法二：采用自连接的方法，查学号01同学所学课程，将stu表和sc表合并后，选出匹配同学信息。

SELECT DISTINCT s.*

FROM student s JOIN sc ON s.sid=sc.sid

WHERE (cid IN(

SELECT cid FROM sc

WHERE sid='01'))

AND sc.sid !='01';

◆9.查询和" 01 "号的同学学习的课程完全相同的其他同学的信息--此题答案待优化。

思路：在第8题基础上，对SID进行分组，并选取科目数量与学号01相同的学生信息

此写法最大的问题在于刚好能保证3门课全选，如果01号同学只选了2门，eg,01和02，但是不能保证用count(cid)=2,所学课程就一定相同。

SELECT s.*

FROM student s JOIN sc ON s.sid=sc.sid

WHERE (cid IN(

SELECT cid FROM sc

WHERE sid='01'))

AND sc.sid !='01'

GROUP BY s.sid

HAVING count(1)=3;

10. 查询没学过"张三"老师讲授的任一门课程的学生姓名

思路：将sc表和course、teacher表内连接，查询张三老师所教学生的SId，在student表中选取非张三老师教的学生SId，采用‘not in’语句即可。

SELECT student.sname FROM student

WHERE sid NOT IN(

SELECT sid

FROM teacher t JOIN course c ON t.tid=c.tid

JOIN sc ON c.cid=sc.cid

WHERE tname='张三');

9 表连接

1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

解题思路：先筛选01成绩比02成绩高的学生分数情况，以SID为主键进行学生信息的连接，整个程序的运行顺序已为大家标在下表的注释中，方便大家理解。

SELECT *

FROM student s RIGHT JOIN -- 5.将学生信息表与成绩表连接

(SELECT sc1.sid,sc1.score AS '01',sc2.score AS '02' -- 4.选取SID,课程01和课程02分数

FROM sc sc1 JOIN sc sc2 -- 1.先将sc表进行自连接

ON sc1.SId = sc2.SId AND sc1.cid='01' AND sc2.cid='02' -- 2.连接条件为SID相同且第一张表选课程01分数，第二张表选02分数

WHERE sc1.score > sc2.score) AS t -- 3.筛选条件课程1大于课程2

on s.sid = t.sid; -- 6.连接条件为sid相同

1.1 查询同时存在" 01 "课程和" 02 "课程的情况

SELECT s1.sid,s1.score AS '01课程',s2.score AS '02课程' -- 3.选择所需字段

FROM sc s1 JOIN sc s2 ON s1.sid=s2.sid -- 1.将sc表以sid进行自连接

WHERE s1.cid='01' AND s2.cid='02'; -- 2.连接条件课程分别为01，02

1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )

思路：分别筛选01课程和02课程学习情况，以01课程为基准进行左连接

SELECT a.sid,a.score AS '01',b.score AS '02' --5.选择

FROM

(SELECT *FROM sc sc1 WHERE sc1.cid='01') AS a --1.筛选01课程

LEFT JOIN --3.左连接

(SELECT *FROM sc sc2 WHERE sc2.cid='02') AS b --2.筛选02课程

ON a.sid=b.sid; --4.连接条件

1.3 查询不存在" 01 "课程但存在" 02 "课程的情况(本题要求个人理解为不需要显示课程03的情况)

思路：先筛选出存在01课程的学生，使用not in函数将此类人员排除在外，同时附加条件存在02课程

SELECT * FROM sc -- 3.显示学生信息

WHERE sid NOT IN (SELECT sid FROM sc WHERE cid='01') -- 1.筛选01课程的学生

AND sc.cid='02'; -- 2.选择含有02课程的学生

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

SELECT stu.SId,stu.Sname,avg_score --4.取需要的关键字段

FROM student stu RIGHT JOIN --1.student表和成绩表连接

(SELECT sid, avg(score) as avg_score --2.取平均分大于等于60的sid和分数

FROM sc

GROUP BY SId

HAVING avg(score) >= 60) as b

ON stu.sid=b.sid --3.连接条件是sid相等

3. 查询在 SC 表存在成绩的学生信息

SELECT DISTINCT s.* -- 2.筛选学生信息

FROM sc JOIN student s ON sc.sid = s.sid; -- 1.连接2表

6.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩

SELECT stu.sid,stu.sname,avg(score) '平均成绩'

FROM sc JOIN student stu ON sc.sid=stu.sid

GROUP BY 1

HAVING avg(score)>= 85;

26. 查询学过「张三」老师授课的同学的信息

思路：将4张表连接后，通过张三老师姓名进行查询，显示student表中符合条件的学生信息。

SELECT s.*

FROM teacher t JOIN course c ON t.tid = c.tid

JOIN sc ON sc.cid = c.cid

JOIN student s ON s.sid = sc.sid

WHERE tname = '张三';

27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT sname,score

FROM sc JOIN student stu ON sc.sid=stu.sid

JOIN course c ON c.cid=sc.cid

WHERE c.cname='数学' and score<60

28. 查询所有学生的课程及分数情况(存在学生没成绩，没选课的情况)

SELECT s.sid,sc.cid,sc.score

FROM student s LEFT JOIN sc ON s.sid=sc.sid

29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数

SELECT s.sname,c.cname,sc.score

FROM sc JOIN student s ON sc.sid=s.sid

JOIN course c ON sc.cid=c.cid

where sc.score>70

31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

此题如果直接取score>80分，值为NULL，因为符合条件的2人的分数均为80分，故加了个等号。

SELECT s.sid,s.sname

from sc JOIN student s ON sc.sid=s.sid

WHERE cid='01' AND score>=80

35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT DISTINCT sc1.SId,sc1.CId,sc1.score

FROM sc sc1 join sc sc2 ON sc1.CId!=sc2.CId

WHERE sc1.score=sc2.score;

刚刷完题，还没来得及把题目讲解进行补充，同学们稍安勿躁哈！

如果你觉得以上题目目前难度较大，建议先对以下原文进行学习，待掌握后再来刷本文的题目，相信会有柳暗花明又一村的感觉^-^大玲子：SQL入门之The JOIN operation​zhuanlan.zhihu.com大玲子：SQL快速入门之Using Null​zhuanlan.zhihu.com大玲子：SQL快速入门之More JOIN operations​zhuanlan.zhihu.com大玲子：基于vintage、迁徙率等指标浅析拍拍贷逾期数据(含SQL代码)​zhuanlan.zhihu.com