MySQL之存储过程，存储函数，触发器详解

最新推荐文章于 2024-05-12 20:39:09 发布
最新推荐文章于 2024-05-12 20:39:09 发布
阅读量871 收藏 22
点赞数 22
文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/huaz_md/article/details/136465401
版权

华子目录

存储过程

什么是存储过程？

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

在这里插入图片描述

特点

  • 封装，复用
  • 可以接收参数，也可以返回数据
  • 减少网络交互，效率提升

在这里插入图片描述

存储过程的创建

mysql> create procedure 存储过程名([参数列表])
	-> begin
	->     SQL语句;
	-> end;

在这里插入图片描述

存储过程的调用

mysql> call 存储过程名([参数列表]);

在这里插入图片描述
注：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter指定SQL语句的结束符

mysql> delimiter @   #指定结束符
mysql> select * from employee@
+------+-----------+-----------------+--------+-------+--------+
| id   | name      | job             | salary | bonus | job_id |
+------+-----------+-----------------+--------+-------+--------+
|  101 | 麦当      | 后端研发        |  25000 |  5000 |   1003 |
|  102 | 咕咚      | 网络运维        |  15000 |  3000 |   1003 |
|  103 | 迪亚      | 测试工程师      |  12000 |  2000 |   1003 |
|  104 | 米龙      | 后端开发        |  20000 |  3500 |   1003 |
|  105 | 极光      | 前端开发        |  15000 |  2500 |   1003 |
|  106 | 村长      | 人力资源        |  10000 |   500 |   1001 |
|  107 | 五条人    | 销售工程师      |  14000 |  7000 |   1002 |
|  108 | 皇帝      | 董事长          |  30000 | 10000 |   1004 |
+------+-----------+-----------------+--------+-------+--------+


mysql> create procedure p1()
    -> begin
    -> select count(*) from employee;
    -> end@
Query OK, 0 rows affected (0.04 sec)


mysql> call p1()@
+----------+
| count(*) |
+----------+
|        8 |
+----------+

在这里插入图片描述

存储过程的查看

查询指定数据库的存储过程及状态信息

mysql> select * from information_schema.routines where routine_schema='XXX'  #XXX为库名

mysql> select * from information_schema.routines where routine_schema='huazi';  # 查看huazi库
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION                       | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE
                                                            | ROUTINE_COMMENT | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| p1            | def             | huazi          | p1           | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | begin
select count(*) from employee;
end |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2024-03-04 23:02:54 | 2024-03-04 23:02:54 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+

查询某个存储过程的定义

mysql> show create procedure 存储过程名;

