MySQL数据库进阶第四篇(视图/存储过程/触发器)

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL数据库进阶第四篇(视图/存储过程/触发器)

在这篇博文中,将深入探讨了视图的创建、检查选项、更新及其作用,并详细介绍了存储过程与触发器的概念、创建、调用、查看以及删除的方法。文章通过丰富的示例和图片清晰地展示了语法和实践操作,对于数据库管理系统的学习者和专业人士提供了实用的指导。尤其是对于MySQL中视图与存储过程的增删改查(CRUD)操作、条件分支(IF/CASE)、循环控制(WHILE/REPEAT/LOOP)及游标使用的详细解说,都有助于读者更好地理解和运用这些数据库对象,以便于更加高效地进行数据操作和管理。此外,文章还涉及了异常处理和存储函数的使用技巧,为数据库编程提供了有力的支持。通过阅读这篇文章,可以帮助用户简化操作,保证数据安全,应对真实结构变化,以及优化业务逻辑实现。

一、视图简单介绍与基础语法

操作语法:

视图的创建、查看、修改、删除

# 创建
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
# 查询
SHOW CREATE VIEW 视图名称;      # 查看创建视图语句
SELECT * FROM 视图名称 ...... ; # 查看视图数据
# 修改 方式一
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH
[ CASCADED | LOCAL ] CHECK OPTION ]
# 修改 方式二
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED |
LOCAL ] CHECK OPTION ]
# 删除
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

二、视图的检查选项

使用子句with check option创建视图时,MYSQL会通过视图检查正在更改的每行(插入,更新,删除等操作)是否符合视图的定义。MySQL允许基于一个视图创建另一个视图,并检查依赖视图中的规则以保持一致性。为了确定检查的范围,提供了两个选项

WITH CASCADED CHECK OPTION 是指在更新视图时,会检查视图中所有相关的视图和基表的约束。如果更新操作违反了任何一个相关表的约束条件,更新将被拒绝。

WITH LOCAL CHECK OPTION 是指在更新视图时,仅检查当前视图的约束条件。如果更新操作违反了当前视图的约束条件,更新将被拒绝。不会检查其他相关表的约束条件。

三、视图的更新

要使视图可更新,视图中的行与基础表中的行之间必须存在一对一的关系。

如果视图包含以下任何一 项,则该视图不可更新:

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

DISTINCT C. GROUP BY D. HAVING E. UNION 或者 UNION ALL

举例:create view v1 as select count(*) from student;

insert int v1 values(10);

报错,提示当前视图不能插入数据,因为count(*) 的结果 没有 和student一样是一一对应的关系

四、视图的作用

简单:把复杂查询条件定义为视图,简化用户操作

安全:数据库可以通过视图 保证了某些数据的安全性,通过视图 用户只能查询和修改它们所能见到的数据

数据独立:帮助用户屏蔽真实结构变化带来的影响

五、存储过程的概念与特点

概念:是一段预先编译并保存在数据库中的可重复使用的代码块。存储过程由一系列的 SQL 语句和控制流语句组成,可以接受参数,并且可以返回结果。存储过程通常用于执行复杂的数据库操作,实现业务逻辑和数据处理等任务。

六、存储过程的 创建,调用,查看,删除

# ****************************创建****************************
CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN
-- SQL语句
END ;
# ****************************调用****************************
CALL 名称 ([ 参数 ]);
# ****************************查看****************************
-- 查询指定数据库的存储过程及状态信息
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'; 
-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE 存储过程名称; 
# ****************************删除****************************
DROP PROCEDURE [ IF EXISTS ] 存储过程名称;
# ****************************举例****************************
delimiter $$  #设置MySQL语句结束符为$$
create procedure p1()
begin
    select count(*) from student;
end$$
delimiter ; #再设置为默认初始的 ;
call p1();  # 调用p1
drop procedure if exists p1; # 删除p1

七、存储过程 — 系统变量

注意:

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

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

八、存储过程 — 用户定义变量

九、存储过程 — 局部变量

# 查看系统变量 如果没有指定SESSION/GLOBAL,默认是SESSION,会话变量
SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE 'xxx'; -- 可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值
# 设置系统变量
SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;
# 赋值 用户定义变量  赋值时,可以使用 = ,也可以使用 := 
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
# 使用 用户定义变量  用户定义的变量无需对其进行声明或初始化,只不过获取到的值为NULL
SELECT @var_name ;
# 声明 局部变量
DECLARE 变量名 变量类型 [DEFAULT ... ] ;
# 赋值 局部变量
SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;

十、存储过程 — if 判断

