SQL常见100面试题解析

内容简介

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

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

• 部门表（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 10000 AND 15000;

解析：BETWEEN AND 用于查找范围值，而且包含两端值。

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，可以使用 NULLS FIRST 和 NULLS LAST 进行控制；默认升序排序时时 NULL 值排在最后面，默认降序时 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 条数据）？

答案：

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），如何知道每个学生的最高得分？

答案：

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

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

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

答案：

SELECT emp_name, EXTRACT( year FROM CURRENT_DATE) - EXTRACT( year FROM HIRE_DATE)
  FROM employee;

解析：CURRENT_DATE 函数返回当前日期，EXTRACT 函数可以提取日期中的各个部分，本例中使用 year 参数获取年份信息。

31. 工资信息比较敏感，不宜直接显示。按照范围显示收入水平，小于 10000 显示为“低收入”，大于等于 10000 并且小于 20000 显示为“中等收入”，大于 20000 显示为“高收入”。如何使用 SQL 实现？

答案：

SELECT emp_name,
       CASE WHEN salary < 10000 THEN '低收入'
            WHEN salary < 20000 THEN '中等收入'
            ELSE '高收入'
       END "薪水等级"
  FROM employee;

解析：CASE 表达式可以类似于 IF-THEN-ELSE 的逻辑处理。SQL 支持简单 CASE 和搜索 CASE，可以为查询增加基于逻辑的复杂分析功能。掌握好 CASE 表达式是使用 SQL 进行数据分析的必备技能之一。

32. 如何统计员工的数量、平均月薪、最高月薪、最低月薪以及月薪的总和？

答案：

SELECT COUNT(*), AVG(salary), MAX(salary), MIN(salary), SUM(salary)
  FROM employee;

解析：聚合函数针对一组数据计算出单个结果值。常见的聚会函数包括：

• AVG - 计算一组值的平均值。
• COUNT - 统计某个字段的行数。
• MIN - 返回一组值中的最小值。
• MAX - 返回一组值中的最大值。
• SUM - 计算一组值的和值。

33. 以下两个 COUNT 函数返回的结果是否相同？

SELECT COUNT(*), COUNT(bonus)
  FROM employee;

答案：结果不同，COUNT(*) 返回 25 条记录，COUNT(bonus) 返回 9 条记录。

解析：除了 COUNT (*) 之外，其他聚合函数都会忽略字段中的 NULL 值。另外，聚合函数中的 DISTINCT 选项可以在计算之前排除重复值。

34. 群发邮件时，多个邮件地址使用分号进行分隔。如何获取所有员工的群发邮件地址？

答案：

-- MySQL 实现
SELECT GROUP_CONCAT(email SEPARATOR ';')
  FROM employee;

-- Oracle 实现
SELECT LISTAGG(email, '; ') WITHIN GROUP (ORDER BY NULL)
  FROM employee;

解析：使用字符串的聚合函数将多个字符串合并成一个。MySQL 中使用 GROUP_CONCAT 函数，Oracle 使用 LISTAGG 函数。

35. 如何获取每个部门的统计信息，比如员工的数量、平均月薪？

答案：

SELECT dept_id, COUNT(*), AVG(salary)
  FROM employee
 GROUP BY dept_id;

解析：SQL 中使用 GROUP BY 进行数据的分组，结合聚合函数可以获得分组后的统计信息。另外，可以使用多个字段分成更多的组。

36. 以下语句能否正常运行，为什么？

SELECT dept_id, COUNT(*), emp_name
  FROM employee
 GROUP BY dept_id;

答案：不能运行。

解析：使用了 GROUP BY 分组之后，SELECT 列表中只能出现分组字段和聚合函数，不能再出现其他字段。上面的语句中，按照部门分组后，再查看员工姓名的话，存在逻辑上的错误。因为每个部门有多个员工，应该显示哪个员工呢？

37. 如果只想查看平均月薪大于 10000 的部门，怎么实现？

答案：

SELECT dept_id, AVG(salary)
  FROM employee
 GROUP BY dept_id
HAVING AVG(salary) > 10000;

解析：HAVING 子句用于对分组后的结果进行过滤，它必须跟在 GROUP BY 之后。

38. 如果想要知道哪些部门月薪超过 5000 的员工数量大于 5，如何写 SQL 查询？

答案：

SELECT dept_id, COUNT(*)
  FROM employee
 WHERE salary > 5000
 GROUP BY dept_id
HAVING COUNT(*) > 5;

解析：WHERE 用于对表中的数据进行过滤，HAVING 用于对分组后的数据进行过滤，两者可以结合使用。

SQL 高级查询

39. 什么是连接查询？SQL 中有哪些连接查询？

答案：连接（join）查询是基于两个表中的关联字段将数据行拼接到一起，可以同时返回两个表中的数据。SQL 支持以下连接：

• 内连接（INNER JOIN），用于返回两个表中满足连接条件的数据行。
• 左外连接（LEFT OUTER JOIN），返回左表中所有的数据行；对于右表中的数据，如果没有匹配的值，返回空值。
• 右外连接（RIGHT OUTER JOIN），返回左表中所有的数据行；对于右表中的数据，如果没有匹配的值，返回空值。
• 全外连接（FULL OUTER JOIN），等价于左外连接加上右外连接，返回左表和右表中所有的数据行。MySQL 不支持全外连接。
• 交叉连接（CROSS JOIN），也称为笛卡尔积（Cartesian product），两个表的笛卡尔积相当于一个表的所有行和另一个表的所有行两两组合，结果的数量为两个表的行数相乘。
• 自连接（Self Join），是指连接操作符的两边都是同一个表，即把一个表和它自己进行连接。自连接主要用于处理那些对自己进行了外键引用的表。

40. 如何通过内连接返回员工所在的部门名称？

答案：可以使用以下两种连接语句：

SELECT d.dept_id, 
       d.dept_name,
       e.emp_name
  FROM employee e
  JOIN department d ON (e.dept_id = d.dept_id);

SELECT d.dept_id, 
       d.dept_name,
       e.emp_name
  FROM employee e, department d
 WHERE e.dept_id = d.dept_id;

解析：使用两个表的部门编号（dept_id）进行连接，可以获得员工所在的部门信息。推荐使用第一种语句，即 JOIN 和 ON 的连接方式，语义上更清晰。

41. 统计每个部门的员工数量，同时显示部门名称信息。如何使用连接查询实现？

答案：

SELECT d.dept_name, COUNT(e.emp_name)
  FROM department d
  LEFT JOIN employee e ON (e.dept_id = d.dept_id)
 GROUP BY d.dept_name;

解析：由于某些部门可能还没有员工，不能使用内连接，而需要使用左外连接或者右外连接；否则可能缺少某些部门的结果。

42. 如何知道每个员工的经理姓名（manager）？

答案：

