文章目录
存储过程
概述
概念
-
存储过程和函数定义:
类似于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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
案例
- 参数列表为空
- 创建带in模式参数的存储过程
- 创建带有out模式参数的存储过程
- 创建带有inout参数的存储过程
删除
语法
drop procedure 存储过程名;
- 1
案例:只支持删除一个
查看
语法
show create procedure 存储过程名;
- 1
案例
函数
概述
定义
一组预先编译好的SQL语句的集合,理解成批处理语句。
好处
提高代码的重用性。
简化操作。
减少了编译次数并且减少了和数据库服务器的连接次数,提高了效率。
存储过程与函数区别
区别 | 存储过程 | 函数 |
---|---|---|
返回值 | 个数可以是0个或者多个 | 有且仅有一个 |
操作 | 适合批量插入、批量更新 | 适合做处理后返回一个结果 |
创建
语法
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子句中的表
视图和表的比较
区别 | 视图 | 表 |
---|---|---|
语法 | 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)
)
- 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