sql基础(3)之（存储过程，函数，触发器和三大范式）

存储过程

1.概念：

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

2.创建存储过程的语法：

DELIMITER $$
-- $$为结束的标志符，可以自己设置
CREATE
    BEGIN
-- 在这个里面放置逻辑语句
    END$$
DELIMITER ;

注意：创建存储过程需要管理员分配权限

3.调用存储过程的语法：

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

4.传参和获取返回值：

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

in 和 out 的搭配使用：

-- 创建存储过程
DELIMITER $$
CREATE
    PROCEDURE `performance_schema`.`myTestPro`(IN num INT,OUT r INT)
    -- 传参数定为num 输入参数定为r
    BEGIN
	DELETE FROM emp WHERE empno=num;
	-- 根据传入的参数执行过程
	SELECT COUNT(*) FROM emp INTO r;
	-- 将过程的值赋值给 r
    END$$
DELIMITER ;

-- 接受存储过程的返回值
call myTestPro(9527,@rr)
select @rr

inout的单独使用：

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

// 查看存储过程
SET @sloution="筛选的值";
call call myTestPro(@sloution)
select @sloution

5.删除存储过程

drop procedure 存储过程名

6.查看存储过程

show procedure status\G;  
-- 查看所有的存储过程状态
show create procedure 存储过程名字\G; 
-- 查看创建存储过程的语句

7.变量

8.带有选择的存储过程（if then elseif else end if）

存储过程：

DELIMITER $$
CREATE   
    PROCEDURE `work`.`iff`(IN num INT ,OUT str VARCHAR(20) )
    BEGIN
	IF num=1 THEN 
	SET str='星期一';
	ELSEIF num=2 THEN
	SET str='星期二';
	ELSEIF num=3 THEN
	SET str='星期三';
	ELSEIF num=4 THEN
	SET str='星期四';
	ELSE 
	SET str='输入有误';
	
	END IF;
    END$$
DELIMITER ;

询问窗口：

SET @a=4;-- 定义一个变量@a并给赋值为4
CALL iff(@a,@b);-- 语法比较松散，没有定义就可以随便拉一个值过来用
SELECT @b; -- 查询@b的值

9.带有循环的存储过程 while do

要求：利用存储过程，求1到100之间的和

存储过程：

DELIMITER $$
CREATE
    PROCEDURE `work`.`whf`(IN num INT,OUT res INT)
    BEGIN
    -- 定义两个变量
	DECLARE j INT DEFAULT 1;
	-- 定义一个自增变量
	DECLARE i INT DEFAULT 0;
	-- 定义一个数字和
	WHILE j <=num DO
	SET i=i+j;
	SET j=j+1;
	END WHILE;
-- 最后将整数和赋值给输出变量
	SET res=i;
    END$$
DELIMITER ;

询问：

CALL whf(100,@a);
SELECT @a;

其他循环语句

例如：  REPEAT 循环 
DELIMITER $
 CREATE PROCEDURE proRepeat(OUT outnum INT)
     BEGIN
     DECLARE i INT DEFAULT 0;
     REPEAT
         SET i=i+1;
         UNTIL i>=5  -- 注意这里不要加分号,不然语法报错 UNTIL 结束条件
     END REPEAT; -- 记着结束
     SET outnum=i;
  END $
  
  -- 调用循环
  CALL proRepeat(@num);
  SELECT @num;

例如：loop 循环 
 DELIMITER $
 CREATE PROCEDURE proLoop(OUT outnum INT)
  BEGIN
     DECLARE i INT DEFAULT 0;
      myloop:LOOP    -- 这里的myloop 是我给循环起的一个标号或者说名字,这是语法要求,不起就会报错
         SET i=i+1;
        IF i>=5 THEN
            LEAVE myloop;  -- 根据我的循环标号中断循环 leave 是中断循环的意思
         END IF;
    END LOOP;
     SET outnum=i;
  END $
  
  -- 调用循环
  CALL proLoop(@num);
  SELECT @num;

• 控制循环的两个关键字

  leave 相当于java中的 break

  iterate相当于java中的continue

触发器 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 ;     
  

函数

函数：包括内置函数，和自定义函数
自定义函数语法
  DELIMITER $$
  
  CREATE
      FUNCTION `mytestdb`.`myFun`(num INT)
      RETURNS INT
      BEGIN
  	DECLARE i INT DEFAULT 100;
  	SET i=i+num;
      RETURN i;
      -- 返回返回值
      END$$
  
  DELIMITER ;
  
  函数的调用 select 函数名();

函数和存储过程的区别：
1.存储过程没有返回值，函数必须要有返回值。但是存储过程可以用out能实现返回值这个作用
2.存储过程有in out inout 这几个参数类型 函数的参数全是用来收实参

三大范式

1. 第一范式：要求表的每个字段不可再分
   例如： 张小名|狗娃
2. 第二范式：在第一范式的基础上，要求每张表只表达一个意思。表的每个字段都和表的主键有依赖。
   例如：一张表上做一个项目
3. 第三范式：在第二范式基础，要求每张表的主键之外的其他字段都只能和主键有直接决定依赖关系。
   例如：每个表上都有冗余的数据
   职员表 工作表 部门表 都有职员名称 和部门编号等