SELECT e.emp_name AS "员工姓名",
       m.emp_name AS "经理姓名"
  FROM employee e
  LEFT JOIN employee m ON (m.emp_id = e.manager)
 ORDER BY e.emp_id;

解析：通过自连接关联两个员工表，使用左连接是因为有一个员工没有上级，他就是公司的最高领导。

43. SQL 支持哪些集合运算？

答案：SQL 中提供了以下三种集合运算：

• 并集运算（UNION、UNION ALL），将两个查询结果合并成一个结果集，包含了第一个查询结果以及第二个查询结果中的数据。
• 交集运算（INTERSECT），返回两个查询结果中的共同部分，即同时出现在第一个查询结果和第二个查询结果中的数据。MySQL 不支持 INTERSECT。
• 差集运算（EXCEPT），返回出现在第一个查询结果中，但不在第二个查询结果中的数据。MySQL 不支持 EXCEPT，Oracle 使用 MINUS 替代 EXCEPT。

44. 假设存在以下两个表：

CREATE TABLE t1(id int);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (2);

CREATE TABLE t2(id int);
INSERT INTO t1 VALUES (1);
INSERT INTO t1 VALUES (3);

下列查询的结果分别是什么？

-- Oracle 实现
SELECT id FROM t1 UNION SELECT id FROM t2;
SELECT id FROM t1 UNION ALL SELECT id FROM t2;
SELECT id FROM t1 INTERSECT SELECT id FROM t2;
SELECT id FROM t1 EXCEPT SELECT id FROM t2;

答案：结果分别为（1、2、3）、（1、1、2、3）、（1）以及（2）。

解析：UNION 的结果集中删除了重复的数据，UNION ALL 保留了所有的数据。

45. 对于 MySQL 而言，如何实现上题中的交集运算和差集运算效果？

答案：

-- 使用连接查询实现交集运算
SELECT t1.id FROM t1 JOIN t2 ON (t1.id = t2.id);

-- 使用左连接查询实现差集运算
SELECT t1.id FROM t1
  LEFT JOIN t2 ON (t1.id = t2.id)
 WHERE t2.id IS NULL;

解析：交集运算等价于基于所有字段的内连接查询，差集运算等价于左连接中右表字段为空的结果。

46. 什么是子查询？子查询有哪些类型？

答案：子查询（subquery）是指嵌套在其他语句（SELECT、INSERT、UPDATE、DELETE、MERGE）中的 SELECT 语句。子查询中也可以嵌套另外一个子查询，即多层子查询。

子查询可以根据返回数据的内容分为以下类型：

• 标量子查询（scalar query）：返回单个值（一行一列）的子查询。上面的示例就是一个标量子查询。
• 行子查询（row query）：返回包含一个或者多个值的单行结果（一行多列），标量子查询是行子查询的特例。
• 表子查询（table query）：返回一个虚拟的表（多行多列），行子查询是表子查询的特例。

基于子查询和外部查询的关系，也可以分为以下两类：关联子查询（correlated subqueries）和非关联子查询（non-correlated subqueries）。关联子查询会引用外部查询中的列，因而与外部查询产生关联；非关联子查询与外部查询没有关联。

47. 如何找出月薪大于平均月薪的员工？

答案：

SELECT emp_name, salary
  FROM employee
 WHERE salary > (SELECT AVG(salary) FROM employee);

解析：使用子查询获得平均月薪，然后在外部查询中的 WHERE 条件中使用该值。这是一个非关联的标量子查询。

48. 以下查询语句的结果是什么？

SELECT *
  FROM employee
 WHERE dept_id = (SELECT dept_id FROM department);

答案：执行出错。

解析：外部查询的 WHERE 条件使用了等于号，但是子查询返回了多个值，此时需要使用 IN 来进行匹配。正确的查询语句如下：

SELECT *
  FROM employee
 WHERE dept_id IN (SELECT dept_id FROM department);

另外，NOT IN 用于查询不在列表中的值。

49. 哪些员工的月薪高于本部门的平均值？

答案：

SELECT emp_name, salary
  FROM employee e
 WHERE salary > (SELECT AVG(salary)
                   FROM employee
                  WHERE dept_id = e.dept_id);

解析：使用关联子查询获取每个员工所在部门的平均月薪，然后传递给外部查询进行判断。

50. 显示员工信息时，增加一列，用于显示该员工所在部门的人数。如何编写 SQL 查询？

答案：

SELECT emp_name, 
       (SELECT COUNT(*)
          FROM employee
         WHERE dept_id = e.dept_id) AS dept_count
  FROM employee e;

解析：SELECT 列表中同样可以使用关联子查询。

51. 以上问题能否使用下面的查询实现？

SELECT emp_name, 
       dept_count
  FROM employee e
  JOIN (SELECT COUNT(*) AS dept_count
           FROM employee
          WHERE dept_id = e.dept_id) d
    ON (1=1);

答案：该语句执行出错。

解析：FROM 子句中不能直接使用关联子查询，因为子查询和查询处于相同的层级，不能引用前表（e）中的数据。不过，Oracle 中支持横向（LATERAL）子查询，可以实现该功能：

SELECT emp_name, 
       dept_count
  FROM employee e
  JOIN LATERAL (SELECT COUNT(*) AS dept_count
           FROM employee
          WHERE dept_id = e.dept_id) d
    ON (1=1);

52. 找出哪些部门中有女性员工？

答案：

SELECT *
  FROM department d
 WHERE EXISTS (SELECT 1 
                 FROM employee e 
                WHERE e.sex ='女'
                  AND e.dept_id = d.dept_id);

解析：EXISTS 运算符用于检查子查询中结果的存在性。针对外部查询中的每条记录，如果子查询存在结果（部门中存在女性员工），外部查询即返回结果。NOT EXISTS 执行相反的操作。

53. 按照部门和职位统计员工的数量，同时统计部门所有职位的员工数据，再加上整个公司的员工数量。如何用一个查询实现？

答案：

-- MySQL 实现
SELECT dept_id, job_id, COUNT(*)
  FROM employee
 GROUP BY dept_id, job_id WITH ROLLUP;

-- Oracle 实现
SELECT dept_id, job_id, COUNT(*),GROUPING(dept_id)
  FROM employee
 GROUP BY ROLLUP (dept_id, job_id);

解析：GROUP BY 支持扩展的 ROLLUP 选项，可以生成按照层级进行汇总的结果，类似于财务报表中的小计、合计和总计。

54. GROUP BY 中的另一个选项 CUBE 的作用是什么？

解析：CUBE 用于生成多维立方体式的汇总统计。例如，以下查询统计不同部门和职位员工的数量，同时统计部门所有职位的员工数据，加上所有职位的员工数据，以及整个公司的员工数量。

-- Oracle 实现
SELECT dept_id, job_id, COUNT(*),GROUPING(dept_id)
  FROM employee
 GROUP BY CUBE (dept_id, job_id);

另外，GROUPING SETS 用于指定更加复杂的自定义分组方式。MySQL 暂未支持 CUBE 和 GROUPING SETS。

