大家好，我是讯享网，很高兴认识大家。



1.概述

• 什么是存储引擎，有什么用？

  存储引擎是MySQL中特有的一个术语，其它数据库中没有。（Oracle中有，但是不叫这个名字）存储引擎这个名字高端大气上档次。实际上存储引擎是一个表存储/组织数据的方式。
  不同的存储引擎，表存储数据的方式不同。

• 查看当前建表使用的存储引擎
  
  讯享网
• 怎么给表添加/指定“存储引擎”呢？

  可以在建表的时候给表指定存储引擎。

  讯享网

  在建表的时候可以在最后小括号的”)“的右边使用：
  ENGINE来指定存储引擎。
  CHARSET来指定这张表的字符编码方式。

• 结论：
  mysql默认的存储引擎是：InnoDB
  mysql默认的字符编码方式是：utf8
• 怎么查看mysql支持哪些存储引擎呢？
  命令： show engines G

讯享网

mysql支持九大存储引擎，当前5.7.24支持8个。版本不同支持情况不同

讯享网

可被转换为压缩、只读表来节省空间

提示一下：

MyISAM存储引擎特点：

讯享网

MyISAM不支持事务机制，安全性低。

这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
InnoDB支持事务，支持数据库崩溃后自动恢复机制。
InnoDB存储引擎最主要的特点是：非常安全。

它管理的表具有下列主要特征：
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）

– 提供一组用来记录事务性活动的日志文件
– 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
– 提供全 ACID 兼容
– 在 MySQL 服务器崩溃后提供自动恢复
– 多版本（MVCC）和行级锁定
– 支持外键及引用的完整性，包括级联删除和更新

InnoDB最大的特点就是支持事务：
以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，
不能很好的节省存储空间。

MEMORY 存储引擎管理的表具有下列特征：
– 在数据库目录内，每个表均以.frm 格式的文件表示。
– 表数据及索引被存储在内存中。（目的就是快，查询快！）
– 表级锁机制。
– 不能包含 TEXT 或 BLOB 字段。

MEMORY 存储引擎以前被称为HEAP 引擎。

MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

• 什么是事务？
• 什么是一个完整的业务逻辑？

  假设转账，从A账户向B账户中转账10000.
  将A账户的钱减去10000（update语句）
  将B账户的钱加上10000（update语句）
  这就是一个完整的业务逻辑。

• 只有DML语句才会有事务这一说，其它语句和事务无关！！！
  insert、delete、update
  只有以上的三个语句和事务有关系，其它都没有关系。

  因为 只有以上的三个语句是数据库表中数据进行增、删、改的。
  只要你的操作一旦涉及到数据的增、删、改，那么就一定要考虑安全问题。

• 假设所有的业务，只要一条DML语句就能完成，还有必要存在事务机制吗？

  真是因为做某件事的时候，需要多条DML语句共同联合起来才能完成，所以需要事务的存在。如果任何一件复杂的事儿都能一条DML语句搞定，那么事务则没有存在的价值了。

• 到底什么是事务呢？

  说到底，说到本质上，一个事务其实就是多条DML语句同时成功，或者同时失败
  事务：就是批量的DML语句同时成功或者同时失败！

• 事务是怎么做到多条DML语句同时成功或者同时失败？

  InnoDB存储引擎：提供一组用来记录事务性活动的日志文件

  事务开启了：
  insert
  insert
  insert
  delete
  update
  update
  update
  事务结束了！

• 提交事务？
  清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。
  提交事务标志着，事务的结束。并且是一种全部成功的结束。
• 回滚事务？
  将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
  回滚事务标志着，事务的结束。并且是一种全部失败的结束。

• 提交事务：commit; 语句
• 回滚事务：rollback; 语句（回滚永远都是只能回滚到上一次的提交点！）
• 事务对应的英语单词是：transaction
• 测试一下，在mysql当中默认的事务行为是怎样的？
  mysql默认情况下是支持自动提交事务的。（自动提交）
• 什么是自动提交？
  每执行一条DML语句，则提交一次！
  这种自动提交实际上是不符合我们的开发习惯，因为一个业务
  通常是需要多条DML语句共同执行才能完成的，为了保证数据
  的安全，必须要求同时成功之后再提交，所以不能执行一条
  就提交一条。
