MySQL | 存储《康师傅MySQL从入门到高级》笔记

文章目录

存储 Stored
- 1. 存储过程 PROCEDURE
- - （1）创建存储过程
  - （2）调用存储过程
  - （3）查看存储过程
  - （4）修改存储过程
  - （5）删除存储过程
- 2. 存储函数 FUNCTION
- - （1）创建存储函数
  - （2）调用存储函数
  - （3）查看存储函数
  - （4）修改存储函数
  - （5）删除存储函数
存储的补充知识
- 1. 变量
- - （1）系统变量
  - - ①全局系统变量（简称：全局变量）
    - ②会话系统变量（简称：local变量）
  - （2）用户自定义变量
  - - ①会话用户变量
    - ②局部变量
- 2. “异常”处理
- - （1）定义条件
  - （2）处理程序
- 3. 流程控制
- - （1）条件判断语句`IF` `CASE`
  - - ①IF
    - ②CASE
  - （2）循环语句`LOOP` `WHILE` `REPEAT`
  - - ①LOOP（一般用于实现简单的"死"循环。（通常要结合LEAVE使用））
    - ②WHILE（先判断后执行）
    - ③REPEAT（先执行后判断，无条件至少执行一次）
  - （3）跳转语句`ITERATE` `LEAVE`
  - - ①`LEAVE`类似于`break`
    - ②`ITERATE`类似于`continue`
- 4. 触发器
- - （1）触发器的创建
  - （2）查看触发器
  - （3）删除触发器
  - （4）触发器的优缺点

自己重新梳理一遍，也是一个消化的过程。学习一定要注重输入和输出，如果只输入的话，很多时候只是大脑此刻懂了，但并未记住。这样在实际做题或应用时，就无从下笔了。

存储 Stored

存储过程和存储函数可以将复杂的SQL逻辑封装在一起，应用程序无需关注存储过程和函数内部复杂的SQL逻辑，只需要简单地调用存储过程和函数即可。

1. 存储过程 PROCEDURE

思想

一组经过预先编译的SQL语句的封装

执行过程

存储过程预先存储在MySQL服务器上
需要执行的时候，客户端只需要向服务器发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列SQL语句全部执行。

优点：

可以一次编译多次使用——减少开发人员的压力
良好的封装性——减少失误，提高效率
减少网络传输量
存储过程的安全性强——减少SQL语句暴露的风险

缺点：

可移植性差
调试困难
存储过程的版本管理很困难
不适合高并发的场景

使用

像使用函数一样简单

存储过程没有返回值

（1）创建存储过程

DELIMITER $  -- 新的结束标记

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型, IN|OUT|INOUT 参数名 参数类型, ...)

	[characteristics ...]  -- 存储过程的约束条件
	--LANGUAGE SQL
	--| [NOT] DETERMINISTIC
	--| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
	--| SQL SECURITY { DEFINER | INVOKER }  -- 安全级别（定义者可以调用 | 有权限就可以调用），默认为DEFINER
	--| COMMENT 'string'

	BEGIN
		查询语句
	END $

DELIMITER ;  -- 恢复结束标记

常用的两种结束标记：$、//
SET：赋值语句，对变量进行赋值。
SELECt INTO：将赋值语句的查询结果存放到变量中，也就是为变量赋值。
五种输入参数

①没有参数（无参数无返回）
②IN（由参数无返回）
③OUT（无参数有返回）
④IN和OUT（有参数有返回）
⑤INOUT（有参数有返回）

（2）调用存储过程

CALL 存储过程名(实参列表)

（3）查看存储过程

①查看存储过程的创建信息

SHOW CREATE PROCEDURE 存储过程名

②查看存储过程的状态信息

SHOW PROCEDURE STATUS [LIKE '...']

（4）修改存储过程

ALTER PROCEDURE 存储函数名

（5）删除存储过程

DROP PROCEDURE IF EXIST 存储函数名；

2. 存储函数 FUNCTION

