【MySQL进阶篇】存储对象：视图、存储过程及触发器

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库SQL语言层面的代码的封装与重用。用户自定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。

人间无解

2430人浏览 · 2024-07-22 18:30:52

人间无解 · 2024-07-22 18:30:52 发布

一、视图

1、介绍

视图（view）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来定义视图的查询中使用的表（基表），并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

2、操作语法

2.1、创建

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT [WITH[CASCADED | LOCAL] CHECK OPTION]

2.2、查询

查看创建视图语句：SHOW CREATE VIEW 视图名称;

查看视图数据：SELECT * FROM 视图名称.....;

2.3、修改

方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT [WITH[CASCADED | LOCAL] CHECK OPTION]

方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT [WITH[CASCADED | LOCAL] CHECK OPTION]

2.4、删除

DROP VIEW [IF EXISTS] 视图名称;

#创建视图
create or replace view stu_v as select id,name from student where id<=10;
#查询视图
show create view stu_v;
select * from stu_v;
select * from stu_v where id<3;
#修改视图
create or replace view stu_v as select id,name,no from student where id<=10;
alter view stu_v as select id,name from student where id<=10;
#删除视图
drop view if exists stu_v;

3、检查选项

create or replace view stu_v as select id,name from student where id<=10;
select * from stu_v;
insert into stu_v values(5,'tom');
#视图并不存储数据，数据在基表当中存储，也就意味着我们插入的数据实际上是插入到student表当中了
insert into stu_v values(15,'tom');
#基表当中会包含这条数据，但是在查询视图时并不包含这条数据，这是因为在创建视图时指定了id<=10，为了避免这种情况的发生可以加上检查选项

1.1、试图当中的检查选项

当使用 WITH CHECK OPTION 子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查以来视图中的规则一保持一致性。为了确定检查的范围，MySQL提供了两个选项：CASCADED 和 LOCAL,默认值为CASCADED。

CASCADED:

#CASCADE :
create or replace view stu_v1 as select id,name from student where id<=20;
create or replace view stu_v2 as select id,name from stu_v1 where id>=10 with cascaded check option ;
insert into stu_v2 values(5,'tom');
insert into stu_v2 values(25,'tom');
#都不能插入成功，会检查stu_v2所依赖的底层所有的视图
create or replace view stu_v3 as select id,name from stu_v2 where id<=15;
insert into stu_v3 values(11,'tom');
insert into stu_v3 values(17,'tom');#能执行成功，会在基表中出现该数据，stu_v3视图的条件不需要检查，只需要检查另外两个视图
insert into stu_v3 values(28,'tom');

LOCAL:

#LOCAL :
create or replace view stu_v_1 as select id,name from student where id<=20;
create or replace view stu_v_2 as select id,name from stu_v_1 where id>=10 with local check option ;
insert into stu_v_2 values(5,'tom');
#需要检查上一个视图的条件但上一个视图没有with check option选项，并且也不满足当前视图的条件
insert into stu_v_2 values(25,'tom');
#满足当前视图的条件，虽然不满足上一个视图的条件，但上一个视图没有with check option选项，因此可以插入
create or replace view stu_v_3 as select id,name from stu_v_2 where id<=15;
insert into stu_v_3 values(11,'tom');
insert into stu_v_3 values(17,'tom');
insert into stu_v_3 values(28,'tom');
#都可以插入进来

4、更新及作用

4.1、视图的更新

要使视图更新，视图中的行为与基础表中的行之间必须存在一对一的关系。如果试图中包含一下任何一项，则该视图不可更新：

1、聚合函数或窗口函数（SUM()、MAX()、MIN()、COUNT()等）

2、DISTINCT

3、GROUP BY

4、HAVING

5、UNION 与 UNION ALL

4.2、视图的作用

1、简单：试图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2、安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。

3、数据独立：视图可以帮助用户屏蔽真实表结构带来的影响。

二、存储过程

1、介绍

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

存储过程思想上很简单，就是数据库SQL语言层面的代码的封装与重用。

2、特点

封装、复用；可以接收参数，也可以返回数据；减少网络交互，效率提升。

3、操作语法

3.1、创建

CREATE PROCEDURE 存储过程名称[(参数列表)]

BEGIN

--SQL语句

END;

3.2、调用

CALL 名称 [(参数)];