if条件判断的结构中,ELSE IF 结构可以有多个,也可以没有。 ELSE结构可以有,也可以没有

IF 条件1 THEN
  ..... # 条件1 成立执行该THEN后的语句
ELSEIF 条件2 THEN -- 可选
  ..... # 条件2 成立执行该THEN后的语句
ELSE -- 可选
  ..... # 以上条件都不成立 成立执行该THEN后的语句
END IF;
# ---------------------举例:根据定义参数score,判定当前分数对应等级--------------------
drop procedure if exists p3;
create procedure p3()
begin
    declare score int default 58; #声明变量score为58,判断其分数等级
    declare grade varchar(10); #用于接收等级
    if score >= 85 then
        set grade := '优秀';
    elseif score >= 60 then
        set grade := '及格';
    else
        set grade := '不及格';
    end if;
    select grade;
end;
call p3; # 不及格

十一、存储过程 — 参数

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
  -- SQL语句
END ;
#---------------------举例:根据传入的200分制的分数,返回换算成百分制分数---------------------
create procedure  p5(inout score double)
begin
    set score := score * 0.5;
end;
set @myscore := 180;
call p5(@myscore);
select @myscore; # 90
# ---------------------举例:根据传入参数score,判定并返回当前分数对应等级--------------------
drop procedure if exists p4;
create procedure p4(in score int, out grade varchar(10))
begin
    if score >= 85 then
        set grade := '优秀';
    elseif score >= 60 then
        set grade := '及格';
    else
        set grade := '不及格';
    end if;
end;
call p4(66,@mygrade); # 调用时,直接传入两个参数,使用用户自定义变量接收grade的值
select @mygrade;

十二、存储过程 — case

case结构及作用,与基础篇中的流程控制函数类似,两种语法格式:

#语法1,含义: 当case_value的值为 when_value1时,执行statement_list1,当值为 when_value2时,执行statement_list2, 否则就执行 statement_list
CASE case_value
  WHEN when_value1 THEN statement_list1
  [ WHEN when_value2 THEN statement_list2] ...
  [ ELSE statement_list ]
END CASE;
#语法2,含义: 当条件search_condition1成立时,执行statement_list1,当条件search_condition2成立时,执行statement_list2, 否则就执行 statement_list
CASE
  WHEN search_condition1 THEN statement_list1
  [WHEN search_condition2 THEN statement_list2] ...
  [ELSE statement_list]
END CASE;
#---------------------举例:根据传入的月份,判定所属季度---------------------
drop procedure if exists p6;
create procedure p6(in month int)
begin
    declare ans varchar(10);
    case
        when month in (1,2,3)
            then set ans := '第一季度';
        when month in (4,5,6)
            then set ans := '第二季度';
        when month in (7,8,9)
            then set ans := '第三季度';
        when month in (10,11,12)
            then set ans := '第四季度';
    end case;
    select concat('您输入的月份为:',month, ',所在季度是:',ans);
end;
call p6(10); # 第四季度

十三、存储过程 — while

-- 先判定条件,如果条件为true,则执行逻辑,否则,不执行逻辑
WHILE 条件 DO
  SQL逻辑...
END WHILE;
#---------------------举例:计算从1累加到n的和,n为传入的参数值---------------------
drop procedure if exists p7;
create procedure p7(in n int)
begin
    declare sum int default 0;
    while n > 0 do
        set sum := sum + n;
        set n := n - 1;
    end while;
    select concat('从1到n的和为:',sum);
end;
call p7(10); #55

十四、存储过程 — repeat

-- 先执行一次逻辑,然后判定UNTIL条件是否满足,如果满足,则退出。如果不满足,则继续下一次循环
REPEAT
  SQL逻辑...
  UNTIL 条件
END REPEAT;
#---------------------举例:计算从1累加到n的和,n为传入的参数值---------------------
drop procedure if exists p8;
create procedure p8(in n int)
begin
    declare sum int default 0;
    repeat
        set sum := sum + n;
        set n := n - 1;
    until  n = 0
    end repeat;
    select concat('从1到n的和为:',sum);
end;
call p8(100); #5050

十四、存储过程 — loop

LOOP可以配合以下两个语句使用:

LEAVE :配合循环使用,退出循环

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

[begin_label:] LOOP
  SQL逻辑...
END LOOP [end_label];
LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环
#  begin_label,end_label,label 都是自定义的标记
#---------------------举例:计算从1累加到n的偶数和,n为传入的参数值---------------------
drop procedure if exists p10;
create procedure p10(in n int)
begin
    declare sum int default 0;
    getsum:loop #循环代码
        if n = 0 then
            leave getsum; # 退出循环 leave
        end if;
        if n%2 = 1 then
            set n := n - 1;
            ITERATE getsum;
        end if;
        set sum := sum + n;
        set n := n - 1;
    end loop getsum;
    select concat('从1到n的和为:',sum);
