MySQL高级-03-授课笔记

day03-实训在线授课笔记 文章目录课前回顾:项目正规流程:立项:初期:1)需要的服务器(物理机,虚拟机,阿里云),数据库,环境,带宽2) 需求: 需求分析文档(3个月)-设计(UI,产品经理-模型)-编程-测试-实施(上线)电商项目举例3) 项目运维的部署方案(为了后期方便别人操作)4) 发现问题: 写出大致的解决方案,提交,进行上级的授权。5) 优化和维护6)监控 课前回顾: 技术名词: devops, CI/CD,... 阅读详情

MySQL高级-03-授课笔记

一、MySQL存储过程和函数

1. 存储过程和函数的概念
  • 存储过程和函数是:事先经过编译并存储在数据库中的一段 SQL 语句的集合
  • 存储过程和函数,类似于java里的方法
    • 存储过程和函数,是存储了多行sql语句
    • java是存储了多行java代码
  • 存储过程与函数的应用场景:
    • 如果某N条sql语句,比较复杂,比较常用,有业务逻辑,这时候就可以将这N条sql语句放入存储过程或函数中
2. 存储过程和函数的好处
  • 存储过程和函数可以重复使用,减轻开发人员的工作量。类似于java中方法可以多次调用
  • 减少网络流量,存储过程和函数位于服务器上,调用的时候只需要传递名称和参数即可
  • 减少数据在数据库和应用服务器之间的传输,可以提高数据处理的效率
  • 将一些业务逻辑在数据库层面来实现,可以减少代码层面的业务处理
3. 存储过程和函数的区别
  • 函数必须有返回值
  • 存储过程没有返回值
4. 创建存储过程
  • 小知识
