MySQL面试题

最新推荐文章于 2023-09-07 01:22:43 发布

疼痛文学小青年

最新推荐文章于 2023-09-07 01:22:43 发布

阅读量2.9k

点赞数 5

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

本文链接：https://blog.csdn.net/weixin_43840280/article/details/119973042

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一、数据库相关

1、类别

数据库总体分为关系型数据库和非关系型数据库：

关系型数据库有MySQL，非关系型数据库，包括键值存储数据库（Redis）、面向文档数据库（MongoDB）、图数据库（Neo4j）等。

2、表的类型

（1）共有5种类型的表格：

MyISAM
Heap
Merge
INNODB
ISAM

（2）简述MyISAM和InnoDB的区别：

是否⽀持⾏级锁 : MyISAM 只有表级锁(table-level locking)，⽽InnoDB ⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁。
是否⽀持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原⼦性,其执⾏速度⽐InnoDB类型更快，但是不提供事务⽀持。但是InnoDB 提供事务⽀持事务，外部键等⾼级数据库功能。具有事务(commit)、回滚(rollback)和崩溃修复能⼒(crash recovery capabilities) 的事务安全(transaction-safe (ACID compliant))型表。
是否⽀持外键： MyISAM不⽀持，⽽InnoDB⽀持。
是否⽀持MVCC ：仅 InnoDB ⽀持。应对⾼并发事务, MVCC⽐单纯的加锁更⾼效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下⼯作;MVCC可以使⽤乐观 (optimistic)锁和悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统⼀。

3、视图

（1）基本表

基本表是本身独立存在的表，在 SQL 中一个关系就对应一个表。

（2）视图

视图是从一个或几个基本表导出的表。视图本身不独立存储在数据库中，是一个虚表。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

（3）视图的优点

视图能够简化用户的操作
视图使用户能以多种角度看待同一数据
视图为数据库提供了一定程度的逻辑独立性
视图能够对机密数据提供安全保护

二、事务

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。

1、事务的特性

原⼦性（Atomicity）：事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么完全不起作⽤；
⼀致性（Consistency）：执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；
隔离性（Isolation）：并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；
持久性（Durability）：⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

2、并发事务带来的问题

在典型的应⽤程序中，多个事务并发运⾏，经常会操作相同的数据来完成各⾃的任务（多个⽤户对同⼀数据进⾏操作）。并发虽然是必须的，但可能会导致以下的问题。

脏读（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）就会发现多了⼀些原本不存在的记录，就好像发⽣了幻觉⼀样，所以称为幻读。

其中，不可重复读的重点是修改⽐如多次读取⼀条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除⽐如多次读取⼀条记录发现记录增多或减少了。

3、事务的隔离级别

READ-UNCOMMITTED(读取未提交)：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED(读取已提交)：允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。
REPEATABLE-READ(可重复读)：对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣。
SERIALIZABLE(可串⾏化)：最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰，也就是说，该级别可以防⽌脏读、不可重复读以及幻读。

MySQL InnoDB 存储引擎的默认⽀持的隔离级别是 REPEATABLE-READ（可重读）

4、事务回滚机制

事务回滚是指将该事务已经完成的对数据库的更新操作撤销。要同时修改数据库中两个不同表时，如果它们不是一个事务的话，当第一个表修改完，可能第二个表修改过程中出现了异常而没能修改，此时就只有第二个表依旧是未修改之前的状态，而第一个表已经被修改完毕。而当你把它们设定为一个事务的时候，当第一个表修改完，第二表修改出现异常而没能修改，第一个表和第二个表都要回到未修改的状态，这就是所谓的事务回滚。

三、索引

1、含义及类型

（1）含义

数据库索引好比是一本书前面的目录，能加快数据库的查询速度。索引是对数据库表中一个或多个列的值进行排序的结构。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

（2）类型

从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式
从应用层次来分：普通索引，唯一索引，复合索引
根据中数据的物理顺序与键值的逻辑（索引）顺序关系：聚集索引，非聚集索引。

平时讲的索引类型一般是指在应用层次的划分。

普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行
非聚簇索引：不是聚簇索引，就是非聚簇索引

2、官方建议使用自增长主键作为索引的原因

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

3、主键、外键和索引的区别

主键：唯一标识一条记录，不能有重复的，不允许为空。它是用来保证数据完整性。但是主键只能有一个
外键：表的外键是另一表的主键, 外键可以有重复的, 可以是空值。它是用来和其他表建立联系用的。其中，一个表可以有多个外键
索引：该字段没有重复值，但可以有一个空值。它是提高查询排序的速度。其中，一个表可以有多个唯一索引