55. 使用扩展分组时，会产生一些 NULL 值，如何确认这些 NULL 值代表的意义？

答案：使用 GROUPING 函数，例如：

-- 
SELECT CASE GROUPING(dept_id) WHEN 1 THEN '所有部门' ELSE dept_id END, 
       CASE GROUPING(job_id) WHEN 1 THEN '所有职位' ELSE job_id END,
       COUNT(*)
  FROM employee
 GROUP BY dept_id, job_id WITH ROLLUP;

查询结果如下图所示。

解析：GROUPING 函数用于判断某个统计结果是否与该字段有关。如果是，函数返回 0；否则返回 1。比如第 3 行数据是所有职位的统计，与职位无关。然后使用 CASE 表达式进行转换显示。

56. 如何使用 SQL 查询生成以下连续的数字序列？

答案：

-- MySQL 实现
WITH RECURSIVE cte (n) AS
(
  SELECT 1 FROM dual
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 10
)
SELECT * FROM cte;

-- Oracle 实现
WITH cte (n) AS
(
  SELECT 1 FROM dual
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 10
)
SELECT * FROM cte;

解析：通用表表达式（WITH 子句）是一个在语句级别的临时结果集。定义之后，相当于有了一个表变量，可以在语句中多次引用该通用表表达式。递归（RECURSIVE）形式的通用表表达式可以用于生成序列，遍历层次数据或树状结构的数据。Oracle 中省略 RECURSIVE 即可。

57. 如何获取员工在公司组织结构中的结构图，也就是从最高领导到员工的管理路径？

答案：

-- MySQL 实现
WITH RECURSIVE employee_paths (emp_id, emp_name, path) AS
(
  SELECT emp_id, emp_name, CAST(emp_name AS CHAR(200))
    FROM employee
    WHERE manager IS NULL
  UNION ALL
  SELECT e.emp_id, e.emp_name, CONCAT(ep.path, '->', e.emp_name)
    FROM employee_paths ep 
    JOIN employee e
      ON ep.emp_id = e.manager
)
SELECT * FROM employee_paths ORDER BY path;

查询结果如下（显示部分内容）：

解析：同样是利用递归通用表表达式实现数据的遍历。Oracle 中省略 RECURSIVE 即可。通用表表达式是 SQL 中非常强大的功能，可以帮助我们简化复杂的连接查询和子查询，并且可以完成递归处理和层次遍历。

58. 什么是窗口函数？有哪些常见的窗口函数？

答案：窗口函数（Window function）也称为分析函数，与聚合函数类似，也是基于一组数据进行分析；但是，窗口函数针对每一行数据都会返回一个结果。窗口函数为 SQL 提供了强大的数据分析功能。

常见的窗口函数包括聚合窗口函数和专用的窗口函数。前者就是将聚合函数作为窗口函数使用，包括：COUNT、MIN、MAX、AVG 以及 SUM 等。专用窗口函数主要包括 ROW_NUMBER、RANK、DENSE_RANK、PERCENT_RANK、CUME_DIST、NTH_VALUE、NTILE、FIRST_VALUE、LAST_VALUE、LEAD 以及 LAG 等。

59. 查询员工的月薪，同时返回该员工所在部门的平均月薪。如何使用聚合函数实现？

答案：

SELECT emp_name, salary, AVG(salary) OVER (PARTITION BY dept_id)
  FROM employee;

解析：窗口函数 AVG 基于部门（dept_id）分组后的数据计算平均月薪，为每个员工返回一条记录。窗口函数不需要和 GROUP BY 一起使用。虽然也可以使用关联子查询与聚合函数实现相同的功能，显然窗口函数更加简单易懂。

60. 查询员工的月薪，同时计算其月薪在部门内的排名？

答案：

SELECT emp_name, dept_id, salary, 
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC),
       RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC),
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC)
  FROM employee;

解析：ROW_NUMBER、RANK 和 DENSE_RANK 都可以用于计算排名。它们不同之处在于对排名相同的数据处理方式不一样。比如说 10、9、9、8 这四个数，ROW_NUMBER 一定会排出不同的名次（1、2、3、4）；RANK 对于相同的数据排名相同（1、2、2、4）；DENSE_RANK 对于相同的数据排名相同，并且后面的排名不会跳跃（1、2、2、3）。

61. 查询员工的入职日期，同时计算其部门内在该员工之前一个和之后一个入职的员工？

答案：

SELECT emp_name, dept_id, hire_date, 
       LAG(emp_name, 1) OVER (PARTITION BY dept_id ORDER BY hire_date),
       LEAD(emp_name, 1) OVER (PARTITION BY dept_id ORDER BY hire_date)
  FROM employee;

解析：LAG 和 LEAD 用于返回排名中相对于当前行的指定偏移量之前和之后的数据。

62. 查询员工的月薪，同时计算其部门内到该员工为止的累计总月薪？

答案：

SELECT emp_name, dept_id, salary, 
       SUM(salary) OVER (PARTITION BY dept_id ORDER BY NULL ROWS UNBOUNDED PRECEDING)
  FROM employee;

解析：窗口函数支持定义窗口范围，UNBOUNDED PRECEDING 表示从分组内的第一行到当前行，可以用于计算累计值。

63. 查询员工的月薪，同时计算其部门内按照月薪排序后，前一个员工、当前员工以及后一个员工的平均月薪？

答案：

