跳转至

特别发送(一) 经典面试题讲解第一弹

你好,我是朱晓峰。

到这里,“实践篇”的内容咱们就学完了。今天,我们来学点儿不一样的——5道经典面试题。这些都是在实际面试中的原题,当然,我没有完全照搬,而是结合咱们课程的具体情况,有针对性地进行了调整。我不仅会给你提供答案,还会和你一起分析,让你能够灵活地吃透这些题目,并能举一反三。

话不多说,我们现在开始。我先带你从一道简单的关于“索引”的面试题入手,索引在面试题里经常出现,来看看这一道你能不能做对。

第一题

下面关于索引的描述,正确的是:

  1. 建立索引的主要目的是减少冗余数据,使数据表占用更少的空间,并且提高查询的速度
  2. 一个表上可以建立一个或者多个索引
  3. 组合索引可以有效提高查询的速度,比单字段索引更高效,所以,我们应该创建一个由所有的字段组成的组合索引,这样就可以解决所有问题了
  4. 因为索引可以提高查询效率,所以索引建得越多越好

解析:这道题的正确答案是选项2,我们来分析一下其他选项。

  • 选项1说对了一半,索引可以提高查询效率,但是创建索引不能减少冗余数据,而且索引还要占用额外的存储空间,所以选项1不对。
  • 选项3不对的原因有2个。第一,组合索引不一定比单字段索引高效,因为组合索引的字段是有序的,遵循左对齐的原则。如果查询的筛选条件不包含组合索引中最左边的字段,那么组合索引就完全不能用。第二,创建索引也是有成本的,需要占用额外的存储空间。用所有的字段创建组合索引的存储成本比较高,而且利用率比较低,完全用上的可能性几乎不存在,所以很少有人会这样做。而且一旦更改任何一个字段的数据,就必须要改索引,这样操作成本也比较高。
  • 选项4错误,因为索引有成本,很少作为筛选条件的字段,没有必要创建索引。

如果这道题你回答错了,一定要回去复习下第11讲的内容。

第二题

假设我们有这样一份学生成绩单,所有同学的成绩都各不相同,请编写一个简单的SQL语句,查询分数排在第三名的同学的成绩:

解析:这道题考查的是我们对查询语句的掌握情况。针对题目中的场景,可以分两步来进行查询。

第一步,按照成绩高低进行排序:

mysql> SELECT *
-> FROM demo.test1
-> ORDER BY score DESC; -- DESC表示降序排列
+----+------+-------+
| id | name | score |
+----+------+-------+
| 2 | 李四 | 90.00 |
| 4 | 赵六 | 88.00 |
| 1 | 张三 | 80.00 |
| 3 | 王五 | 76.00 |
| 5 | 孙七 | 67.00 |
+----+------+-------+
5 rows in set (0.00 sec)

第二步,找出排名第三的同学和对应的成绩。我们可以用第4讲里学过的对返回记录进行限定的关键字LIMIT:

mysql> SELECT *
-> FROM demo.test1
-> ORDER BY score DESC
-> LIMIT 2,1;
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | 张三 | 80.00 |
+----+------+-------+
1 row in set (0.00 sec)

在MySQL中,LIMIT后面可以跟2个参数,第一个参数表示记录的起始位置(第一个记录的位置是0),第二个参数表示返回几条记录。因此,“LIMIT 2,1”就表示从第3条记录开始,返回1条记录。这样,就可以查出排名第三的同学的成绩了。

第三题

现在我们有两个表,分别是人员表(demo.person)和地址表(demo.address),要求你使用关联查询查出完整信息。无论有没有地址信息,人员的信息必须全部包含在结果集中。

人员表:

地址表:

解析: 这个是典型的外查询,咱们在第6讲里学过。题目要求我们查出人员表中的全部信息,而地址表中信息则可以为空,就可以用下面的查询代码:

mysql> SELECT *
-> FROM demo.person AS a
-> LEFT JOIN demo.address AS b -- 左连接,确保demo.person中的记录全部包括在结果集中
-> ON (a.id=b.id);
+----+-------+-------+------+---------+------+-----------+
| id | fname | lname | id | country | city | address |
+----+-------+-------+------+---------+------+-----------+
| 1 | 张 | 三 | 1 | 中国 | 北京 | 海淀123 |
| 2 | 李 | 四 | 2 | 美国 | 纽约 | 奥巴尼333 |
| 3 | 王 | 五 | NULL | NULL | NULL | NULL |
+----+-------+-------+------+---------+------+-----------+
3 rows in set (0.02 sec)

第四题

假设有这样一个教学表(demo.teach),里面包含了人员编号(id)、姓名(fname)和对应的老师的人员编号(teacherid)。如果一个人是学生,那么他一定有对应的老师编号,通过这个编号,就可以找到老师的信息;如果一个人是老师,那么他对应的老师编号就是空。比如说,下表中李四的老师编号是101,我们就可以通过搜索人员编号,找到101的名称是张三,那么李四的老师就是张三;而张三自己就是老师,所以他对应的老师编号是空。