四、锁

数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。三种类型（级别）的锁定机制：表级锁定，行级锁定和页级锁定。

表级锁定（table-level）:是MySQL各存储引擎中最大颗粒度的锁定机制。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁定（row-level）：行级锁定最大的特点就是锁定对象的颗粒度很小。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
页级锁定（page-level）：是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

五、连接

主要分为四种：内连接、左连接（左外连接）、右连接（右外连接）、全连接（全外连接）

1、内连接

组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key

2、左连接

左(外)连接，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key

3、右连接

与左(外)连接相反，右(外)连接，左表只会显示符合搜索条件的记录，而右表的记录将会全部表示出来。左表记录不足的地方均为NULL。

SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key

4、全连接

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
union
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

5、其他连接

（1）自连接

select A1.id from A AS A1,fruits AS A2 where A1.id = A2.id

（2）只有一个表

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL

SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL

（3）两表独有

SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL
union
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL

六、优化

Mysql的优化主要就在于：索引的优化，sql语句的优化，表的优化，在高并发网络环境下，除了优化数据库外，还会涉及到分布式缓存，CDN，数据库读写分离等高并发优化技术。

1、SQL语句优化方法

Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾。HAVING最后
用EXISTS替代IN、用NOT EXISTS替代NOT IN
避免在索引列上使用计算
避免在索引列上使用IS NULL和IS NOT NULL
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
在千万级分页时使用limit
对于经常使用的查询，可以开启缓存

2、优化数据库的方法

选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，例如’省份’、’性别’最好适用ENUM
使用连接(JOIN)来代替子查询
适用联合(UNION)来代替手动创建的临时表
事务处理
锁定表、优化事务处理
适用外键，优化锁定表
建立索引
优化查询语句

3、索引优化

只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
尽量使用短索引，如果可以，应该制定一个前缀长度
对于经常在where子句使用的列，最好设置索引，这样会加快查找速度
对于有多个列where或者order by子句的，应该建立复合索引
对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
尽量不要在列上进行运算（函数操作和表达式操作）
尽量不要使用not in和<>操作

4、表优化

表的字段尽可能用NOT NULL
字段长度固定的表查询会更快
把数据库的大表按时间或一些标志分成小表
将表拆分。数据表拆分：主要就是垂直拆分和水平拆分。水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。

七、范式

1、常见的三大范式

在实际开发中最为常见的设计范式有三个：

第一范式(确保每列保持原子性)--1NF。1NF是对属性的原子性约束，要求属性具有原子性，不可再分解第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
第二范式(确保表中的每列都和主键相关)--2NF。2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性。
第三范式(确保每列都和主键列直接相关，而不是间接相关)--3NF。3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

2、范式化设计的优缺点

优点：可以尽量得减少数据冗余，使得更新快，体积小
缺点：对于查询需要多个表进行关联，减少写得效率增加读得效率，更难进行索引优化

八、函数

可以对 MySQL 常用函数进行简单的分类，大概包括数值型函数、字符串型函数、日期时间函数、聚合函数等。

1、聚合函数

2、流程控制函数

3、字符串函数

4、数值型函数

5、日期和时间函数

6、其他函数

（1）排名函数

RANK：并列跳跃排名，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，跳跃到总共的排名。
DENSE_RANK：并列连续排序，并列即相同的值，相同的值保留重复名次，遇到下一个不同值时，依然按照连续数字排名。
ROW_NUMBER：连续排名，即使相同的值，依旧按照连续数字进行排名。

#原表
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | curry |   100 |
|  2 | klay  |    99 |
|  3 | KD    |   100 |
|  4 | green |    90 |
|  5 | James |    99 |
|  6 | AD    |    96 |
+----+-------+-------+

#SQL语句
select id, name, rank() over(order by score desc) as r,
	DENSE_RANK() OVER(order by score desc) as dense_r,
	row_number() OVER(order by score desc) as row_r
from students;

#结果
+----+-------+---+---------+-------+
| id | name  | r | dense_r | row_r |
+----+-------+---+---------+-------+
|  1 | curry | 1 |       1 |     1 |
|  3 | KD    | 1 |       1 |     2 |
|  2 | klay  | 3 |       2 |     3 |
|  5 | James | 3 |       2 |     4 |
|  6 | AD    | 5 |       3 |     5 |
|  4 | green | 6 |       4 |     6 |
+----+-------+---+---------+-------+

九、增删改查

1、创建

（1）普通表

普通创建