用于查询
有且仅有一个返回值

（1）创建存储函数

DELIMITER //

CREATE FUNCTION 存储函数名(参数名, 参数类型)
	RETURNS 返回值类型
			# 为了避免报错，需要加上下面三行
	        DETERMINISTIC  --
	        CONTAINS SQL -- 包含SQL
	        READS SQL DATA
	
	BEGIN
		RETURN(SELECT查询语句);
	END //

DELIMITER;  --把结束符号重新设为`;`

或

# 为避免报错，需要加这一句
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER //

CREATE FUNCTION 存储函数名(参数名1 参数类型, 参数名2 参数类型, ...)
	RETURNS 返回值类型

	BEGIN
		RETURN(SELECT查询语句);
	END //

DELIMITER;  --把结束符号重新设为`;`

（2）调用存储函数

SELECT 存储函数名();  -- 调用函数

SET @emp_id := 102;  -- 定义变量
# SET @emp_id = 102;  `:=`和`=`都可以
SELECT 存储函数名(@emp_id);  --调用函数

巧记：因为存储函数适用于查询，所以调用它也用SELECT 。

（3）查看存储函数

①查看存储函数的创建信息

SHOW CREATE FUNCTION 存储函数名

②查看存储函数的状态信息

SHOW FUNCTION STATUS [LIKE '...']

（4）修改存储函数

ALTER FUNCTION 存储函数名

（5）删除存储函数

DROP FUNCTION IF EXIST 存储函数名；

存储的补充知识 1. 变量

两种赋值符号都可以，效果一样

（1）系统变量

系统变量用@@开头

两种默认值

编译MySQL时参数的默认值
my.iniMySQL配置文件中的参数值

修改系统变量的值

在MySQL服务运行期间，使用SET命令重新设置系统变量的值。
修改my.iniMySQL配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）

默认为会话系统变量

①全局系统变量（简称：全局变量）

global关键字

max_connections：只能是全局系统变量，限制服务器的最大连接数
查看所有全局变量SHOW GLOBAL VARIABLES;
查看满足条件的部分全局变量SHOW GLOBAL VARIABLES LIKE '%标识符%';
查看指定全局变量SELECT @@global.变量名;

为全局变量赋值

方式1

SELECT @@global.变量名;
SET GLOBAL 变量名=变量值;

方式2

SELECT @@global.变量名;
SET @@global.变量名=变量值;

②会话系统变量（简称：local变量）

session关键字

会话系统变量的初始值是全局系统变量值的复制。
pseudo_thread_id：作用于只能是当前会话，用于标记当前会话的MySQL连接ID。
查看所有会话变量SHOW SESSION VARIABLES;或SHOW VARIALBLES;（因为变量默认是会话系统变量）
查看满足条件的部分会话变量SHOW SESSION VARIABLES LIKE '%标识符%';
查看指定会话变量SELECT @@session.变量名;或SELECT @@变量名;

为会话变量赋值

方式1

SELECT @@session.变量名;
SET SESSION 变量名=变量值;

方式2

SELECT @@session.变量名;
SET @@session.变量名=变量值;

（2）用户自定义变量

用户自定义变量用@开头：会话用户变量（不用指明类型），局部变量（需要指明类型）

①会话用户变量

定义与赋值（需要加@，不需要指定类型）
1.手动赋值

SET @用户变量 = 值；
SET @用户变量 := 值；

2.赋表中的字段值

SELECT @用户变量 := 表达式 [FROM 等字句];
SELECt 表达式 INTO @用户变量 [FROM 等字句];

作用域
只对当前会话有效
可以加在会话的任何地方
查看用户变量
```
SELECt @用户变量
```

②局部变量

定义（需要指定类型）

DECLARE 局部变量名 类型 [default 值];
# 如果没有default子句，初始值为NULL

赋值（不需要加@）
1.手动赋值

SET 变量名=值
SET 变量名:=值

2.赋表中的字段值

