SQL 常见面试题解析

董旭阳,十余年数据库管理与开发经验,目前在一家全球性的博彩企业从事数据库架构设计和开发,CSDN 学院签约讲师。

著名的技术问答网站 Stack Overflow 发布了 2019 年度开发者调查报告,SQL 在最受欢迎的编程语言中排在了第 3 名,超过一半的开发者在...

文章正文

内容简介

本文介绍并分析了 100 道常见 SQL 面试题,主要分为三个模块:SQL 初级查询、SQL 高级查询以及数据库设计与开发。文章内容结构如下图所示:

sql

本文主要使用三个示例表:员工表(employee)、部门表(department)和职位表(job)。下面是这些示例表的结构图:

erd

  • 部门表(department),包含部门编号(dept_id)和部门名称(dept_name)字段,主键为部门编号。
  • 职位表(job),包含职位编号(job_id)和职位名称(job_title)字段,主键为职位编号。
  • 员工表(employee),包含员工编号(emp_id)、员工姓名(emp_name)、性别(sex)、部门编号(dept_id)、经理编号(manager)、入职日期(hire_date)、职位编号(job_id)、月薪(salary)、奖金(bonus)以及电子邮箱(email)。主键为员工编号,部门编号字段是引用部门表的外键,职位编号字段是引用职位表的外键,经理编号字段是引用员工表自身的外键。

创建示例表和初始化数据的脚本可以从 GitHub 上进行下载。

所有示例都可以在 Oracle 12c 和 MySQL 8.0 中通用,除非另有说明。

SQL 初级查询

1. 什么是 SQL?SQL 有哪些功能?

答案:SQL 代表结构化查询语言,它是访问关系数据库的通用语言,支持数据的各种增删改查操作。SQL 语句可以分为以下子类:

  • DQL,数据查询语言。这个就是 SELECT 语句,用于查询数据库中的数据和信息。
  • DML,数据操作语言。包括 INSERT、UPDATE、DELETE 和 MERGE 语句,主要用于数据的增加、修改和删除。
  • DDL,数据定义语言。主要包括 CREATE、ALTER 和 DROP 语句,用于定义数据库中的对象,例如表和索引。
  • TCL,事务控制语言;主要包括 COMMIT、ROLLBACK 和 SAVEPOINT 语句,用于管理数据库的事务。
  • DCL,数据控制语言。主要包括 GRANT 和 REVOKE 语句,用于控制对象的访问权限。

解析:SQL 是一种声明性的编程语言,只需要告诉计算机想要什么内容(what),不需要指定具体怎么实现(how)。通过几个简单的英文单词,例如 SELECT、INSERT、UPDATE、CREATE、DROP 等,就可以完成大部分的数据操作。

2. 如何查看员工表中的姓名和性别?

答案

SELECT emp_name, sex FROM employee;

解析:SQL 使用 SELECT 和 FROM 查询表中的字段,多个字段使用逗号分隔。

3. 如何查看员工表中的所有字段?

答案

SELECT * FROM employee;

或者:

SELECT emp_id, emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email
  FROM employee;

解析:SQL 查询中的星号(*)表示查询所有字段,可以方便快速查询数据;但是在产品中不推荐使用,因为星号可能带来不确定性。

4. 如何知道每个员工一年的总收入?

答案

SELECT emp_name, salary * 12 + COALEASE(bonus, 0)
  FROM employee;

解析:查询结果中可以使用各种运算、函数以及表达式。COALEASE 函数用于将空值转换为 0。

5. 如何为查询结果指定一个容易理解标题?

答案

SELECT emp_name AS "姓名", salary * 12 + COALEASE(bonus, 0) "年薪"
  FROM employee;

解析:SQL 中的别名可以为查询中的表或结果指定一个临时名称。别名使用关键字 AS 表示,可以省略。

6. 怎么查看女性员工的信息?

答案

SELECT *
  FROM employee
 WHERE sex = '女';

解析:SQL 中使用 WHERE 子句指定过滤条件,只有满足条件的数据才会返回。除了等于(=),还可以使用大于(>)、大于等于(>=)、小于(<)、小于等于(<=)以及不等于(!= 或者 <>)这些比较运算符作为过滤条件。

7. 如何查看月薪范围位于 8000 到 12000 之间的员工?

答案

SELECT *
  FROM employee
 WHERE salary BETWEEN 8000 AND 12000;

解析:BETWEEN 用于查找范围值,包含两端的值。

8. 确认员工中有没有叫做“张三”、“李四” 或“张飞”的人,有的话查出他们的信息。

答案

