面试 - SQL QA

有关 SQL 在面试过程中的一些问题，SQL/数据库是任何与计算机科学相关的学术或学位课程的重要组成部分，这也是事实。

因此，大多数工程师确实对 SQL 概念有基本的了解，但是他们需要专注于面试官最有可能提出问题的 SQL 领域。因此，我们想吸引您的重点是使您在 SQL 面试中取得成功的相关和必要条件。

除了面试外，前提需要对 SQL 架构有一个整体的认证，《MySQL 实战 45 讲》的基础篇架构、日志、事务、索引、锁基本概念，SQL 实践篇的常见问题。

SQL 面试的主要问题¹

主键、普通索引、唯一索引、外键区别？

以 Mysql 为例，Mysql 中的表记录信息是以及索引存储的，同时索引是以B+树数据结构组织存储的，需要注意索引大小、维护开销；

1. 主键：单张表只能有一个，不能为 NULL，通常是 int 或 bigint 类型且自增的，InnoDB 中的主键是聚簇索引(行内容在索引节点上)，如果没有显示声明 Mysql 也会自动创建一个主键索引；不同行的主键索引不能相同，主键可以作为其他表的外键关联，表示表的一个行记录；
2. 普通索引：单张表可以指定多个普通索引，普通索引的列允许重复的元素(注意可能不符合最佳原则)，允许为 NULL，可以是任意数据类型(数值、日期、字符串)
3. 唯一索引：单张表中可以有多个唯一索引，唯一索引的列不同行的数据都是唯一的，唯一约束可能为 NULL(多行相同)；
4. 外键：
   • 外键是表中的一个字段，该字段是另一个表的主键，用于标识另一个表的独立一行的记录；
   • 一个表可以有多个外键；
   • 外键不会创建聚簇或非聚簇索引，需要自己手动加索引；

JOIN 的连接类型与差异？

JOIN 关键字用于关联多张表的结果数据，返回包含连接表中至少一个匹配项的行，连接类型有内连接、左连接、右连接、完全连接、交叉连接几类：

1. 内连接(简单连接)：SELECT * FROM tb1 INNER JOIN tb2 USING(c);
   • 两个表使用指定列连接，仅返回两个表都存在匹配的记录
   • 没有指定INNER JOIN也是按这个模式执行
2. 左连接(也叫左外连接): SELECT * FROM tb1 LEFT JOIN tb2 USING(c);
   • 两个表使用指定列连接，返回左表匹配的记录，即使右表连接的条件不满足；
   • 右表若不满足连接条件，返回为 NULL；
3. 右连接(也叫右外连接): SELECT * FROM tb1 RIGHT JOIN tb2 USING(c);
   • 返回右表和正确匹配左表的行记录，与LEFT JOIN相反的操作；
   • 引擎内部将右连接，转成左连接操作；
4. 全连接(也叫全外连接): SELECT * FROM tb1 FULL JOIN tb2 USING(c)
   • 返回左、右表匹配查询结果的组合，即等同于执行UNION两个查询的结果；
   • 基于ON tb1.c=tb2.c的查询会报错；
5. 交叉连接: SELECT * FROM tb1 CROSS JOIN tb2;
   • 如果不带WHERE条件，则返回两个表的笛卡尔积效果；
   • 带WHERE条件，返回两个表的 INNER JOIN 效果；
6. 自连接：SELECT * FROM tb1, tb2 WHERE tb1.c=tb2.c;
   • 类似于 INNER JOIN 效果，连接部分需要在WHERE条件中指定，使用SELECT * FROM tb1,tb2 USING(c)类似的 SQL 会解析错误；

• Tips:
  • 不加条件的内连接、自连接、全连接、交叉连接返回结果一致，左连接和右连接不加条件会报错；
  • USING(c)可以用类似ON tb1.x=tb2.y模式替换，两者差别是USING(c)将 c 列公共出来了；
  • 尽量采用INNERT JOIN、LEFT JOIN表示，语义简单明了；

设计规范化问题？

满足遵循第三范式即可，但设计数据库表字段最大程度的减少数据冗余；

• 第一范式(1NF)
  • 表的属性(列)不能包含多个值，应当是保证原子值；
  • EG: 比如用户有多个手机号码，我们通过,分隔符连接(不符合字段的原子性)，即不符合1NF，我们应该新增一条用户记录与该手机号对应；
  • 1NF 的问题: 为了保证字段原子值(用户名、地址、手机号)，用户基础数据有冗余(用户名、地址等信息)
• 第二范式(2NF)：
  • 表符合第 1NF，并且表的非素数属性(与对象易于关联的属性，比如人和年龄、姓名、性别等)，不依赖于其他候选键的子集
  • EG：比如{教师 ID, 年龄, 课程}，其中年龄是非素数属性，但依赖{教师 ID, 课程}候选键，因此不符合第二范式，我们需要将非质数属性拆分成独立的教师表来满足第二范式；
