MYSQL存储过程函数event触发器

#存储过程   
#存储函数
#事件event

show VARIABLES like '%schedule%';

#能查看所有存储过程
SHOW PROCEDURE STATUS;
#c查看某个存储过程具体信息
SHOW CREATE PROCEDURE DeleteOldData;

show EVENTS;
select DATE_SUB(NOW(), INTERVAL 3 MONTH) from dual;

DELIMITER $$

CREATE PROCEDURE DeleteOldData()
BEGIN
    -- 计算三个月前的日期
    DECLARE three_months_ago DATE;
    SET three_months_ago = DATE_SUB(NOW(), INTERVAL 3 MONTH);
    
    -- 删除三个月前的数据
    DELETE FROM books7
    WHERE uptime < three_months_ago;
    
    -- 提交事务
    COMMIT;
END$$

DELIMITER ;



CREATE EVENT IF NOT EXISTS weekly_delete_old_data
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-08-25 02:00:00' -- 设定为你想要的起始时间
DO
		CALL DeleteOldData();


CREATE EVENT minly_delete_old_data
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP -- 设定为你想要的起始时间
DO
		CALL DeleteOldData();




SHOW CREATE PROCEDURE DeleteOldData;

show EVENTS;

select * from books7;

ALTER EVENT minly_delete_old_data ON  COMPLETION PRESERVE ENABLE;

ALTER EVENT minly_delete_old_data ON  COMPLETION PRESERVE DISABLE;

show VARIABLES like '%schedule%';



CREATE PROCEDURE GetOfficeByCountry(IN N int)
 BEGIN
 SELECT *   
 FROM scores
 WHERE id < N;
 END 


call GetOfficeByCountry(5);