/*
	该关键字用来声明sql语句的分隔符,告诉MySQL该段命令已经结束!
	sql语句默认的分隔符是分号,但是有的时候我们需要一条功能sql语句中包含分号,但是并不作为结束标识。
	这个时候就可以使用DELIMITER来指定分隔符了!
*/
-- 标准语法
DELIMITER 分隔符
delimiter:  [dɪ'lɪmɪtə]  分隔符
因为在存储过程中会有多条SQL语句,那如果还是以分号分隔,那mysql就会认为这存储多过程中的第一条sql语句执行完就结束了,所以我们需要在使用存储过程的时候修改分隔符
  • 数据准备
-- 创建db8数据库
CREATE DATABASE db8;

-- 使用db8数据库
USE db8;

-- 创建学生表
CREATE TABLE student(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 学生id
	NAME VARCHAR(20),					-- 学生姓名
	age INT,							-- 学生年龄
	gender VARCHAR(5),					-- 学生性别
	score INT                           -- 学生成绩
);
-- 添加数据
INSERT INTO student VALUES (NULL,'张三',23,'男',95),(NULL,'李四',24,'男',98),
(NULL,'王五',25,'女',100),(NULL,'赵六',26,'女',90);

-- 按照性别进行分组,查询每组学生的总成绩。按照总成绩的升序排序
SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
  • 创建存储过程语法
-- 修改分隔符为$  (注意:创建存储过程之前修改分隔符为$)
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称(参数...)  -- procedure:[prəˈsi:dʒə(r)] 过程
BEGIN
	sql语句;  -- 注意:数据库不会认为这里是结束
END$  -- 注意:数据库认为这里才是结束

-- 修改分隔符为分号
DELIMITER ;  -- 注意:只有在存储过程中不能用;结束,既然存储过程都创建好了,我们就再改为以;结束
  • 创建存储过程
-- 修改分隔符为$
DELIMITER $

-- 创建存储过程,封装分组查询学生总成绩的sql语句
CREATE PROCEDURE stu_group()
BEGIN
	-- 注意:我们这里是简单使用一些存储过程,所以只写了一句sql,不过即使只有一句sql,放到存储过程之后,可以重复使用
	SELECT gender,SUM(score) getSum FROM student GROUP BY gender ORDER BY getSum ASC;
END$

-- 修改分隔符为分号
DELIMITER ;
5. 调用存储过程
  • 调用存储过程语法
-- 标准语法
CALL 存储过程名称(实际参数);

-- 调用stu_group存储过程
CALL stu_group();
6. 查看存储过程
  • 查看存储过程语法
-- 查询数据库中所有的存储过程 标准语法
SELECT * FROM mysql.proc WHERE db='数据库名称';
-- 注意: proc:procedure
-- 理解: 相当于存储过程是存储在mysql.proc这个表中,这个表中有一个字段db,是当前存储过程所属的数据库
7. 删除存储过程
  • 删除存储过程语法
-- 标准语法(注意: 语法中,中括号的内容是可选内容)
DROP PROCEDURE [IF EXISTS] 存储过程名称;  -- 注意: 中括号中的if exists , 可写可不写

-- 删除stu_group存储过程
DROP PROCEDURE stu_group; -- 注意: 删除存储过程,不需要写小括号,调用的时候需要
8. 存储过程语法
8.1 存储过程语法介绍
  • 存储过程是可以进行编程的。意味着可以使用变量、表达式、条件控制语句、循环语句等,来完成比较复杂的功能!
8.2 变量的使用
  • 定义变量
-- 标准语法
DECLARE 变量名 数据类型 [DEFAULT 默认值];
-- 注意: DECLARE定义的是局部变量,只能用在BEGIN END范围之内
-- declare :  [dɪˈkleə(r)]  声明

-- 定义一个int类型变量、并赋默认值为10
DELIMITER $

CREATE PROCEDURE pro_test1()
BEGIN
	DECLARE num INT DEFAULT 10;   -- 定义变量
	SELECT num;                   -- 查询变量
END$

DELIMITER ;

-- 调用pro_test1存储过程
CALL pro_test1();
  • 变量的赋值1
-- 标准语法
SET 变量名 = 变量值;

-- 定义字符串类型变量,并赋值
DELIMITER $

CREATE PROCEDURE pro_test2()
BEGIN
	DECLARE NAME VARCHAR(10);   -- 定义变量
	SET NAME = '存储过程';       -- 为变量赋值
	SELECT NAME;                -- 查询变量
END$

DELIMITER ;

-- 调用pro_test2存储过程
CALL pro_test2();
  • 变量的赋值2: 将查询结果输入到变量
-- 标准语法
SELECT 列名 INTO 变量名 FROM 表名 [WHERE 条件]; -- into 输入

-- 定义两个int变量,用于存储男女同学的总分数
DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
	DECLARE men,women INT;  -- 定义变量
	SELECT SUM(score) INTO men FROM student WHERE gender='男';    -- 计算男同学总分数赋值给men
	SELECT SUM(score) INTO women FROM student WHERE gender='女';  -- 计算女同学总分数赋值给women
	SELECT men,women;           -- 查询变量
END$

DELIMITER ;

-- 调用pro_test3存储过程
CALL pro_test3();
8.3 if语句的使用
  • 标准语法
-- 标准语法
IF 判断条件1 THEN 执行的sql语句1;    -- 注意: then 然后, 如果满足添加,然后执行xx
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;
  • 案例演示
/*
	定义一个int变量,用于存储班级总成绩
	定义一个varchar变量,用于存储分数描述
	根据总成绩判断:
		380分及以上    学习优秀
		320 ~ 380     学习不错
		320以下       学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test4()
BEGIN
	-- 定义总分数变量
	DECLARE total INT;
	-- 定义分数描述变量
	DECLARE description VARCHAR(10);
	-- 为总分数变量赋值
	SELECT SUM(score) INTO total FROM student;
	-- 判断总分数
	IF total >= 380 THEN 
		SET description = '学习优秀';
	ELSEIF total >= 320 AND total < 380 THEN 
		SET description = '学习不错';
	ELSE 
		SET description = '学习一般';
	END IF;
	
	-- 查询总成绩和描述信息
	SELECT total,description;
END$

DELIMITER ;

-- 调用pro_test4存储过程
CALL pro_test4();
8.4 参数的传递
  • 参数传递的语法
DELIMITER $

-- 标准语法
CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT] 参数名 数据类型)
BEGIN
	执行的sql语句;
END$
/*
	IN:代表输入参数,需要由调用者传递实际数据。默认的
	OUT:代表输出参数,该参数可以作为返回值
	INOUT:代表既可以作为输入参数,也可以作为输出参数
*/
DELIMITER ;
  • 输入参数

    • 标准语法
    DELIMITER $
    
    -- 标准语法
    CREATE PROCEDURE 存储过程名称(IN 参数名 数据类型)
    BEGIN
    	执行的sql语句;
    END$
    
    DELIMITER ;
    
    • 案例演示
    /*
    	输入总成绩变量,代表学生总成绩
    	定义一个varchar变量,用于存储分数描述
    	根据总成绩判断:
    		380分及以上  学习优秀
    		320 ~ 380    学习不错
    		320以下      学习一般
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test5(IN total INT)
    BEGIN
    	-- 定义分数描述变量
    	DECLARE description VARCHAR(10);
    	-- 判断总分数
    	IF total >= 380 THEN 
    		SET description = '学习优秀';
    	ELSEIF total >= 320 AND total < 380 THEN 
    		SET description = '学习不错';
    	ELSE 
    		SET description = '学习一般';
    	END IF;
    	
    	-- 查询总成绩和描述信息
    	SELECT total,description;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test5存储过程
    CALL pro_test5(390);
    CALL pro_test5((SELECT SUM(score) FROM student));
    
  • 输出参数

    • 标准语法
    DELIMITER $
    
    -- 标准语法
    CREATE PROCEDURE 存储过程名称(OUT 参数名 数据类型)
    BEGIN
    	执行的sql语句;
    END$
    
    DELIMITER ;
    
    • 案例演示
    /*
    	输入总成绩变量,代表学生总成绩
    	输出分数描述变量,代表学生总成绩的描述
    	根据总成绩判断:
    		380分及以上  学习优秀
    		320 ~ 380    学习不错
    		320以下      学习一般
    */
    DELIMITER $
    
    CREATE PROCEDURE pro_test6(IN total INT,OUT description VARCHAR(10))
    BEGIN
    	-- 判断总分数
    	IF total >= 380 THEN 
    		SET description = '学习优秀';
    	ELSEIF total >= 320 AND total < 380 THEN 
    		SET description = '学习不错';
    	ELSE 
    		SET description = '学习一般';
    	END IF;
    END$
    
    DELIMITER ;
    
    -- 调用pro_test6存储过程
    CALL pro_test6(310,@description); -- 注意: @变量名,定义一个会话变量,这个变量作为输出参数传递给存储过程,然后在存储过程中,进行赋值,最后可以通过select 进行查询变量的值
    
    -- 查询总成绩描述
    SELECT @description;
    
    • 小知识
    @变量名:  这种变量要在变量名称前面加上“@”符号,叫做用户会话变量,代表整个会话过程他都是有作用的,这个类似于全局变量一样。
    	会话:是指一次连接(再打开sqlyog,然后连接,这时候就会丢失会话变量)
    
    @@变量名: 这种在变量前加上 "@@" 符号, 叫做系统变量 
    
8.5 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;
  • 案例演示
/*
	输入总成绩变量,代表学生总成绩
	定义一个varchar变量,用于存储分数描述
	根据总成绩判断:
		380分及以上  学习优秀
		320 ~ 380    学习不错
		320以下      学习一般
*/
DELIMITER $

CREATE PROCEDURE pro_test7(IN total INT)
BEGIN
	-- 定义变量
	DECLARE description VARCHAR(10);
	-- 使用case判断
	CASE
	WHEN total >= 380 THEN
		SET description = '学习优秀';
	WHEN total >= 320 AND total < 380 THEN
		SET description = '学习不错';
	ELSE 
		SET description = '学习一般';
	END CASE;
	
	-- 查询分数描述信息
	SELECT description;
END$

DELIMITER ;

-- 调用pro_test7存储过程
CALL pro_test7(390);
CALL pro_test7((SELECT SUM(score) FROM student));
8.6 while循环
  • 标准语法
-- 标准语法
初始化语句;
WHILE 条件判断语句 DO
	循环体语句;
	条件控制语句;
END WHILE;
  • 案例演示
/*
	计算1~100之间的偶数和
*/
DELIMITER $

CREATE PROCEDURE pro_test8()
BEGIN
	-- 定义求和变量
	DECLARE result INT DEFAULT 0;
	-- 定义初始化变量
	DECLARE num INT DEFAULT 1;
	-- while循环
	WHILE num <= 100 DO
		-- 偶数判断
		IF num%2=0 THEN
			SET result = result + num; -- 累加
		END IF;
		
		-- 让num+1
		SET num = num + 1;         
	END WHILE;
	
	-- 查询求和结果
	SELECT result;
END$

DELIMITER ;

-- 调用pro_test8存储过程
CALL pro_test8();
9. 存储过程的总结
  • 存储过程是 事先经过编译并存储在数据库中的一段 SQL 语句的集合。可以在数据库层面做一些业务处理
  • 说白了存储过程其实就是将sql语句封装为方法,然后可以调用方法执行sql语句而已
  • 存储过程的好处
    • 安全
    • 高效
    • 复用性强
10. 存储函数
  • 存储函数和存储过程是非常相似的。存储函数可以做的事情,存储过程也可以做到!

  • 存储函数有返回值,存储过程没有返回值(参数的out其实也相当于是返回数据了)

  • 标准语法

    • 创建存储函数
    DELIMITER $
    
    -- 标准语法
    CREATE FUNCTION 函数名称([参数 数据类型])
    RETURNS 返回值类型
    BEGIN
    	执行的sql语句;
    	RETURN 结果;
    END$
    
    DELIMITER ;
    
    • 调用存储函数
    -- 标准语法
    SELECT 函数名称(实际参数);  -- 调用存储函数,使用的是select,不是call
    
    • 删除存储函数
    -- 标准语法
    DROP FUNCTION 函数名称;
    
  • 案例演示

/*
	定义存储函数,获取学生表中成绩大于95分的学生数量
*/
DELIMITER $

CREATE FUNCTION fun_test1()
RETURNS INT
BEGIN
	-- 定义统计变量
	DECLARE result INT;
	-- 查询成绩大于95分的学生数量,给统计变量赋值
	SELECT COUNT(*) INTO result FROM student WHERE score > 95;
	-- 返回统计结果
	RETURN result;
END$

DELIMITER ;

-- 调用fun_test1存储函数
SELECT fun_test1();

二、MySQL触发器

1. 触发器的概念
  • 触发器是与表有关的数据库对象,可以在 insert/update/delete 之前或之后,触发并执行触发器中定义的SQL语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作 。
  • 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。
触发器类型OLD的含义NEW的含义
INSERT 型触发器无 (因为插入前状态无数据)NEW 表示将要或者已经新增的数据
UPDATE 型触发器OLD 表示修改之前的数据NEW 表示将要或已经修改后的数据
DELETE 型触发器OLD 表示将要或者已经删除的数据无 (因为删除后状态无数据)
  • 理解:触发器:一个功能执行之后,自动触发另一个功能执行

  • 例子:触发器实现日志记录功能
    在这里插入图片描述

    • 插入王五,就会在log表中有一条insert操作的记录
    • 更新李四,就会在log表中有一条update操作的记录
    • 通过new关键字可以获取新的数据,通过old关键字可以获取旧的数据
2. 创建触发器
  • 标准语法
DELIMITER $

CREATE TRIGGER 触发器名称   -- Trigger:触发器
BEFORE|AFTER INSERT|UPDATE|DELETE  -- 之前|之后(触发时机) 插入|更新|删除(触发器类型)
ON 表名
[FOR EACH ROW]  -- 行级触发器
BEGIN
	触发器要执行的功能;
END$

DELIMITER ;
# 注意:触发时机一般都是after之后,意思就是在数据改变 之后 , 执行触发器
  • 触发器演示。通过触发器记录账户表的数据变更日志。包含:增加、修改、删除

    • 创建账户表
    -- 创建db9数据库
    CREATE DATABASE db9;
    
    -- 使用db9数据库
    USE db9;
    
    -- 创建账户表account
    CREATE TABLE account(
    	id INT PRIMARY KEY AUTO_INCREMENT,	-- 账户id
    	NAME VARCHAR(20),					-- 姓名
    	money DOUBLE						-- 余额
    );
    -- 添加数据
    INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',2000);
    
    • 创建日志表
    -- 创建日志表account_log
    CREATE TABLE 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触发器
    -- 创建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,'}'));
    	-- 注意: 
    	-- 要获取新增之后的数据,也就是新数据,所以通过new关键字获取
    	-- CONCAT() 字符串拼接,concat:concatenate 连接
    END$
    
    DELIMITER ;
    
    -- 向account表添加记录
    INSERT INTO account VALUES (NULL,'王五',3000);
    
    -- 查询account表
    SELECT * FROM account;
    
    -- 查询日志表
    SELECT * FROM account_log;
    
    • 创建UPDATE触发器
    -- 创建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,'}'));
    	-- 注意:修改数据,新旧数据都需要保存,所以需要通过new和old获取新旧数据
    END$
    
    DELIMITER ;
    
    -- 修改account表
    UPDATE account SET money=3500 WHERE id=3;
    
    -- 查询account表
    SELECT * FROM account;
    
    -- 查询日志表
    SELECT * FROM account_log;
    
    • 创建DELETE触发器
    -- 创建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,'}'));
    	-- 注意: 删除的数据需要记录,通过old获取删除之前的旧数据
    END$
    
    DELIMITER ;
    
    -- 删除account表数据
    DELETE FROM account WHERE id=3;
    
    -- 查询account表
    SELECT * FROM account;
    
    -- 查询日志表
    SELECT * FROM account_log;
    
