黑马MySQL数据库从入门到精通-基础篇 2

游标

1
游标(CURSOR)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和 CLOSE，其语法分别如下。

用法

1
2
3
4
5
6
7
8
9
10
11
声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标
OPEN 游标名称;

获取游标记录
FETCH 游标名称 INTO 变量[,变量];

关闭游标
CLOSE 游标名称

案例1

image-20240111002240398

image-20240111002411661

image-20240111003237427

image-20240111003359274

1
执行创建存储过程命令报错，声明变量和声明游标的顺序有要求

image-20240111003504209

image-20240111003723995 
1
上面的报错是因为执行存储过程后，当游标里的数据遍历完成后，由于是死循环当无数据时也执行，导致报错

异常处理程序

image-20240111004654529

1
上图是对前面的案例的改进

案例2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
创建存储过程，里面使用游标，对每一行进行数据修改

create procedure a()
begin
	declare cno int;
	declare sno int;	
	declare my_cursor cursor for select cno,sno from t;
	open my_cursor;
	fetch mycursor into cno,sno;
	while fetch_status = 0
	do
		update t set score = score + 5 where sno = t.sno and cno = t.cno;
		fetch mycursor into cno,sno;
	end while;
end;

案例3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
创建存储过程，里面使用游标，对score小于60的进行删除

create procedure d()
begin
declare score int;
declare my_cursor cursor for select score from t;
open my_cursor;
fetch my_cursor into score;
where fetch_status = 0
do
	if score < 60 then
		delete t where t.score = score;
	end if;
	fetch my_cursor into score;
end while;
end;

案例4 异常处理程序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DELIMITER //
CREATE PROCEDURE my_test8()
BEGIN
DECLARE result VARCHAR(100) DEFAULT '';-- 定义一个result 来存储商品名称的拼接
DECLARE product_name VARCHAR(10);-- 定义一个变量来接收每次游标的商品名称
DECLARE done INT DEFAULT FALSE; -- 定义done变量 默认值为false
-- 查询所有的商品名称放入游标中 CURSOR 表示该变量为游标类型
DECLARE curl CURSOR FOR SELECT p_name FROM xmcc_product;
-- 游标遍历结束 会出现not found 设置done的值为true
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
OPEN curl;-- 打开游标
    WHILE(NOT done) DO
    FETCH curl INTO product_name; -- 弹出游标的一条数据赋值给product_name
    SET result:=CONCAT(result,',',product_name);-- 拼接字符串
    END WHILE;
CLOSE curl;-- 关闭游标 就像java中需要关闭resultset一样
SELECT result;-- 打印出结果
END;//

#调用
CALL my_test8();

案例5 带输入参数 in

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create procedure sp1(in p int)
comment 'insert into a int value'
begin
/* 定义一个整形变量 */
declare v1 int;

/* 将输入参数的值赋给变量 */
set v1 = p;

/* 执行插入操作 */
insert into test(id) values(v1);
end

/* 调用这个存储过程 */
call sp1(1)//

案例6 带输出参数 out

1
2
3
4
5
6
7
8
9
10
create procedure sp2(out p int)
begin
select max(id) into p from test;
end

/* 调用该存储过程，注意：输出参数必须是一个带@符号的变量 */
call sp2(@pv)
/* 查询刚刚在存储过程中使用到的变量 */

select @pv

案例7 带输入和输出参数 in out

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create procedure sp3(in p1 int , out p2 int)
begin
if p1 = 1 then
/* 用@符号加变量名的方式定义一个变量，与declare类似 */
set @v = 10;
else
set @v = 20;
end if;

/* 语句体内可以执行多条sql，但必须以分号分隔 */
insert into test(id) values(@v);
select max(id) into p2 from test;
end

/* 调用该存储过程，注意：输入参数是一个值，而输出参数则必须是一个带@符号的变量 */
call sp3(1,@ret)//

select @ret//

案例8 inout

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
drop procedure if exists sp4 //
create procedure sp4(inout p4 int)
begin
if p4 = 4 then
set @pg = 400;
else
set @pg = 500;
end if; 

select @pg;

end//

call sp4(@pp)//

/* 这里需要先设置一个已赋值的变量，然后再作为参数传入 */
set @pp = 4//
call sp4(@pp)//

案例9 事务 异常处理程序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
#存储过程异常处理
DELIMITER//
CREATE PROCEDURE my_test7()
BEGIN
DECLARE i INT DEFAULT 0;
#发生SQL异常时，程序继续，并设置i=-1；
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SET i:=-1;
START TRANSACTION;#开启事务
UPDATE xmcc_product SET p_name='苹果' WHERE p_id=10000;
INSERT INTO xmcc_product VALUES(10000,'主键冲突',10,100); 
IF i=-1 THEN
SELECT '出现异常，事务回滚';
ROLLBACK; #回滚
ELSE
COMMIT; #提交
END IF;
END//
DELIMITER ;


