MYSQL笔记-视图、存储过程、触发器

1 视图

• 视图是基于SQL语句的结果集的可视化的表
  
• 视图包含行和列，像一个真实的表
  
• 视图中的字段就是来自一个或多个数据库中的真实的表中的字段
  

-- 创建视图
CREATE VIEW view_name AS
SELECT col1, col2...
FROM table_name
WHERE condition;

-- 使用视图
SELECT * FROM view_name;

-- 删除视图
DROP VIEW view_name;

2 存储过程

• 存储过程可封装并隐藏复杂的逻辑
• 存储过程可以接受参数、回传值
• 存储过程无法使用SELECT指令运行，因为它是子程序
• 存储过程可以用在数据检验，强制实行商业逻辑
• 存储过程是用来完成特定功能的具有名字的一段代码
• 存储过程保存在数据库的数据字典中
• 存储过程的参数名要避免和列名同名，避免被数据库引擎当作列名进行误处理
• 存储过程的参数标识尽量明确in/out，避免使用inout
• 存储过程中可以使用用户变量名，用户变量名一般以@开头，滥用用户变量会导致存储过程难以理解
• mysql 中的存储过程不能直接修改内容，只能修改其注释、特性（COMMENT、SQL SECURITY 等），如需修改内容需要删除存储过程再重建

-- 创建存储过程
-- 临时修改语句结束符为 $$(可以自定义)，以正常传输该语句到数据库，避免被客户端直接解析
DELIMITER $$
-- 定义局部变量(一定要在存储过程声明之前)
DECLARE local_variable_name datatype;
-- 定义存储过程
CREATE PROCEDURE store_procedure_name (IN in_param in_param_type, out out_param out_param_type)
BEGIN
-- store_param_condition
END$$
-- 恢复语句结束符为 ;
DELIMITER ;

-- 调用存储过程
-- 定义参数
set @in_param = in_param_value;
set @out_param = out_param_value;
-- 调用
call store_procedure_name(@in_param,@out_param);
-- 查看参数结果
select @in_param,@out_param;

-- 给参数赋值
select 'testaaa' into @in_param;
-- 查看参数值
select @in_param;

-- 查看存储过程
show create procedure store_procedure_name;

-- 删除存储过程
drop procedure store_procedure_name;

3 触发器

• mysql中的触发器是一个特殊的存储过程，其在预定义的事件发生后由mysql自动调用
• mysql中，只有执行insert、update、delete操作时才能激活触发器，对应mysql中的三种触发器：insert触发器、update触发器、delete触发器
• 常用于在数据变更前后自动进行一些操作，比如日志记录、数据同步、自动计算、数据校验等

-- 创建触发器
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
--- 触发器逻辑
END;

-- 查看全部触发器
SHOW TRIGGERS;

-- 删除触发器
DROP TRIGGER IF EXISTS trigger_name;

触发器中的OLD和NEW

• OLD：被删除（或更新前）的数据
• NEW：新插入或更新后的数据

使用规则：

操作类型	可用关键字	说明
INSERT	NEW.column_name	新插入的值
UPDATE	OLD.column_name、NEW.column_name	更新前和更新后的值
DELETE	OLD.column_name	被删除的数据

注意事项：

1. 不能修改当前表的数据，避免递归问题
2. 不能在触发器中使用COMMIT或ROLLBACK，不支持事务控制
3. 不能直接调用SELECT ... INTO 语句
4. 每次只针对每一行触发（FOR EACH ROW），不能整批处理