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 函数名();

内置函数

数据库自带函数