• 怎么将mysql的自动提交机制关闭掉呢？
  先执行这个命令：start transaction;

A：原子性（atom）

说明事务是最小的工作单元。不可再分。

C：一致性（consistency）

I：隔离性（Isolation）

A事务和B事务之间具有一定的隔离。
教室A和教室B之间有一道墙，这道墙就是隔离性。
A事务在操作一张表的时候，另一个事务B也操作这张表会那样？？？

D：持久性（duration）

事务和事务之间的隔离级别有哪些呢？4个级别

（1）读未提交：

read uncommitted（最低的隔离级别）《没有提交就读到了》
什么是读未提交？

这种隔离级别存在的问题就是：

讯享网

这种隔离级别一般都是理论上的，大多数的数据库隔离级别都是二档起步！

（2）读已提交：

read committed《提交之后才能读到》
什么是读已提交？

这种隔离级别解决了什么问题？

讯享网

这种隔离级别存在什么问题？

这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实。
oracle数据库默认的隔离级别是：read committed

（3）可重复读：

repeatable read《提交之后也读不到，永远读取的都是刚开启事务时的数据》
什么是可重复读取？

讯享网

可重复读解决了什么问题？

可重复读存在的问题是什么？

讯享网

mysql中默认的事务隔离级别就是这个！！！！！！！！！！！

（4）序列化/串行化：

serializable（最高的隔离级别）
这是最高隔离级别，效率最低。解决了所有的问题。
这种隔离级别表示事务排队，不能并发！
synchronized，线程同步（事务同步）
每一次读取到的数据都是最真实的，并且效率是最低的。

查看隔离级别：SELECT @@tx_isolation；

验证隔离级别的操作：

讯享网

讯享网

1.什么是索引

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。
索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

select * from t_user where name = 'jack';
以上的这条SQL语句会去name字段上扫描，为什么？
因为查询条件是：name='jack'
如果name字段上没有添加索引（目录），或者说没有给name字段创建索引，
MySQL会进行全扫描，会将name字段上的每一个值都比对一遍。效率比较低。

• MySQL在查询方面主要就是两种方式：
  第一种方式：全表扫描
  第二种方式：根据索引检索。

注意：
在实际中，汉语字典前面的目录是排序的，按照a b c d e f....排序，
为什么排序呢？因为只有排序了才会有区间查找这一说！（缩小扫描范围
其实就是扫描某个区间罢了！）

在mysql数据库当中索引也是需要排序的，并且这个所以的排序和TreeSet
数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！
在mysql当中索引是一个B-Tree数据结构。
遵循左小又大原则存放。采用中序遍历方式遍历取数据。

什么条件下，我们会考虑给字段添加索引呢？
条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

• 讯享网

  给emp表的ename字段添加索引，起名：emp_ename_index

• 删除索引：

  将emp表上的emp_ename_index索引对象删除。

• 添加索引

• 讯享网

• 在mysql当中，怎么查看一个SQL语句是否使用了索引进行检索？

扫描14条记录：说明没有使用索引。type=ALL

讯享网

扫描1条记录：说明使用了索引。type=ref

• 失效的第1种情况：

  讯享网

​ ename上即使添加了索引，也不会走索引，为什么？

• 失效的第2种情况：

  讯享网

  使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有
  索引，才会走索引，如果其中一边有一个字段没有索引，那么另一个
  字段上的索引也会实现。所以这就是为什么不建议使用or的原因。

• 失效的第3种情况：
  使用复合索引的时候，没有使用左侧的列查找，索引失效
  什么是复合索引？
  两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。

• 失效的第4种情况：

  在where当中索引列参加了运算，索引失效。

  讯享网

  讯享网

• 失效的第5种情况：

  

  在where当中索引列使用了函数

  单一索引：一个字段上添加索引。
  复合索引：两个字段或者更多的字段上添加索引。

  主键索引：主键上添加索引。
  唯一性索引：具有unique约束的字段上添加索引。
  .....

  注意：唯一性比较弱的字段上添加索引用处不大。

• 什么是视图？

  视图：站在不同的角度去看待同一份数据。

• 创建视图对象：

  讯享网

• 删除视图对象：

• 注意：只有DQL语句才能以view的形式创建。
  create view view_name as 这里的语句必须是DQL语句;