#调用 
CALL my_test7();
#查看结果
SELECT * FROM xmcc_product

案例10 复杂案例 异常处理 回滚

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
#编写取消订单的存储过程
DELIMITER $$
#输入参数为订单order_id 输出参数为result 1 代表存储过程成功 -1代表存储过程出现异常
CREATE PROCEDURE cancel_order(IN order_id VARCHAR(30),OUT result INT)
BEGIN 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result:=-1;#如果出现异常设置返回值为-1

START TRANSACTION;#开启事务

SET result:=1;#设置初始值 这里设置为1
UPDATE xmcc_order SET o_statu=1 WHERE o_id=order_id; #修改订单状态

BEGIN 
    DECLARE done INT DEFAULT TRUE;#定义变量done用来判断
    DECLARE product_id VARCHAR(30); #定义变量 来接收游标的商品id
    DECLARE quantity_1 INT; #定义变量来接收游标的商品数量
    
    #定义游标 存储根据订单id查询到订单项中的商品id与数量
    DECLARE cur CURSOR FOR SELECT p_id,quantity FROM xmcc_orderdetail WHERE o_id=order_id;
    #当游标循环结束 设置done的值为false
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done:=FALSE;
    OPEN cur;#打开游标
    
    WHILE done DO #循环
        FETCH cur INTO product_id,quantity_1;#将游标的值设置到变量中
        IF done THEN #判断 不然会多修改一次
        	UPDATE xmcc_product SET p_stock=p_stock+quantity_1 WHERE p_id=product_id;
        END IF;
    END WHILE;
    CLOSE cur;
END ;
IF result=1 THEN #没有出现异常就提交
	COMMIT;
ELSE 
	ROLLBACK;
END IF;
END ; $$


#测试
CALL cancel_order('20001',@result);
#查看结果
SELECT @result

案例

参考

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
DELIMITER //
#传入参数category_id 分类id
CREATE PROCEDURE find_category_id(IN category_id INT)
BEGIN
DROP TABLE IF EXISTS tmp_id;
#创建临时表 用来存储所有的id 临时表在链接结束会自动删除
CREATE TEMPORARY TABLE  tmp_id(id INT);
#清空该表
TRUNCATE TABLE tmp_id;
#临时表 用来存储所有的商品信息;
#根据分类查询商品的时候，商品展示列表只需要这几个字段就可以了，详情的时候才是所有字段 
DROP TABLE IF EXISTS tmp_product;
CREATE TEMPORARY TABLE  tmp_product(SELECT id,NAME,subtitle,main_image,price FROM xmcc_product WHERE 1=2);
TRUNCATE TABLE tmp_id;
#调用另外的存储过程
CALL recursive_find_id(category_id);
  BEGIN
  #设置循环条件
   DECLARE done INT DEFAULT '0';
   #设置cid来接收游标中弹出的id
   DECLARE cid INT DEFAULT '0';
   #递归过程将所有的id放在临时表中 现在查出放在游标ids中
   DECLARE ids CURSOR FOR SELECT id FROM tmp_id;
   #当游标遍历结束 done=1
   DECLARE CONTINUE HANDLER  FOR NOT FOUND SET done=1;
   #打开游标
   OPEN ids;
   #弹出游标一个值赋给cid
   FETCH ids INTO cid;
   WHILE (done=0) DO
   #根据分类查找到商品 ，插入数据到商品临时表中
   INSERT INTO tmp_product  SELECT id,NAME,subtitle,main_image,price FROM xmcc_product xp WHERE xp.category_id=cid and xp.status=1;
   FETCH ids INTO cid;
   END WHILE;
  END;
  #返回查询到的商品结果集
SELECT * FROM tmp_product;
END ;//
 
 
DELIMITER //
CREATE PROCEDURE recursive_find_id(IN category_id INT)
BEGIN 
#定义id接收每次查询到的分类id
DECLARE cid INT;
#定义done为0TRUE继续循环
DECLARE done INT DEFAULT TRUE;
#定义ids游标来获得parent_id为传入的id的集合
DECLARE ids CURSOR FOR SELECT id FROM xmcc_category WHERE parent_id=category_id;
#游标循环结束 出现not found  done=1循环结束
DECLARE CONTINUE HANDLER  FOR NOT FOUND SET done=FALSE;
#mysql 递归的时候 需要设置深度
SET @@max_sp_recursion_depth = 10;
#将id插入临时表中
INSERT INTO tmp_id VALUES(category_id);
OPEN ids;#打开游标
#将查询到的游标结果 弹出赋值给id 每次弹出一个
FETCH ids INTO cid;
WHILE done DO
#递归调用当前存储过程
CALL recursive_find_id(cid);
FETCH ids INTO cid;
END WHILE;
CLOSE ids;
END;//

