PHP教程（13）mysql数据库备份+事物操作+触发器+用户管理

PHP教程（13）mysql数据库备份+事物操作+触发器+用户
管理
数据备份
数据备份将数据库里面的数据，复制到对应的文件，保存到对应的磁盘空间。

如果在以后需要使用数据的时候，可以通过备份文件将数据还原到对应的数据库。

数据备份分为三种方式：文件备份，表数据备份，SQL备份
文件备份
文件备份就是将数据库对应的文件夹或者文件进行备份。

InnoDB和myisam的备份方式不一样：存储引擎文件存储的方式不一样。

InnoDB备份：表结构文件和ibdata1，如果有一些额外的数据，iblogfile也需要备份
myisam备份：直接被分三个文件：结构，索引和数据
文件备份还原：直接将文件放到对应的数据库文件夹下。

数据表备份
数据表备份指的是只针对某一张具体的表内的数据进行备份。

需要通过mysql提供的命令，将数据表中的数据导出到对应的外部文件。

备份操作
备份语法
select */字段列表into outfile 文件路径from 表名;
注意：只备份纯数据，没有表结构
指定结构备份：行数据如何处理，字段如何处理
select */字段列表 into outfile 文件路径 [fields 操作] [lines 操作] from 表名;
fields操作
terminated by：字段以什么结束，字段后面跟什么内容，默认的是“\t”
enclosed by：字段以什么包裹，字段两边用什么字符来包裹，默认是’’，什么都没有
escaped by：字段的特殊字符如何处理，默认的是使用’\\’
lines 操作
starting by：每行一开始用什么字符显示，默认是’’
terminated by：每行用什么来结束（换行），默认的是’\r\n’所有的文件备份，只能将数据备份到不存在的文件里面，如果文件存在，那么备份失败。

还原操作
将文件中的数据按照指定的格式，还原到对应的数据表
还原语法
load data infile 文件路径 into table 表名[(字段列表)] [fields 操作] [lines 操作];
不管是字段还是行操作都是与备份时应该一一对应。

正确还原
备份的数据可以不需要清空数据表就进行数据还原：前提是备份的数据可以插入到表中，而不会因为数据重复出错。

SQL备份
SQL备份：系统对需要备份的表，系统自动的组织表的创建语句和数据的插入语句（SQL语句），然后将对应的SQL语句备份到外部文件。

即便在表不存在的情况下，系统也能根据备份的SQL语句自动创建表，并将备份的数据插入到表中。

备份操作
SQL备份必须通过客户端（mysqldump.exe）来操作，而不是通过SQL命令实现。

既然是通过客户端操作服务器：必须要连接认证（-hPup）
语法
mysqldump/mysqldump.exe –hPup 数据库数据表1 数据表2 … > 文件路径
SQL备份的最大颗粒度是到数据库，不能对整个数据库系统进行
备份。

还原操作
还原就是将对应的SQL语句重新执行。

SQL备份还原有两种方式：mysql.exe进行外部还原和进入到SQL环境下使用命令还原。

mysql.exe还原
语法
mysql/mysql.exe –hPhp 数据库名字 < 文件路径
SQL命令还原
要使用命令还原，必须先进入数据库环境。

语法
source 文件路径;
三种备份中，SQL备份用的最多。

如果数据量很大，那么备份的效率会很低，所以备份时间显得非常重要。

SQL备份适用于全表备份。

如果是想备份最新的数据SQL备份做不到。

增量备份
增量备份就是指对新的东西进行备份，将上次备份点之后的所有数据操作都备份，备份的SQL的日志。

事务安全
什么是事务安全？
事务：事务指的是一连串的数据写操作，操作之间是互相影响的事务安全：保证一连串的操作都是成功的
银行账户：小王和小明
小王欠小明100，还钱
小王账号里，扣去100
小明账户里，增加100
事务操作流程
从某个指定点开始，将所有的操作都捆绑到一起，最后通过结果
判断，要么都成功，要么都失败。