《方便，简化开发，利于维护》
我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致
原表**作！（视图的特点：通过对视图的操作，会影响到原表数据。）

• 面向视图查询

  讯享网

• 面向视图插入

• 查询原表数据

  讯享网

• 面向视图删除

• 查询原表数据

  讯享网

  Empty set (0.00 sec)

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。
每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？
可以把这条复杂的SQL语句以视图对象的形式新建。
在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。
并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要
修改视图对象所映射的SQL语句。

我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。
可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是
存储在硬盘上的，不会消失。

再提醒一下：
视图对应的语句只能是DQL语句。
但是视图对象创建完成之后，可以对视图进行增删改查等操作。

小插曲：
增删改查，又叫做：CRUD。
CRUD是在公司中程序员之间沟通的术语。一般我们很少说增删改查。
一般都说CRUD。

C:Create（增）
R:Retrive（查：检索）
U:Update（改）
D:Delete（删）

注意：在windows的dos命令窗口中：

可以导出指定的表吗？

讯享网

注意：需要先登录到mysql数据库服务器上。
然后创建数据库：create database bjpowernode;
使用数据库：use bjpowernode
然后初始化数据库：

• 什么是数据库设计范式？
  数据库表的设计依据。教你怎么进行数据库表的设计。
• 数据库设计范式共有？
  3个。
• 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
• 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，
  不要产生部分依赖。
• 第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，
  不要产生传递依赖。
• 声明：三范式是面试官经常问的，所以一定要熟记在心！
• 设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费。

讯享网

讯享网

数据库设计三范式是理论上的。

实践和理论有的时候有偏差。

最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。

因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）

有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，
并且对于开发人员来说，sql语句的编写难度也会降低。

面试的时候把这句话说上：他就不会认为你是初级程序员了！

存储过程：是保存一条或多条SQL的批处理脚本

存储过程的作用：

第一：存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。

第二：存储过程可接受参数、输出参数、返回单个或多个结果集及返回值。向程序返回错误原因。

第三：存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。

第四：存储过程主要是在服务器上运行，减少对客户机的压力。

第五：存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏数据逻辑。

第六：存储过程可以在单个存储过程中执行一系列SQL语句。

第七：存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

存储过程的优点：

1、存储过程的能力大大增强了SQL语言的功能和灵活性。

2、可保证数据的安全性和完整性。

3、通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。

4、通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。

5、在运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。

6、可以降低网络的通信量。

7、使体现企业规则的运算程序放入数据库服务器中，以便集中控制。

SQL 注入（SQL Injection）是发生在 Web 程序中数据库层的安全漏洞，是网站存在最多也是最简单的漏洞。主要原因是程序对用户输入数据的合法性没有判断和处理，导致攻击者可以在 Web 应用程序中事先定义好的 SQL 语句中添加额外的 SQL 语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步获取到数据信息。

简而言之，SQL 注入就是在用户输入的字符串中加入 SQL 语句，如果在设计不良的程序中忽略了检查，那么这些注入进去的 SQL 语句就会被数据库服务器误认为是正常的 SQL 语句而运行，攻击者就可以执行计划外的命令或访问未被授权的数据。

SQL 注入已经成为互联网世界 Web 应用程序的最大风险，我们有必要从开发、测试、上线等各个环节对其进行防范。下面介绍 SQL 注入的原理及避免 SQL 注入的一些方法。

SQL注入的原理

SQL 注入的原理主要有以下 4 点：

1）恶意拼接查询

我们知道，SQL 语句可以查询、插入、更新和删除数据，且使用分号来分隔不同的命令。例如：

SELECT * FROM users WHERE user_id = \(user_id</p> <p>其中，user_id 是传入的参数，如果传入的参数值为“1234; DELETE FROM users”，那么最终的查询语句会变为：</p> <p>SELECT * FROM users WHERE user_id = 1234; DELETE FROM users</p> <p>如果以上语句执行，则会删除 users 表中的所有数据。</p> <h5>2）利用注释执行非法命令。</h5> <p>SQL 语句中可以插入注释。例如：</p> <p>SELECT COUNT(*) AS 'num' FROM game_score WHERE game_id=24411 AND version=\)version

如果 version 包含了恶意的字符串，那么最终查询语句会变为：

