MySQL学习笔记（五）— 事务控制（TCL语言）、视图、变量、存储过程及函数等-CSDN博客

本文链接：https://blog.csdn.net/weixin_36488653/article/details/121322635

文章目录

1. 事务控制(ACID特性)
2. 视图
3. 变量
4. 存储过程
5. 函数
6. 流程控制

1. 事务控制(ACID特性)

/*
事务：
一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行
事务的ACID（acid）属性：
	1. 原子性（Atomicity）：事务时不可分割的工作单位，事务中的操作要么都发生，要么都不发生；
	2. 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态；
	3. 隔离性（Isolation）：一个事物的执行不能被其他事物干扰，即食物内部多的操作及使用的数据对并发的其他事物是隔离的，互不干扰；
	4. 持久性（Duration）：一个事务一旦提交，则会永久改变数据库的数据；

事务的创建
隐式事务：事务没有明显的开启和结束的标记，如insert、update、delete语句；
显式事务：事务具有明显的开启和结束的标记，前提是必须设置自动提交功能为禁用即：set autocommit=0；

步骤1：开启事务
set autocommit=0;
start transaction; # 可选的
步骤2：编写事务中的sql语句（select、insert、update、delete）
语句1；
语句2；
...
步骤3：结束事务
commit; # 提交事务
rollback; # 回滚事务
*/
# 查看mysql支持的存储引擎
SHOW ENGINES;

DROP TABLE IF EXISTS account;
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(20),
	balance DOUBLE
);

INSERT INTO account(username, balance)
VALUES('张无忌', 1000), ('赵敏', 1000);

# 开启事务
SET autocommit=0;
START TRANSACTION;
# 编写一组事务语句
UPDATE account SET balance=500 WHERE username='张无忌';
UPDATE account SET balance=1500 WHERE username='赵敏';
# 结束事务
COMMIT;

命令行进行事务的隔离级别：

# 终端开启sql
# 查看隔离级别
select @@tx_isolation;
# 将隔离级别设置成最低
set session transaction isolation level read uncommitted;
set autocommit=0;
use test;
select * from account;
set names gbk;
update account set username='join' where id=25;

# 再开一个终端，开启sql
# 将隔离级别设置成最低
set session transaction isolation level read uncommitted;
use test;
set autocommit=0;
set names gbk;
select * from account;
# 会出现脏读、不可重复读和幻读现象

# 终端窗口一
set session transaction isolation level read committed;
# 终端窗口二
set session transaction isolation level read committed;
# 可以避免脏读，其他的仍存在

set session transaction isolation level repetable read;
# 可以避免脏读、不可重复读，但仍存在幻读

set session transaction isolation level serializable;
# 最高隔离级别，可以避免所有的问题
/*
# 在事务中添加保存点，与rollback共同使用
savepoint 节点名；
ROLLBACK TO 节点名；
事务的隔离级别：
	read uncommitted：出现脏读、幻读、不可重复读；
	read committed：出现不可重复读、幻读；（oracal默认级别）
	repetable read：出现幻读；（mysql默认级别）
	serializable：最高隔离级别；
*/

# delete和truncate在事务使用时的区别
# delete：可撤销
SET autocommit=0;
START TRANSACTION;
DELETE FROM account;
ROLLBACK;
# truncate：不支持回滚，不可撤销
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;

2. 视图

/*
含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据
只保存sql逻辑，不保存查询结果
*/
SELECT stuname, majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`=m.`id`
WHERE s.`stuname` LIKE '张%';

# 构建视图
CREATE VIEW v1
AS 
SELECT stuname, majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid`=m.`id`

SELECT * FROM v1 WHERE stuname LIKE '张%';

# 一、创建视图
/*
语法：
create view 试图名
as
语句；
...
*/

CREATE VIEW myv1
AS
SELECT last_name, department_name, job_title
FROM employees e
JOIN departments d 
ON e.`department_id`=d.`department_id`
JOIN jobs j 
ON j.`job_id`=e.`job_id`;

SELECT * FROM myv1 WHERE last_name LIKE '%a%';

# 查询各部门平均工资级别
CREATE VIEW myv2
AS
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id;

