Mysql数据库实验报告--实验五 存储过程和触发器

在这个系列会更新一些最近老师要求写的实验报告~
大家尽量对着我的文章做一个参考，不要盲目的cv，毕竟这个对于我们以后的工作学习还是十分重要的。

从这个实验开始，就不在cmd命令行里面进行mysql数据库的操作，因为代码长度和代码的复杂性，需使用 MySQL Workbench：

双击数据库（密码还是启动cmd命令行的密码）

使用示例：
查询databases

show databases;

运行当前行：ctrl+enter

其他常用快捷方式：
1、执行整篇sql脚本：【Ctrl】+【Shift】+【Enter】

2、执行当前行：【Ctrl】+【Enter】

3、注释/取消注释：【Ctrl】+【/】

4、格式化sql语句（美化sql语句）：【Ctrl】+【B】

5、自动补全：【Ctrl】+【Space】

---

事不宜迟，贴上本次实验的题目：

创建学生表，课程表，选课表。

#创建学生表：包括学号，姓名，年龄，性别，院系
CREATE TABLE student
(
Sno VARCHAR(7)PRIMARY KEY,
Sname VARCHAR(10)NOT NULL,
Sage INT,
Ssex VARCHAR(2),
Sdept VARCHAR(20) DEFAULT '计算机系'
);

#创建课程表：包括课程号，课程名，选修课课程号，学分
CREATE TABLE course
(
Cno VARCHAR(10)PRIMARY KEY,
Cname VARCHAR (20)NOT NULL,
Cpno VARCHAR(10),
Ccredit INT
);


#创建选课表
CREATE TABLE sc
(
Sno VARCHAR(7),
Cno VARCHAR(10),
grade INT,
FOREIGN KEY (sno) REFERENCES student(Sno),
FOREIGN KEY (cno) REFERENCES course(cno)
);

向表中插入数据：

#  向学生表S中插入数据
INSERT INTO student
   (Sno,Sname,Sage,Ssex,Sdept)
VALUE
   ("10001","张三",20,'男','计算机'),
   ("10002","李梅",19,'女','计算机'),
   ("10003","王五",18,'男','CS'),
   ("10004","小明",21,'男','计算机'),
   ("10006","黎明",18,'男','艺术表演'),
   ("10008","杰克",21,'男','计算机'),
   ("10005","小红",22,'女','CS');

#  向课程表C中插入数据   
INSERT INTO course
   (Cno,Cname,Cpno,Ccredit)
VALUE
   ("1","离散数学",NULL,5),
   ("2","线性代数",'3',6),
   ("3","高等数学",NULL,4),
   ("4","数据结构",'3',6),
   ("5","操作系统",'1',4),
   ("6","数据库",'4',5);



#  向选课表SC中插入数据  
INSERT INTO sc
  (Sno,Cno,grade)
VALUE
  ("10001","1",70),
  ("10001","6",56),
  ("10003","4",90),
  ("10003","5",83),
  ("10004","1",75),
  ("10004","3",90),
  ("10008","1",70),
  ("10008","5",70),
  ("10008","6",88),
  ("10002","1",85),
  ("10002","6",89);

一、创建并调用如下的存储过程。

1、 定义一个存储函数，按性别计算所有学生的平均年龄。

delimiter //
create procedure avg_age_sex(in stu_sex varchar(30))
begin
select avg(Sage) from student where Ssex=stu_sex;
end//
delimiter ;
call avg_age_sex('女');

2、 编写存储过程，根据给定的学生学号，计算该生的平均成绩

delimiter //
create procedure avg_grade_cno1(in stu_cno int)
begin
select avg(grade) as '平均成绩' from sc where Cno=stu_cno;
end//
delimiter ;

call avg_grade_cno1(1);

3、 创建存储过程，能够查询出指定课程（课程名）的平均成绩，并用OUT参数返回结果。

delimiter //
create procedure avg_grade7(in stu_cno varchar(30),out avgsc1 decimal(6,4))
begin
select avg(grade) as '平均成绩' from sc,course where course.Cname=stu_cno and course.Cno=sc.Cno;
end//
delimiter ;
set @avgsc1 = 60;
call avg_grade7('高等数学',@avgsc1);
select @avgsc1;

4、创建存储过程p_Insertstu，实现向学生数据表中插入一个新同学，并提供相应的实参值（实参值由用户自定）。

delimiter //
create  procedure p_Insertstu(in Sno varchar(7),in Sname varchar(10),in Sage int,in Ssex varchar(2),in Sdept varchar(20))
reads sql data
begin
insert into student values(Sno,Sname,Sage,Ssex,Sdept);
end//
delimiter ;
call p_Insertstu('10009','紫涵','20','女','计算机');
select  * from student;

二、创建触发器。

1、创建触发器，在学生表中删除学生信息的同时将选课信息表中该学生的选课信息删除，以确保数据的完整性；

#关闭安全模式
show variables like 'SQL_SAFE_UPDATES';
SET SQL_SAFE_UPDATES = 0;
drop trigger if exists delete_stu;
#题目代码
delimiter //
create trigger delete_stu1
after delete on student
for each row
begin 
	delete from sc where sc.Sno=@Sno;
end //

2、创建触发器，在选课信息表中添加学生选课信息的同时，增加学生表中该学生的选课数量信息，以确保数据的完整性；

delimiter //
create trigger update_stu1
after insert on sc
for each row
begin 
	set @csum=0;
    select count(Sno) into @csum from sc where Sno=@Sno;
	update student set csum=@csum where Sno=@sno;
end //

完整sql文件：
链接：https://pan.baidu.com/s/1PD_mD9Q-uCNQPANq2TmFew?pwd=69ev
提取码：69ev

推荐一个b站讲的不错的视频：
mysql数据库高级篇