MySQL学习笔记（三）

已于 2023-02-05 19:12:48 修改
阅读量651 收藏 17
点赞数 18
文章标签： mysql 数据库 java Powered by 金山文档
于 2023-01-11 18:47:39 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_44991992/article/details/128649341
版权

3 视图

3.1 概述

3.1.1 概念

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

3.1.2 优势

  • 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

  • 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。

  • 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

3.2 视图的增删改查

3.2.1 创建视图

基础语法

CREATE VIEW <视图名> AS <SELECT语句>

  • <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

  • <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

对于创建视图中的 SELECT 语句的指定存在以下限制：

  • 用户除了拥有 CREATE VIEW 权限外，还具有操作中涉及的基础表和其他视图的相关权限。

  • SELECT 语句不能引用系统或用户变量。

  • SELECT 语句不能包含 FROM 子句中的子查询。

  • SELECT 语句不能引用预处理语句参数。

3.2.2 修改视图

基础语法

CREATE VIEW <视图名> AS <SELECT语句>
-- 或者
CREATE OR REPLACE <视图名> AS <SELECT语句>

  • <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

  • <SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

需要注意的是，对于 ALTER VIEW 语句的使用，需要用户具有针对视图的 CREATE VIEW 和 DROP 权限，以及由 SELECT 语句选择的每一列上的某些权限。

  • 修改视图的定义，除了可以通过 ALTER VIEW 外，也可以使用 DROP VIEW 语句先删除视图，再使用 CREATE VIEW 语句来实现。

还有一些特定的其他结构，这些结构会使得视图不可更新。更具体地讲，如果视图包含以下结构中的任何一种，它就是不可更新的：

  • 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。

  • DISTINCT 关键字。

  • GROUP BY 子句。

  • HAVING 子句。

  • UNION 或 UNION ALL 运算符。

  • 位于选择列表中的子查询。

  • FROM 子句中的不可更新视图或包含多个表。

  • WHERE 子句中的子查询，引用 FROM 子句中的表。

  • ALGORITHM 选项为 TEMPTABLE（使用临时表总会使视图成为不可更新的）的时候。

3.2.3 删除视图

基础语法

DROP VIEW <视图名1> [ , <视图名2> …]

3.2.4 查看视图

基础语法

DESCRIBE 视图名;
-- 或者
DESC 视图名;

4 存储过程

4.1 概述

4.1.1 概念

存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。

4.1.2 优势

(1) 封装性

通常完成一个逻辑功能需要多条 SQL 语句，而且各个语句之间很可能传递参数，所以，编写逻辑功能相对来说稍微复杂些，而存储过程可以把这些 SQL 语句包含到一个独立的单元中，使外界看不到复杂的 SQL 语句，只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改，而不会影响到调用它的应用程序源代码。

(2) 可增强 SQL 语句的功能和灵活性

存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

(3) 可减少网络流量

由于存储过程是在服务器端运行的，且执行速度快，因此当客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而可降低网络负载。

(4) 高性能

当存储过程被成功编译后，就存储在数据库服务器里了，以后客户端可以直接调用，这样所有的 SQL 语句将从服务器执行，从而提高性能。但需要说明的是，存储过程不是越多越好，过多的使用存储过程反而影响系统性能。

(5) 提高数据库的安全性和数据的完整性

存储过程提高安全性的一个方案就是把它作为中间组件，存储过程里可以对某些表做相关操作，然后存储过程作为接口提供给外部程序。这样，外部程序无法直接操作数据库表，只能通过存储过程来操作对应的表，因此在一定程度上，安全性是可以得到提高的。

(6) 使数据独立

数据的独立可以达到解耦的效果，也就是说，程序可以调用存储过程，来替代执行多条的 SQL 语句。这种情况下，存储过程把数据同用户隔离开来，优点就是当数据表的结构改变时，调用表不用修改程序，只需要数据库管理者重新编写存储过程即可。

4.2 创建存储过程

4.2.1 基础语法

