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，默认为当前数据库

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

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

二、视图的检查选项

三、视图的更新

四、视图的作用

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

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

七、存储过程 — 系统变量

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

九、存储过程 — 局部变量

十、存储过程 — if 判断

十一、存储过程 — 参数

十二、存储过程 — case

十三、存储过程 — while

十四、存储过程 — repeat

十四、存储过程 — loop

十四、存储过程 — 游标（CURSOR）

十五、存储过程 — 条件处理程序（Handler）

十六、存储函数

十七、触发器

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

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

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

二、视图的检查选项

三、视图的更新

四、视图的作用

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

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

七、存储过程 — 系统变量

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

九、存储过程 — 局部变量

十、存储过程 — if 判断

十一、存储过程 — 参数

十二、存储过程 — case

十三、存储过程 — while

十四、存储过程 — repeat

十四、存储过程 — loop

十四、存储过程 — 游标（CURSOR）

十五、存储过程 — 条件处理程序（Handler）

十六、存储函数

十七、触发器

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

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