mysql loop 不在存储过程 mysql不建议用存储过程

存储过程

基本概念

存储过程和存储函数 类似于 Java 当中的方法, 可以对多组 SQL语句,进行封装操作。从 MySQL5.0 版本开始, 支持存储过程和存储函数。

• 存储函数和存储过程的区别(了解)

1.存储函数：必须有返回值。
2.存储过程：可以没有返回值,也可以拥有返回值。

• 优点和缺点(了解)

1.优点：
	A.存储过程可以封装 复杂的SQL语句，简化SQL的编写
	B.存储过程可以回传递增，并接受参数
	C.存储过程无法使用select指定来运行，与查看表数据或定义函数不同
	D.存储过程可以用在数据校验，强制实行商业逻辑等。
	
2.缺点：
	A.存储过程，往往制定在特定的数据库中，因为支持的编程语言不同，当切换其他厂商数据库时候，需要重写原有的存储过程。
	
	B.存储过程性能调校与撰写，受限于种种数据库系统。

准备数据

-- 删除数据库 db7
DROP DATABASE IF EXISTS db7;

-- 创建db7数据库
CREATE DATABASE IF NOT EXISTS db7 CHARACTER SET utf8;

-- 使用数据库
USE  db7;

-- 删除表数据
DROP TABLE IF EXISTS student;

-- 创建学生 student 表
CREATE TABLE IF NOT EXISTS student(
	id INT,  -- 学生id
	name VARCHAR(30),	-- 学生姓名
	age INT, -- 学生年龄
	gender VARCHAR(20),  -- 学生性别
	score INT  -- 考试成绩
);

-- 添加数据
INSERT INTO  student VALUES (1,'张三',23,'男',95),(2,'李四',24,'男',98),(3,'王五',24,'女',100),(4,'赵六',26,'女',90);

-- 查看结果
SELECT * FROM student;

-- 按照性别进行分组,查看每组学生的总成绩. 按照总成绩的升序排列
SELECT gender,SUM(score) '总分' FROM student GROUP BY gender ORDER BY '总分' ASC;

创建存储过程

• 语法

-- 修改结束分隔符
DELIMITER $

-- 创建存储过程
CREATE PROCEDURE  存储过程名称(参数列表)
BEGIN 
    SQL语句列表;
END $

-- 修改结束分隔符(⚠️警告：一定要打空格)
DELIMITER ;

• 举例

-- 修改结束分隔符
DELIMITER $

-- 创建存储过程
CREATE PROCEDURE stu_group ()
BEGIN
	SELECT gender,SUM(score) FROM student GROUP BY gender;
END $

-- 修改结束分隔符
DELIMITER ;

调用存储过程

• 语法

-- 调用存储过程
CALL  存储过程名称(实际参数);

• 举例

-- 案例代码：调用存储过程
call stu_group();

查看存储过程

• 语法

-- 查看存储过程（语法）
SELECT * FROM mysql.proc WHERE db = '数据库名称';

• 举例

-- 案例代码: 查看存储过程
SELECT * FROM mysql.proc WHERE db = 'db7';

删除存储过程

• 语法

-- 删除存储过程（语法）
DROP PROCEDURE IF EXISTS 存储过程名称;

• 举例

-- 案例代码: 删除存储过程
DROP PROCEDURE IF EXISTS stu_group;

存储过程 变量使用

• 创建存储过程，并定义变量

-- 修改结束符
delimiter $

-- 创建存储过程
create procedure stu_getsum()

-- 开始
begin
	-- 定义变量total，默认值10
	declare total int default 10;
	-- 修改变量total的值
	set total=20;
	-- 查询student表的总分，赋值给变量total
	select sum(score) into total from student;
	-- 查询total的结果
	select total;
-- 结束
end $

-- 修改结束符(⚠️警告：一定要打空格)
delimiter ;

• 调用存储过程

call stu_getsum();

存储过程 if语句

• 语法

IF  判断条件  THEN  条件满足需要执行的语句;
ELSEIF  判断条件  THEN  条件满足需要执行的语句;
ELSE  其他情况下需要执行的语句;
END IF;

• 举例

需求：
    1. 定义一个 int 类型的变量,用于存储班级的总成绩
    2. 定义一个 varchar 类型的变量,用于存储分数的描述
    3. 根据总成绩,进行判断
	380分以上:  学习优秀
	320-380分:  学习不错
	320分以下:  学习一般