SELECT myv2.`ag`, g.`grade_lecvel` FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

# 查询平均工资最低的部门信息
SELECT * FROM muv2 ORDER BY ag LIMIT 1;
# 查询工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT * FROM muv2 ORDER BY ag LIMIT 1;

SELECT d.* m.ag 
FROM myv3 m
JOIN departments d
ON m.`department_id`=d.`department_id`;

# 二、视图的修改
/*
# 方式一
CREATE OR REPLACE VIEW 视图名
as 
语句;
# 方式二
ALTER VIEW 视图名
AS
语句；
*/
# 方式一
CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary), job_id
FROM employees
GROUP BY job_id;
# 方式二
ALTER VIEW myv3
AS
SELECT * FROM employees;

# 三、删除、查看视图
/*
# 删除
drop view 视图名, 视图名, ...
# 查看
desc 视图名;
SHOW CREATE VIEW 视图名；
*/
# 删除
DROP VIEW myv1, myv2, myv3;
# 查看
DESC myv3;
SHOW CREATE VIEW myv3;

# 四、视图的更新
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name, email, salary*12(1+IF NULL(commmission_pct, 0)) 'annual salary'
FROM employees;

SELECT * FROM myv1;
SELECT * FROM employees;
# 1.插入
INSERT INTO myv1 VALUES('张飞', 'zf@qq.com', 1000000)
# 2. 修改
UPDATE myv1 SET last_name='张无忌' WHERE last_name='张飞';
# 3. 删除
DELETE FROM myv1 WHERE last_name='张无忌';
# 原始表的数据也会发生改变，因此需要将视图设置权限；

/*
不允许更新的视图类型：
1. 包含分组函数、distinct、group by、having、union、union all的视图不允许更新；
2. 常量视图不允许更新；
3. SELECT中包含子查询；
4. join；
5. from一个不能更新的视图；
6. where子句的子查询引用了from子句中的表；
*/

3. 变量

/*
系统变量：
	全局变量
	会话变量
自定义变量：
	用户变量
	局部变量

一、系统变量
# 1.全局变量
1. 查看所有的系统变量
SHOW GLOBAL | [SESSION] variables;
2. 查看满足条件的部分系统变量
SHOW GLOBAL | [SESSION] variables LIKE '%char%';
3. 查看指定的某个系统变量的值
select @@global | [session]. 系统变量名；
4. 为某个系统变量赋值
方式一：
set global | [session] 系统变量名=值；
方式二：
set @@global | [session].系统变量名=值；
*/

# 全局变量
# 1. 查看所有的全局变量
SHOW GLOBAL VARIABLES;
# 2. 查看部分全局变量
SHOW GLOBAL VARIABLES LIKE '%char%';
# 3. 查看指定全局变量的值
SELECT @@global.autocommit;
SELECT @@tx_isolation;
# 4. 为某个指定全局变量赋值
SET @@global.auotocommit=0;
/*
2. 会话变量
作用域：仅作用于当前会话
*/
# 1. 查看所有会话变量
SHOW VARIABLES;
SHOW SESSION VARIABLES;
# 2. 查看部分的会话变量
SHOW VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';
# 3. 查看指定的某个会话变量
SELECT @@tx_isolation;
SELECT @@session.tx_isolation;
# 4. 为某个会话变量赋值
SET @@session.tx_isolation='read-uncommitted';
SET SESSION tx_isolation='read-committed';

# 二、自定义变量
/*
声明、赋值、使用
1. 用户变量：针对当前会话、连接有效，同于会话变量的作用域；
#1 声明并初始化
SET @用户变量名=值;
SET @用户变量名:=值;
SELECT @用户变量名:=值;
#2 赋值
方式一：
	SET @用户变量名=值;
	SET @用户变量名:=值;
	SELECT @用户变量名:=值;
方式二：
	SELECT 字段 INTO @用户变量名
	FROM 表；
#3 使用
SELECT @用户变量名

2. 局部变量：仅仅在定义它的begin end中有效，只能在begin end中定义且为第一句话
# 1. 声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;
# 2. 赋值
方式一：
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT @局部变量名:=值;
方式二：
	SELECT 字段 INTO 局部变量名
	FROM 表；
# 3. 使用
SELECT 局部变量名;
*/
# 用户变量
# 声明
SET @name='john';
SET @name=100;
SET @count=1;
# 赋值
SELECT COUNT(*) INTO @count 
FROM employees;
# 查看
SELECT @count;
# 局部变量