-- show create procedure GetOfficeByCountry;
-- 
-- 
-- characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：
-- LANGUAGE SQL ：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
-- [NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定
-- 的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定
-- 的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为NOT DETERMINISTIC。
-- { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使
-- 用SQL语句的限制。
-- CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但是并不包含读写数据的SQL语句；
-- NO SQL表示当前存储过程的子程序中不包含任何SQL语句；
-- READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句；
-- MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句。
-- 默认情况下，系统会指定为CONTAINS SQL。
-- SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执
-- 行当前存储过程。
-- DEFINER 表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
-- INVOKER 表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
-- 如果没有设置相关的值，则MySQL默认指定值为DEFINER。
-- CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
-- [characteristics ...]
-- BEGIN
-- 存储过程体
-- END
-- 修饰符 返回类型 方法名(参数类型 参数名,...){
-- 方法体;
-- }
-- LANGUAGE SQL
-- | [NOT] DETERMINISTIC
-- | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
-- | SQL SECURITY { DEFINER | INVOKER }
-- | COMMENT 'string'
-- COMMENT 'string' ：注释信息，可以用来描述存储过程。

#存储过程语法：

DELIMITER $
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
sql语句1;
sql语句2;
END $
DELIMITER ;

#创建
delimiter $
create PROCEDURE selectEmp()
COMMENT '查看所有员工信息'
begin
	select * from employees ;
end $
delimiter ;

#调用
call selectEmp();

#删除存储过程
DROP PROCEDURE [ IF EXISTS ] <过程名>

DROP PROCEDURE some_salary;

show create PROCEDURE selectEmp;


# OUT参数 定义
DELIMITER //
CREATE PROCEDURE show_max_salary(OUT ms DOUBLE)
BEGIN
SELECT MAX(salary) INTO ms FROM employees;
END //
DELIMITER ;

#调用
call show_max_salary(@maxsa);
select @maxsa;


# IN参数 定义
DELIMITER //
CREATE PROCEDURE show_someone_did(IN d_id VARCHAR(20))
COMMENT '查看所有部门员工信息'
BEGIN
SELECT * FROM employees WHERE department_id = d_id;
END //
DELIMITER ;

#调用
call show_someone_did(30);


# IN 、OUT参数
DELIMITER $$
create procedure some_salary(IN seid int,OUT sal DOUBLE)
COMMENT '某人薪资'
BEGIN
	select salary into sal FROM employees where employee_id = seid;
END $$
DELIMITER ;
#调用
call some_salary(108,@sal);
select @sal;


## INOUT参数
DELIMITER $$
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
BEGIN
SELECT last_name INTO empname FROM employees
WHERE employee_id = (SELECT manager_id FROM employees WHERE last_name=empname);
END $$
DELIMITER ;

set @empname = 'Chen';
call show_mgr_name(@empname);
select @empname;




#存储函数
#参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数
#characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics ...]
BEGIN
函数体 #函数体中肯定有 RETURN 语句
END



DELIMITER $
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
READS SQL DATA 
BEGIN
	set N = N - 1;
  RETURN (
      select ifnull((
          select distinct salary 
          from employees
          order by salary desc limit N,1),null) as getNthHighestSalary
  );
END $
DELIMITER ;
#调用
select getNthHighestSalary(2);
#删除
drop FUNCTION getNthHighestSalary;


DELIMITER $$
CREATE FUNCTION count_by_id(dept_id INT) RETURNS INT
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
COMMENT '查询部门平均工资'
BEGIN
RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END $$
DELIMITER ;

#调用
-- SET @dept_id = 50;
-- SELECT count_by_id(@dept_id);
select count_by_id(30);

-- 若在创建存储函数中报错“ you might want to use the less safe
-- log_bin_trust_function_creators variable ”
-- 加上必要的函数特性或者如下设置
#SET GLOBAL log_bin_trust_function_creators = 1;





#事件event
#查看事件开启状态
show VARIABLES like '%event_scheduler%';
#在my.cnf中配置event_scheduler=1或者event_scheduler=0开可控制是否开启。不配置则默认为关闭

#启动事件调度器
SET GLOBAL event_scheduler = 1; 
SET GLOBAL event_scheduler = on;
#关闭事件调度器
SET GLOBAL event_scheduler = 0;
SET GLOBAL event_scheduler = off;

#查看具体定义的事件
show EVENTS;
#禁用某个事件
ALTER EVENT minly_delete_old_data DISABLE; 
#启用某个事件
ALTER EVENT minly_delete_old_data ENABLE; 
#删除某个事件
DROP EVENT my_event;
-- 开启指定事件：ALTER EVENT 具体事件名称 ON COMPLETION PRESERVE ENABLE;
-- 关闭指定事件：ALTER EVENT 具体的事件名称 ON COMPLETION PRESERVE DISABLE;
-- 查看事件：SHOW EVENTS; 或者 SELECT * FROM INFORMATION_SCHEMA.'EVENTS';
-- 删除指定事件：DROP EVENT IF EXISTS 具体事件名称;

#查看当前存在的事件状态信息
show EVENTS;
#通过查询information_schema数据库中的EVENTS表来查看事件的执行情况
SELECT * FROM information_schema.EVENTS;
#查看调度器线程
show processlist;


#创建语法
CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO event_body;
 
schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
 
interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

-- event_name ：创建的event名字（唯一确定的）。
-- ON SCHEDULE：计划任务。
-- schedule: 决定event的执行时间和频率（注意时间一定要是将来的时间，过去的时间会出错），有两种形式 AT和EVERY。
-- [ON COMPLETION [NOT] PRESERVE]： 可选项，默认是ON COMPLETION NOT PRESERVE 即计划任务执行完毕后自动drop该事件；ON COMPLETION  PRESERVE则不会drop掉。
-- [COMMENT 'comment'] ：可选项，comment 用来描述event；相当注释，最大长度64个字节。
-- [ENABLE | DISABLE] ：设定event的状态，默认ENABLE：表示系统尝试执行这个事件， DISABLE：关闭该事情，可以用alter修改
-- DO event_body: 需要执行的sql语句（可以是复合语句）。CREATE EVENT在存储过程中使用时合法的。




select DATE_SUB(NOW(), INTERVAL 3 MONTH) from dual;
select DATE_ADD(NOW(), INTERVAL 5 MONTH)from dual;

DELIMITER $$
CREATE PROCEDURE DeleteOldData()
BEGIN
    -- 计算三个月前的日期
    DECLARE three_months_ago DATE;
    SET three_months_ago = DATE_SUB(NOW(), INTERVAL 3 MONTH);
    
    -- 删除三个月前的数据
    DELETE FROM books7
    WHERE uptime < three_months_ago;
    
    -- 提交事务
    COMMIT;
END$$
DELIMITER ;



CREATE EVENT IF NOT EXISTS weekly_delete_old_data
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-08-25 02:00:00' -- 设定为你想要的起始时间
DO
		CALL DeleteOldData();

#every
CREATE EVENT minly_delete_old_data
ON SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP -- 设定为你想要的起始时间
DO
		CALL DeleteOldData();

#at
CREATE EVENT IF NOT EXISTS at_d_test
ON SCHEDULE at '2024-09-11 17:18:00'
DO
		CALL DeleteOldData();

SHOW CREATE PROCEDURE DeleteOldData;
show EVENTS;

常用的时间间隔示例：

#(1)每隔10秒钟执行
ON SCHEDULE EVERY 10 SECOND
#(2)每隔10分钟执行
ON SCHEDULE EVERY 10 MINUTE
#(3)每天凌晨5点执行
ON SCHEDULE EVERY 1 DAY
STARTS DATE_ADD(DATE_ADD(CURDATE(),INTERVAL 1 DAY), INTERVAL 5 HOUR)
#(4)每隔3个月执行，从现在起2周后开始
ON SCHEDULE EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 2 WEEK
#(5)每隔3小时执行，从现在起10分钟后开始，1个星期后结束
ON SCHEDULE EVERY 3 HOUR
STARTS CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
ENDS CURRENT_TIMESTAMP + INTERVAL 1 WEEK
#(6)在2022-06-30晚上9点执行
ON SCHEDULE AT '2022-06-30 21:00:00'


#查看所有全局变量
SHOW GLOBAL VARIABLES;
#查看所有会话变量
SHOW SESSION VARIABLES;
或
SHOW VARIABLES;


#查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE '%events%';
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%events%';



#触发器


CREATE TABLE bookst (
    id INT PRIMARY KEY AUTO_INCREMENT,
    titles VARCHAR(100),
    author_id INT,
		uptime TIMESTAMP DEFAULT NOW()
);

CREATE TABLE bookstb (
    id INT PRIMARY KEY AUTO_INCREMENT,
		old_id VARCHAR(100),
    titles VARCHAR(100),
    author_id VARCHAR(50),
		uptime TIMESTAMP DEFAULT NOW(),
		users VARCHAR(100)
);

CREATE TABLE bookstt (
    id INT PRIMARY KEY AUTO_INCREMENT,
    titles VARCHAR(100),
    author_id INT,
		uptime TIMESTAMP DEFAULT NOW()
);

CREATE TABLE bookst (
    id INT PRIMARY KEY AUTO_INCREMENT,
    titles VARCHAR(100),
    author_id INT,
		uptime TIMESTAMP DEFAULT NOW()
);

delimiter $$
create trigger salary_check
before insert on emp for each row
BEGIN
	DECLARE mgrsalary double;
	select salary INTO mgrsalary from emp where eid=new.mid;
	if new.salary > mgrsalary then
			SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
	END IF;

END $$
delimiter ;


insert into emp values(DEFAULT,'etts1',3000,2);
insert into emp values(DEFAULT,'etts2',5000,2);

#查看当前数据库的所有触发器的定义
show triggers;
#查看当前数据库中某个触发器的定义
show create trigger salary_check;
#从系统库information_schema的TRIGGERS表中查询触发器的信息
SELECT * FROM information_schema.TRIGGERS;
#删除触发器
DROP TRIGGER IF EXISTS delbokbak;


-- 注意，如果在子表中定义了外键约束，并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句，此
-- 时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子
-- 表的UPDATE和DELETE语句定义的触发器并不会被激活。
-- 例如：基于子表员工表（t_employee）的DELETE语句定义了触发器t1，而子表的部门编号（did）字段定
-- 义了外键约束引用了父表部门表（t_department）的主键列部门编号（did），并且该外键加了“ON
-- DELETE SET NULL”子句，那么如果此时删除父表部门表（t_department）在子表员工表（t_employee）
-- 有匹配记录的部门记录时，会引起子表员工表（t_employee）匹配记录的部门编号（did）修改为NULL，
-- mysql> update demo.membermaster set memberdeposit=20 where memberid = 2;
-- ERROR 1054 (42S22): Unknown column 'aa' in 'field list'
-- 但是此时不会激活触发器t1。只有直接对子表员工表（t_employee）执行DELETE语句时才会激活触发器
-- t1。
-- 

create trigger insetb_af 
after insert on bookst for each row
BEGIN
		insert into bookstt VALUES (DEFAULT,new.titles,new.author_id,NOW());
end

insert into bookst() VALUES
(default,'book2',2,DEFAULT),
(default,'book2',2,DEFAULT),
(default,'book2',2,DEFAULT),
(default,'book2',2,DEFAULT),
(default,'book2',2,DEFAULT),
(default,'book2',2,DEFAULT),
(default,'book2',2,DEFAULT),
(default,'book2',2,DEFAULT);


create trigger uptb_bf
before update on bookst for each row
begin
	update bookstt set titles=new.titles,author_id=new.author_id,uptime=NOW() WHERE id = old.id;

end

update bookst set titles='newtitles111',author_id=3,uptime=NOW() WHERE id = 2;


create trigger delbokbak 
before delete on bookst for each row
begin
	insert into bookstb VALUES(DEFAULT,old.id,CONCAT('旧标题：',old.titles),old.author_id,NOW(),USER());

end

delete from bookst where id = 8;
delete from bookst where id in (6,7) ;


#eg

select score,DENSE_RANK() over(ORDER BY score desc) as 'rank' from Scores;

select s1.score,count(DISTINCT s2.score) as 'rank' from Scores as s1,Scores as s2 WHERE s1.score <= s2.score GROUP BY s1.id ORDER BY s1.score desc;

CREATE TABLE `tb_dau` (
  `fdate` date DEFAULT NULL,
  `user_id` int DEFAULT NULL
);

select user_id '用户id', MAX(c_day) '最长连续登录天数' from 
(SELECT user_id,MAX(date_sort)-MIN(date_sort)+1 as c_day from 
(select 
    user_id,
    fdate,
    DENSE_RANK() over(partition by user_id order by fdate) as date_sort,
    date_sub(fdate, interval row_number() over(partition by user_id order by fdate) day) as b_date 
from tb_dau
where fdate between '2023-01-01' and '2023-12-31' AND user_id = 10000) as t1 
GROUP BY b_date) as t2 GROUP BY user_id;