LiangGaRy-学习笔记-Day30

1、MySQL的存储过程

1.1、存储过程的介绍

mysql5开始之后会支持存储过程

它就想shell函数，有固定的语法；
sql语句针对一条、或者是多个表的单个语句；
也支持多条sql语句

存储过程的概念：

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它

简单理解就是像极了shell脚本

存储过程的好处：

执行速度非常快；–>提前写好，直接调用就可以了
复用性强，减少网络通信量
可维护性高
安全性高
分布式强

存储过程的缺点：

可移植性很差
对于简单的SQL语句、存储过程没有任何的优势
团队标准、维护
大并发不能使用过多涉及运算

存储过程的特点：

有输入输出参数,可以声明变量,有if/else/case/while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能
函数的普通特性:模块化,封装,代码复用
速度快,只有首次执行需要经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤

1.2、存储过程的语法

语法：

#首先需要修改mysql的默认结束符-->delimiter命令
delimiter 自定义结束符
create procedure 过程名 （参数1，参数2，参数3，....）
begin
	sql 语句
end 自定义的结束符
delimiter ；		#回到原来mysql的结束符；

注意：使用存储过程，需要修改mysql的默认结束符–>delimiter命令

设定变量：set @p_in=1

#修改默认的结束符
mysql> delimiter %
	#调用sql语句的时候结束符就要改用%
mysql> show databases%
............

#设置一个变量
mysql> set @p_in=11
    -> %
Query OK, 0 rows affected (0.00 sec)

创建存储过程

#创建一个存储过程
mysql> use GAME
mysql> create procedure selGM()
    -> begin
    -> select * from game_characters;
    -> end ?
Query OK, 0 rows affected (0.01 sec)

调用存储过程

#调用存储过程
mysql> call selGM ?

#调用存储过程
mysql> call selGM() ;

删除存储过程

#删除存储过程
mysql> drop procedure selGM;
Query OK, 0 rows affected (0.00 sec)

1.3、存储过程的选项参数

in参数–>用于传入参数

读取外部变量值，且有效范围仅限存储过程内部

#先进入一个数据库中
mysql> use GAME;
Database changed

#修改默认的结束符
mysql> delimiter //

#创建一个存储过程
mysql> create procedure demo1(in abc int)		#给定了参数abc
    -> begin
    -> select abc;						#第一次输出就是abc
    -> set abc=66;						#然后在复制给到abc
    -> select abc;						#再调用出来
    -> end;//
Query OK, 0 rows affected (0.00 sec)
	#调用存储过程，发现出来的数值会是两个数值
mysql> delimiter ;
mysql> call demo1(100);
+------+
| abc  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

+------+
| abc  |
+------+
|   66 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

变量的复制与查询

#给定一个变量；
mysql> set @y=101;
Query OK, 0 rows affected (0.00 sec)

#调用一个数值
mysql> select @y;
+------+
| @y   |
+------+
|  101 |
+------+
1 row in set (0.00 sec)

#把这个数值直接复制给到存储过程demo
mysql> call demo1(@y);
+------+
| abc  |
+------+
|  101 |
+------+
1 row in set (0.00 sec)

+------+
| abc  |
+------+
|   66 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

应用案例：

#修改结束符
mysql> delimiter //

#创建存储过程
mysql> create procedure getGAME(in game_characters int)
    -> begin 
    -> select id,name,role,description where id=2;
    -> end //
Query OK, 0 rows affected (0.01 sec)

out参数–>用于传出参数

特点：不读取外部变量值，在存储过程执行完毕后保留新值；

#切换数据库
mysql> use books;
Database changed

#创建存储过程
mysql> delimiter //
mysql> create procedure pout(out p_out int)
    -> begin
    -> select p_out;
    -> set p_out=2;
    -> select p_out;
    -> end;
    -> //
Query OK, 0 rows affected (0.13 sec)

