MySQL面试题

1. 描述主键、外键、超键是什么

   超键(super key):在关系中能唯一标识元组的属性集称为关系模式的超键
   候选键(candidate key): 不含有多余属性的超键称为候选键。也就是在候选键中，若再删除属性，就不是键了！
   主键(primary key):用户选作元组标识的一个候选键程序主键

   外键(foreign key):如果关系模式R中属性K是其它模式的主键，那么K在模式R中称为外键

2. 数据库设计的三大范式

   范式：Normal Format，符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度。
   1、第一范式，确保每列保持原子性；
   2、第二范式，确保表中的每列都和主键相关，消除依赖；
   3、第三范式，确保每列都和主键列直接相关，而不是间接相关，消除传递。

3. drop,delete与truncate的区别

   数据恢复方面：delete 可以恢复删除的数据，而 truncate 和 drop 不能恢复删除的数据。
   执行速度方面：drop > truncate > delete。
   删除数据方面：drop 是删除整张表，包含结构和内容，而 truncate 和 delete 只删除了表数据，不删除表结构。
   添加条件方面：delete 可以使用 where 表达式添加查询条件，而 truncate 和 drop 不能添加 where 查询条件。
   重置自增列方面：在 InnoDB 引擎中，truncate 可以重置自增列，而 delete 不能重置自增列。

   DDL --- Data Definition Language 定义
   DML --- Data Manipulation Language 操纵

   delete： 属于数据库DML操作语言，只删除数据不删除表的结构，会走事务，执行时会触发trigger
   truncate：属于数据库DDL定义语言，不走事务。本质上是新建了一个表结构，再把原先的表删除掉
   drop：属于数据库DDL定义语言

4. SQL UNION 和 UNION ALL 区别

   并集操作
   UNION去重且排序
   UNION ALL不去重不排序
   UNION和UNION ALL关键字都是将两个结果集合并为一个，并集操作,但UNION ALL 要比UNION快很多

5. exists、in、any、all区别

   嵌套查询

   • exists是表示子查询是否返回结果，而不管返回的具体内容
   • in表示值是否存在子查询结果集中
   • any是表示子查询结果中任意一个
   • all表示子查询结果中的所有

6. sql语句的执行顺序

   1. from 子句组装来自不同数据源的数据
   2. where 子句基于指定的条件对记录行进行筛选
   3. group by 子句将数据划分为多个分组
   4. 使用聚集函数进行计算
   5. 使用 having 子句筛选分组
   6. 计算所有的表达式
   7. select 的字段筛选
   8. 使用 order by 对结果集进行排序。
   9. limit

7. count(*)和count(1)和count(id)区别

   count(*)、count(1) 和 count(id) 都表示返回满足条件的结果集的总行数；
   而count(字段)则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

   count(id)：InnoDB 引擎会遍历整张表，取值，不包括NULL
   count(1)：InnoDB 引擎遍历整张表，但不取值，包括NULL
   count(*)：并不会把全部字段取出来，而是直接按行累加。获取表的总行数，包括NULL

   执行效率：count(*) ≈ count(1) > count(id) = count(非空字段) > count(可空字段)
   若列名为主键，count(列名)会比count(1)快
   若列名不为主键，count(1)会比count(列名)快
   若表多个列并且没有主键，则 count（1） 的执行效率优于 count（*）
   若表有主键，则 count（主键）的执行效率是最优的
   若表只有一个字段，则count（*）最优。

8. SQL语句优化（不少于6条）

   1. 性能分析
   2. 用explain关键字分析你写的sql，尤其是走不走索引这块。
   3. 正确建立索引
   4. select语句指明字段名，禁用*来查询
   • 只查询一条数据的时候，使用limit
   • 避免在where子句中对字段进行null值判断，考虑用默认值代替null
   • 尽量避免在where子句中使用or来连接条件。or可能会导致索引失效，导致全表扫描
   • 尽量避免在where自居中对字段进行表达式操作，这会导致系统放弃使用索引而进行全表扫描
   • 尽量使用inner join，避免left join
   • 不建议使用%前缀模糊查询
   • insert插入多条数据时，采用手动提交事务
   • 创建表的时候使用同一编码
   • 多表关联查询时，小表在前，大表在后
   • 使用表的别名
   • 对于复杂的查询，可以使用中间临时表暂存数据
   • 尽量避免在字段开头模糊查询
   • 如果检索结果中不会有重复记录，推荐用union all代替unoin
   • 小表驱动大表
   • 高效的分页
   • 用连接查询代替子查询
   • 控制索引数量，一般不超过5个
   • 索引优化
   • 有大量重复数据的字段，不适合建立索引
   • 用varchar代替char

9. SQL常用函数有哪些？

   数学函数
   字符串函数
   日期和时间函数
   条件判断函数
   系统信息函数
   加密函数
   格式化函数等

10. 左连接 右连接 内连接的区别

    左连接 返回包括左表中的所有记录和右表中连接字段相等的记录；
    右连接 返回包括右表中的所有记录和左表中连接字段相等的记录；
    内连接 只返回两个表中连接字段相等的行；
    全外连接 返回左右表中所有的记录和左右表中连接字段相等的记录

11. Mysql三种常见引擎的区别

    MySQL常见的三种存储引擎为InnoDB、MyISAM和MEMORY。其区别体现在事务安全、存储限制、空间使用、 内存使用、插入数据的速度和对外键的支持。

    1. 事务安全：InnoDB支持事务安全，MyISAM和MEMORY两个不支持。
    2. 存储限制：InnoDB有64TB的存储限制，MyISAM和MEMORY要是具体情况而定。
    3. 空间使用：InnoDB对空间使用程度较高，MyISAM和MEMORY对空间使用程度较低。
    4. 内存使用：InnoDB和MEMORY对内存使用程度较高，MyISAM对内存使用程度较低。
    5. 插入数据的速度：InnoDB插入数据的速度较低，MyISAM和MEMORY插入数据的速度较高。
    6. 对外键的支持：InnoDB对外键支持情况较好，MyISAM和MEMORY两个不支持外键

sql注入

[SQL注入]（SQL Injection）是一种常见的Web安全漏洞，主要形成的原因是在数据交互中，前端的数据传入到后台处理时，没有做严格的判断，导致其传入的“数据”拼接到SQL语句中后，被当作SQL语句的一部分执行。 从而导致数据库受损（被脱库、被删除、甚至整个服务器权限陷）。

*即*：*注入产生的原因是后台服务器接收相关参数未经过滤直接带入数据库查询***

1. 对输入进行严格的转义和过滤
2. 使用参数化（Parameterized）
3. PDO预处理
4. 云端防护（如阿里云盾）
5. 通过WAF设备启用防SQL Inject注入策略（或类似防护系统）