SQL面试题大全 – wiki基地

作者： /

SQL面试题大全：从基础到高级，助你征服面试

在当今数据驱动的世界里，SQL（Structured Query Language，结构化查询语言）无疑是最重要的技能之一。无论是数据分析、数据工程、后端开发还是数据库管理，SQL 能力都是衡量技术水平的关键指标。因此，几乎所有的技术面试，无论职位高低，都少不了 SQL 相关的考察。

面对五花八门的 SQL 面试题，如何才能做到胸有成竹？本文旨在构建一个全面的 SQL 面试题知识体系，从最基础的概念到高级的优化技巧，为你提供详细的解析和实战示例，助你轻松应对各类 SQL 面试挑战。

文章结构如下：

第一部分：基础概念与语法 (Fundamental Concepts & Syntax)
* SQL 是什么？它有什么用？
* DDL、DML、DCL、TCL 的区别？
* 关系型数据库核心概念：表、列、行、主键、外键、唯一键、索引
* 基本数据类型与约束

第二部分：核心查询操作 (Core Query Operations)
* SELECT, FROM, WHERE 子句详解
* 聚合函数 (COUNT, SUM, AVG, MIN, MAX) 与 GROUP BY 子句
* HAVING 子句与 WHERE 子句的区别
* ORDER BY 子句
* LIMIT/TOP 子句 (分页查询)
* DISTINCT 关键字

第三部分：多表连接 (Joins)
* 四种主要的 JOIN 类型：INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN
* CROSS JOIN (交叉连接)
* SELF JOIN (自连接)
* 如何选择合适的 JOIN 类型？

第四部分：子查询与公共表表达式 (Subqueries & CTEs)
* 子查询的类型：独立子查询、关联子查询
* 子查询的用法：WHERE 子句中 (IN, EXISTS)、SELECT 子句中、FROM 子句中
* 公共表表达式 (CTE – Common Table Expression) 的概念与用法 (WITH 子句)
* 子查询与 CTE 的比较及适用场景

第五部分：窗口函数 (Window Functions)
* 什么是窗口函数？解决什么问题？
* OVER() 子句的理解 (分区 PARTITION BY, 排序 ORDER BY)
* 排名函数：ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
* 聚合函数作为窗口函数：SUM(), AVG(), COUNT(), MIN(), MAX() 与 OVER()
* 偏移函数：LAG(), LEAD()

第六部分：事务与并发控制 (Transactions & Concurrency Control)
* 什么是事务？
* ACID 特性详解 (原子性 Atomicity, 一致性 Consistency, 隔离性 Isolation, 持久性 Durability)
* 事务隔离级别 (Isolation Levels) 及可能出现的问题 (脏读 Dirty Read, 不可重复读 Non-repeatable Read, 幻读 Phantom Read)
* 死锁 (Deadlock) 的概念与避免

第七部分：索引与性能优化 (Indexing & Performance Optimization)
* 什么是索引？作用是什么？优缺点？
* 常见索引类型 (B-tree, Hash) 及原理简介
* 聚集索引 (Clustered Index) 与非聚集索引 (Non-Clustered Index) 的区别
* 何时创建索引？何时避免创建索引？
* 复合索引 (Composite Index) 与最左前缀原则
* 查询性能分析工具：EXPLAIN/EXPLAIN PLAN 的用法与输出解读
* 常见的 SQL 优化技巧：
* 避免 SELECT *
* WHERE 子句中的优化 (避免函数、类型转换)
* 优化 JOIN 操作
* 使用 UNION ALL 代替 UNION
* 批量操作代替逐行操作

第八部分：高级话题与常见场景题 (Advanced Topics & Common Scenarios)
* NULL 值的处理 (IS NULL, IS NOT NULL, COALESCE, NULLIF)
* 集合操作 (UNION, UNION ALL, INTERSECT, EXCEPT/MINUS)
* 视图 (View) 的作用
* 存储过程 (Stored Procedure), 函数 (Function), 触发器 (Trigger) (概念介绍)
* 面试场景题解析：
* 查找重复记录及删除重复记录
* 查找第 N 高的薪水
* 计算累积总和、移动平均
* 行转列 (Pivot) 与列转行 (Unpivot)
* 连续登录天数、最长连胜记录等问题

第九部分：面试技巧与准备建议 (Interview Tips & Preparation Advice)
* 如何有效准备 SQL 面试？
* 如何在面试中清晰地表达思路？
* 面对不会的问题怎么办？
* 向面试官提问的技巧

接下来，我们将展开详细描述每个部分。

第一部分：基础概念与语法 (Fundamental Concepts & Syntax)

