存储过程(Stored Procedure)
是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中经过第一次编译后再次调用不需要再次编译,
用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
mysql的存储优点
- 1.增强sql语言的功能和灵活性
-
2.存储过程被创建后,可以在程序中被多次调用
-
3.较快的执行速度
-
4.减少网络流量
-
5.保证数据的安全
mysql的存储过程
-
1.较快的执行速度
-
2.减少网络流量
-
3.作为一个安全机制
为什么很少使用存储过程?
-
1.可移植性差
-
2.对于简单sql语句,存储过程没什么优势
-
3.如果存储过程中不一定减少网络传输
-
4.如果只有一个用户使用数据库,那么存储过程对安全没影响
-
5.团队开发时需要统一标准,否则后期维护成本大
-
6.在大并发量访问的情况下,不宜过多涉及运算的存储过程
-
7.业务逻辑复杂时,特别涉及到对很大的表进行操作的时候,不如在前端优化。
定义存储的过程
语法
create procedure 过程名 (参数1,参数2...)
begin
sql语句;
end
提示:创建存储过程之前我们必须修改mysql默认结束符。
delimiter【分隔符】可以修改执行符号。
语法:
delimiter 执行符号 可以是%或//
use book
调用存储过程
- call 过程名(参数一,参数二,…) 自定义的结束符号
例子过程
mysql> delimiter %
mysql> create procedure selCg()
-> begin
-> select * from category;
-> end%
mysql> call selCg%
+---------+---------------+
| bTypeId | bTypeName |
+---------+---------------+
| 1 | windows应用 |
| 2 | 网站 |
| 3 | 3D动画 |
| 4 | linux学习 |
| 5 | Delphi学习 |
| 6 | 黑客 |
| 7 | 网络技术 |
| 8 | 安全 |
| 9 | 平面 |
| 10 | AutoCAD技术 |
+---------+---------------+
调用存储过程
call 存储过程名称;
查看创建的存储过程
show procedure status\G; #查看所有的存储过程
删除存储过程
drop procedure 名称; #删除
存储过程的参数类型
关键字 | 描述 | 名称 |
---|---|---|
in | 输入参数 | 传递值给存储过程,必须在调用存储过程时指定,在存储过程中修改该参数的值不能;默认类型是in |
out | 输出参数 | 该值可在存储过程内部被改变,并可返回 |
inout | 输入/输出参数 | 调用时指定,并且可被改变和返回 |
使用说明
设置局部变量:set @y=1//
例子:
DELIMITER //
mysql> create procedure xx(in abc int)
-> begin
-> select abc;
-> set abc=2;
-> select abc;
-> end
-> //
mysql> set @y=1//
mysql> call xx(@y)//
+------+
| abc |
+------+
| 1 |
+------+
1 row in set (0.27 sec)
+------+
| abc |
+------+
| 2 |
+------+
1 row in set (0.27 sec)
存储过程的传出参数
特点:不读取外部变量值,在存储过程执行完毕后保留新值
例子:
out参数的传出;
create procedure pout(out p_out int)
begin
select p_out;
set p_out=2;
select p_out;
end//
set @out=0//
call pout(out)//
+--------+
| @p_out |
+--------+
| 2 |
+--------+
存储过程的传入与传出
inout:先传入后传出
mysql> create procedure pinout(inout p_out int) begin select p_out; set p_out=2; select p_out; end//
set @pinout=100;
mysql> call pinout(@pinout)//
+-------+
| p_out |
+-------+
| 100 |
+-------+
1 row in set (0.00 sec
+-------+
| p_out |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)
存储过程变量的使用
mysql中使用declare进行变量定义
decalare 变量名 数据类型(int,float,varcahr)
变量赋值 set 变量名 = 表达式
例子:
mysql>delimiter //
mysql> create procedure decl()
-> begin
-> declare name varchar(200);
-> set name=(select bName from books where bId=12);
-> select name;
-> end//
craete table t2(id int(8))//
call decl(5)//
selcet * from t2//
**存储过程语句的注释**
mysql有两种风格
“--“:单行注释
“/*…..*/”:一般用于多行注释
Mysql中的变量
mysql> show global variables; #查看全局变量
mysql> show session variables; #查看会话变量
mysql> set session sort_buffer_size = 40000; #设置会话变量
mysql> show session variables like “sort_buffer_size”; #查看会话变量
mysql> show global variables like “%关键字%”; #查看全局变量
mysql> set @y = 3; #用户自定义变量,直接赋值
mysql> select max(uid) into @y from user; #使用sql命令查询结果赋值
存储过程中的运算
例子:
mysql> set @z=1+2;select @z;
mysql> set @x=1; set @y=2;set @z=@x*@y; select @z;
mysql> set @x=1; set @y=2;set @z=@x-@y; select @z;
mysql> set @x=1; set @y=2;set @z=@x/@y; select @z;
存储过程中的使用:
mysql> drop procedure if exists say;
mysql> delimiter //
mysql> create procedure say(
in bash char(20), in nologin char(25), out x int , out y int
)
begin
declare z int ;
set z=0;
select count(name) into @x from db9.user where shell=bash;
select count(name) into @y from userdb.user where shell=nologin;
set z=@x+@y;
select z;
end
//
mysql> delimiter ;
执行存储过程查询结果
mysql>call say("/bin/bash","/sbin/nologin",@x,@y);
+-------+
| z |
+------+
| 38 |
+-------+
存储过程中的条件判断
示例:
mysql> delimiter //
mysql> create procedure p(in x int(1))
-> begin
-> if x is null then
-> set @x = 1;
-> select * from user.usertab where id=x;
-> end if;
-> if x <= 10 then
-> select * from user.usertab where id<=x;
-> end if;
-> end
-> //
mysql> delimiter ;
调用存储过程
mysql> call p(10);
存储过程中的循环结构
- 循环结构控制语句
- 控制循环结构的执行行
– LEAVE 标签名 //结束循环
– ITERATE 标签名 //放弃本次循环,执行下一次循环
输出数字一到5
mysql> delimiter //
mysql> create procedure p()
begin
declare i int;
set i=1;
while i <=5 do
select i;
set i=i+1;
end while;
end
mysql> delimiter ;
//
死循环打印数字
delimiter //
create procedure say6()
begin
declare i int;
set i=1;
loop
select i;
set i=i+1;
end loop;
end
//
delimiter ;
call p;
call say; #死循环需要手动结束Ctrl+C
匹配即停止
delimiter //
create procedure p11()
begin
declare i int;
set i=1;
biaoqian1:loop
select i;
set i=i+1;
if i = 4 then
LEAVE biaoqian1;
end if;
end loop;
end
//
call p11 #当执行到i=4时候终止
当LEAVE遇见ITERATE
delimiter //
create procedure p24()
begin
declare i int;
set i=0;
biaoqian2:loop
set i=i+1;
if i = 5 then
ITERATE biaoqian2;
end if;
if i = 11 then
LEAVE biaoqian2;
end if;
select i;
end loop;
end
//
delimiter ;
repeat相当于shell中的for循环
delimiter //
create procedure p12()
begin
declare i int;
set i=1;
repeat
select i;
set i=i+1;
until i=5
end repeat;
end
//
delimiter ;
触发器
触发器是一种特殊的存储过程,它在插入、删除或修改特定的表中的数据时出发执行,它比数据库本身标准
的功能有更精细和更复杂的数据库控制能力。
触发器作用
- 1.安全性可以基于数据库的值使用户具有操作数据库的某种权利
- 2.审计
- 3.实现复杂的数据完整性规则
- 4.实现复杂的非标准的数据库相关完整性规则
- 5.实时同步地复制表中的数据
- 6.自动计算数据值
创建触发器
语法:
create trigger 触发器的名称 触发的时机 触发的动作 on 表名 for each row 触发器状态
参数说明:
触发器名称:自己定义
触发的时间:before/after 在执行动作之前还是之后
触发的动作:指的激发触发器程序的语句类型<insert,update,delete>
each row:操作第一行我都监控着
触发器创建语法四要素:
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
例:当category表中,删除一个bTypeid=3的图书分类时,books表中也要删除对应分类的图书信息(类似级联删除)
事务
什么是事务?
逻辑处理单元,要不全成功要不全失败
MYSQL中只有INNODB和BDB类型的数据表才讷讷个支持事务处理!其他类型不支持!
开启事务
SET AUTOCOMMIT = {0 | 1} 设置事务是否自动提交,默认是自动提交的。
0:禁止自动提交
1:开启自动提交。
MySQL事务处理的方法
1、 用BEGIN,ROLLBACK,COMMIT来实现
START TRANSACTION | BEGIN [WORK] 开启事务
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]提交当前事务,执行永久操作。
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO]RELEASE] 回滚当前事务到开始点,取消上一次开始点后的所有操作。
SAVEPOINT 名称 折返点
2、 直接用set来改变mysql的自动提交模式
MYSQL默认是自动提交的,也就是你提交一个QUERY,它就直接执行!
视图
什么是视图(View)
- 是一种虚拟存在的表
-
内容与真实的表相似,包含一系列带有名称的列和行数据。
-
视图并不在数据库中以存储的数据的形式存在。
-
行和列的数据来自定义视图时查询所引用的基本表,并且在具体引用视图时动态生成。
-
更新视图的数据,就是更新基表的数据
-
更新基表数据,视图的数据也会跟着改变
视图优点
1.视图能够简化用户的操作
视图机制用户可以将注意力集中在所关心的数据上。如果这些数据不是直接来自基本表,则可以通过定义视图,使数据库看起来结构简单、清晰,并且可以简化用户的数据查询操作
2.视图是用户能以不同的角度看待同样的数据。
对于固定的一些基本表,我们可以给不同的用户建立不同的视图,这样不同的用户就可以看到自己需要的信息了。
3.视图对重构数据库提供了一定程度的逻辑性。
比如原来的A表被分割成了B表和C表,我们仍然可以在B表和C表的基础上构建一个视图A,而使用该数据表的程序可以不变。
4.视图能够对机密数据提供安全保护
比如说,每门课的成绩都构成了一个基本表,但是对于每个同学只可以查看自己这门课的成绩,因此可以为每个同学建立一个视图,隐藏其他同学的数据,只显示该同学自己的
5.适当的利用视图可以更加清晰的表达查询数据。
有时用现有的视图进行查询可以极大的减小查询语句的复杂程度。
使用视图的限制
- 不能在视图上创建索引
-
在视图的FROM子句中不能使用子查询
-
以下情形中的视图是不可更新的
-
包含以下关键字的SQL语句:聚合函数(SUM、MIN、MAX、COUNT等)、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL
-
常量视图
-
JOIN
-
FROM一个不能更新的视图
-
WHERE子句的子查询引用了FROM子句中的表
-
使用了临时表,视图是不可更新
我们在怎样的场景使用它,为什么使用视图?
- 如果某个查询结果出现的非常频繁,也就是,要经常拿这个查询结果来做子查询这种
创建视图
- create view 视图名称(你要创建名称) as select 语句
使用方法
解释:视图相当于创建一个函数,在查询的时候调用函数,简便的查询。也相当于linux中自己创建的别名。
例子:
create view bc as select b.name,b.price,c.bTypeName from books as b left join caregory as c on b.bTypeId=c.bTypeId;左连接查询
修改:
atler view bc as select b.Name ,b,publishing ,c.bTpyeName from books as b left join caregory asc on b.bTypeId=c.bTypeId;
删除视图:
drop view bc;
查看当前库下所有表的状态信息
show table status;
show table status where comment="view"\G; #过滤有视图的表
查看创建的视图结构
show create view v2\G;
对视图的增删改查
-> 查询记录
Select 字段名列表 from 视图名 where 条件;
-> 插入记录
Insert into 视图名(字段名列表) values(字段值列表);
-> 更新记录
Update 视图名 set 字段名=值 where 条件;
-> 删除记录
Delete from 视图名 where 条件;
创建视图并且重命名
mysql> create view as select (create or replace view as select [覆盖重名的视图])
-> a.username as aname,
-> b.username as bname,
-> a.uid as auid,
-> b.uid as buid
-> from user2 a left join info b on a.uid=b.uid;
select * from v1;
视图的ALGORITHM
WITH CHECK OPTION
当视图是根据另一个视图定义时,对视图更新
LOCAL和CASCADED关键字决定了检查的范围。
– LOCAL (默认值) 仅检查当前视图的限制。
– CASCADED 同时要满足基表的限制。
示例:
create table a select * from usertab where uid<10;
create view va1 as select * from a where uid<10 with check option; #创建视图,通过视图进行的修改,必须也能通过该视图看到修改后的结果
create view va2 as select * from va1 where uid<=5 with local check option; #创建视图,满足本地条件才能够添加
create view va3 as select * from va1 where uid>=5 with cascaded check option; #创建视图,并且条件要满足自己的限制也要慢著va1视图的限制
Hey There. I found your blog using msn. This is a really well written article. I’ll make sure to bookmark it and return to read more of your useful information. Thanks for the post. I will certainly return.
I really enjoy looking at on this internet site, it has fantastic blog posts. “Don’t put too fine a point to your wit for fear it should get blunted.” by Miguel de Cervantes.