-- 创建存储过程
DELIMITER $

CREATE PROCEDURE stu_if()
BEGIN
    -- 定义变量,接收结果,查询总成绩赋值。
    DECLARE message VARCHAR(10); 
    DECLARE total INT;
    
    SELECT SUM(score) INTO total FROM student;
    IF total > 380 THEN SET message = '学习优秀';
    ELSEIF total >= 320 AND total <= 380 THEN SET message = '学习不错';
    ELSE SET message = '学习一般';
    END IF;
    -- 查询总成绩和描述信息
    SELECT total,message;
    
END $
DELIMITER ;

-- 第03步：调用存储过程
CALL stu_if();

存储过程 参数传递

• 语法

CREATE  PROCEDURE  存储过程的名称([IN][OUT][INOUT]  参数名称  参数类型)
BEGIN 
		SQL语句列表;
END $

• 举例

-- 第01步: 删除存储过程
DROP PROCEDURE IF EXISTS stu_param;

-- 第02步: 创建存储过程,设置为参数传递
DELIMITER $
CREATE PROCEDURE stu_param(IN total INT,INOUT message VARCHAR(20))
BEGIN
	IF total>380 THEN SET message = '学习优秀';
	ELSEIF total>=320 AND total<=380 THEN SET message = '学习不错';
	ELSE SET message = '学习一般';
	END IF;
END $
DELIMITER ;

-- 第03步: 调用存储过程
CALL stu_param(350,@message);

-- 第04步: 查询返回结果
SELECT @message;

存储过程 循环

• 语法

初始化语句;
WHILE  	条件判断语句 DO
		循环体语句;
		条件控制语句;
END  WHILE;

• 举例

-- 第01步：删除存储过程
DROP PROCEDURE IF EXISTS stu_loop;

-- 第02步：创建存储过程,计算 1-100 之间的偶数和
DELIMITER $

CREATE PROCEDURE stu_loop()
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE num INT DEFAULT 1;
    WHILE num <= 100 DO 
    IF num % 2 = 0 THEN SET result = result + num;
    END IF;
    SET num = num + 1;
END WHILE;
SELECT result;
END $

DELIMITER ;

-- 第03步: 调用存储过程
CALL stu_loop();

存储函数

存储函数和存储过程理解上是一样的，只是存储函数有返回值，必须写return语句。

无参数的存储函数

• 创建存储函数格式

DELIMITER $
CREATE  FUNCTION  函数名称(参数列表)  RETURNS 返回值类型
BEGIN
	SQL 语句列表;
	RETURN  结果;
END $
DELIMITER ;

• 创建存储函数举例

-- 查询学生表当中,成绩大于 95分的学生人数。

-- 第01步: 删除存储函数
DROP FUNCTION IF EXISTS stu_hanshu;

-- 第02步: 创建存储函数
DELIMITER $
CREATE FUNCTION  stu_hanshu()  
RETURNS INT
BEGIN 
	-- 定义变量,记录查询结果,返回
	DECLARE total INT;
	-- 获取到查询的结果
	SELECT COUNT(*) INTO total FROM student WHERE score>95;
	-- 返回结果
	RETURN total;
END $
DELIMITER ;

-- 第03步: 调用存储函数
SELECT stu_hanshu();

有参数的存储函数

-- 查询学生表当中,成绩大于 95分的学生人数。

-- 第01步: 删除存储函数
DROP FUNCTION IF EXISTS stu_hanshu;

-- 第02步: 创建存储函数
DELIMITER $
CREATE FUNCTION  stu_hanshu()  
RETURNS INT
BEGIN 
	-- 定义变量,记录查询结果,返回
	DECLARE total INT;
	-- 获取到查询的结果
	SELECT COUNT(*) INTO total FROM student WHERE score>95;
	-- 返回结果
	RETURN total;
END $
DELIMITER ;

-- 第03步: 调用存储函数
SELECT stu_hanshu();

触发器

触发器就是在表数据发生变化的时候，自动触发的一些 SQL 操作。

触发器分类

触发器类型	OLD 触发器之前的效果	NEW 触发器之后的效果
INSERT 类型的触发器	无（因为插入前，没有之前的数据）	NEW 表示将要或者已经新增的数据
UPDATE 类型的触发器	OLD 表示修改之前的数据	NEW 表示将要或者已经修改后的数据
DELETE 类型的触发器	OLD 表示将要或者已经修改的数据	无（因为删除后，状态无数据）

