MySQL-存储过程-函数-视图

存储过程

概述

概念

• 存储过程和函数定义：
  类似于java中的方法。
  一组预先编写好的SQL语句的集合，理解成批处理语句。

• 好处：
  提高代码的重用性；简化操作；减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

创建create procedure

语法

create procedure 存储过程名（参数列表）
begin
	存储过程体（一组合法有效的SQL语句）
end

注意

• 参数列表包含三部分

参数模式 参数名 参数类型
案例：
in stuname varchar(20);

• 参数模式：

模式	说明
in	该参数可以作为输入，也就是该参数需要传入值（其中in可以省略）
out	该参数可以作为输出，也就是该参数可以作为返回值
inout	该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值。

• 如果存储过程体仅仅只有一句话，begin end可以省略
• 存储过程体中的每条SQL语句的结尾要求必须加分号，存储过程的结尾可以使用delimiter重新设置

delimiter 结束标记
delimiter $

调用call

语法

call 存储过程名（实参列表）;

举例说明

# 调用in模式的参数
call sp1('值');

# 调用out模式的参数
set @name;
call sp1(@name);
select @name;

# 调用inout模式的参数
set @name = 值;
call sp1(@name);
select @name;

案例

• 参数列表为空
  
• 创建带in模式参数的存储过程
  
• 创建带有out模式参数的存储过程
  
• 创建带有inout参数的存储过程
  

删除

语法

drop procedure 存储过程名;

案例：只支持删除一个

查看

语法

show create procedure 存储过程名;

案例

函数

概述

定义
一组预先编译好的SQL语句的集合，理解成批处理语句。

好处

提高代码的重用性。
简化操作。
减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率。

存储过程与函数区别

区别	存储过程	函数
返回值	个数可以是0个或者多个	有且仅有一个
操作	适合批量插入、批量更新	适合做处理后返回一个结果

创建

语法

create function 函数名（参数名 参数类型） returns 返回类型
begin
	函数体
end

注意

• 参数列表—包含两部分：参数名、参数类型
• 函数体—肯定有return语句，如果没有写会报错
  如果return语句没有放在函数体的最后也不报错，但不建议
• 函数体仅有一句话，则可以省略begin end
• 使用delimiter语句设置结束标记

调用

语法

select 函数名（参数列表）;

查看

语法

show create function 函数名;

删除

语法

drop function 函数名;

案例

视图

概述

含义：虚拟表，和普通表一样使用。
mysql5.1版本出现的新特性，它的数据来自于表，通过执行时动态生成。比如：普通班级和舞蹈班级的例子。
行和列的数据来自定义属性的查询中使用的表，并且是在使用视图时动态生成的。
只保存了sql逻辑，不保存查询结果。

应用
多个地方用到同样的查询结果。
该查询结果使用的sql语句较复杂。

好处
简化sql语句；
提高了sql语句的重用性；
保护基表的数据，提高了安全性；

创建视图

语法

create view 视图名
as
查询语句;

视图的使用 就是把其当作表来使用就可以了；

案例

视图的修改

语法1

create or replace view 视图名
as
查询语句;

语法2

alter view 视图名
as
查询语句;

删除视图drop

drop view 视图1，视图2....

查看视图

# 方式1
desc 视图名;

# 方式2
show create view 视图名;

案例

视图可以更新

插入

insert into 视图名 values();

更新

update 视图名 set 属性 = 值;

删除

delete from 视图名 where = 筛选条件;

均对原始数据表也进行了修改

视图不可更新

视图的可更新性和视图中查询的定义有关系。

• 包含以下关键字的sql语句：分组函数（count、sum、avg、min、max）、distinct、group by、having、union或者union all。
  
• 常量视图

• select 中包含子查询

• join
  
• from一个不能更新的视图
  
• where子句的子查询引用了from子句中的表

视图和表的比较

区别	视图	表
语法	create view	create table
是否占用物理空间	只保存sql逻辑	保存数据
功能	查（一般不用增删改）	增删改查

测试题

1 book创建

create table book(
	bid int primary key,
	bname varchar(20) unique not null,
	price float default 10,
	btypeId int,
	foreign key(btypeId) references bookType(id)
)

2 开启事务，向表中插入一行数据

set autocommit = 0;
start transaction;
insert into book values(1,"java基础",45.26,5)；
commit;

3 创建视图，实现查询价格大于100的书名和类型名

create or replace view vi1
as
select b.bname,bt.name
from book b
inner join bookType bt 
on b.btypeId = bt.id
where b.price > 100;

4 修改视图，实现查询价格在90~120之间的书名和价格

create or replace view vi1
as
select bname,price
from book bo
inner join bookType bt on bo.btypeId = bt.id 
where price between 90 and 120;

5 删除创建的视图

drop view vi1;