4. 存储过程

/*
# 存储过程：一组预先编译好的SQL语句的集合，理解成批处理语句
# 一、创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
	存储过程体（一组合法的SQL语句）
END
注意：
1. 参数列表包含三部分：参数模式、参数名、参数类型
参数模式包含：
IN（该参数可以作为输入，也就是该参数需要调用方传入值）；
OUT（该参数可以作为输出，也就是该参数可以作为返回值）；
INOUT（既可以作为输入，又可以作为输出）
2. 如果存储体只有一句话，BEIGN END可以省略；
3. 存储过程体中每条sql语句必须带分号，结尾可以用DELIMITER重新设置；
 
# 二、调用语法
CALL 存储过程名（实参列表）;
*/
# 1. 空参列表
# 插入到admin表中5条记录
SELECT * FROM admin;
# 设置结束标记
DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
	INSERT INTO admin(username, `password`)
	VALUES('john1', '0000'), ('john2', '0000'), ('john3', '0000');
END $
# 调用
CALL myp1()$
# 2. 创建带in模式的存储过程
CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id=b.boyfriend_id
	WHERE b.name=beautyName
END $
# 调用
SET names gbk$
CALL myp2('柳岩')$

CREATE PROCEDURE myp3(IN username VARCHAR(20), IN PASSWORD VARCHAR(20))
BEGIN
	DECLARE result INT DEFAULT 0; # 声明并初始化
	
	SELECT COUNT(*) INTO result # 赋值
	FROM admin
	WHERE admin.username=username
	AND admin.PASSWORD=PASSWARD; 

	SELECT IF(result>0, '成功', '失败');# 使用
END $
# 调用
CALL myp3('张飞', '8888')$

# 3. 创建带out模式的存储过程
CREATE PROCEDURE myp5(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20))
BEGIN 
	SELECT bo.boyName INTO boyName
	FROM boys bo
	INNER JOIN beauty b ON bo.id=b.boyfriend_id
	WHERE b.name=beautyName;
END$
# 调用
SET @bName$
CALL myp5('小昭', @bName)$
SELECT @bName$

CREATE PROCEDURE myp6(IN beautyName VARCHAR(20), OUT boyName VARCHAR(20), OUT userCP INT)
BEGIN 
	SELECT bo.boyName, bo.userCP INTO boyName, userCP
	FROM boys bo
	INNER JOIN beauty b ON bo.id=b.boyfriend_id
	WHERE b.name=beautyName;
END$
# 调用
CALL myp5('小昭', @bName, @usercp)$
SELECT @bName, @usercp$

# 4. 创建INOUT模式的存储过程
CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END$
# 调用
SET @m=10$
SET @n=20$
CALL myp8(@m, @n)$
SELECT @m, @n$

# 二、删除存储过程
/*
DROP PROCEDURE 存储过程名
*/
# 仅支持删除一个存储过程
DROP PROCEDURE p1;

# 三、查看存储过程
SHOW CREATE PROCEDURE myp2;

5. 函数

/*
存储过程：可以无返回及多个返回，适合批量增删改
函数：只能必须只有一个返回，适合做处理数据后返回一个结果
# 一、创建语法
CREATE FUNCTION 函数名（参数列表） RETURNS 返回类型
BEGIN
	函数体
END
注意：
1. 参数列表包含：参数名，参数类型
2. 函数体：肯定会有return语句，如果没有会报错，但不建议
3. 函数体中只有一句话时可以省略BEGIN END
4. 使用delimiter语句设置结束标记

# 二、调用语法
SELECT 函数名（参数列表）
*/
# 1. 无参数有返回
delimiter $
CREATE FUNCTION myf1() RETURNS INT
BEGIN
	DECLARE c INT DEFAULT 0;
	SELECT COUNT(*) INTO c
	FROM employees;
	RETURN c;
