MySQL面试题

雨中的记忆fzh

已于 2023-06-24 11:20:24 修改

阅读量198

点赞数

分类专栏：面试文章标签： mysql

于 2023-06-22 15:13:22 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_47957229/article/details/131232615

版权

面试专栏收录该内容

2 篇文章

订阅专栏

本文介绍了关系型数据库的基本概念，如MySQL数据类型、CHAR与VARCHAR的区别、主键和外键的作用，以及事务的ACID特性。此外，还讨论了视图的功能和事务管理，包括如何创建和控制事务，以及不同隔离级别对数据一致性的影响。最后，提到了数据库索引的重要性和不同类型的加密方式，如MD5和PASSWORD。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

什么是关系型数据库

关系型数据库是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，用户通过查询来检索数据库中的数据，而查询是一个用于限定数据库中某些区域的执行代码。

MySQL数据类型

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型：FLOAT、DOUBLE、DECIMAL
字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型：Date、DateTime、TimeStamp、Time、Year
其他数据类型：BINARY、VARBINARY、ENUM

CHAR和VARCHAR区别

CHAR可用于存储固定长度的字符串。它根据定义的长度来划分存储空间，当数据存储完成后，CHAR会使用空格对剩余的空间进行填充。
VARCHAR可用于存储可变长度的字符串。它根据字符串的实际长度来改变存储空间，仅使用必要的存储空间，比固定长度类型（CHAR）更节省空间。

什么是主键

主键是关系表中记录的唯一标识。选取主键时要求主键不带有业务含义，应使用BIGINT自增或者GUID类型，而且主键不允许NULL，可以使用多个列作为联合主键，但联合主键并不常用。

什么是外键

外键是指一个表中的一个或多个字段值必须在另一个表中的某个字段中存在，并且被参照的表中的字段通常是主键。

外键作用

外键可以用来保证数据的完整性和一致性。通过外键，我们可以将多个表关联起来，避免数据重复和冗余，同时外键也可以限制数据的删除和修改，以避免对关联表中的数据造成影响。

什么是视图

视图是一张虚拟表，其内容由查询内容来定义，并不真实存在，但视图也存在一系列带有名称的列和行数据。视图在数据库中只存放了定义，并没有存放数据，数据还在原来的表中存放。当使用视图查询数据时，数据库会从数据表中根据定义查询数据来填充视图，因此一旦数据表中的数据发生改变，视图中的数据也会相应发生改变，同理视图中的数据发生改变，数据表中的数据也会改变。

视图的作用

使操作简单化。将用户经常需要的数据定义为视图，用户便可直接得到数据，无需每次都使用SQL语句进行查询，使用户操作变得简单化。
增强数据安全性。通过视图，用户只能对视图数据进行查询和修改，而视图外的数据用户接触不到，极大地增强了数据的安全性。
提高逻辑独立性。对数据表中未被引用列进行添加和删除，都不会影响视图，提高了表之间的逻辑独立性。

创建视图的SQL语句

CREATE 
VIEW 视图名(列别名1,列别名2···)//列别名可省略
AS 
SELECT 数据列1,数据列2,数据列3··· 
FROM 表名
WHERE 查询条件;

什么是事务

事务是一个最小的不可再分的工作单元，通常一个事务对应一个完整的业务。事务只和DML语句有关，一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成。

事务的四大特性（ACID）

A：原子性（Atomicity）事务中的操作要么都不做，要么就全做。
C：一致性（Consistency）事务执行的结果必须是从数据库从一个一致性状态转换到另一个一致性状态。
I：隔离性（Isolation）一个事务的执行不能被其他事务所干扰
D：持久性（Durability）也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

事务的作用

保证了数据的一致性和完整性。
为数据库操作提供了一个从失败中恢复到正常状态的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此的操作互相干扰。

事务开启的标志

任何一个DML(insert、update、delete)语句的执行都代表事务的开启

事务结束的标志

提交：成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
回滚：失败的结束，将所有的DML语句操作历史记录全部清空

创建事务的SQL语句

事务可以自动开启，也可以手动开启。开启事务的SQL语句为：