SELECT *
  FROM employee
  WHERE emp_name IN ('张三', '李四', '张飞');

解析:IN 用于查找列表中的任意值。

9. 只知道某个员工的姓名里有个“云”字,但不知道具体名字,怎么样查看有哪些这样的员工?

答案

SELECT *
  FROM employee
 WHERE emp_name LIKE '%云%';

解析:SQL 中的 LIKE 运算符用于字符串的模式匹配。LIKE 支持两个通配符:% 匹配任意多个字符,_ 匹配单个字符。Oracle 区分大小写,MySQL 不区分大小写。

10. 有些员工有奖金(bonus),另一些没有。怎么查看哪些员工有奖金?

答案

SELECT emp_name,
       bonus
  FROM employee
 WHERE bonus IS NOT NULL;

解析:SQL 中的 NULL 表示空值,意味着缺失或者未知数据。判断空值不能直接使用等于或不等于,而需要使用特殊的 IS NULL 和 IS NOT NULL。

11. 在前面我们知道了如何查询女员工,如何查看 2010 年 1 月 1 日之后入职的女员工呢?

答案

SELECT emp_name, sex, hire_date
  FROM employee
 WHERE sex = '女'
   AND hire_date > DATE '2010-01-01';

解析:AND、OR 和 NOT 表示逻辑与、逻辑或和逻辑非,可以用于构造复杂的查询条件。

12. 以下查询会不会出错,为什么?

SELECT *
  FROM employee
 WHERE 1 = 0 AND 1/0 = 1;

答案:不会出错,但是查不到任何数据。

解析:因为 SQL 对于逻辑运算符 AND 和 OR 使用短路运算(short-circuit evaluation)。也就是说,只要前面的表达式能够决定最终的结果,不执行后面的计算。

13. 如何去除查询结果中的重复记录,比返回如员工性别的不同取值?

答案

SELECT DISTINCT sex
  FROM employee;

解析:DISTINCT 用于消除查询结果中的重复值,上面的查询只返回两个不同的性别记录。

14. 查看员工信息的时候,想要按照薪水从高到低显示,怎么实现?

答案

SELECT *
  FROM employee
 ORDER BY salary DESC;

解析:ORDER BY 子句用于对查询结果进行排序;ASC 表示升序,DESC 表示降序。

15. 在上面的排序结果中,有些人的薪水一样多;对于这些员工,希望再按照奖金的多少进行排序,又怎么实现?

答案

SELECT *
  FROM employee
 ORDER BY salary DESC, bonus DESC;

解析:按照多个字段排序时,使用逗号分隔;排序时先按照第一个条件排列,对于排名相同的数据,再按照第二个条件排列,以此类推。

16. 员工的姓名是中文,如何按照姓名的拼音顺序进行排序?

答案

-- MySQL 实现
SELECT emp_name
  FROM employee
 WHERE emp_id <= 10
 ORDER BY CONVERT(emp_name USING GBK);

-- Oracle 实现
SELECT emp_name
  FROM employee
 WHERE emp_id <= 10
 ORDER BY NLSSORT(emp_name,'NLS_SORT = SCHINESE_PINYIN_M');

解析:中文可以按照拼音进行排序,或者按照偏旁部首进行排序。MySQL 中的 GBK 编码支持拼音排序,Oracle 可以指定排序规则。

17. 由于很多人没有奖金,bonus 字段为空,对于下面的查询:

SELECT *
  FROM employee
 ORDER BY bonus;

没有奖金的员工排在最前面还是最后面?

答案:取决于数据库的实现。

解析:对于 MySQL ,升序时 NULL 值排在最前面,降序时 NULL 值排在最后面。对于 Oracle,默认升序排序时时 NULL 值排在最后面,降序时 NULL 值排在最前面;还可以使用 NULLS FIRST 或 NULLS LAST 指定 NULL 值排在最前或最后。

18. 薪水最高的 3 位员工都有谁?

答案

-- Oracle 12c 实现
SELECT emp_name, salary
  FROM employee
 ORDER BY salary DESC
 FETCH NEXT 3 ROWS ONLY;

-- MySQL 实现
SELECT emp_name, salary
  FROM employee
 ORDER BY salary DESC
 LIMIT 3;

解析:SQL 中用于限制返回数量的关键字是 FETCH,MySQL 使用 LIMIT。

19. 在上面的问题中,如果有 2 个人的排名都是第 3 位,怎么才能都返回(一共 4 条数据)?

答案

-- Oracle 12c 实现
SELECT emp_name, salary
  FROM employee
 ORDER BY salary DESC
 FETCH NEXT 3 ROWS WITH TIES;

