MySQL-存储过程-函数-

存储过程

概述

概念

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

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

创建

语法

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

注意

• 参数列表包含三部分

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

• 参数模式：

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

delimiter 结束标记
delimiter $
1
2

调用

语法

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

举例说明

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

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

# 调用inout模式的参数
set @name = 值;
call sp1(@name);
select @name;
1
2
3
4
5
6
7
8
9
10
11
12

案例

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

删除

语法

drop procedure 存储过程名;
1

案例：只支持删除一个

查看

语法

show create procedure 存储过程名;
1

案例

函数

概述

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

好处

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

存储过程与函数区别

创建

语法

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

注意

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

调用

语法

select 函数名（参数列表）;
1

查看

语法

show create function 函数名;
1

删除

语法

drop function 函数名;
1

案例

视图

概述

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

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

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

创建视图

语法

create view 视图名
as
查询语句;
1
2
3

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

案例

视图的修改

语法1

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

语法2

alter view 视图名
as
查询语句;
1
2
3

删除视图drop

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

查看视图

# 方式1
desc 视图名;

# 方式2
show create view 视图名;
1
2
3
4
5

案例

视图可以更新

插入

insert into 视图名 values();
1

更新

update 视图名 set 属性 = 值;
1

删除

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

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

视图不可更新

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

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

• select 中包含子查询

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

视图和表的比较

测试题

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)
)
1
2
3
4
5
6
7

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

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

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;
1
2
3
4
5
6
7

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;
1
2
3
4
5
6

5 删除创建的视图

drop view vi1;
1