-- 修改分隔符为$
DELIMITER $
CREATE PROCEDURE 存储过程名((IN|OUT|INOUT 参数名 参数类型,...)
BEGIN
	-- 存储过程体(SQL语句)
END ;
DELIMITER;

  • IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量），默认为IN

  • OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

  • INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

在定义过程时，使用 DELIMITER $ 命令将语句的结束符号从分号 ; 临时改为 $，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。

4.2.2 变量

4.2.2.1 定义变量

  • DECLARE

通过 DECLARE 可以定义一个局部变量，该变量的作用范围只能在 BEGIN…END 块中。

DECLARE 变量名[,...] 数据类型 [DEFAULT 默认值]

示例 :

-- 不带默认值
DECLARE name VARCHAR(20);

-- 带默认值
DECLARE age INT default 5;

-- 定义多个变量
DECLARE x, y INT DEFAULT 0;
4.2.2.2 变量赋值

  • SET

直接赋值使用 SET，可以赋常量或者赋表达式，具体语法如下：

SET var_name = expr [, var_name = expr] ...

示例 :

DECLARE name VARCHAR(20);
DECLARE age INT default 5;

-- 单个变量赋值
SET age = 10;
SET age = (SELECT age FROM user WHERE id = 1);
-- 用户变量
SET @sex := '男';

-- 多个变量赋值
SET age, name = 10, '张三';

  • SELECT

基础语法

SELECT @变量名 = 常量;
SELECT @变量名 = 列名 FROM 表名 [WHERE 条件];
SELECT 列名[,...] INTO 变量名[,...] FROM 表名 [WHERE 条件];

示例 :

DECLARE name VARCHAR(20);
DECLARE age INT default 5;

-- 单个变量赋值
SELECT @sex = 10;
SELECT @sex = sex FROM user WHERE id = 1;
SELECT age INTO age FROM user WHERE id = 1;

-- 多个变量赋值
SELECT age, name INTO age, name FROM user WHERE id = 1;

4.2.3 逻辑判断

4.2.3.1 if条件判断

语法结构 :

IF 判断条件1 THEN 执行的sql语句1;
[ELSEIF 判断条件2 THEN 执行的sql语句2;]
...
[ELSE 执行的sql语句n;]
END IF;
4.2.3.2 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;

4.2.4 条件控制

4.2.4.1 loop语句

LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，具体语法如下：

[循环名称:] LOOP
  条件判断语句
    -- 遇到LEAVE语句，退出循环，如果不加退出循环的语句，那么就变成了死循环。
    [LEAVE 循环名称;]
    -- 跳出循环，不过是跳出本次循环，直接进入下次循环，类似Java的continue
    [ITERATE 循环名称;]
  循环体语句;
  条件控制语句;
END LOOP 循环名称;

loop循环以“循环标志：loop“开始，以“END：loop“结束

如果不加退出循环的语句，那么就变成了死循环

4.2.4.2 while循环

语法结构:

WHILE 条件判断语句 DO
  循环体语句;
  条件控制语句;
END WHILE;
4.2.4.3 repeat循环

有条件的循环控制语句, 当满足条件的时候退出循环 。while 是满足条件才执行，repeat 是满足条件就退出循环。

语法结构:

REPEAT
  循环体语句;
  条件控制语句;
  UNTIL 条件判断语句
END REPEAT;

4.2.5 游标

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

声明游标：

DECLARE 游标名称 CURSOR FOR 查询sql语句 ;

OPEN 游标：

OPEN 游标名称 ;

FETCH 游标：

FETCH 游标名称 INTO var_name [, var_name] ...

CLOSE 游标：

CLOSE 游标名称 ;

4.3 调用存储过程

基础语法：

call 存储过程名([参数]) ;

4.4 删除存储过程

基础语法：

DROP procedure [IF EXISTS] 存储过程名;

4.5 查看存储过程

-- 查询db_name数据库中的所有的存储过程
select name from mysql.proc where db='db_name';

-- 查询存储过程的状态信息
show procedure status;

-- 查询某个存储过程的定义
show create procedure test.pro_test1 \G;

4.6 常见问题

4.6.1 如何双重循环？

方法一

DELIMITER $

CREATE ROCEDURE `p_test`(IN v_grade INT)
	BEGIN
 
	-- 遍历数据结束标志 
	DECLARE done INTEGER DEFAULT 0;
	-- 定义变量
	DECLARE v_class_name VARCHAR(500);
	DECLARE v_student_account VARCHAR(500);
	DECLARE v_chinese_score DECIMAL(11,2);
	DECLARE v_math_score DECIMAL(11,2);
	DECLARE v_english_score DECIMAL(11,2);
	DECLARE v_content VARCHAR(500);
	 	 
	-- 声明游标
	DECLARE out_cursor CURSOR FOR 
	SELECT class_name FROM class WHERE grade = v_grade;
	
	DECLARE inner_cursor CURSOR FOR 
	SELECT student_account, chinese, math, english FROM student 
	WHERE grade = v_grade AND class_name = v_class_name;
	 
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	 
	  -- 打开游标
	  OPEN out_cursor;
	 
	    -- 第一个游标循环
	    out_loop:LOOP
	      -- 将游标中的值赋值给变量，要注意sql结果列的顺序
	      FETCH NEXT FROM out_cursor INTO v_class_name;
		IF done = 1 THEN 
		  LEAVE out_loop;
		END IF;
	 
	      -- 打开第二个游标
	      OPEN inner_cursor;
		-- 第二个游标循环
		inner_loop:LOOP
		    FETCH NEXT FROM inner_cursor INTO v_student_account, v_chinese_score, v_math_score, v_english_score;
		    IF done = 1 THEN 
		      LEAVE inner_loop;
		    END IF;
		  
		    IF done = 1 THEN 
		      LEAVE inner_loop;
		    END IF;
	 
		    IF v_chinese_score >= 90 THEN  
		      SET v_content = '语文优秀';
		    ELSE IF v_chinese_score >= 60 THEN  
		      SET v_content = '语文及格';
		    ELSE
		      SET v_content = '语文不及格';
		    END IF;
		    
		    IF v_math_score >= 90 THEN  
		      SET v_content = CONCAT(v_content, ',数学优秀');
		    ELSE IF v_math_score >= 60 THEN  
		      SET v_content = CONCAT(v_content, ',数学及格');
		    ELSE
		      SET v_content = CONCAT(v_content, ',数学不及格');
		    END IF;
		    
		    IF v_english_score >= 90 THEN  
		      SET v_content = CONCAT(v_content, ',英语优秀');
		    ELSE IF v_english_score >= 60 THEN  
		      SET v_content = CONCAT(v_content, ',英语及格');
		    ELSE
		      SET v_content = CONCAT(v_content, ',英语不及格');
		    END IF;
		    
		    REPLACE INTO student_content 
		    (grade, class, student_account, content)
		    VALUES
		    (v_grade, v_class_name, v_student_account, v_content);
		    
		-- 结束内层循环
		END LOOP inner_loop;
	 
	      -- 循环结束后关闭内层游标
	      CLOSE inner_cursor;
	      -- **停止循环标志**
	      SET done=0;
	 
	    -- 结束外层循环
	    END LOOP out_loop;
	  
	  -- 循环结束后，关闭外层游标
	  CLOSE out_cursor;
	END$

DELIMITER ;

方法二

DELIMITER $

CREATE ROCEDURE `p_test`(IN v_grade INT)

BEGIN
    -- 遍历数据结束标志 
	DECLARE done INTEGER DEFAULT 0;
	-- 定义变量
	DECLARE v_class_name VARCHAR(500);
    DECLARE out_cursor CURSOR FOR 
	SELECT class_name FROM class WHERE grade = v_grade;
	
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
             -- 打开游标
	  OPEN out_cursor;
	 
	    -- 第一个游标循环
	    out_loop:LOOP
            FETCH out_cursor INTO v_class_name;
                IF done = 1 THEN 
                    LEAVE out_loop;
                END IF;
                    -- 第二层循环
		    BEGIN
		        DECLARE done2 INT DEFAULT 0;
			DECLARE v_student_account VARCHAR(500);
			DECLARE v_chinese_score DECIMAL(11,2);
			DECLARE v_math_score DECIMAL(11,2);
			DECLARE v_english_score DECIMAL(11,2);
			DECLARE v_content VARCHAR(500);
			    
			DECLARE inner_cursor CURSOR FOR 
			SELECT student_account, chinese, math, english FROM student 
			WHERE grade = v_grade AND class_name = v_class_name;
			
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = 1;  
			OPEN inner_cursor;
			inner_loop : LOOP
			FETCH NEXT FROM inner_cursor INTO v_student_account, v_chinese_score, v_math_score, v_english_score;
			IF done2 THEN 
			    LEAVE inner_loop;
			END IF;
			
			IF v_chinese_score >= 90 THEN  
			  SET v_content = '语文优秀';
			ELSE IF v_chinese_score >= 60 THEN  
			  SET v_content = '语文及格';
			ELSE
			  SET v_content = '语文不及格';
			END IF;
		    
			IF v_math_score >= 90 THEN  
			  SET v_content = CONCAT(v_content, ',数学优秀');
			ELSE IF v_math_score >= 60 THEN  
			  SET v_content = CONCAT(v_content, ',数学及格');
			ELSE
			  SET v_content = CONCAT(v_content, ',数学不及格');
			END IF;
		    
			IF v_english_score >= 90 THEN  
			  SET v_content = CONCAT(v_content, ',英语优秀');
			ELSE IF v_english_score >= 60 THEN  
			  SET v_content = CONCAT(v_content, ',英语及格');
			ELSE
			  SET v_content = CONCAT(v_content, ',英语不及格');
			END IF;
		    
			REPLACE INTO student_content 
			(grade, class, student_account, content)
			VALUES
			(v_grade, v_class_name, v_student_account, v_content);
				
			END LOOP;
			CLOSE inner_cursor;
		    END;         
            END LOOP;
            CLOSE out_cursor;
END$

DELIMITER ;

5 函数

5.1 概述

5.1.1 概念

存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。存储函数可以通过 return 语句返回函数值，主要用于计算并返回一个值。而存储过程没有直接返回值，主要用于执行操作。

5.1.2 优势

  • 良好的封装性

  • 应用程序和SQL逻辑分离

  • 让SQL也具有处理能力

  • 减少网络交互

  • 能够提高系统性能

  • 降低数据出错的概率，保证数据的一致性和完整性

  • 保证数据的安全性

5.2 增删改查

5.2.1 创建存储函数

-- 修改分隔符为$
DELIMITER $
CREATE FUNCTION func_name ([param_name type[,...]])
RETURNS type 
[characteristic ...] 
BEGIN
	fun_body; 
END;
DELIMITER;

  • func_name ：存储函数的名称。

  • param_name type：可选项，指定存储函数的参数。type参数用于指定存储函数的参数类型，该类型可以是MySQL数据库中所有支持的类型。

  • RETURNS type：指定返回值的类型。

  • characteristic：可选项，指定存储函数的特性。

  • fun_body：该参数有一组 SQL 语句来执行操作。它至少需要一个 RETURN 语句。执行 return 语句时，函数将自动终止。函数体如下： BEGIN -- SQL 语句 END $ DELIMITER

示例

delimiter $ -- 设置结束符为
create function ym_date(mydate date)
returns varchar(15)-- returns指定函数的返回类型
begin
return date_format(mydate,'%Y-%m'); -- 函数需要返回值，return返回相应的处理结果
end$
delimiter; -- 恢复结束符

存储函数的定义变量、变量赋值、逻辑判断和条件控制与存储过程相同

5.2.2 修改存储函数

ALTER FUNCTION func_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

5.2.3 删除存储函数

drop function [if exists] func_name;

5.2.4 调用存储函数

SELECT func_name([parameter[,…]]);

-- 示例
SELECT func_user(1);

5.3 常用函数

5.3.1 数字函数

函数名称

作 用

ABS

求绝对值

SQRT

求二次方根

MOD

求余数

CEIL 和 CEILING

两个函数功能相同，都是返回不小于参数的最小整数，即向上取整

FLOOR

向下取整，返回值转化为一个BIGINT

RAND

生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列

ROUND

对所传参数进行四舍五入

SIGN

返回参数的符号

POW 和 POWER

两个函数的功能相同，都是所传参数的次方的结果值

SIN

求正弦值

ASIN

求反正弦值，与函数 SIN 互为反函数

COS

求余弦值

ACOS

求反余弦值，与函数 COS 互为反函数

TAN

求正切值

ATAN

求反正切值，与函数 TAN 互为反函数

COT

求余切值

5.3.2 字符串函数

函数名称

作 用

LENGTH

计算字符串长度函数，返回字符串的字节长度

CONCAT

合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个

INSERT

替换字符串函数

LOWER

将字符串中的字母转换为小写

UPPER

将字符串中的字母转换为大写

LEFT

从左侧字截取符串，返回字符串左边的若干个字符

RIGHT

从右侧字截取符串，返回字符串右边的若干个字符

TRIM

删除字符串左右两侧的空格

REPLACE

字符串替换函数，返回替换后的新字符串

SUBSTRING

截取字符串，返回从指定位置开始的指定长度的字符换

REVERSE

字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串

5.3.3 日期函数

函数名称

作 用

CURDATE 和 CURRENT_DATE

两个函数作用相同，返回当前系统的日期值

CURTIME 和 CURRENT_TIME

两个函数作用相同，返回当前系统的时间值

NOW 和 SYSDATE

两个函数作用相同，返回当前系统的日期和时间值

MONTH

获取指定日期中的月份

MONTHNAME

获取指定日期中的月份英文名称

DAYNAME

获取指定曰期对应的星期几的英文名称

DAYOFWEEK

获取指定日期对应的一周的索引位置值

WEEK

获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53

DAYOFYEAR

获取指定曰期是一年中的第几天，返回值范围是1~366

DAYOFMONTH

获取指定日期是一个月中是第几天，返回值范围是1~31

YEAR

获取年份，返回值范围是 1970〜2069

TIME_TO_SEC

将时间参数转换为秒数

SEC_TO_TIME

将秒数转换为时间，与TIME_TO_SEC 互为反函数

DATE_ADD 和 ADDDATE

两个函数功能相同，都是向日期添加指定的时间间隔

DATE_SUB 和 SUBDATE

两个函数功能相同，都是向日期减去指定的时间间隔

ADDTIME

时间加法运算，在原始时间上添加指定的时间

SUBTIME

时间减法运算，在原始时间上减去指定的时间

DATEDIFF

获取两个日期之间间隔，返回参数 1 减去参数 2 的值

DATE_FORMAT

格式化指定的日期，根据参数返回指定格式的值

WEEKDAY

获取指定日期在一周内的对应的工作日索引

5.3.4 聚合函数

函数名称

作用

MAX

查询指定列的最大值

MIN

查询指定列的最小值

COUNT

统计查询结果的行数

SUM

求和，返回指定列的总和

AVG

求平均值，返回指定列数据的平均值

6 事件

6.1 概述

6.1.1 概念

event事件 事件（event）是MySQL在相应的时刻调用的过程式数据库对象。一个事件可调用一次，也可 周期性的启动，它由一个特定的线程来管理的，也就是所谓的“事件调度器”。

6.1.2 优势

  • 一些对数据定时性操作不再依赖外部程序，而直接使用数据库本身提供的功能。

  • 可以实现每秒钟执行一个任务，这在一些对实时性要求较高的环境下就非常实用了。

6.2 数据库开启事件

--查看事件是否开启
show VARIABLES like 'event%';  
--开启事件
SET GLOBAL event_scheduler = 1;
--关闭事件
SET GLOBAL event_scheduler = 0;

6.3 增删改查

6.3.1 创建事件

create [definer=user] event [if not exists] event_name
  on schedule 时间与频率
  [on completion [not] preserve]
  [enable | disable | disable on slave]
  [comment '注释']
  do 事件体;

  -- 时间与频率：
  at timestamp [+ interval 时间间隔 时间单位] ... | 
  every 时间间隔 时间单位 [starts timestamp [+ interval 时间间隔 时间单位] ...]
                         [ends timestamp [+ interval 时间间隔 时间单位] ...]

  -- 时间间隔 时间单位：
  数量 {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
       WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
       DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

  • on completion表表示定义事件一旦过期是否立即被删除，默认为not preserve，表示删除；设置为preserve表示不删除。

  • enable表示当前事件可用，默认为enable；disable表示当前事件不可用。

  • comment表示注释，最多可有64个字符。

  • at表示事件只执行一次，every表示时间定期执行。starts和ends表示定期执行事件的时间范围。

  • every后设置事件的执行间隔时间，starts设置从何时开始，ends设置到何时结束。

  • start后的[+ interval 时间间隔 时间单位]表示延时到timestamp后指定时间开始执行；

ends后的[+ interval 时间间隔 时间单位]表示延时到timestamp后指定时间结束执行；

当starts和ends后指定多个[+ interval 时间间隔 时间单位]时，这多个指定时间间隔的时间将被合并。

  • 使用show events语句可以方便的查看事件的开始时间和结束时间以及执行间隔等信息。

示例

CREATE EVENT IF NOT EXISTS `e_test` ON SCHEDULE 
EVERY 10 MINUTE 
STARTS '2023-01-10 00:10:00' 
ON COMPLETION PRESERVE ENABLE 
DO BEGIN
        call p_test();
END

6.3.2 修改事件

alter event event_name
  [on schedule 时间与频率]
  [on completion [not] preserve]
  [rename to 新的事件名]
  [enable | disable | disable on slave]
  [comment '注释']
  [do 事件体];

6.3.3 删除事件

drop event [if exists] 事件名;

6.3.4 查看事件

-- 查看事件
show events [{FROM | IN} schema_name] [LIKE 'pattern' | WHERE expr];
-- 查看事件创建的语句
show create event event_name;

事件创建后存储再数据库中，其相关信息存在Information_schema库中的events表中。

6.3.5 开启/关闭事件

-- 开启事件
alter event 事件名 on completion preserve enable;
-- 关闭事件
alter event 事件名 on completion preserve disable;

7 触发器

7概述

7.1.1 概念

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

7.1.2 优势

  • SQL触发器提供了检查数据完整性的替代方法。

  • SQL触发器可以捕获数据库层中业务逻辑中的错误。

  • SQL触发器提供了运行计划任务的另一种方法。通过使用SQL触发器，您不必等待运行计划的任务，因为在对表中的数据进行更改之前或之后自动调用触发器。

  • SQL触发器对于审核表中数据的更改非常有用。

7.2 增删改查

7.2.1 创建触发器

create trigger trigger_name 
before/after insert/update/delete
on tbl_name 
[ for each row ]  -- 行级触发器
begin
	trigger_stmt ;
end;

  • 一张表中，每一个触发时机绑定的触发事件对应的触发器类型只能有一个

一张表表中只能有一个对应的after insert 触发器

最多只能有6个触发器

before insert
after insert
before update
after update
before delete
after delete

示例

需求

通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;

首先创建一张日志表 :

create table emp_logs(
  id int(11) not null auto_increment,
  operation varchar(20) not null comment '操作类型, insert/update/delete',
  operate_time datetime not null comment '操作时间',
  operate_id int(11) not null comment '操作表的ID',
  operate_params varchar(500) comment '操作参数',
  primary key(`id`)
)engine=innodb default charset=utf8;

创建 insert 型触发器，完成插入数据时的日志记录 :

DELIMITER $

create trigger emp_logs_insert_trigger
after insert 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));	
end $

