十二、触发器

触发器自动执行时，以响应特定事件的存储程序。（特定事件：对数据库表的增删改）

触发器可以被定义在INSERT、UPDATE、DELETE语句更改数据表之前或之后被自动执行。

12.1、创建触发器

语法：CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW [trigger_order] trigger_body

参数详解：

• trigger_time：中发器执行的时间：AFTER(之后) | BEFORE(之前)
• trigger_event：触发器触发的事件：DELETE | UPDATE | INSERT
• FOR EACH ROW：表示任何一条记录上的操作满足触发事件都会触发该触发器
• table_name：表元触发事件操作表的名字
• trigger_body：创建触发器的SQL语句

（1）创建Insert触发器，每向t_student中插入一条记录后，则向t_log表中插入该表的表名t_student和插入的时间。

	CREATE TABLE t_log  
	(logno int AUTO_INCREMENT primary key,    
	tname varchar(20),    
	logtime datetime);
	SELECT * FROM t_log;

	create trigger tr_insert after insert on t_student for each row 
	insert into t_log (tname,logtime) VALUES('t_student',now());

	insert into t_student(stu_id) values("0123456789");
	select * from t_student;
	select  * from t_log;

（2）创建一个t_score1表的插入触发器，当向t_score1表中插入一条数据时，在t_student1自动添加一条stu_id对应的记录，在t_cours1e中自动添加一条course_id对应的记录。

	create table t_score1 like t_score;
	insert into t_score1 select  * from t_score;

	//创建触发器
	create trigger t_insert3 after insert on t_score1 for each row 
	begin
	insert into t_student1(stu_id) VALUES("1123456789") ;
	insert into t_course1(course_id) VALUES("12345678") ;
	end

	//验证
	insert into t_score1(score_id,stu_id,course_id) values(1018,1631607101,16610001);
	select  * from  t_student1 where stu_id = "1123456789";
	select  * from  t_course1 where course_id = "12345678";

12.2、查看触发器

	show triggers;

12.3、删除触发器

	drop trigger trigger_name;

12.4、NEW和OLD的应用

MySQL中定义了NEW和OLD两个临时表，用来保存触发器修改之前和之后的表，方便引用。

12.4.1、流程图

12.4.2、案例

（1）修改表t_student1中一个学生的生日时，向另一个表t_backup（需新建）表中插入修改前的生日与修改后的生日。

	//创建触发器
	create trigger t_updata1 after update on t_student1 for each row
	begin
	insert into t_backup(old_birthday,new_birthday) values(old.stu_birthday,new.stu_birthday);
	end

	//测试
	update t_student1 set stu_birthday="1999-12-12" where stu_name="王伟";

（2）删除t_major1表中的一条记录时，将删除的这条记录插入到另一个表（需新建表）中。

	//创建触发器
	create trigger t_delete1 after delete on t_major1 for each row
	begin
	insert into t_major_back values(old.major_id,old.major_name);
	end
	//测试
	delete from t_major1 where major_name="计算机应用技术";

十三、 事务

语法：

	START TRANSACTION | BEGIN
	DML语句
	COMMIT 或 ROLLBACK

• START TRANSACTION、BEGIN：可以开始一项新的事务
• DML语句：insert、delete、update
• COMMIT、ROLLBACK：定义提交、回滚事务

13.1、介绍

事务是一个完整的业务逻辑，是一个工作单元。如：

假设转账，从A账户向B账户中转账10000。

- 将A账户的钱减去10000 (update语句)
- 将B账户的钱加上10000 (update语句)
这就是一个完整的业务逻辑。这两个updata语句要求必须同时成功或同时失败。

只有DML语句才会有事务这一说，其它语句和事务无关! ! !

insert、delete、update
只有以上的三个语句和事务有关系，其它都没有关系。

所以，事务就是批量的DML语句同时成功，或者同时失败

13.2、提交、回滚事务

• 提交事务：commit; 语句 ，代表事务结束。
• 回滚事务：rollback; 语句（回滚永远都是只能回滚到上一次的提交点!)

13.3、事务包括4个特性

A（原子性）：

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

C（一致性）：

所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。

I（隔离性）重点：

A事务和B事务之间具有一定的隔离。 教室A和教室B之间有一道墙，这道墙就是隔离性。

A事务在操作一张表的时候，另一个事务B也操作这张表会那样? ? ?

D（持久性）：

事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上!

13.4、案例