1. SQL 是什么？它有什么用？
* 解释: SQL（Structured Query Language）是一种用于管理关系型数据库的标准语言。它允许用户创建、修改和查询数据库中的数据。
* 用途: 数据查询、数据插入、更新和删除、数据库结构定义（创建表、修改表）、数据库访问控制等。

2. DDL、DML、DCL、TCL 的区别？
* DDL (Data Definition Language – 数据定义语言): 用于定义和管理数据库对象的结构。
* 命令：CREATE (创建数据库、表、视图、索引等), ALTER (修改表结构等), DROP (删除数据库、表等), TRUNCATE (删除表中的所有行，但保留表结构)。
* DML (Data Manipulation Language – 数据操纵语言): 用于操作数据库中的数据。
* 命令：SELECT (查询数据), INSERT (插入数据), UPDATE (更新数据), DELETE (删除数据)。
* DCL (Data Control Language – 数据控制语言): 用于控制数据库用户的权限。
* 命令：GRANT (授予权限), REVOKE (收回权限)。
* TCL (Transaction Control Language – 事务控制语言): 用于管理数据库事务。
* 命令：COMMIT (提交事务), ROLLBACK (回滚事务), SAVEPOINT (设置保存点)。

3. 关系型数据库核心概念：表、列、行、主键、外键、唯一键、索引
* 表 (Table): 数据库中最基本的数据存储单元，由行和列组成。
* 列 (Column): 表中的一个字段，代表一种特定的数据类型（如姓名、年龄、薪水）。
* 行 (Row): 表中的一条记录，包含了一行数据的完整信息。
* 主键 (Primary Key): 一列或一组列，用于唯一标识表中的每一行。主键值必须唯一且非空 (NOT NULL)。每个表最多只能有一个主键。
* 外键 (Foreign Key): 表中的一列或一组列，其值与另一张表 (referenced table) 的主键或唯一键相对应。外键用于建立表之间的关系，并维护数据的一致性（引用完整性）。外键值可以重复，也可以为 NULL (取决于定义)。
* 唯一键 (Unique Key): 一列或一组列，用于确保该列或这些列的组合在表中是唯一的。唯一键值可以为 NULL (取决于数据库系统)，但只能有一个 NULL 值 (在大多数系统中)。一个表可以有多个唯一键。
* 索引 (Index): 一种数据库对象，用于加快数据的检索速度。它类似书的目录，通过存储特定列的值和指向表中对应行的指针来实现快速查找。

4. 基本数据类型与约束
* 数据类型: 常见的有整型 (INT, BIGINT)、浮点型 (FLOAT, DOUBLE)、十进制型 (DECIMAL)、字符串 (VARCHAR, TEXT)、日期时间 (DATE, TIME, DATETIME, TIMESTAMP)、布尔型 (BOOLEAN) 等。面试时可能会问到不同数据类型的区别和适用场景（如 DECIMAL 用于高精度计算）。
* 约束 (Constraints): 规定表中的数据必须满足的规则，用于保证数据的准确性和完整性。
* NOT NULL: 列值不能为空。
* UNIQUE: 列值必须唯一。
* PRIMARY KEY: 主键约束，是 UNIQUE 和 NOT NULL 的组合。
* FOREIGN KEY: 外键约束，维护表之间的引用完整性。
* CHECK: 限制列的取值范围或满足特定条件。
* DEFAULT: 为列指定默认值。

第二部分：核心查询操作 (Core Query Operations)

1. SELECT, FROM, WHERE 子句详解
* SELECT: 指定要检索的列。SELECT * 表示检索所有列。
* FROM: 指定要从中检索数据的表。
* WHERE: 指定筛选数据的条件。可以使用比较运算符 (=, !=, <, >, <=, >=)、逻辑运算符 (AND, OR, NOT)、范围条件 (BETWEEN … AND …)、列表条件 (IN, NOT IN)、模式匹配 (LIKE) 等。
* 例: SELECT employee_name, salary FROM employees WHERE salary > 5000 AND department = 'IT';

2. 聚合函数与 GROUP BY 子句
* 聚合函数: 对一组值执行计算，并返回单个值。
* COUNT(): 计算行数。COUNT(*) 计算所有行，COUNT(column_name) 计算指定列非 NULL 的行数。
* SUM(column_name): 计算指定列的总和。
* AVG(column_name): 计算指定列的平均值。
* MIN(column_name): 查找指定列的最小值。
* MAX(column_name): 查找指定列的最大值。
* GROUP BY: 将具有相同值的行分组，通常与聚合函数一起使用，以便对每个组进行计算。
* 例: SELECT department, AVG(salary) FROM employees GROUP BY department; (计算每个部门的平均薪水)

