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 beginIF 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 pymysqlconn = 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()mit()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('外交部'),('公关部'),('')#创建触发器的语法 : CREATETRIGGER tregger_nametregger_time trigger_eventON tbl_name FOR EACH ROWtrigger_bodytrigger_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 //CREATETRIGGER tri_after_insert_cmd_logafter insertON cmd_log FOR EACH ROWBEGIN if new.is_success = 'no' THENinsert 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()beginselect * 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)BEGINselect * 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=123print(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)beginselect * 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)beginselect * 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)BEGINDECLARE exit handler for sqlexceptionBEGIN-- ERRORset p_return_code = 1;rollback;END;DECLARE exit handler for sqlwarningBEGIN-- WARNINGset p_return_code = 2;rollback;END;START TRANSACTION;insert into test(username,dep_id) values('egon',1);DELETE from tb1111111; #执行失败COMMIT;-- SUCCESSset 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 thenset res = 200;elseif i = 30 thenset res = 300;else set res = 400;end if ;return res ;end //dilimiter;#while循环 delimiter //create procedure proc_while();begindeclare num int ;set num = 0 ;while num < 10 doselect num ;set num = num + 1 ;end while ;end //delimiter ;