3.3、查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUINE_SCHEMA='xxx';#查询指定数据库的存储过程及状态信息

SHOW CREATE PROCEDURE 存储过程名称;#查询某个存储过程的定义

3.4、删除

DROP PROCEDURE [IF EXISTS] 存储过程名称;

#创建
create procedure p1()
begin
    select count(*) from student;
end;
#调用
call p1();
#查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='test';
show create procedure p1;
#删除
drop procedure if exists p1;

注意：在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter 指定SQL的结束符

 delimiter $$
mysql> create procedure p1()
    -> begin
    ->     select count(*) from student;
    -> end$$
Query OK, 0 rows affected (0.01 sec)

4、变量

4.1、系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面，分为全局变量（GLOBAL）、会话变量（SESSION）。

4.1.1、查看系统变量

SHOW [SESSION | GLOBAL] VARIABLES;#查看所有系统变量

SHOW [SESSION | GLOBAL] VARIABLES LIKE'....';#可以通过LIKE模糊匹配的方式查找变量

SELECT @@[SESSION | GLOBAL] 系统变量名;#查看指定变量的值

4.1.2、设置系统变量

SET [SESSION | GLOBAL] 系统变量名=值;

SET @@[SESSION |GLOBAL] 系统变量名=值;

#查询系统变量
show session variables;
show session variables like 'auto%';#查看与事务自动提交相关的变量
select @@session.autocommit;
#设置系统变量
set session autocommit=0;
set @@session.autocommit=1;

注意：

1、如果没有指定SESSION/GLOBAL默认是SESSION会话变量；

2、MySQL服务器重启之后，所设置的全局参数会失效。

4.2、自定义变量

用户自定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用“@变量名”使用就可以。其作用域为当前连接。

4.2.1、用户变量赋值

SET @var_name=expr [,@var_name=expr]...;

SET @var_name:=expr [,@var_name:=expr]...;

推荐使用:=因为在MySQL中判断相等与赋值都是用的=，为了避免混淆，用:=。

SELECT @var_name:=expr [,@var_name:=expr]...;

SELECT 字段名 INTO @var_name FROM 表名;

4.2.2、用户变量使用

SELECT @var_name;

#用户变量赋值
set @myname='test';
set @myage:='10',@mygender:='男';
select @mycolor:='black';
select count(*) into @mycount from tb_user
#使用自定义变量
select @myname,@myage,@mygender;

注意：用户定义的变量无需对其进行声明或优化，只不过获取到的值为NULL。

4.3、局部变量

局部变量是根据需要定义在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BIGINT...END块。

4.3.1、局部变量的声明

DECLARE 变量名变量类型 [FEFAULT ...];

变量类型就是数据库字段类型：INT、BIGINT、VARCHAR、DATE、TIME等。

4.3.2、局部变量赋值

SET 变量名=值;

SET 变量名：=值;

SELECT 字段名 INTO 变量名 FROM 表名...;

#局部变量的声明和赋值
create procedure p2()
begin
    declare stu_count int default 0;
    set stu_count:=100;
    #select count(*) into stu_count from student;
    select stu_count;
end;
call p2();

5、if判断

5.1、语法结构

IF 条件1 THEN

...

ELSE 条件2 THEN

...

ELSE

...

END IF;

#需求：更具定义的分数score变量，判断对应等级
#1、score>=85，等级为优秀
#2、score>=60并且score<85，等级为及格
#3、score<60，等级不及格
create procedure p1()
begin
    declare score int default 58;
    declare result varchar(10);
    if score>=85 then
       set result:='优秀';
    elseif score>=60 and score<85 then
       set result:='及格';
    else
        set result:='不及格';
    end if;
    select result;
end;
call p1();

6、存储过程的参数

类型	含义	备注
IN	该类参数作为输入，也就是调用时需要传入值	默认
OUT	该类参数作为输出，也就是该参数可以作为返回值
INOUT	既可以作为输入参数，也可以作为输出参数

6.1、语法

CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数值参数类型])

BEGIN

--SQL语句

END;

#存储过程中的参数
#1、需求：根据传入参数的分数score，判断对应等级，并返回
#score>=85，等级为优秀
#score>=60并且score<85，等级为及格
#score<60，等级不及格
create procedure p2(in score int,out result varchar(10))
begin
    if score>=85 then
       set result:='优秀';
    elseif score>=60 and score<85 then
       set result:='及格';
    else
        set result:='不及格';
    end if;