案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
DELIMITER //
#传入参数category_id 分类id
CREATE PROCEDURE find_category_id(IN category_id INT)
BEGIN
DROP TABLE IF EXISTS tmp_id;
#创建临时表 用来存储所有的id 临时表在链接结束会自动删除
CREATE TEMPORARY TABLE  tmp_id(id INT);
#清空该表
TRUNCATE TABLE tmp_id;
#临时表 用来存储所有的商品信息;
#根据分类查询商品的时候，商品展示列表只需要这几个字段就可以了，详情的时候才是所有字段 
DROP TABLE IF EXISTS tmp_product;
CREATE TEMPORARY TABLE  tmp_product(SELECT id,NAME,subtitle,main_image,price FROM xmcc_product WHERE 1=2);
TRUNCATE TABLE tmp_id;
#调用另外的存储过程
CALL recursive_find_id(category_id);
  BEGIN
  #设置循环条件
   DECLARE done INT DEFAULT '0';
   #设置cid来接收游标中弹出的id
   DECLARE cid INT DEFAULT '0';
   #递归过程将所有的id放在临时表中 现在查出放在游标ids中
   DECLARE ids CURSOR FOR SELECT id FROM tmp_id;
   #当游标遍历结束 done=1
   DECLARE CONTINUE HANDLER  FOR NOT FOUND SET done=1;
   #打开游标
   OPEN ids;
   #弹出游标一个值赋给cid
   FETCH ids INTO cid;
   WHILE (done=0) DO
   #根据分类查找到商品 ，插入数据到商品临时表中
   INSERT INTO tmp_product  SELECT id,NAME,subtitle,main_image,price FROM xmcc_product xp WHERE xp.category_id=cid and xp.status=1;
   FETCH ids INTO cid;
   END WHILE;
  END;
  #返回查询到的商品结果集
SELECT * FROM tmp_product;
END ;//
 
 
DELIMITER //
CREATE PROCEDURE recursive_find_id(IN category_id INT)
BEGIN 
#定义id接收每次查询到的分类id
DECLARE cid INT;
#定义done为0TRUE继续循环
DECLARE done INT DEFAULT TRUE;
#定义ids游标来获得parent_id为传入的id的集合
DECLARE ids CURSOR FOR SELECT id FROM xmcc_category WHERE parent_id=category_id;
#游标循环结束 出现not found  done=1循环结束
DECLARE CONTINUE HANDLER  FOR NOT FOUND SET done=FALSE;
#mysql 递归的时候 需要设置深度
SET @@max_sp_recursion_depth = 10;
#将id插入临时表中
INSERT INTO tmp_id VALUES(category_id);
OPEN ids;#打开游标
#将查询到的游标结果 弹出赋值给id 每次弹出一个
FETCH ids INTO cid;
WHILE done DO
#递归调用当前存储过程
CALL recursive_find_id(cid);
FETCH ids INTO cid;
END WHILE;
CLOSE ids;
END;//

存储过程

参考

参考

介绍

1
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库SQL 语言层面的代码封装与重用。

特点

1
2
3
4
封装，复用，更加安全。
可以接收参数，也可以返回数据
减少网络交互，效率提升
可以实现复杂的操作，比如包含逻辑语句while if等

基本语法

创建

1
2
3
4
create procedure pro_name()
begin
x
end;
变量
1
2
3
声明局部变量：要在存储过程中声明一个变量，可以使用DECLARE语句

DECLARE variable_name datatype[(size)]  DEFAULT default_value;
1
2
3
4
5
赋值：要为变量分配一个值，可以使用SET语句，或者使用SELECT INTO语句将查询的结果分配给一个变量
DECLARE total_count INT DEFAULT 0;
SET total_count := 10; # SET total_count= 10;
或者：
SELECT COUNT(*) INTO total_count  FROM products
1
2
3
4
5
6
7
变量范围(作用域)

一个变量有自己的范围(作用域)，它用来定义它的生命周期。 如果在存储过程中声明一个变量，那么当达到存储过程的END语句时，它将超出范围，因此在其它代码块中无法访问。

如果您在BEGIN END块内声明一个变量，那么如果达到END，它将超出范围。 可以在不同的作用域中声明具有相同名称的两个或多个变量，因为变量仅在自己的作用域中有效。 但是，在不同范围内声明具有相同名称的变量不是很好的编程习惯。

以@符号开头的变量是会话变量。直到会话结束前它可用和可访问。
参数
1
2
3
4
5
在MySQL中，参数有三种模式：IN，OUT或INOUT。