3. HAVING 子句与 WHERE 子句的区别
* WHERE: 用于在分组 之前 筛选行。不能直接使用聚合函数。
* HAVING: 用于在分组 之后 筛选组。可以且通常使用聚合函数。
* 执行顺序: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
* 例: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 6000; (计算平均薪水大于 6000 的部门)

4. ORDER BY 子句
* 用于对查询结果进行排序。默认为升序 (ASC)。使用 DESC 指定降序。可以按一列或多列排序。
* 例: SELECT * FROM employees ORDER BY salary DESC, employee_name ASC; (先按薪水降序，再按姓名升序排序)

5. LIMIT/TOP 子句 (分页查询)
* 用于限制查询返回的行数。
* MySQL, PostgreSQL: LIMIT count OFFSET offset; (OFFSET 跳过的行数，LIMIT 返回的行数)
* SQL Server: SELECT TOP count ...; 或 OFFSET offset ROWS FETCH NEXT count ROWS ONLY; (SQL Server 2012+)
* Oracle: 使用 ROWNUM (早期版本) 或 OFFSET/FETCH NEXT (12c+)
* 例 (MySQL): SELECT * FROM products ORDER BY price DESC LIMIT 10; (最贵的10个产品)
* 例 (MySQL 分页): SELECT * FROM orders LIMIT 10 OFFSET 20; (获取第 21 到 30 条记录)

6. DISTINCT 关键字
* 用于去除结果集中的重复行。
* 例: SELECT DISTINCT city FROM customers; (列出所有不同的客户所在城市)

第三部分：多表连接 (Joins)

1. 四种主要的 JOIN 类型
假设有 employees 表 (employee_id, name, department_id) 和 departments 表 (department_id, department_name)。

  • INNER JOIN (内连接): 返回在两个表中都存在匹配行的结果。

    • SELECT e.name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;
    • 结果只包含有部门的员工和有员工的部门。

  • LEFT JOIN (或 LEFT OUTER JOIN – 左连接): 返回左表中的所有行，以及右表中与左表匹配的行。如果右表中没有匹配，则右表的列显示为 NULL。

    • SELECT e.name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;
    • 结果包含所有员工，无论他们是否有对应的部门。没有部门的员工，department_name 列将显示 NULL。

  • RIGHT JOIN (或 RIGHT OUTER JOIN – 右连接): 返回右表中的所有行，以及左表中与右表匹配的行。如果左表中没有匹配，则左表的列显示为 NULL。

    • SELECT e.name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;
    • 结果包含所有部门，无论它们是否有对应的员工。没有员工的部门，name 列将显示 NULL。

  • FULL OUTER JOIN (全外连接): 返回左表和右表中的所有行。如果某行在其中一个表中没有匹配，则另一表对应的列显示为 NULL。

    • SELECT e.name, d.department_name FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.department_id;
    • 结果包含所有员工和所有部门。没有部门的员工，department_name 为 NULL；没有员工的部门，name 为 NULL。
    • (注意: MySQL 不直接支持 FULL OUTER JOIN，通常通过 UNION LEFT JOIN 和 RIGHT JOIN 来模拟)

2. CROSS JOIN (交叉连接)
* 返回左表中的每一行与右表中的每一行组合的结果集。结果集的行数等于两表行数的乘积，通常用于生成所有可能的组合或用于测试。
* SELECT * FROM table1 CROSS JOIN table2;

3. SELF JOIN (自连接)
* 将表与其自身连接，通常通过给表起别名来实现。常用于查询表中具有层级关系的数据，如查找员工的经理。
* 假设 employees 表有一个 manager_id 列，指向同一个表中的 employee_id。
* 例: SELECT e.name AS EmployeeName, m.name AS ManagerName FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id; (查找每个员工及其经理，包括没有经理的员工)

4. 如何选择合适的 JOIN 类型？
* 取决于你需要哪些数据。
* 只关心两表都有的数据：INNER JOIN。
* 需要左表全部数据，以及右表的匹配数据：LEFT JOIN。
* 需要右表全部数据，以及左表的匹配数据：RIGHT JOIN。
* 需要两表全部数据，无论是否匹配：FULL OUTER JOIN。
* 需要所有可能的组合：CROSS JOIN。
* 需要处理同一表中关联的数据：SELF JOIN。

第四部分：子查询与公共表表达式 (Subqueries & CTEs)