mysql> show create procedure p1;
+-----------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure
                                               | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p1        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`()
begin
select count(*) from employee;
end | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

存储过程的删除

mysql> drop procedure [if exists] 存储过程名;

在这里插入图片描述

变量

系统变量

  • 系统变量是MySQL服务器提供的，不是用户定义的，属于服务器层面。分为全局变量（global），会话变量（session），默认是session

查看系统变量

#查看所有系统变量
mysql> show [session/global] variables; 


#模糊匹配查找变量
mysql> show [session/global] variables like '......';


#查看指定变量的值
mysql> select @@[session/global]系统变量名;

设置系统变量

mysql> set [session/global] 系统变量名 = 值;
或
mysql> set @@[session/global]系统变量名 = 值;

在这里插入图片描述
在这里插入图片描述

用户定义变量

  • 用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接使用 @变量名 就可以，其作用域为当前会话（在其他会话中无效）
  • 注：用户定义的变量无需对其进行声明或初始化，只不过获取到的值为null（如果不给用户变量赋值就是null）

赋值

mysql> set @变量名=值;
或
mysql> set @变量名:=值;

mysql> select @变量名:=值;
或
mysql> select 字段 into @变量名 from 表名;

使用

mysql> select @变量名;

在这里插入图片描述

mysql> set @myname = '华子';
Query OK, 0 rows affected (0.00 sec)

mysql> set @myage = 18;
Query OK, 0 rows affected (0.00 sec)

mysql> set @mygender = '男',@myhobby = '编程';
Query OK, 0 rows affected (0.00 sec)


mysql> select @mycolor := 'red';
+-------------------+
| @mycolor := 'red' |
+-------------------+
| red               |
+-------------------+


mysql> select count(*) into @mycount from employee;
Query OK, 1 row affected (0.04 sec)


mysql> select @myname,@myage,@mygender,@myhobby,@mycolor,@mycount;
+---------+--------+-----------+----------+----------+----------+
| @myname | @myage | @mygender | @myhobby | @mycolor | @mycount |
+---------+--------+-----------+----------+----------+----------+
| 华子    |     18 | 男        | 编程     | red      |        8 |
+---------+--------+-----------+----------+----------+----------+


mysql> set @myage := 22;  #修改myage的值
Query OK, 0 rows affected (0.00 sec)


mysql> select @myage;
+--------+
| @myage |
+--------+
|     22 |
+--------+

在这里插入图片描述

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin … end块。

声明

mysql> declare 变量名 变量类型 [default 值];

变量类型和数据库字段类型一样：int，bigint，char，varchar，date，time等

赋值

mysql> set 变量名=值;
或
mysql> set 变量名:=值;
或
mysql> select 字段 into 变量名 from 表名 [where条件];

在这里插入图片描述

mysql> delimiter $


mysql> create procedure p2()
    -> begin
    -> declare employee_count int default 0;  #定义employee变量，并默认值为0
    -> select count(*) into employee_count from employee; #将查出来的count(*)结果赋值给employee_count变量
    -> select employee_count;
    -> end$
Query OK, 0 rows affected (0.01 sec)


mysql> call p2()$   #调用p2存储过程
+----------------+
| employee_count |
+----------------+
|              8 |
+----------------+


mysql> show create procedure p2$
+-----------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode
           | Create Procedure
                                                                 | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p2        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p2`()
begin
declare employee_count int default 0;
select count(*) into employee_count from employee;
select employee_count;
end | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+


#查看huazi库中的存储过程
mysql> select * from information_schema.routines where routine_schema='huazi'$
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+---------------------------------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION
                                      | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE                                                                                                              | ROUTINE_COMMENT | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+---------------------------------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| p1            | def             | huazi          | p1           | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | begin
select count(*) from employee;
end                                                                                  |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2024-03-04 23:02:54 | 2024-03-04 23:02:54 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| p2            | def             | huazi          | p2           | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | begin
declare employee_count int default 0;
select count(*) into employee_count from employee;
select employee_count;
end |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2024-03-05 09:45:37 | 2024-03-05 09:45:37 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+---------------------------------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+

if条件

if 条件1 then
	sql语句;
elseif 条件2 then
	sql语句;
else
	sql语句;
end if;

在这里插入图片描述

if案例

根据定义的分数score变量，判定当前分数对应的分数等级。

  • score >=90分，等级为优秀。
  • score >=80分，等级为良好。
  • score >=70分，等级为中等。
  • score >=60分，等级为及格。
  • score < 60分，等级为不及格。
mysql> delimiter $
mysql> create procedure p3()
    -> begin
    -> declare score int default 58;
    -> declare result varchar(10);
    -> if score>=90 then
    -> set result:='优秀';
    -> elseif score >=80 then
    -> set result:='良好';
    -> elseif score>=70 then
    -> set result:='中等';
    -> elseif score>=60 then
    -> set result:='及格';
    -> else
    -> set result:='不及格';
    -> end if;
    -> select result;
    -> end$
Query OK, 0 rows affected (0.03 sec)

mysql> call p3()$
+-----------+
| result    |
+-----------+
| 不及格    |
+-----------+