#调用存储过程
mysql> delimiter ;
mysql> set @p_out=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call pout(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
1 row in set (0.00 sec)

+-------+
| p_out |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

inout参数

特点：读取外部变量，在存储过程执行完成之后保留新值；

#创建存储过程
mysql> delimiter  //
mysql> create procedure pinout(inout p_inout int)
    -> begin
    -> select p_inout;
    -> set p_inout=2;
    -> select p_inout;
    -> end
    -> //
Query OK, 0 rows affected (0.00 sec)

#给一个变量，然后调用存储过程
mysql> delimiter ;
mysql> set @p_inout=1;
Query OK, 0 rows affected (0.00 sec)
	#调用存储过程
mysql> call pinout(@p_inout);
+---------+
| p_inout |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

+---------+
| p_inout |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

应用案例

查询用户的余额
当用户存钱（3000）
余额+用户存储数=@pout
用户的余额

#创建存储过程
mysql> delimiter //
mysql> create procedure t2(n1 int)
    -> begin
    -> set @x=0;
    -> repeat set @x=@x+1;
    -> insert into t2 values(@x);
    -> until @x>n1
    -> end
    -> repeat;
    -> end;//
Query OK, 0 rows affected (0.00 sec)

1.4、存储过程的变量

MySQL中使用declare进行变量定义；

变量的赋值

set 变量

#创建存储过程
mysql> delimiter //
mysql> create procedure dec1()
    -> begin
    -> declare name varchar(200);
    -> set name=(select bName from books where bId=12);
    -> select name;
    -> end//
Query OK, 0 rows affected (0.01 sec)

#调用存储过程
mysql> call dec1//
+-------------------------------+
| name                          |
+-------------------------------+
| Fireworks 4网页图形制作       |
+-------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

存储过程语句的解释

mysql注释—单行注释
/—/多行注释

2、MySQL的触发器

2.1、触发器说明

触发器：在插入、删除或者是修改特定表中的数据时触发执行，他比数据库本身标准的功能有更精细和复杂的数据控制能力

触发器是用户定义在关系表上的一类由事件驱动的特殊过程。一旦被定义，触发器会被自动的保存在数据库服务器中。任何用户对表的增删改操作均由服务器自动激活相应的触发器

触发器的作用：

审计
安全性
实时同步
自动计算

创建触发器的语法

create trigger  触发器的名称 触发的时间 触发的动作 on 表名 for each row 触发器的状态

创建触发器的四个要素

监视的地点
监视的事件
触发的事件

2.2、触发器使用

#创建触发器
mysql> delimiter //

mysql> create trigger delCategory after delete on category for each row delete from books where bTypeId=3;//
Query OK, 0 rows affected (0.01 sec)

#查看触发器
mysql> select bName,bTypeId from books where bTypeId=3;//
+--------------------------------------+---------+
| bName                                | bTypeId |
+--------------------------------------+---------+
| 3D MAX 3.0 创作效果百例              | 3       |
| 3DS MAX 4横空出世                    | 3       |
| 3D MAX R3动画制作与培训教程          | 3       |
| 3D Studio Max 3综合使用              | 3       |
+--------------------------------------+---------+
4 rows in set (0.00 sec)

#查看触发器
mysql> show create trigger delCategory \G
mysql> show triggers \G
*************************** 1. row ***************************
             Trigger: delCategory
               Event: DELETE
               Table: category
           Statement: delete from books where bTypeId=3
              Timing: AFTER
             Created: 2023-08-10 17:52:56.78
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

#删除触发器
mysql> drop trigger delCategory;//
Query OK, 0 rows affected (0.01 sec)
	#再次查看触发器
mysql> show triggers \G
Empty set (0.00 sec)

3、MySQL的事务

3.1、MySQL的事务说明

数据库事务：是由一组SQL语句组成的逻辑处理单元，要么全部成功，要么全部失败；

事务的分类：

隐式事务：该事务没有明显的开启和结束标记，它们都具有自动提交事务的功能；
显示事务：该事务具有明显的开启和结束标记；

书写事务的步骤：

开启事务
编写事务中的SQL语句，insert 、 update 、delete
结束事务
提交事务

3.2、MySQL的事务操作

#创建一个事务
mysql> delimiter //
mysql> start transaction;
    -> update books set bName='liangjiawei' where bId=1;
    -> update books set bName='huangzhiming' where bId=2;
    -> commit;//

#查看事务-->查看数据
mysql> select bName from books where bId=1 or bId=2;//
+--------------+
| bName        |
+--------------+
| liangjiawei  |
| huangzhiming |
+--------------+
2 rows in set (0.00 sec)

更改引擎为innodb

#修改引擎
mysql> alter table books engine=innodb;
    -> //
Query OK, 44 rows affected (0.03 sec)
Records: 44  Duplicates: 0  Warnings: 0
#修改引擎
mysql> alter table category engine=innodb;
Query OK, 10 rows affected (0.33 sec)
Records: 10  Duplicates: 0  Warnings: 0

#查看引擎结构
mysql> show create table books \G
*************************** 1. row ***************************
       Table: books
Create Table: CREATE TABLE `books` (
  `bId` int NOT NULL AUTO_INCREMENT,
  `bName` varchar(255) DEFAULT NULL,
  `bTypeId` enum('1','2','3','4','5','6','7','8','9','10') DEFAULT NULL,
  `publishing` varchar(255) DEFAULT NULL,
  `price` int DEFAULT NULL,
  `pubDate` date DEFAULT NULL,
  `author` varchar(30) DEFAULT NULL,
  `ISBN` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`bId`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8mb3
1 row in set (0.00 sec)

创建一个事务

#创建一个事务
mysql> delimiter  //
mysql> start transaction;
    -> update books set bName='zhangsan' where bId=1;
    -> update books set bName='lisi' where bId=2;//

#查看数据
mysql> delimiter ;
mysql> select bName from books where bId=1 or bId=2;
+----------+
| bName    |
+----------+
| zhangsan |
| lisi     |
+----------+
2 rows in set (0.00 sec)

#返回原来的设定
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select bName from books where bId=1 or bId=2;
+--------------+
| bName        |
+--------------+
| liangjiawei  |
| huangzhiming |
+--------------+
2 rows in set (0.00 sec)

事务的注意事项

确保是innodb
不要提交事务
rollback可以返回

Liang_GaRy