数据库相关知识整理（MySQL）

语句类型

• DDL（数据定义语言)
  定义和管理数据对象，如数据库，数据表等
• DML（数据操作语言）
  用于操作数据库对象中所包含的数据
• DQL（数据查询语言）
  用于查询数据库数据
• DCL（数据控制语言）
  用来管理数据库的语言，包括管理权限及数据更改

---

临时表

临时表将在连接MySQL期间存在，当连接断开时，MySQL会自动删除表并释放空间，也可以手动删除

CREATE TEMPORARY TABLE 临时表名(
	 `字段名1`    列类型 [ 属性 ]  [ 索引 ] [注释] 
)

---

数据库事务隔离级别（脏读、幻读、不可重复读）

级别越低隔离性越差，系统的开销越低，可支持的并发越高，但是隔离越差

隔离级别

• 读未提交：脏读 不可重复读 幻读
• 读已提交：不可重复读 幻读
• 可重复读：幻读
• 可串行化：

1、脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

2、不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

3、幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

---

三大范式

• 第一范式
  是指数据库中表的每一列都是不可分割的基本数据，也就是说每列都是具有原子性的，不可再分

• 第二范式
  是在第一范式的基础上建立的，每个表只描述一件事情

• 第三范式
  是在第二范式的基础上建立的，表的非主键列必须和主键直接相关，不能间接相关，也就是说，非主键列之间不能相关依赖

---

存储过程

为之后的使用保存一条或多条MySQL语句的集合
存储过程的思想是在数据库SQL语言层面上的代码的封装和重用

优点：

• 把处理封装在容易使用的单元中，简化复杂的操作
• 防止错误保存数据的一致性
• 简化对变动的管理
• 提高性能

缺点：

• 编写SQL语句比较复杂
• 权限问题
• 在MySQL 5.0之前是不支持的

IN OUT INOUT 参数

• IN 输入参数，表示改参数的值，必须在调用存储过程时指定，在存储过程中改参数的值不能被返回
• OUT 输出参数，该值在存储过程中，内部被改变并可以返回
• INOUT 输入输出参数，调用是指定，并且可以被改变和返回

---

事务

事务必须具备以下四个属性，简称ACID 属性

• 原子性（Atomicity）
• 一致性（Consistency）
• 隔离性（Isolation）
• 持久性（Durability）

原子性（Atomicity）

原子性是指一个事务是不可分割的工作单元，其中的操作要么都做要么都不做，如果事务中一个SQL语句失败了，则全部回滚（数据库退回到事务执行前的状态）

一致性（Consistency）
是指事务必须使数据库从一个一致状态改变为另一个一致状态，也就是说一个事务的执行致之前和执行之后都必须处于一致状态

隔离性（Isolation）

与原子性和持久性侧重研究事务本身不用，隔离性是指不同的事务之间相互的影响。
事务的内部操作与其他事务是隔离的，不能相互干扰。

持久性（Durability）

持久性是指事务一旦提交，它对数据库的影响是永久性的，接下来的操作和故障不应该对齐有影响

SET AUTOCOMMIT  = 0;    # 关闭自动提交模式
START TRANSACTION; -- 开启事务
SELECT gradeName from grade WHERE gradeID =16;
UPDATE grade set gradeName = '研一' WHERE gradeID =16;
SELECT gradeName from grade WHERE gradeID =16;
ROLLBACK;  -- 回滚
-- COMMIT;  --  提交
SET AUTOCOMMIT  = 1;    # 开启自动提交模式

---

索引

作用

• 提高查询的速度
• 确保数据的唯一性
• 可以加速表之间的链接，实现表与表之间的参照完成性
• 使用分组和排序进行数据检索时可以显著的减少时间
• 索引的目的在于提高查询的效率

缺点

• 索引虽然可以提高查询效率但是会降低更新表的速度 比如 INSERT UPDATE DELETE
• 因为表的更新，MySQL 不仅要保存数据，还要保存索引文件
• 建立索引会占用磁盘空间的索引文件，一般情况都不太严重，但是一个表中创建了多种组合索引
• 索引文件就会膨胀的很快，如果某个数据列包含了许多重复的内容，它建立索引没有太大的实际效果
• 对于非常小的表大部分情况下 简单的扫描的全表效率更好