SELECT emp_name, dept_id, salary, 
       AVG(salary) OVER (PARTITION BY dept_id ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  FROM employee;

解析：BETWEEN N PRECEDING AND M FOLLOWING 定义了一个随着当前行移动的窗口，可以用于计算移动平均值。

窗口函数为我们带来了强大的数据分析和报表生成功能，MySQL 8.0 也增加了对于窗口函数的支持。

设计与开发

64. 什么是数据库（Database）？什么是数据库管理系统（DBMS）？

答案：数据库（Database）是各种数据的集合，按照一定的数据结构进行存储和管理；数据库管理系统（Database Management System）是用于管理数据库的软件，负责数据库的创建、查询、修改等管理操作。这两者共同构成了数据库系统（Database System）。应用程序或者最终用户通过 DBMS 访问和管理数据库。

65. 什么是关系数据库？

答案：关系数据库是指基于关系模型的数据库。在关系模型中，用于存储数据的逻辑结构就是二维表（Table）。表由行和列组成，行也称为记录，代表了单个实体；列也称为字段，代表了实体的某些属性。关系数据库使用 SQL 作为标准语言，执行数据的增删改查以及各种管理操作。关系数据库还定义了三种约束完整性：实体完整性、参照完整性以及用户定义完整性。

大多数主流数据库都属于关系数据库，例如 Oracle、MySQL、SQL Server 以及 PostgreSQL 等。另外，数据库领域还存在一些非关系模型的数据库（NoSQL ），例如 Mongodb、Redis、Cassandra 等。

66. 关系数据库有哪些完整性约束？

答案：关系数据库定义了以下约束：

• 非空约束（NOT NULL），用于限制字段不会出现空值。比如员工姓名不能为空。
• 唯一约束（UNIQUE），用于确保字段中的值不会重复。例如，每个员工的电子邮箱不能重复。每个表可以有多个唯一约束。
• 主键约束（Primary Key），主键是唯一标识表中每一行的字段。例如员工编号，部门编号等。主键字段必须唯一且非空，每个表可以有且只能有一个主键。
• 外键约束（FOREIGN KEY），用于表示两个表之间的引用关系。例如，员工属于部门，因此员工表中的部门编号字段可以定义为外键，它引用了部门信息表中的主键。
• 检查约束（CHECK），可以定义更多用户自定义的业务规则。例如，薪水必须大于 0 ，性别只能是男和女等。
• 默认值（DEFAULT），用于向字段中插入默认数据。

67. OLTP 和 OLAP 的区别？

答案：

OLTP	OLAP
在线事务处理系统	在线分析处理系统
专注于事务数据的增删改，事务相对简单但频繁，要求响应时间快	专注于决策数据分析，查询通常比较复杂，处理时间长
数据来源于在线业务	数据来源于各种 OLTP
通常采用规范化的设计，需要保证数据的完整性	不需要太多规范化，可以存储冗余信息，采用多维数据模型
常见应用包括银行 ATM、在线订票系统、网上商城	常见应用包括数据仓库、报表分析、商务智能

68. 什么是数据库规范化，有哪些常见的数据库范式？

答案：数据库规范化是一种数据库设计的方法，用于有效地组织数据，减少数据的冗余和相互之间的依赖，增加数据的一致性。由于非规范化的数据库存在冗余，可能导致数据的插入、删除、修改异常等问题，因此引入了规范化过程。

数据库规范化的程度被称为范式（Normal Form），目前已经存在第一范式到第六范式，每个范式都是基于前面范式的增强。

• 第一范式（First Normal Form），表中的每个属性都是单值属性，每个记录都唯一，也就是需要主键。举例来说，如果员工存在工作邮箱和个人邮箱，不能都放到一个字段，而需要拆分成两个字段；
• 第二范式（Second Normal Form），首先需要满足第一范式，且不包含任何部分依赖关系。举例来说，如果将学生信息和选课信息放在一起，学号和课程编号可以作为复合主键；但此时学生的其他信息依赖于学号，即主键的一部分。通常使用单列主键可以解决部分依赖问题；
• 第三范式（Third Normal Form），首先需要满足第二范式，并且不存在传递依赖关系。举例来说，如果将部门信息存储在每个员工记录的后面，那么部门名称依赖部门编号，部门编号又依赖员工编号，这就是传递依赖。解决的方法就是将部门信息单独存储到一个表中；
• 更高的范式包括 Boyce-Codd 范式、第四范式、第五范式以及第六范式等，不过很少使用到这些高级范式。对于大多数系统而言，满足第三范式即可。

另外，反规范化（Denormalization）是在完成规范化之后执行的相反过程。反规范化通过增加冗余信息，减少 SQL 连接查询的次数，从而减少磁盘 IO 来提高查询时的性能。但是反规范化会导致数据的重复，需要更多的磁盘空间，并且增加了数据维护的复杂性。

数据库的设计是一个复杂的权衡过程，需要综合考虑各方面的因素。

69. 什么是实体关系图（ERD）？

答案：实体关系图是一种用于数据库设计的结构图，它描述了数据库中的实体，以及这些实体之间的相互关系。实体代表了一种对象或者概念。例如，员工、部门和职位可以称为实体。每个实体都有一些属性，例如员工拥有姓名、性别、工资等属性。

关系用于表示两个实体之间的关联。例如，员工属于部门。三种主要的关系是一对一、一对多和多对多关系。例如，一个员工只能属于一个部门，一个部门可以有多个员工，部门和员工是一对多的关系。

ERD 也可以按照抽象层次分为三种：

• 概念 ERD，即概念数据模型。概念 ERD 描述系统中存在的业务对象以及它们之间的关系。
• 逻辑 ERD，即逻辑数据模型。逻辑 ERD 是对概念数据模型进一步的分解和细化，明确定义每个实体中的属性并描述操作和事务。
• 物理 ERD，即物理数据模型。物理 ERD 是针对具体数据库的设计描述，需要为每列指定类型、长度、可否为空等属性，为表增加主键、外键以及索引等约束。

下图是我们使用的三个示例表的物理 ERD（基于 MySQL 实现）：

70. 数据库常见对象有哪些？

答案：表（Table）、视图（View）、序列（Sequence）、索引（Index）、存储过程（Stored Procedure）、触发器（Trigger）、用户（User）以及同义词（Synonym）等等。其中，表是关系数据库中存储数据的主要形式。

71. 常见 SQL 数据类型有哪些？

答案：SQL 定义了大量的数据类型，其中最常见的类型包括字符类型、数字类型、日期时间类型和二进制数据类型。

• 字符数据类型，分为固定长度的 CHAR(n) 、可变长度的 VARCHAR(n) 以及字符大对象 CLOB。
• 数字数据类型，分为精确数字 INTEGER、BIGINT、NUMERIC 以及近似数字 FLOAT、DOUBLE PRECISION 等。
• 日期时间类型，分为日期 DATE、时间 TIME 以及时间戳 TIMESTAMP 。
• 二进制数据类型，主要是 BLOB。用于存储图片、文档等二进制数据。

主流的数据库都支持这些常见的数据类型，但是在类型名称和细节上存在一些差异。另外，SQL 还提供其他的数据类型，例如 XML、JSON 以及自定义的数据类型。

72. CHAR 和 VARCHAR 类型的区别？

答案：CAHR 是固定长度的字符串，如果输入的内容不够使用空格进行填充，通常用于存储固定长度的编码；VARCHAR 是可变长度的字符串，通常用于存储姓名等长度不一致的数据。Oracle 中使用 VARCHAR2 表示变长字符串。

73. 如何创建一个表？

答案：SQL 中创建表的基本语句如下：

CREATE TABLE table_name
(
  column_1 data_type column_constraint,
  column_2 data_type,
  ...,
  table_constraint
);

其中 table_name 指定了表的名称，括号内是字段的定义，创建表时可以指定字段级别的约束（column_constraint）和表级别的约束（table_constraint）。以下是员工表（employee）的创建语句：

CREATE TABLE employee
    ( emp_id    INTEGER NOT NULL PRIMARY KEY
    , emp_name  VARCHAR(50) NOT NULL
    , sex       VARCHAR(10) NOT NULL
    , dept_id   INTEGER NOT NULL
    , manager   INTEGER
    , hire_date DATE NOT NULL
    , job_id    INTEGER NOT NULL
    , salary    NUMERIC(8,2) NOT NULL
    , bonus     NUMERIC(8,2)
    , email     VARCHAR(100) NOT NULL
    , CONSTRAINT ck_emp_sex CHECK (sex IN ('男', '女'))
    , CONSTRAINT ck_emp_salary CHECK (salary > 0)
    , CONSTRAINT uk_emp_email UNIQUE (email)
    , CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id)
    , CONSTRAINT fk_emp_job FOREIGN KEY (job_id) REFERENCES job(job_id)
    , CONSTRAINT fk_emp_manager FOREIGN KEY (manager) REFERENCES employee(emp_id)
    ) ;

