/*
触发器
*/
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;