MySQL视图,存储过程以及触发器

楚钧艾克

已于 2024-05-14 08:53:30 修改

阅读量861

点赞数 13

分类专栏：数据库 # MySQL 文章标签： mysql 数据库后端

于 2023-12-16 09:59:58 首次发布

本文链接：https://blog.csdn.net/c0210g/article/details/135029110

版权

数据库同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

MySQL

4 篇文章 0 订阅

订阅专栏

视图(view)

1.视图概述

视图是一种虚拟存在的表.视图中的数据并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的.

视图:站在不同的角度去看待同一份数据.

视图只保存了查询的SQL逻辑,不保存查询结果.所以创建视图时,主要是创建SQL查询语句

2.视图的增删改查

创建:

create [OR REPLACE] view emp_view as select * from emp [WITH[CASCADED | LOCAL]] CHECK OPTION]; #创建视图时OR REPLACE 可以省略,可用于替换视图

删除:

drop view dept2_view;

只有DQL语句语句才能以view的形式创建.即as后面的语句必须是DQL语句.

查询:

#查询创建视图语句
SHOW CREATE VIEW 视图名称;
#查看视图数据
SELECT * FROM 视图名称...

修改:

#OR REPLACE 替换视图
create OR REPLACE view emp_view as select * from emp [WITH[CASCADED | LOCAL]] CHECK OPTION];
#2
ALTER VIEW 视图名称[(列表名称)] AS SELECT语句 [WITH[CASCADED | LOCAL]] CHECK OPTION]

删除:

DROP VIEW [IF EXISTS] 视图名称 [,视图名称]

WITH CASCADED CHECK OPTION:创建视图,修改视图时增加检查选项,以后对视图查询出来的数据进行增删改查时,需要符合创建视图时的条件.

3.视图的检查选项

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

CASCADED 检查所有依赖的视图

LOCAL 会先判断依赖的视图是否添加了检查选项 , 如果添加了再去检查数据是否符合,否则不检查.

4.视图用法

可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被改变.(视图的特点)

视图对应的语句即as后面的语句只能是DQL语句.

但是视图对象创建完成之后,可以对视图进行增删改查等操作.

5.视图的更新及作用

要使视图可更新,视图中的行与基础表中的行必须存在一一对应的关系,即视图中的一行的数据必须对应基础表中的一行数据.如果视图包含一下任何一项,则不可更新:

聚合函数
DISINCT
Group by
UNION或者UNION ALL

作用:

1.视图可以简化用户对数据的理解,简化操作.可以将经常查询的数据定义为视图,用户不必为以后的操作每次指定全部条件.

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

3.视图可以屏蔽表中结构变化的影响.

存储过程

1.概述

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

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

特点:

1.封装,复用

2.可以接收参数,也可以返回数据

3.减少网络交互,效率提升

2. 基本语法

创建:

CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
    --SQL语句
END;

如果是在终端命令行中创建存储过程,需要通过 delimiter 指定SQL语句的结束符

delimiter $$ ; #指定sql语句的结束符号为$$

调用:

CALL 存储过程名称([参数]);

查看:

#查询指定数据库的存储过程以及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINE_SCHEMA='XXX';

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

删除:

DROP PROCEDURE IF EXISTS 存储过程名称;

3. 变量

3.1 系统变量

系统变量是MySQL服务器提供的,不是用户定义的,属于服务器层面.分为全局变量(GLOBAL) , 会话变量(SESSION).

查看系统变量:

SHOW [SESSION | GLOBAL] WARIABLES; #查看所有的系统变量
SHOW [SESSION | GLOBAL] VARIABLES LIKE '......' ; #可以通过LIKE模糊匹配的方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; --查看指定变量的值.

3.2 设置系统变量

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

如果没有指定 SESSION|GLOBAL , 默认是SESSION , 会话变量

MySQL 服务重新启动之后 , 所设置的全局参数会失效,要想不失效,可以在/etc/my.cnf 中配置.

3.2 用户变量

用户定义变量是用户根据需要自己定义的变量,用户变量不用提前声明,在用的时候直接用"@变量名" 使用就可以. 其作用域为当前链接.

赋值:

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

SELECT @var_name := value  [,@var_name := value]...;#和set 用法相似
#将SELECT查询出的结果赋值给 var_name
SELECT 字段名 INTO @var_name FROM 表名;
# select count(*) into @mycount from tb_user;

使用:

SELECT @var_name;

获取不到值为NULL

3.3 局部变量

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

声明:

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

变量类型就是数据库字段类型

赋值:

SET 变量名 = 值;
SET 变量名 := 值;
SELECT 字段名 INTO 变量名 FROM 表名...;

create procedure p1()
begin
    declare stu_student int default 0;
    select count(*) into stu_count from student;
    select stu_count;
end;

4.函数

4.1 IF

语法:

IF 条件 THEN
...
ELSEIF 条件2 THEN
...
ELSE
...
END IF; (END IF 不能省略)

4.2存储过程的参数

类型:

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

用法:

CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型])
BEGIN
 --SQL
END;

实例:

#IN 和 OUT 参数的使用
create procedure p4(in score int , out result varchar(10))
begin
 if score >= 85 then
    set result := '优秀'
 end if
end;
call p4(68 , @result)
select @result;



#INOUT 参数的使用
create procedure p5(inout score double)
begin
    set score := score * 0.5;
end;

#要先定义score变量
set @score = 78
call p5(@score)

4.3 Case

语法:

CASE case_value
    WHEN value1 THEN statement_list1
    [WHEN value2 THEN statement_list2]
    [ELSE statement_list]
END CASE;   

#二
CASE 
    WHEN search_condition1 THEN statement_list1
    [WHEN search_condition1 THEN statement_list2]...
    [ELSE statement_list]
END CASE;

4.4 while

while循环是有条件的循环控制语句.

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

WHILE 条件 DO
    SQL逻辑...
END WHILE;

4.5 repeat 循环

repeat循环先执行一次逻辑 , 然后判断条件是否满足,如果满足,则退出.如果不满足,则继续下一次循环.

#先执行一次逻辑 , 然后判断条件是否满足,如果满足,则退出.如果不满足,则继续下一次循环
REPEAT
    SQL逻辑;
    UNTIL 条件
END REPEAT;

4.6 loop 循环

loop 实现简单的循环 , 如果不在SQL逻辑中增加退出循环的条件,可以用来实现简单的死循环.LOOP可以配合一下两个语句使用:

LEAVE : 配合循环使用 , 退出循环
ITERATE: 必须用在循环过程中,作用是跳过当前循环剩下的语句,直接进入下一次循环.

#begin_lable 只是自定义标记
[begin_label:]LOOP
 SQL逻辑..
END LOOP [end_lable]; 


create procedure p_loop(in n int , out result int)
begin
 sum:loop
    if n <= 0 then
        leave sum;#退出sum循环
    end if;
    set result := result + n;
    set n := n -1
 end loop sum;
end;

call p_loop(63 , @result);
select @result;

4.7 游标

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

语法:

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

#打开游标
OPEN 游标名称;

#获取游标记录
FETCH 游标名称 INTO 变量[,变量];

#关闭游标
CLOSE 游标名称;

使用:

create procedure p_cursor(in uage int)
begin
    #需要先声明普通变量 , 在去声明游标
    declare name varchar(25)
    declare profession varchar(100)
    #声明游标,存储查询结果集
    declare u_cursor cursor for select name , profession from tb_user where age <= uage;
    #声明条件处理程序 当sql状态码为02000 即游标中没有数据退出u_cursor
    declare exit handler for SQLSTATE '02000' close u_cursor;
    create table if not exists tb_user_profession(
        id int primary key auto_increment,
        name varchar(25),
        profession varchar(100)
    );
    
    #打开游标
    open u_cursor
    
    #循环遍历游标记录
    while true do
        #获取游标记录,将其赋值给name 和 profession
        fetch u_cursor into name , profession;
        #插入数据
        insert into tb_user_profession values(null , name , profession);
    end while;
    #关闭游标
    close u_cursor;
END;

4.8条件处理程序

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

语法:

DECLARE handler_action HANDLER FOR condition_value [,condition_value]... SQL语句;

handler_action
    CONTINUE:继续执行当前程序
    EXIT:终止执行当前程序
condition_value
    SQLSTATE sqlstate_value:状态码,如 02000
    SQLWARNING:所有以01开头的SQLSTATE代码的简写
    NOT FOUND:所有以02开头的SQLSTATE代码的简写
    SQLEXCEPTION:所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

存储函数

存储函数时有返回值的存储过程,存储函数的参数只能是IN类型的.

create function 存储函数名称([参数列表])
#指定返回值的类型 type   characteristic返回值的特性
RETURNS type [characteristic...]
BEGIN
    --SQL语句
    RETURN...;
END;


# 存储函数名称([参数]); 调用方法


#####
characteristic说明:
    DETERMINISTIC:相同的输入参数总是产生相同的结果
    NO SQL:不包含SQL语句
    READS SQL DATA:包含读取数据的语句,但是不包含写入数据的语句.

触发器

1.介绍

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

使用别名OLD 和 NEW 来引用触发器中发生变化的记录内容,这与其它的数据库是相似的.现在的触发器仅支持行级触发(每影响一行数据就触发一次),不支持语句级触发.

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

2. 使用

语法:

#创建触发器
CREATE TRIGGER 触发器名称
BEFORE/AFTER INSERT/UPDATE/DELETE
ON table_name FOR EACH ROW -- 行级触发器
BEGIN
 tigger_stmt; --触发器内的具体逻辑
END;

#查看
SHOW TRIGGERS;

#删除
DROP TRIGGER [schema_name.]触发器名称; --如果没有指定 schema_name , 默认为当前数据库.


#示例:
create trigger tb_user_insert_trigger
    after insert on for each row
begin
    #NEW 表示的就是新插入的数据行
    insert into user_logs(id , operation , operate_time , operater_id , operate_params) Values
    (null , 'insert' , now() , NEW.id , .....)
end;