74. 如何基于已有的表复制一个表？

答案：使用以下语句可以基于已有的表或者查询语句复制一个表：

CREATE TABLE table_name
    AS
SELECT ...;

查询的结果也会复制到新的表中，如果在查询中使用 WHERE 子句指定一个永不为真的条件，可以创建只有结构的空表。例如，以下语句基于 employee 表创建一个空的新表：

CREATE TABLE emp_new
    AS
SELECT *
  FROM employee
 WHERE 1=0;

MySQL 还支持以下语句复制一个空表：

CREATE TABLE emp_copy
  LIKE employee;

75. 什么是自增列？

答案：自增列（auto increment），也称为标识列（identity column），用于生成一个自动增长的数字。它的主要用途就是为主键提供唯一值。Oracle 使用标准 SQL 中的 GENERATED ALWAYS AS IDENTITY 表示自增列，MySQL 使用关键字 AUTO_INCREMENT 表示自增列。以下示例演示了自增列的使用：

-- Oracle 实现
CREATE TABLE emp_identity(
  emp_id     INT GENERATED ALWAYS AS IDENTITY, 
  emp_name   VARCHAR(50) NOT NULL,
  PRIMARY KEY (emp_id)
);

-- MySQL 实现
CREATE TABLE emp_identity(
  emp_id     INT AUTO_INCREMENT,
  emp_name   VARCHAR(50) NOT NULL,
  PRIMARY KEY (emp_id)
);

INSERT INTO emp_identity(emp_name) VALUES ('张三');
INSERT INTO emp_identity(emp_name) VALUES ('李四');
INSERT INTO emp_identity(emp_name) VALUES ('王五');

SELECT * FROM emp_identity;
emp_id |emp_name
------------------
1      |张三       
2      |李四      
3      |王五       

插入数据时，不需要为自增列提供输入值，系统自动生成一个增长的数字序列。

76. 如何修改表的结构？

答案：SQL 提供了 ALTER TABLE，用于修改表的结构：

ALTER TABLE table_name action;

其中，action 表示要执行的修改操作，常见的操作包括增加列，修改列，删除列；增加约束，修改约束，删除约束等。例如，以下语句可以为 emp_new 表增加一列：

ALTER TABLE emp_new
  ADD weight NUMERIC(4,2) DEFAULT 60 NOT NULL;

不同的数据库实现了各自支持的修改操作，具体实现可以查看产品的文档。

77. 如何删除一个表？

答案：SQL 中删除表的命令如下：

DROP TABLE table_name;

如果被删除的表是其他表的外键引用表，比如部门表（department），需要先删除子表。Oracle 支持级联删除选项，同时删除父表和子表：

DROP TABLE department CASCADE CONSTRAINTS;

78. DROP TABLE 和 TRUNCATE TABLE 的区别？

答案：DROP TABLE 用于从数据库中删除表，包括表中的数据和表结构自身。同时还会删除与表相关的的所有对象，包括索引、约束以及访问该表的授权。TRUNCATE TABLE 只是快速删除表中的所有数据，回收表占用的空间，但是会保留表的结构。

79. 什么是数据库事务？

答案：在数据库中，事务（Transaction）是指一个或一组相关的操作（SQL 语句），它们在业务逻辑上是一个原子单元。一个最常见的数据库事务就是银行账户之间的转账操作。比如从 A 账户转出 1000 元到 B 账户，其中就包含了多个操作：

1. 查询 A 账户的余额是否足够；
2. 从 A 账户减去 1000 元；
3. 往 B 账户增加 1000 元；
4. 记录本次转账流水。

数据库必须保证所有的操作要么全部成功，要么全部失败。如果从 A 账户减去 1000 元成功执行，但是没有往 B 账户增加 1000 元，意味着客户将会损失 1000 元。用数据库中的术语来说，这种情况导致了数据库的不一致性。

数据库事务拥有以下 4 个特性：原子性、一致性、隔离性以及持久性（ACID）。

• Atomic，原子性。一个事务包含的所有 SQL 语句要么全部成功，要么全部失败。例如，某个事务需要更新 100 条记录，但是在更新到一半时，系统出现故障，数据库必须保证能够回滚已经修改过的数据，就像没有执行过该事务一样。
• Consistency，一致性。事务开始之前，数据库位于一致性的状态；事务完成之后，数据库仍然位于一致性的状态。例如，银行转账事务中，如果一个账户扣款成功，但是另一个账户加钱失败，那么就会出现数据不一致（此时需要回滚已经执行的扣款操作）。另外，数据库还必须保证满足完整性约束，比如账户扣款之后不能出现余额为负数（可以在余额字段上添加检查约束）。
• Isolation，隔离性。隔离性与并发事务有关，一个事务的影响在其完成之前对其他事务不可见，多个并发的事务之间相互隔离。例如，账户 A 向账户 B 转账的过程中，账户 B 查询的余额应该是转账之前的数目；如果多人同时向账户 B 转账，结果也应该保持一致性，就像依次转账的结果一样。
• Durability，持久性。已经提交的事务必须永久生效，即使发生断电、系统崩溃等故障，数据库都不会丢失数据。

80. 数据库事务支持哪些隔离级别？

答案：当数据库存在并发访问时，可能导致以下问题：

• 更新丢失，当两个事务同时更新某一数据时，后者会覆盖前者的结果；
• 脏读，当一个事务正在操作某些数据但并未提交时，如果另一个事务读取到了未提交的结果，就出现了脏读；
• 不可重复读，第一个事务第一次读取某一记录后，该数据被另一个事务修改提交，第一个事务再次读取该记录时结果发生了改变；
• 幻象读，第一个事务第一次读取数据后，另一个事务增加或者删除了某些数据，第一个事务再次读取时结果的数量发生了变化。

为了解决并发访问可能导致的问题，数据库提供了不同的事务隔离级别：

	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读已提交	不会	可能	可能
可重复读	不会	不会	可能
序列化	不会	不会	不会

Oracle 默认的隔离级别为 READ COMMITTED，MySQL 中 InnoDB 存储引擎的默认隔离级别为 REPEATABLE READ。

81. MySQL 中的 InnoDB 和 MyISAM 存储引擎有什么区别？

答案：MySQL 的一大特点就是支持不同的存储引擎，存储引擎用于管理表中的数据并提供 SQL 操作接口。MySQL使用以下命令查看系统支持的存储引擎：