事务流程
1. 开启事务（事务起始点）
begin/start transaction;
从这个点开始，后面所有的操作，都是一起的，不是分开的，要么都成功，要么都失败
2. 事务操作，所有捆绑的事件
3. 事务结束
a) 成功：commit；提交
b) 失败：rollback；回滚（撤销）
事务原理
事务操作示例
1. 开启事务
2. 事务操作
操作1
操作2
3. 结束事务，先判断事务，选择如何结束
都成功：commit
不成功：rollback
事务操作特点
如果事务操作过程中断（意外），系统会自动判定事务处理失败
事务回滚点
事务在操作的过程中，可能有很多个节点，后续的节点必须依赖前面节点的成功而操作，但是有可能操作失败，需要先回到上一个成功的节点重新开始下一次操作，就需要在某个节点的时候设置回滚点，允许后面回到该点上。

语法
savepoint 回滚点名字;
回到回滚点
rollback to 回滚点名字
系统默认的就是自动事务提交，本来每一个操作都需要用户进行确认操作。

查看自动提交
show variables like 'autocommit%’;
关闭自动提交：
set autocommit = Off;
效果
注意：一般情况下事务都是自动提交，除非特殊情况下（转账）才会使用事务，使用事务的时候手动使用。

事务特点
事务有4个特点：原子性，一致性，隔离性和永久性
事务特点ACID
原子性：Atomicity，事务是整体，从开始到结束，之间所有的操作都是一个整体，要么全部成功，要么全部失败。

一致性：consistency，事务前后数据完整性一致，数据在操作过程中没有影响到数据的变化，直到事务被提交，才改变数据。

持久性：Durability事务一旦提交，数据改变永久
隔离性：isolation，多用户并发互不干扰，相互隔离
事务条件
存储引擎是InnoDB
变量
在mysql中，变量分为两种：系统变量和自定义变量
系统变量
系统已经定义好的变量。

show variables;
查看系统变量
show variables; -- 查看所有
show variables like 'pattern’; -- 查看部分
select @@变量名; -- 查看具体变量
语法1：修改变量（会话级别：当前连接修改有效）
set 变量名 = 值;
语法2：修改全局变量（变量本身是全局变量，针对所有用户所有连接都有效）
set GLOBAL 变量名 = 值;
set @@global.变量名 = 值;
语法3：修改全局变量
找到mysql的配置文件，在配置项里面修改。

（不建议）
自定义变量
定义
语法1：定义变量并赋值
set @变量名 = 值;
语法2：直接从记录中将数据存放到变量
select 字段列表from 表名where条件into 变量列表（@变量名）;
select @变量 := 字段,… from 表名 where条件;
在SQL中，=有其特殊的函数：比较运算符，也可以用于变量赋值，SQL为了区分赋值符号和比较符号，特意新增了一个赋值符号： :=
使用自定义变量
与系统变量一致
select @变量名;
修改变量
所有的变量，要进行修改必须使用关键字set
set @变量名 = 值;
删除变量
系统没有提供删除变量的方式，要删除变量，其实就是将变量置空
set @变量 = null;
用户自定义的变量全部都是会话级别的，当前连接有效，断开就无效了。

用户自定义的变量是属于会话级别，但是是全系统有效（跨库）变量作用域
变量作用的范围，在SQL中，变量的作用域分为两种：全局作用域和局部作用域，与js一样，全局变量可以在任何地方使用，而局部变量只能在函数内部使用。

凡是使用@符号定义的变量，都是全局变量（函数内部也可以定义全局变量）
局部变量语法：没有@符号，也不用set声明
declare 变量名数据类型default 默认值;
需求：
有两张表：订单表和商品表
每增加一张订单，对应的商品库存数量要减少。

触发器
触发器：一个代码的容器，将一堆要执行的代码捆绑到一起，在某一件事情发生的时候，自动的触发这段代码。

