数据库笔记04 MySQL中存储过程，变量，函数，触发器

数据库笔记04 MySQL中存储过程，变量，函数，触发器

一.存储过程：

MySQL中的存储过程是在数据库中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。在创建存储过程时需要权限，在图形化界面中点击对应数据库中的存储过程，右键创建存储过程，使用到的语法如下：

delimiter $$
create procedure `存储过程名` (in 输入参数 输入参数数据类型,out 输出参数 输出参数类型)
	begin
		存储过程体（语句以;结尾）
	end$$
delimiter ;

语句解析：

• delimiter是mysql中定义结束标记的，在mysql客户端中结束标记默认是分号，一次输入语句较多且语句中有分号这种情况就需要指定一个特殊的结束符了，delimiter $$ 表示mysql用$$来表示语句结束，过程结束后肯定会有一句delimiter表示恢复默认
• 在begin和end$$之间封装多条mysql语句来实现对应的功能，实际上就相当于一个方法

调用存储过程：

CALL 存储过程名 (参数);

存储过程分为三类参数：

• 1）in 输入参数： 在对应的PROCEDURE后面加上IN id int也就是传参id，类型为int
• 2）out 输出参数类似于返回值： 在对应的PROCEDURE后面加上OUT res int也就是返回res，类型为int，在begin和end中在查询对应的过程中要使用select语句：select... INTO res将结果传出
  相应的在调用时可以定义会话变量：set @r = 0; 会话变量可以在调用时进行接收; 接受完成之后使用select @r可以对结果进行查看
• 3）inout 输入输出参数，两个都干

条件判断与循环：

• 在存储过程中使用对应的条件判断：IF THEN ELSEIF ELSE END IF其中的条件判断时没有==一说，要使用对应的=进行判断
• 在存储过程中使用while循环：WHILE 条件 DO 动作
• 在存储过程中的循环中：leave相当于break，iterate相当于continue

注意事项：
在begin和end中给OUT赋值除了使用查询时将结果INTO输出参数之外，还可以使用SET设定对应的值

二.MySQL中的变量：

MySQL中共可以定义三种变量，分别是：

• 1）内置变量： 内置变量也叫做全局变量，可以在任何会话中来访问
  查看数据库中全部的内置变量：show VARIABLES;
  查看某个内置变量：select @变量名
• 2）会话变量： 变量只存活于一次会话中，会话结束了，变量也就消失了
  会话： 客户端与数据库服务器之间建立的一次连接
  会话变量的定义： set @变量名
  注意：会话变量在语句中作为临时变量的定义：@变量:=值
• 3）局部变量： 定义在存储过程和函数中的变量
  局部变量是在函数或循环中需要定义的临时变量语法：语法举例：declare i INT DEFAULT 1;

二.函数：

MySQL中的函数实际上与存储过程会有功能上的重叠，但是函数在MySQL的作用主要是在辅助编写trigger和存储过程上应用的较多，其中函数分为两种：内置函数和自定义函数两种，这两种函数用到的较多的是内置函数，而自定义函数由于与存储过程在功能上重叠较多，因此自定义函数应用较少
首先介绍一些函数的通用知识：

• 函数的调用：使用select来调用
• 直接调用： SELECT 2+3>5; //返回1，表示true
• 调用函数：SELECT SIN(3.14); //接近0，表示3.14的正弦值
  注：单独调用函数意义不大，注意配合mysql语句

下面将介绍这两种函数

1.MySQL中的内置函数：

1）数学函数：
向下取整：SELECT FLOOR(字段) FROM 表
2）字符串函数：
常用的有：

SELECT SUBSTRING('我爱你中国',1,3); //返回我爱你，即从第几个字符开始截取几个字符作为子字符串

大小写转换：

SELECT LOWER(表.字段) FROM 表; //转小写
SELECT UPPER(表.字段) FROM 表; //转大写
SELECT char_length(字段) FROM 表; //查看字段中的字符长度
SELECT length(字段) FROM 表; //查看字段中的字节长度

3）日期函数：

SELECT NOW(); //取到当前详细时间
SELECT DATE(NOW()); //获取当前日期
SELECT TIME(NOW()); //获取当前时间
SELECT DATE_FORMAT(NOW(),format()); //将日期类型转换成字符串类型，format中年%Y，月%M，日%D
SELECT STR_TO_DATE(字段,format()); //将字段中的字符串转换成格式规定的日期类型
计算两个日期的间隔：TIMESTAMPDIFF(需要计算的插值单位年/月/日等，日期1，日期2); //日期1，2必须是日期类型

2.自定义函数：

自定义函数基本上很少自定义，一般来说内置函数够用，自定义函数的语法格式如下：

DELIMITER $$
CREATE
    FUNCTION `数据库名`.`函数名`()
    RETURNS TYPE //返回值类型
    BEGIN
	函数体实现细节
    END$$
DELIMITER ;

三.触发器：

MySQL中的触发器实际上类似于JS中的监听器：触发器监听的是对于表中数据的增删改这些操作
定义触发器的语法：

TRIGGER 监听数据库名.触发器名 BEFORE/AFTER等 //触发器类型或出发时间选择 
DELETE //触发器要监测的触发条件的动作
ON 数据库名.表名	//被监测的表
FOR EACH ROW BEGIN //行级触发，没操作一行都会触发
	BGIN 	
		触发器方法体
	END$$

触发器中的old和new：
1）old字段：可以获取到被监听的表中的字段旧值
2）new字段：可以获取到被监听的表中更新后的字段新值，比如插入新值或者修改旧值
举例：比如在t1表中插入被监听表t2中变化的新值：INSERT INTO t1(id) VALUES(new.id); //被监听的是t2
比如在更新t2表中的值时：UPDATE t2 SET name=new.username WHERE id=old.id; //被监听的是t1