mysql> show create procedure p3$
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure

                                                                                                                          | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p3        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p3`()
begin
declare score int default 58;
declare result varchar(10);
if score>=90 then
set result:='优秀';
elseif score >=80 then
set result:='良好';
elseif score>=70 then
set result:='中等';
elseif score>=60 then
set result:='及格';
else
set result:='不及格';
end if;
select result;
end            | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+



mysql> select * from information_schema.routines where routine_schema='huazi'$
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION

                                | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE                                                                                                              | ROUTINE_COMMENT | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| p1            | def             | huazi          | p1           | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | begin
select count(*) from employee;
end
                                                                               |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2024-03-04 23:02:54 | 2024-03-04 23:02:54 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| p2            | def             | huazi          | p2           | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | begin
declare employee_count int default 0;
select count(*) into employee_count from employee;
select employee_count;
end                                                                                                                                                                              |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2024-03-05 09:45:37 | 2024-03-05 09:45:37 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| p3            | def             | huazi          | p3           | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | begin
declare score int default 58;
declare result varchar(10);
if score>=90 then
set result:='优秀';
elseif score >=80 then
set result:='良好';
elseif score>=70 then
set result:='中等';
elseif score>=60 then
set result:='及格';
else
set result:='不及格';
end if;
select result;
end            |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2024-03-05 13:36:35 | 2024-03-05 13:36:35 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+

在这里插入图片描述

参数

类型含义备注
in该类参数作为输入，也就是需要调用时传入值默认
out该类参数作为输出，也就是该参数可以作为返回值
inout既可以作为输入参数，也可以作为输出参数
mysql> create procedure 存储过程名([in/out/inout 参数名 参数类型])
	-> begin 
	-> 		sql语句;
	-> end;

在这里插入图片描述

参数案例

  1. 根据传入参数score，判定当前分数对应的分数等级，并返回
  • score >=90分，等级为优秀。
  • score >=80分，等级为良好。
  • score >=70分，等级为中等。
  • score >=60分，等级为及格。
  • score < 60分，等级为不及格。
mysql> delimiter $
mysql> create procedure p4(in score int,out result varchar(10))
    -> begin
    -> if score>=90 then
    -> set result='优秀';
    -> elseif score>=80 then
    -> set result='良好';
    -> elseif score>=70 then
    -> set result='中等';
    -> elseif score>=60 then
    -> set result='及格';
    -> else
    -> set result='不及格';
    -> end if;
    -> end$
Query OK, 0 rows affected (0.03 sec)


mysql> call p4(68,@result)$


mysql> select @result$
+---------+
| @result |
+---------+
| 及格    |
+---------+


mysql> call p4(78,@result)$


mysql> select @result$
+---------+
| @result |
+---------+
| 中等    |
+---------+


mysql> call p4(0,@result)$


mysql> select @result$
+-----------+
| @result   |
+-----------+
| 不及格    |
+-----------+


mysql> show create procedure p4$
+-----------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure

                                                                             | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p4        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p4`(in score int,out result varchar(10))
begin
if score>=90 then
set result='优秀';
elseif score>=80 then
set result='良好';
elseif score>=70 then
set result='中等';
elseif score>=60 then
set result='及格';
else
set result='不及格';
end if;
end            | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+


mysql> select * from information_schema.routines where routine_schema='huazi'$
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| SPECIFIC_NAME | ROUTINE_CATALOG | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | DTD_IDENTIFIER | ROUTINE_BODY | ROUTINE_DEFINITION

                                | EXTERNAL_NAME | EXTERNAL_LANGUAGE | PARAMETER_STYLE | IS_DETERMINISTIC | SQL_DATA_ACCESS | SQL_PATH | SECURITY_TYPE | CREATED             | LAST_ALTERED        | SQL_MODE                                                                                                              | ROUTINE_COMMENT | DEFINER        | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+
| p1            | def             | huazi          | p1           | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | begin
select count(*) from employee;
end
                                                                               |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2024-03-04 23:02:54 | 2024-03-04 23:02:54 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| p2            | def             | huazi          | p2           | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | begin
