MySQL小记——存储过程、触发器、函数、视图

这篇具有很好参考价值的文章主要介绍了MySQL小记——存储过程、触发器、函数、视图。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

存储过程 procedure

语法

参数

调用存储过程 call

删除存储过程 drop

带有IF逻辑的存储过程 if then elseif else

带有循环的存储过程 while do

变量

触发器 Trigger

语法

old和new

视图 View

函数

自定义函数

内置函数

存储过程 procedure

存储过程是数据库中的一个对象，存储在服务端，用来封装多条SQL语句且带有逻辑性，可以实现一个功能，由于他在创建时，就已经对SQL进行了编译，所以执行效率高，而且可以重复调用，类似与我们Java中的方法。

语法

DELIMITER $$
CREATE
PROCEDURE `performance_schema`.`myTestPro`()

BEGIN

END$$

DELIMITER ;

参数

in：输入参数
out：输出参数
inout：输入输出参数

例如：
DELIMITER $$

CREATE
PROCEDURE `performance_schema`.`myTestPro`(IN num INT,OUT r INT)
BEGIN
DELETE FROM emp WHERE empno=num;
SELECT COUNT(*) FROM emp INTO r;
END$$

DELIMITER ;

调用存储过程 call

set @rr=0; //会话变量

语法：call myTestPro(9527,@rr)
查询结果： select @r

删除存储过程 drop

drop procedure myTestPro;

带有IF逻辑的存储过程 if then elseif else

DELIMITER $$
CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
BEGIN
   IF num=1 THEN
       SET str='星期一';
   ELSEIF num=2 THEN -- 注意elseif 连在一块
       SET str='星期二';
   ELSEIF num=3 THEN
       SET str='星期三'; -- 注意要用分号结束
   ELSE
       SET str='输入错误';
   END IF; -- 注意要结束if 后面有分号
END $$

带有循环的存储过程 while do

DELIMITER $
CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
BEGIN
   -- 定义一个局部变量
   DECLARE i INT DEFAULT 1;
   DECLARE vsum INT DEFAULT 0;
   WHILE i<=num DO
   SET vsum = vsum+i;
   SET i=i+1;
   END WHILE; -- 要记得结束循环
   SET result=vsum;
END $

变量

1.全局变量（内置变量）

-- 查看所有全局变量：
show variables

2.会话变量

只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失

-- 定义会话变量:
set @变量=值
-- 查看会话变量：
select @变量

3.局部变量

在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失

定义局部变量的语法：
DECLARE i INT DEFAULT 1;
给变量设置值 set i=10;

触发器 Trigger

数据库中的一个对象，相当于JS中的监听器，触发器可以监听增删改三个动作，比如说我想监听一张表，只要我增删改了这张表中的数据，我就可以触发这个触发器，去往另外一张表中记录一下日志

语法

DELIMITER $$

CREATE
TRIGGER `mytestdb`.`myTriger` BEFORE/AFTER INSERT/UPDATE/DELETE
ON `mytestdb`.`<Table Name>`
FOR EACH ROW
BEGIN

END$$

DELIMITER ;

BEFORE 行为发生之前就触发

AFTER 行为发生之后触发
FOR EACH ROW 行级触发，每操作一行就触发

例如：在test表中删除一条数据，在日志表logger中添加一条记录

DELIMITER $$

CREATE TRIGGER `mytestdb`.`MyTri3` AFTER DELETE ON test FOR EACH ROW
BEGIN
INSERT INTO logger VALUES(NULL,"你删除了一条数据",NOW());
END$$

DELIMITER ;

old和new

old.字段可以获取到被监听的表中的字段的旧值
new.字段可以获取到被监听表中更新后的字段的新值

例如：往一张表t1中添加一条数据，另一张表t2也要添加一条同样的数据
DELIMITER $$
CREATE
TRIGGER `mytestdb`.`myTri6` AFTER INSERT
ON `mytestdb`.`t1`
FOR EACH ROW BEGIN
INSERT INTO t2 VALUES(new.id,new.username,new.age);
END$$

DELIMITER ;

例如：修改一张表t1中的数据，另一张表t2中的数据也要修改
DELIMITER $$
CREATE
TRIGGER `mytestdb`.`MyTri7` AFTER UPDATE
ON `mytestdb`.`t1`
FOR EACH ROW
BEGIN
UPDATE t2 SET id=new.id,username=new.username,age=new.age WHERE id=old.id;
END$$

DELIMITER

视图 View

有结构(有行有列),但没有结果(结构中不真实存储数据)的虚拟的表，虚拟表的结构来源不是自己定义,而是从对应的基表中产生(视图数据的来源)

1.创建视图语法
   create view 视图名称 as select语句(这个语句可以是一张或多张表的的普通查询,或多表查询)
例如:创建单表视图
   create view my_v1 as select * from student;

例如:创建多表视图注意:不要查询两张表中的同名字段不然会报错
   create view my_v2 as select a.字段名,b.字段名 from a,b where a.id=b.id;

2.查看视图其实视图是一张虚拟表那关于查询表的语句对视图都是可以用的
   比如:show tables; desc my_v1
   只是在查看视图创建语句的的时候把table 改成view
   如:show create view my_v1;

3.视图的使用: 视图的使用,只是为了简化查询,你可以把视图当作表一样去使用例如:select * from my_v1;
   视图的执行:其实本质就是执行封装的select语句

4.删除视图: drop view 视图名称
   例如:drop view my_v1

5.修改视图:视图本身不可以修改,但是视图的来源是可以修改的(其实就是修改select 语句)
   语法: alter view 视图名字 as 新的select语句

6.视图的意义:(1)视图可以节省SQL语句,将一条复杂的查询语句,使用视图进行保存,以后可以直接对视图进行操作.
   (2)数据安全,视图操作注意是针对查询语句的,如果对视图结构进行处理(比如删除),不会影响基表的数据.
       所以相对来说数据比较安全
   (3)视图往往是在大项目中去使用,而且是多系统中去使用.我可以对外提供一些有用的数据,隐藏一些关键的数据.
   (4)视图对外可以提供友好的数据:不同的视图提供不同的数据,对外提供的数据好像是经过专门设计的一样.
   (5)视图可以更好的进行权限控制比如对外隐藏我的一些基表的名称

7.视图数据的操作:视图是可以进行数据操作的(比如增,删,改,视图中的数据),但是有很多限制
       视图插入数据:

       (1)多表视图不能插入数据
       (2)单表视图中可以插入数据(如果视图中字段没有基表中不能为空的字段且没有默认值的字段,是插入不成功的)
       (3)视图是可以向基表中插入数据的 (视图的操作是影响基表的)

       视图删除数据
       (1):多表视图不能删除数据
       (2):单表视图可以删除数据,也会影响到基表

函数

函数：包括内置函数和自定义函数

自定义函数

DELIMITER $$

CREATE

FUNCTION `mytestdb`.`myFun`(num INT)
RETURNS INT

BEGIN
   DECLARE i INT DEFAULT 100;
   SET i=i+num;
RETURN i;
END$$

DELIMITER ;

函数调用：

select 函数名();