1. 子查询 (Subquery)
* 在一个 SQL 查询中嵌套另一个查询。子查询的结果通常用作外部查询的输入。
* 类型:
* 独立子查询 (Non-correlated Subquery): 子查询独立执行，不依赖于外部查询的任何列。
* 例 (在 WHERE 中使用 IN): 查找薪水高于平均薪水的员工。
* SELECT employee_name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
* 关联子查询 (Correlated Subquery): 子查询的执行依赖于外部查询的当前行。对于外部查询的每一行，子查询都会执行一次。
* 例 (在 WHERE 中使用 EXISTS): 查找至少有一笔订单的客户。
* SELECT customer_name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);
* EXISTS 通常比 IN 更高效，特别是当子查询返回大量结果时。
* 其他用法:
* 在 SELECT 中: 返回一个标量值（单行单列）。
* SELECT employee_name, salary, (SELECT AVG(salary) FROM employees) AS average_salary FROM employees; (效率低下，通常不用)
* 在 FROM 中 (派生表/Derived Table): 子查询的结果被当作一个临时表来使用。
* SELECT d.department_name, sub.avg_salary FROM (SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id) sub JOIN departments d ON sub.department_id = d.department_id; (查找每个部门的平均薪水)

2. 公共表表达式 (CTE – Common Table Expression)
* 使用 WITH 子句定义的命名临时结果集，可以在单个 SQL 语句 (SELECT, INSERT, UPDATE, DELETE) 中多次引用。它提高了查询的可读性和模块化。
* 用法:
* WITH cte_name (column1, column2, ...) AS ( -- CTE 定义查询 ) SELECT ... FROM cte_name ...;
* 例 (使用 CTE 查找平均薪水高于公司平均水平的部门):
* WITH DepartmentAverageSalary AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ), CompanyAverageSalary AS ( SELECT AVG(salary) AS company_avg_salary FROM employees ) SELECT d.department_name FROM DepartmentAverageSalary das JOIN departments d ON das.department_id = d.department_id CROSS JOIN CompanyAverageSalary cas WHERE das.avg_salary > cas.company_avg_salary;

3. 子查询与 CTE 的比较及适用场景
* 可读性: CTE 通常比嵌套多层的子查询更易读和理解，特别是复杂的查询。
* 复用性: CTE 可以在同一个查询语句中被多次引用，而子查询通常需要重新写。
* 递归: CTE 支持递归查询，可用于处理层级结构数据 (如组织架构树)。子查询不支持递归。
* 性能: 在大多数情况下，现代数据库的查询优化器能够很好地处理子查询和 CTE，性能差异不大。但在某些复杂场景下，CTE 由于其结构清晰，可能更易于优化器处理。
* 适用场景:
* 简单、一次性的嵌套查询：子查询可能更简洁。
* 复杂、多步骤的逻辑处理：CTE 更佳，因为它将逻辑分解为独立的、可命名的步骤。
* 需要多次引用同一结果集：CTE 更高效。
* 处理层级或图结构数据：必须使用递归 CTE。

第五部分：窗口函数 (Window Functions)

1. 什么是窗口函数？解决什么问题？
* 解释: 窗口函数在与当前行相关的“窗口”内执行计算。与聚合函数不同，窗口函数不会将行分组为单个输出行，而是为每一行返回一个结果。
* 解决问题: 用于解决需要在每一行上进行聚合计算，同时又保留原始行细节的场景。例如，计算每个员工在其部门内的排名、计算累积销售额、计算前后行的差值等。

2. OVER() 子句的理解
* OVER() 子句定义了窗口函数的计算范围，即“窗口”。
* PARTITION BY: 将结果集划分为多个分区。窗口函数在每个分区内独立计算。类似 GROUP BY 的分组概念，但不会折叠行。
* ORDER BY: 定义窗口内的行的顺序。对于排名函数和偏移函数，ORDER BY 是必须的。对于聚合函数，ORDER BY 定义了计算是累积的还是固定的。

3. 排名函数
* ROW_NUMBER(): 为窗口内的每一行分配一个唯一的序号，从 1 开始。
* RANK(): 为窗口内的每一行分配一个排名。如果有并列值，它们会得到相同的排名，下一个排名会跳过。
* DENSE_RANK(): 为窗口内的每一行分配一个排名。如果有并列值，它们会得到相同的排名，下一个排名 不会 跳过。
* NTILE(n): 将窗口内的行分成 n 个等级或组。

  • 例 (查找每个部门薪水最高的员工):
    • WITH RankedEmployees AS ( SELECT employee_name, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rnk FROM employees ) SELECT employee_name, department_id, salary FROM RankedEmployees WHERE rnk = 1;