end;
call p10(10); #30

十四、存储过程 — 游标(CURSOR)

# 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句 ;
# 打开游标
OPEN 游标名称 ;
# 获取游标记录
FETCH 游标名称 INTO 变量 [, 变量 ] ;
# 关闭游标
CLOSE 游标名称 ;

十五、存储过程 — 条件处理程序(Handler)

DECLARE handler_action HANDLER FOR condition_value [, condition_value] ... statement ;
handler_action:
  CONTINUE: 继续执行当前程序
  EXIT: 终止执行当前程序
condition_value:
  SQLSTATE sqlstate_value: 状态码,如 02000
  SQLWARNING: 所有以01开头的SQLSTATE代码的简写
  NOT FOUND: 所有以02开头的SQLSTATE代码的简写
  SQLEXCEPTION: 所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写
  drop procedure if exists p12;
#---------------------举例:根据传入的参数uage,查询用户表tb_user中所有用户年龄小于等于uage的name和profession,并将这两个字段插入到新表 (id,name,profession)中---------------------
create procedure p12(in user_age int)
begin
    # 有先后顺序:先声明普通变量,再声明游标
    declare namenew varchar(100);
    declare profnew varchar(100);
    # 1.声明游标 存储查询结果集
    declare getInfo cursor for
        select name,profession from tb_user where age <= user_age;
    # 定义 条件处理程序,当状态码为02000时触发条件处理程序 -> 退出操作,关闭游标
    declare exit handler for sqlstate '02000' close getInfo;
    # declare exit handler for not found close getInfo;
    # 2.创建新表的 表结构
    drop table if exists tb_user_new;
    create table if not exists tb_user_new(
        id int primary key auto_increment,
        name varchar(100),
        profession varchar(100)
    );
    # 3.开启游标
    open getInfo;
    # 4.获取游标查询的结果集,循环遍历
    # 4.1 遍历的时候把查询结果集的name和profession赋值给两个局部变量,声明两个变量
    while true do
        # 获取游标记录
        fetch getInfo into namenew,profnew;
        # 4.3 插入新表数据
        insert into tb_user_new values(null,namenew,profnew);
    end while;
    # 关闭游标
    close getInfo;
end;
call p12(30);

十六、存储函数

CREATE FUNCTION 存储函数名称 ([ 参数列表 ])
RETURNS type [characteristic ...] # 指定返回值类型
BEGIN
  -- SQL语句
  RETURN ...; # 返回结果
END ;
#---------------------举例:计算从1累加到n的值,n为传入的参数值---------------------
create function fun1(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 fun1(100);

十七、触发器

# 创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON tbl_name FOR EACH ROW -- 行级触发器
BEGIN
  trigger_stmt ;
END;
# 查看
SHOW TRIGGERS ;
# 删除
DROP TRIGGER [schema_name.]trigger_name ; 
-- 如果没有指定 schema_name,默认为当前数据库


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
MySQL数据库进阶第六篇(InnoDB引擎架构,事务原理,MVCC)
|
1月前
|
SQL 关系型数据库 MySQL
MySQL数据库进阶第五篇(锁)
MySQL数据库进阶第五篇(锁)
|
1天前
|
存储 SQL 关系型数据库
MySQL语句详解:从基础到进阶的全面指南
MySQL语句详解:从基础到进阶的全面指南
|
22天前
|
JSON 关系型数据库 MySQL
MySQL常用函数解读:从基础到进阶的全方位指南
MySQL常用函数解读:从基础到进阶的全方位指南
|
20天前
|
存储 关系型数据库 分布式数据库
PolarDB产品使用问题之在将RDS迁移到PolarDB后,原先由root用户创建的视图、存储过程等是否可以继续使用的
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
23天前
|
SQL 关系型数据库 MySQL
MySQL进阶 - 备份与恢复
MySQL进阶 - 备份与恢复
15 0
|
23天前
|
SQL 关系型数据库 MySQL
MySQL进阶 - 日志
MySQL进阶 - 日志
10 0
|
23天前
|
存储 安全 关系型数据库
MySQL进阶(二)
MySQL进阶(二)
14 0
|
23天前
|
存储 监控 关系型数据库
MySQL进阶(一)
MySQL进阶(一)
8 0
|
24天前
|
存储 SQL 关系型数据库
在MySQL中使用存储过程返回更新前的记录
在MySQL中使用存储过程返回更新前的记录
15 0