mysql 存储过程 存储函数 游标 事件 视图

 mysqldump 备份还原表

1.导出所有(包括结构&数据&存储过程&函数&事件&触发器)(不写-R -E默认仅导出结构和数据)

-- 注意这里不要加分号(密码随后输入即可)
mysqldump -h192.168.2.252 -uroot -p -R -E ipvacloud > d:2.sql  

2.导入数据库

mysql>use voice;
mysql>source d:xxx.sql;
再次出现mysql>并且没有提示错误即还原成功

这里为什么要用mysqldump呢?  因为速度快啊,数据库5个G用它比直接用navicat导出sql文件快100倍, 原来，mysqldump默认导出的就有table和view，而且view还是作为temp table导出来的，而且在导出来的sql文件里面，你看不到create view这样类似的字眼。但是，你执行source 2.sql命令时，会在视图里找到aaa这个视图

1.存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `test_procedure`(IN `age` int,OUT `result_out` int)
BEGIN
    DECLARE user_name VARCHAR (64) -- 声明变量，但不使用
	#Routine body goes here...
  SELECT * FROM employee; -- 返回结果集
	SET result_out=age *2; -- out结果
	END

点击运行按钮，只要输入输入参数就可以啦

--navicat中使用call来调用 存储过程  1个输入参数， 1个输出参数

CALL test_procedure(6, @a);

2.存储函数（必须要有返回值，且入参只有IN, 没有OUT）

CREATE DEFINER=`root`@`%` FUNCTION `test_function`(`age` int) 
RETURNS int(11)  -- 表示返回值的类型
BEGIN
	RETURN age * 2;
END

navicat中使用如下语句，调用存储函数

SELECT test_function(22);

3.游标 

MySql 游标的使用（二）FETCH INTO取出来的部分字段为NULL-CSDN博客

BEGIN
 -- 这里定义的变量不能和表字段相同（重点）
 DECLARE uid VARCHAR(100);
 DECLARE subuid VARCHAR(100);
 DECLARE create_name VARCHAR(256);-- 上两级用户username
 DECLARE ss VARCHAR(256);
 declare t_node int; 
 DECLARE done INT DEFAULT 0; 

 DECLARE curJob CURSOR FOR ( -- 为临时表创建游标 定义游标映射的表的字段要和FETCH curJob INTO uid, create_user_name;的字段一致
      SELECT UserId, create_user_name FROM user_temp  
 );

 DECLARE curJob1 CURSOR FOR ( 
      SELECT UserId FROM sub_user_temp 
 );
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 监听器在游标定义的后面(只声明一次)

-- delcare完就开启事务 定义handler来处理异常
declare exit handler for sqlexception select concat('失败:',case t_node when 1 then '失败' when 2 then '失败' end);ROLLBACK;

CREATE TEMPORARY TABLE  IF NOT EXISTS user_temp(
   UserId varchar(100),
   create_user_name varchar(256)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- 创建临时表必须在声明异常的下面
start TRANSACTION; -- 和下面的COMMIT对应
-- 将查询的结果放到临时表里
INSERT INTO user_temp SELECT am.UserId,au.create_user_name FROM aspnet_Membership am INNER JOIN aspnet_users au ON am.UserId=au.UserId WHERE customer_status='3';

OPEN curJob; -- 打开游标  
    out_loop:LOOP   -- loop名称可以是out_loop inner_loop
        FETCH curJob INTO uid, create_name;
        IF done=1 THEN LEAVE out_loop;END IF;
        -- 存放子用户信息的临时表
				CREATE TEMPORARY TABLE  IF NOT EXISTS sub_user_temp(
					 UserId varchar(50) NOT NULL
				) ENGINE=InnoDB DEFAULT CHARSET=utf8;

        INSERT INTO sub_user_temp SELECT UserId FROM aspnet_users WHERE create_user_name in (SELECT UserName FROM aspnet_users WHERE UserId=uid);

				SELECT COUNT(1) INTO ss FROM sub_user_temp;
        IF(ss > 0) THEN   -- 存在子用户则将子用户挂在上二级用户username下,然后直接删除
						OPEN curJob1; -- 打开游标
						inner_loop:LOOP  
								FETCH curJob1 INTO subuid;
								IF done=1 THEN LEAVE inner_loop;END IF;
				
                -- 遍历多个子用户,update为此用户的create_user_name为上两级用户的name即可
                UPDATE aspnet_users SET create_user_name=create_name WHERE UserId=subuid;
						END LOOP inner_loop;   
					CLOSE curJob1;

				  DROP TABLE sub_user_temp; -- 删除临时表
          SET done = 0; -- 注意这里（重点）
        END IF;

    END LOOP out_loop;   
CLOSE curJob; -- 关闭游标  


COMMIT; 
END

4.事件

mysql事件 定时调用存储过程备份历史数据

 

  事件 调用 该存储过程  每天早8点定时执行备份近7天的天客流数据

SET GLOBAL event_scheduler = ON;
SHOW VARIABLES LIKE 'event_scheduler';
SELECT @@event_scheduler;
SHOW PROCESSLIST;

两种事件类型,一个是间隔触发，另一个是特定事件触发(我们用这种,特定时间触发)。
事件定时任务跟触发器很像，都是被动执行的，事件是因为时间到了触发执行，而触发器是因为某件事件（增删改）触发执行；
Create Database  If Not Exists MyDB Character Set UTF8;  即使是MyDB大写库名也是小写

真实的开发环境中，会遇到mysql服务重启或者断电的情况，此时则会出现事件调度器被关闭的情况，所有事件都不在起作用，要想解决这个办法，则需要在mysql.ini文件中加入event_scheduler = ON; 的语句

DEFINER：创建者；

ON COMPLETION [NOT] PRESERVE ：表示当事件不会再发生的情况下，删除事件（注意特定时间执行的事件，如果设置了该参数，执行完毕后，事件将被删除，不想删除的话可以设置成ON COMPLETION PRESERVE）；

ENABLE：表示系统将执行这个事件；
begin和end之间(包括begin和end)写成一个存储过程,让事件调用这个存储过程  


DROP EVENT IF EXISTS `event_minute`;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` EVENT `event_minute` ON SCHEDULE EVERY 1 MINUTE STARTS '2018-01-02 08:00:00' ON COMPLETION NOT PRESERVE ENABLE DO 

BEGIN
    INSERT INTO USER(name, address,addtime) VALUES('test1','test1',now());
END
;;
DELIMITER ;

5.视图

CREATE VIEW view_name as SELECT * FROM person