解析:FETCH 子句支持 WITH TIES 选项,用于返回更多排名相同的数据。另外,还可以使用 PERCENT 按照百分比返回数据。

20. 怎么返回第 11 名到 15 名,也就是实现分页显示的效果?

答案

-- Oracle 12c 实现
SELECT emp_name, salary
  FROM employee
 ORDER BY salary DESC
 OFFSET 10 ROWS
 FETCH NEXT 5 ROWS ONLY;

-- MySQL 实现
SELECT emp_name, salary
  FROM employee
 ORDER BY salary DESC
 LIMIT 5 OFFSET 10;

解析:OFFSET 关键字指定一个偏移量,表示忽略前面多少行数据,然后返回结果。

21. 什么是函数?SQL 中的函数有哪些分类?

答案:函数是一种功能模块,可以接收零个或多个输入值,并且返回一个输出值。

在 SQL 中,函数主要分为两种类型:标量函数(scalar function)和聚合函数(aggregate function)。标量函数针对每一行输入参数,返回一行输出结果。例如,ABS 函数可以计算绝对值。聚合函数针对一组数据进行操作,并且返回一个汇总结果。例如,AVG 函数可以计算一组数据的平均值。

22. 如何知道每个员工的邮箱长度?

答案

SELECT emp_name, length(email)
  FROM employee;

解析:length 函数用于返回字符长度。需要注意的是,Oracle 是按照字符数量计算,lengthb 按照字节计算;MySQL 是按照字节数量计算,char_length 按照字符数量计算。对于汉字这种多字节字符需要注意区分。

23. 如何确认谁的邮箱是“GUANXING@SHUGUO.COM”?

答案

SELECT emp_name, email
  FROM employee
 WHERE UPPER(email) = 'GUANXING@SHUGUO.COM';

解析:UPPER 函数用于将字符串转换为大写形式。另外,LOWER 函数用于将字符串转换为小写形式。

24. 以 CSV(逗号分隔符)格式显示员工的姓名、性别、薪水信息,如何写 SQL 查询语句?

答案

-- MySQL 实现
SELECT CONCAT_WS(',' emp_name, sex, salary)
  FROM employee;

-- Oracle 实现
SELECT emp_name||','||sex||','||salary
  FROM employee;

解析:CONCAT 函数用于连接两个字符串。MySQL 中的 CONCAT_WS 扩展了该功能;Oracle 支持使用 || 连接字符串。

25. 如何获取员工邮箱中的用户名部分( @ 符号之前的字符串)?

答案

SELECT emp_name, SUBSTR(email, 1, INSTR(email,'@') - 1)
  FROM employee;

解析:此处使用了两个字符串函数,INSTR 函数查找 @ 符号的位置,SUBSTR 函数获取该位置之前的子串。

26. 将员工邮箱中的“.com”替换为“.net”,写出 SQL 语句?

答案

SELECT emp_name, REPLACE(EMAIL, '.com','.net')
  FROM employee;

解析:REPLACE 函数用于替换字符串中的字串。另外,TRIM 函数用于截断字符串。

27. 如何返回随机排序的员工信息?

答案

-- MySQL 实现
SELECT emp_name, RAND()
  FROM employee
 ORDER BY RAND();

-- Oracle 实现
SELECT emp_name, DBMS_RANDOM.VALUE
  FROM employee
 ORDER BY DBMS_RANDOM.VALUE;

解析:利用生成随机数的函数进行排序。MySQL 使用 RAND 函数,Oracle 使用 DBMS_RANDOM.VALUE 函数。

28. 数学函数 CEILING、FLOOR 和 ROUND 有什么区别?

答案

SELECT CEILING(1.1), FLOOR(1.1), ROUND(1.1)
  FROM employee
 WHERE emp_id = 1;

解析:CEILING 向上取整,FLOOR 向下取整,ROUND 四舍五入。Oracle 中使用 CEIL 函数替代 CEILING。

29. 下图是一个学生成绩表(score),如何知道每个学生的最高得分? score 答案

SELECT student_id, GREATEST(chinese, math, english, history)
  FROM score;

解析:GREATEST 函数用于返回列表中的最大值,LEAST 函数用于返回列表中的最小值。

30. 如何知道每个员工的工作年限?

答案

隐藏内容 支付可见
购买文章 ¥4.99
订阅频道首月仅需 12 元/月,预计可省 1288 元
¥4.99
¥4.99购买
订阅频道免费读
× 订阅 Java 精选频道
首次订阅 ¥ 12 元/月 15元/月
订阅即可免费阅读所有精选内容