3. 查看触发器
-- 标准语法
SHOW TRIGGERS;  -- 查看触发器是show,显示,不是select , 与 查看所有数据库,表的语句一样:show databases,show tables

-- 查看触发器
SHOW TRIGGERS;
4. 删除触发器
-- 标准语法
DROP TRIGGER 触发器名称;

-- 删除DELETE触发器
DROP TRIGGER account_delete;
5. 触发器的总结
  • 触发器是与表有关的数据库对象
  • 可以在 insert/update/delete 之前或之后,触发并执行触发器中定义的SQL语句
  • 触发器的这种特性可以协助应用在数据库端确保数据的完整性 、日志记录 、数据校验等操作
  • 使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容

三、MySQL事务

1. 事务的概念
  • 一条或多条 SQL 语句组成一个执行单元,其特点是这个单元要么同时成功要么同时失败,单元中的每条 SQL 语句都相互依赖,形成一个整体

  • 如果某条 SQL 语句执行失败或者出现错误,那么整个单元就会回滚,撤回到事务最初的状态,

  • 如果单元中所有的 SQL 语句都执行成功,则事务就顺利执行

  • 很多逻辑是由多个sql语句组成的,而且还需要保证这多个sql语句都得同时执行成功

  • 应用场景:如果一个操作中有多个sql语句要执行,这时候就要保证这多个sql语句是一个整体,要么同时成功要么同时失败

  • 比如:银行转账
    在这里插入图片描述

    • 如果第一条sql语句执行成功,但是由于某些原因第二条sql语句执行失败了
    • 这时候张三就会莫名其妙的少了500块钱
    • 这种事情,在银行是不会发生的
    • 那银行如何保证都成功呢?通过事务
