mysql上机实验5

实验五 2学时

存储过程与函数、视图

一、学习目标

1. 掌握如何创建存储过程
2. 掌握如何创建存储函数
3. 熟悉变量的使用方法
4. 熟悉如何定义条件和处理程序
5. 了解光标的使用方法
6. 掌握流程控制的使用
7. 掌握如何调用存储过程和函数
8. 熟悉如何查看存储过程和函数
9. 掌握修改存储过程和函数的方法
10. 熟悉如何删除存储过程和函数
11. 掌握创建存储过程和函数的方法和技巧
12. 了解视图的含义和作用
13. 掌握创建视图的方法
14. 熟悉如何查看视图
15. 掌握修改视图的方法
16. 掌握更新视图的方法
17. 掌握删除视图的方法
18. 掌握视图应用的方法和技巧

二、实验内容

1. 通过实例掌握存储过程和函数的创建和使用，步骤如下：

①创建一个名称为sch的数据表，表结构如下所示，将下面sch表内容中的数据插入到sch表中

sch表结构

字段名	数据类型	主键	外键	非空	唯一	自增
id	INT(11)	是	否	是	是	否
name	VARCHAR(50)	否	否	是	否	否
glass	VARCHAR(50)	否	否	是	否	否

sch表内容

id	name	glass
1	xiaoming	glass1
2	xiaojun	glass2

create table sch
(id int(11) not null unique primary key,
 name varchar(50) not null,
glass varchar(50) not null);

insert into sch value
(1,'xiaoming','glass 1'),
(2,'xiaojun','glass 2');

②创建一个存储函数，用来统计表sch中的记录数

create function count_sch()
returns int
return (select count(*) from sch);

③创建一个存储过程，通过调用存储函数的方法来获取表sch中的记录数和sch表中id的和

delimiter //
create procedure add_id(out count int)
begin
declare itmp int;
declare cur_id cursor for select id from sch;
declare exit handler for not found close cur_id;
select count_sch() into count;
set @sum=0;
open cur_id;
repeat
fetch cur_id into itmp;
if itmp<10
then set @sum= @sum+itmp;
end if;
until 0 end repeat;
close cur_id;
end //

1. 掌握视图的创建、查询、更新和删除操作。

假如HenanHebei的3个学生参加Tsinghua University、Peking University的自学考试，现在需要用数据对其考试的结果进行查询和管理，Tsinghua University的分数线为40，Peking University的分数线为41。学生表包含了学生的学号、姓名、家庭住址和电话号码；报名表包含学号、姓名、所在学校和报名的学校，表结构以及表中的内容分别如下列各表所示。

stu表结构

字段名	数据类型	主键	外键	非空	唯一	自增
s_id	INT	是	否	是	是	否
s_name	VARCHAR(20)	否	否	是	否	否
addr	VARCHAR(50)	否	否	是	否	否
tel	VARCHAR(50)	否	否	是	否	否

sign表结构

字段名	数据类型	主键	外键	非空	唯一	自增
s_id	INT	是	否	是	是	否
s_name	VARCHAR(20)	否	否	是	否	否
s_sch	VARCHAR(50)	否	否	是	否	否
s_sign_sch	VARCHAR(50)	否	否	是	否	否

stu_mark表结构

字段名	数据类型	主键	外键	非空	唯一	自增
s_id	INT	是	否	是	是	否
s_name	VARCHAR(20)	否	否	是	否	否
mark	INT	否	否	是	否	否

stu表内容

s_id	s_name	addr	tel
1	XiaoWang	Henan	0371-12345678
2	XiaoLi	Hebei	1336312XXXX
3	XiaoTian	Henan	0371-12345670

sign表内容

s_id	s_name	s_sch	s_sign_sch
1	XiaoWang	Middle School1	Peking University
2	XiaoLi	Middle School2	Tsinghua University
3	XiaoTian	Middle School3	Tsinghua University

stu_mark表内容

s_id	s_name	mark
1	XiaoWang	80
2	XiaoLi	71
3	XiaoTian	70

步骤如下：

①创建学生表stu，插入3条记录

create table stu(
s_id int primary key,
s_name varchar(20) not null,
addr varchar(50) not null,
tel varchar(50) not null);

insert into stu
values
(1,'XiaoWang','Henan','0371-12345678'),
(2,'XiaoLi','Hebei','1336312XXXX'),
(3,'XiaoTian','Henan','0371-12345670');

②创建报名表sign，插入3条记录

create table sign
(
s_id int primary key,
s_name varchar(20) not null,
s_sch varchar(50) not null,
s_sign_sch varchar(50) not null
);

insert into sign
values
(1,'Xiaowang','Middle School1','Peking University'),
(2,'Xiaoli','Middle School2','Tsinghua University'),
(3,'XiaoTian','Middle School3','Tsinghua University');

③创建成绩表stu_mark，插入3条记录

create table stu_mark
(
s_id int primary key,
s_name varchar(20) not null,
mark int not null
 );

insert into stu_mark 
values
(1,'XiaoWang',80),
(2,'XiaoLi',71),
(3,'XiaoTian',70);


select * from stu_mark;

④创建考上Peking University的学生的视图

create view admit_PU_view
as select * from stu
where s_id=
(select s_id from stu_mark
where mark>41 and s_id=
(select s_id from sign
where s_sign_sch='Peking University')
);
select * from admit_PU_view;

⑤创建考上Tsinghua University的学生的视图

create view admit_TU_view
as select * from stu
where s_id in
(select s_id from stu_mark
where mark>40 and s_id in
(select s_id from sign
where s_sign_sch='Tsinghua University')
);
select * from admit_TU_view;

⑥XiaoTian的成绩在录入的时候录入错误，多录了50分，对其录入成绩进行更正

update stu_mark
set mark=mark-50
where s_name='XiaoTian';

⑦查看更新过后视图和表的情况

select * from stu_mark;
select * from admit_TU_view;

⑧查看视图的创建信息

show create view admit_PU_view;
show create view admit_TU_view;

⑨删除创建的视图

drop view admit_PU_view;
drop view admit_TU_view;

三、思考题（04）

1. MySQL存储过程和函数有什么区别？
2. 在存储过程中可以调用其他存储过程吗？
3. 存储过程的参数不要与数据表中的字段名相同的原因是什么？
4. 存储过程的参数可以使用中文吗？
5. MySQL中视图和表的区别以及联系是什么？