4. 聚合函数作为窗口函数
* 聚合函数 (SUM, AVG, COUNT, MIN, MAX) 结合 OVER() 子句，可以在一个窗口内进行聚合计算。
* OVER (ORDER BY ...): 执行累计计算。
* OVER (PARTITION BY ...): 计算每个分区的总和、平均值等。
* OVER (PARTITION BY ... ORDER BY ...): 在每个分区内按顺序进行累计计算。
* 例 (计算每日销售额的累计总和):
* 假设 sales 表有 sale_date, amount
* SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date) AS cumulative_sum FROM sales ORDER BY sale_date;
* 例 (计算每个产品类别中每件产品的销售额占类别总销售额的比例):
* 假设 product_sales 表有 category, product, amount
* SELECT category, product, amount, SUM(amount) OVER (PARTITION BY category) AS category_total, amount * 100.0 / SUM(amount) OVER (PARTITION BY category) AS percentage_of_category FROM product_sales;

5. 偏移函数
* LAG(column_name, offset, default_value): 获取当前行 之前 某个偏移量的行的指定列的值。
* LEAD(column_name, offset, default_value): 获取当前行 之后 某个偏移量的行的指定列的值。
* 例 (计算相邻两天的销售额差异):
* 假设 daily_sales 表有 sale_date, amount
* SELECT sale_date, amount, amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS daily_difference FROM daily_sales ORDER BY sale_date;

第六部分：事务与并发控制 (Transactions & Concurrency Control)

1. 什么是事务？
* 解释: 事务是数据库操作的最小逻辑工作单元。它是一系列操作的集合，这些操作要么全部成功并提交，要么全部失败并回滚。
* 目的: 确保数据库的状态从一个一致性状态转移到另一个一致性状态，即使在系统发生故障时也能保证数据的完整性。

2. ACID 特性详解
* Atomicity (原子性): 事务中的所有操作是一个不可分割的单元。要么全部成功，要么全部失败（回滚到事务开始前的状态）。
* Consistency (一致性): 事务必须使数据库从一个有效状态转移到另一个有效状态。它不会破坏数据库的完整性约束（如主键、外键约束）。
* Isolation (隔离性): 并发执行的事务之间互不影响。一个事务的中间结果对其他事务是不可见的，直到该事务提交。
* Durability (持久性): 一旦事务提交，其结果就是永久性的，即使系统发生故障（如停电），数据也不会丢失。

3. 事务隔离级别 (Isolation Levels) 及可能出现的问题
为了解决并发事务带来的问题，SQL 定义了四种隔离级别，从低到高依次为：

  • Read Uncommitted (读未提交): 最低的隔离级别。一个事务可以读取另一个事务尚未提交的数据（可能发生脏读）。
  • Read Committed (读已提交): 一个事务只能读取另一个事务已经提交的数据。避免了脏读，但可能发生不可重复读和幻读。
  • Repeatable Read (可重复读): 同一个事务中多次读取同一数据，结果总是一致的。避免了脏读和不可重复读，但可能发生幻读。

  • Serializable (串行化): 最高的隔离级别。事务按串行顺序执行，完全避免了脏读、不可重复读和幻读。但并发性能最低。

  • 可能出现的问题解释:

    • 脏读 (Dirty Read): 一个事务读取了另一个事务尚未提交的数据，而这个数据随后又被回滚了。
    • 不可重复读 (Non-repeatable Read): 同一个事务中，两次读取同一数据，结果不一致。发生在另一个事务提交了对该数据的修改。
    • 幻读 (Phantom Read): 同一个事务中，两次执行同一个查询，第二次查询返回了第一次查询没有返回的行。发生在另一个事务提交了新的数据插入或删除操作。

4. 死锁 (Deadlock)
* 解释: 死锁是指两个或多个事务在执行过程中，因争夺资源而造成的一种互相等待的现象。如果没有外部干预，它们将永远无法继续执行。
* 避免/解决: 数据库管理系统通常有死锁检测机制，并在检测到死锁时选择一个事务作为牺牲品并将其回滚，从而解除死锁。开发者可以通过合理设计数据库结构、优化查询、按照固定顺序访问资源等方式来降低死锁发生的概率。

第七部分：索引与性能优化 (Indexing & Performance Optimization)

1. 什么是索引？作用是什么？优缺点？
* 解释: 索引是一种用于加快数据检索速度的数据库结构。它维护着数据列的值以及指向这些值所在行的物理位置的指针。
* 作用: 大幅提高 SELECT 查询的速度，尤其是在大型表上进行过滤 (WHERE 子句) 或排序 (ORDER BY 子句) 操作时。
* 优点: 显著加快数据检索速度。
* 缺点:
* 占用存储空间。
* 增加数据修改 (INSERT, UPDATE, DELETE) 的开销，因为修改数据时需要同时更新索引。
* 不恰当地使用索引可能反而降低性能。

