欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL视图,触发器,事务,存储过程,函数

程序员文章站 2022-06-04 07:50:16
...

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 ;