在之前的文章中已经总结了一部分，SQL语法知识点，本篇继续。

8、事务处理

不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

MySQL 默认是隐式提交，每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。

指令

START TRANSACTION - 指令用于标记事务的起始点。

SAVEPOINT - 指令用于创建保留点。

ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START TRANSACTION 语句处。

COMMIT - 提交事务。

-- 开始事务

START TRANSACTION;

-- 插入操作 A

INSERT INTO `user`

VALUES (1, 'root1', 'root1', 'xxxx@163.com');

-- 创建保留点 updateA

SAVEPOINT updateA;

-- 插入操作 B

INSERT INTO `user`

VALUES (2, 'root2', 'root2', 'xxxx@163.com');

-- 回滚到保留点 updateA

ROLLBACK TO updateA;

-- 提交事务，只有操作 A 生效

COMMIT;

9、权限控制

GRANT 和 REVOKE 可在几个层次上控制访问权限：

整个服务器，使用 GRANT ALL 和 REVOKE ALL

整个数据库，使用 ON database.*；

特定的表，使用 ON database.table；

特定的列；

特定的存储过程。

新创建的账户没有任何权限。

账户用 username@host 的形式定义，

username@% 使用的是默认主机名。

MySQL 的账户信息保存在 mysql 这个数据库中。USE mysql;

SELECT user FROM user;

1. 创建账户

CREATE USER myuser IDENTIFIED BY 'mypassword';

2. 修改账户名

UPDATE user SET user='newuser' WHERE user='myuser';

FLUSH PRIVILEGES;

3. 删除账户

DROP USER myuser;

4. 查看权限

SHOW GRANTS FOR myuser;

5. 授予权限

GRANT SELECT, INSERT ON *.* TO myuser;

6. 删除权限

REVOKE SELECT, INSERT ON *.* FROM myuser;

7. 更改密码

SET PASSWORD FOR myuser = 'mypass';

10、存储过程

存储过程可以看成是对一系列 SQL 操作的批处理；

使用存储过程的好处

代码封装，保证了一定的安全性；

代码复用；

由于是预先编译，因此具有很高的性能。

创建存储过程

命令行中创建存储过程需要自定义分隔符，因为 命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。

包含 in、out 和 inout 三种参数。

给变量赋值都需要用 select into 语句。

每次只能给一个变量赋值，不支持集合的操作。

1. 创建存储过程

DROP PROCEDURE IF EXISTS `proc_adder`;

DELIMITER ;;

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)

BEGIN

DECLARE c int;

if a is null then set a = 0;

end if;

if b is null then set b = 0;

end if;

set sum = a + b;

END

;;

DELIMITER ;

2. 使用存储过程

set @b=5;

call proc_adder(2,@b,@s);

select @s as sum;

11、游标

游标（cursor）是一个存储在 DBMS 服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。

在存储过程中使用游标可以对一个结果集进行移动遍历。

游标主要用于交互式应用，其中用户需要对数据集中的任意行进行浏览和修改。

使用游标的四个步骤：

声明游标，这个过程没有实际检索出数据；

打开游标；

取出数据；

关闭游标；

DELIMITER $

CREATE PROCEDURE getTotal()

BEGIN

DECLARE total INT;

-- 创建接收游标数据的变量

DECLARE sid INT;

DECLARE sname VARCHAR(10);

-- 创建总数变量

DECLARE sage INT;

-- 创建结束标志变量

DECLARE done INT DEFAULT false;

-- 创建游标

DECLARE cur CURSOR FOR SELECT id,name,age from cursor_table where age>30;

-- 指定游标循环结束时的返回值

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

SET total = 0;

OPEN cur;

FETCH cur INTO sid, sname, sage;

WHILE(NOT done)

DO

SET total = total + 1;

FETCH cur INTO sid, sname, sage;

END WHILE;

CLOSE cur;

SELECT total;

END $

DELIMITER ;

-- 调用存储过程

call getTotal();

12、触发器

触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。

可以使用触发器来进行审计跟踪，把修改记录到另外一张表中。

MySQL 不允许在触发器中使用 CALL 语句，也就是不能调用存储过程。

BEGIN 和 END

当触发器的触发条件满足时，将会执行 BEGIN 和 END 之间的触发器执行动作。

⏩ 注意：在 MySQL 中，分号 ; 是语句结束的标识符，遇到分号表示该段语句已经结束，MySQL 可以开始执行了。因此，解释器遇到触发器执行动作中的分号后就开始执行，然后会报错，因为没有找到和 BEGIN 匹配的 END。

这时就会用到 DELIMITER 命令（DELIMITER 是定界符，分隔符的意思）。它是一条命令，不需要语句结束标识，语法为：DELIMITER new_delemiter。new_delemiter 可以设为 1 个或多个长度的符号，默认的是分号 ;，我们可以把它修改为其他符号，如 $ - DELIMITER $ 。在这之后的语句，以分号结束，解释器不会有什么反应，只有遇到了 $，才认为是语句结束。注意，使用完之后，我们还应该记得把它给修改回来。

NEW 和 OLD

MySQL 中定义了 NEW 和 OLD 关键字，用来表示触发器的所在表中，触发了触发器的那一行数据。

在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；

在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；

使用方法：NEW.columnName （columnName 为相应数据表某一列名）

1. 创建触发器

⏩ 提示：为了理解触发器的要点，有必要先了解一下创建触发器的指令。

CREATE TRIGGER 指令用于创建触发器。

语法：

CREATE TRIGGER trigger_name

trigger_time

trigger_event

ON table_name

FOR EACH ROW

BEGIN

trigger_statements

END;

说明：

trigger_name：触发器名

trigger_time: 触发器的触发时机。取值为 BEFORE 或 AFTER。

trigger_event: 触发器的监听事件。取值为 INSERT、UPDATE 或 DELETE。

table_name: 触发器的监听目标。指定在哪张表上建立触发器。

FOR EACH ROW: 行级监视，Mysql 固定写法，其他 DBMS 不同。

trigger_statements: 触发器执行动作。是一条或多条 SQL 语句的列表，列表内的每条语句都必须用分号 ; 来结尾。

示例：

DELIMITER $

CREATE TRIGGER `trigger_insert_user`

AFTER INSERT ON `user`

FOR EACH ROW

BEGIN

INSERT INTO `user_history`(user_id, operate_type, operate_time)

VALUES (NEW.id, 'add a user', now());

END $

DELIMITER ;

2. 查看触发器

SHOW TRIGGERS;

3. 删除触发器

DROP TRIGGER IF EXISTS trigger_insert_user;

学习IT相关内容，找“职坐标在线”