2. 事务的数据准备
-- 创建db10数据库
CREATE DATABASE db10;

-- 使用db10数据库
USE db10;

-- 创建账户表
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- 账户id
	NAME VARCHAR(20),			-- 账户名称
	money DOUBLE				-- 账户余额
);
-- 添加数据
INSERT INTO account VALUES (NULL,'张三',1000),(NULL,'李四',1000);
3. 未管理事务演示
-- 张三给李四转账500元
-- 1.张三账户-500
UPDATE account SET money=money-500 WHERE NAME='张三';
-- 2.李四账户+500
出错了... -- 这里因为没有添加注释,中文执行肯定报错
UPDATE account SET money=money+500 WHERE NAME='李四';

-- 该场景下,这两条sql语句要么同时成功,要么同时失败。就需要被事务所管理!
4. 管理事务演示
  • 操作事务的三个步骤
    1. 开启事务:记录回滚点,并通知服务器,将要执行一组操作,要么同时成功、要么同时失败
    2. 执行sql语句:执行具体的一条或多条sql语句
    3. 结束事务(提交|回滚)
      • 提交:没出现问题,数据进行更新
      • 回滚:出现问题,数据恢复到开启事务时的状态
  • 开启事务
-- 标准语法
START TRANSACTION; -- Transaction:[trænˈzækʃn] 事务
  • 回滚事务
-- 标准语法
ROLLBACK;  -- rollback:反转
  • 提交事务
-- 标准语法
COMMIT;  -- 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;
5. 事务的提交方式
  • 提交方式

    • 自动提交(MySQL默认为自动提交)
    • 手动提交
  • 修改提交方式

    • 查看提交方式
    -- 标准语法
    SELECT @@AUTOCOMMIT;  -- 1代表自动提交    0代表手动提交
    -- 注意:@@autocommit是一个系统变量
    
    • 修改提交方式
    -- 标准语法
    SET @@AUTOCOMMIT=数字;
    
    -- 修改为手动提交
    SET @@AUTOCOMMIT=0;
    
    -- 查看提交方式
    SELECT @@AUTOCOMMIT;
    
    • 如果改为收到提交,那么所有的增删改sql语句,都需要自己执行commit才可以
    UPDATE account SET money=2000 WHERE NAME='张三';
    -- 执行完之后,查询表中数据,发现已经是2000了,其实这个数据是临时的数据,退出sqlyog再次进入就会恢复
    -- 只有执行过commit才可以
    commit;
    