end;
call p2(68,@result);
select @result;
#2、将传入的200分制的分数进行换算，换算成百分制，然后返回
create procedure p3(inout score double)
begin
    set score :=score*0.5;
end;
set @score:=78;
call p3(@score);
select @score;

7、case

7.1、语法

语法一

CASE case_value

WHEN then_value1 THEN statement_list1

[WHEN then_value2 THEN statement_list2]

[ELSE statement_list]

END CASE;

语法二

CASE

WHEN search_condition1 THEN statement_list1

[WHEN search_condition2 THEN statement_list2]

#需求：根据传入的月份，判断月份所属的季度（采用case结构）
#1、1-3月份，第一季度
#2、4-6月份，第二季度
#3、7-9月份，第三季度
#4、10-12月份，第四季度
create procedure p4(in month int)
begin
    declare result varchar(10);
    case
        when month>=1 and month<=3 then
         set result:='第一季度';
        when month>=4 and month<=6 then
         set result:='第二季度';
        when month>=7 and month<=9 then
         set result:='第三季度';
        when month>=10 and month<=12 then
         set result:='第四季度';
        else
         set result:='非法参数';
    end case;
    select concat('您输入的月份为：',month,'所属季度：',result);
end;
call p4(16);

8、while循环

while循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。

语法结构：

#先判定条件，如果条件为true，则执行逻辑，否则不执行

WHILE 条件 DO

SQL语句...

END WHILE;

#需求：计算从1累加到n的值，n为传入的参数值
#A.定义局部变量，用来记录累加之后的值
#B.每循环一次n-1，知道满足n<0,则不再循环
create procedure p5(in n int)
begin
    declare sum int default 0;
    while n>0 do
        set sum:=sum+n;
        set n:=n-1;
        end while;
    select sum;
end;
call p5(10);

9、repeat循环

repeat是有条件的控制语句，当满足条件的时候退出循环。

语法结构：

#先执行一次逻辑，然后判断逻辑是否满足，如果满足则退出，反之则进行下一次循环

REPEAT

SQL逻辑...

UNTIL 条件

END REPEAT;

create procedure p6(in n int)
begin
    declare sum int default 0;
    repeat
        set sum :=sum+n;
        set n :=n-1;
    until n<=0
        end repeat;
    select sum;
end;
call p6(10);

10、loop循环

loop实现简单的循环，如果不在SQL逻辑中增加退出循环的条件，可以用其实现简单的死循环。loop可以配合以下两个语句使用：

· LEAVE:退出循环。

· ITERATE:必循用在循环中，作用是跳过当前语句剩下的语句，直接进入下一次循环。

[begin_label:] LOOP

SQL 逻辑...

END LOOP [end_lable];

LEAVE lable;#跳出循环

ITERATE lable;#进入下一次循环

#需求：计算从1累加到n的值，n为传入的参数值
#A.定义局部变量，用来记录累加之后的值
#B.每循环一次n-1，知道满足n<0,则不再循环
create procedure p7(in n int)
begin
    declare sum int default 0;
    total:loop
        if n<=0 then
            leave total;
        end if;
        set sum :=sum+n;
        set n :=n-1;
    end loop total;
    select sum;
end;
call p7(10);
#需求：计算从1到n之间偶数的累加值，n为传入的参数值
create procedure p8(in n int)
begin
    declare sum int default 0;
    total:loop
        if n<=0 then
            leave total;
        elseif n%2=1 then
            set n:=n-1;
            iterate total;
        end if;
        set sum :=sum+n;
        set n :=n-2;
    end loop total;
    select sum;
end;
call p8(10);

11、游标(cursor)

create procedure p9()
begin
    declare stu_count int default 0;
    select * into stu_count from student;
    select stu_count;
end;
call p9();
#会报这样一条错误：The used SELECT statements have a different number of columns

这个时候就需要游标了。

游标是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理，游标的使用包括游标的声明、OPEN、FETCH和CLOSE。

11.1操作语法

声明游标：

DECLARE 游标名称 CURSOR FOR 查询语句;

打开游标：

OPEN 游标名称;

获取游标记录：

FETCH 游标名称 INTO 变量[,变量];

关闭游标：

CLOSE 游标名称;

11.2、条件处理程序

条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法：

