数据库 高频面试题

之前以为面经只是死记硬背的东西，后来发现记住了它们，对自己对知识的理解确实有帮助，难怪语文的文章老是要求背背背。

前言

这次的面经整理分为以下几个部分，希望对大家的工作有帮助。

内容	链接地址
Java 基础	传送门
Java 集合	传送门
Java 多线程	传送门
Java虚拟机	传送门
计算机网络	传送门
数据结构和算法	传送门
数据库	传送门
JavaWeb	传送门
设计模式	传送门
Spring、MyBatis	传送门

1 结构化查询语言分类

名称	解释	命令
DQL（数据查询语言）	用于查询数据库数据	SELECT
DDL（数据定义语言）	用于操作数据库对象，如数据库和表等	CREATE、DROP、ALTER
DML（数据管理语言）	用于操作数据库对象中所包含的数据	INSERT、UPDATE、DELETE
TCL（事务控制语言）	用于管理数据库，如管理权限和数据更改	GRANT、COMMIT、CALLBACK

1.1 数据查询语言

查询的结果是一个虚拟的表格，且在进行查询之前需要指定库名：use myemployees;

1）SELECT 语句执行顺序

开始->FROM子句->（连接查询）-> WHERE子句 ->GROUP BY子句 -> HAVING子句 -> SELECT子句 -> ORDER BY子句 -> LIMIT子句 -> 最终结果

2）各个查询方式

查询方式	关键字	搭配使用
条件查询	WHERE	<、LIKE、IN、BETWEEN AND 、AND
分组查询	GROUP BY	select 函数 from 表【where 分组前筛选】group by 分组的列表【having 分组后的筛选】
子查询	SELECT	……
排序查询	ORDER BY	ORDER BY 字段 【ASC/DESC】，字段【ASC/DESC】：先按第一字段排序，再按第二字段排序
分页查询	LIMIT	……
联合查询	UNION	查询语句1 union 查询语句2：要查询的结果来自于多个表，且没有直接的连接关系，但查询的信息一致
连接查询	★

1. 内连接：FROM table1 INNER JOIN table2 ON 条件；得到的是条件中两者都匹配的行；
2. 左外连接：FROM table1 LEFT OUTER JOIN table2 ON 条件； 得到的是左表全部行 + 右表中匹配的行，如果左表中某行在右表中没有匹配的行，则显示NULL。
3. 右外连接：与左外连接相反；
4. 全外连接：显示全部行，左表在右边没有的显示 NULL，右表在左边没有的显示 NULL；
5. 交叉连接；

关于连接查询的内连接和外连接参考这篇博客。传送门

1.2 数据定义语言

删除数据库

DROP DATABASE [IF EXISTS] 数据库名；

删除表

DROP TABLE [IF EXISTS] 表名

注意：IF EXISTS 最好要加上，因为如果删除不存在的数据表会抛出错误

修改表（ALTER TABLE）

添加字段 : ALTER TABLE 表名 ADD 字段名 属性

删除字段 : ALTER TABLE 表名 DROP 字段名

修改表名 :ALTER TABLE 旧表名 RENAME AS 新表名

修改字段 :

• ALTER TABLE 表名 MODIFY 字段名 属性：修改字段属性
• ALTER TABLE 表名 CHANGE 旧字段名 新字段名 属性：修改字段

1.3 数据管理语言

INSERT命令

INSERT INTO 表名[(字段1,字段2,字段3,...)] VALUES('值1','值2','值3')

UPDATE 命令

UPDATE 表名 SET column_name=value [,column_name2=value2,...] [WHERE condition];

DELETE命令

DELETE FROM 表名 [WHERE condition];

TRUNCATE命令

TRUNCATE [TABLE] table_name;

注意：区别于 DELETE 命令

• 相同：都能删除数据 , 不删除表结构 , 但 TRUNCATE 速度更快

• 不同：使用 TRUNCATE TABLE 不会对事务有影响

1.4 常见关键字

字段名	作用
DISTINCT	去重
AS	起别名
CONCAT	字符串拼接
LENGTH	字符串长度
UPPER/LOWER	将字符串变成大写/小写
COUNT	计数
TRIM	去掉字符串两边的空格
SUM	求和
AVG	求平均

 

2 引擎

1 什么是存储引擎

存储引擎指的就是 MySQL 中将数据保存到磁盘中的技术。主要有 MyISAM 和 InnoDB，这两者的区别在于 InnoDB 支持事务、行级锁和外键约束，但不支持全文索引，同时存储空间较大。

2 在创建表时配置存储引擎

