MySQL（六）：数据库的备份（导入和导出）+索引+权限管理（修改密码，创建用户等）

一、事务

1.什么是事物？

将一组增删改查看成一个执行单元，要么全成功，要么有一个失败，数据库就会回滚，所有操作都不生效

2.事务的特性？

• ①原子性：将一组增删改看成一个整体，要不全成功，要不有一个失败，数据库就回滚，所有操作都不生效
• ②一致性：事务开始前的状态和事务执行后的状态保持一致
• ③隔离性：当多个用户并发访问数据库时，线程会为用户开启一个事务，事务与事务相互隔离，互不干扰
• ④持久性：一旦事务执行生效会永久运用到数据库，即使设备故障也不影响

3.如果不考虑隔离性会导致以下问题

• 脏读：当事务A访问了事务B未提交的数据，并使用了它
• 幻读：当事务A访问了数据表，事务B修改或删除了数据，导致事务A读取与实际不符合，类似出现幻觉
• 不可重复读：当事务A多次访问同一条数据，事务B修改了它，导致事务A两次读取的数据不一样

4.四个隔离级别

• ①Read uncommitted：读未提交，最低隔离级别，可能会出现所有情况
• ②Read committed：读提交，可以避免幻读
• ③Repeatable read：可重复读，可以避免幻读和不可重复读
• ④Serialzable：串行化，可以避免脏读、幻读和不可重复读。

5.事务的三个基本操作

• 开启事务：begin
• 提交事务：commit
• 回滚事务：rollback

6.事务的演示

开启两个sql窗口,在cmd1窗口1开启一个事务，插入一条数据，但是先不提交 在cmd窗口2查询user表，发现数据没有发生变化 当窗口1执行提交操作 在窗口②重新查询user表，发现新数据添加成功 在窗口①重新开启一个事务，执行删除id=100的数据 因为窗口1未提交，所以窗口②查询不到 在表1处执行回滚操作，数据恢复（注意是未提交之前）

二、视图

1.为什么使用视图？

• 当查询的复杂度很高时，指多字段，多关系查询，会导致sql语句混乱
• 视图是一张虚拟的表，有字段和数据，只存放查询语句，但是视图是基于源表的；

2.优点

• 简单：视图构建了一个虚拟的表，表里的数据是来源于复杂的查询语句，我们将复杂的查询语句存入视图，使用是直接调用视图
• 安全：数据具有对库和表的权限管理，但是没有对字段权限，可以通过视图来实现权限的功能
• 数据的独立性：视图基于源表，当源表的结构发生变化时，不会对视图的结构产生影响

3.缺点

• 使用视图时，一般存的都是复杂的查询，如果存的是简单的查询，在使用视图时，会作为复杂查询来去执行，会降低查询效率
• 会增加数据库的维护和管理成本，会对数据迁移造成很大影响

4.视图的增删查

创建视图

 CREATE VIEW 视图名 AS(查询语句);

查看所有视图

 SHOW TABLE STATUS WHERE COMMENT=’view’;

使用视图

SELECT * FROM 视图名;

④删除视图

DROP VIEW 视图名;

5.使用

例如我们在school数据库中的user表创建一个视图user_show来查看我们user的所有信息 添加视图

create view user_show as(select * from user);

查看所有的视图

show table status where comment='view'\G;

使用视图

select * from user_show;

删除视图

drop view user_show;

三、触发器

（1）定义：

触发器(trigger)也叫触发程序，触发器是一种特殊的存储过程，但是触发器不需要语句调用，也不需要手动启动，通过有增删改事件触发，当指定表发生指定动作，将激活触发器。

（2）添加触发器语句：

create trigger 触发器名字 触发时间 触发事件 on 表名 for each row begin 触发后的事件 end;

（3）查看所有触发器：

select * from information_schema.triggers/G;

（4）删除触发器：

drop trigger 触发器名字;

四、索引 1.介绍索引

一种高效获取数据的存储结构，类似于子字典的目录

1.为什么要使用索引

• 优点：提升数据的查询速度，减少io操作
• 缺点：创建索引时，数据库会额外的创建一张表，来维护索引，会占用磁盘资源，一般索引的空间比数据大

注意：添加索引可以增加查询的速度，但是不是索引越多越好

添加索引一般是给经常用作where条件的字段添加，我们一般遵循最左原则

Select * from user where username=’zhansgan’ and age=18;

2.索引的分类

索引分类特点普通索引 index可以为空 可以重复 当我们使用物理外键时就默认给该字段添加了普通索引唯一索引 unique可以为空 不能重复主键索引 primary key不能为空且唯一多列索引 index()将多个字段绑定到一起添加一个索引 2.索引的操作

①添加索引：

如果不指定索引的名字，那么索引名就以字段名为准

alter table 表名 add 索引类型 【索引名】(字段名);

②删除主键索引的格式：

删除主键索引要先将主建索引的自增属性删除 然后才能删除主键索引

Alter table 表名 drop primary key;

③查看索引：

 show index from 表名;

④删除索引：

drop index 索引的名字 on 表名;

五、关系型数据库三大范式

1.第一范式

字段要具有原子性，不可以继续拆分，一般根据实际需求来去决定

例如此例，每个字段在此需求不可拆分，因此满足第一范式

2.第二范式

建立在第一范式的基础上，每一列数据必须可以被唯一的区分，依赖于主键 每一张表至少要有一个主键

3.第三范式

建立在第二范式的基础之上，一般应用于设计多表关系中，要求一个数据表中不包含已在其他表中已包含的非主键字段，因为会出现冗余，表的信息如果能被推导出来就不应该单独设计一个字段来存储，可以使用外键进行关联，而不是将另一张表中的非主键属性直接写在当前表中

在此例子，如果我们把每个商品后都写分类，会造成数据冗余，因此我们单独建立一个类别表并且和第一个商品表进行关联

4.反三范式

允许部分字段冗余

注意： 三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

六、Mysql数据库的优化

1.为什么要优化？

一个应用吞吐量瓶颈往往出现在数据库的处理速度上，随着应用程序的使用和业务的拓展，数据库数据量逐渐增多，数据库处理压力逐渐增大，关系型数据库数据存放在磁盘上的，读写速度较慢（与内存中的数据相比）

2.优化方式：

架构优化：

• 使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能节约磁盘IO;
• 主从分离读写;采用主从复制把数据库的读操作和写入操作分离开来;
• 优化硬件;

库和表结构的优化：

• 垂直分库分表：把一些不经常读的数据或者结果复杂的表拆分成多张表，较少磁盘I/O操作;
• 水平分库分表：于数据量庞大的表，使用水平分库分表

语句优化：

• 设计表的时候严格根据数据库的设计范式来设计数据库;
• select 后尽量不使用*
• 尽量不使用嵌套查询，使用连接查询或者where查询
• sql关键词尽量使用大写
• 尽量使用逻辑外交不使用物理外键
• 给查询频繁的字段添加索引，并且遵循最左原则（尽量将首选关键字段放在最前边）

七、数据的导入和导出

1.数据表导出

备份表：

 mysqldump -u root -p 库名 表名 > 备份的表名.sql

2.数据库的导出

实际上是一次导入多张表，只不过表在一个sql文件

备份库：

mysqldump -u root -p 库名 >备份的库名.sql

3.数据的导入

导入库需要先创建输入库，因为实际是导入表，必须重新创入个新的数据库，然后再导入：

mysql -uroot -p 库名