DECLARE handler_action HANDLER condition_value [,condition_vakue]...statement;

handler_action

CONTINUE:继续执行当前程序

EXIT:终止执行当前程序

condition_value

SQLSTATE sqlstate_value:状态码，如02000

SQLWARNING:所有以01开头的SQLSTATE代码的简写

NOT FOUND: 所有以01开头的SQLSTATE代码的简写

SQLEXCEPTION:所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

#需求：根据传入的参数uage，用来查询用户表tb_user中，所有用户年龄小于等于uage的用户姓名（name）和专业（profession）
#并将用户的姓名和专业插入到所创建的一张新表（id，name，profession）中
#A.声明游标，存储查询结果集
#B.创建表结构
#C.开启游标
#D.获取游标中的记录
#E.插入数据到新表中
#F.关闭游标
create procedure p9(in uage int)
begin
    declare u_name varchar(10);
    declare u_profession varchar(20);
    declare u_cursor cursor for select name,profession from tb_user where age<=uage;
    declare exit handler for SQLSTATE '02000' close u_cursor;
    create table if not exists tb_user_pro(
        id int primary key auto_increment,
        name varchar(10) ,
        profession varchar(20)
    );
    open u_cursor;
    while true do
        fetch u_cursor into u_name,u_profession;
        insert into tb_user_pro values(null,u_name,u_profession);
        end while;
    close u_cursor;
end;

三、存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的。具体语法：

CREATE FUNCTION 存储过程名称[(参数列表)]

RETURNS type [characteristic ...]

BEGIN

--SQL语句

RETURN...;

END;

characterstic说明：

DETERMINISTIC：相同的输入参数总是产生相同的结果

NO SQL：不包含SQL语句

READS SQL DATA：包含读取数据的语句，但不包含写入数据的语句

#从1到n的累加
create function fun(n int)
returns int deterministic
begin
    declare sum int default 0;
    while n>0 do
        set sum:=sum+n;
        set n:=n-1;
        end while;
    return sum;
end;
select fun(100);

四、触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器这种特性可以协作应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，者与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

触发器类型	NEW和OLD
INSERT型触发器	NEW表示将要或已经新增的数据
UPDATE型触发器	OLD表示修改之前的数据，NEW表示将要修改或修改后的数据
DELETE型触发器	OLD表示将要货已经删除的数据

创建：

CREATE TRIGGER trigger_name

BEDORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW

BEGIN

trigger_stmt;

END;

查看：

SHOW TRIGGERS;

删除：

DROP TRIGGER [schema_name.] trigger_name;#如果没有指定schema_name.默认当前数据库

#需求：通过触发器记录user表的数据变更日志（user_logs），包含增加，修改、删除
create table user_log(
    id int(11) not null auto_increment,
    operation varchar(20) not null comment '操作类型，insert/update/delete',
    operate_time datetime not null comment '操作时间',
    operate_id int(11) not null comment '操作的ID',
    operate_params varchar(500) comment '操作参数',
    primary key (`id`)
)engine=innodb default charset =utf8;
#插入数据的触发器
create trigger insert_trigger
    after insert on tb_user for each row
begin
       insert into user_log(id, operation, operate_time, operate_id, operate_params) values
        (null,'insert',now(),new.id,concat('插入的数据内容为:id=',new.id,'name=',new.name,'phone=',new.phone,'profession=',new.profession));
end;
#插入数据到tb_user表，验证
insert into tb_user values(5,'二秃子',66,'1','12345678910','通信工程');
#修改数据的触发器
create trigger update_trigger
    after update on tb_user for each row
begin
       insert into user_log(id, operation, operate_time, operate_id, operate_params) values
        (null,'update',now(),new.id,concat('更新之前的数据:id=',old.id,'name=',old.name,'phone=',old.phone,'profession=',old.profession,
                                    '更新之后的数据:id=',new.id,'name=',new.name,'phone=',new.phone,'profession=',new.profession));
end;
update tb_user set age =56 where age=55;
#删除数据的触发器
create trigger delete_trigger
    after delete on tb_user for each row
begin
       insert into user_log(id, operation, operate_time, operate_id, operate_params) values
        (null,'delete',now(),old.id,concat('删除之前的数据:id=',old.id,'name=',old.name,'phone=',old.phone,'profession=',old.profession));
end;
delete from tb_user where id=5;
show triggers;