MySQL高频面试题梳理

0.题目汇总

mysql的数据类型有哪些
数据库引擎有哪些
InnoDB与MyISAM差别
MySQL删除数据的方式都有哪些？delete/drop/truncate区别、谁的速度更快以及原因
视图的作用是什么？有哪些好处？
什么叫内连接、外连接、左连接、右连接?
并发事务带来哪些问题?
事务隔离级别有哪些?MySQL的默认隔离级别是?
创建索引的三种方式以及删除索引
mysql的权限表有哪些
将一张表的部分数据更新到另一张表，该如何操作呢？
说一说你对数据库事务的了解
数据库范式
数据库关系运算符:选择、投影、连接、除、笛卡尔积
数据库语句的执行顺序
1.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、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection

2.数据库引擎有哪些

InnoDB引擎

MySQL 5.5或更高版本的默认存储引擎
设计遵循ACID模型，支持事务，支持提交、回滚和紧急恢复功能来保护数据。
支持行级锁定，可以提升多用户并发时的读写性能
支持自动增长列AUTO_INCREMENT
支持外键，保持数据的一致性和完整性
拥有自己独立的缓冲池，常用的数据和索引都在缓存中
$\color{red}{与传统的ISAM与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务(Transaction)功能 }$
InnoDB 物理文件结构为：
- .frm 文件：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等；
- .ibd 文件或 .ibdata 文件：这两种文件都是存放 InnoDB 数据的文件，之所以有两种文件形式存放 InnoDB 的数据，是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。
  - 独享表空间存储方式使用.ibd文件，并且每个表一个.ibd文件；
  - 共享表空间存储方式使用.ibdata文件，所有表共同使用一个.ibdata文件（或多个，可自己配置）。
InnoDB存储引擎的优势在于提供了良好的事务管理、崩溃修复能力和并发控制。缺点是其读写效率稍差，占用的数据空间相对比较大。

ISAM引擎(Indexed Sequential Access Method，索引顺序存取方法)

索引顺序存取方法(ISAM, Indexed Sequential Access Method)最初是IBM公司发展起来的一个文件系统，可以连续地(按照他们进入的顺序)或者任意地(根据索引)记录任何访问。

ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

MYISAM引擎

MYISAM是MYSQL的ISAM扩展格式和缺省的数据库引擎(Mysql5.1前)。除了提供ISAM里所没有的索引和字段管理的大量功能，MYISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MYISAM还有一些有用的扩展，例如用来修复数据库文件的MYISAMCHK工具和用来恢复浪费空间的MYISAMPACK工具。

MYISAM强调了快速读取操作，这可能就是为什么MYSQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。

MYISAM物理文件结构：
- 每个MyISAM在磁盘上存储成三个文件，每一个文件的名字均以表的名字开始，扩展名指出文件类型。
- .frm 文件存储 表定义;
- .MYD (MYData)文件存储 表的数据;
- .MYI (MYIndex)文件存储 表的索引。

MEMORY存储引擎

MEMORY存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择该存储引擎。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。如果该数据库需要一个用于查询的临时表，可以选择该存储引擎。

HEAP引擎

HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。

ARCHIVE引擎

适合对于不经常访问又删除不了的数据做归档储存，插入效率很高，而且占用空间小，该存储引擎只支持插入和查询操作，不支持删除和修改。

3.InnoDB与MyISAM差别

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条纪律也会锁住整个表，不适合高并发操作	行锁，操作时只锁某一行，不对其它行有影响，适合高并发的操作
表空间	小	大
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
关注点	性能	事务
是否保存表的具体行数	是	否

InnoDB 支持事务，MyISAM 不支持事务。 这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
InnoDB 不保存表的具体行数，执行select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。MyISAM一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
InnoDB 是聚簇索引，MyISAM 是非聚簇索引。

4.MySQL删除数据的方式都有哪些？delete/drop/truncate区别、谁的速度更快以及原因

常用的三种删除方式：通过 delete、truncate、drop 关键字进行删除；这三种都可以用来删除数据，但场景不同

可以这么理解，一本书，delete是把目录撕了，truncate是把书的内容撕下来烧了，drop是把书烧了

执行速度

drop > truncate >> DELETE

区别详解

delete

1、DELETE属于数据库DML操作语言，只删除数据不删除表的结构，会走事务，执行时会触发trigger；

2、在 InnoDB 中，DELETE其实并不会真的把数据删除，mysql 实际上只是给删除的数据打了个标记为已删除，因此 delete 删除表中的数据时，表文件在磁盘上所占空间不会变小，存储空间不会被释放，只是把删除的数据行设置为不可见。虽然未释放磁盘空间，但是下次插入数据的时候，仍然可以重用这部分空间（重用 → 覆盖）。

3、DELETE执行时，会先将所删除数据缓存到rollback segement中，事务commit之后生效;