DELIMITER ;

创建 update 型触发器，完成更新数据时的日志记录 :

DELIMITER $

create trigger emp_logs_update_trigger
after update 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')'));                                                                      
end $

DELIMITER ;

创建delete 行的触发器 , 完成删除数据时的日志记录 :

DELIMITER $

create trigger emp_logs_delete_trigger
after delete 
on emp 
for each row 
begin
  insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));                                                                      
end $

DELIMITER ;

7.2.2 删除触发器

drop trigger [schema_name.]trigger_name;

如果没有指定 schema_name，默认为当前数据库 。

7.2.3 查看触发器

可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。

show triggers ;

show triggers\G

相关文章

MySQL学习笔记（一）

MySQL学习笔记（二）

MySQL学习笔记（三）

MySQL学习笔记（四）

傲雪孤鹰
关注
  • 18
    点赞
  • 踩
  • 17
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Mysql存储过程编写
weixin_33754065的博客
05-20 1425
Mysql存储过程编写存储过程编写的模板：Create PROCEDURE PROCEDUREName (IN para mint,……)BeginDeclare varname type;语句；End;以上就是存储过程的编写模板。其中，type可以是表中的任意类型，比如：varchar,bigint,int,decimal,longtext等等类型。游标的声明是：Declare cursorNa...
mysql游标提前退出（详细说明，完整用例，结果）
欢迎光临我的博客！
11-15 2705
mysql游标退出
MySQL数据库中“存储过程”的“LOOP循环”使用
m0_65800896的博客
09-10 5369
loop实现简单的得循环，如果不在SQL逻辑中增加退出循环的条件，可以用来实现简单的死循环。loop可以配合以下两个语句使用: 1、leace:配合循环使用，退出循环。 2、iterate:必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。 例题: 计算从1到n之间偶数累加的值，n为传入的参数值。 编写思路： 1、定义局部变量，记录累加之后的值； 2、每循环一次，就会对n进行
MySQL 高级 loop循环
Leon_Jinhai_Sun的博客
12-01 6343
loop语句 LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现，具体语法如下： [begin_label:] LOOP statement_list END LOOP [end_label] 如果不在 statement_list 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。 leave语句 用来从标注的流程构造中退出，通常和 BEGIN ... END 或者循环一起使用。下面是一个使用 LOOP 和 LEAVE
break和continue
tu_feng_wei的博客
09-12 163
switch中的break和continue /* switch中的break和fallthrough语句 break：可以在switch中使用，也可以在for循环中使用 强制结束case语句，从而结束switch分支 fallthrough：用于穿透switch 当switch中某个case匹配成功之后，就执行该case语句 如果遇到fallthrough，那么后面紧邻的case无需匹配，执行穿透执行 fallthrough应该位于某个case的最后一行 */ n := 1 sw
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
MySql学习笔记（详细）
05-18
4、Mysql函数 5、事务和索引 6、权限设计 7、JDBC 能学到什么：详细了解MYSQL基础以及使用 阅读建议： 建议配合狂神教学视频使用：...
MySQL学习笔记 MySQL学习笔记
09-28
MySQL是世界上最受欢迎的关系型数据库管理系统之一，尤其在Web应用...学习MySQL不仅涉及理论知识，还需要大量的实践操作来熟悉其语法和功能。掌握MySQL能让你在数据管理、Web开发和系统集成等领域具备更强的竞争力。
MySQL进阶篇学习笔记
05-14
黑马MySQL课程总结的学习笔记
MySQL学习笔记小结
09-10
本篇笔记将总结MySQL的学习要点，包括慢查询日志、日志分类、SQL架构以及其内部组件的功能。 首先，慢查询日志是MySQL中用于追踪执行时间过长的SQL语句的重要工具。默认情况下，MySQL不会记录所有查询，但可以通过...
mysql存储过程游标之loop循环
duduyingya的博客
08-10 2831
mysql存储过程游标 一共有3中循环方式 while, repeat, loop, loop DELIMITER $ CREATE PROCEDURE DAY081002() BEGIN #定义参数 后面使用 DECLARE a INT; DECLARE b INT DEFAULT 0; # 定义游标 DECLARE c_cursor CURSOR FOR SELECT shopstoreid FROM shopstore; OPEN c_cursor; #开启游标 #定义loop循环 循环名字为
mysql 存储过程 LOOP 及 LEAVE、ITERATE
殇沫流年的专栏
09-09 1万+
LOOP 及 LEAVE、ITERATE 这里LOOP用来标记循环； 而LEAVE表示离开循环，好比编程里面的break一样； ITERATE则继续循环，好比编程里面的continue一样。
mysql存储过程 loop循环
热门推荐
殇沫流年的专栏
08-04 1万+
-- loop 与 leave,iterate 实现循环   -- loop 标志位无条件循环，       leave 类似于break 语句，跳出循环，跳出 begin end，        iterate 类似于continue ，结束本次循环 create procedure pro12()  begin    declare  b int;    declar
mysql 存储过程 - 循环嵌套
西瓜
07-25 774
BEGIN             DECLARE _isexists1 INT;     DECLARE _depid INT;     DECLARE _userid INT; DECLARE _done int DEFAULT 0;     DECLARE cur_SaleDepas CURSOR FOR     SELECT Department_ID FROM Department WH...
mysql游标嵌套游标_mysql游标嵌套循环
weixin_32304077的博客
01-18 1089
最近在mysql开发过程中用到了游标的嵌套循环,下面列举了3种嵌套循环(loop-loop,loop-while,loop-repeat).程序用到的表和数据CREATE TABLE tb_dic_class (class_id int(11) DEFAULT NULL,class_name varchar(20) DEFAULT NULL,createtime datetime DEFAULT ...
mysql 嵌套存储过程_mysql 存储过程嵌套
weixin_30914751的博客
01-18 363
新建脚本update_mysql.sqlDELIMITER //DROP PROCEDURE IF EXISTS updateOldData; //如果之前调用过以下 过程函数，需要将其dropCREATE PROCEDURE updateOldData()BEGINDECLAREprimary_agent_id BIGINT;DECLAREsecondary_agent_id BIGINT;DE...
java中outloop的作用_在html中嵌入图像在Outlook 2010中不起作用
weixin_33418394的博客
02-27 408
我正在尝试从我的ASP.NET Web窗体应用程序发送电子邮件，并且电子邮件正文有一个图像 . 我按照下面的讨论，并能够将图像设置为base64-data字符串 .在HTML模板(用于构建电子邮件内容)中设置图像src后，我使用以下c＃代码来设置cid值 .C#AlternateView htmlView = AlternateView.CreateAlternateViewFromString(...
MySQL表中允许有多少个 TRIGGERS（触发器）？
sheji888的专栏
07-17 439
MySQL的触发器可以分为六种类型：BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE，每种类型可以针对一个表定义多个触发器，但是每种类型的触发器在单个表上只能有一个与之关联的命名触发器。：虽然技术上讲每种类型的触发器在单个表上只能有一个命名触发器，但你可以通过创建具有不同名称的相同类型的多个触发器来绕过这个限制，只要这些触发器在触发时满足不同的条件（例如，检查不同的列或满足不同的业务逻辑）。
MySQL：SELECT 语句
最新发布
Zachyy的博客
07-20 215
在 MySQL 中，可以使用 SELECT语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
MySQL学习笔记：从基础到高级
"一千行+MySQL+学习笔记.pdf" MySQL是一种广泛应用的关系型数据库管理系统（RDBMS），它以其高效、稳定和易用性受到广大开发者的青睐。这份学习笔记涵盖了MySQL的多个关键知识点，包括基本的SQL语句、索引、数据...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值