触发器语法
创建触发器的语法
临时语句结束符：delimiter
delimiter $$ -- 从当前开始，后面的所有代码，直到碰到$$才认为一条语句结束
create trigger 触发器名字触发时间事件类型 on 表名 for each row
begin
捆绑的代码
代码是以行为单位，每行都必须有语句结束符：”;”
end
$$
delimiter ; -- 将语句结束符改回来
触发时间：分为两种，数据操作前和操作后
before：数据真正写入到表之前
after：数据已经写入到表之后
事件类型：所有的触发器都是针对数据写操作（增删改）
insert：新增
update：修改
delete：删除
触发时间和事件类型共同组成了触发器的类型：触发器类型一共6种。

一张表最多可以有6个触发器。

同一种触发器一张表只能有一个。

查看触发器
查看所有的触发器
show triggers\G
触发器使用
触发器的使用不是用户手动调用，而是在操作数据的时候如果满足某一个特定的条件，就会自动触发。

删除触发器
语法
drop trigger 触发器名字;
触发器获取外部数据
在触发器的内部，内置了两个对象：old和new，两个对象代表了触发前和触发后的数据记录。

可以使用这两个对象来访问对应的数据。

语法：
old.字段名/new.字段名
只有更新语句可以使用old和new，但是删除的触发器只有old，新增的触发器只有new
效果
触发器应用
触发器主要用户数据的连表操作（对多条记录进行写的连带操作）触发器虽好，但是会导致数据库数据维护变得不可控，那么从
php的角度出发，程序很少使用触发器。

作业
1. 一张表最多有几个触发器？6个
2. 当一张表6个触发器都存在的时候，以下情况会触发哪些触发器
a) insert on duplicate key，主键重复
b) replace into，主键重复
索引
1. 什么是索引？
索引就是类似书的目录，提高检索数据的效率。

索引是系统按照某个具体的算法（哈希，散列，二叉树），将数据从全部数据里进行提取，维护成一个索引文件，然后系统在进行数据查询的时候，发现如果查询条件刚好满足索引条件，就可以从索引文件中快速的定位的数据所在位置。

mysql中有哪些索引？
主键索引（primary key效率最高的索引）
唯一索引（unique key）：不为空的情况下效率最高
普通索引（index）对数据没有要求，文件很大，效率比较低
全文索引（fulltext），对整个文章内部进行关键字索引（mysql5.5以后InnoDB支持全文索引）
英文的全文索引很简单：英文单词默认是用空格分离的
中文的全文索引很难：中文的词组成很麻烦，需要利用分词工具（sphinx）
用户管理
root是超级管理员用户，拥有最大权限，可以进行任何操作。

1. 创建用户
create user 用户信息 identified by 密码; -- 创建用户使用密码用户信息：用户名@主机名，主机名是允许访问的主机地址，*************.114.%，允许局域网内部用户访问
密码：系统里面默认使用sha1加密方式加密，但是该处使用明文密码，系统自动加密
所有的用户都在mysql数据库下的user表中存在
2. 授权：给用户指定数据库的操作权限
grant 权限列表 on 库名.表名 to 用户;
权限列表：select,update,delete….
用户：用户名@主机地址
效果
权限列表
一次性赋予全部权限
grant all privileges on 库.* to user;
3. 回收权限
revoke 权限列表 on 库.表 from user;
回收权限之后需要用户下次登录的时候才会生效。

4. 删除用户
drop user 用户;
如果有时候会因为权限的阻塞，缓存没有刷新，需要刷新权限缓存。

flush privileges;
root密码忘了？怎么办？
重装数据库
root密码找回
1. 关闭服务器
2. 无权限重启：重新启动服务使用以下命令
mysqld.exe –skip-grant-tables
3. 无用户直接进入数据库
mysql直接访问
4. 进入到mysql数据库内的user表中修改root用户的密码
修改密码的时候，必须使用password函数进行加密
update user set password = password('root’) where
user=’root’ and host ='localhost’;
5. 重启服务器
6. 使用新的密码进行登录
注意：在进行无权限登录的时候，所有人都可以无条件进入数据库，而且拥有全部权限。

所以在进行root密码找回的时候，必须特别谨慎。

（可以利用操作系统的安全性），应该在最没有用户访问的情况下，进行修改，将损失减小到最低。