4、delete from table_name删除表的全部数据,对于MyISAM 会立刻释放磁盘空间，InnoDB 不会释放磁盘空间;

5、对于delete from table_name where xxx 带条件的删除, 不管是InnoDB还是MyISAM都不会释放磁盘空间;

6、delete操作以后使用optimize table table_name会立刻释放磁盘空间。不管是InnoDB还是MyISAM 。所以要想达到释放磁盘空间的目的，delete以后执行optimize table 操作。

7、delete 操作是一行一行执行删除的，并且同时将该行的的删除操作日志记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，生成的大量日志也会占用磁盘空间。

truncate

1、truncate：属于数据库DDL定义语言，不走事务，原数据不放到 rollback segment 中，操作不触发 trigger。

执行后立即生效，无法找回执行后立即生效，无法找回执行后立即生效，无法找回

2、truncate table table_name立刻释放磁盘空间，不管是 InnoDB和MyISAM。truncate table其实有点类似于drop table 然后creat,只不过这个create table 的过程做了优化，比如表结构文件之前已经有了等等。所以速度上应该是接近drop table的速度;

3、truncate能够快速清空一个表。并且重置auto_increment的值。

小心使用 truncate，尤其没有备份的时候，如果误删除线上的表，记得及时联系中国民航，订票电话：400-806-9553

Drop

1、drop：属于数据库DDL定义语言，同Truncate；

2、drop table table_name 立刻释放磁盘空间，不管是 InnoDB 和 MyISAM;drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index); 依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

小心使用 drop ，要删表跑路的兄弟，请在订票成功后在执行操作！订票电话：400-806-9553

5.视图的作用是什么？有哪些好处？

视图（子查询）：是从一个或多个表导出的虚拟的表，其内容由查询定义。具有普通表的结构，但是不实现数据存储。

对视图的修改：单表视图一般用于查询和修改，会改变基本表的数据，

多表视图一般用于查询，不会改变基本表的数据。

作用：

①简化了操作，把经常使用的数据定义为视图。

我们在使用查询时，在很多时候我们要使用聚合函数，同时还要显示其它字段的信息，可能还会需要关联到其它表，这时写的语句可能会很长，如果这个动作频繁发生的话，我们可以创建视图，这以后，我们只需要select * from view就可以啦，这样很方便。

②安全性，用户只能查询和修改能看到的数据。

因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以将基表中重要的字段信息，可以不通过视图给用户，视图是动态的数据的集合，数据是随着基表的更新而更新。同时，用户对视图不可以随意的更改和删除，可以保证数据的安全性。

③逻辑上的独立性，屏蔽了真实表的结构带来的影响。

视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上的。有了视图之后，程序可以建立在视图之上，从而程序与数据库表被视图分割开来。

缺点:

①性能差

数据库必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。

②修改限制

当用户试图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的视图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。

6.什么叫内连接、外连接、左连接、右连接?

7.并发事务带来哪些问题?

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确
的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务1读取某表中的数据A=20，事务2也读取A=20，事
务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

8.事务隔离级别有哪些?MySQL的默认隔离级别是?