将学生学号为“1631607101”的C语言成绩加10分，如果发现加10分后的成绩大于100分，则执行事务的回滚操作，否则提交事务。

	mysql> select * from t_score1;
	+----------+------------+-----------+-------+
	| score_id | stu_id     | course_id | grade |
	+----------+------------+-----------+-------+
	| 1001     | 1631607101 | 16610001  |    92 |
	| 1002     | 1631607101 | 16610002  |    75 |
	| 1003     | 1631607101 | 16610003  |    88 |
	| 1004     | 1631607102 | 16610004  |   100 |
	| 1005     | 1631607102 | 16610005  |    99 |
	| 1006     | 1631611104 | 16610003  |    76 |
	| 1007     | 1631611104 | 16610004  |   107 |
	| 1008     | 1631611104 | 16610005  |    97 |
	| 1009     | 1631611104 | 16610006  |    90 |
	| 1010     | 1731613106 | 16610009  |    75 |
	| 1011     | 1731613107 | 16610009  |    97 |
	| 1012     | 1631601101 | 16610002  |    86 |
	| 1013     | 1631601102 | 16610003  |    85 |
	| 1014     | 1631601103 | 16610004  |    90 |
	| 1015     | 1631601104 | 16610005  |    80 |
	| 1016     | 1631601105 | 16610006  |    79 |
	| 1017     | 1631601105 | 16610007  |    98 |
	| 1018     | 1631607101 | 16610001  |  NULL |
	+----------+------------+-----------+-------+
	18 rows in set (0.00 sec)

	mysql> begin;
	mysql> update t_score1 set grade=grade+10 where stu_id="1631607101";
	mysql> select * from t_score1 where stu_id="1631607101" and grade < 100;
	+----------+------------+-----------+-------+
	| score_id | stu_id     | course_id | grade |
	+----------+------------+-----------+-------+
	| 1002     | 1631607101 | 16610002  |    85 |
	| 1003     | 1631607101 | 16610003  |    98 |
	+----------+------------+-----------+-------+


	--回滚后，数据就没有改变
	mysql> rollback;  --如果这里换成commit; 代表事务结束。

	mysql> select * from t_score1 where stu_id="1631607101" and grade < 100;
	+----------+------------+-----------+-------+
	| score_id | stu_id     | course_id | grade |
	+----------+------------+-----------+-------+
	| 1001     | 1631607101 | 16610001  |    92 |
	| 1002     | 1631607101 | 16610002  |    75 |
	| 1003     | 1631607101 | 16610003  |    88 |
	+----------+------------+-----------+-------+
	3 rows in set (0.00 sec)
	
		--执行commit;后，就无法回到commit之前，代表事务结束。
	mysql> commit;

十四、用户管理与权限管理

语法：CREATE USER user_name [IDENTIFIED BY [PASSWORD] “user_password”]

user_name：创建的账号名，完整的账号由用户名和主机组成，形式为'user_name'@'localhost'。

14.1、用户管理

14.1.1、创建用户

1、默认所以主机

	create user test_1 identified by '123456';

2、 指定用户的主机为localhost

	create user test1@localhost identified by '123456';

14.1.2、修改用户密码

14.1.3、删除用户

	drop user user_name;

14.2、权限管理

14.2.1、授予权限

GRANT priv_type[(column_list)] ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password'] [,user IDENTIFIED BY [PASSWORD] 'password'] ... [WITH with-option['with-option']...]

• priv_type：授权类型（ALL表示所有的权限）。
• column_list：指定列名，表示权限作用在那些列上，不指定作用于整个表。
• database.table：指定数据库和表。
• user：用户名，完整的账号由用户名和主机组成，形式为'user_name'@'localhost'。
• IDENTIFIED BY：指定为账户设置密码，已经存在的用户可不指定密码。
• password：表示用户新密码，已经存在的用户可以不用密码。
• WITH with-option[‘with-option’]：指定授权选项。

1、使用grant语句创建用户soft，密码为123456，并授予对所有数据库所有表的select,insert,update权限和转授权限（grant option）

	grant select,insert,update on *.* to soft@localhost identified by '123456' with grant option;

2、使用grant语句，并授予对所有数据库所有表的select,insert,update权限

	grant select,insert,update on *.* to soft;

14.2.1.1、查看权限

语法：GRANT priv_type[(column_list)] ON [object_type]{table_name | * | . | db_name.*} FROM user [,user]...

	select * from user;

普遍用户也可执行

	show grants for ‘user'@'host';

14.2.2、撤销权限

	revoke select,insert,update on *.* from soft@localhost;

十五、备份、恢复数据库

15.1、备份

语法：

	E:\db_backup>mysqldump
	Usage: mysqldump [OPTIONS] database [tables]
	OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
	OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
	For more options, use mysqldump --help

备份一个数据库

	MYSQLDUMP -u user_name -p db_name table_name1 table_name2... > backup_name.sql

• user_name：用户名
• db_name：选择备份的数据库
• table_name：选择备份数据库里面的表
• backup_name：生成的备份文件名

备份多个数据库

	MYSQLDUMP -u user_name -p --databases db_name1 db_name2... > backup_name.sql

备份所有数据库

	MYSQLDUMP -u user_name -p --all-databases > backup_name.sql

15.2、恢复

	MYSQL -u user_name -p [db_name] < backup_name.sql

db_name：用来指定数据库的名称，指定数据库时，还原该数据库下的表。不指定时，表示还原备份文件中的所有数据。

十六、日志

MySQL四种日志类型：错误日志、查询日志、慢查询日志、二进制日志 四种类型的日志对应的文件，保存在mysql数据目录data目录下，可以使用show variables命令查看 错误日志默认开启，其它三种日志默认打开。

16.1、错误日志

错误日志记录着 MySQL服务启停及运行时的报错信息, 如：运行的sql语句语法错误。 错误日志功能默认开启且无法被禁止。

查看“错误日志”是否开启

16.2、查询日志

查询日志记录着MySQL服务器的启停信息、客户端连接信息、增删查改数据记录的SQL语句。 查询日志默认关闭，由于查询日志会记录用户的所有操作，故如若开启查询日志，会占用较多磁盘空间，查询日志建议定义清理，以节省磁盘空间。

查看“查询日志”是否开启

16.3、慢查询日志 慢日志记录执行时间超过指定时间的各种操作。 通过分析慢查询日志能有效定位MySQL各指令执行的性能瓶颈。

查看“慢查询日志”是否开启

16.4、二进制日志

二级制日志以二进制的形式记录数据库除了查询以外的各种操作也叫变更日志。主要用于记录修改数据或有可能引起数据改变的MySQL语句，并且记录着语句发生时间、执行时长、操作的数据等。

查看“二进制日志”是否开启