SQL面试必备：100道高频考题解析

前言

在众多IT职场中，SQL技术一直是一个非常重要的技能点。如果你正在准备SQL相关的面试，那么这份“SQL面试 100 问”绝对是你不能错过的宝藏！

这份清单涵盖了100道高频考题，从基础知识到复杂应用都有所涉及，帮助你全面掌握SQL面试必备技能，轻松应对各种挑战。

同时，每个问题还有详尽的解析和答案，让你更好地理解SQL核心概念和编程思路。

没有什么比自信满满地走进面试室更能给你加分了，快来查漏补缺，提升自己的SQL实力吧！

本文介绍并分析了 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 子句指定过滤条件，只有满足条件的数据才会返回。除了等于(=)，还可以使用大于(>)、大 于等于(>=)、小于( 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

  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+ 树索引，使用平衡树或者扩展的平衡树结构创建索引。这是最常见的一种索引，几乎所有的数据库都支持。这种索引通 常用于优化 =、