MySQL面试题附答案-2019
随着技术的进步和开展,面试官们对面试者的要求越来越高,此刻只如果后端开发的职位,面试必定会问数据库的相关知识,而mysql作为当前最为流行的免费的关系型数据库治理技术，面试时问到与之相关的问题也就不足为奇了。那么此刻，我们就为大家手机了一些面试题及答案。一起来看看吧。
引荐教程：MySQL入门视频
1、 主键 超键 候选键 外键
主 键：
数据库表中对贮存数据对象予以独一和完全标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不克不及缺失，即不克不及为空值（Null）。
超 键：
在关系中能独一标识元组的属性集称为关系模式的超键。一个属性可认为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包括候选键和主键。
候选键：
是最小超键，即没有冗余元素的超键。
外 键：
在一个表中存在的另一个表的主键称此表的外键。
2、数据库事务的四个特性及含义
数据库事务transanction准确施行的四个根本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、耐久性(Durability)。
原子性:整个事务中的所有操纵，要末全部完成，要末全部不完成，不成能障碍在中心某个环节。事务在施行历程中发生错误，会被回滚（Rollback）到事务开端前的状态，就像这个事务从来没有施行过一样。
一致性:在事务开端此前和事务完毕今后，数据库的完全性束缚没有被毁坏。
隔离性:隔离状态施行事务，使它们仿佛是系统在给按时间内施行的独一操纵。假如有两个事务，运转在雷同的时间内，施行 雷同的功效，事务的隔离性将确保每一事务在系统中认为只要该事务在使用系统。这种属性有时称为串行化，为了防止事务操纵间的混淆，必需串行化或序列化请 求，使得在统一时间仅有一个恳求用于统一数据。
耐久性:在事务完成今后，该事务所对数据库所作的更换便耐久的留存在数据库之中，并不会被回滚。
3、视图的作用，视图可以更换么？
视图是虚拟的表，与包括数据的表不一样，视图只包括使用时动态检索数据的查询；不包括任何列或数据。使用视图可以简化复杂的sql操纵，潜藏详细的细节，庇护数据；视图创立后，可以使用与表雷同的方式利用它们。
视图不克不及被索引，也不克不及有关联的触发器或默许值，假如视图本身内有order by 则对视图再次order by将被覆盖。
创立视图：create view XXX as XXXXXXXXXXXXXX;
关于某些视图比方未使用联结子查询分组汇集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表停止更新；但是视图主要用于简化检索，庇护数据，并不消于更新，并且大部分视图都不成以更新。
4、drop,delete与truncate的不同
drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开端 delete删除表中数据，可以加where字句。
（1） DELETE语句施行删除的历程是每次从表中删除一行，并且同时将该行的删除操纵作为事务记载在日志中留存以便停止停止回滚操纵。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把独自的删除操纵记载记入日志留存，删除行是不克不及复原的。并且在删除的历程中不会激活与表有关的删除触发器。施行速度快。
（2） 表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会复原到初始大小，而DELETE操纵不会减少表或索引所占用的空间。drop语句将表所占用的空间全开释掉。
（3） 一样而言，drop > truncate > delete
（4） 利用范畴。TRUNCATE 只能对TABLE；DELETE可以是table和view
（5） TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（构造和数据）。
（6） truncate与不带where的delete ：只删除数据，而不删除表的构造（定义）drop语句将删除表的构造被依靠的束缚（constrain),触发器（trigger)索引（index);依靠于该表的储备历程/函数将被保存，但其状态会变为：invalid。
（7） delete语句为DML（data maintain Language),这个操纵会被放到 rollback segment中,事务提交后才生效。假如有响应的 tigger,施行的时候将被触发。
（8） truncate、drop是DLL（data define language),操纵马上生效，原数据不放到 rollback segment中，不克不及回滚
（9） 在没有备份状况下，慎重使用 drop 与 truncate。要删除部分数据行采纳delete且留意结合where来束缚影响范畴。回滚段要足够大。要删除表用drop;若想保存表而将表中数据删除，假如于事务无关，用truncate即可实现。假如和事务有关，或教师想触发trigger,还是用delete。
（10） Truncate table 表名 速度快,并且效力高,由于:
truncate table 在功效上与不带 WHERE 子句的 DELETE 语句雷同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记载一项。TRUNCATE TABLE 通过开释储备表数据所用的数据页来删除数据，并且只在事务日志中记载页的开释。
（11） TRUNCATE TABLE 删除表中的所有行，但表构造及其列、束缚、索引等保持不变。新行标识所用的计数值重置为该列的种子。假如想保存标识计数值，请改用 DELETE。假如要删除表定义及其数据，请使用 DROP TABLE 语句。
（12） 关于由 FOREIGN KEY 束缚援用的表，不克不及使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记载在日志中，所以它不克不及激活触发器。
5、索引的工作道理及其品种
数据库索引，是数据库治理系统中一个排序的数据构造，以协助快速查询、更新数据库表中数据。索引的实现平常使用B树及其变种B+树。
在数据之外，数据库系统还保护着知足特定查寻算法的数据构造，这些数据构造以某种方式援用（指向）数据，这样就可以在这些数据构造上实现高级查寻算法。这种数据构造，就是索引。
为表设定索引要付出代价的：一是增添了数据库的储备空间，二是在插入和修改数据时要花费较多的时间(由于索引也要随之变更)。
图展现了一种大概的索引方式。左边是数据表，一共有两列七笔记录，最左边的是数据记载的物理地址（留意逻辑上相邻的记载在磁盘上也并不是必然物理相邻的）。为了加快Col2的查寻，可以保护一个右侧所示的二叉查寻树，每个节点离别包括索引键值和一个指向对应数据记载物理地址的指针，这样就可以使用二叉查寻在O(log2n)的复杂度内猎取到响应数据。
创立索引可以大大提高系统的机能。
第一，通过创立独一性索引，可以包管数据库表中每一行数据的独一性。
第二，可以大大加快数据的检索速度，这也是创立索引的最主要的缘由。
第三，可以加快表和表之间的连接，特殊是在实现数据的参照 完全性方面特殊成心义。
第四，在使用分组和排序子句停止数据检索时，一样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的历程中，使用优化潜藏器，提高系统的机能。
或许会有人要问：增添索引有如此多的长处，为什么不合错误表中的每一个列创立一个索引呢？由于，增添索引也有很多不利的方面。
第一，创立索引和保护索引要消耗时间，这种时间随着数据量的增添而增添。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占必然的物理空间，假如要创立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据停止增添、删除和修改的时候，索引也要动态的保护，这样就落低了数据的保护速度。
索引是创立在数据库表中的某些列的上面。在创立索引的时候，应当思考在哪些列上可以创立索引，在哪些列上不克不及创立索引。一样来说，应当在这些列上创立索引：在经常需要搜索的列上，可以加快搜索的速度；在作为主键的列上，强迫该列的独一性和组织表中数据的摆列构造；在经常用在连接的列上，这些列主如果一些外键，可以加快连接的速度；在经常需要按照范畴停止搜索的列上创立索引，由于索引已经排序，其指定的范畴是持续的；在经常需要排序的列上创立索引，由于索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创立索引，加快前提的推断速度。
一样，关于有些列不该该创立索引。一样来说，不该该创立索引的的这些列具有以下特点：
第一，关于那些在查询中很少使用或者参照 的列不该该创立索引。这是由于，既然这些列很少使用到，因此有索引或者无索引，并不克不及提高查询速度。相反，由于增添了索引，反而落低了系统的保护速度和增大了空间需求。
第二，关于那些只要很少数据值的列也不该该增添索引。这是由于，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增添索引，并不克不及明显加快检索速度。
第三，关于那些定义为text, image和bit数据类型的列不该该增添索引。这是由于，这些列的数据量要末相当大，要末取值很少。
第四，当修改机能远弘远于检干脆能时，不该该创立索引。这是由于，修改机能和检干脆能是互相矛盾的。当增添索引时，会提高检干脆能，但是会落低修改机能。当减少索引时，会提高修改机能，落低检干脆能。因此，当修改机能远弘远于检干脆能时，不该该创立索引。
按照数据库的功效，可以在数据库设计器中创立三种索引：独一索引、主键索引和汇集索引。
独一索引
独一索引是不同意其中任何两行具有雷同索引值的索引。
当现有数据中存在反复的键值时，大多数数据库不同意将新创立的独一索引与表一起留存。数据库还大概防止增加将在表中创立反复键值的新数据。例如，假如在employee表中职员的姓(lname)上创立了独一索引，则任何两个员工都不克不及同姓。 主键索引 数据库表经常有一列或列组合，其值独一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义主键将主动创立主键索引，主键索引是独一索引的特定类型。该索引要求主键中的每个值都独一。当在查询中使用主键索引时，它还同意对数据的快速拜访。 汇集索引 在汇集索引中，表中行的物理次序与键值的逻辑（索引）次序雷同。一个表只能包括一个汇集索引。
假如某索引不是汇集索引，则表中行的物理次序与键值的逻辑次序不匹配。与非汇集索引比拟，汇集索引平常供给更快的数据拜访速度。
部分性道理与磁盘预读
由于储备介质的特性，磁盘本身存取就比主存慢许多，再加上机械运动消耗，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效力，要尽量减少磁盘I/O。为了到达这个目的，磁盘往往不是严厉按需读取，而是每次都会预读，即便只需要一个字节，磁盘也会从这个位置开端，次序向后读取必然长度的数据放入内存。这样做的理论根据是运算机科学中闻名的部分性道理：当一个数据被用到时，其邻近的数据也平常会立刻被使用。程序运转期间所需要的数据平常比力集中。
由于磁盘次序读取的效力很高（不需要寻道时间，只需很少的扭转时间），因此关于具有部分性的程序来说，预读可以提高I/O效力。
预读的长度一样为页（page）的整倍数。页是运算机治理储备器的逻辑块，硬件及操纵系统往往将主存和磁盘储备区分割为持续的大小相等的块，每个储备块称为一页（在很多操纵系统中，页得大小平常为4k），主存和磁盘以页为单位交流数据。当程序要读取的数据不在主存中时，会触发一个缺页非常，此时系统会向磁盘发出读盘信号，磁盘会寻到数据的起始位置并向后持续读取一页或几页载入内存中，然后非常返回，程序连续运转。
B-/+Tree索引的机能剖析
到这里终于可以剖析B-/+Tree索引的机能了。
上文说过一样使用磁盘I/O次数评论索引构造的好坏。先从B-Tree剖析，按照B-Tree的定义，可知检索一次最多需要拜访h个节点。数据库系统的设计者奇妙利用了磁盘预读道理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了到达这个目的，在实际实现B-Tree还需要使用如下技巧：
每次创建节点时，直接申请一个页的空间，这样就包管一个节点物理上也储备在一个页里，加之运算机储备分配都是按页对齐的，就实现了一个node只需一次I/O。
B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O(h)=O(logdN)。一样实际利用中，出度d是非常大的数字，平常超越100，因此h非常小（平常不超越3）。
而红黑树这种构造，h明显要深的多。由于逻辑上很近的节点（父子）物理上大概很远，没法利用部分性，所以红黑树的I/O渐进复杂度也为O(h)，效力明显比B-Tree差许多。
综上所述，用B-Tree作为索引构造效力是非常高的。
6、连接的品种
查询剖析器中施行：
--建表table1,table2：
create table table1(id int,name varchar(10)) create table table2(id int,score int) insert into table1 select 1,'lee' insert into table1 select 2,'zhang' insert into table1 select 4,'wang' insert into table2 select 1,90 insert into table2 select 2,100 insert into table2 select 3,70
如表
------------------------------------------------- table1 | table2 | ------------------------------------------------- id name |id score | 1 lee |1 90| 2 zhang| 2 100| 4 wang| 3 70| -------------------------------------------------
以下均在查询剖析器中施行
一、外连接
1.概念：包罗左向外联接、右向外联接或完全外部联接
2.左连接：left join 或 left outer join
(1)左向外联接的结果集包罗 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。假如左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选中列表列均为空值(null)。
(2)sql 语句
select * from table1 left join table2 on table1.id=table2.id
-------------结果------------- idnameidscore ------------------------------ 1lee190 2zhang2100 4wangNULLNULL ------------------------------
注释：包括table1的所有子句，按照指定前提返回table2响应的字段，不相符的以null显示
3.右连接：right join 或 right outer join
(1)右向外联接是左向外联接的反向联接。将返回右表的所有行。假如右表的某行在左表中没有匹配行，则将为左表返回空值。
(2)sql 语句
select * from table1 right join table2 on table1.id=table2.id
-------------结果------------- idnameidscore ------------------------------ 1lee190 2zhang2100 NULLNULL370 ------------------------------
注释：包括table2的所有子句，按照指定前提返回table1响应的字段，不相符的以null显示
4.完全外部联接:full join 或 full outer join
(1)完全外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选中列表列包括空值。假如表之间有匹配行，则整个结果集行包括基表的数据值。
(2)sql 语句
select * from table1 full join table2 on table1.id=table2.id
-------------结果------------- idnameidscore ------------------------------ 1lee190 2zhang2100 4wangNULLNULL NULLNULL370 ------------------------------
注释：返回摆布连接的和（见上左、右连接）
二、内连接
1.概念：内联接是用比力运算符比力要联接列的值的联接
2.内连接：join 或 inner join
3.sql 语句
select * from table1 join table2 on table1.id=table2.id
-------------结果------------- idnameidscore ------------------------------ 1lee190 2zhang2100 ------------------------------
注释：只返回相符前提的table1和table2的列
4.等价（与以下施行结果雷同）
A:select a.*,b.* from table1 a,table2 b where a.id=b.id
B:select * from table1 cross join table2 where table1.id=table2.id (注：cross join后加前提只能用where,不克不及用on)
三、穿插连接(完全)
1.概念：没有 WHERE 子句的穿插联接将发生联接所触及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。（table1和table2穿插连接发生3*3=9笔记录）
2.穿插连接：cross join (不带前提where...)
3.sql语句
select * from table1 cross join table2
-------------结果------------- idnameidscore ------------------------------ 1lee190 2zhang190 4wang190 1lee2100 2zhang2100 4wang2100 1lee370 2zhang370 4wang370 ------------------------------
注释：返回3*3=9笔记录，即笛卡尔积
4.等价（与以下施行结果雷同）
A:select * from table1,table2
7、数据库范式
1） 第一范式（1NF）
在任何一个关系数据库中，第一范式（1NF）是对关系模式的根本要求，不知足第一范式（1NF）的数据库就不是关系数据库。
所谓第一范式（1NF）是指数据库表的每一列都是不成分割的根本数据项，统一列中不克不及有多个值，即实体中的某个属性不克不及有多个值或者不克不及有反复的属性。假如显现反复的属性，就大概需要定义一个新的实体，新的实体由反复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包括一个实例的信息。简而言之，第一范式就是无反复的列。
2 ）第二范式（2NF）
第二范式（2NF）是在第一范式（1NF）的根基上创立起来的，即知足第二范式（2NF）必需先知足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必需可以被唯一地区分。为实现区分平常需要为表加上一个列，以储备各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。
第二范式（2NF）要求实体的属性完全依靠于主关键字。所谓完全依靠是指不克不及存在仅依靠主关键字一部分的属性，假如存在，那么这个属性和主关键字的这一部分应当别离出来构成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分平常需要为表加上一个列，以储备各个实例的唯一标识。简而言之，第二范式就是非主属性非部分依靠于主关键字。
3 ）第三范式（3NF）
知足第三范式（3NF）必需先知足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包括已在其它表中已包括的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不克不及再将部门名称、部门简介等与部门有关的信息再参加员工信息表中。假如不存在部门信息表，则按照第三范式（3NF）也应当构建它，不然就会有大量的数据冗余。简而言之，第三范式就是属性不依靠于其它非主属性。（我的懂得是消弭冗余）
8、数据库优化的思绪
这个我借鉴了慕课上关于数据库优化的课程。
一、SQL语句优化
1）应尽量幸免在 where 子句中使用!=或<>操纵符，不然将引擎舍弃使用索引而停止全表扫描。
2）应尽量幸免在 where 子句中对字段停止 null 值推断，不然将致使引擎舍弃使用索引而停止全表扫描，如：
select id from t where num is null
可以在num上设定默许值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3）许多时候用 exists 代替 in 是一个好的选中
4）用Where子句更换HAVING 子句 由于HAVING 只会在检索出所有记载之后才对结果集停止过滤
二、索引优化
看上文索引
三、数据库构造优化
1）范式优化： 比方消弭冗余（节约空间。。）
2）反范式优化：比方恰当加冗余等（减少join）
3）拆分表： 分区将数据在物理上分隔开，不一样分区的数据可以拟定留存在处于不一样磁盘上的数据文件里。这样，当对这个表停止查询时，只需要在表分区中停止扫描，而不必停止全表扫描，明显缩短了查询时间，别的处于不一样磁盘的分区也将对这个表的数据传输分离在不一样的磁盘I/O，一个精心设定的分区可以将数据传输对磁盘I/O竞争平均地分离开。对数据量大的不时表可采取此办法。可按月主动建表分区。
4）拆分其实又分垂直拆分和水平拆分： 案例： 简便购物系统暂设触及如下表： 1.产品表（数据量10w，不乱） 2.订单表（数据量200w，且有增长趋势） 3.会员表 （数据量100w，且有增长趋势） 以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数目级在百万静态数据可以到千万 垂直拆分：解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长显现的压力 方案： 把产品表和会员表放到一个server上 订单表独自放到一个server上 水平拆分： 解决问题：单表中数据量增长显现的压力 不解决问题：表与表之间的io争夺
方案： 会员表通过性别拆分为男会员表和女会员表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男会员表放一个server上 女会员表放一个server上(女的爱购物 哈哈)
四、效劳器硬件优化
这个么多花钱咯！
9、储备历程与触发器的不同
触发器与储备历程非常类似，触发器也是SQL语句集，两者独一的不同是触发器不克不及用EXECUTE语句调取，而是在会员施行Transact-SQL语句时主动触发（激活）施行。触发器是在一个修改了指定表中的数据时施行的储备历程。平常通过创立触发器来强迫实现不一样表中的逻辑相关数据的援用完全性和一致性。由于会员不克不及绕过触发器，所以可以用它来强迫实施复杂的业务规则，以确保数据的完全性。触发器不一样于储备历程，触发器主如果通过事件施行触发而被施行的，而储备历程可以通过储备历程名称名字而直接调取。当对某一表停止诸如UPDATE、INSERT、DELETE这些操纵时，SQLSERVER就会主动施行触发器所定义的SQL语句，从而确保对数据的处置必需相符这些SQL语句所定义的规则。
原文地址：https://www.cnblogs.com/frankielf0921/p/5930743.html
以上就是MySQL面试题附答案-2019的具体内容，更多请关注百分百源码网其它相关文章！