SHOW ENGINES;

主要的存储引擎包括 InnoDB 和 MyISAM。自从 MySQL 5.5 版本之后，默认使用 InnoDB 存储引擎。

InnoDB 存储引擎支持事务（ACID），提供了事务提交、回滚以及故障恢复能力，能够确保数据不会丢失。InnoDB 支持行级锁和多版本一致性的非锁定读取，能够提高并发访问和性能。InnoDB 使用聚集索引存储数据，能够减少使用主键查找时的磁盘 I/O。另外，InnoDB 还支持外键约束，能够维护数据的完整性。

MyISAM 存储引擎数据文件占用的空间更小。MyISAM 采用表级锁，限制了同时读写的性能，通常用于只读或者以读为主的应用。

下表是两者对于各种功能特性的支持比较。

特性	MyISAM	InnoDB
B 树索引	支持	支持
备份/时间点恢复	支持	支持
聚集索引	不支持	支持
压缩数据	支持	支持
数据缓存	不支持	支持
加密数据	支持	支持
外键支持	不支持	支持
全文搜索索引	支持	支持
空间数据类型	支持	支持
空间数据索引	支持	支持
哈希索引	不支持	不支持
索引缓存	支持	支持
锁定级别	表级	行级
MVCC	不支持	支持
复制	支持	支持
存储限制	256TB	64TB
数据库事务	不支持	支持

一般情况下，使用默认的 InnoDB 存储引擎即可，除非是有特殊的需求和应用场景。

82. 如何插入数据？

答案：SQL 主要提供了两种数据插入的方式：

• INSERT INTO … VALUES …
• INSERT INTO … SELECT …

第一种方式通过提供字段的值插入数据，例如：

INSERT INTO department(dept_id, dept_name) VALUES (1, '行政管理部');

MySQL 支持一次提供多个记录值的方式插入多条记录：

-- MySQL 实现
INSERT INTO department(dept_id, dept_name) VALUES (1, '行政管理部'), (2, '人力资源部'), (3, '财务部');

第二种方式使用查询的结果值插入多条数据，例如：

INSERT INTO emp_new
SELECT * FROM employee;

以上查询将员工表种的所有数据插入表 emp_new 中。

83. 如何修改数据？

答案：SQL 中的 UPDATE 语句用于更新表中的数据：

UPDATE table_name
   SET column1 = expr1,
       column2 = expr2,
       ...
[WHERE condition];

其中，table_name 是要更新的表名；SET 子句指定了要更新的列和更新后的值，多个字段使用逗号进行分隔；满足 WHERE 条件的数据行才会被更新，如果没有指定条件，将会更新表中所有的行。以下语句为表 emp_new 中的员工“赵云”增加 1000 元的月薪：

UPDATE emp_new
   SET salary = salary + 1000
 WHERE emp_name = '赵云';

84. 如何删除数据？

答案：SQL 中用于删除数据的命令主要是 DELETE 语句。

DELETE FROM table_name
[WHERE conditions];

DELETE 语句删除满足条件的数据；如果不指定 WHERE 子句，将会删除表中的所有数据。以下语句将会清空表 emp_new 中的所有数据：

DELETE FROM emp_new;

Oracle 中可以省略 FROM 关键字。

85. 删除数据时，DELETE 和 TRUNCATE 语句的区别？

DELETE	TRUNCATE
用于从表中删除指定的数据行。	用于删除表中的所有行，并释放包含该表的存储空间。
删除数据后，可以提交或者回滚。	操作无法回滚。
属于数据操作语言（DML）。	属于数据定义语言（DDL）。
删除数据较多时比较慢。	执行速度很快。

通常来说，使用 DELETE 语句删除数据时需要指定一个 WHERE 条件，否则会删除表中所有的数据；使用 TRUNCATE 语句需要小心，因为它会直接清空数据。

86. 什么是 MERGE 或者 UPSERT 操作？

答案：MERGE 是 SQL:2003 标准中引入的一个新的数据操作命令，它可以同时完成 INSERT 和 UPDATE 的操作，甚至 DELETE 的功能。

基本的 MERGE 语句如下：

MEGRE INTO target_table [AS t_alias] 
USING source_table [AS s_alias]
   ON (condition)
 WHEN MATCHED THEN
      UPDATE SET column1 = expr_1,
                 column2 = expr_2,
                 ...
 WHEN NOT MATCHED THEN
      INSERT (column1, column2, ...)
      VALUES (expr_1, expr_2, ...);

其中，target_table 是合并的目标表；USING 指定了数据的来源，可以是一个表或者查询结果集；ON 指定了合并操作的判断条件，对于数据源中的每一行，如果在目标表中存在满足条件的记录，执行 UPDATE 操作更新目标表中对应的记录；如果不存在匹配的记录，执行 INSERT 在目标表中插入一条新记录。

Oracle 提供了 MERGE 语句的支持，MySQL 使用另一种专用的 UPSERT 语法：

INSERT INTO target_table (column1, column2, ...)
SELECT col1, col2, ...
  FROM source_table s
    ON DUPLICATE KEY UPDATE
       column1 = s.col1,
       column2 = s.col2,
       ...;

87. 什么是索引？有哪些类型的索引？

答案：索引（Index）是一种数据结构，主要用于提高查询的性能。索引类似于书籍最后的索引，它指向了数据的实际存储位置；索引需要占用额外的存储空间，在进行数据的操作时需要额外的维护。另外，索引也用于实现约束，例如唯一索引用于实现唯一约束和主键约束。

不同的数据库支持的索引不尽相同，但是存在一些通用的索引类型，主要包括：

• B/B+ 树索引，使用平衡树或者扩展的平衡树结构创建索引。这是最常见的一种索引，几乎所有的数据库都支持。这种索引通常用于优化 =、<、<=、>、BETWEEN、IN 以及字符串的前向匹配查询。
• Hash 索引，使用数据的哈希值进行索引。主要用于等值（=）查询。
• 聚集索引，将表中的数据按照索引的结构（通常是主键）进行存储。MySQL 中称为聚集索引，Oracle 中称为索引组织表（IOT）。
• 非聚集索引，也称为辅助索引。索引与数据相互独立，MySQL 中的 InnoDB 存储的是主键值，Oracle 中存储的时物理地址。
• 全文索引，用于支持全文搜索。
• 唯一索引与非唯一索引。唯一索引可以确保被索引的数据不会重复，可以实现数据的唯一性约束。非唯一索引仅仅用于提高查询的性能。
• 单列索引与多列索引。基于多个字段创建的索引称为多列索引，也叫复合索引。
• 函数索引。基于函数或者表达式的值创建的索引。

索引是优化 SQL 查询的一个有效方法，但是索引本身也需要付出一定的代价，过渡的索引可能给系统带来负面的影响。

88. 如何查看 SQL 语句的执行计划？

