create triggr triafterinsertcmdlog
after insert on cmd_log FOR EACH ROW
trigger_body
.#NEW : 代表新的记录
.#OLD : 代表老的记录
#准备表
CREATE TABLE CMD (
id INT PRIMARY KEY auto_increment,
user char(32),
priv char(10),
cmd char(64),
sub_time datetime, #提交时间
success enum('yes','no') #0代表NO
);
create table err_log(
id int primary key auto_increment,
err_cmd char(64),
err_time datetime
);
#创建触发器
delimiter //
create table tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
begin
IF NEW.success = 'no' THEN #等值判断一个信号
INSERT INTO err_log(err_cmd,err_time) values(new.cmd. new.sub_time) #必须加分号
END IF; #必须加分号
END//
delimiter;
call 是调存储过程
3 . 事务 :
要么同时成立,要么同时不成立.
create table user( id int primary key auto_increment, name char(32), balance int );
insert into user(name,balance) values ('pp',6),('ww',6),('ff',6);
.#原子操作 start transaction; update user set balance = 2 where name = 'pp', update user set balance = 10 where name = 'ww'
4 . 存储过程
1. 程序与数据实现解耦
2. 减少网络传输的数据量
对于存储过程,可以接受参数,其参数有三种 :
1. in 仅用于传入参数用
2. out 仅用于返回值用
在python里面运行
import pymysql
conn = pymysql.connect(host = '127.0.0.1' , port = 5555 , user = 'root' , passwd = '123' , db = '...')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#执行存储过程
cursor.callproc('p1',args=(1,2,3,4))
#获取执行完存储的参数
cursor.excute(?????????????????????)
res = cursor.fetchall()
conn.commit()
cursor.close()
conn.close()
print(result)
#建表
create table dep(
id int primary key auto_increment,
name char(32)
);
create table user(
id int primary key auto_increment,
name char(32),
dep_id int,
foreign key(dep_id) references dep(id)
);
insert into dep(name) values('外交部'),('公关部'),('')
#创建触发器的语法 :
CREATE
TRIGGER tregger_name
tregger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
trigger_time : {BEFORE | AFTER}
trigger_event : {INSERT | UPDATE | DELETE}
#第一步 : 准备表
create table cmd_log(
id int primary key auto_increment,
cmd_name char(64),
sub_time datetime,
user_name char(32),
is_success enum('yes','no')
);
create table err_log(
id int primary key auto_increment,
cname char(64),
stime datetime
);
#第二步 : 创建触发器
delimiter //
CREATE
TRIGGER tri_after_insert_cmd_log
after insert
ON cmd_log FOR EACH ROW
BEGIN
if new.is_success = 'no' THEN
insert into err_log(cname,stime) values(new.cmd_name,new.sub_time);
end if ;
END //
delimiter ;
#测试
insert into cmd_log(cmd_name,sub_time,user,is_success) values
('ls -l /etc | grep *.conf',now(),'root','no'), #NEW.id,NEW.cmd_name,NEW.sub_time
('ps aux |grep mysqld',now(),'root','yes'),
('cat /etc/passwd |grep root',now(),'root','yes'),
('netstat -tunalp |grep 3306',now(),'egon','no');
########存储过程
#使用存储过程的优点 :
1. 程序与数据实现解耦
2. 减少网络传输的数量
#创建无参存储过程
delimiter //
create procedure p1()
begin
select * from test;
insert into test(username,dep_id) values('zsb',2);
end //
delimiter ;
#调用存储过程
call p1() ; #在mysql中调用
cursor.callproc('p1') #在python中通过pymysql模块调用
=============
对于存储过程,可以接受参数,参数有三类 :
#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当做返回值
#创建有参存储过程之 in 的使用
delimiter //
create procedure p2(
in m int,
in n int)
begin
select * from test where id between m and n ;
end //
delimiter ;
#调用存储过程
call p2(3,7) ; #在mysql中调用
cursor.callproc('p2',args=(3,7)) #在python中通过pymysql模块调用
================
#创建有参存储过程之out的使用
delimiter //
create procedure p3(
in m int,
in n int,
out res int
)
BEGIN
select * from blog where id between m and n;
set res = 1;
END //
delimiter ;
#调用存储过程
#在mysql中调用
set @res=0; #0代表假(执行失败),1代表真(执行成功)
call p3(3,7,@res);
select @res;
#在python中基于pymysql调用
cursor.callproc('p3',(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123
print(cursor.fetchall()) #查询select的查询结果,只拿到存储过程中的select的查询结果
cursor.execute('select @_p3_0,@_p3_1,@_p3_2;') #@p3_0代表第一个参数,@p3_1代表第二个参数,即返回值
print(cursor.fetchall())
#===============================================
#创建有参存储过程之inout的使用
delimiter //
create procedure p4(
inout m int
)
begin
select * from test where id > m;
set m=1;
end //
delimiter ;
#在mysql中
set @x=2;
call p4(@x);
select @x;
delimiter //
create procedure p5(
inout m int
)
begin
select * from test11111111 where id > m;
set m=1;
end //
delimiter ;
set @x=2;
call p5(@x);
select @x;
#====================捕捉异常+事务===========================
delimiter //
create PROCEDURE p6(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
insert into test(username,dep_id) values('egon',1);
DELETE from tb1111111; #执行失败
COMMIT;
-- SUCCESS
set p_return_code = 0; #0代表执行成功
END //
delimiter ;
#用python模拟
try:
START TRANSACTION;
DELETE from tb1; #执行失败
insert into blog(name,sub_time) values('yyy',now());
COMMIT;
set p_return_code = 0; #0代表执行成功
except sqlexception:
set p_return_code = 1;
rollback;
except sqlwaring:
set p_return_code = 2;
rollback;
mysql> show procedure status like 'p1%'; #查看某一类存储过程
#7. 流程控制
#函数中不要写sql语句,它仅仅是一个功能,是一个在sql中被应用的功能
#若要想在begin...end...中写sql,请用存储过程
delimiter //
create function f5(
i int
);
returns int
begin
declare res int default 0;
if i = 10 then
set res = 100;
elseif i = 20 then
set res = 200;
elseif i = 30 then
set res = 300;
else
set res = 400;
end if ;
return res ;
end //
dilimiter;
#while循环
delimiter //
create procedure proc_while();
begin
declare num int ;
set num = 0 ;
while num < 10 do
select
num ;
set num = num + 1 ;
end while ;
end //
delimiter ;