外键,触发器,函数,存储过程,视图,定时任务

外键

外键是某个表中的一列，它包含在另一个表的主键中。
外键也是索引的一种，是通过一张表中的一列指向另一张表中的主键，来对两张表进行关联。
一张表可以有一个外键，也可以存在多个外键，与多张表进行关联

环境 a,b两张表 分别创建 id ,name age 三列

一张表修改后 , 需要另一张表也做修改的情况

创建两张表

CREATE TABLE `t1` (
  `id1` int(22) NOT NULL,
  `name1` varchar(22) DEFAULT NULL,
  `age1` int(222) DEFAULT NULL,
  PRIMARY KEY (`id1`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t2` (
  `id2` int(22) NOT NULL,
  `name2` varchar(255) DEFAULT NULL,
  `age2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id2`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

外键的语法

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(外键字段名)
REFERENCES 外表表名(主键字段名)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
参数 意义
RESTRICT(默认值) 限制外表中的外键改动（也是最安全的设置）
CASCADE 跟随外键改动
SET NULL 设为null值
NO ACTION 无动作
SET DEFAULT 设为默认值
创建外键t1_id ( t1的id1列 关联 t2的id2列)
语法
    alter table 表名 add constraint 外键名 foreign key t1(name) references t2(id);  # 添加外键
    或者
    CONSTRAINT `a` FOREIGN KEY (`name`) REFERENCES `t2` (`name`)   #创建外键 在创建表时候添加
例子
    ALTER TABLE t1 ADD CONSTRAINT t1_id FOREIGN KEY(id1) references t2(id2);
    先添加t2才能添加t1 ，t1表删除一行t2表没变化，t1和t2都不能更新

语法
    alter table 表名 add constraint 外键名 foreign key t1(name) references t2(id) ON DELETE CASCADE;  # 添加外键
    或者
    CONSTRAINT `a` FOREIGN KEY (`name`) REFERENCES `t2` (`name`) ON DELETE CASCADE   #创建外键 在创建表时候添加
例子
    alter table t1 add constraint t1_id foreign key t1(id1) references t2(id2) ON DELETE CASCADE;
    先添加t2才能添加t1，t1表删除一行t2表没变化，t1和t2都不能更新，t2表删除一行t1表对应行也会删除
ON UPDATE CASCADE
语法
    alter table 表名 add constraint 外键名 foreign key a(name) references b(id) ON UPDATE CASCADE;  # 添加外键
    或者
    CONSTRAINT `a` FOREIGN KEY (`name`) REFERENCES `t2` (`name`) ON UPDATE CASCADE;  #创建外键 在创建表时候添加
例子
    alter table t1 add constraint t1_id foreign key t1(id1) references t2(id2) ON UPDATE CASCADE;
    先添加t2才能添加t1，t1表删除一行t2表没变化，t2表更新id1 t1表对应id2也会更新  先添加t2才能添加t1，
删除外键
alter table 表名 drop foreign key 外键名;
alter table t1 drop foreign key t1_id;
查看外键
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   #定时任务名second_event
on schedule every 1 second   # 每秒钟执行一次
on completion preserve disable   # 创建后并不开始生效
do call p1();   # 执行存储过程p1
  1. 第一行create event second_event是创建名为second_event的事件,注意此处没有括号
  2. 第二行是创建周期定时的规则，本处的意思是每秒钟执行一次
  3. 第三行on completion preserve disable是表示创建后并不开始生效。
  4. 第四行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天

END

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容