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