要求:请写一个SQL语句,查询出至少有2名学生的老师姓名。

说明一下,在刚刚的数据表中,张三有3名学生,分别是李四、王五和周八。赵六有一名学生,是孙七。因此,正确的SQL语句的查询结果应该是:

解析:

针对这道题,我们可以按照这样的思路去做:

  1. 通过统计学生对应的老师编号,就可以获取至少有2个学生的老师的编号。
  2. 通过关联查询和自连接获取需要的信息。所谓的自连接,就是数据表与自身进行连接。你可以认为是把数据表复制成一模一样的2个表,通过给数据表起不同的名字来区分它们,这样方便对表进行操作,然后就可以对这2个表进行连接操作了。
  3. 通过使用条件语句WHERE和HAVING对数据进行筛选:先用WHERE筛选出所有的老师编号,再用HAVING筛选出有2个以上学生的老师编号。

首先,我们来获取老师编号,如下:

mysql> SELECT teacherid
    -> FROM demo.teach
    -> WHERE teacherid is not NULL     -- 用WHERE筛选出所有的老师编号
    -> GROUP BY teacherid
    -> HAVING COUNT(*)>=2;             -- 用HAVING筛选出有2个以上学生的老师编号
+-----------+
| teacherid |
+-----------+
|       101 |
+-----------+
1 row in set (0.00 sec)

接着,通过自连接,来获取老师的姓名:

mysql> SELECT a.id,a.fname
    -> FROM demo.teach AS a
    -> JOIN
    -> (
    ->  SELECT teacherid
    ->  FROM demo.teach
    ->  WHERE teacherid IS NOT NULL
    ->  GROUP BY teacherid
    ->  HAVING COUNT(*)>=2
    -> ) AS b
    -> ON (a.id = b.teacherid);
+-----+-------+
| id  | fname |
+-----+-------+
| 101 | 张三  |
+-----+-------+
1 row in set (0.00 sec)

第五题

假设某中学高三年级有多位同学,分成多个班,我们有统一记录学生成绩的表(demo.student)和班级信息表(demo.class),具体信息如下所示:

学生成绩表:

班级信息表:

要求:写一个SQL查询语句,查出每个班级前三名的同学。

说明一下,针对上面的数据,正确的SQL查询应该得出下面的结果:

解析:

  1. 从题目给出的查询结果看,不需要考虑并列的情况。那么,现在要选出分数排名前三的同学,其实只要找出3个最好的分数以及对应的同学就可以了。
  2. 这道题需要用到我们讲过的关联查询和子查询的知识。
  3. WHERE语句的筛选条件表达式中,也可以包括一个子查询的结果。

第一步,我们假设有一个分数X,就是那个第N好的分数,算一下有多少个同学的成绩优于这个分数:

SELECT COUNT(DISTINCT b.points)
FROM demo.student AS b
WHERE b.points > X;

这个查询的结果小于3的话,就代表这个分数X是排名第三的分数了。

第二步,查询出哪些同学满足成绩排名前三的这个档次:

mysql> SELECT a.stdname,a.points
    -> FROM demo.student AS a
    -> WHERE 3 >                       -- 比这个成绩好的不超过3,说明这是第三好的成绩
    -> (
    ->   SELECT COUNT(DISTINCT b.points)   -- 统计一下有几个成绩
    ->   FROM demo.student AS b
    ->   WHERE b.points > a.points         -- 比这个成绩好
    -> );
+---------+--------+
| stdname | points |
+---------+--------+
| 张三    |     85 |
| 李四    |     80 |
| 赵六    |     90 |
| 周八    |     85 |
+---------+--------+
4 rows in set (0.00 sec)

第三步,与班级表关联,按班级统计前三名同学的成绩,并且获取班级信息:

mysql> SELECT c.classname,a.stdname,a.points
    -> FROM demo.student AS a
    -> JOIN demo.class AS c
    -> ON (a.classid = c.id)          -- 关联班级信息
    -> WHERE 3 >
    -> (
    ->   SELECT COUNT(DISTINCT b.points)
    ->   FROM demo.student AS b
    ->   WHERE b.points > a.points
    ->   AND b.classid = a.classid   -- 按班级分别查询
    -> )
    -> ORDER BY c.classname,a.points DESC;
+-----------+---------+--------+
| classname | stdname | points |
+-----------+---------+--------+
| 创新班    | 赵六    |     90 |
| 创新班    | 张三    |     85 |
| 创新班    | 周八    |     85 |
| 创新班    | 郑九    |     70 |
| 普通班    | 李四    |     80 |
| 普通班    | 王五    |     65 |
+-----------+---------+--------+
6 rows in set (0.00 sec)

总结

今天,我们借助几个面试题,回顾了索引的概念、查询、子查询和关联查询的知识,以及条件语句WHERE和HAVING的不同使用方法。如果你发现哪些内容掌握得还没有那么牢固,一定要及时回去复习一下。