CREATE TABLE 表名(
   -- 省略一些代码
   -- Mysql注释
   -- 1. # 单行注释
   -- 2. /*...*/ 多行注释
)ENGINE = MyISAM (or InnoDB) , CHARSET = utf8; -- MySQL 的默认存储编码是不支持中文的， 如无设定 , 则根据MySQL数据库配置文件 my.ini 中的参数设定

3 如何查看当前表所使用的存储引擎

# 反向得到当初创建数据库或者表的命令
SHOW CREATE DATABASE bank;
SHOW CREATE TABLE employees;

4 MyISAM 存储引擎与 InnoDB 引擎的区别

MyISAM 是 MySQL 原本的默认引擎，不过现在变成了 InnoDB；（MyISAM 发音为 “my-z[ei]m”; InnoDB 发音为 “in-no-db” ）

两者的区别如下：

名称	MyISAM	InnoDB
事务处理	不支持	支持
数据行级锁	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约两倍

⼀般况下我们选择 InnoDB 都是没有问题的，如果是某些情况下你并不在乎可扩展能⼒和并发能⼒，也不需要事务⽀持，也不在乎崩溃后的安全恢复问题的话，选择 MyISAM 也是⼀个不错的选择。但是⼀般情况下，我们都是需要考虑到这些问题的。

5 InnoDB 与 MyISAM 这两种存储引擎存储的物理文件的区别

所谓存储引擎就是 MySQL 中将数据保存到磁盘中的技术，事实上，MySQL 数据表以文件方式存放在磁盘中，存储位置为：C:\ProgramData\MySQL\MySQL Server 8.0\Data，其中目录名对应数据库名，该目录下的文件名对应数据表名。

InnoDB 与 MyISAM 这两种存储引擎存储的物理文件有一定区别：

• InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件
• MyISAM类型数据表对应三个文件 :
  • * . frm – 表结构定义文件
  • * . MYD – 数据文件 ( data )
  • * . MYI – 索引文件 ( index )

 

3 事务

1 什么是事务

事务是将一组 SQL 语句放在同一批次内去执行的操作，如果其中一条 SQL 语句出错，则该批次内的所有 SQL 都将被取消执行。

事务是逻辑上的一组操作，要么都执行，要么都不执行。

2 事务的四大特性

1. 原子性： 事务是最小的执行单位，不允许分割。即保证了 SQL 语句要么都成功，要么都失败；
2. 一致性： 事务前后的数据完整性要保持一致；
3. 隔离性： 并发访问数据库时，一个事务不会被其他事务所干扰，各个事务之间是相互隔离的；
4. 持久性： 一个事务被提交之后，它对数据库中数据的改变是持久的。

3 并发事务带来的问题

在典型的应用程序中，多个事务经常会操作相同的数据来完成各自的任务，即并发事务。在这个过程中，可能会导致以下问题：

• 脏读（Dirty read）: 一个事务读取了另一个事务还未提交（修改）的数据；
• 不可重复读（Unrepeatableread）: 一个事务多次读取同一数据，而该数据在第一个事务读取后被第二个事务所修改了，所以导致第一个事务多次读取的结果不一样；
• 幻读（Phantom read）: 一个事务多次读取同一数据，而在这个事务内，该事务读取到了别的事务所插入或删除的数据，导致前后读取的结果不一样。

4 事务隔离级别有哪些？可以防止哪些并发问题

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

隔离级别	脏读	不可重复读	幻读
READ-UNCOMMITTED（读取未提交）	√	√	√
READ-COMMITTED（读取已提交）	×	√	√
REPEATABLE-READ（可重复读）	×	×	√
SERIALIZABLE（可串行化）	×	×	×

MySQL 默认的是 可重复读 隔离级别，事务隔离机制的实现基于锁机制和并发调度。

5 事务的代码实现

-- 使用set语句来改变自动提交模式
SET autocommit = 0;   /*关闭*/
SET autocommit = 1;   /*开启*/

-- 注意:
--- 1.MySQL中默认是自动提交
--- 2.使用事务时应先关闭自动提交
SET autocommit = 0;   /*关闭*/

-- 开始一个事务,标记事务的起始点
START TRANSACTION  

--这里假设有两个 SQL 语句
-- 

-- 提交一个事务给数据库
COMMIT

-- 将事务回滚,数据回到本次事务的初始状态
ROLLBACK

-- 还原MySQL数据库的自动提交
SET autocommit =1;

-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点

/*
测试题目

A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000

创建数据库shop和创建表account并插入2条数据
*/
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;

CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
# 反引号，它是为了区分MYSQL的保留字与普通字符而引入的符号
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00);

