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

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 ;