6. 事务的四大特征(ACID)
  • 原子性(atomicity)
    • 原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚,因此事务的操作如果成功就必须要完全应用到数据库,如果操作失败则不能对数据库有任何影响
  • 一致性(consistency)
    • 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态
    • 拿转账来说,假设张三和李四两者的钱加起来一共是2000,那么不管A和B之间如何转账,转几次账,事务结束后两个用户的钱相加起来应该还得是2000,这就是事务的一致性
  • 隔离性(isolcation)
    • 隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离
  • 持久性(durability)
    • 持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作
7. 事务的隔离级别
  • 隔离级别的概念
    • 多个客户端操作时 ,各个客户端的事务之间应该是隔离的,相互独立的 , 不受影响的。
    • 而如果多个事务操作同一批数据时,则需要设置不同的隔离级别 , 否则就会产生问题 。
    • 我们先来了解一下四种隔离级别的名称 , 再来看看可能出现的问题
  • 四种隔离级别
1读未提交read uncommitted
2读已提交read committed
3可重复读repeatable read
4串行化serializable
  • 可能引发的问题
问题现象
脏读是指在一个事务处理过程中读取了另一个未提交的事务中的数据 , 导致两次查询结果不一致
不可重复读是指在一个事务处理过程中读取了另一个事务中修改并已提交的数据, 导致两次查询结果不一致
幻读select 某记录是否存在,不存在,准备插入此记录,但执行 insert 时发现此记录已存在,无法插入。或查询数据不存在执行delete删除,却发现删除成功
  • 查询数据库隔离级别
-- 标准语法
SELECT @@TX_ISOLATION;
  • 修改数据库隔离级别
-- 标准语法
SET GLOBAL TRANSACTION ISOLATION LEVEL 级别字符串;  -- isolation : [ˌaɪsəˈleɪʃn] 隔离

-- 修改数据库隔离级别为read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;

-- 查看隔离级别
SELECT @@TX_ISOLATION;   -- 修改后需要断开连接重新开
8. 事务隔离级别演示
  • 脏读的问题

    • 窗口1
    -- 查询账户表
    select * from account;
    
    -- 设置隔离级别为read uncommitted  (注意:级别为读未提交,会造成脏读问题)
    set global transaction isolation level read uncommitted;
    
    -- 开启事务  (窗口2也执行此sql开启事务)
    start transaction;
    
    -- 转账
    update account set money = money - 500 where id = 1;
    update account set money = money + 500 where id = 2;
    
    -- 窗口2查询转账结果 ,出现脏读(查询到其他事务未提交的数据)
    
    -- 窗口1执行回滚  (窗口2查看转账结果后,发现被骗了)
    rollback;
    
    • 窗口2 (再开一个sqlyog连接到数据库)
    -- 查询隔离级别
    select @@tx_isolation;
    
    -- 开启事务
    start transaction;
    
    -- 查询账户表
    select * from account;
    
  • 解决脏读的问题和演示不可重复读的问题

    • 窗口1
    -- 设置隔离级别为read committed  (注意: 级别为读已提交,可以解决脏读问题)
    set global transaction isolation level read committed;
    
    -- 开启事务 (窗口2也执行此sql开启事务)
    start transaction;
    
    -- 转账
    update account set money = money - 500 where id = 1;
    update account set money = money + 500 where id = 2;
    
    -- 窗口2查看转账结果,并没有发生变化(脏读问题被解决了)
    
    -- 执行提交事务。
    commit;
    
    -- 窗口2查看转账结果,数据发生了变化(出现了不可重复读的问题,读取到其他事务已提交的数据)
    -- 注意: 在窗口2中查询两次,结果数据不一样,这就是不可重复读
    -- 其实这不是问题,正常现象就应该是如此
    -- 老师举的例子,小王上午查询当年度的营收是100w,下午老板查询是80w,然后出现了出入,其实这也是正常的,因为数据一直在变化,如果出现这种情况,小王完全可以说,我统计的是某个时间点之前的营收。
    
    • 窗口2
    -- 查询隔离级别(更改过隔离级别,最好重新开启窗口2,然后查询隔离级别)
    select @@tx_isolation;
    
    -- 开启事务
    start transaction;
    
    -- 查询账户表
    select * from account;
    
  • 解决不可重复读的问题

    • 窗口1
    -- 设置隔离级别为repeatable read
    set global transaction isolation level repeatable read;
    
    -- 开启事务(窗口2也执行此sql开启事务)
    start transaction;
    
    -- 转账
    update account set money = money - 500 where id = 1;
    update account set money = money + 500 where id = 2;
    
    -- 窗口2查看转账结果,并没有发生变化
    
    -- 执行提交事务
    commit;
    
    -- 这个时候窗口2只要还在上次事务中,看到的结果都是相同的。只有窗口2结束事务,才能看到变化(不可重复读的问题被解决)
    -- 什么情况会使用可重复读?可重复读的应用场景:
    -- 某员工上午开始做一个数据采样,这个工作非常耗费时间,需要花费一天的时间,
    -- 上午做了一半,到中午他去吃饭了,下午回来,继续往下进行的时候,发现数据对不上了(有人修改了数据),
    -- 他还得重新开始
    -- 这种情况下,他只是要做一个数据采样,唯一需要的就是,在做这件工作的时候,从开始到结束数据不能变化,这时候就需要可重复读(别人修改数据,我也不需要知道)。
    
    • 窗口2
    -- 查询隔离级别
    select @@tx_isolation;
    
    -- 开启事务
    start transaction;
    
    -- 查询账户表
    select * from account;
    
    -- 提交事务
    commit;
    
    -- 查询账户表
    select * from account;
    
  • 幻读的问题和解决

    • 窗口1
    -- 设置隔离级别为repeatable read
    set global transaction isolation level repeatable read;
    
    -- 开启事务 (窗口2也开启事务)
    start transaction;
    
    -- 添加一条记录
    INSERT INTO account VALUES (3,'王五',1500);
    
    -- 查询账户表,本窗口可以查看到id为3的结果
    SELECT * FROM account;
    -- 窗口2查询账户表,查询不到新添加的id为3的记录
    -- 窗口2,添加id为3的一条数据,发现添加失败 (执行完sql,发现左上角一直转圈圈,卡死了)
    
    -- 提交事务   
    COMMIT;
    -- 窗口1提交过事务,窗口2立马就报错
    -- 只有等窗口2提交过事务,再次查询,才会发现id为3的数据已经存在
    -- 之前明明没有,现在却出现了,好像幻觉一样
    
    • 窗口2
    -- 查询隔离级别
    select @@tx_isolation;
    
    -- 开启事务
    start transaction;
    
    -- 查询账户表,查询不到新添加的id为3的记录
    select * from account;
    
    -- 添加id为3的一条数据,发现添加失败。出现了幻读
    INSERT INTO account VALUES (3,'测试',200);
    
    -- 提交事务
    COMMIT;
    
    -- 查询账户表,查询到了新添加的id为3的记录
    select * from account;
    
    • 解决幻读的问题
    /*
    	窗口1
    */
    -- 设置隔离级别为serializable (这个级别会解决所有的问题,但是效率低)
    set global transaction isolation level serializable;
    
    -- 开启事务 
    start transaction;
    
    -- 添加一条记录
    INSERT INTO account VALUES (4,'赵六',1600);
    
    -- 查询账户表,本窗口可以查看到id为4的结果
    SELECT * FROM account;
    
    -- 窗口2查询账户表,发现查询语句无法执行,数据表被锁住(左上角转圈)!只有窗口1提交事务后,才可以继续操作
    
    -- 提交事务
    COMMIT;
    
    -- 窗口1提交过事务,窗口2才会出现查询的数据
    -- 发现有id为4的数据,就不会再插入id为4的数据
    
    
    /*
    	窗口2
    */
    -- 查询隔离级别
    select @@tx_isolation;
    
    -- 开启事务
    start transaction;
    
    -- 查询账户表,发现查询语句无法执行,数据表被锁住!只有窗口1提交事务后,才可以继续操作
    select * from account;
    
    -- 添加id为4的一条数据,发现已经存在了,就不会再添加了!幻读的问题被解决
    INSERT INTO account VALUES (4,'测试',200);
    
    -- 提交事务
    COMMIT;
    