答案：查询计划是数据库执行 SQL 的具体方式。包括读取表的方式，使用全表扫描还是使用索引；表的连接方式；预计占用的 CPU、IO 等资源。查看查询计划是进行 SQL 性能诊断和优化的基础。所有主流的数据库都提供了类似的查看执行计划的方式：EXPLAIN 命令。

MySQL 查看执行计划：

EXPLAIN
SELECT *
  FROM employee e
 WHERE emp_id = 5;

id|select_type|table|partitions|type |possible_keys|key    |key_len|ref  |rows|filtered|Extra|
--|-----------|-----|----------|-----|-------------|-------|-------|-----|----|--------|-----|
 1|SIMPLE     |e    |          |const|PRIMARY      |PRIMARY|4      |const|   1|     100|     |

由于 emp_id 是主键，执行计划显示通过主键索引（PRIMARY）进行查询。

Oracle 查看执行计划：

EXPLAIN PLAN FOR
SELECT *
  FROM employee e
 WHERE emp_id = 5;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT                                                                          |
-------------------------------------------------------------------------------------------|
Plan hash value: 897659145                                                                 |
                                                                                           |
-------------------------------------------------------------------------------------------|
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     ||
-------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT            |             |     1 |    56 |     1   (0)| 00:00:01 ||
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE    |     1 |    56 |     1   (0)| 00:00:01 ||
|*  2 |   INDEX UNIQUE SCAN         | SYS_C007418 |     1 |       |     0   (0)| 00:00:01 ||
-------------------------------------------------------------------------------------------|
                                                                                           |
Predicate Information (identified by operation id):                                        |
---------------------------------------------------                                        |
                                                                                           |
   2 - access("EMP_ID"=5)                                                                  |

同样显示使用了索引唯一扫描（INDEX UNIQUE SCAN）的方式。

另外，也可以通过一些图形工具或者数据库的其他方式查看 SQL 语句的执行计划。

89. 以下查询语句会不会使用索引？

CREATE INDEX idx ON test (col);
SELECT COUNT(*)
  FROM test
 WHERE col * 12 = 2400；

答案：不会。

解析：针对索引字段进行运算或者使用函数之后，会导致无法使用索引。可以将运算改到操作符的右边：

SELECT COUNT(*)
  FROM test
 WHERE col  = 2400 / 12；

90. 针对以下查询，如何创建索引？

SELECT *
  FROM test
 WHERE col1 = 100
   AND col2 = 'SQL'
SELECT *
  FROM test
 WHERE col2 = 'NoSQL';

答案：创建一个复合索引，并且将 col2 放在前面：

CREATE INDEX idx ON test (col2, col1);

解析：创建复合索引时需要注意字段的顺序。当查询条件使用索引左侧的字段时，可以有效的利用索引。

91. 员工表的 email 字段上存在唯一索引，以下查询会不会使用该索引？

SELECT *
  FROM employee e
 WHERE email LIKE 'zhang%';

答案：会。

解析：对于 LIKE 运算符，如果通配符不在最左侧，可以使用索引。但是 ‘%zhang’ 和 ‘%zhang%’ 无法使用索引。

92. 多表连接查询有哪三种执行方式？

答案：数据库在实际执行连接查询时，可以采用以下三种物理方式：

• 嵌套循环连接（Nested Loop Join），针对驱动表中的每条记录，遍历另一个表找到匹配的数据，相当于两层循环。Nested Loop Join 适用于驱动表数据比较少，并且连接的表中有索引的时候。
• 排序合并连接（ Sort Merge Join），先将两个表中的数据基于连接字段进行排序，然后合并。Sort Merge Join 通常用于没有索引，并且数据已经排序的情况，比较少见。
• 哈希连接（Hash Join），将一个表的连接字段计算出一个哈希表，然后从另一个表中一次获取记录并计算哈希值，根据两个哈希值来匹配符合条件的记录。Hash Join 对于数据量大，且没有索引的情况下可能性能更好。

MySQL 目前只支持 Nested Loop Join，不建议使用多个表的连接查询，因为多层循环嵌套会导致查询性能的急剧下降。

93. 什么是视图？

答案：视图（View）是一个存储在数据库中的 SELECT 语句。视图也被称为虚表，在许多情况下可以当作表来使用。视图与表最大的区别在于它自身不包含数据，数据库中存储的只是视图的定义语句。

视图具有以下优点：

• 替代复杂查询，减少复杂性；
• 提供一致性接口，实现业务规则；
• 控制对于表的访问，提高安全性。

但是，使用视图也需要注意以下问题：

• 不当使用可能会导致查询的性能问题；
• 可更新视图（Updatable View）需要满足许多限制条件。

94. 创建一个视图，包含员工所在部门、所属职位、姓名、性别以及邮箱信息？

答案：

CREATE OR REPLACE VIEW emp_info
    AS
SELECT d.dept_name,j.job_title, e.emp_name, e.sex, e.email
  FROM employee e
  JOIN department d ON (d.dept_id = e.dept_id)
  JOIN job j ON (j.job_id = e.job_id);

SELECT *
  FROM emp_info
 WHERE emp_name = '法正';

解析：视图的定义中可以像其他查询语句一样包含任意复杂的多表连接、子查询、以及集合操作等。

95. 什么是可更新视图？

答案：可更新视图是指可以通过对视图的 INSERT、UPDATE、DELETE 等操作，实现对视图对应的基础表的数据修改。通常来说，可更新视图必须是简单的查询语句，不能包含以下内容：

• 聚合函数，例如 SUM、AVG 以及 COUNT 等；
• DISTINCT 关键字；
• GROUP BY 或者 HAVING 子句；
• 集合操作符 UNION 等；
• 不同的数据库特定的限制

简单来说，可能导致无法通过视图找到对应基础表中的数据的操作都不允许。以下语句创建了一个简单的视图，只包含了开发部门的员工信息，并且隐藏了工资等敏感信息：

CREATE OR REPLACE VIEW emp_devp
    AS
SELECT emp_id, emp_name, sex, manager, hire_date, job_id, email
  FROM employee
 WHERE dept_id = 4
  WITH CHECK OPTION;

其中的 WITH CHECK OPTION 确保无法通过视图修改超出其可见范围之外的数据。以下是通过该视图修改员工信息的操作：

 UPDATE emp_devp
    SET email = 'zhaoyun@sanguo.net'
  WHERE emp_name = '赵云';

如果尝试更新非开发部门的员工，不会更新到任何数据：

 UPDATE emp_devp
    SET email = 'zhangfei@sanguo.net'
  WHERE emp_name = '张飞';

96. 什么是存储过程？

答案：存储过程（Stored Procedure）是存储在数据库中的程序，它是数据库对 SQL 语句的扩展，提供了许多过程语言的功能，例如变量定义、条件控制语句、循环语句、游标以及异常处理等等。一旦创建之后，应用程序（Java、C++ 等）可以通过名称调用存储过程。

