存储过程(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视图的限制

《Mysql-存储过程-触发器-事务-视图》有3个想法

Mireille Lipp说道：

2018年12月27日上午8:24

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.

登录以回复
Delisa Vagott说道：

2018年12月27日上午10:58

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.

登录以回复

发表评论取消回复

要发表评论，您必须先登录。

董的博客

沉淀思想，跨步前行，这是生活更是理想！

Mysql-存储过程-触发器-事务-视图

存储过程(Stored Procedure)

mysql的存储优点

mysql的存储过程

为什么很少使用存储过程？

定义存储的过程

调用存储过程

存储过程的参数类型

存储过程的传出参数

存储过程的传入与传出

存储过程变量的使用

Mysql中的变量

存储过程中的运算

存储过程中的条件判断

存储过程中的循环结构

触发器

触发器作用

创建触发器

事务

什么是事务？

MySQL事务处理的方法

视图

什么是视图(View)

视图优点

使用视图的限制

我们在怎样的场景使用它，为什么使用视图？

创建视图

WITH CHECK OPTION

《Mysql-存储过程-触发器-事务-视图》有3个想法

发表评论取消回复

存储过程(Stored Procedure)

mysql的存储优点

mysql的存储过程

为什么很少使用存储过程？

定义存储的过程

调用存储过程

存储过程的参数类型

存储过程的传出参数

存储过程的传入与传出

存储过程变量的使用

Mysql中的变量

存储过程中的运算

存储过程中的条件判断

存储过程中的循环结构

触发器

触发器作用

创建触发器

事务

什么是事务？

MySQL事务处理的方法

视图

什么是视图(View)

视图优点

使用视图的限制

我们在怎样的场景使用它，为什么使用视图？

创建视图

WITH CHECK OPTION

《Mysql-存储过程-触发器-事务-视图》有3个想法

发表评论 取消回复

发表评论取消回复