数据库原理及应用mysql版陈业斌实验三
🏝️专栏：Mysql_猫咪-9527的博客-CSDN博客
🌅主页：猫咪-9527-CSDN博客“欲穷千里目，更上一层楼。会当凌绝顶，一览众山小。”
目录
实验五存储程序
1.实验数据如下
student 表（学生表）
course 表（课程表）
teacher 表（教师表）
score 表（成绩表）
2. 插入数据
student 表中的数据
course 表中的数据
teacher 表中的数据
score 表中的数据
3.实验内容
5-1 创建存储过程 pro_fndname
5-2 设计函数 count_credit
5-3 创建存储过程p_count_credit
5-4 创建触发器 sum_credit
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))
beginselect *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 dataBegindeclare 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()begindeclare 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 dofetch 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_creditafter insert on score for each rowBeginif(new.grade>=60) thenupdate 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_scorebefore delete on student for each rowbegindelete from score where sno=old.sno;end ##delimiter ;
删除前：
删除后：
