SQL入门50题详解(含知识点讲解及代码运行步骤拆解)

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

本文首先会讲一些简单的sql基础知识，并创建练习所需表格内容，最后针对sql面试50题进行讲解，废话不多说，开始正文吧！

目前计划精选30题（基本涵盖50题对应所有知识点，为大家节省40%时间），因工程量有点大，故今日先更新一部分，待题目更新完毕后将按知识点，由简入难进行排序，方便大家阅读和学习，你们的点赞和收藏数是我更新的最大动力哦！

一、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还可以和聚合函数放在一起。

2.4 聚合函数

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

Count()
Sum()
Max()
Min()
Avg()
Group_concat() 字符串
3.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，SId，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('01','数学','01');
INSERT INTO Course VALUES('01','英语','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、查询"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 s.sid,s.sname,AVG(score) AS '平均成绩' -- 3. 筛选姓名和平均分
FROM student s JOIN sc ON s.sid = sc.sid      -- 1.连接2表
GROUP BY sc.sid                               -- 2.按sid进行分组         
HAVING AVG(score) >=60;                       -- 4.平均分大于60

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

SELECT DISTINCT s.*                        -- 2.筛选学生信息
FROM sc JOIN student s ON sc.sid = s.sid;  -- 1.连接2表

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

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

SELECT s.SId,s.Sname,COUNT(CId) AS '选课总数',sum(score) AS '总成绩'
FROM student s LEFT JOIN sc ON s.sid = sc.sid
GROUP BY sc.SId;

此时运算结果如下，回看原先提供的SC表并未出现SId=09，而连接的前提是s.sid=sc.sid，所以我对此处为何会显示第一条数据不太理解，欢迎知道答案的你留言告知。

4.1 查有成绩的学生信息

SELECT *                                 -- 3
FROM student s                           -- 1
WHERE s.sid IN(SELECT sid FROM sc);      -- 2

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

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

SELECT count(1) AS '李姓老师数量'
FROM teacher
WHERE tname LIKE '李%';

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

思路：将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 = '张三';

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

SELECT *
FROM student s WHERE sid not IN
(SELECT sid FROM sc
GROUP BY sid
HAVING COUNT(*) > 2);

查询至少有一门课与学号为" 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相同的学生信息

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;

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

思路：将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='张三');

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

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

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

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

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;

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;

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

SQL入门50题详解(含知识点讲解及代码运行步骤拆解)

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

SQL入门50题详解(含知识点讲解及代码运行步骤拆解)

相关课程

相关文章

相关电子书

相关实验场景