SELECT 字段名或表达式 INTO 变量名 FROM 表;

作用域

只在BEGIN和END语句块中有效，且只能放在第一句。
只能在存储过程和函数中使用
使用变量
```
SELECt 局部变量名
```

DELIMITER //

CLEAR PROVEDURE/FUNCTION 存储过程名/存储函数名

BEGIN
	# 声明局部变量
	DECLARE 局部变量名 类型 DEFAULT 默认值;

	# 为局部变量赋值
	SET 局部变量名 = 值;
	SELECT 字段名 INTO 局部变量名 FROM 表名 WHERe 筛选条件;
	
	# 查看局部变量的值
	SELECt 变量1, 变量2, ...;
END

DELIMITER ;

2. “异常”处理

为了避免程序一报错就停止运行，可以实现将可能发生的错误列出来，当错误出现时，只用报相应的错误代码，而保证程序能够顺利执行完毕。

（1）定义条件

即：给实现定义程序执行过程中可能遇到的问题命名。将一个错误名字和指定的错误条件关联起来。

# MySQL_error_code
DECLARE 错误名 CONDITION FOR 数值类型错误码

# sqlstate_value
DECLARE 错误名 CONDITION FOR SQLSTATE 字符串类型错误码

例：在ERROR 1418(HY000)中：

1418是MySQL_error_code（数值类型错误代码），
HY000是sqlstate_value（长度为5的字符串类型错误代码）

DECLARE field_Not_Be_NULL CON

（2）处理程序

即：定义在遇到问题时应当采取的处理方式，保证存储过程或函数在遇到警告或错误时能继续执行。

增强存储程序处理问题的能力，避免程序异常停止运行。

DECLARE 处理方式 HANDLER FOR 错误类型 处理语句

处理方式

CONTINUE：遇到错误不处理，继续执行。
EXIT：遇到错误马上退出

错误类型

SQLSTATE ‘字符串错误码’：表示长度为5的sqlstate_value类型的错误代码
MySQL_error_code：匹配数值类型错误代码
错误名称：表示DECLARE...CONDITION定义的错误条件名称
SQLWARNING ：匹配所有以01开头的SQLSTATE错误代码；
NOT FOUND ：匹配所有以02开头的SQLSTATE错误代码；
SQLEXCEPTION ：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码；

处理语句
SET 变量 = 值

BEGIN...END

定义处理程序的几种方式，代码如下：

#方法1：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

#方法2：捕获mysql_error_value
DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';

#方法3：先定义条件，再调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';

#方法4：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

#方法5：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

#方法6：使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

3. 流程控制（1）条件判断语句IF CASE ①IF

用在BEGIN...END中

DELIMITER //

CREATE PROCEDURE/FUNCTION 存储过程名/存储函数名()

	[标记名:]BEGIN
	
		IF ...
			THEN ...  -- LEAVE 标记名
		ELSEIF ...
			THEN ...
		ELSE
		END IF;
	
	END //

DELIMITER ;

②CASE

用于BEGIN...END中

情况1（类似与switch）

CASE 表达式
WHEN 值1 THEN 结果1或语句1(语句1的结尾要加;)
WHEN 值2 THEN 结果2或语句2(语句1的结尾要加;)
...
ELSE 结果n或语句n(语句n的结尾要加;)
END CASE

情况2（类似于多重IF）

CASE 
WHEN 条件1 THEN 结果1或语句1(语句1的结尾要加;)
WHEN 条件2 THEN 结果2或语句2(语句1的结尾要加;)
...
ELSE 结果n或语句n(语句n的结尾要加;)
END CASE

（2）循环语句LOOP WHILE REPEAT

三种循环都可以省略名称。
但如果循环中添加了循环控制语句（LEAVE或ITERATE），则必须添加名称。

①LOOP（一般用于实现简单的"死"循环。（通常要结合LEAVE使用））

DELIMITER //

CREATE PROCEDURE/FUNCTION 存储过程名/存储函数名()

