SQL面试 100 问

SQL语句
629
0
0
2023-01-12
MYSQL数据库-复合查询
上一篇
技术分享 | SQL 优化：ICP 的缺陷
下一篇

本文介绍并分析了 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)。主键为员工编号，部门编号字段是引用部门表的外键，职位编号字段是引用职位表的外键，经理编号字段是引用员工表自身的 外键。

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 + COALESCE(bonus, 0) FROM employee; 解析:查询结果中可以使用各种运算、函数以及表达式。COALESCE 函数用于将空值转换为 0。

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

答案: SELECT emp_name AS "姓名", salary * 12 + COALESCE(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)，如何知道每个学生的最高得分?

答案: 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 用于对分组后的数据进行过滤，两者可以结合使用。

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 MINUS 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)中的数据。不过，使 用横向(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() FROM employee GROUP BY ROLLUP (dept_id, job_id); 解析:GROUP BY 支持扩展的 ROLLUP 选项，可以生成按照层级进行汇总的结果，类似于财务报表中的小计、合计和总 计。MySQL 中使用 WITH ROLLUP，与 SQL 标准不太一致。

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

解析:CUBE 用于生成多维立方体式的汇总统计。例如，以下查询统计不同部门和职位员工的数量，同时统计部门所有职位的 员工数据，加上所有职位的员工数据，以及整个公司的员工数量。-- Oracle 实现 SELECT dept_id, job_id, COUNT() 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 行数据是所

56. 有职位的统计，与职位无关。然后使用 CASE 表达式进行转换显示。

答案: -- 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 提供了强大的数据分析功能。专用窗口函数主要包括 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)分组后的数据计算平均月薪，为每个员工返回一条记录。窗口函数中的 PARTITION BY 作用类似于 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 定义了大量的数据类型，其中最常见的类型包括字符类型、数字类型、日期时间类型和二进制数据类型。日期时间类型，分为日期 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 支持级联删除选项，同时删 除父表和子表: -- Oracle 实现 DROP TABLE department CASCADE CONSTRAINTS;

78. DROP TABLE 和 TRUNCATE TABLE 的区别?

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

79. 什么是数据库事务?

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

从 A 账户减去 1000 元; 往 B 账户增加 1000 元; 记录本次转账流水。数据库必须保证所有的操作要么全部成功，要么全部失败。如果从 A 账户减去 1000 元成功执行，但是没有往 B 账户增加 1000 元，意味着客户将会损失 1000 元。用数据库中的术语来说，这种情况导致了数据库的不一致性。数据库事务拥有以下 4 个特性:原子性、一致性、隔离性以及持久性(ACID)。Atomic，原子性。一个事务包含的所有 SQL 语句要么全部成功，要么全部失败。例如，某个事务需要更新 100 条记录，但是 在更新到一半时，系统出现故障，数据库必须保证能够回滚已经修改过的数据，就像没有执行过该事务一样。Consistency，一致性。事务开始之前，数据库位于一致性的状态;事务完成之后，数据库仍然位于一致性的状态。例如，银 行转账事务中，如果一个账户扣款成功，但是另一个账户加钱失败，那么就会出现数据不一致(此时需要回滚已经执行的扣款 操作)。另外，数据库还必须保证满足完整性约束，比如账户扣款之后不能出现余额为负数(可以在余额字段上添加检查约 束)。Isolation，隔离性。隔离性与并发事务有关，一个事务的影响在其完成之前对其他事务不可见，多个并发的事务之间相互隔 离。例如，账户 A 向账户 B 转账的过程中，账户 B 查询的余额应该是转账之前的数目;如果多人同时向账户 B 转账，结果也 应该保持一致性，就像依次转账的结果一样。Durability，持久性。已经提交的事务必须永久生效，即使发生断电、系统崩溃等故障，数据库都不会丢失数据。数据库通常 使用重做日志(redo log)来保证数据的持久性。

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

答案: 当数据库存在并发访问时，可能导致以下问题: 更新丢失，当两个事务同时更新某一数据时，后者会覆盖前者的结果; 脏读，当一个事务正在操作某些数据但并未提交时，如果另一个事务读取到了未提交的结果，就出现了脏读; 不可重复读，第一个事务第一次读取某一记录后，该数据被另一个事务修改提交，第一个事务再次读取该记录时结果发生了改 变; 幻象读，第一个事务第一次读取数据后，另一个事务增加或者删除了某些数据，第一个事务再次读取时结果的数量发生了变 化。为了解决并发访问可能导致的问题，数据库提供了不同的事务隔离级别: 脏读 不可重复读 幻读 「读未提交」 可能 可能 可能 「读已提交」 不会 可能 可能 「可重复读」 不会 不会 可能 「序列化」 不会 不会 不会 Oracle 默认的隔离级别为 READ COMMITTED，MySQL 中 InnoDB 存储引擎的默认隔离级别为 REPEATABLE READ。

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

答案:MySQL 的一大特点就是支持不同的存储引擎，存储引擎用于管理表中的数据并提供数据操作接口。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 索引，使用数据的哈希值进行索引。主要用于等值(=)和 IN 查询。聚集索引，将表中的数据按照索引的结构(通常是主键)进行存储。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)进行查询。

另外，也可以通过一些图形工具或者数据库提供的其他方式查看 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;

MYSQL数据库-复合查询
上一篇
技术分享 | SQL 优化：ICP 的缺陷
下一篇