IN - 是默认模式。(可写可不写) 在存储过程中定义IN参数时，调用程序必须将参数传递给存储过程。 另外，IN参数的值被保护。这意味着即使在存储过程中更改了IN参数的值，在存储过程结束后仍保留其原始值。换句话说，存储过程只使用IN参数的副本。
OUT - 可以在存储过程中更改OUT参数的值，并将其更改后新值传递回调用程序。请注意，存储过程在启动时无法访问OUT参数的初始值。
INOUT - INOUT参数是IN和OUT参数的组合。这意味着调用程序可以传递参数，并且存储过程可以修改INOUT参数并将新值传递回调用程序。

delimiter

1
mysql中的默认的语句结束符为分号；但是例如我们需要在命令行输入较复杂的语句时(如存储过程，存储函数等)，可能包含多行语句或者语句中包含分号，那么mysql在遇到第一个分号时就会执行。delimiter就是用于修改语句结束符，使得在正确编写完sql语句后再执行
1
2
3
4
5
6
7
8
9
10
DELIMITER $$ 
DROP TRIGGER IF EXISTS `updateegopriceondelete`$$ 
CREATE 
    TRIGGER `updateegopriceondelete` AFTER  DELETE ON  `customerinfo` 
    FOR EACH ROW BEGIN 
DELETE FROM egoprice  WHERE customerId=OLD.customerId; 
    END$$ 
DELIMITER ; 

其中DELIMITER 定好结束符为"$$", 然后最后又定义为";"
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> delimiter // 
mysql> CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT) 
mysql>     RETURNS varchar(255) 
mysql> BEGIN 
mysql> IF ISNULL(S) THEN 
mysql>     RETURN ''; 
mysql> ELSEIF N<15 THEN 
mysql>     RETURN LEFT(S, N); 
mysql> ELSE 
mysql>     IF CHAR_LENGTH(S) <=N THEN 
mysql>    RETURN S; 
mysql>     ELSE 
mysql>    RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5)); 
mysql>     END IF; 
mysql> END IF; 
mysql> END;// 

这样只有当//出现之后，mysql解释器才会执行这段语句 
1
2
3
4
5
6
7
8
delimiter //
create procedure val_session()
begin
    # 查看会话变量
    show session variables;
end //

call val_session() //

调用

1
call pro_name();

查看

1
2
3
SELECT * FROM INFORMATON_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='xxx'; -查询指定数库的存储过程及状态信息

SHOW CREATE PROCEDURE 存储过程名称;- 查询某个存储过程的定义

删除

1
DROP PROCEDURE [IF EXIST5] 存储过程名称;

注意

1
2
注意: 在命令行中，执行创建存储过程的SQL时，
需要通过关键字delimiter指定SQL语句的结束符

image-20240110214023091

image-20240110214100926

image-20240110214120773

存储过程异常处理handler

1
2
3
4
5
6
7
01）当sql出现异常的时候跳出存储过程并设置值为xxx
    Declare exit handler for sqlException set …..

02）当sql出现异常的时候继续存储过程并设置值为xxx
    Declare continue handler for sqlException set …..

-- continue继续  exit 退出  HANDLER执行处理器    SQLEXCEPTION:sql异常
1
2
3
4
5
6
7
8
9
10
11
12
13
14
注意：异常情况可以写异常错误码、异常别名或SQLSTATE码。

handler操作：
CONTINUE： 继续
EXIT： 退出
UNDO： 撤销

异常情况列表：
mysql_error_code
SQLSTATE [VALUE] sqlstate_value
condition_name
SQLWARNING
NOT FOUND
SQLEXCEPTION
1
2
3
4
5
6
7
8
handler_action
	CONTINUE: 继续执行当前程序
	EXIT: 终止执行当前程序
condition_value
    SQLSTATE sqlstate_value: 状态码，如02000
    SQLWARNING: 所有以01开头的SQLSTATE代码的简写
    NOT FOUND:所有以02开头的SQLSTATE代码的简写
    SQLEXCEPTION: 所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

Prepare、execute、deallocate

语法

1
2
3
4
5
6
7
PREPARE stmt from '你的sql语句'; // PREPARE msql FROM @MyQuery; 变量存放sql
EXECUTE stmt (如果sql有参数的话, USING xxx,xxx); // 这里USING的只能是会话变量
DEALLOCATE PREPARE stmt;

预定义好sql.
执行预定义的sql
释放掉数据库连接
1
2
3
PREPARE语句准备好一条SQL语句，并分配给这条SQL语句一个名字供之后调用。准备好的SQL语句通过EXECUTE命令执行，通过DEALLOCATE PREPARE命令释放掉。

准备好的SQL语句名字可以是字符串，也可以是用户指定的包含SQL文本的变量。PREPARE中的SQL文本必须代表一条单独的SQL语句而不能是多条SQL语句。在SQL语句中，? 字符用来作为后面执行查询使用的一个参数。
1
使用预处理语句时  要使用的是  【用户变量】(使用SET @xxx = xxxx 方式声明 )  不同于局部变量 （DECLARE 定义的变量）。

