mysql 视图、触发器、存储过程

# 创建视图
create view emp2dep as select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id = dep.id;

create view 视图名称 sql语句;

# 修改视图
alter view 视图名称 as sql语句;

# 删除视图
drop view 视图名称;

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

# 增=》insert
# 在插入之前做操作
create trigger tri_before_insert_t1 before insert on t1 for each row
begin
    sql语句;
end

# 在插入之后做操作
create trigger tri_after_insert_t1 after insert on t1 for each row
begin
    sql语句;
end

# 删除=》delete
# 在删除之前做操作
create trigger tri_before_delete_t1 before delete on t1 for each row
begin
    sql语句;
end
# 在删除之后做操作
create trigger tri_after_delete_t1 after delete on t1 for each row
begin
    sql语句;
end


# 修改=》update





# 例如
insert into tt1 values(1,"egon",‘male‘);


delimiter // # 由于在触发器中需要用到;然后;在mysql中会被认为是结束符号导致触发器语法错误，所以需要在写触发器之前将结束符号改为// 也可以是其他符号
create trigger tri_before_insert_tt1 before insert on tt1 for each row
begin
    insert into tt2 values(NEW.name);
end //

delimiter ;



insert into tt1 values(2,"tom",‘female‘);





# 练习
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代表执行失败
);

CREATE TABLE errlog (
    id INT PRIMARY KEY auto_increment,
    err_cmd CHAR (64),
    err_time datetime
);

delimiter $$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
    if NEW.success = ‘no‘ then
        insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
    end if;
end $$
delimiter ;

insert into cmd(user,priv,cmd,sub_time,success) values
    (‘egon‘,‘0755‘,‘ls -l /etc‘,NOW(),‘yes‘),
    (‘egon‘,‘0755‘,‘cat /etc/passwd‘,NOW(),‘no‘),
    (‘egon‘,‘0755‘,‘useradd xxx‘,NOW(),‘no‘),
    (‘egon‘,‘0755‘,‘ps aux‘,NOW(),‘yes‘);

drop trigger tri_after_insert_cmd ;

# 创建无参存储过程
delimiter $$
create procedure p1()
begin
    select * from emp;
end $$

delimiter ;

call p1();

# 创建有参存储过程
delimiter $$
create procedure p2(
    in n int,
    out res int
)
begin
    select * from emp where id > n;
    set res=1;
end $$

delimiter ;



==========================>在mysql里如何调用存储过程
mysql> set @x=1111;
Query OK, 0 rows affected (0.00 sec)

mysql> call p2(3,x);
ERROR 1414 (42000): OUT or INOUT argument 2 for routine db4.p2 is not a variable or NEW pseudo-variable in BEFORE trigger
mysql> call p2(3,@x);