END $
SELECT myf1()$
# 2. 有参数有返回
CREATE FUNCTION myf2(empName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	SET @sal=0; # 定义用户变量
	SELECT salary INTO @sal
	FROM employees
	WHERE last_name=empName;
	
	RETURN @sal;
END$

SELECT myf2('kochhar')$
# 3. 根据部门名返回该部门的平均工资
CREATE FUNCTION myf3(deptName VARCHAR(20)) RETURNS DOUBLE
BEGIN
	DECLARE sal DOUBLE;
	SELECT AVG(salary) INTO sal
	FROM employees e
	JOIN departments d ON e.department_id=d.department_id
	WHERE d.departmemt_name=deptName;
	
	RETURN sal;
END$

SELECT myf2('IT')$

# 三、查看函数
DELIMITER ;
SHOW CREATE FUNCTION myf3;

# 四、删除函数
DROP FUNCTION myf3;

6. 流程控制

/*
顺序结构
分支结构
循环结构

# 一、分支结构
1. if函数：实现简单的双分支
SELECT IF（表达式1，表达式2，表达式3）：若1成立返回2，否则返回3
if结构
if 条件1 then 语句1;
elseif 条件2 then 语句2；
...
end if;
应用在begin end中
2. case结构：
情况1：类似于switch，一般用于等值判断
CASE 变量|表达式|字段
WHEN 要判断的值 THEN 返回值1或语句1;
WHEN 要判断的值 THEN 返回值2或语句2;
WHEN 要判断的值 THEN 返回值3或语句3;
...
ELSE 返回值n或语句n;
END CASE;
情况2：类似于java中的多重IF语句，一般用于实现区间判断
CASE
WHEN 要判断的条件1 THEN 返回值1或语句1;
WHEN 要判断的条件2 THEN 返回值2或语句2;
WHEN 要判断的条件3 THEN 返回值3或语句3;
....
ELSE 要返回的值n或语句n;
END CASE;

*/
# case结构
# 创建存储过程，根据传入的成绩，来显示等级，如传入的成绩90-100显示A，80-90显示B，60-80显示C，否则D
CREATE PROCEDURE test_case(IN score INT)
BEGIN
	CASE
	WHEN score>=90 AND score<=100 THEN SELECT 'A';
	WHEN score>=80 THEN SELECT 'B';
	WHEN score>=60 THEN SELECT 'C';
	ELSE SELECT 'D';
	END CASE;
END$

CALL test_case(95)$
# if结构
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
	IF score>=90 AND score<=100 THEN RETURN 'A';
	ELSEIF score>=80 THEN RETURN 'B';
	ELSEIF score>=60 THEN RETURN 'C';
	ELSE RETURN 'D';
	END IF
END $

SELECT test_if(86)$

# 二、循环结构
/*
分类：while、loop、repeat
循环控制：iterate类似于continue，leave类似于break
# 1.while
[标签]: while 循环条件 do
	循环体;
end while [标签];
# 2. loop
[标签:] loop
	循环体;
end loop [标签];
# 3. repeat
[标签:] repeat
	循环体;
until 结束循环的条件
end repeat [标签];
*/
# 批量插入
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	WHILE i<=insertCount DO
		INSERT INTO admin(username, `passaord`) VALUES(CONCAT('Rose', i), '666');
		SET i=i+1;
	END WHILE;
END$

CALL pro_while1(100)$
# 添加循环控制leave
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		INSERT INTO admin(username, `passaord`) VALUES(CONCAT('Rose', i), '666');
		# 循环控制语句
		IF i>=20 THEN LEAVE a;
		END IF;
		SET i=i+1;
	END WHILE a;
END$

# 2. 添加iterate语句
CREATE PROCEDURE pro_while1(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		SET i=i+1;
		# 循环控制语句
		IF MOD(i, 2)!=0 THEN ITERATE a;
		END IF;
		INSERT INTO admin(username, `passaord`) VALUES(CONCAT('Rose', i), '666');
	END WHILE a;
END$