优点

1
2
他还可以在存储过程中动态的拼接表名，字段名，来达到动态查询的效果
sql语句中还可以用?来代表参数，这样可以有效的防止sql注入

sql注入

疑惑解答

1
2
3
疑惑解答：
MySQL的解析器要求SQL语句中的标识符（表名、字段名）必须在编译时确定。
普通SELECT/UPDATE等语句在存储过程中是静态SQL，其结构（包括标识符）必须是固定字符串。

案例

1
2
3
4
5
6
7
8
9
10
delimiter //
create procedure myTest()
begin
set @_sql = 'select ? + ?';
set @a = 5;
set @b = 6;
PREPARE stmt from @_sql; // 预定义sql
EXECUTE stmt USING @a,@b;// 传入两个会话变量来填充sql中的 ?
DEALLOCATE PREPARE stmt; // 释放连接
end //
1
2
3
4
5
6
7
8
9
10
delimiter //
create procedure myTest(in columnName varchar(32)) // 传入一个字符串
BEGIN
drop table if exists tmpTable; // 如果临时表存在先删除掉
set @_sql = concat('create temporary table if not exists tmpTable( ', columnName, ' varchar(32), id int(11), _name varchar(32));'); // 创建临时表的语法，我们把传入的参数拼接进来
PREPARE stmt from @_sql;    
EXECUTE stmt;   执行
DEALLOCATE PREPARE stmt;
desc tmpTable;
end //
1
2
3
4
5
6
# 第一种使用方式： 使用USING传入参数


# 第二种使用方式： 
SET @select_test = CONCAT('SELECT * FROM ', @table_name);
PREPARE pr2 FROM @select_test;
1
2
3
4
5
6
7
8
9
10
11
SET @TargetTableName = CONCAT('xxxxxxx','_',DATE_FORMAT(NOW(),'%Y')); --yourtablename_2022你的分表格式，前缀加日期

SET @c_ExecSql = CONCAT('SELECT AVG(AvgDust) INTO @c_AvgDust FROM  ', @TargetTableName);

SET @c_ExecSqlFinally = CONCAT(@c_ExecSql,'WHERE id = @id AND    StartTime >=@StartTime AND EndTime <= @EndTime'); # 最终要执行的SQL语句

PREPARE pr1 FROM @c_ExecSqlFinally;
EXECUTE  pr1; # 最终执行语句
DEALLOCATE PREPARE pr1 ; # 释放资源

# 说明： 最终要执行的语句要看你自己的业务了我这里只是计算一个均值，直接使用了CONCAT函数把语句依次拼接完整。

存储函数

1
2
3
存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。

是一种允许用户扩展MySQL功能的机制。用户可以在SQL查询中直接调用这些函数，实现特定的业务逻辑。

语法

1
2
3
4
5
6
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS 返回值类型
BEGIN
	--SQL语句
	RETURN...;
END;

与存储过程的区别

1
2
3
4
5
存储函数只能返回一个值，而存储过程可以返回零个或多个值
存储函数可以作为查询语句的一部分来调用，而存储过程则一般需要通过CALL语句来调用。
存储函数只能有输入参数（且参数名不能带IN关键字），而存储过程可以有输入、输出或输入输出参数。

存储过程的功能更强大，可以实现复杂的业务逻辑，包括数据修改操作（如INSERT、UPDATE、DELETE），而存储函数则主要用于计算和返回一个值，对数据库表的直接操作较少。

image-20240111005826523

image-20240111010116757

1
报错是因为版本问题

image-20240111010215465

image-20240111010240659

触发器

参考

介绍

1
2
3
4
5
6
触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

行级触发器：影响多少行就触发多少次
语句级触发器：不管影响多少行数据，只触发一次

image-20240111012402634

语法

image-20240111012950606

1
2
3
4
5
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON tbl_name FOR EACH ROW --行级触发器
BEGIN
	trigger_stmt;
END;
1
SHOW TRIGGERS;  --当前数据库触发器
1
DROP TRIGGER.[schema_name.]trigger_name;

insert类型

insert插入之后在其它表插入相关信息

image-20240111013509019

image-20240111014019456

image-20240111014032948 
1
2
3
4
5
6
7
DELIMITER //
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, '账户创建成功')//
DELIMITER ;
1
AFTER INSERT 特别适合这种状态变更的关联写入操作。比如开户、暂停、注销等各类状态变更。

insert插入之前检查数据是否符合规范

1
2
3
4
5
6
7
8
9
10
DELIMITER //
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
IF NEW.sales_amount>10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "你输入的销售总额超过 10000 元。";
END IF//
DELIMITER ;