-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION;  -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# ROLLBACK;
SET autocommit = 1; -- 恢复自动提交

 

4 索引

1 什么是索引（MySQL 官方）

索引相当于一个目录，是一种帮助 MySQL 高效获取数据的数据结构。

2 索引有什么优缺点

优点：建立它可以更快地查询数据库表中的数据；

缺点：

• 时间方面：创建索引需要时间，同时当对表中的数据进行增、改、查时，索引也要动态的维护，会降低增、改、删的执行效率；
• 空间方面：索引需要占物理空间。

3 索引的分类

从物理存储角度分：

• 聚簇（cù）索引：将数据存储与索引放到了一块，找到索引也就找到了数据；
• 非聚簇索引：将数据存储与索引分开存放；

从逻辑角度分：

1. 主键索引（PRIMARY KEY）：平常用的主键就是一个索引。
   • 确保数据记录的唯一性，一张表中只能有一个主键索引，不允许为 Null 值；
2. 唯一索引（UNIQUE KEY）：
   • 确保数据记录的唯一性，一张表中可以有多个唯一索引，允许为 Null 值；
3. 普通索引：普通索引（KEY）
4. 全文索引： 是目前搜索引擎使用的一种关键技术。
   • MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
   • MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引；
   • 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

4 创建索引的代码实现