BEGIN

	# 定义变量，用于记录循环次数
	DECLARE loop_count INT DEFAULT 0;

	# ①初始化循环条件
	SELECT ...

	[标记名:]Loop

		# ②循环条件（何时结束循环）
		[IF ... THEN LEAVE [标记名];
		END IF;]
		[IF ... THEN ITERATE [标记名];
		END IF;]
		# ③循环体——循环要完成的任务
		... -- 根据题目要求写
		
		# ④迭代条件
		...;  -- 重新计算循环条件（与初始化循环条件一致）
		
		SET loop_count = loop_count +1;  -- 循环次数+1

	END LOOP loop名;
	
END //

DELIMITER ;

②WHILE（先判断后执行）

DELIMITER // 

CREATE PROCEDURE/FUNCTION 存储过程名/存储函数名()

BEGIN
	# ①初始化循环条件

	# ②循环条件
	[标记名]:WHILE 循环条件 DO
		# ③循环体
		# ④迭代条件
		[IF ...
			LEAVE 标记名;
		END IF;]
		
	END WHILE;
	
END //

DELIMITER ;

③REPEAT（先执行后判断，无条件至少执行一次）

DELIMITER //

CREATE PROCEDURE/FUNCTION 存储过程名/存储函数名()

BEGIN

	REPEAT
		循环
	UNTIL  -- 结束循环的条件。注意：这句没有`;`	
	END REPEAT;

END //

DELIMITER ;

（3）跳转语句ITERATE LEAVE ①LEAVE类似于break

LEAVE 标记名

②ITERATE类似于continue

只能用在LOOP/REPEAT/WHILE循环语句中

ITERATE 标记名

4. 触发器

事件：用户的动作或触发某项行为，包括INSERT, UPDATE, DELETE。

当执行INSERT, UPDATE, DELETE事件时，会自动激发触发器执行相应的操作。

（1）触发器的创建

DELIMITER //

CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名  -- `表名`为触发器监控的对象
FOR EACH ROW

BEGIN
	触发器执行的语句块;
	-- VALUE(NEW.字段名)
	-- VALUE(OLD.字段名)
END //

DELIMITER ;

BEFORE 表示在事件之前触发
AFTER 表示在事件之后触发
INSERT 表示插入记录时触发
UPDATE 表示更新记录时触发
DELETE 表示删除记录时触发
NEW关键字用于调用最新插入的数据（用在触发器执行的语句块的VALUE语句中，如VALUE(NEW.字段名)可用于查看该字段最新插入的数据值。）
OLD关键字用于调用最近删除的数据（用在触发器执行的语句块的VALUE语句中，如VALUE(OLD.字段名)可用于查看该字段最近删除的数据值。）

验证触发器是否起作用
注：与上方创建触发器的代码中的BEGIN...END中对应

BEGIN
触发器执行的语句块

这个语句块可用于将触发后的操作记录在指定表中。
.
这样，当被触发器监控的对象触发操作时，这个指定表就会记录相应的内容。
.
通过SELECT * FROM 指定表;，可以查看是否触发了触发器。

END

（2）查看触发器

查看当前数据库的所有触发器的定义
SHOW TRIGGERS
查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
从系统库information_schema的TRIGGER表中查询“salary_check_trigger”触发器的信息
SELECt * FROM information_schema.TRIGGERS;

（3）删除触发器

DROP TRIGGER IF EXISTS 触发器名称

（4）触发器的优缺点

优点

确保数据的完整性
帮助我们记录操作日志
在操作数据前，对数据进行合法性检查

缺点

可读性差（触发器由事件驱动，不受应用层的控制）
相关数据的变更，可能会导致触发器出错
如果在子表中定义了外键约束，并且外键指定了DML操作，此时修改父表也会引起子表的相应操作。但是，基于子表的相应触发器不会被激活。

MySQL | 存储《康师傅MySQL从入门到高级》笔记

Java相关栏目本月热门文章