update类型

before update

1
BEFORE UPDATE触发器与BEFORE INSERT 触发器非常类似，我们可以使用BEFORE UPDATE 触发器在更新数据之前，先做一次业务逻辑检测，避免发生误操作。
1
2
3
4
5
6
7
8
9
10
DELIMITER //
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'VIP 级别客户不能降级为普通级别客户';
END IF //
DELIMITER ;

after update

1
AFTER UPDATE 多用于 log 记录，在管理系统多操作者使用的环境中，管理员需要设置操作 log 记录，以便在出问题时，可以查看操作者对表编辑的操作，可追根溯源。
image-20240111014714830

image-20240111014827947

1
下面的就是前面说的行级触发器问题了，影响多少行就触发多少次

image-20240111015039380

image-20240111015108795

1
2
3
4
5
6
7
8
9
10
触发器将监测用户 ID 、更新前的销售总额、更新后的销售总额、操作者 ID、修改时间等信息，作为 log 存入 audit_log 表中。

使用以下命令建立这个 log 记录触发器：
DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;

delete类型

after delete

image-20240111015757656

image-20240111015858232

1
2
3
4
5
6
7
8
9
10
AFTER DELETE触发器的另一个用途是在删除主表中的数据后，与这个主表关联的数据，一起自动删除。

我们来看一下这个触发器如何创建：
DELIMITER //
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
Delete from customers where customer_id=OLD.customer_id;//
DELIMITER ;

before delete

1
2
这些类型的触发器通常用于在不同的相关表上强制执行参照完整性。
BEFORE DELETE 的应用场景通常是确保有关联的数据不被错误的误删除掉。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
例如：sales 表通过customer_id 与customers表相关联。如果操作者删除了customers 表中的一条数据，那么 sales 表中某些数据就失去了关联线索。

为了避免这种情况的发生，我们需要创建一个 BEFORE DELETE触发器，防止记录被误删除。

DELIMITER //
CREATE TRIGGER validate_related_records
BEFORE DELETE
ON customers
FOR EACH ROW
IF OLD.customer_id in (select customer_id from sales) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '这位客户有相关联的销售记录，不能删除。';
END IF//
DELIMITER ;
1
不过有时候，我们需要删除主数据后，再让系统自动帮我们删除与之相关联的其他所有数据。这时，我们就要用到 AFTER DELETE 这个触发器了。

变量

系统变量

会话变量

1
会话变量是由系统提供的，只在当前会话（连接）中有效。
查看
1
2
3
4
5
6
7
8
# 查看所有会话变量
show session variables;

# 查看指定的会话变量
select @@session.val_name;

# 修改指定的会话变量
set @@session.val_name = 0;
设置
1
2
SET [SESSION|GLOBAL] 系统变量名=值;
SET @@[SESSION|GLOBAL].系统变量名=值;
1
2
3
注意:
如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。
mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。

全局变量

1
2
3
全局变量由系统提供，整个MySQL服务器内有效。

语法： @@global.val_name
1
2
3
4
5
# 查看全局变量中变量名有char的记录
show global variables like '%char%' 

# 查看全局变量character_set_client的值
select @@global.character_set_client

用户自定义变量

1
用户变量不用提前声明，使用即为声明。其作用域为当前连接(会话)。
赋值
1
2
3
4
5
6
7
SET @var_name=expr[,@var_name=expr] ...;

SET @var_name:=expr[,@var_name:= expr] ...;

SELECT @var_name:=expr[,@var_name:= expr]...;

SELECT 字段名 INTO @var_name FROM 表名;

image-20240110220247083

image-20240110220607263

1
2
mysql中没有==比较运算符，赋值和比较运算符都是=
这里建议用:=
使用
1
select @var_name;

image-20240110220318820

注意
1
2
注意:
用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。
案例
1
2
3
4
5
6
7
8
9
10
11
delimiter //
create procedure val_in(in val_name varchar(32))
begin
     # 使用用户变量出参(为用户变量赋参数值)
     set @val_name1 = val_name;
end //
 
# 调用函数
 call val_in('DK') //
# 查询该用户变量
select @val_name1 //
1
2
3
4
5
6
7
8
9
10
11
12
13
delimiter //
# 创建一个入参和出参的存储过程
create procedure val_out(in val_id int,out val_name varchar(32))
begin
    # 传入参数val_id查询员工返回name值(查询出的name值用出参接收并返回)
    select name into val_name from employee where id = val_id;
end //

# 调用函数传入参数并声明传入一个用户变量
call val_out(1, @n) //

