mysql 触发器测试 mysql触发器例题

1.创建存储过程

（1）创建一个存储过程，查询女同学选修的课程信息。

#创建学生表
create table st_table(
    id int auto_increment primary key,
    name varchar(10),
    sex varchar(1)
);
#创建学生选修课信息表
create table course_table(
    id int,
    course1 varchar(20) default null,
    course2 varchar(20) default null,
    course3 varchar(20) default null,
    course4 varchar(20) default null,
    course5 varchar(20) default null,
    course6 varchar(20) default null,
    course7 varchar(20) default null,
    course8 varchar(20) default null,
    course9 varchar(20) default null,
    course10 varchar(20) default null
);
#添加外键
alter table course_table add constraint fk_id foreign key(id) references st_table(id);


#向学生表里面插入数据
insert into st_table(name,sex) 
values('张三','男'),
('李四','男'),
('小倩','女'),
('小青','女');
#学生选修课里面插入信息
insert into course_table(id,course1,course2) values(1,'搬砖',null),(2,'手劈榴莲',null),(3,'奶茶',null),(4,'手摇奶茶','手摇咖啡');

delimiter $
create procedure check_sex()
comment '学生名选修课信息'
	begin
    select * from(
    select a.name,a.sex,b.course1,b.course2,b.course3,b.course4,b.course5,b.course6,b.course7,b.course8,b.course9,b.course10 from
    st_table a left outer join course_table b on a.id=b.id) c#作为一个新的多表联合查询的派生表，必须要有一个别名
    where c.sex=st_sex;
end $
delimiter ;

#查看女学生的选修课信息
call check_sex();

#需要注意的是，这两个表里面时有同字段名字的存在(id)，所以在选择显示的时候应该区分a表的id与b表的id
#(如果字段是不一样的则可以使用select *来进行简写)
#使用到的左外连接:select * from st_table a left outer join course_table b on a.id=b.id;

（2）创建一个带输入参数的存储过程，输入t_course表中某个老师的姓名，输出该老师讲授的课程course_id与course_name。

一个老师按实际生活中是可以进行多个选修课的教学的

#创建一个教师表
create table t_t(
    id int auto_increment primary key,
    name varchar(20)
);
#创建一个t_course表
create table t_course(
    id int,
    name varchar(20),
    course_id int,
    course_name varchar(20)
);
#添加外键
alter table t_course add constraint fk_tid foreign key(id) references t_t(id);
#插入教师的信息
insert t_t(name) values('张三'),('李四');
#插入教师的授课信息
insert t_course values(1,'张三',101,'手摇奶茶'),
(1,'张三',102,'脚摇奶茶'),
(2,'李四',103,'手摇榴莲'),
(2,'李四',104,'脚摇榴莲');

#创建带一个输入参数的存储过程
delimiter $
create procedure t_check(in t_name varchar(20))
begin
select course_id,course_name from t_course where name=t_name;
end $
delimiter ;
call t_check('张三');

（3）创建一个带输出参数的存储过程，输出教师的总人数。

# 创建一个教师表
create table t_k(
    id int auto_increment primary key,
    name varchar(20)
);
#插入教师的信息
insert t_k(name) values('张三'),('李四');
#创建带一个输出参数的存储过程
delimiter $
create procedure c_check(out ccheck int)
begin
	# declare ccheck int default 0;
    set ccheck=(select count(*) from t_k);
end $
delimiter ;
#通过传入一个变量来接受输出参数
call c_check(@ccheck);
#查看变量被赋值多少
select @ccheck;

2.创建触发器

（1）创建Insert触发器，每向t_student中插入一条记录后，则向t_log(tname,logtime)表中插入该表的表名t_student和插入的时间。

#创建t_student表
create table t_student(id int,name varchar(20));
#测试插入数据
insert into t_student values(1,'张三'),(2,'李四');
#创建t_log表
create table t_log(tname varchar(30),logtime datetime);
#创建爱你触发器
delimiter $
create trigger luichun
after insert
on t_student for each row
begin
    insert into t_log(tname,logtime) values('t_student',now());
end $
delimiter ;
#测试插入
insert into t_student values(3,'张三'),(4,'李四');

（2）创建一个t_course表的更新触发器，当更新t_course表中某一条记录后，分别查看更新前后的课程名称。

#创建t_course表
create table t_course(id int auto_increment primary key,course varchar(20));
#向表内插入数据
insert into t_course values(1,'手摇奶茶'),(2,'手摇榴莲');
#更新测试
update t_course set course='手摇开水' where id='1'; 

#创建触发器
delimiter $
create trigger luichun1
after update
on t_course for each row
begin
    set @oldcourse=old.course;
    set @newcourse=new.course;
    select old.course into @oldcourse;
    select new.course into @newcourse;
end $
delimiter ;
#查看更新前后课程名称
select @oldcourse,@newcourse;

（3）创建一个delete触发器，删除t_student表中的数据时，将对应的t_score表中的数据删除。

#t_student表的创建
create table t_student(
    id int auto_increment primary key,
    name varchar(20)
);
#创建t_score表
create table t_score(
    id int,
    name varchar(20)
);
#创建外键
alter table t_score add constraint fk_score_id foreign key(id) references t_student(id);

#创建触发器
delimiter $
create trigger luichun2
before delete
on t_student for each row
begin
    delete  from t_score where t_score.id=old.id;
end $
delimiter ;
#插入数据
insert into t_student(id,name) values(1,'张三'),(2,'李四');
insert into t_score(id,name) values(1,'100'),(2,'200');
#删除数据
delete from t_student where id='1';
#删除触发器
drop trigger luichun2;