触发器

触发器

-- 从sys_user表同步数据到act_id_user表
insert into act_id_user(ID_,FIRST_,PWD_,REV_,EMAIL_) select id,name,password,1,email from sys_user;


-- 删除触发器
drop trigger '触发器名称' 
-- 示例：drop trigger tri;

-- 触发器（当sys_user表中有insert事件时，就会触发插入sql）
create trigger sys_user_insert after insert on sys_user FOR EACH ROW
	insert into act_id_user(ID_,FIRST_,PWD_,REV_,EMAIL_)values(new.account,new.name,new.password,1,new.email);

show triggers;

-- 更新和删除

create trigger sys_user_update BEFORE update on sys_user for each ROW
BEGIN
	if new.deleted = 1 then
  delete from act_id_user where ID_ = new.id;
	ELSE
	update act_id_user set ID_=new.id,FIRST_=new.account,PWD_=new.password,REV_=REV_+1,EMAIL_=new.email WHERE ID_ = new.id;
	end if;
END;




UPDATE sys_user set deleted = 1 where id = 'asdf'

update sys_user set account='wilshon',`password` = '1234567,890',email = 'andmy@qq.com' WHERE id = 'asdf'

SQL复制

-- 从act_id_user复制数据到sys_user
INSERT INTO `act_id_user`(`ID_` ,`REV_` ,`FIRST_` ,`EMAIL_` ,`PWD_`) select `account` , '1',`name` ,`email` , `password`  FROM `sys_user` where `deleted` = false

导出excel

-- 我数据库里几千万行数据如果把数据全部导出的话费时,我只想导出部分数据,navicat自带的导出功能不能满足,必须要使用sql 语句
SELECT * FROM biz_task WHERE create_time >= '2019-03-18 18:49:38' AND create_time <= '2019-03-19 18:49:38' into OUTFILE 'D:\\test.xls'

-- 一些版本的mysql对通过文件导入导出做了限制，默认不允许
show variables LIKE "secure_file_priv";