# 查询用户变量
select @n //
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
delimiter //
 create procedure val_inout(in val_name varchar(32), inout val_age int)
 begin
     # 声明一个a变量
     declare a int;
     # 将传入的参数赋值给a变量
     set a = val_age;
     # 通过name查询age并返回val_age
     select age into val_age from employee where name = val_name;
    # 将传入的a与-和查询age结果字符串做拼接并查询出来(concat——拼接字符串)
    select concat(a, '-', val_age);
end //

# 声明一个用户变量并赋予参数为40
set @ages = '40' //
# 调用函数并传入参数值
call val_inout('Ziph', @ages) //
# 执行结果
# 40-18

局部变量

1
局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量，局部变量的范围是在其内声明的BEGIN...END块
声明
1
2
3
DECLARE 变量名 变量类型[DEFAULT ...];

变量类型就是数据库字段类型:INT、BIGINT、CHAR、VARCHAR、DATE、TIME等
赋值
1
2
3
SET 变量名=值
SET 变量名:=值
SELECT 字段名 INTO 变量名 FROM 表名 ...;

image-20240110221513243

IF条件判断

语法

1
2
3
4
5
6
7
IF 条件1 THEN
...
ELSEIF 条件2 THEN  --可选
...
ELSE  --可选
...
END IF;

image-20240110222047760

参数

image-20240110222312861

用法

1
2
3
4
CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
-- SQL语句
END;

image-20240110222949445

image-20240110223357171

case

语法一

1
2
3
4
5
CASE case_value
WHEN when_value1 THEN statement_list1
[WHEN when_value2 THEN statement_list2]...
[ELSE statement_list]
END CASE;

语法二

1
2
3
4
5
CASE
WHEN search_condition1 THEN statement_list1
[WHEN search_condition2 THEN statement_lis选2]...
[ELSE statement_list]
END CASE;

image-20240110224333069

循环

1
leave和iterate与break和continue类似

while

1
2
3
[别名] WHILE 条件 DO
    循环语句
END WHILE [别名]

image-20240110224700400

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
delimiter //
create procedure s_while()
begin
    declare i int default 1;
    declare str varchar(256) default '1';
    # 开始while循环
    num:
    # 指定while循环结束条件
    while i < 10 do
        set i = i + 1;
        set str = concat(str, '+', i);
    # while循环结束
    end while num;
    # 查询while循环拼接字符串
    select str;
end //

call s_while();

repeat

1
2
3
repeat循环类似Java中的do while循环

repeat是有条件的循环控制语句,当满足条件的时候退出循环。具体语法为:
1
2
3
4
[别名:] REPEAT
    循环语句
UNTIL 条件
END REPEAT [别名]

image-20240110225051485

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
delimiter //
create procedure s_repeat()
begin
    declare i int default 1;
    declare str varchar(256) default '1';
    # 开始repeat循环
    num:
    repeat
        set i = i + 1;
        set str = concat(str, '-', i);
    # until 结束条件
    # end repeat 结束num 结束repeat循环
    until i >= 10 end repeat num;
    # 查询字符串拼接结果
    select str;
end //

call s_repeat();

loop

1
loop为死循环，需要手动退出循环，我们可以使用 leave 来退出循环
1
2
3
LOOP 实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用:
LEAVE:配合循环使用，退出循环。
ITERATE:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
1
2
3
[别名:] LOOP
    循环语句
END LOOP [别名]

image-20240111000914488

image-20240111001248674

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
delimiter //
create procedure s_loop()
begin
    # 声明计数器
    declare i int default 1;
    # 开始循环
    num:
    loop
        # 查询计数器记录的值
        select i;
        # 判断大于等于停止计数
        if i >= 10 then
            leave num;
        end if;
        # 计数器自增1
        set i = i + 1;
    # 结束循环
    end loop num;
end //

call s_loop();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
delimiter //
create procedure s_loop1()
begin
    # 声明变量i计数器
    declare i int default 1;
    # 声明字符串容器
    declare str varchar(256) default '1';
    # 开始循环
    num:
    loop
        # 计数器自增1
        set i = i + 1;
        # 字符串容器拼接计数器结果
        set str = concat(str, '-', i);
        # 计数器i如果小于10就继续执行
        if i < 10 then
           iterate num;
        end if;
        # 计数器i如果大于10就停止循环
        leave num;
    # 停止循环
    end loop num;
    # 查询字符串容器的拼接结果
    select str;
end //

call s_loop1();

视图

1
视图是一个虚拟表，其内容由查询定义。数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。同样对视图的更新，会影响到原来表的数据。
1
安全原因，视图可以隐藏一些数据，例如，员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数等。这个视图就像一个窗口，从中只能看到你想看的数据列。

作用

优点

1
2
3
4
5
6
7
8
1.使操作简单化
视图需要达到的目的就是所见即所需。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2.增加数据的安全性
通过视图，用户只能查询和修改指定的数据。指定数据之外的信息，用户根本接触不到。这样可以防止敏感信息被未授权的用户查看，增强机密信息的安全性。

