MySQL数据库面试题六（2020最新版）

十五：触发器

1. 什么是触发器？触发器的使用场景有哪些？
   触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程。触发器是指一段代码，当触发某个事件时，自动执行这些代码。
   使用场景
   1）可以通过数据库中的相关表实现级联更改。
   2）实时监控某张表中的某个字段的更改而需要做出相应的处理。
   3）例如可以生成某些业务的编号。
   4）注意不要滥用，否则会造成数据库及应用程序的维护困难。
   5）大家需要牢记以上基础知识点，重点是理解数据类型CHAR和VARCHAR的差异，表存储引擎InnoDB和MyISAM的区别。
2. MySQL中都有哪些触发器？
   在MySQL数据库中有如下六种触发器：
   -Before Insert
   After Insert
   Before Update
   After Update
   Before Delete
   After Delete

十六：常用SQL语句

3. SQL语句主要分为哪几类
   数据定义语言DDL（Data Ddefinition Language）CREATE，DROP，ALTER
   主要为以上操作 即对逻辑结构等有操作的，其中包括表结构，视图和索引。
   数据查询语言DQL（Data Query Language）SELECT
   这个较为好理解 即查询操作，以select关键字。各种简单查询，连接查询等 都属于DQL。
   数据操纵语言DML（Data Manipulation Language）INSERT，UPDATE，DELETE
   主要为以上操作 即对数据进行操作的，对应上面所说的查询操作 DQL与DML共同构建了多数初级程序员常用的增删改查操作。而查询是较为特殊的一种 被划分到DQL中。
   数据控制功能DCL（Data Control Language）GRANT，REVOKE，COMMIT，ROLLBACK
   主要为以上操作 即对数据库安全性完整性等有操作的，可以简单的理解为权限控制等。

4. 超键、候选键、主键、外键分别是什么？
   1）超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。
   2）候选键：是最小超键，即没有冗余元素的超键。
   3）主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
   4）外键：在一个表中存在的另一个表的主键称此表的外键。

5. SQL 约束有哪几种？
   1）NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
   2）UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
   3）PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
   4）FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
   5）CHECK: 用于控制字段的值范围。

6. 六种关联查询
   1）交叉连接（CROSS JOIN）
   2）内连接（INNER JOIN）
   3）外连接（LEFT JOIN/RIGHT JOIN）
   4）联合查询（UNION与UNION ALL）
   5）全连接（FULL JOIN）
   6）交叉连接（CROSS JOIN）

SELECT * FROM A,B(,C)或者SELECT * FROM A CROSS JOIN B (CROSS JOIN
C)#没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义，很少使用内连接（INNER JOIN）SELECT * FROM A,B
WHERE A.id=B.id或者SELECT * FROM A INNER JOIN B ON
A.id=B.id多表中同时符合某种条件的数据记录的集合，INNER JOIN可以缩写为JOIN 1

内连接分为三类
1）等值连接：ON A.id=B.id
2）不等值连接：ON A.id > B.id
3）自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

外连接（LEFT JOIN/RIGHT JOIN）
1）左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN
2）右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

联合查询（UNION与UNION ALL）

SELECT * FROM A UNION SELECT * FROM B UNION ...

1）就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并
2）如果使用UNION ALL，不会合并重复的记录行
3）效率 UNION 高于 UNION ALL

全连接（FULL JOIN）
1）MySQL不支持全连接
2）可以使用LEFT JOIN 和UNION和RIGHT JOIN联合使用

SELECT * FROM A LEFT JOIN B ON A.id=B.id UNIONSELECT * FROM A RIGHT JOIN B ON A.id=B.id

十七：什么是子查询
1.条件：一条SQL语句的查询结果做为另一条查询语句的条件或查询结果
2.嵌套：多条SQL语句嵌套使用，内部的SQL查询语句称为子查询。
3.子查询的三种情况
1）子查询是单行单列的情况：结果集是一个值，父查询使用：=、 <、 > 等运算符
– 查询工资最高的员工是谁？

select * from employee where salary=(select max(salary) from employee);

2）子查询是多行单列的情况：结果集类似于一个数组，父查询使用：in 运算符
– 查询工资最高的员工是谁？

select * from employee where salary=(select max(salary) from employee);