//手动开启事务
start transaction;

在事务进行过程中，DML语句是不会改变数据底层数据，只是将操作结果记录下来，保存到内存中。只有在事务成功完成状态下，才会保存到硬盘中。

开启事务后，执行DML操作，只有执行完成且进行提交（commit)后，数据才彻底保存到硬盘中，操作结果不可回滚。具体提交事务的SQL语句如下：

//执行DML操作
mysql> insert into t_user(UserName,PassWord) value('李四','123456');
//查询DML操作后表中的数据
mysql> select * from t_user;
+----+----------+----------+
| ID | UserName | PassWord |
+----+----------+----------+
|  1 | 张三     | 123456   |
|  3 | 李四     | 123456   |
+----+----------+----------+
//提交
mysql> commit;
//回滚
mysql> rollback;
//查询表中的数据
mysql> select * from t_user;
+----+----------+----------+
| ID | UserName | PassWord |
+----+----------+----------+
|  1 | 张三     | 123456   |
|  3 | 李四     | 123456   |
+----+----------+----------+
//回滚失败，说明数据成功提交后，无法进行回滚撤销DML操作

当DML操作失败或者错误操作，需要撤销操作时，可以在提交成功操作前进行回滚，撤销DML操作。具体的回滚操作的SQL语句如下：

//执行DML操作
mysql> insert into t_user(UserName,PassWord) value('李四','123456');
//查询DML操作后表中的数据
mysql> select * from t_user;
+----+----------+----------+
| ID | UserName | PassWord |
+----+----------+----------+
|  1 | 张三     | 123456   |
|  2 | 李四     | 123456   |
+----+----------+----------+
//回滚
mysql> rollback;
//查询表中的数据
mysql> select * from t_user;
+----+----------+----------+
| ID | UserName | PassWord |
+----+----------+----------+
|  1 | 张三     | 123456   |
+----+----------+----------+
//回滚成功后，数据库撤销DML操作，数据表数据未发生改变

事务执行过程中出现的三种现象

在事务执行过程中，可能会出现三种现象：脏数据、不可重复读和幻读。

脏数据：事务A执行过程中，读取到事务B还未提交的数据，读取的数据称为“脏数据”
不可重复读：事务A执行过程中会对一条数据进行两次或多次读取，在读取过程中，事务B修改了该数据，导致事务A两次读取的数据不一致，这种现象称为“不可重复读”
幻读：事务A执行过程中会对同一个集合数据进行两次或多次读取，在读取过程中，事务B增加或删除了部分数据，导致事务A前后读取到的数据量不一致，这种现象称为“幻读”

隔离级别

为处理上述三种现象，数据库提供了四种隔离级别：

读未提交（read uncommited）
读已提交（read commited）
可重复读（repeatable read
串行读（serializable）

隔离级别可处理的现象

	读未提交	读已提交	可重复读	串行读
脏数据	未解决	解决	解决	解决
不可重复读	未解决	未解决	解决	解决
幻读	未解决	未解决	未解决	解决

设置事务级别的SQL语句

SET TRANSACTION ISOLATION LEVEL 事务隔离级别[如：READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE];
例如： SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

对数据库索引的理解

索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

数据库索引分类

主要有普通索引、唯一索引、主键索引、外键索引、全文索引、复合索引

数据库索引结构

MySQL的索引是根据B+树实现的，内存的空间有限，数据库存储的数据量可能非常大，无法全部载入内存，而B+树可以分段加载需要的节点数据，可以在内存资源有限的前提下，极大提高查询效率

索引的优缺点

优点：

创建唯一性索引，保证数据库表中每一行数据的唯一性
大大加快数据的检索速度，这也是创建索引的最主要的原因
加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能

缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

数据库底层为什么不用红黑树而用B+树

红黑树是一种近似平衡二叉树，在数据量非常大的时候，需要访问判断的节点数还是比较多，同时数据在磁盘上存储，访问效率低；而B+树的多叉的，可以有效减少磁盘IO次数，同时还可以保证范围查询时找到起点和终点后快速取出需要的数据。