3.提高表的逻辑独立性
视图可以屏蔽原有表结构变化带来的影响。例如原有表增加列和删除未被引用的列，对视图不会造成影响。同样，如果修改表中的某些列，可以使用修改视图来解决这些列带来的影响。

缺点

1
2
3
在实际数据表的基础上创建视图，那么如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂， 可读性不好，容易变成系统的潜在隐患。因为创建视图的SQL查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。实际项目中，如果视图过多，会导致数据库维护成本的问题。

所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视图，使系统整体达到最优。

语法

1
2
3
4
创建视图是指在已经存在的数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中

创建视图需要具有CREATE VIEW的权限。同时应该具有查询涉及的列的SELECT权限。可以使用SELECT语句来查询这些权限信息。查询语法如下：
SELECT Select_priv,Create_view_priv FROM mysql.user WHERE user='用户名';
1
2
3
4
5
6
7
8
CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW 视图名[(属性清单)]
AS SELECT语句
[WITH [CASCADED|LOCAL] CHECK OPTION];

-ALGORITHM：可选项，表示视图选择的算法。
-OR REPLACE:若加了[or replace]时，还需要用户具有删除视图（drop view）的权限。表示替换已经创建的视图。
-WITH CHECK OPTION：可选项，表示更新视图时要保证在该视图的权限范围之内。CASCADE 与LOCAL为可选参数，CASCADE为默认值，表示更新视图时要满足所有相关视图和表的条件；LOCAL表示更新视图时满足该视图本身定义的条件即可。

创建

1
2
3
4
5
6
针对别名的处理，可以在创建视图的子查询中指定对应的别名

CREATE VIEW v_student1
AS 
SELECT id stu_id,name stu_name,sex gender
FROM student ;
1
2
3
4
5
6
也可以在创建视图的视图名称后添加对应的别名字段

CREATE VIEW v_student2(stu_id,stu_name,gender)
AS 
SELECT id ,name ,sex 
FROM student ;
1
2
3
4
5
可以基于视图创建视图
CREATE VIEW v_student_score1 AS 
SELECT *  FROM
 v_student_score 
WHERE grade > 80;

通过视图变更数据

插入

1
2
3
4
5
6
7
8
9
10
首先v_order是很多张表连接得到的视图

成功
INSERT INTO v_order(pid,pname,price) VALUES('p010','柴油','34');

报错
INSERT INTO v_order(oid,pid,pname,price,quantity) VALUES('D00021','P011','一号柴油','35','12')

第一条语句成功对Product表进行插入数据
可以通过视图插入数据，但是只能基于一个基础表进行插入，不能跨表更新数据

WITH CHECK OPTION

1
如果在创建视图的时候制定了“WITH CHECK OPTION”，那么更新数据时不能插入或更新不符合视图限制条件的记录。
1
2
3
4
5
6
7
8
9
10
create view v_price3000up
as
select * from product where price >=3000
with check option

insert into v_price3000up(pid,pname,price) value('p011','机油','42')
此时插入不符合视图条件的会报错

update v_price3000up set price=1000 where pid='p007'
此时更新结果不符合视图条件的会报错

删除

1
DROP VIEW IF EXISTS view_student;

注意

1
2
3
4
5
6
-在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持 INSERT、UPDATE、DELETE；
-在定义视图的SELECT语句后的字段列表中使用DISTINCT、聚合函数、GROUP BY、HAVING、UNION等，视图将不支持INSERT、UPDATE、DELETE；
-在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询 ，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值；
-在定义视图的时候指定了“ALGORITHM = TEMPTABLE”，视图将不支持INSERT和DELETE操作；
-视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作；
-在定义视图的SELECT语句中使用了 JOIN联合查询 ，视图将不支持INSERT和DELETE操作；

临时表

1
在MySQL中，临时表是一种特殊的表，它用于在当前会话中存储临时数据集。这些表在会话结束时自动销毁，非常适合存储中间结果集或进行复杂查询时使用。

语法

1
2
3
4
5
CREATE TEMPORARY TABLE temp_table_name (
column1 datatype,
column2 datatype,

);
1
2
3
4
5
也可以通过查询结果来创建临时表，这样可以直接将查询结果集存储在临时表中，而不需要单独插入数据。例如：
CREATE TEMPORARY TABLE temp_table_name AS
SELECT column1, column2,
FROM source_table
WHERE condition;

注意

1
2
3
一旦临时表被创建，你就可以像使用普通表一样对其进行各种操作，包括插入、查询、修改和删除数据

临时表只在创建它的会话中可见，其他会话无法访问。如果你需要在多个会话之间共享数据，应该使用普通表而不是临时表。此外，临时表不应与永久表共享相同的名称，以避免混淆和数据丢失的风险。