declare employee_count int default 0;
select count(*) into employee_count from employee;
select employee_count;
end                                                                                                                                                                              |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2024-03-05 09:45:37 | 2024-03-05 09:45:37 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| p3            | def             | huazi          | p3           | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | begin
declare score int default 58;
declare result varchar(10);
if score>=90 then
set result:='优秀';
elseif score >=80 then
set result:='良好';
elseif score>=70 then
set result:='中等';
elseif score>=60 then
set result:='及格';
else
set result:='不及格';
end if;
select result;
end            |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2024-03-05 13:36:35 | 2024-03-05 13:36:35 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| p4            | def             | huazi          | p4           | PROCEDURE    |           |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL           | NULL           | SQL          | begin
if score>=90 then
set result='优秀';
elseif score>=80 then
set result='良好';
elseif score>=70 then
set result='中等';
elseif score>=60 then
set result='及格';
else
set result='不及格';
end if;
end                                                                                            |          NULL | SQL               | SQL             | NO               | CONTAINS SQL    |     NULL | DEFINER       | 2024-03-05 13:54:29 | 2024-03-05 13:54:29 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |                 | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+---------------+-----------------+----------------+--------------+--------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+----------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+-------------------+-----------------+------------------+-----------------+----------+---------------+---------------------+---------------------+-----------------------------------------------------------------------------------------------------------------------+-----------------+----------------+----------------------+----------------------+--------------------+

在这里插入图片描述
在这里插入图片描述

  1. 将传入的200分制的分数，进行换算，换算成百分制，然后返回
mysql> create procedure p5(inout score double)
    -> begin
    -> set score=score*0.5;
    -> end$
Query OK, 0 rows affected (0.00 sec)


mysql> set @score=198$


mysql> call p5(@score)$


mysql> select @score$
+--------+
| @score |
+--------+
|     99 |
+--------+



mysql> show create procedure p5$
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode
           | Create Procedure                                                                                    | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p5        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p5`(inout score double)
begin
set score=score*0.5;
end | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

在这里插入图片描述

case语法

语法一

case 常量值
	when 值1 then 
		sql语句;
	when 值2 then 
		sql语句;
	else 
		sql语句;
end case;

在这里插入图片描述

语法二

case
	when 条件1 then 
		sql语句;
	when 条件2 then 
		sql语句;
	else 
		sql语句;
end case;

在这里插入图片描述

case案例

根据传入的月份，判定月份所属的季节（使用case）

  • 1-3月，为第一季度
  • 4-6月，为第二季度
  • 7-9月，为第三季度
  • 10-12月，为第四季度
mysql> create procedure p6(in month int)
    -> begin
    -> declare result varchar(10);
    -> case
    -> when month>=1 and month<=3 then
    -> set result='第一季度';
    -> when month>=4 and month<=6 then
    -> set result='第二季度';
    -> when month>=7 and month<=9 then
    -> set result='第三季度';
    -> when month>=10 and month<=12 then
    -> set result='第四季度';
    -> else
    -> set result='非法参数';
    -> end case;
    -> select concat('您输入的月份为：',month,'，所属季度为：',result);
    -> end$
Query OK, 0 rows affected (0.03 sec)


mysql> call p6(4)$
+-------------------------------------------------------------------------+
| concat('您输入的月份为：',month,'，所属季度为：',result)                |
+-------------------------------------------------------------------------+
| 您输入的月份为：4，所属季度为：第二季度                                 |
+-------------------------------------------------------------------------+


mysql> call p6(11)$
+-------------------------------------------------------------------------+
| concat('您输入的月份为：',month,'，所属季度为：',result)                |
+-------------------------------------------------------------------------+
| 您输入的月份为：11，所属季度为：第四季度                                |
+-------------------------------------------------------------------------+



mysql> show create procedure p6$
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure


                                                               | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p6        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p6`(in month int)