9. 隔离级别总结
隔离级别名称出现脏读出现不可重复读出现幻读数据库默认隔离级别
1read uncommitted读未提交是是是
2read committed读已提交否是是Oracle / SQL Server
3repeatable read可重复读否否是MySQL
4**serializable **串行化否否否

注意:隔离级别从小到大安全性越来越高,但是效率越来越低 , 所以不建议使用READ UNCOMMITTED 和 SERIALIZABLE 隔离级别.

10. 事务的总结
  • 一条或多条 SQL 语句组成一个执行单元,其特点是这个单元要么同时成功要么同时失败。例如转账操作
  • 开启事务:start transaction;
  • 回滚事务:rollback;
  • 提交事务:commit;
  • 事务四大特征
    • 原子性
    • 持久性
    • 隔离性
    • 一致性
  • 事务的隔离级别
    • read uncommitted(读未提交)
    • read committed (读已提交)
    • repeatable read (可重复读)
    • serializable (串行化)
求1到100的和(sql) 用sql语言实现求1到100的和,简单易懂,思路清晰,特别适合初学者的学习 立即下载

相关推荐

QT+SOEM主站协议库实战指南—从编译到嵌入式应用

本文提供了一份QT与SOEM开源EtherCAT主站协议库的集成实战指南。详细介绍了在Windows和Linux平台下编译SOEM库的步骤,并讲解了如何将其无缝集成到QT项目中,构建具备实时监控与控制功能的工业上位机应用。内容涵盖从环境搭建、核心协议开发到嵌入式部署与优化的全流程,为开发高性价比、自主可控的工业控制解决方案提供完整路径。

u6v7w8x的博客 442

C语言期末课程设计:学生成绩管理系统设计(结构体)

结构体学生成绩管理系统

qq_74741286的博客 1861

神州数码防火墙(DCN)NAT SSL-VPN配置

SSL-VPN是基于应用层的VPN,采用SSL协议来实现远程接入的一种新型VPN技术,比L2TP更加灵活的VPN方案。是天然的安全远程接入方案。用户可以通过浏览器的HTTPSs登录VPN的网址。SSL-VPN的认证方式包括LDAP,radius,CA,USB Key等方式。 SSL VPN的主要应用场景是保证远程用户能够在企业外部安全\高效的访问企业内部的网络资源

qq_50966485的博客 4560

MYSQL高级——存储过程(习题)计算两数之和,循环,判断,赋值查询