存储过程的优点包括：

• 提高应用的执行效率。存储过程经过编译之后存储在数据库中，执行时可以进行缓存，可以提高执行的速度；
• 减少了应用与数据库之间的数据传递。调用存储过程时，只需要传递参数，业务代码已经存在数据中；
• 存储过程可以实现代码的重用。不同的应用可以共享相同的存储过程；
• 存储过程可以提高安全性。存储过程实现了代码的封装，应用程序通过存储过程进行数据访问，而不需要之间操作数据表。

另一方面，存储过程也存在一些缺点：

• 不同数据库的实现不同，Oracle 中称为 PL/SQL，MySQL 中称为 PSM，其他数据库也都有各自的实现；
• 存储过程需要占用数据库服务器的资源，包括 CPU、内存等，而数据库的扩展性不如应用；
• 存储过程的开发和维护需要专业的技能。

是否使用存储过程需要考虑具体的应用场景。对于业务变化快的互联网应用，通常倾向于将业务逻辑放在应用层，便于扩展；而对于传统行业的应用，或者复杂的报表分析，合理使用存储过程可以提高效率。

97. 如何创建存储过程？

答案：使用 CREATE PROCEDURE 语句创建存储过程，不同的数据库存在一些实现上的差异。以下语句创建了一个为员工表增加员工的存储过程：

-- MySQL 实现
DELIMITER $$
CREATE PROCEDURE insert_employee(IN pi_emp_id INT,
                                 IN pi_emp_name VARCHAR(50),
                                 IN pi_sex VARCHAR(10),
                                 IN pi_dept_id INT,
                                 IN pi_manager INT,
                                 IN pi_hire_date DATE,
                                 IN pi_job_id INT,
                                 IN pi_salary NUMERIC,
                                 IN pi_bonus NUMERIC,
                                 IN pi_email VARCHAR(100))
BEGIN
  DECLARE EXIT HANDLER FOR 1062
  SELECT CONCAT('Duplicate employee: ', pi_emp_id);
 
  INSERT INTO employee(emp_id, emp_name, sex, dept_id, manager,
                       hire_date, job_id, salary, bonus, email)
  VALUES(pi_emp_id, pi_emp_name, pi_sex, pi_dept_id, pi_manager,
         pi_hire_date, pi_job_id, pi_salary, pi_bonus, pi_email);

END$$
DELIMITER ;

-- Oracle 实现
CREATE OR REPLACE PROCEDURE insert_employee(IN pi_emp_id INT,
                                 IN pi_emp_name VARCHAR2,
                                 IN pi_sex VARCHAR2,
                                 IN pi_dept_id INT,
                                 IN pi_manager INT,
                                 IN pi_hire_date DATE,
                                 IN pi_job_id INT,
                                 IN pi_salary NUMERIC,
                                 IN pi_bonus NUMERIC,
                                 IN pi_email VARCHAR2)
BEGIN
  INSERT INTO employee(emp_id, emp_name, sex, dept_id, manager,
                       hire_date, job_id, salary, bonus, email)
  VALUES(pi_emp_id, pi_emp_name, pi_sex, pi_dept_id, pi_manager,
         pi_hire_date, pi_job_id, pi_salary, pi_bonus, pi_email);
EXCEPTION
  WHEN dup_val_on_index THEN
    RAISE_APPLICATION_ERROR(SQLCODE, 'Duplicate employee: '||pi_emp_id);
  WHEN OTHERS THEN
    RAISE;
END;

然后可以调用存储过程增加新的员工：

CALL (26, '张三', '男', 5, 2, CURRENT_DATE, 10, 5000, NULL, 'zhangsan@shuguo.com');

98. 如何删除存储过程？

答案：使用 DROP PROCEDURE 命令删除存储过程，使用 DROP FUNCTION 命令删除存储函数。以下语句删除存储过程 insert_employee：

DROP PROCEDURE insert_employee;

99. 什么是触发器？

答案：触发器（Trigger）是一种特殊的存储过程，当某个事件发生的时候自动执行触发器中的操作。最常见的触发器是基于表的触发器，包括 INSERT、UPDATE 和 DELETE 语句触发器。根据触发的时间，又可以分为 BEFORE 和 AFTER 触发器。另外，根据触发的粒度，又可以分为行级触发器和语句级触发器。

触发器典型的应用场景包括：

• 审计表的数据修改。某些表中可能包含敏感信息，比如员工的薪水，要求记录所有的修改历史。这种需求可以通过创建针对员工表的 语句级 UPDATE 触发器实现。
• 实现复杂的业务约束。在触发器中增加业务检查和数据验证，阻止非法的业务操作。

不过，触发器也可能带来一些问题。比如增加数据库服务器的压力；逻辑隐藏在数据库内部，应用端无法进行控制。

触发器的管理主要包括创建和删除：

• CREATE TRIGGER 用于创建触发器。
• DROP TRIGGER 用于删除触发器。

另外，Oracle 还支持 DDL 触发器和系统事件触发器。

100. 为员工表创建一个审计表和审计触发器，记录每次修改员工月薪的操作。

答案：

CREATE TABLE employee_audit
    ( emp_id     INTEGER NOT NULL
    , salary_old NUMERIC(8,2) NOT NULL
    , salary_new NUMERIC(8,2) NOT NULL
    , update_ts  TIMESTAMP NOT NULL
    );

-- MySQL 实现
DELIMITER $$
CREATE TRIGGER employee_audit 
  BEFORE UPDATE ON employee
  FOR EACH ROW 
BEGIN
  IF OLD.salary <> NEW.salary THEN
    INSERT INTO employee_audit(emp_id, salary_old, salary_new, update_ts)
    VALUES(OLD.emp_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
  END IF;
END$$
DELIMITER ;

-- Oracle 实现
CREATE OR REPLACE TRIGGER employee_audit
  BEFORE UPDATE ON employee
  FOR EACH ROW
DECLARE
BEGIN
  IF :OLD.salary <> :NEW.salary THEN
    INSERT INTO employee_audit(emp_id, salary_old, salary_new, update_ts)
    VALUES(:OLD.emp_id, :OLD.salary, :NEW.salary, CURRENT_TIMESTAMP);
  END IF;
END;

不同的数据库在语法上存在一些差异，但是基本的原理相同。然后可以修改员工的月薪，并且查看审计的结果：

UPDATE employee
   SET salary = salary + 1000
 WHERE emp_name = '张飞';

SELECT * FROM employee_audit;

总结

作为一份 SQL 常见面试题解析，本文主要给大家梳理了 SQL 中常见的各种数据操作以及数据库设计和开发的基本知识，并提供了 Oracle 和 MySQL 中的具体实现。SQL 是一门面向集合的编程语言，通过简单的声明就可以完成各种数据的操作；但是这种简单性也有可能导致性能问题。因此，理解数据库的原理和性能优化是学习进阶的必要技能。