begin
declare result varchar(10);
case
when month>=1 and month<=3 then
set result='第一季度';
when month>=4 and month<=6 then
set result='第二季度';
when month>=7 and month<=9 then
set result='第三季度';
when month>=10 and month<=12 then
set result='第四季度';
else
set result='非法参数';
end case;
select concat('您输入的月份为：',month,'，所属季度为：',result);
end                                    | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

在这里插入图片描述
在这里插入图片描述

循环

while循环

while 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为:

#先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
while 条件 do
	sql语句;
end while;

在这里插入图片描述

while循环案例

计算从1累加到n的值，n为传入的参数

mysql> create procedure p7(in n int)
    -> begin
    -> declare total int default 0;
    -> while n>0 do
    -> set total=total+n;
    -> set n=n-1;
    -> end while;
    -> select total;
    -> end$
Query OK, 0 rows affected (0.03 sec)


mysql> call p7(100)$
+-------+
| total |
+-------+
|  5050 |
+-------+


mysql> show create procedure p7$
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure
                                                                                                                         | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p7        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p7`(in n int)
begin
declare total int default 0;
while n>0 do
set total=total+n;
set n=n-1;
end while;
select total;
end | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

在这里插入图片描述

repeat循环

repeat是有条件的循环控制语句,当满足条件的时候退出循环。具体语法为:

#先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
repeat
	sql语句;
	until 条件
end repeat;

在这里插入图片描述

repeat循环案例

计算从1累加到n的值，n为传入的参数

mysql> create procedure p8(in n int)
    -> begin
    -> declare total int default 0;
    -> repeat
    -> set total=total+n;
    -> set n=n-1;
    -> until n<=0
    -> end repeat;
    -> select total;
    -> end$
Query OK, 0 rows affected (0.03 sec)


mysql> call p8(100)$
+-------+
| total |
+-------+
|  5050 |
+-------+


mysql> show create procedure p8$
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure
                                                                                                                               | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p8        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p8`(in n int)
begin
declare total int default 0;
repeat
set total=total+n;
set n=n-1;
until n<=0
end repeat;
select total;
end | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

在这里插入图片描述

loop循环

loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP可以配合一下两个语句使用:

  • leave：配合循环使用，退出循环。
  • iterate：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
[begin_label:] loop
	sql语句
end loop [end_label];

leave label;  #退出指定标记的循环体
iterate label;  #直接进入下一次循环

在这里插入图片描述

loop循环案例

1. 计算从1累加到n的值，n为传入的参数

mysql> delimiter $
mysql> create procedure p9(in n int)
    -> begin
    -> declare total int default 0;
    -> label:loop
    -> if n<=0 then
    -> leave label;
    -> end if;
    -> set total=total+n;
    -> set n=n-1;
    -> end loop label;
    -> select total;
    -> end$
Query OK, 0 rows affected (0.00 sec)


mysql> call p9(100)$
+-------+
| total |
+-------+
|  5050 |
+-------+


Query OK, 0 rows affected (0.01 sec)