2. 常见索引类型 (B-tree, Hash) 及原理简介
* B-tree 索引 (B-Tree Index): 最常见的索引类型。数据存储在平衡树结构中，每个叶节点包含实际的数据指针或数据本身。适用于等值查询 (=), 范围查询 (<, >, BETWEEN), 排序 (ORDER BY) 等。大多数关系型数据库默认使用 B-tree 索引。
* Hash 索引 (Hash Index): 基于哈希表实现。适用于等值查询 (=)。查找速度非常快，但在范围查询和排序方面无优势。不存储键的排序信息。

3. 聚集索引 (Clustered Index) 与非聚集索引 (Non-Clustered Index) 的区别
* 聚集索引: 决定了表中的数据行的物理存储顺序。一个表只能有一个聚集索引。数据行本身存储在索引的叶节点中。对表进行聚集索引排序后，数据实际上是按索引键的顺序物理存放的。主键通常默认创建聚集索引。
* 非聚集索引: 不改变表中数据行的物理存储顺序。索引单独存储，索引的叶节点包含索引键值以及指向实际数据行的指针 (通常是聚集索引键或行地址)。一个表可以有多个非聚集索引。

4. 何时创建索引？何时避免创建索引？
* 创建时机:
* 经常用于 WHERE 子句、JOIN 条件或 ORDER BY 子句的列。
* 区分度高（即列中唯一值多）的列。
* 用于主键和外键的列 (通常数据库会自动或建议创建索引)。
* 避免创建:
* 非常小的表 (数据量小，全表扫描可能更快)。
* 数据经常变动（插入、更新、删除频繁）的表。
* 区分度非常低的列 (如只有几个不同值的枚举列)。
* 不常用于查询条件的列。
* 在列上使用函数操作 (这会导致索引失效)。

5. 复合索引 (Composite Index) 与最左前缀原则
* 复合索引: 在多个列上创建的索引。例如，在 (column1, column2, column3) 上创建复合索引。
* 最左前缀原则: 当使用复合索引进行查询时，只有查询条件使用了索引的 最左边前缀 列时，索引才能被有效利用。例如，在 (A, B, C) 上的复合索引：
* 查询条件 WHERE A = ... 可以使用索引。
* 查询条件 WHERE A = ... AND B = ... 可以使用索引。
* 查询条件 WHERE A = ... AND C = ... 只能使用索引的 A 部分。
* 查询条件 WHERE B = ... 或 WHERE C = ... 不能使用索引。

6. 查询性能分析工具：EXPLAIN/EXPLAIN PLAN 的用法与输出解读
* 解释: 这些命令（在 MySQL 中是 EXPLAIN，在 Oracle/PostgreSQL 中通常是 EXPLAIN PLAN 后跟 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 等）用于分析 SQL 查询的执行计划。它们显示数据库如何执行查询，包括使用了哪些索引、JOIN 的顺序、扫描的行数等信息。
* 用法: 在需要分析的 SELECT/INSERT/UPDATE/DELETE 语句前加上 EXPLAIN。
* EXPLAIN SELECT * FROM employees WHERE salary > 5000;
* 输出解读: 关注的关键指标包括：
* type (或 access method): 显示访问表的方式 (如 ALL 全表扫描、index 索引扫描、ref 非唯一索引查找、eq_ref 唯一索引查找、range 范围扫描等)。理想情况是避免 ALL。
* possible_keys: 可能使用的索引。
* key: 实际使用的索引。
* key_len: 使用的索引字节长度 (越大越具体，越好)。
* rows: 估计需要扫描的行数 (越少越好)。
* Extra: 额外信息，如 Using index (覆盖索引，效率高), Using where (使用了 WHERE 条件), Using temporary (使用了临时表), Using filesort (使用了文件排序，通常需要优化) 等。