read-uncommitted(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
read-committed(读取已提交)：允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
repeatable-read(可重复读)：对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
serializable(可串行化)：最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读幻读	幻读
READ UNCOMMITTED	Y	Y	Y
READ COMMITTED	N	Y	Y
REPEATABLE READ	N	N	Y
SERIALIZABLE	N	N	N

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过

SELECT @@tx_isolation;

命令来查看。

这里需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 repeatable-read（可重读）事务隔离级别下使用Next-Key Lock算法实现了行锁，并且不允许读取已提交的数据，所以解决了不可重复读的问题。另外，该算法(Next-Key Lock)包含了间隙锁，会锁定一个范围，因此也解决了幻读的问题。所以说InnoDB 存储引擎的默认支持的隔离级别是 repeatable-read（可重读）已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 serializable(可串行化) 隔离级别。

9.创建索引的三种方式以及删除索引

创建索引

在执行CREATE TABLE时创建索引

使用CREATE TABLE创建表时，除了可以定义列的数据类型外，还可以定义主键约束、外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。

-- 创建普通索引
create table book(
    book_id int,
    book_name varchar(50),
    authors varchar(50),
    info varchar(50),
    comment varchar(50),
    year_publication year,
    index idx_bname(book_name) -- 声明索引，字段为book_name
);
-- 创建全文索引，全文索引在MySQL5.5及之前 MyISAM支持，InnoDB不支持
create table book1(
    book_id int,
    book_name varchar(50),
    authors varchar(50),
    info varchar(50),
    comment varchar(50),
    year_publication year,
    fulltext index fk_idx_cmt(comment(20)) -- 声明索引，字段为comment,长度取20，避免索引长度过长
);

使用ALTER TABLE 命令去增加索引

-- 创建普通索引
alter table book2 add index idx_cmt(comment);
-- 创建唯一索引
alter table book2 add unique uk_idx_bname(book_name);
-- 创建联合索引
alter table book2 add index mul_bid_bname_info(book_id,book_name,info);

CREATE INDEX 命令创建（只能增加普通索引和UNIQUE索引）

create index idx_cmt on book3(comment);-- 创建普通索引
create unique index uk_idx_bname on book3(book_name);-- 创建唯一索引
create index mul_bid_bname_info on book3(book_id,book_name,info);-- 创建联合索引

删除索引

使用 ALTER TABLE 删除索引

--语法格式：
ALTER TABLE table_name DROP INDEX index_name;

--使用实例：
alter table book3 drop index idx_cmt;
alter table book3 drop index mul_bid_bname_info;
--注意：添加 auto_increment 自增 约束字段的唯一索引不能被删除。

使用 DROP INDEX 删除索引

--语法格式：
DROP INDEX index_name on table_name;

--使用实例：
drop index idx_cmt on book2;
drop index mul_bid_bname_info on book2;

10.mysql的权限表有哪些

user表，用来记录允许连接到服务器的账号信息，该表里启用的所有权限都是全局级的，适用于所有数据库；
db表，存储了用户对某个数据库的操作权限；
tables_priv表，用来对单个表进行权限设置；
columns_priv表，用来对单个数据列进行权限设置；
procs_priv表，用于对存储过程和存储函数进行权限设置。

11. 将一张表的部分数据更新到另一张表，该如何操作呢？

可以采用关联更新的方式，将一张表的部分数据，更新到另一张表内。参考如下代码：

update b set b.col=a.col from a,b where a.id=b.id;
update b set b.col=a.col from b inner join a on a.id=b.id;
update b set b.col=a.col from b left Join a on b.id = a.id;

12.说一说你对数据库事务的了解

事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。在事务中的操作，要么都执行修改，要么都不执行，这就是事务的目的，也是事务模型区别于文件系统的重要特征之一。

事务需遵循ACID四个特性：

Atomic（原子性）：
指整个数据库事务是不可分割的工作单位。只有使数据库中所有的操作执行成功，才算整个事务成功；事务中任何一个SQL语句执行失败，那么已经执行成功的SQL语句也必须撤销，数据库状态应该退回到执行事务前的状态。

Consistency（一致性）：
指数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性。例如对银行转帐事务，不管事务成功还是失败，应该保证事务结束后ACCOUNTS表中Tom和Jack的存款总额为2000元。

Isolation（隔离性）：
指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。

Durability（持久性）：
指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。

13.数据库范式

1NF
- 每个关系r的属性值为不可分的原子值
2NF
- 满足1NF，非主属性完全函数依赖于候选键(左部不可约)
3NF
- 满足2NF，消除非主属性对候选键的传递依赖
BCNF
- 满足3NF，消除每一属性对候选键的传递依赖

14.数据库关系运算符:选择、投影、连接、除、笛卡尔积

选择

定义：在关系中选择在指定属性上有确定值的关系的子集。表示为：

$$ \sigma_F{R} = \{t|t \in R \and F(t)="真"\} $$

例：

查询信息系（IS系）全体学生
$$ σ_{Sdept='IS'}(Student) $$
查询年龄小于20岁的学生
$$ σ_{Sage<20}(Student) $$

投影

投影是选取关系中列的子集，投影的结果不是原来的关系，是R中的几列属性。表示为：

$$ \pi_{A}(R)=\{t[A]|t \in R\} $$

例：

查询学生关系Student中都有哪些系，即查询关系Student上所在系属性上的投影
$$ π_{Sdept}(Student) $$

连接

从两个关系的笛卡尔积中选属性间满足一定条件的元组。

除

设关系R除以关系S的结果为关系T，则T包含所有在R但不在S中的属性及其值，且T的元组与S的元组的所有组合都在R中

例题：已知关系R和S如下，求$R ÷ S$的结果

关系R

A	B	C
2	5	8
4	3	6
3	9	2
1	3	6
4	3	4
1	9	2

关系S

B	C	D
3	6	4
9	2	5

第一步 ：因为R÷S所得到的属性值是包含于R，但是S不包含的属性，R和S共同属性为B和C的组合，所以R÷S得到的属性列有(A)，关系S在B、C上的投影为 { (3,6),(9,2)}

第二步 ：R在A的取值域为{1,2,3,4}

第三步 ：求象集

1对应的象集为 {(3,6),(9,2)}
2对应的象集为 {(5,8)}
3对应的象集为 {(9,2)}
4对应的象集为 {(3,6),(3,4)}

第四步：从第三步中可以发现，只有1 的值对应象集包含关系S的投影集，所以R÷S={1}

15.数据库语句的执行顺序

1、from

2、where

3、group by

4、having

5、select

6、order by

7、limit