create table 表名(
 列名 数据类型 [约束类型] [comment '备注'],
 ...,
 constraint 约束名 约束类型(列名)  
 )engine=innodb defalut charset=utf8;

从其他表创建

create table 表名1 as select 列1,列2 from 表名2

（3）创建索引

普通索引

alter table `table_name` add index index_name( `column` )

唯一索引

alter table `table_name` add unique( `column` )

主键索引

alter table `table_name` add primary key ( `column` )

2、插入

（1）按列名添加一行数据

insert into 表名[(列名1,列名2...)] values(列1数据,列2数据...);

（2）从其他表中复制数据

insert into 表名1 select 列名 from 表名2

3、删除

（1）按条件删除指定数据

delete from 表名 where 选择条件

（2）销毁整张表或约束

drop table 表名;
drop index 约束名;

4、修改

（1）修改表的数据

按条件修改数据

update 表名 set 列名=列值,列2名=列2值...where 选择条件

将子查询结果赋值给表中数据

update 表名 set 列名=(子查询)

（2）修改表的结构

添加列

alter table 表名 add 列名 数据类型;

添加约束

alter table 表名 add [constraint 约束名] 约束类型(列名);

修改表名

alter table 表名 rename 新表名

修改列的字段名

alter table 表名 change cloumn 列名 新列名 新列数据类型

修改列的数据类型

alter table 表名 alter column 列名 数据类型;

添加一列到表中

alter table 表名 add 列名 数据类型;

删除表中一列

alter table 表名 drop column 列名

查看表的结构

desc 表名

5、查询

select 列名1 [as] [列别名],列名2
 from 表1 [as] [表别名]
 [left] join 表2 on 连接条件
 [left] join 表3 on 连接条件
 where 检索条件(不可用统计函数)
 group by 分组列1,列2
 having 检索条件(可用统计函数min,max,sum,avg)
 order by 排序列 [desc降序]
 limit 起始行号,显示行数

（1）distinct

去除重复的元素。

使用时放于select后面，如：select distinct age,sex from teacher;
意思是若有数据中的age和sex完全相同，查询只显示一条，若改成*意思就是两条完全相同的数据只留一条

（2）order by

对所选字段进行排序，默认是增大的 ASC，倒序desc

（3）limit

对获得的数据进行限定。

select * from 表名 limit start count   #计算机里默认表里的第一条数据位置为0
select * from student limit (n-1)*m,m   #每页显示m条数据，问显示第n页的数据
select * from student limit 0,3；查询student表中的数据从0开始，显示3条数据

（4）like

‘%da’ 表示匹配da结尾的数据
‘da%’ 表示匹配da开始的数据
‘%da%’ 表示匹配任何包含da的数据
‘____’:四个下划线表示匹配四个字符的数据

注意：如果要搜索的数据中包含%或者_，那么需要用**反斜杠""**来标注

（5）between...and...

查询年龄18到20的学生：select * from student where age between 18 and 20，
必须小的值在前大的在后；
或age<18 and age>20，包含18，20

（6）空和null

空判断（null和‘’是不同的，后者意思是空字符，使用null时不能用“=”，要用“is”，null代表什么都没有，如果要查空字符需要用xx=''）
查询没有填写身份证的学生：select * from student where card is null;（非null这样写……card is not null）

（7）group by

group by 单个字段

FROM test Group BY name：该句执行后，我们想象生成了虚拟表3，如下所图所示，生成过程是这样的：group by name，那么找name那一列，具有相同name值的行，合并成一行，如对于name值为aa的，那么<1 aa 2>与<2 aa 3>两行合并成1行，所有的id值和number值写到一个单元格里面。

接下来聚合select name,sum(number) from test group by name，结果：

group by 多个字段

group by 多个字段该怎么理解呢：如group by name,number，我们可以把name和number 看成一个整体字段，以他们整体来进行分组

接下来聚合select name,sum(id) from test group by name,number结果：

疼痛文学小青年

关注

5
点赞
踩
15

收藏

觉得还不错? 一键收藏
6
评论
MySQL面试题

一、数据库相关1、类别数据库总体分为关系型数据库和非关系型数据库：关系型数据库有MySQL，非关系型数据库，包括键值存储数据库（Redis）、面向文档数据库（MongoDB）、图数据库（Neo4j）等。2、表的类型（1）共有5种类型的表格：MyISAM Heap Merge INNODB ISAM（2）简述MyISAM和InnoDB的区别：是否⽀持⾏级锁 : MyISAM 只有表级锁(table-level locking)，⽽...
复制链接

扫一扫