mysql> show create procedure p9$
+-----------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure
                                                                                                                                                              | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p9        | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p9`(in n int)
begin
declare total int default 0;
label:loop
if n<=0 then
leave label;
end if;
set total=total+n;
set n=n-1;
end loop label;
select total;
end | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

在这里插入图片描述
2. 计算从1到n之间的偶数累加的值，n为传入的参数值。

mysql> create procedure p10(in n int)
    -> begin
    -> declare total int default 0;
    -> label:loop
    -> if n<=0 then
    -> leave label;
    -> end if;
    -> if n%2=1 then
    -> set n=n-1;
    -> iterate label;
    -> end if;
    -> set total=total+n;
    -> set n=n-1;
    -> end loop label;
    -> select total;
    -> end$
Query OK, 0 rows affected (0.01 sec)

mysql> call p10(4)$
+-------+
| total |
+-------+
|     6 |
+-------+


mysql> call p10(6)$
+-------+
| total |
+-------+
|    12 |
+-------+


mysql> show create procedure p10$
+-----------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure

                            | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| p10       | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `p10`(in n int)
begin
declare total int default 0;
label:loop
if n<=0 then
leave label;
end if;
if n%2=1 then
set n=n-1;
iterate label;
end if;
set total=total+n;
set n=n-1;
end loop label;
select total;
end | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

在这里插入图片描述

游标cursor

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

#声明游标
declare 游标名 cursor for select语句;

#打开游标
open 游标名;

#获取游标记录
fetch 游标名 into 变量,[变量];

#关闭游标
close 游标名;

在这里插入图片描述

游标案例

根据传入的参数uage，来查询用户表tb_user中所有的用户年龄<=uage的用户姓名(name)和专业(profession)，并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中。
在这里插入图片描述

条件处理程序handler

条件处理程序(handler)可以用来解决在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为:

declare handler_action handler for condition_value [,condition_value]... sql语句;


handler_action
	continue：继续执行当前程序
	exit：终止执行当前程序

condition_value
	sqlstate：状态码，如02000
	sqlwarning：所有以01开头的sqlstate代码的简写
	not found：所有以02开头的sqlstate代码的简写
	sqlexception：所有没有被sqlwarning或not found捕获的sqlstate代码的简写

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是in类型的。具体语法如下:

mysql> create function 存储函数名([参数列表])
	-> returns type [characteristic...]
	-> begin
	->		sql语句
	->		return...;
	-> end;

characteristic说明:
	deterministic：相同的输入参数总是产生相同的结果
	no sql：不包含sql语句
	reads sql data：包含读取数据的语句，但不包含写入数据的语句

在这里插入图片描述

存储函数案例

1. 计算从1累加到n的值，n为传入的参数

mysql> create function fun1(n int)
    -> returns int deterministic
    -> begin
    -> declare total int default 0;
    -> while n>0 do
    -> set total=total+n;
    -> set n=n-1;
    -> end while;
    -> return total;
    -> end$
Query OK, 0 rows affected (0.03 sec)


mysql> select fun1(100)$
+-----------+
| fun1(100) |
+-----------+
|      5050 |
+-----------+


mysql> show create function fun1$
+----------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Function | sql_mode                                                                                                              | Create Function
                                                                                                                                                    | character_set_client | collation_connection | Database Collation |
+----------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| fun1     | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `fun1`(n int) RETURNS int
    DETERMINISTIC
begin
declare total int default 0;
while n>0 do
set total=total+n;
set n=n-1;
end while;
return total;
end | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+----------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

在这里插入图片描述

触发器

  • 触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后触发并执行触发器中定义的SOL语句。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。
  • 使用别名old和new来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发
触发器类型old和new
insert型触发器new表示将要或者以及新增的数据
update型触发器old表示修改之前的数据，new表示将要或者以及新增的数据
delete型触发器old表示将要或者已经删除的数据

在这里插入图片描述

创建

mysql> create trigger 触发器名
	-> before/after insert/update/delete
	-> on 表名 for each row   -- 行级触发器
	-> begin
	-> 		sql语句
	-> end;

查看

mysql> show triggers;

删除

mysql> drop trigger 触发器名;

在这里插入图片描述

触发器案例

通过触发器记录 tb_user 表的数据变更日志，将变更日志插入到日志表user_logs中,包含增加,修改,删除;
在这里插入图片描述
在这里插入图片描述

插入数据时触发

在这里插入图片描述

修改数据时触发

在这里插入图片描述

删除数据时触发

在这里插入图片描述
在这里插入图片描述

^～^前行者～～～
关注
  • 22
    点赞
  • 踩
  • 22
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
Mysql事项,视图,函数,触发器命令(详解)
12-16
事项开启和使用 //修改表的引擎 alter table a engine=myisam; //开启事务 begin; //关闭自动提交 set autocommit=0;...update bank set money=money-100 where bid=1;...update bank set money=mone
关于MySQL中的查询开销查看方法详解
01-21
MySQL逻辑架构 如果能在头脑中构建一幅MySQL各组件之间...所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。 最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引
mysql存储过程与函数、触发器、游标、变量等知识点详解
m0_小淼的博客
10-30 874
mysql变量、存储过程、游标、触发器详解
MySQL-SQL存储函数以及触发器详解
一起努力共同进步，为了未来一起奋斗吧！
06-24 3493
触发器是与表有关的数据库对象，指在之前(BEFORE)或之后(AFTER)，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录数据校验等操作。使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。
MySQL中存储过程和存储函数以及触发器的基础知识
weixin_67201964的博客
12-14 524
MySQL中提供存储过程与存储函数机制，我们姑且将存储过程和存储函数合称为存储程序。与一般的SQL语句需要先编译然后立即执行不同，存储程序是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，当用户通过指定存储程序的名字并给定参数（如果该存储程序带有参数）来调用才会执行。存储程序就是一条或者多条SQL语句和控制语句的集合，我们可以将其看作MySQL的批处理文件，当然，其作用不仅限于批处理。当想要在不同的应用程序或平台上执行相同的功能一段程序或者封装特定功能时，存储程序是非常有用的。
MySQL之数据库编程（创建存储函数&&设置触发器）
遇安的博客
08-22 2536
存储函数也是过程式对象之一，与存储过程相似。他们都是由SQL和过程式语句组成的代码片段，并且可以从应用程序和SQL中调用。然而，他们也有一些区别： 1、存储函数没有输出参数，因为存储函数本身就是输出参数。 2、不能用CALL语句来调用存储函数。 3、存储函数必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含于存储过程中
详解MySQL中存储函数创建与触发器设置
小魏快起床的博客
08-23 1005
在mysql触发器中的sql语句可以关联表中的任意列，但不能直接使用列的名称去标志，这样系统会混淆，因为激活触发器的语句可能已经修改、删除或添加了新的列名，而列的久名同时存在。例如：假设BookStore数据库中有一个与Members表结构完成一样的表member_b,创建一个触发器，在Member表中添加数据的时候，调用存储过程，将member_b表中的数据与Members表同步。存储函数创建完成后，调用存储函数的方法和使用系统提供的内置函数相同，都是使用select关键字。而update可以同时使用。
Mysql小结( 存储过程&存储函数&触发器 )
削尖螺丝刀的钉文
12-14 412
Mysql小结(存储过程&存储函数&触发器) 存储过程： 特点： 入参必须定义参数种类，类型in被call则直接出结果，其余两种都类型得出的被封装在对象里，需另外再select这个对象才会出结果。 创建存储过程模板如下 —— 参数种类： in，out ，inout 参数：即入参的数据 数据类型： 即规定入参数据的类型，如varchar,int… CREATE PROCEDURE 存储过程名称( 参数的种类1 参数1 数据类型1
MySql零基础入门到精通(资料集)SQL语句+触发器+存储过程+安装卸载+导入导出+性能优化
09-21
第二阶段---MySQL存储过程实例教程.doc 第二阶段---MYSQL存储过程技术ppt 第二阶段---MySQL导入导出数据库文件.doc 第二阶段---Mysgl数据类型(字段)介绍.doc 第二阶段---MySQL样例数据库、操作库操作表实例教程.doc ...
MySQL架构及优化原理详解
02-24
存储过程、触发器、视图等3>存储引擎:负责MySQL中的数据存储和提取，和Linux下的文件系统类似，每种存储引擎都有其优势和劣势，中间的服务层通过API与存储引擎通信，这些API接口屏蔽不同存储引擎间的差异MySQL整个...
mysql中event的用法详解
12-15
通过单独或调用存储过程使用，在某一特定的时间点，触发相关的SQL语句或存储过程。 二、适用范围对于每隔一段时间就有固定需求的操作，如创建表，删除数据等操作，可以使用event来处理。例如：使用event在每月的1日...
mysql中varchar与bigint直接比较会导致精度丢失以至于匹配到多行数据
卖炫迈的小男孩的博客
05-09 421
如果联表的两个字段类型不一样，数据库会对两个不同类型的字段进行隐式的类型转换，导致索引失效。varchar与bigint进行比较的时候，mysql先转换为双精度浮点数double再进行比较的，由于bigint的位数超过了16位所以会导致丢失一部分精度，所以会匹配上多行数据。而在mysql中double的精度的精度为16位有效数据,因此如果要比较varchar和bigint类型的话，如果允许的话就改为统一的类型进行比较。也可以将bigint转换为varchar进行比较，或者两者都使用更高精度的Decimal类
java项目之智慧图书管理系统设计与实现(springboot+vue+mysql)
sinat_26552841的博客
05-08 320
智慧图书管理系统设计与实现的主要使用者分为： 管理员权限操作的功能包括对注册读者信息的管理，对图书，对图书留言，对图书借阅记录，对论坛帖子等信息的管理。 读者权限操作的功能包括借阅图书，对图书进行留言或收藏图书，查看论坛帖子信息，查看借阅的图书等。
MySql软件安装
最新发布
m0_62731842的博客
05-12 198
1.打开mysql官网网址 MySQL :: Download MySQL Community Server 2.本次针对版本8的图形化界面安装，下载成功后接下来对MySQL进行安装 3.图形化下载后有一个MSI文件 4.我们安装典型即可，选择第一个 5.选择数据库信息存放的路径，我默认放在C盘不做改变 6.MySQL平时作为一些应用的数据提供者，选择服务类型即可 7.端口号（暴露的端口号便于我们连接数据库，进行相关数据操作）可以修改，但我们平常使用的端口号是33
一 MySQL、SQL Server、Oracle三者的区别
m0_74216169的博客
05-11 54
Oracle、SQL Server和MySQL作为三种广泛使用的数据库管理系统，它们各自有不同的特点和优势。SQL Server适合中小型企业和Windows平台的应用。Oracle数据库以其强大的功能和高安全性而著称。Oracle适合对性能和安全性要求极高的大型系统。而MySQL则适合成本敏感和轻量级应用的需求。它支持高可扩展性和高可用性的大型企业应用。
MySQL中的ON DUPLICATE KEY UPDATE和REPLACE
MichaelZ的博客
05-08 513
在 MySQL 中，ON DUPLICATE KEY UPDATE 和 REPLACE 语句都可以用来处理插入数据时主键或唯一键冲突的情况，但它们在处理冲突的方式上有所不同。其中column1 = value1, column2 = value2, …表示需要更新的列和对应的新值。适用于完全替换冲突行的情况，包括所有列的值。选择使用哪种方法取决于具体的需求和预期的操作结果。适用于需要在冲突时更新指定列的值，而保留其他列的原始值的情况。表示要插入的列和对应的值。
MySQL监控与诊断：从SHOW命令到第三方监控工具
Dxy1239310216的博客
05-09 752
MySQL的监控和诊断是确保数据库性能和稳定性的关键步骤。通过使用MySQL的内置工具（如SHOW命令和INFORMATION_SCHEMA）以及第三方监控工具，我们可以获取全面的监控数据，并进行有效的性能调优和故障诊断。在选择监控工具时，我们需要根据实际需求进行评估和选择，以确保获得最佳的监控和诊断效果。
MySQL中的存储过程、存储函数、触发器和视图（View）的用途
07-25
存储过程、存储函数、触发器和视图在MySQL中都有各自的用途。 存储过程是一组预编译的SQL语句，可以在数据库中创建和存储，然后在需要的时候调用。它们的优点是可以实现模块化的设计，可以被多次调用，执行效率较高...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

打赏作者

^～^前行者～～～

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

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

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

打赏作者

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

抵扣说明：

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

余额充值