常用加密方式

MD5(‘密码’)
MD5是单向加密，加密后的密码不可还原。

插入 -> insert into 数据表(列名) value(MD5('要加密的数据'));
更新 -> update 数据表 set 列名=MD5('要加密的数据') where 查询条件;

//查询t_user数据库中的数据
mysql> select * from t_user;
+----+----------+----------+
| ID | UserName | PassWord |
+----+----------+----------+
|  1 | 张三     | 123456   |
|  3 | 李四     | 123456   |
|  4 | 王五     | 123456   |
+----+----------+----------+

//将用户名是张三的用户密码按MD5进行加密存储
mysql> update t_user set Password=MD5('123456') where UserName="张三";

//查询t_user数据库中的数据
mysql> select * from t_user;
+----+----------+----------------------------------+
| ID | UserName | PassWord                         |
+----+----------+----------------------------------+
|  1 | 张三     | e10adc3949ba59abbe56e057f20f883e |
|  3 | 李四     | 123456                           |
|  4 | 王五     | 123456                           |
+----+----------+----------------------------------+

PASSWORD(‘密码’)
PASSWORD是单向加密，加密后的密码不可还原。MySQL8.0及以上删除了该加密方式，在MySQL5版本上可用。

插入 -> insert into 数据表(列名) value(PASSWORD('要加密的数据'));
更新 -> update 数据表 set 列名=PASSWORD('要加密的数据') where 查询条件;

//查询t_user数据库中的数据
mysql> select * from t_user;
+----+----------+----------+
| ID | Username | Password |
+----+----------+----------+
|  1 | 张三     | 123456   |
|  3 | 王五     | 123456   |
|  4 | 赵六     | 123456   |
+----+----------+----------+

//将用户名是张三的用户密码按PASSWORD进行加密存储
mysql> update t_user set Password=PASSWORD('123456') where Username="张三";
//查询t_user数据库中的数据
mysql> select * from t_user;
+----+----------+-------------------------------------------+
| ID | Username | Password                                  |
+----+----------+-------------------------------------------+
|  1 | 张三     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  3 | 王五     | 123456                                    |
|  4 | 赵六     | 123456                                    |
+----+----------+-------------------------------------------+

ENCODE(‘密码’,‘任意字符’)
ENCODE是双向加密，加密后用相同的任意字符可以实现解密，即加密后的密码可以还原。但MySQL8.0及以上删除了该加密方式，在MySQL5版本上可用。

插入 -> insert into 数据表(列名) value(ENCODE('要加密的数据','密钥【自己设置】'));
更新 -> update 数据表 set 列名=MD5('要解密的数据','密钥【所解密数据加密时用的密钥】') where 查询条件;

//查询t_user数据库中的数据
select * from t_user;
+----+----------+-------------------------------------------+
| ID | Username | Password                                  |
+----+----------+-------------------------------------------+
|  1 | 张三     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  3 | 王五     | 123456                                    |
|  4 | 赵六     | 123456                                    |
+----+----------+-------------------------------------------+

//将用户名是王五的用户密码按ENCODE以abc为密钥进行加密存储
mysql> update t_user set Password=ENCODE('123456','abc') where Username="王五";
//查询t_user数据库中的数据
mysql> select * from t_user;
+----+----------+-------------------------------------------+
| ID | Username | Password                                  |
+----+----------+-------------------------------------------+
|  1 | 张三     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  3 | 王五     |  閿&                                    |
|  4 | 赵六     | 123456                                    |
+----+----------+-------------------------------------------+

//将用户名是王五的用户密码按DECODE以abc为密钥进行解密显示
mysql> update t_user set Password=DECODE(ENCODE('123456','abc'),'abc') where Username="王五";

//查询t_user数据库中的数据
mysql> select * from t_user;
+----+----------+-------------------------------------------+
| ID | Username | Password                                  |
+----+----------+-------------------------------------------+
|  1 | 张三     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
|  3 | 王五     | 123456                                    |
|  4 | 赵六     | 123456                                    |
+----+----------+-------------------------------------------+