准备数据

-- 删除数据库
DROP DATABASE IF EXISTS db8;

-- 创建数据库 db8
CREATE DATABASE IF NOT EXISTS db8 CHARSET utf8;

-- 使用数据库
USE db8;

-- 创建账户表
CREATE TABLE IF NOT EXISTS account(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 账户id
	NAME VARCHAR(20),  	-- 姓名
	money DOUBLE		-- 余额
);

-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);

-- 创建日志表 account_log
CREATE TABLE IF NOT EXISTS account_log(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 日志id
	operation VARCHAR(20),  	-- 操作的类型(insert update delete)
	operation_time DATETIME, 	-- 操作时间
	operation_id INT,		-- 操作表的id
	operation_params VARCHAR(200)	-- 操作参数
);

INSERT 触发器

基础语法

-- 删除触发器
DROP TRIGGER IF EXISTS 触发器的名称;

-- 第02步,创建触发器
DELIMITER $

CREATE TRIGGER  触发器的名称  
AFTER INSERT ON 需要监测的表名称  FOR EACH ROW 
BEGIN
	INSERT INTO 记录结果的表名称 VALUES (值1,值2,值3,值4);
END $

DELIMITER ;

代码举例

-- 创建 INSERT 类型的触发器
-- 第01步,删除触发器
DROP TRIGGER IF EXISTS account_insert;

-- 第02步,创建触发器
DELIMITER $

CREATE TRIGGER account_insert  
AFTER INSERT ON account FOR EACH ROW 
BEGIN
	INSERT INTO account_log VALUES (
		NULL,
		'INSERT',
		NOW(),     -- 使用NOW函数,获取到当前的系统时间
		new.id,
		CONCAT('插入后{id=',new.id,'name=',new.name,'money=',new.money,'}')
	);
END $

DELIMITER ;

-- 第03步,向 account 表当中添加一条记录
INSERT INTO account VALUES (NULL,'王五',2000);

-- 第04步, 查询 account 表记录
SELECT * FROM account;

-- 第05步, 查询 account_log 表记录
SELECT * FROM account_log;

UPDATE 触发器

基础语法

-- 创建触发器, 创建更新的触发器 
DELIMITER $

CREATE TRIGGER  触发器的名称  
AFTER UPDATE ON  需要监测的表名称 FOR EACH ROW 
BEGIN
	INSERT INTO 记录结果的表名称 VALUES (值1,值2,值3,值4);
END $

DELIMITER ;

代码实现

-- 创建 UPDATE 类型的触发器

-- 第01步,删除触发器
DROP TRIGGER IF EXISTS account_update;

-- 第02步,创建触发器
DELIMITER $

CREATE TRIGGER account_update  
AFTER UPDATE ON account FOR EACH ROW 
BEGIN
	INSERT INTO account_log VALUES (
		NULL,
		'UPDATE',
		NOW(),     -- 使用NOW函数,获取到当前的系统时间
		new.id,
		CONCAT('更新前{id=',old.id,'name=',old.name,'money=',old.money,'}',
		'更新后{id=',new.id,'name=',new.name,'money=',new.money,'}')
	);
END $

DELIMITER ;

-- 第03步,向 account 表当中更新一条记录
UPDATE account SET money = money+10000 WHERE id = 1;

-- 第04步, 查询 account 表记录
SELECT * FROM account;

-- 第05步, 查询 account_log 表记录
SELECT * FROM account_log;

DELETE 触发器

基础语法

-- 创建触发器, 创建删除的触发器 
DELIMITER $

CREATE TRIGGER 触发器的名称
AFTER DELETE ON 需要监测的表名称 FOR EACH ROW 
BEGIN
	INSERT INTO 记录结果的表名称 VALUES (值1,值2,值3,值4);
END $

DELIMITER ;

代码实现

-- 创建 DELETE 类型的触发器
 
-- 第01步,删除触发器
DROP TRIGGER IF EXISTS account_delete;

-- 第02步,创建触发器
DELIMITER $

CREATE TRIGGER account_delete
AFTER DELETE ON account FOR EACH ROW 
BEGIN
	INSERT INTO account_log VALUES (
		NULL,
		'DELETE',
		NOW(),     -- 使用NOW函数,获取到当前的系统时间
		old.id,
		CONCAT('删除前{id=',old.id,'name=',old.name,'money=',old.money,'}')
	);
