一、MySql存储过程和函数
概念
事先经过编译并存储在数据库中的一段 SQL 语句的集合
好处
- 存储过程和函数可以
重复使用，减轻开发人员的工作量。类似于java中方法可以多次调用
-
减少网络流量，存储过程和函数位于服务器上，调用的时候只需要传递名称和参数即可
- 减少数据在数据库和应用服务器之间的传输，可以
提高数据处理的效率
- 将一些业务逻辑在数据库层面来实现，可以
减少代码层面的业务处理
存储过程和函数的区别
创建存储过程
创建存储过程语法
-- 修改分隔符为$
DELIMITER $
-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)
BEGIN
sql语句;
END$
-- 修改分隔符为分号
DELIMITER ;
创建存储过程
-- 修改分隔符为$
DELIMITER $
-- 创建存储过程，封装分组查询学生总成绩的sql语句
CREATE PROCEDURE stu_group()
BEGIN
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$
-- 修改分隔符为分号
DELIMITER ;
调用存储过程
-- 标准语法
CALL 存储过程名称(实际参数);
-- 调用stu_group存储过程
CALL stu_group();
查看存储过程
-- 查询数据库中所有的存储过程 标准语法
SELECT * FROM mysql.proc WHERE db='数据库名称';
删除存储过程
-- 标准语法
DROP PROCEDURE [IF EXISTS] 存储过程名称;
-- 删除stu_group存储过程
DROP PROCEDURE stu_group;
存储过程语法
定义变量
-- 标准语法
DECLARE 变量名 数据类型 [DEFAULT 默认值];
-- 注意： DECLARE定义的是局部变量，只能用在BEGIN END范围之内
DECLARE num INT DEFAULT 10; -- 定义变量
变量的赋值1
-- 标准语法
SET 变量名 = 变量值;
SET NAME = '存储过程'; -- 为变量赋值
变量的赋值2
-- 标准语法
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件];
SELECT SUM(score) INTO men FROM student WHERE gender='男'; -- 计算男同学总分数赋值给men
if语句的使用
-- 标准语法
IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;
演示：
IF total >= 380 THEN
SET description = '学习优秀';
ELSEIF total >= 320 AND total < 380 THEN
SET description = '学习不错';
ELSE
SET description = '学习一般';
END IF;
参数的传递
-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
执行的sql语句;
END$
/*
IN:代表输入参数，需要由调用者传递实际数据。默认的
OUT:代表输出参数，该参数可以作为返回值
INOUT:代表既可以作为输入参数，也可以作为输出参数
*/
DELIMITER ;
演示：
CREATE PROCEDURE pro_test5(IN total INT)
case语句的使用
-- 标准语法1
CASE 表达式
WHEN 值1 THEN 执行sql语句1;
[WHEN 值2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;
-- 标准语法2
CASE
WHEN 判断条件1 THEN 执行sql语句1;
[WHEN 判断条件2 THEN 执行sql语句2;]
...
[ELSE 执行sql语句n;]
END CASE;
-- 使用case判断
CASE
WHEN total >= 380 THEN
SET description = '学习优秀';
WHEN total >= 320 AND total < 380 THEN
SET description = '学习不错';
ELSE
SET description = '学习一般';
END CASE;
while循环
-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
循环体语句;
条件控制语句;
END WHILE;
-- while循环
WHILE num <= 100 DO
-- 偶数判断
IF num%2=0 THEN
SET result = result + num; -- 累加
END IF;
-- 让num+1
SET num = num + 1;
END WHILE;
repeat循环
-- 标准语法
初始化语句;
REPEAT
循环体语句;
条件控制语句;
UNTIL 条件判断语句
END REPEAT;
-- 注意：repeat循环是条件满足则停止。while循环是条件满足则执行
-- repeat循环
REPEAT
-- 累加
SET result = result + num;
-- 让num+1
SET num = num + 1;
-- 停止循环
UNTIL num>10
END REPEAT;
loop循环
-- 标准语法
初始化语句;
[循环名称:] LOOP
条件判断语句
[LEAVE 循环名称;]
循环体语句;
条件控制语句;
END LOOP 循环名称;
-- 注意：loop可以实现简单的循环，但是退出循环需要使用其他的语句来定义。我们可以使用leave语句完成！
-- 如果不加退出循环的语句，那么就变成了死循环。
-- loop循环
l:LOOP
-- 条件成立，停止循环
IF num > 10 THEN
LEAVE l;
END IF;
-- 累加
SET result = result + num;
-- 让num+1
SET num = num + 1;
END LOOP l;
游标
-
游标的概念
- 游标可以遍历返回的多行结果，每次拿到一整行数据
- 在存储过程和函数中可以使用游标对结果集进行循环的处理
- 简单来说游标就类似于集合的迭代器遍历
- MySQL中的游标只能用在存储过程和函数中
二、MySql触发器
触发器的概念
可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句
可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作
| 触发器类型 |
OLD的含义 |
NEW的含义 |
| INSERT 型触发器 |
无 (因为插入前状态无数据) |
NEW 表示将要或者已经新增的数据 |
| UPDATE 型触发器 |
OLD 表示修改之前的数据 |
NEW 表示将要或已经修改后的数据 |
| DELETE 型触发器 |
OLD 表示将要或者已经删除的数据 |
无 (因为删除后状态无数据) |
创建触发器
标准语法
DELIMITER $
CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
[FOR EACH ROW] -- 行级触发器
BEGIN
触发器要执行的功能;
END$
DELIMITER ;
-- 创建INSERT触发器
DELIMITER $
CREATE TRIGGER account_insert
AFTER INSERT
ON account
FOR EACH ROW
BEGIN
INSERT INTO account_log VALUES (NULL,'INSERT',NOW(),new.id,CONCAT('插入后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$
DELIMITER ;
-- 向account表添加记录
INSERT INTO account VALUES (NULL,'王五',3000);
-- 查询account表
SELECT * FROM account;
-- 查询日志表
SELECT * FROM account_log;
-- 创建UPDATE触发器
DELIMITER $
CREATE TRIGGER account_update
AFTER UPDATE
ON account
FOR EACH ROW
BEGIN
INSERT INTO account_log VALUES (NULL,'UPDATE',NOW(),new.id,CONCAT('修改前{id=',old.id,',name=',old.name,',money=',old.money,'}','修改后{id=',new.id,',name=',new.name,',money=',new.money,'}'));
END$
DELIMITER ;
-- 修改account表
UPDATE account SET money=3500 WHERE id=3;
-- 查询account表
SELECT * FROM account;
-- 查询日志表
SELECT * FROM account_log;
-- 创建DELETE触发器
DELIMITER $
CREATE TRIGGER account_delete
AFTER DELETE
ON account
FOR EACH ROW
BEGIN
INSERT INTO account_log VALUES (NULL,'DELETE',NOW(),old.id,CONCAT('删除前{id=',old.id,',name=',old.name,',money=',old.money,'}'));
END$
DELIMITER ;
-- 删除account表数据
DELETE FROM account WHERE id=3;
-- 查询account表
SELECT * FROM account;
-- 查询日志表
SELECT * FROM account_log;
查看触发器
-- 标准语法
SHOW TRIGGERS;
-- 查看触发器
SHOW TRIGGERS;
删除触发器
-- 标准语法
DROP TRIGGER 触发器名称;
-- 删除DELETE触发器
DROP TRIGGER account_delete;
触发器的总结
- 触发器是与表有关的数据库对象
- 可以在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句
- 触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作
- 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容
三、MySQL事务
事物的概念
一条或多条 SQL 语句组成一个执行单元，其特点是这个单元要么同时成功要么同时失败，单元中的每条 SQL 语句都相互依赖，形成一个整体，如果某条 SQL 语句执行失败或者出现错误，那么整个单元就会回滚，撤回到事务最初的状态，如果单元中所有的 SQL 语句都执行成功，则事务就顺利执行。
未管理事务演示
-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERE NAME='张三';
-- 2.李四账户+500
出错了...
UPDATE account SET money=money+500 WHERE NAME='李四';
-- 该场景下，这两条sql语句要么同时成功，要么同时失败。就需要被事务所管理！
管理事务演示
-- 标准语法 开启事务
START TRANSACTION;
-- 标准语法 回滚事务
ROLLBACK;
-- 标准语法 提交事务
COMMIT;
-- 开启事务
START TRANSACTION;
-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERE NAME='张三';
-- 2.李四账户+500
-- 出错了...
UPDATE account SET money=money+500 WHERE NAME='李四';
-- 回滚事务(出现问题)
ROLLBACK;
-- 提交事务(没出现问题)
COMMIT;
事务的提交方式
-- 标准语法
SELECT @@AUTOCOMMIT; -- 1代表自动提交 0代表手动提交
修改提交方式
-- 标准语法
SET @@AUTOCOMMIT=数字;
-- 修改为手动提交
SET @@AUTOCOMMIT=0;
-- 查看提交方式
SELECT @@AUTOCOMMIT;
事务的四大特征
- 原子性
- 一致性
- 事务必须使数据库从一个一致性状态变换到另一个一致性状态
- 隔离性
- 当多个用户并发访问数据库时，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
- 持久性
- 事务一旦被提交了，那么对数据库中的数据的改变就是永久性的
事务的隔离级别
-
隔离级别的概念
- 多个客户端操作时 ,各个客户端的事务之间应该是隔离的，相互独立的 , 不受影响的。
- 而如果多个事务操作同一批数据时，则需要设置不同的隔离级别 , 否则就会产生问题 。
-
四种隔离级别
| 1 |
读未提交 |
read uncommitted |
| 2 |
读已提交 |
read committed |
| 3 |
可重复读 |
repeatable read |
| 4 |
串行化 |
serializable |
| 问题 |
现象 |
| 脏读 |
是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致 |
| 不可重复读 |
是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致 |
| 幻读 |
select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入。或不存在执行delete删除，却发现删除成功 |
-- 标准语法
SELECT @@TX_ISOLATION;
修改数据库隔离级别
-- 标准语法
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;
-- 修改数据库隔离级别为read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;
-- 查看隔离级别
SELECT @@TX_ISOLATION; -- 修改后需要断开连接重新开
隔离级别总结
|
隔离级别 |
名称 |
出现脏读 |
出现不可重复读 |
出现幻读 |
数据库默认隔离级别 |
| 1 |
read uncommitted |
读未提交 |
是 |
是 |
是 |
|
| 2 |
read committed |
读已提交 |
否 |
是 |
是 |
Oracle / SQL Server |
| 3 |
repeatable read |
可重复读 |
否 |
否 |
是 |
MySQL |
| 4 |
serializable |
串行化 |
否 |
否 |
否 |
|
注意：隔离级别从小到大安全性越来越高，但是效率越来越低 , 所以不建议使用READ UNCOMMITTED 和 SERIALIZABLE 隔离级别.