#方法一：创建表时
  　　CREATE TABLE 表名 (
               字段名1 数据类型 [完整性约束条件…],
               字段名2 数据类型 [完整性约束条件…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [索引名] (字段名[(长度)] [ASC |DESC])
               );

#方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (字段名[(长度)] [ASC |DESC]) ;

#方法三：ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;
                                                
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;

5 创建索引的原则

1. 较频繁作为查询条件的字段才去创建索引；
2. 更新频繁的字段不适合创建索引；
3. 小数据量的表不建议加索引；
4. 最左前缀匹配原则；
5. 限制索引的数目。

6 说一下最左前缀匹配原则

该原则针对的是联合索引，在检索数据时从联合索引的最左边开始匹配，遇到范围查询(>、<、between、like)就会停止匹配。

例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；

其中联合索引指的是 MySQL 可以使用多个字段同时建立一个索引。

7 索引什么时候会失效

1. 对索引使用函数；
2. 对索引使用运算；
3. 模糊查询时，索引在通配符 % 之后；
4. 使用 NOT、IN 等进行条件查询时；
5. 当 MySQL 认为全表查询更快时。

8 索引的底层实现是什么？这两者有什么区别？

索引又分为 Hash 索引和 B 树索引，其中 Hash 索引底层就是 Hash 表，B 树索引的底层是 B+ 树。

这两者的区别是：

1. Hash 索引在一般情况下进行等值查询时更快，然而不支持范围查询；而 B 树索引两种查询都支持；
2. Hash 索引的查询效率不稳定，而 B 树索引相对稳定；
3. Hash 索引任何时候都避免不了回表查询数据，而 B 树索引在符合某些条件，比如聚簇索引的时候可以只通过索引完成查询，不需要回表查询。

所以大多数情况下，直接选择 B 树索引可以获得稳定且较好的查询速度，而不需要使用 Hash 索引。

 

6 锁

1 锁的定义

当数据库存在并发事务时，需要一些机制来保证访问的次序，从而保证数据的一致性，锁便是这样一种机制。

2 锁的种类

1. 如果按锁级别分类则有：共享（读）锁、排他（写）锁；
2. 按锁粒度从大到小分类：表锁，页锁和行锁；
3. 编程时的两种并发思想：悲观锁、乐观锁。

3 什么是共享锁和排他锁

共享锁： 又叫做读锁。 当事务 A 对数据 B 加上读锁时，其它事务仍然可以并发地读取数据，但是包括事务 A 在内的所有事务都不能对数据 B 进行修改，直到所有事务释放对该数据的读锁。

排他锁：又叫做写锁。当事务 A 对数据 B 加上写锁后，其它事务不能再对数据 B 加任何类型的锁。获得写锁的事务既能读数据，又能修改数据。

4 根据锁的粒度区分锁

行级锁：行级锁是 MySQL 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。

特点：加锁慢、开销大；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁：表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，被大部分 MySQL 引擎支持。

特点：加锁快，开销小；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

页级锁：页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁，一次锁定相邻的一组记录。

5 介绍一下乐观锁和悲观锁

乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作，避免其他事务意外修改数据，因此在操作数据时会直接把数据锁住。

乐观锁：假设不会发生并发冲突，认为别人不会同时修改数据，因此乐观锁不会上锁，只在提交操作时检查是否违反数据完整性。实现方式：一般使用版本号机制或 CAS 算法实现。

两种锁的使用场景：

• 乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量；
• 悲观锁适合多写的场景。

6 什么是死锁，怎么解决

死锁是指两个或多个事务互相持有对方所需要的资源，并请求锁定对方的资源，导致这些事务一直处于等待其它事务释放资源的状态。

解决方式：

1. 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
2. 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度；
3. 如果不同事务并发存取表，尽量约定以一定的顺序访问表，可以大大降低死锁机会。

 

7 其它重要问题

1 早期的数据库连接的过程是怎样的

1. 加载数据库驱动程序；
2. 通过 JDBC（Java Database Connection） 建立数据库连接；
3. 访问数据库，执行 sql 语句；
4. 断开数据库连接。

2 数据库连接池了解吗

数据库连接池就是通过一种池化的思想来进行数据库的连接，即建立一个连接的“缓冲池”，每次建立数据库连接的过程就是从该池中拿出连接的过程，使用完毕后再放回去，减少了资源的消耗，提高了数据库连接的可管理性。

3 什么是 SQL 注入，怎么防止 SQL 注入

SQL 注入就是攻击者在用户输入的字符串中加入其它 SQL 语句，如果在设计不良的程序中忽略了检查，那么这些注入进去的 SQL 语句就会被数据库服务器误认为是正常的 SQL 语句而运行，从而导致数据库执行计划外的命令或者访问未被授权的数据。

如何防止 SQL 注入：

1. 利用正则表达式来过滤输入内容；
2. 避免使用动态 SQL；
3. 使用参数化查询：此时，数据库服务器不会将参数的内容视为 SQL 语句的一部分来进行处理，而是在数据库完成 SQL 语句的编译之后，才套用参数运行，因此就算参数中含有破坏性的指令，也不会被数据库所运行。

4 一条 SQL 语句执行很慢的原因

1. 大多数情况下很正常，偶尔很慢，则有如下原因：

   • 数据库在刷新脏页；
   • 执行的时候，遇到锁，如表锁、行锁。

2. 这条 SQL 语句一直执行的很慢，则有如下原因：

   • 没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引；
   • 数据库选错了索引。

5 为什么尽量设定一个主键

主键是数据库的数据行在整张表中唯一性的保障，即使业务上本张表没有主键，也建议添加一个自增长的 ID 列作为主键。设定了主键之后，也可以使得查询速度加快。

6 SQL 怎么优化

1. 统一 SQL 语句的格式；
2. 最大化利用索引：比如尽量避免在索引字段前加上模糊查询，从而减少全表查询；
3. SQL 语句的优化：比如避免使用 SELECT * 来查询所有数据、连接多个表时，尽量使用表的别名.列名的形式等；
4. 考虑使用临时表暂存中间结果。

7 为什么使用数据库

当数据保存到内存（数组、集合等）中时，其虽然存取数据快，但此时的数据并不能永久保存，极易出现丢失；当数据保存到文件中时，虽然可以永久保存，但存取数据时会有频繁的 IO 操作，导致速度比内存操作慢，同时查询数据也不方便；

所以，数据库就应运而生了。数据库有以下优点：

1. 实现数据的持久化；
2. 使用 SQL 语句，查询效率高；
3. 管理数据方便。

8 什么是 SQL，什么是 MySQL

SQL 指的是结构化查询语言，是一种专门用来与数据库通信的语言，用于存取、查询、更新和管理关系数据库系统。

MySQL 是一种使用 SQL 语言的关系型数据库管理系统（DBMS）；

9 数据库的三大范式

第一范式：每个列都不可以再拆分；

第二范式：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分；

第三范式：在第二范式的基础上，非主键列只依赖于主键，不依赖于其它非主键；

10 什么是主键，什么是外键

主键：是一个列或多列的组合，它的值可以唯一地标识表中的每一行，通过它可强制表的实体完整性；

外键：在一个表中存在的另一个表的主键称此表的外键，外键可以防止非法数据插入，也可以防止破坏表之间的连接。

11 varchar 与 char 的区别

两者在 SQL 中都是用来保存字符串的。

• char 的长度是不可变的，varchar 的长度是可变的。当定义一个 char[10] 和 varchar[10] 时，如何存入的字符串是‘zhao’，则 char 的长度依然为 10，而 varchar 的长度就变为 4 了；
• char 的存取速度比 varchar 快很多，因为其长度固定，方便程序的存储和查找，也就是空间换时间；
• char 对英文字符占 1 个字节，汉字占 2 个字节，而 varchar 对英文和汉字都占 2 个字节；