END $

DELIMITER ;

-- 第03步,向 account 表当中删除一条记录
DELETE FROM account WHERE id = 2;

-- 第04步, 查询 account 表记录
SELECT * FROM account;

-- 第05步, 查询 account_log 表记录
SELECT * FROM account_log;

查看和删除触发器

-- 1. 查看触发器
SHOW  TRIGGERS;

-- 2. 删除触发器
DROP  TRIGGER  触发器的名称;

MySQL事务

事务介绍

• 事务：一条或多条SQL语句组成一个执行单元，其特点是这个单元要么同时成功，要么同时失败
• 单元中的每条SQL语句都相互依赖，形成一个整体
• 如果某条SQL语句执行失败或者出现错误，那么整个单元就会撤回到事务最初的状态
• 如果单元中的所有SQL语句都执行成功，则事务就顺利执行

事务处理可以用来维护数据库的完整性，多条的 SQL 语句要么全部执行，要么全部不执行。

事务举例：
	张三给李四转账500元钱，需要让三账户的余额-500，李四的账户余额+500，转账是一个连续的过程，要么同时成功，要么同时失败。不能出现张三转出了500块，而李四没收到的情况，这将是很严重的问题。

准备数据

-- 1. 删除数据库,如果存在,则删除
DROP DATABASE IF  EXISTS db7;

-- 2. 创建数据库
CREATE DATABASE IF NOT EXISTS db7 CHARSET utf8;

-- 3. 使用数据库
USE db7;

-- 4. 创建账户表信息
CREATE TABLE IF NOT EXISTS account(
	id INT PRIMARY KEY AUTO_INCREMENT,  -- 账户的id
	NAME VARCHAR(20),  -- 账户名称
	money DOUBLE       -- 账户余额
);

-- 5. 添加数据
INSERT INTO account VALUES (NULL,'张三',2000),(NULL,'李四',2000);

-- 6. 查询数据表
SELECT * FROM account;

-- 7. 修改账户信息,都修改成为 2000 元
UPDATE account SET money = 2000;

事务的基本使用

-- 1. 开启事务
START TRANSACTION; 

-- 2. 修改数据表记录,张三的账户,金额减少 100 元
UPDATE account SET money = money - 100 WHERE NAME = '张三';

-- 3. 修改数据表记录,李四的账户,金额增加 100 元
UPDATE account SET money = money + 100 WHERE NAME = '李四';

-- 5. 提交事物
COMMIT;   

-- 6. 查询账户信息
select * from account;

事务的提交方式

1. 事务提交方式分类:
	A. 自动提交 (MySQL 默认的提交方式)
	B. 手动提交 (Oracle 采用的提交方式)

2. 相关SQL语法
	-- 查看事务的提交方式, 说明: 1表示自动提交, 0表示手动提交
	SELECT @@AUTOCOMMIT;
	-- 修改事务的提交方式, 标准语法
	SELECT @@AUTOCOMMIT = 数字;

事务的四大特征

• 原子性：要嘛成功要嘛失败
• 一致性：事务开启之前和开启之后的状态一致(转账前后的钱一样)
• 隔离性：多用户访问同一张数据表时，不可以被干扰操作
• 持久性：改变数据就是永久保存了

事务的隔离级别

多个事务之间是隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

• 存在的问题

1. 脏读：一个事务，读取到另一个事务中没有提交的数据
2. 不可重复读：在同一个事务中，两次读取到的数据不一样,读取到已提交的数据。
3. 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

序号	隔离级别	名称	脏读	不可重复读	幻读	数据库默认隔离级别
1	READ UNCOMMITTED	读未提交	YES	YES	YES
2	READ COMMITTED	读已提交	NO	YES	YES	Oracle 数据库默认级别
3	REPEATABLE READ	可重复读	NO	NO	YES	MySQL 数据库默认级别
4	SERIALIZABLE	串行化	NO	NO	NO

• 设置隔离级别

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

数据库查询隔离级别：
    select @@tx_isolation;
数据库设置隔离级别：(需要重新连接才可以查看修改之后的数据)
    set global transaction isolation level  级别字符串;
    set session transaction isolation level 级别字符串;(改变当前会话)