创建存储过程 create procedure 存储名(in 传入参数 int,in b int,out 输出参数 int) begin -- 开始 set sum = a+b; -- 执行操作 end; -- 结束 -- 调用 call mypro(传入参数1,传入参数2,输出参数(@参数名)); select @参数名; 练习- -计算两个数值 -- 计算两个数的和 create procedure mypro(in a int,in b int,out sum int) begin set sum

weixin_50235904的博客 8742

51单片机+HX711电子秤Proteus仿真保姆级教程(附完整源码与避坑指南)

本文详细介绍了51单片机与HX711电子秤在Proteus仿真中的全流程实战指南,包括电路设计、核心代码实现、典型问题排查及系统集成等关键步骤。特别提供了HX711驱动程序的编写技巧和称重数据处理算法,帮助开发者快速搭建高精度电子秤仿真系统,并避免常见的设计陷阱。

weixin_30752699的博客 416

流媒体学习之路(mediasoup)——simulcast 与 svc 简介(7)

流媒体学习之路(mediasoup)——simulcast 与 svc(7) 文章目录流媒体学习之路(mediasoup)——simulcast 与 svc(7)

qw225967的博客 7343

MySQL中利用存储过程求1-100的数之和

【代码】MySQL中利用存储过程求1-100的数之和。

qq_51980838的博客 4104

Docker容器化部署Ceph集群:从零搭建高可用分布式存储

本文详细介绍了如何使用Docker容器化部署Ceph集群,从零搭建高可用分布式存储系统。通过环境一致性、快速扩容和资源隔离等优势,Docker显著提升了Ceph集群的部署效率和运维便捷性。文章涵盖硬件规划、镜像选择、单节点部署、多节点扩展及性能调优等实战内容,帮助开发者快速掌握分布式存储系统的容器化部署技巧。

weixin_29081101的博客 426

【MySQL】使用存储过程计算1到100的累计和

create procedure kaka(out count int) begin declare v1 int(11) default 1; declare v2 int(11) default 0; while v1 < 100 do set v2 = v2 + v1; set v1 = v1 + 1; end while; set count = v2; end ...

咔咔博客 1万+

手把手教你用SNAP做全极化SAR图像分类

引言 最近在研究极化SAR图像分类方面的内容,发现用SNAP软件可以很方便的实现图像的分类。因为在入门过程中踩了很多坑,浪费了一些时间,为了不让大家踩坑,所以决定写下这篇教程。 前期准备 软件下载 SNAP是欧空局旗下的一个SAR图像处理软件,可以点击下载地址进行下载。 数据下载 数据下载地址:https://mdacorporation.com/sample_data/RS2_OK76385_...

zz651044657的博客 1万+

41、矩形和圆柱形开槽微带贴片天线设计及性能分析

本文提出了一种适用于ISM频段(2.4 - 2.48 GHz)的微带圆形贴片天线,专为生物医学植入和可穿戴设备设计。通过在贴片上蚀刻矩形槽并去除圆柱形槽,实现了小型化与性能优化。采用Rogers 3010基板材料,结合缺陷接地结构(DGS)技术,有效改善了带宽、回波损耗和极化纯度。仿真结果显示,天线在弯曲状态下仍保持良好性能,回波损耗达-13 dB,VSWR为1.6,方向性提升至5.38 dB。与以往设计相比,本次设计在回波损耗和谐振频率稳定性方面表现优异,具备良好的生物相容性和应用潜力。未来将从开槽结构、

github5actions的博客 260

MySQL高级学习笔记-day03

Mysql高级-day03 MySQL高级部分学习笔记,该笔记仅供学习参考。 1.应用优化 前面章节,我们介绍了很多数据库层面的优化措施。但是在实际生产环境中由于数据库本身性能局限,就必须要对前台的应用进行一些优化,来降低数据库的访问。 1.1使用连接池 对于范文数据库来说,简历联结的代价是比较昂贵的,因为我们频繁的创建关闭联结,是比较耗费资源的,我们有必要建立数据库连接池,以提高访问的性能。 1.2减少对MySQL的访问 1.2.1避免对数据进行重复检索 在编写应用代码时,需要能够理清对数据库的访问逻辑。

String小四的博客 598

uniapp(全端兼容) - 超详细接入百度统计功能,给uniapp全平台各端项目引入添加百度统计的插件统计流量详细的步骤教程(提供最新的百度统计源代码,详细的示例代码复制粘贴即可快速搞定!)

uniapp h5 app 使用加入接入百度统计,uniapp百度统计,uniapp详细引入百度统计的教程,uniapp接入百度统计,uniapp如何实现加入百度的统计代码,uniapp怎么植入百度统计详细教程,uniapp如何对接百度统计,uniapp百度统计不生效怎么办,uniapp使用百度统计不生效常见问题,uniapp引入百度统计后没有生效,uniapp h5网页接入百度统计代码后没有统计无效,uniapp最新接入使用百度统计功能,uniapp增加百度统计代码,uniapp app如何接入百度统计,

✅ 精选推荐文章 1万+

尚硅谷MySQL学习笔记

MySQL笔记目录一、MySQL数据库基础篇大纲1. 数据库概述与MySQL安装篇第01章:数据库概述第02章:MySQL环境搭建2. SQL之SELECT使用篇第03章:基本的SELECT语句第04章:运算符第05章:排序与分页第06章:多表查询第07章:单行函数第08章:聚合函数第09章:子查询3. SQL之DDL、DML、DCL使用篇第10章:创建和管理表第11章:数据处理之增删改第12章:MySQL数据类型精讲第13章:约束4. 其它数据库对象篇第14章:视图第15章:存储过程与函数第16章:变量、

IAMLSL的博客 2万+