7. 常见的 SQL 优化技巧
* 避免 SELECT *: 只选择需要的列，减少数据传输和处理量。
* WHERE 子句中的优化:
* 避免在 WHERE 子句中的列上使用函数操作 (WHERE YEAR(order_date) = 2023 会导致索引失效，应改为 WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31')。
* 避免在 WHERE 子句中使用类型转换。
* 避免 LIKE '%keyword' 开头的模糊匹配 (无法利用索引，LIKE 'keyword%' 可以利用索引)。
* 避免使用 OR (可能导致索引失效，可以考虑使用 UNION ALL 或优化逻辑)。
* 优化 JOIN 操作:
* 确保 JOIN 条件列上有索引。
* 小表驱动大表 (某些数据库优化器不一定按 FROM 顺序，但理解这个概念有助于思考)。
* 选择合适的 JOIN 类型。
* 使用 UNION ALL 代替 UNION: 如果确定结果中没有重复行或者重复行可以接受，使用 UNION ALL 更快，因为它不需要去重。
* 批量操作代替逐行操作: INSERT INTO ... SELECT ... 或使用存储过程进行批量处理，比逐行循环插入或更新效率高得多。

第八部分：高级话题与常见场景题 (Advanced Topics & Common Scenarios)

1. NULL 值的处理
* IS NULL / IS NOT NULL: 用于判断列值是否为 NULL。WHERE column_name IS NULL 或 WHERE column_name IS NOT NULL。
* COALESCE(value1, value2, …): 返回第一个非 NULL 的值。常用于将 NULL 值替换为其他默认值。
* 例: SELECT COALESCE(email, 'N/A') AS contact_info FROM customers; (如果 email 为 NULL，则显示 ‘N/A’)
* NULLIF(value1, value2): 如果 value1 等于 value2，则返回 NULL；否则返回 value1。

2. 集合操作 (UNION, UNION ALL, INTERSECT, EXCEPT/MINUS)
* 用于合并或比较两个或多个 SELECT 语句的结果集。所有 SELECT 语句返回的列数和对应列的数据类型必须兼容。
* UNION: 合并结果集并去除重复行。
* UNION ALL: 合并结果集，保留所有行，包括重复行。
* INTERSECT: 返回在两个结果集中都存在的行（交集）。
* EXCEPT / MINUS (Oracle): 返回在第一个结果集中存在，但在第二个结果集中不存在的行（差集）。

3. 视图 (View) 的作用
* 解释: 视图是基于一个或多个表的查询结果而创建的虚拟表。它不存储实际数据，只是一个存储在数据库中的 SELECT 语句。
* 作用:
* 简化复杂查询: 将复杂的 JOIN、子查询等封装在一个视图中。
* 控制数据访问: 只向用户暴露视图中的部分列或行，隐藏敏感数据。
* 提供数据一致性: 可以在底层表结构变化时，通过修改视图定义来保持对用户的接口不变。

4. 存储过程 (Stored Procedure), 函数 (Function), 触发器 (Trigger)
* 存储过程: 预编译的 SQL 语句集合，存储在数据库中。可以接受参数，执行一系列操作（包括 DML、DDL、控制流语句），不一定返回结果集。常用于执行复杂的业务逻辑。
* 函数: 预编译的 SQL 语句集合，存储在数据库中。接受参数，必须返回一个值。可以在 SQL 语句中作为表达式使用。
* 触发器: 一种特殊的存储过程，在特定的数据库事件（如 INSERT, UPDATE, DELETE）发生时自动执行。常用于强制业务规则或维护数据一致性。

5. 面试场景题解析

  • 查找重复记录及删除重复记录

    • 查找重复: 使用 GROUP BY 和 HAVING COUNT(*) > 1 找出重复的组，然后 JOIN 回原表或使用窗口函数 ROW_NUMBER()。
      • SELECT column1, column2, COUNT(*) FROM your_table GROUP BY column1, column2 HAVING COUNT(*) > 1;
      • 使用 ROW_NUMBER() 查找：WITH RankedRows AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY some_unique_id) as rn FROM your_table ) SELECT * FROM RankedRows WHERE rn > 1;
    • 删除重复: 保留每组重复记录中的一条，删除其余的。
      • 使用 JOIN 和子查询：DELETE FROM your_table WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY some_unique_id) as rn FROM your_table) t WHERE t.rn > 1); (注意：不同的数据库删除重复记录有不同的惯用法，如 SQL Server 的 CTE + DELETE)

  • 查找第 N 高的薪水

    • 使用子查询和 LIMIT (MySQL/PostgreSQL): SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET N-1; (如果可能存在并列，这只会返回一个值)
    • 使用 DENSE_RANK() 窗口函数 (推荐，处理并列):
      • WITH RankedSalaries AS ( SELECT salary, DENSE_RANK() OVER(ORDER BY salary DESC) as rnk FROM employees ) SELECT DISTINCT salary FROM RankedSalaries WHERE rnk = N;

  • 计算累积总和

    • 使用聚合函数 SUM 作为窗口函数：
      • SELECT sale_date, amount, SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum FROM sales ORDER BY sale_date; (ROWS 子句是可选的，但显式指定有助于理解)

  • 行转列 (Pivot) 与列转行 (Unpivot)

    • 行转列: 将某个列的不同值转换为新的列。常使用 CASE WHEN 或数据库提供的 PIVOT 函数。
      • 例 (使用 CASE WHEN): 假设有 sales 表 (product, quarter, amount)，需要按产品统计每个季度的销售额。
        • SELECT product, SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS Q1_Sales, SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS Q2_Sales, ... FROM sales GROUP BY product;
    • 列转行: 将多个列转换为单列的多个行。常使用 UNION ALL 或数据库提供的 UNPIVOT 函数。
      • 例 (使用 UNION ALL): 假设有 product_sales 表 (product, Q1_Sales, Q2_Sales)。
        • SELECT product, 'Q1' AS quarter, Q1_Sales AS amount FROM product_sales UNION ALL SELECT product, 'Q2' AS quarter, Q2_Sales AS amount FROM product_sales;

  • 连续出现的问题

    • 这类问题常需要结合窗口函数 (LAG, LEAD, ROW_NUMBER)、自连接、或通过计算辅助列（如分组 ID）来解决。例如，计算连续登录天数，可以先计算每次登录与前一次登录的日期差，然后找出差值连续为 1 的序列。

