Mysql中的函数和触发器

函数

函数是什么？

多用于查询语句，实现了某种功能；用途与存储过程不同，但语法是类似的；

函数语法

create function 函数名([参数列表]) returns 数据类型

begin

DECLARE 变量；

sql 语句;

return 值;

end;

设置函数可以没有参数

需要单独执行一次，必须设置为true，否则函数就创建不成功。

SET GLOBAL log_bin_trust_function_creators=TRUE;

删除函数

FROP Function 函数名；

注：测试函数时可以使用dual，如下：
select new_count() from dual;
dual是一个虚拟表，一般用于实现查询结构；

以下是一些举例：

SET GLOBAL log_bin_trust_function_creators=TRUE; 
-- 创建函数 不带参数
​
DELIMITER $$
​
CREATE FUNCTION `gradedb`.`admin_count`() RETURNS INT
​BEGINDECLARE admin_num INT DEFAULT 0;SELECTCOUNT(*) INTO admin_num FROM admin;RETURN admin_num;END$$
DELIMITER ;
​
-- 测试函数
SELECT admin_count();
SELECT admin_count() FROM DUAL;  -- dual是虚拟表，用来完善查询结构
​
​
-- 删除函数
DROP FUNCTION admin_count();
​
​
​
-- 有参函数  通过admin id找到admin account
DELIMITER $$
​
CREATE FUNCTION `gradedb`.`findAdminById`(admin_id INT) RETURNS VARCHAR(10)
​BEGINDECLARE a_account VARCHAR(10) ;SELECT account INTO  a_account FROM admin WHERE admin.`id`=admin_id;RETURN a_account;END$$
​
DELIMITER ;
​
​
-- 有参函数 课程转化，通过课程id转化成课程名称
DELIMITER $$
​
CREATE FUNCTION `gradedb`.`class_turn`(class_id INT) RETURNS VARCHAR(10)
​BEGINDECLARE classname VARCHAR(10);SELECT NAME INTO classname FROM class WHERE id = class_id;RETURN classname;END$$
DELIMITER ;
-- 测试函数
SELECT id,class_turn(1) FROM class WHERE id=1; 

触发器

是一种特殊的存储过程，不需要用户直接调用，而是在对表增加，修改，删除，没有查询，操作之前或之后触发自动执行的存储过程。

触发器的特点：

1.与表相关联

触发器定义在特定的表上，这个表称为触发器表。

2.自动激活触发器

当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。

3.不能直接调用

与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。

4.作为事务的一部分

触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中的任何位置回滚。

触发器是行级触发 FOR EACH ROW -- 行级触发 ，就是每操作表中的一行数据，就会调用一次触发器

在创建触发器时，可以通过old和new调用你想要的数据：
old可以表示删除操作时你要删除的那一条数据，以及修改操作中修改前的数据；
new可以表示你增加操作时，新加入的那一条数据；以及修改操作后的数据；

触发器的语法：

CREATE TRIGGER 触发器名称 触发时机 触发事件

ON 表名称

FOR EACH ROW -- 设置行级触发

BEGIN

语句

END;

触发时机指before和after

触发事件：包括insert、delete、update

举例：

-- 创建触发器   删除student ，需要删除他与所选课程的关系
DELIMITER $$
CREATE TRIGGER `gradedb`.`delete_student_class` BEFORE DELETEON `gradedb`.`student` FOR EACH ROW -- 行级触发，每对表中的一行数据进行操作，就会触发一次触发器BEGINDELETEFROM student_class WHERE studentid=old.id;END$$
DELIMITER ;
​
-- 测试触发器
DELETEFROM student WHERE id=7;
​
​
​
-- 创建触发器 新增用户时，需要向其他表插入数据
DELIMITER $$
CREATE TRIGGER `gradedb`.`insert_student_class` AFTER INSERT
ON `gradedb`.`student` 
FOR EACH ROW -- 行级触发，每对表中的一行数据进行操作，就会触发一次触发器
BEGIN
INSERTINTO student_class(studentid,classid,grade) VALUE(new.id,1,1);
END $$
DELIMITER;
​
-- 测试触发器
INSERTINTO student(account,NAME) VALUE(777,"小明")