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 operationzhuanlan.zhihu.com大玲子:SQL快速入门之Using Nullzhuanlan.zhihu.com大玲子:SQL快速入门之More JOIN operationszhuanlan.zhihu.com大玲子:基于vintage、迁徙率等指标浅析拍拍贷逾期数据(含SQL代码)zhuanlan.zhihu.com