• Sql面试50题 详解 持续更新

    Sql面试50题

    前言：此文章是根据【数据分析】- SQL面试50题 - 跟我一起打怪升级 一起成为数据科学家

    学习整理而来，仅供复习参考。

    在这里插入图片描述

    建表与插入数据

    --建立学生表
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`)
);

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    --插入学生表测试数据
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);

--查询学生表所有数据
SELECT *
FROM student


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43

    1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）

    • 涉及的表：score，子查询
    • 使用的关键字：SELECT,FROM,INNER JOIN,ON,AS

    解题思路：

    构建如图的表格，使用简单查询即可得到结果。

    在这里插入图片描述

    为构建图中表格，首先需要单独查询到选修课程01的学生id与成绩，然后得到02课程的学生id与成绩。使用两个子查询；

    然后**以s_id为连接条件，通过内连接求交集即可得到图下的表。**之后在查询01课程成绩大于02课程的学生id即可，如需要查询学生具体信息，则需要再与学生表进行内连接，以s_id为连接条件即可。

    在这里插入图片描述

    SQL语句：

    SELECT a.s_id,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
WHERE a.s_score>b.s_score

    • 1
    • 2
    • 3
    • 4

    查询结果：
    在这里插入图片描述

    2、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）

    • 涉及的表：score
    • 使用的关键字：SELECT,FROM,INNER JOIN,ON,AS

    解题思路：

    此题目考察的是对 GROUP BY 语句的使用与理解，首先需要对成绩表按照学生id进行分组，然后使用函数avg统计出平均成绩，再进行条件判断即可。

    注意：

    SELECT 中查询的字段最好是GROUP BY中使用到的字段或者是统计函数，不然的话可能没有意义。

    SQL语句：

    --2、查询平均成绩大于60分的学生的学号和平均成绩（简单，第二道重点）
SELECT s_id,AVG(s_score)
FROM score
GROUP BY s_id
HAVING AVG(s_score)>60

    • 1
    • 2
    • 3
    • 4
    • 5

    查询结果：

    在这里插入图片描述

    5/28更新

    3、查询所有学生的学号、姓名、选课数、总成绩（不重要）

    • 涉及的表：score,student
    • 使用的关键字：SELECT,FROM,INNER JOIN,ON,AS

    解题思路：

    1. 先把学生表和成绩表进行左连接，这里需要使用左连接，这样不会丢失学生表中的数据；

    2. 利用GROUP BY 和 COUNT,SUM 获取学生所选的选课数，总成绩。

    3. 需要对总成绩为null的情况进行单独处理，让其显示0，这里可以有两种处理方式，一种是IFNULL ，另一个是 case when

      这里可以使用 IFNULL 来进行判断，如果第一个表达式值为空则返回后面的值，即0，如果不为空，则返回第一个表达式的值

    SELECT a.s_id,a.s_name,COUNT(c_id),IFNULL(SUM(s_score),0)
FROM  student AS a  LEFT JOIN score AS b 
ON a.s_id=b.s_id
GROUP BY s_id

    • 1
    • 2
    • 3
    • 4

    ​ 也可也使用 case when 来进行判断

    SELECT a.s_id,a.s_name,COUNT(c_id),
SUM(CASE WHEN s_score IS NULL THEN 0 ELSE s_score END) AS "总成绩"
FROM  student AS a  LEFT JOIN score AS b 
ON a.s_id=b.s_id
GROUP BY s_id

    • 1
    • 2
    • 3
    • 4
    • 5

    查询结果：

    在这里插入图片描述

    ​

    4、查询姓“张”的老师的个数（不重要）

    • 涉及的表：teacher
    • 主要的关键字：LIKE

    解题思路：

    使用 LIKE 关键字进行查询匹配即可。

    • %张 表示结尾带张的字符串
    • %张% 表示查询中间带张的姓名
    • 张% 表示以张开头的字符串
    SELECT COUNT(t_id)
FROM teacher
WHERE t_name LIKE'张%'

--查询不重名的姓张老师的个数
SELECT COUNT(DISTINCT t_name)
FROM teacher
WHERE t_name LIKE'张%'

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    查询结果： 结果为 1

    5、查询没学过“张三”老师课的学生的学号、姓名（重点⭐）

    • 涉及的表：course,teacher,student,score
    • 主要的关键字：LIKE

    解题思路：

    一个老师可能会教多门课程，本题目的背景是成绩表中有成绩表示学生选择了这门课程。

    1. 首先需要在课程表中查询到张三老师教的所有课程， 获取这些课程的课程号;
    2. 然后可以在成绩表中进行查询，获取选修过张三老师课程的学生的学号；
    3. 在学生表中进行查询，使用 not in ，查询学号不在第二步选修了张三老师课程学号的学号，即可得到结果。

    注意：使用子查询效率会低一点，使用临时表会比较多一点。

    ​ 尝试直接查询没有选修的学号，后来发现因为一个学生会选择多门课程，不好排除，因此先查询选修过的同学，在查询没有选修的同学。

    SQL语句：

    多增加了一门课程 和成绩，方便测试。

     INSERT INTO Course VALUES('04','JAVA','01');
 INSERT INTO Score VALUES('01' , '04' , 100);

    • 1
    • 2

    获取张三老师教的所有课程号。

    SELECT c_id
FROM course AS a 
INNER JOIN (SELECT t_id  FROM teacher  WHERE t_name='张三') AS b
ON a.t_id=b.t_id

    • 1
    • 2
    • 3
    • 4

    获取学过了张三老师任意一门课程的学生学号，使用 DISTINCT 对学号去重，因为一个学生可能会选择多门课程。

    SELECT DISTINCT s_id
FROM score
WHERE c_id IN(
SELECT c_id
FROM course AS a 
INNER JOIN (SELECT t_id  FROM teacher  WHERE t_name='张三') AS b
ON a.t_id=b.t_id
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    查询不在选修了的学号里的其他人，即没有学过张三老师课程的同学。

    SELECT s_id,s_name FROM student
WHERE s_id NOT IN(
SELECT DISTINCT s_id
FROM score
WHERE c_id IN(
SELECT c_id
FROM course AS a 
INNER JOIN (SELECT t_id  FROM teacher  WHERE t_name='张三') AS b
ON a.t_id=b.t_id)
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    查询结果：

    在这里插入图片描述

    6、查询学过“张三”老师所教的所有课的同学的学号、姓名（重点）

    • 涉及的表：course,teacher,student,score
    • 主要的关键字：LIKE

    解题思路：

    这题第一感觉可能是对第五题题目进行取反，仔细看题发现要求的是学过张三老师教的所有课程。

    一个老师可能会教多门课程，本题目的背景是成绩表中有成绩表示学生选择了这门课程。

    1. 先把所有的表进行内关联，得到一张大表；
    2. 查询老师姓名等于张三的信息，行；
    3. 统计出张三老师一共教了多少门课程 例题中为2；
    4. 按照学生id统计出学生的行数 即 学生所选修张三老师课程的数目；
    5. 查询选修课数等于张三老师教的所有课程数目的学生信息。

    注意：

    ​ 个人感觉视频中的写法是错误的，题目要求的是学过张三教的所有课，视频的写法只考虑了张三只教一门课的方法，如下图，当张三教了两门课程的时候，按照题目意思应该只查出01号学生雷

    SELECT *
FROM student AS s 
INNER JOIN score AS a ON s.s_id=a.s_id
INNER JOIN course AS b ON a.c_id=b.c_id
INNER JOIN teacher AS c ON b.t_id=c.t_id
WHERE t_name='张三'
ORDER BY s.s_id

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    在这里插入图片描述

    SQL语句：

    SELECT s.s_id,COUNT(s.s_id),s.s_name
FROM student AS s 
INNER JOIN score AS a ON s.s_id=a.s_id
INNER JOIN course AS b ON a.c_id=b.c_id
INNER JOIN teacher AS c ON b.t_id=c.t_id
WHERE t_name='张三'
GROUP BY s.s_id
HAVING COUNT(s.s_id)=(
SELECT COUNT(*) FROM course 
WHERE t_id=(SELECT t_id FROM teacher WHERE t_name='张三')
)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    查询结果：

    在这里插入图片描述

  • 相关阅读:
    【数据结构初阶】直接插入排序和希尔排序&链表排序
    循环分批从数据库获取数据
    JDK8 stream常见用法
    FPGA面试题（6）
    mysql 常用操作
    MyBatis动态SQL
    Linux的文件权限管理
    Java SPI机制
    k8s /apis/batch/v1beta1 /apis/policy/v1beta1 接口作用
    求一批整数中出现最多的数字
  • 原文地址：https://blog.csdn.net/Qiuyuguohou/article/details/124960336