• 第三范式(3NF)
  • 表符合第 2NF，并且表中不能含多个功能依赖，不能存在功能依赖传递
  • EG：比如{教师 ID, 姓名, 年龄, 户籍 ID, 省份, 城市}，其中{姓名, 年龄}依赖教师ID，{省份, 城市}依赖户籍ID，{姓名, 年龄, 户籍 ID}依赖教师ID，存在多个功能的依赖，同时存在功能传递依赖，因此不符合第三范式；我们可以拆成{教师 ID, 姓名, 年龄, 户籍 ID}和{教师 ID, 户籍 ID, 省份, 城市}两个表来符合第三范式。简单的理解，第三范式对应 OOP 的的单一职责，即一个表不应该包含多个不同实体对象的属性信息；
• BCNF
  • 表满足第 3NF，并且每个每个表的主属性是唯一的
  • EG：按上述例子，后一个表中的{教师 ID, 户籍 ID, 省份, 城市}，应该改为{户籍 ID, 省份, 城市}来满足 BCNF

SuperKey 和候选键的区域？

• 超级键是列的组合，可以唯一标识表中的任何行。主键也是超级键。
• 候选键是一个超级键，包含可唯一标识行的最少列的超级键，通常就是主键。

SQL 中不同类型的语句？

• DML(数据操作语句)：CRUD 相关，比如 SELECT、UPDATE、INSERT、DELETE、CALL、EXPLAIN、LOCK TABLES
• DDL(数据定义语句)：创建、更改数据库表结构，处理数据库对象的设计和存储，比如 CREATE、ALTER、DROP、TRUNCATE、RENAME
• DCL(数据控制语句)：比如 GRANT、REVOKE
• TCL(事务性控制语句)：比如 BEGIN/START TRANSACTION、COMMIT、SAVEPOINT、ROLLBACK、SET TRANSATION

COMMIT 在 SQL 事务中的作用？

做事务的提交工作，将所有事务中的 SQL 结果永久存储在数据库中，事务中的操作内容，

数据库的事务的特性？

• ACID
  • 原子性：每个事务的操作要么全部成功，要么全部失败，不会出现部分成功或失败的情况；
  • 一致性：每个事务在操作前后，对数据库整体是一致的，即不会破坏数据库数据整体的一致；
  • 隔离性：多个事务执行期间彼此看到的数据视图版本是相互是独立和隔离的(MVCC)，Mysql 存在不同的隔离级别，默认是 RR；
  • 持久化：事务提交后，数据会持久化写入磁盘，即使是中间宕机、断电等情况发生；(基于 Mysql 的 Binlog 和 RedoLog 以及二阶段事务提交保证)

DELETE、DROP、TRUNCATE 的差别？

• 语法差异：
  • DELETE FROM TABLE、DELETE * FROM TABLE、DELETE FROM TABLE WHERE ...
  • DROP TABLE tb1
  • TRUNCATE TABLE tb1
• DELETE 删除：
  • DELETE 是一个 DML，而 DROP 和 TRUNCATE 属于 DDL；
  • DELETE 语义是按条件删除行记录；DROP 语义是丢弃表，表会被移除；TRUNCATE TABLE是清空表，同时会重置表的主键 ID 标识；
  • DELETE 删除行记录，在事务中操作的话，可以回滚
  • DELETE 删除不会重置主键 ID，但TRUNCATE TABLE会
  • DELETE 会触发触发器的执行
  • DELETE 在事务中执行过程是将删除记录移到 ROLLBACK 空间，回滚的话会取回数据；
  • 仅在按条件删除记录时候使用
• DROP，丢弃表：
  • 从数据库将表移除
  • 无法事务回滚
  • 不会触发触发器执行
  • 仅在不需要表时候使用
• TRUNCATE TABLE，清空表:
  • 清空表，但相关表结构、索引等记录都在
  • 无法事务回滚
  • 会重置自增主键为 1，类似一个全新的表
  • 操作比 DELETE 快，因为少了很多日志记录操作
  • 如果表有外键的话，无法使用
  • 不会触发触发器的执行
  • 无法执行 WHERE 条件
  • 仅在需要清空表时候可以使用这个

什么是数据库索引以及有哪些类型？

• 数据库索引是一种数据存储结构，用于加速数据的检索性能，从数据结构类型区分，Mysql 中的索引有 Hash、B+树类型
• InnoDB 引擎，按功能划分可以分为聚簇索引和非聚簇索引，非聚簇索引又包含普通索引、唯一索引、关联索引，索引支持前缀索引类型；
  • 聚簇索引：一张表的数据记录都聚簇在该索引上，主键索引为聚簇索引，一张表仅有一个聚簇索引；
  • 非聚簇索引：在非主键上创建的索引，称为非聚簇索引，也可以称为普通索引；在多个列上创建的索引称为关联索引，关联索引遵循最左前缀原则；唯一索引指通过索引可以确定在表中的唯一值记录；
• 查询过程中，如果没有做到索引覆盖，则需要回表操作，同时 Mysql 数据库还会基于索引下推来进行查询记录的加速优化！

什么叫子查询，子查询的目的？

子查询也叫嵌套查询，

25 个 SQL 性能面试问答