第九部分：面试技巧与准备建议 (Interview Tips & Preparation Advice)

1. 如何有效准备 SQL 面试？
* 夯实基础: 确保对 DDL, DML, Join, Group By, Having 等核心概念和语法了如指掌。
* 理解原理: 不仅要记住语法，更要理解背后的原理，如 Join 的类型、索引的工作方式、事务的隔离级别等。
* 大量练习: 在 LeetCode 数据库题库、牛客网、或其他在线平台进行大量 SQL 练习。从简单题开始，逐步挑战中等和难题。
* 模拟面试环境: 尝试在限定时间内完成题目，习惯手写 SQL 或在简单的文本编辑器中编写。
* 熟悉常用数据库: 了解你面试公司可能使用的数据库 (MySQL, PostgreSQL, SQL Server, Oracle等) 的一些特定语法或函数差异。
* 复习场景题: 重点练习那些经典的场景题，如 Nth value, duplicates, cumulative sums, pivot/unpivot。

2. 如何在面试中清晰地表达思路？
* 倾听问题: 仔细听清面试官的问题，必要时复述或澄清，确保理解无误。询问关于数据结构、数据量、性能要求等细节。
* 分析问题: 将复杂问题分解为更小的步骤。
* 构思方案: 在草稿纸或白板上画出表结构、关键列，思考需要哪些表、如何连接。
* 逐步构建 SQL: 先写出基础的 SELECT FROM WHERE，然后逐步添加 JOIN, GROUP BY, HAVING, 窗口函数等。每一步都解释清楚为什么这样做。
* 解释优化: 如果有多种解决方案，可以比较它们的优缺点（如子查询 vs CTE, UNION vs UNION ALL），解释为什么选择当前的方案，或者讨论如何进一步优化（考虑索引、数据量等）。
* 代码规范: 编写可读性好的 SQL 代码，使用合适的别名，注意缩进。

3. 面对不会的问题怎么办？
* 诚实: 不要试图蒙混过关。可以坦诚地说“我对这个问题不太熟悉，但我可以尝试分析一下”。
* 思路为重: 即使写不出完整的代码，也要尽力分析问题、阐述解决思路、讨论可能的方法和遇到的挑战。展示你的逻辑思维能力和解决问题的潜力。
* 引导与学习: 如果面试官愿意提供提示，认真听取并尝试在提示下继续。面试后务必回去查阅和学习这个问题。

4. 向面试官提问的技巧
* 在面试结束前，面试官通常会问你有没有问题。这是一个展示你对公司、团队或技术栈兴趣的好机会。
* 可以询问团队日常如何使用 SQL、他们处理的数据规模、常用的数据库技术、对新入职员工的培养方式、团队面临的技术挑战等。
* 避免询问薪资福利（通常由 HR 负责）或在网上很容易查到的基本信息。

总结

SQL 面试考察的不仅仅是语法记忆，更重要的是对关系型数据库原理的理解、对数据处理逻辑的抽象能力，以及在各种场景下运用 SQL 解决实际问题的能力。本文涵盖了从基础到高级的众多知识点和常见题型，希望能为你构建一个清晰的 SQL 知识框架。

请记住，学习 SQL 和准备面试是一个持续的过程。通过反复练习、深入理解概念、并结合实际项目经验，你将能够更自信地面对任何 SQL 面试挑战。

祝你面试顺利，前程似锦！

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部