索引只是提高效率的一个因素，如果MySQL有大量的表，就需要花时间研究建立最有效的索引，或者优化查询的语句
MySQL中同一个表的索引总数限制为16个

分类
主键索引 --（最常见的索引类型）

1. 确保数据记录的唯一性
2. 确定特定的数据记录在数据库中的位置

唯一索引 --（避免同一个表中某数据列中值重复）

主键索引只能有一个 唯一索引可以有多个

常规索引 --（快速的定位到特定数据）

全文索引 --（快速的定位特定数据）

1. 只能用于MyISAM类型的表
2. 只能用于 CHAR VARCHAR TEXT数据类型 适合大型数据集

索引不是越多越好，不要对经常发生变动的数据加索引，小量数据不要加索引，索引一般加在查询多的列上

索引的底层数据结构

B-TREE B+TREE HASH等

B树特征

关键字结合分布在整棵树中 任何一个关键字出现且只出现在一个节点中 搜索可能在非叶子结点结束

B+树 是 B-树的变体，也是一种多路搜索数

所有的关键字都会出现在叶子结点的链表中，且链表中的关键字是有序的 不可能在非叶子结点命中
非叶子节点相当于叶子结点的索引，叶子结点相当于存储数据的层

HASH

是采用一定的哈希算法，把键换算成新的哈希值，检索时不需要类似于B+数 从根节点到叶子结点，逐级查找，
只需要一次哈希算法结合立刻定位到响应的位置，速度非常快 不支持范围查询 比如 WHERE price > 100

---

分页

总数据量: 查询语句
总页数 : 总数据量/每页显示条数== 0 ？ 总数据量/每页显示条数 : (总数据量/每页显示条数) +1
当前页 : LIMIT (当前页-1)*每页显示条数,每页显示条数

---

视图

是一种虚拟表，同真实表一样，由行和列构成，但是视图并不实际存在于数据库中
行和列的数据都来自于查询真实的表中，并且是使用视图实时动态的生成的

数据中只存在视图的定义，并没有存放视图中的数据，这些数据都存放在定义视图查询的真实表中。
使用视图时，数据库会从真实的表中取出相应的数据
所以视图中数据依赖于真实表中的数据，一旦真实表中的数据发生改变，显示在视图中的数据也会发生改变

1、视图不是数据库中的真实表，而是一张虚拟表，其结构和数据是建立在对真实表的查询基础上的

2、视图在数据库中的查询操作SQL 语句定义了视图的内容，列数据和行数据都是来自于查询所以引用的真实表

3、视图中没有实际的物理记录，不是以数据集的形式存储在数据库中的，它所对应的数据实际上是存储在视图所引用的真实表中

4、视图是数据的窗口、而表是内容，表是实际的数据存放单位，而视图只是以不同的方式展示数据，其数据来源还是真实表

5、视图是一种查看数据表的一种方法，可以查询数据表中“某些字段构成数据”只是一些SQL的集合，从安全角度来看，视图的安全性更高
使用视图用户不会直接接触到数据，不会知道真实的表结构

6、视图的建立和删除只影响视图本身，不影响真实表

视图的优点：

视图与表本质上来说是不相同的，但是视图经过定义以后结构和形式和表一样的，可以进行查询、修改、更新、删除等操作

1、可以定制数据，聚焦特定的数据
2、简化数据的操作
3、提高安全性
4、共享所需数据
5、更改数据的格式
6、重用SQL语句

视图是对于查询语句的封装，本身是不包含数据

---

TRUNCATE 命令

用于完全清空表数据，但表结构、索引、约束等不变，区别于DELETE命令

相同

• 都能删除数据、不删除表结构，但TRUNCATE 速度更快

不同

• 使用TRUNCATE TABLE重新设置 AUTO_INCREMENT 计数器
• 使用TRUNCATE TABLE不会对事务有影响