mysql_触发器_存储过程_视图

/*
    触发器
*/

create trigger update_log_after  //触发器名：update_log_after
after 	 //修改student表之后触发
update on student
for each row 
insert into t_opt(opt,date) values(concat(new.id,'被修改'),now()) //修改之后写入t_opt表，

create trigger insert_log_before  //触发器名：insert_log_before
before 	 //插入student表之前触发
insert on student
for each row 
insert into t_opt(opt,date) values('插入了一条数据',now())  //插入之前写入t_opt表，

create trigger delete_log_after  //触发器名：delete_log_after
after 	 //删除student表之后触发
delete on student
for each row   //定义触发器每隔一行执行一次动作,而不是对整个表执行一次
insert into t_opt(opt,date) values(concat(OLD.id,OLD.name,'删除'),now())  
//删除之后写入t_opt表,
//OLD:用于delete; new 用于insert语句; new和OLD用于update语句


触发条件:delete from student where name='zhang'

/*
    存储过程
*/

create procedure mypro1() //存储过程名：mypro1
    BEGIN
        select name from student where id=6;
    end;

调用: call mypro1()


create procedure mypro2(i int) //存储过程名：mypro2
    BEGIN
        select name from student where id=i; //传参
    end;

调用: call mypro2(6)



create procedure mypro3(proId int) //存储过程名：mypro3
    BEGIN
	select p.name,p.id,GROUP_CONCAT(s.name) as names from student s
	    LEFT JOIN professional p on p.id=s.pro
		where p.id=proId  //传参
		    GROUP BY pro;  
    end;

调用: call mypro3(6)

/*
    视图
*/

create view v_pro_names 
    as select p.id,p.name,GROUP_CONCAT(s.name) as names from student s
	left join professional p
	    on p.id=s.pro
		group by pro;