数据库原理及应用mysql版陈业斌实验五
🏝️专栏：Mysql_猫咪-9527的博客-ZEEKLOG博客
🌅主页：猫咪-9527-ZEEKLOG博客
“欲穷千里目，更上一层楼。会当凌绝顶，一览众山小。”
目录
5-5 创建级联删除触发器 delstudent_score
实验五存储程序
【实验目的】
掌握存储函数、存储过程与触发器等存储程序的相关操作方法，理解存储函数、存储过程与触发器的作用。
1.实验数据如下
student 表（学生表）
CREATE TABLE student ( sno CHAR(5) PRIMARY KEY, snme VARCHAR(20) NOT NULL, sdept VARCHAR(20) NOT NULL, sclass CHAR(2) NOT NULL, ssex CHAR(1), birthday DATE, totalcredit DECIMAL(4,1) ); course 表（课程表）
CREATE TABLE course ( cno CHAR(3) PRIMARY KEY, cname VARCHAR(50), ctime DECIMAL(3,0), credit DECIMAL(3,1) ); teacher 表（教师表）
CREATE TABLE teacher ( tno CHAR(6) PRIMARY KEY, tname VARCHAR(20), tsex CHAR(1), tdept VARCHAR(20) ); score 表（成绩表）
CREATE TABLE score ( sno CHAR(5), cno CHAR(3), tno CHAR(6), grade DECIMAL(5,1), PRIMARY KEY (sno, cno, tno), CONSTRAINT fk_sno FOREIGN KEY(sno) REFERENCES student(sno), CONSTRAINT fk_cno FOREIGN KEY(cno) REFERENCES course(cno), CONSTRAINT fk_tno FOREIGN KEY(tno) REFERENCES teacher(tno) ); 2. 插入数据
student 表中的数据
INSERT INTO student VALUES('96001', '马小燕', '计算机', '01', '女', '2000/01/02', 0); INSERT INTO student VALUES('96002', '黎明', '计算机', '01', '男', '2000/03/05', 0); INSERT INTO student VALUES('96003', '刘东明', '数学', '01', '男', '2000/10/05', 0); INSERT INTO student VALUES('96004', '赵志勇', '信息', '02', '男', '2000/08/08', 0); INSERT INTO student VALUES('97001', '马蓉', '数学', '02', '女', '2001/03/04', 0); INSERT INTO student VALUES('97002', '李成功', '计算机', '01', '男', '2001/09/10', 0); INSERT INTO student VALUES('97003', '黎明', '信息', '03', '女', '2002/02/08', 0); INSERT INTO student VALUES('97004', '李丽', '计算机', '02', '女', '2002/01/05', 0); INSERT INTO student VALUES('96005', '司马志明', '计算机', '02', '男', '2001/11/23', 0); course 表中的数据
INSERT INTO course VALUES('001', '数学分析', 64, 4); INSERT INTO course VALUES('002', '普通物理', 64, 4); INSERT INTO course VALUES('003', '微机原理', 56, 3.5); INSERT INTO course VALUES('004', '数据结构', 64, 4); INSERT INTO course VALUES('005', '操作系统', 56, 3.5); INSERT INTO course VALUES('006', '数据库原理', 56, 3.5); INSERT INTO course VALUES('007', '编译原理', 48, 3); INSERT INTO course VALUES('008', '程序设计', 32, 2); teacher 表中的数据
INSERT INTO teacher VALUES('052501', '王成刚', '男', '计算机'); INSERT INTO teacher VALUES('052502', '李正科', '男', '计算机'); INSERT INTO teacher VALUES('052503', '严敏', '女', '数学'); INSERT INTO teacher VALUES('052504', '赵高', '男', '数学'); INSERT INTO teacher VALUES('052505', '刘玉兰', '女', '计算机'); INSERT INTO teacher VALUES('052506', '王成刚', '男', '信息'); INSERT INTO teacher VALUES('052507', '马悦', '女', '计算机'); score 表中的数据
INSERT INTO score VALUES('96001', '001', '052503', 77.5); INSERT INTO score VALUES('96001', '003', '052501', 89); INSERT INTO score VALUES('96001', '004', '052502', 86); INSERT INTO score VALUES('96001', '005', '052505', 82); INSERT INTO score VALUES('96002', '001', '052504', 88); INSERT INTO score VALUES('96002', '003', '052502', 92.5); INSERT INTO score VALUES('96002', '006', '052507', 90); INSERT INTO score VALUES('96005', '004', '052502', 92); INSERT INTO score VALUES('96005', '005', '052505', 90); INSERT INTO score VALUES('96005', '006', '052505', 89); INSERT INTO score VALUES('96005', '007', '052507', 78); INSERT INTO score VALUES('96003', '001', '052504', 69); INSERT INTO score VALUES('97001', '001', '052504', 96); INSERT INTO score VALUES('97001', '008', '052505', 95); INSERT INTO score VALUES('96004', '001', '052503', 87); INSERT INTO score VALUES('96003', '003', '052501', 91); INSERT INTO score VALUES('97002', '003', '052502', 91); INSERT INTO score VALUES('97002', '004', '052505', NULL); INSERT INTO score VALUES('97002', '006', '052507', 92); INSERT INTO score VALUES('97004', '005', '052502', 90); INSERT INTO score VALUES('97004', '006', '052501', 85); 注：把上面的实验数据添加上再开始实验。
3.实验内容
5-1 创建存储过程 pro_fndname
存储过程 profndname 通过模糊查询学生姓名，输入一个汉字，返回所有包含该汉字的学生记录。
delimiter ## create procedure pro_fndname(in seach_char char(1)) begin select *from student where snme like concat('%',seach_char,'%'); end ## delimiter ;
5-2 设计函数 count_credit
函数 countcredit 根据学号计算学生的总学分，前提是成绩大于或等于60分时才能获得课程学分。
delimiter ## create Function count_credit(v_sno char(6)) returns int reads sql data Begin declare sums float ; select ifnull(sum(credit),0) into sums from course natural join score where grade >=60 and v_sno=sno; return sums; end ## delimiter ; 5-3 创建存储过程p_count_credit
存储过程 p_count_credit 使用 countcredit 函数更新 student 表中的总学分。
delimiter ## create procedure p_count_credit() begin declare v_sno varchar(20); declare v_credit int default 0; declare stucur cursor for select sno from student; declare exit handler for not found close stucur ; open stucur; while true do fetch stucur into v_sno; update student set totalcredit =count_credit(v_sno) where sno=v_sno; end while; close stucur; end ## delimiter ; 
5-4 创建触发器 sum_credit
触发器 sum_credit 在向 score 表插入记录时，自动更新 student 表中的总学分。只有成绩大于或等于60分时，才会增加学分。
delimiter ## create trigger sum_credit after insert on score for each row Begin if(new.grade>=60) then update student set totalcredit =totalcredit+(select credit from course where cno=new.cno) where student.sno=new.sno; end if; end ## delimiter ; 
5-5 创建级联删除触发器 delstudent_score
触发器 delstudent_score 实现级联删除，当删除 student 表中的学生记录时，会同时删除 score 表中对应学号的成绩记录。
delimiter ## create trigger del_studnet_score before delete on student for each row begin delete from score where sno=old.sno; end ## delimiter ; 
删除前：
删除后：