在真正的面试中,很少有单纯考查知识点本身的题目,更多的是考查你在解决实际问题的过程中,对知识的灵活运用能力。所以,在学习每一节课时,你一定要结合我给出的实际项目,去真正实践一下,这样才能以不变应万变,在面试中有好的表现。

精选留言(14)
  • freshswq 👍(9) 💬(1)

    之前@June第二题和第五题都可以使用mysql8 新功能窗口函数,试了一下,比较好理解。 相关SQL代码如下(含建表SQL) 第二题: 建表和数据SQL: drop table if exists test1; create table test1 (Id int primary key, Name varchar(20), Score decimal(4,2) ); insert into test1 values(1,'张三',80); insert into test1 values(2,'李四',90); insert into test1 values(3,'王五',76); insert into test1 values(4,'赵六',88); insert into test1 values(5,'孙七',67); 查询SQL: select * from ( SELECT * FROM ( SELECT rank() over ( ORDER BY Score DESC ) AS row_num, Id, NAME, Score FROM test1 ) t WHERE row_num =3 第五题: drop table if exists student; create table student ( Id int primary key, Stdname varchar(20), Points decimal(4,2), Classid tinyint ) insert into student values(1,'张三',85,1); insert into student values(2,'李四',80,2); insert into student values(3,'王五',65,2); insert into student values(4,'赵六',90,1); insert into student values(5,'孙七',69,1); insert into student values(6,'周八',85,1); insert into student values(7,'郑九',70,1); drop table if exists class; create table class ( Id int primary key, Classname varchar(20) ) insert into class values(1,'创新班'); insert into class values(2,'普通班'); 查询SQL: SELECT Classname, Stdname, Points FROM ( SELECT dense_rank() over ( PARTITION BY Classname ORDER BY Points DESC ) AS row_num, Classname, Stdname, Points FROM student a JOIN class b WHERE a.Classid = b.Id ) t WHERE row_num <= 3

    2021-05-12

  • 哈喽 👍(2) 💬(1)

    第五题好难理解啊 看了好几遍也没懂 😭

    2021-07-08

  • lesserror 👍(1) 💬(1)

    偶尔做做SQL题目,和做算法题目一样,都可以预防脑袋生锈。 这一讲中,第五题是一道好题,想了一会儿才想明白。要是面试让我写,不一定能写出来,可能面试官直接会看第五题的结果来筛选候选人。 最近在忙着弄docker搭建新公司的环境问题,弄的头大。还是抽空跟上专栏的脚步!

    2021-04-08

  • giteebravo 👍(1) 💬(1)

    第二题中,排序,限定返回的记录,取其中分数最小的那一条,偏偏就没想到直接用 limit 就解决问题了。 第四题,思路跟老师一致,但在通过分组统计出学生对应的老师编号后,就卡住了,不知道利用自连接就可以直接获取老师的姓名。 在第五题,我想到了先创建一个临时表用来保存最终的查询结果,然后关联查询两张表,分别把不同班的学生成绩插入到这个临时表中,但如何取每个班的前三名,这是个问题。 然后再回头看看题目要求,只写一个 SQL 查询语句,这也太难了…

    2021-04-08

  • 右耳朵猫咪 👍(1) 💬(2)

    第三题貌似有问题,不应该根据两个表的主键关联,应该在地址表创建一个人员表的外键,这个外键与人员表的主键关联才对。

    2021-04-08

  • 李康健 👍(0) 💬(1)

    第三题设计不严谨,无法判断两表之间的关联字段

    2022-06-18

  • Geek_3254e0 👍(0) 💬(1)

    第四题 可以用in很好写 SELECT id,teach.Fname from teach where id in (select Teacherid from teach GROUP BY Teacherid HAVING COUNT(Teacherid)> 2);

    2022-04-27

  • zhuyuping 👍(0) 💬(1)

    第4题,这样写好像也可以?也是采用自连接 select t1.id, t1.fname from demo.teach t1 left join demo.teach t2 on (t1.id = t2.teacherid) group by t1.id having count(t1.id) >=2;

    2022-04-22

  • 沈康 👍(0) 💬(1)

    第五题好难呀,这个很难想到。原来还可以输入X套公式的,还可以使用窗口函数,6啊

    2021-05-22

  • Jun 👍(0) 💬(1)

    老师,请问能否提供创建这些面试题里面的表的代码?

    2021-05-05

  • Jun 👍(0) 💬(1)

    如果mysql版本大于8.0, 第二题和第五题都可以用row_number来解决,更加简洁和容易理解。

    2021-05-05

  • Geek_0162fb 👍(0) 💬(0)

    适合入门小白

    2023-12-04

  • 👍(0) 💬(0)

    有得,感谢朱老师

    2023-09-28

  • 艺超(鲁鸣) 👍(0) 💬(0)

    这个查询的结果小于 3 的话,就代表这个分数 X 是排名第三的分数了。这里是不是应该是排名前三的分数?

    2023-05-23