外键,触发器,函数,
定时任务,存储过程,视图
外键
环境 a,b两张表 分别创建 id ,name age 三列
一张表修改后 , 需要另一张表也做修改的情况
创建外键
ON DELETE RESTRICT(默认)
alter table 表名 add constraint 外键名 foreign key a(name) references b(name); # 添加外键
CONSTRAINT `a` FOREIGN KEY (`name`) REFERENCES `t2` (`name`) #创建外键 在创建表时候添加
默认 ON DELETE RESTRICT a表name关联到b表name
添加: a表只能添加name值跟b表对应的数据 , b表添加一行a表无变化
删除: 先删除a表对应的行,才能删除b表的行, a表删除一行对b表无影响, b表不能删除行,只能先删除a表对应的行,再删除b表的行
修改: ab表都不能修改name列, 除非b表的name列值在a表中没创建
ON DELETE CASCADE
alter table 表名 add constraint 外键名 foreign key a(name) references b(name) ON DELETE CASCADE; # 添加外键
CONSTRAINT `a` FOREIGN KEY (`name`) REFERENCES `t2` (`name`) ON DELETE CASCADE #创建外键 在创建表时候添加
b表删掉一行数据a表也会删除
ON DELETE SET NULL
alter table 表名 add constraint 外键名 foreign key a(name) references b(name) ON DELETE SET NULL; # 添加外键
CONSTRAINT `a` FOREIGN KEY (`name`) REFERENCES `t2` (`name`) ON DELETE SET NULL; #创建外键 在创建表时候添加
b表删除一行 a表对应name值是空
ON UPDATE CASCADE
alter table 表名 add constraint 外键名 foreign key a(name) references b(name) ON UPDATE CASCADE; # 添加外键
CONSTRAINT `a` FOREIGN KEY (`name`) REFERENCES `t2` (`name`) ON UPDATE CASCADE; #创建外键 在创建表时候添加
修改b表name时a表name也会相应改变
删除外键
alter table 表名 drop foreign key 外键名;
查看外键
show create table 表名; # 也就是查看建表语句
触发器
对数据库的a表进行增删改时候,希望也修改另外一张表b 这时候就需要触发器
插入,删除,更新
插入前
delimiter $$
CREATE TRIGGER aa BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
insert into t2 (name) values("abc");
END $$
delimiter ;
插入后
delimiter $$
CREATE TRIGGER aa AFTER INSERT ON t1 FOR EACH ROW
BEGIN
insert into t2 (name) values("abc");
END $$
delimiter ;
其他的
删除前后 BEFORE DELETE , AFTER DELETE
更新前后 BEFORE UPDATE , AFTER UPDATE
FOR EACH ROW : 针对每一行都触发
删除触发器: DROP TRIGGER 触发器名字;
查看触发器
show TRIGGERS;
也可以查看 information_schema.triggers
if判断语句
delimiter $$
CREATE TRIGGER bb BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
if new.name = 'alex' then
insert into t2 (name) values("abc");
end if;
END $$
delimiter ;
判断如果插入t1表的name只值是'alex' 就执行sql insert into t2 (name) values("abc");
delimiter $$
CREATE TRIGGER cc BEFORE DELETE ON t1 FOR EACH ROW
BEGIN
if old.name = 'alex' then
insert into t2 (name) values("alex");
end if;
END $$
delimiter ;
判断如果删除t1表中的name值 是'alex' 就执行sql
new 新增的一行数据 new.name是刚插入的name值
old 删除的哪一行数据 old.name是刚删除的name值
函数
用在sql语句中的变量
定义一个函数
delimiter &&
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
declare maxId int;
select max(id) from t1 into maxId;
set num=i1 +i2 + maxId;
return(num);
END &&
delimiter ;
解释:
delimiter && : 改变之前的sql语句是;结尾改成&&
创建函数f1 ,接收两个变量i1和i2 ,
定义两个变量num和maxId
执行sql语句 把结果传给maxId + i1 + i2 =num 返回
END && 函数创建完成
delimiter ; 改回分号
执行函数: select f1(11,22) select f1(11,id) ,name from t1;
mysql 配置文件要加这个参数: log_bin_trust_function_creators=1 是否可以信任存储函数创建者 ,要是root用户可以不开 其他用户必须开
查看函数
show function status;
select `name` from mysql.proc where db = 'kaoshi' and `type` = 'FUNCTION' #查看kaoshi库下的函数
show CREATE FUNCTION f11
视图
视图就是一个临时表
创建视图
create view v1 as select id,name from t1 where id > 1;
执行视图: select * from v1;
删除视图: drop view v1;
修改视图: alter view v1 as SQL语句
查看视图
SELECT * from information_schema.VIEWS
show CREATE VIEW dsrw
存储过程
一堆sql语句封装到一起
定义存储过程
delimiter &&
create procedure p1()
BEGIN
select * from t1;
END &&
delimiter ;
执行存储过程: call p1();
删除存储过程: drop procedure p1;
查看存储过程
show procedure status;
select * from mysql.proc where db = 'kaoshi' and `type` = 'PROCEDURE' # 查看kaoshi库下的存储过程
show create procedure p1; #查看存储过程的创建语句
定时任务
存储过程 + 定时任务
sql_mode=ANSI_QUOTES # 要关闭
将
"视为标识符引用字符(如""引用字符),而不是字符串引用字符。启用此模式后，您仍然可以使用`来引用标识符。启用ansi_quotes后，不能使用双引号来引用文字字符串，因为它们被解释为标识符。event_scheduler =1; # 要打开
介绍
查看定时任务是否开启
show VARIABLES LIKE '%event_scheduler%';
select @@event_scheduler;
开启定时任务功能
set global event_scheduler =1;
查看本机所有定时任务
SELECT * FROM information_schema.EVENTS;
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
单位有 second,minute,hour,day,week(周),quarter(季度),month,year
创建一个存储过程
delimiter &&
create procedure p1()
BEGIN
insert into t2 (name,time) values("abc",now());
END &&
delimiter ;
创建定时任务执行存储过程
create database kxm;
use kxm;
create table t1 (id int primary key , name varchar(22));
insert into t1(id,name) values (2,"aaaa");
delimiter &&
create procedure p1()
BEGIN
create table t1 (id int primary key , name varchar(22));
END &&
delimiter ;
create event second_event
on schedule every 1 second
on completion preserve disable
do call p1();
alter event second_event on completion preserve enable;
SELECT * FROM information_schema.EVENTS;
create event second_event
on schedule every 1 second
on completion preserve disable
do call p1();
- 第一行create event second_event是创建名为second_event的事件,注意此处没有括号
- 第二行是创建周期定时的规则，本处的意思是每秒钟执行一次
- 第三行on completion preserve disable是表示创建后并不开始生效。
- 第四行do call p1(); 是该event(事件)的操作内容，表示调用我们刚刚创建的call p1();存储过程
开关 定时任务
alter event second_event on completion preserve enable; //开启定时任务 second_event是存储过程名
alter event second_event on completion preserve disable; //关闭定时任务
周期定时规则
周期执行–关键字 EVERY
on schedule every 1 second //每秒执行1次
on schedule every 2 minute //每两分钟执行1次
on schedule every 3 day //每3天执行1次
在具体某个时间执行–关键字 AT
on schedule at current_timestamp()+interval 5 day //5天后执行
on schedule at current_timestamp()+interval 10 minute //10分钟后执行
on schedule at '2016-10-01 21:50:00' //在2016年10月1日，晚上9点50执行
在某个时间段执行–关键字STARTS ENDS
on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month //5天后开始每天都执行执行到下个月底
on schedule every 1 day ends current_timestamp()+interval 5 day //从现在起每天执行，执行5天