SELECT COUNT(*) AS ‘num’ FROM game_score WHERE game_id=24411 AND version=‘-1’ OR 3 AND SLEEP(500)–

以上恶意查询只是想耗尽系统资源，SLEEP(500) 将导致 SQL 语句一直运行。如果其中添加了修改、删除数据的恶意指令，那么将会造成更大的破坏。

3）传入非法参数

SQL 语句中传入的字符串参数是用单引号引起来的，如果字符串本身包含单引号而没有被处理，那么可能会篡改原本 SQL 语句的作用。 例如：

SELECT * FROM user_name WHERE user_name = \(user_name</p> <p>如果 user_name 传入参数值为 G'chen，那么最终的查询语句会变为：</p> <p>SELECT * FROM user_name WHERE user_name ='G'chen'</p> <p>一般情况下，以上语句会执行出错，这样的语句风险比较小。虽然没有语法错误，但可能会恶意产生 SQL 语句，并且以一种你不期望的方式运行。</p> <h5>4）添加额外条件</h5> <p>在 SQL 语句中添加一些额外条件，以此来改变执行行为。条件一般为真值表达式。例如：</p> <p>UPDATE users SET userpass='\)userpass’ WHERE user_id=$user_id;

如果 user_id 被传入恶意的字符串“1234 OR TRUE”，那么最终的 SQL 语句会变为：

UPDATE users SET userpass= ‘’ WHERE user_id=1234 OR TRUE;

这将更改所有用户的密码。

避免SQL注入

对于 SQL 注入，我们可以采取适当的预防措施来保护数据安全。下面是避免 SQL 注入的一些方法。

1. 过滤输入内容，校验字符串

过滤输入内容就是在数据提交到数据库之前，就把用户输入中的不合法字符剔除掉。可以使用编程语言提供的处理函数或自己的处理函数来进行过滤，还可以使用正则表达式匹配安全的字符串。

如果值属于特定的类型或有具体的格式，那么在拼接 SQL 语句之前就要进行校验，验证其有效性。比如对于某个传入的值，如果可以确定是整型，则要判断它是否为整型，在浏览器端（客户端）和服务器端都需要进行验证。

2. 参数化查询

参数化查询目前被视作是预防 SQL 注入攻击最有效的方法。参数化查询是指在设计与数据库连接并访问数据时，在需要填入数值或数据的地方，使用参数（Parameter）来给值。

MySQL 的参数格式是以“?”字符加上参数名称而成，如下所示：

UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4

在使用参数化查询的情况下，数据库服务器不会将参数的内容视为 SQL 语句的一部分来进行处理，而是在数据库完成 SQL 语句的编译之后，才套用参数运行。因此就算参数中含有破坏性的指令，也不会被数据库所运行。

3. 安全测试、安全审计

除了开发规范，还需要合适的工具来确保代码的安全。我们应该在开发过程中应对代码进行审查，在测试环节使用工具进行扫描，上线后定期扫描安全漏洞。通过多个环节的检查，一般是可以避免 SQL 注入的。

有些人认为存储过程可以避免 SQL 注入，存储过程在传统行业里用得比较多，对于权限的控制是有一定用处的，但如果存储过程用到了动态查询，拼接 SQL，一样会存在安全隐患。

下面是在开发过程中可以避免 SQL 注入的一些方法。

1. 避免使用动态SQL

避免将用户的输入数据直接放入 SQL 语句中，最好使用准备好的语句和参数化查询，这样更安全。

​ 什么是动态SQL？
​ 动态SQL，即通过MyBatis 提供的各种标签对条件作出判断已实现动态拼接SQL语句。条件判断使用的表达式为OGNL 表达式。常用的动态标签有如下：
​ &lt; if &gt; &lt; where&gt; &lt; choose &gt; 以及 foreach 等等

2. 不要将敏感数据保留在纯文本中

加密存储在数据库中的私有/机密数据，这样可以提供了另一级保护，以防攻击者成功地排出敏感数据。

3. 限制数据库权限和特权

将数据库用户的功能设置为最低要求；这将限制攻击者在设法获取访问权限时可以执行的操作。

4. 避免直接向用户显示数据库错误

攻击者可以使用这些错误消息来获取有关数据库的信息。