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

存储过程(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);

存储过程中的循环结构

  1. 循环结构控制语句
  2. 控制循环结构的执行行

– 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子句中不能使用子查询

  • 以下情形中的视图是不可更新的

  1. 包含以下关键字的SQL语句:聚合函数(SUM、MIN、MAX、COUNT等)、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL

  2. 常量视图

  3. JOIN

  4. FROM一个不能更新的视图

  5. WHERE子句的子查询引用了FROM子句中的表

  6. 使用了临时表,视图是不可更新

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

  • 如果某个查询结果出现的非常频繁,也就是,要经常拿这个查询结果来做子查询这种

创建视图

  • 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个想法

发表评论