3）子查询是多行多列的情况：结果集类似于一张虚拟表，不能用于where条件，用于select子句中做为子表
– 1) 查询出2011年以后入职的员工信息
– 2) 查询所有的部门信息，与上面的虚拟表中的信息比对，找出所有部门ID相等的员工。

select * from dept d, (select * from employee where join_date > '2011-1-1') e where e.dept_id = d.id;

– 使用表连接：

select d.*, e.* from dept d inner join employee e on d.id = e.dept_id where e.join_date > '2011-1-1'

十八：mysql中 in 和 exists 区别
mysql中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。
如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

十九：varchar与char的区别
char的特点
1）char表示定长字符串，长度是固定的；
2）如果插入数据的长度小于char的固定长度时，则用空格填充；
3）因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
4）对于char来说，最多能存放的字符个数为255，和编码无关
varchar的特点
1）varchar表示可变长字符串，长度是可变的；
2）插入的数据是多长，就按照多长来存储；
3）varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
4）对于varchar来说，最多能存放的字符个数为65532
总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。

varchar(50)中50的涵义
最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为order by
col采用fixed_length计算col长度(memory引擎也一样)。在早期 MySQL 版本中， 50 代表字节数，现在代表字符数。
int(20)中20的涵义 是指显示字符的长度。20表示最大显示宽度为20，但仍占4字节存储，存储范围不变； 不影响内部存储，只是影响带
zerofill 定义的 int 时，前面补多少个 0，易于报表展示
mysql为什么这么设计
对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样；

二十：mysql中int(10)和char(10)以及varchar(10)的区别
1）int(10)的10表示显示的数据的长度，不是存储数据的大小；chart(10)和varchar(10)的10表示存储数据的大小，即表示存储多少个字符。
int(10) 10位的数据长度 9999999999，占32个字节，int型4位
2）char(10) 10位固定字符串，不足补空格 最多10个字符
　　varchar(10) 10位可变字符串，不足补空格 最多10个字符
3）char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间
4）varchar(10)表示存储10个变长的字符，存储多少个就是多少个，空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符

二十一：FLOAT和DOUBLE的区别是什么？
1）FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
2）DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

drop、delete与truncate的区别
三者都表示删除，但是三者有一些差别：

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

UNION与UNION ALL的区别？
1）如果使用UNION ALL，不会合并重复的记录行
2）效率 UNION 高于 UNION ALL

二十二：SQL优化

1. 如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因？
   对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划。 我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。 而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。
   
   执行计划包含的信息 id 有一组数字组成。表示一个查询中各个子查询的执行顺序;
   1）id相同执行顺序由上至下。
   2）id不同，id值越大优先级越高，越先被执行。
   3）id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。
   select_type 每个子查询的查询类型，一些常见的查询类型。
   
   table 查询的数据表，当从衍生表中查数据时会显示 x 表示对应的执行计划id partitions 表分区、表创建的时候可以指定通过那个列进行表分区。 举个例子：

create table tmp (
id int unsigned not null AUTO_INCREMENT,
name varchar(255),
PRIMARY KEY (id)
) engine = innodb
partition by key (id) partitions 5;

type(非常重要，可以看到有没有走索引) 访问类型
1）ALL 扫描全表数据
2）index 遍历索引
3）range 索引范围查找
4）index_subquery 在子查询中使用 ref
5）unique_subquery 在子查询中使用 eq_ref
6）ref_or_null 对Null进行索引的优化的 ref
7）fulltext 使用全文索引
8）ref 使用非唯一索引查找数据
9）eq_ref 在join查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。
possible_keys 可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。
key 显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。
**TIPS:**查询中若使用了覆盖索引(覆盖索引：索引的数据覆盖了需要查询的所有数据)，则该索引仅出现在key列表中
key_length 索引长度
ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows 返回估算的结果集数目，并不是一个准确的值。
extra 的信息非常丰富，常见的有：
1）Using index 使用覆盖索引
2）Using where 使用了用where子句来过滤结果集
3）Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
4）Using temporary 使用了临时表 sql优化的目标可以参考阿里开发手册
【推荐】SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts最好。
说明：
1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
2） ref 指的是使用普通的索引（normal index）。
3） range 对索引进行范围检索。
反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。