NTscan扫描工具

ntscan是一个扫描器,可以扫描没有密码和弱密码的共享,先到配置文件中填写猜测的用户名和密码,扫描过程中右侧出现的是扫描日志。

温度PID控制PLC程序

温度PID稳定控制程序,适用于欧姆龙PLC,对其他PLC也具有很大参考价值

尚硅谷MySQL从入门到大牛 | 学习笔记目录以及建议

说明:根据所做笔记。

m0_74795952的博客 3045

IEM模型,地表粗糙度模拟

利用微波反演土壤水分时候,利用IEM模型可以实现对随机地表的模拟

MySQL 03 高级查询(一)

MySQL 03 高级查询(一) 文章目录MySQL 03 高级查询(一)一、学习目标二、调整数据库的表结构三、修改表四、设置主外键约束五、添加约束 (练习)六、数据操纵语言6.1.DML(Data Manipulation Language)6.2.DML语句——插入单条数据6.3.DML语句——插入多条数据6.4.DML语句——将查询结果插入到新表6.5.DML语句——更新数据6.6.DML语句——删除数据6.7.比较DROP、DELETE与TRUNCATE语句七、数据查询语言八、常用函数8.1.字符串

云哥的博客 4321

Mysql高级-day03

Mysql高级-day03 1. 应用优化 前面章节,我们介绍了很多数据库的优化措施。但是在实际生产环境中,由于数据库本身的性能局限,就必须要对前台的应用进行一些优化,来降低数据库的访问压力。 1.1 使用连接池 对于访问数据库来说,建立连接的代价是比较昂贵的,因为我们频繁的创建关闭连接,是比较耗费资源的,我们有必要建立 数据库连接池,以提高访问的性能。 1.2 减少对MySQL的访问 1.2.1 避免对数据进行重复检索 在编写应用代码时,需要能够理清对数据库的访问逻辑。能够一次连接就获取到结果的,就不用两

MARSHCW的博客 375

【Docker笔记03】【MySQL 与 Redis的主从安装】

本系列是根据学习记录笔记。因为没有视频课件,部分内容摘自。本系列仅为自身学习笔记记录使用,记录存在偏差,推荐阅读原视频内容或本文参考笔记。

猫吻鱼的博客 1885

Mysql整理笔记03

Mysql整理笔记03

xlshi1996的博客 122

MySQL自学笔记(零基础到考试6天)

MySQL笔记(零基础到考试6天) 全部SQL源文件链接:https://pan.baidu.com/s/1wc51qkVetSRybFzcIYGBIg 提取码:3wpt 开始 这是关于MySQL的课程学习笔记,写他只是为了复习,因为我知道,我容易忘,既然容易忘,那就写下来,根据自己的思路去写,怎么学的怎么写,可能和大多数教科书不大一样。 但这都不重要,因为,初衷是方便我自己去看!!!!所以,有的地方不一定是特别好,但我认为这是最适合我自己的,因为是自己的笔记。当然,如果您在阅读中发现什么错误,请留言

杨丙寅 1万+

Mysql高级-03

本人面试腾讯,阿里,百度等企业总结下来的面试经历,都是真实的,分享给大家!本人面试腾讯,阿里,百度等企业总结下来的面试经历,都是真实的,分享给大家![外链图片转存中…(img-X4JBdPY3-1725204775948)][外链图片转存中…(img-I6lq5O4c-1725204775949)][外链图片转存中…(img-ceoWCDIP-1725204775949)][外链图片转存中…(img-muXYmwLL-1725204775950)]

2401_86352791的博客 1671

计算机毕业设计java高校教师授课管理系统 高校教师授课信息管理平台 高等院校教师教学管理系统

随着高等教育规模的不断扩大和教学管理精细化需求的提升,传统的教师授课管理模式面临诸多挑战,如课程信息传递滞后、学生选课流程繁琐、教学评价反馈不及时等。为实现教学管理的智能化与高效化,开发一款适配高校场景的教师授课管理系统成为必然。该系统通过整合课程安排、教学资源、师生互动等核心环节,不仅能减轻管理人员的工作负担,还能为师生提供便捷的信息查询与交互渠道,助力高校教学质量的提升。

毕设源码嘉师哥 1177

MySQL高级查询

第一个参数为列名,第二个参数为往上第n行(可选,默认为1),第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL);第一个参数为列名,第二个参数为往下第n行(可选,默认为1),第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL);① rows只和行号有关,当光标指向第2行,则向上1行,向下1行,则相当于1 + 3 + 5 = 9。② 当第一行查询完毕后,游标会自动向下偏移一个单位,指向第二行,则第二行就变更为当前行。– 这是不对的,也是不允许的!

AGI爱好者_数仓&可视化&数据分析工程师_Vae’伯乐‘ 1393

计算机毕设 java 网络授课考勤系统 SSM 框架网络授课服务平台 Java 开发的网络授课考勤全流程管理系统

传统网络授课存在签到繁琐、作业提交分散、成绩统计低效等问题,难以满足师生线上教学需求。为搭建专业化教学平台,开发一款集课程管理、签到打卡、作业提交于一体的网络授课考勤系统,成为提升线上教学质量的关键举措。

毕设源码嘉师哥 910

导航【mysql高级】【java提高】

导航【mysql高级】【java提高】

小心星的博客 2650
上一篇: MySQL进阶-02-授课笔记
下一篇: MySQL高级-04-授课笔记
InLoadwetrust
博客等级 码龄6年 11粉丝 84原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

InLoadwetrust

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值