MySql

MySql进阶(二)——视图、存储过程、触发器

Nick · 2月25日 · 2026年 · 本文9648字 · 阅读25分钟247

视图

视图(View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。
通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

语法

创建视图

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

查看创建视图语句

SHOW CREATE VIEW 视图名称;

查看视图数据

SELECT * FROM 视图名称…;

查看数据中中所有视图

SHOW FULL TABLES IN 数据库名 WHERE Table_type = 'VIEW';

修改视图

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子句创建视图时，MySOL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。
视图的插入、删除、更新语句和基本表的语句一致，当执行增删改操作时，实际改变的是基本表中的数据，但是不建议通过视图更改基本表。
为了确定检查的范围，mysql 提供了两个选项:CASCADED 和 LOCAL，默认值为CASCADED
cascaded：在对视图进行更删改操作时，会核查该视图及所有直接或间接依赖的视图创建语句中的条件，只有当所有条件都满足才能够操作成功，例如:

  • create view v1 as select id,name from student where id<=20;因为没有指定WITH CHECK OPTION子句，插入时不会核查条件id<=20，直接插入成功
  • create view v2 as select id,name from v1 where id>10 with cascaded check option;插入时会核查v2中的条件id>10和v1中的条件id<=20，只有全部满足时才会插入成功，相当于给v1添加了with cascaded check option子句
  • create view v3 as select id,name from v2 where id<=15;由于v3没有指定WITH CHECK OPTION子句，不会核查条件id<=15，但是由于v2指定了with cascaded check option子句，所以会核查v2的条件id>10和v1的条件id<=20;

local：在对视图进行更删改操作时，会核查该视图及所有直接或间接依赖的且使用WITH CHECK OPTION子句的视图创建语句中的条件，只有当所有条件都满足才能够操作成功，例如：

  • create view v4 as select id,name from student where id<=20;因为没有指定WITH CHECK OPTION子句，插入时不会核查条件id<=20，直接插入成功
  • create view v5 as select id,name from v4 where id>10 with local check option;插入时会核查v5中的条件id>10，因为v4中没有使用WITH CHECK OPTION子句，所以不会核查v4中的条件id<=20，也就是说，不会给v4添加with local check option子句
  • create view v6 as select id,name from v5 where id<=15;因为没有指定WITH CHECK OPTION子句，插入时不会核查条件id<=15，由于v5使用了with local check option子句，所以会核查v5中的条件id>10，又因为v4中没有使用WITH CHECK OPTION子句，所以不会核查v4中的条件id<=20，也就是说，仅仅核查有with local check option子句的视图中的条件

更新及作用

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系，即视图中的每一行必须直接对应基础表中的唯一一行，不能有多行映射到视图中的同一行，也不能有视图中的一行映射到基础表中的多行
如果视图包含以下任何一项，则该视图不可更新或插入：

  1. 聚合函数或窗口函数SUM()、MIN()、MAX()、COUNT()等
  2. DISTINCT
  3. GROUP BY
  4. HAVINGA
  5. UNION 或者 UNION ALL

视图的作用

  • 简单
    视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
  • 安全
    数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。
  • 数据独立
    视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

存储过程是事先经过编译并存储在数据库中的一段 SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程思想上很简单，就是数据库 SQL语言层面的代码封装与重用
特点：

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

1.基本语法

创建

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

调用

CALL 名称([参数]);

查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='xx';     ##查询数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称;    ##查询某个存储过程的定义

删除

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

注意：在命令行中，执行存储过程的SQL语句时，需要通过关键字delimiter指定SQL语句的结束符，如：delimiter $$，此会话之后所有的SQL语句遇到分号不会结束，结束符由分号替换成了$$

2.变量

2.1 系统变量

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

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

设置系统变量

SET [SESSION|GLOBAL] 系统变量名 = 值;    --设置全局/会话变量
SET @@GLOBAL.系统变量名 = 值, @@SESSION.系统变量名 = 值;    --同时设置全局系统变量和会话系统变量
  • 如果没有指定 session / global，默认 session，会话变量
  • myesql 服务器重启之后，所设置的全局参数会失效，要想不失效，需要更改/etc/my.cnf 中的配置。

2.2 用户定义变量

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

SET @var_name = expr [,@var_name = expr]...;    --定义单个或多个变量
SET @var_name := expr [,@var_name := expr]...;

SELECT @var_name := expr [,@var_name = expr]...;
SELECT 字段名 INTO @var_name FROM 表名;    --将查询结果赋值给变量@var_name

使用

SELECT @var_name;
  • 用户定义的变量无需对其进行声明或者初始化，只不过获取到的值为 NULL

2.3 局部变量

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

DECLARE 变量名 变量类型 [DEFAULT 默认值];
  • 变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME等

赋值

SET 变量名=值;

SET 变量名:=值;

SELECT 字段名 INTO 变量名 FROM 表名 ...;    --将查询结果赋值给局部变量

3.if 判断

语法

IF 条件1 THEN
        语句1
ELSEIF 条件2 THEN       -- 可选
        语句2
...
ELSE                   -- 可选
        语句n
END IF;

执行流程：先判断条件1是否成立，成立就执行语句1，否则判断条件2是否成立，成立就执行语句2，否则继续判断，最后执行语句n
案例

create procedure p3()
begin
  declare score int default 58;
  declare result varchar(10);
  if score >= 85 then
    set result :='优秀';
  elseif score >= 60 then
    set result :='及格';
  else
    set result :='不及格';
  end if;
  select result;
end;

4.带参存储过程

MySql进阶(二)——视图、存储过程、触发器-Nickの博客

用法

CREATE PROCEDURE 存储过程名称([IN|OUT|INOUT 参数名 参数类型 ]...)
BEGIN
    SQL语句
END;

5.case语句

语法一

CASE case_value
  WHEN when_value1 THEN statement_list1
  [WHEN when_value2 THEN statement_list2]...
  [ELSE statement_list ]
END CASE;

先得到case_value的值，依次与每个WHEN后的数值when_value比较，如果相等就执行相应的statement_list语句，然后结束CASE语句，如果没有一个when_value与case_value相等，会执行语句ELSE后的语句statement_list，然后结束CASE语句

语法二

CASE
  WHEN search_conditionl THEN statement_list1
  WHEN search_condition2 THEN statement_list2]...
  [ELSE statement_list]
END CASE;

按照顺序判断每个WHEN后的条件search_condition，如果条件为true，就执行相应的语句statement_list，然后结束CASE语句，如果每个WHEN后的条件search_condition都为false，就执行ELSE后的语句statement_list，然后结束CASE语句

6.循环

6.1 while

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

--先判定条件，如果条件为true，则执行逻辑，否则，不执行逻辑
WHILE 循环条件 DO
  SOL逻辑...
END WHILE;

案例

--计算从1累加到 n 的值
create procedure p7(in n int)
begin
  declare total int default 0;

  while n>0 do
    set total := total + n
    set n:=n-1;
  end while;

  select total;
end;
call p7( n: 100);

6.2 repeat

repeat是有条件的循环控制语句,当满足条件的时候退出循环
与 while 区别：

  1. 先进行循环一次再判断。相当于 c 语言中的 do while();
  2. 满足条件则退出

语法

--先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
  SOL逻辑
  UNTIL 循环条件
END REPEAT;

案例

--计算从1累加到 n 的值
create procedure p8(in n int)
begin
  declare total int default 0;

  repeat
    set total := total + n;
    set n := n - 1;
  until n <= 0
  end repeat;

  select total;
end;

call p8( n: 100);

6.3 loop

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

  1. LEAVE：配合循环使用，退出循环（类似break）
  2. ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环（类似continue）

语句

--label：LOOP循环的名字
[begin label:] LOOP
  SQL逻辑
END LOOP [end label];

LEAVE label;  -- 退出指定标记的循环体
ITERATE label;  -- 直接进入下一次循环

案例

--计算从1到n之间的偶数累加的值
create procedure p10(in n int)
begin 
  declare total int default 0;

  sum: loop
    if n <= 1 then
      leave sum;
    end if;

    if n %2 = 1 then
      set n := n - 1;
      iterate sum;
    end if;

    set total := total + n;
    set n := n - 1;
  end loop sum;

  select total;
end;

7.游标-cursor

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

DECLARE 游标名称 CURSOR FOR 查询语句;
  • 游标的声明必须放到变量声明的后面，否则会出错无法运行成功

打开游标

OPEN 游标名称;

获取游标记录

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

关闭游标

CLOSE 游标名称;

案例

--根据传入的参数uage，来查询用户表tb_user 中， 所有的用户年龄小于uage的用户姓名（name)和专业（profession），
--并将用户的姓名和专业插入到所创建的一张新表(id,name,profession)中
create procedure p11(in uage int)
begin 
  declare uname varchar(100);
  declare upro varchar(100);
  --声明游标，存储查询结果集
  declare u_cursor cursor for select name, profession from tb_user where age <= uage;
  --创建表tb_user_pro存游标中的记录
  drop table if exists tb_user_pro;
  create table if not exists tb_user_pro(
    id int primary key auto_increment,
    name varchar(100),
    profession varchar(100)
  );
  --打开游标
  open u_cursor;
  --获取游标中的数据并将其插入到表tb_user_pro中，会发生错误02000，解决方法见 条件处理程序-handler的案例
  while true do
    fetch u_cursor into uname,upro;
    insert into tb_user_pro values(null, uname, upro);
  end while;
  close u_cursor;
end;

8.条件处理程序-handler

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

DECLARE handler_action HANDLER FOR condition_value1, condition_value2... statement;

handler_action
  CONTINUE: 继续执行当前程序
  EXIT: 终止执行当前程序，即退出当前的"BEGIN...END"块

condition_value
  SQLSTATE sqlstate_value：状态码，如 02000
  SQLWARNING：所有以01开头的SQLSTATE代码的简写
  NOT FOUND：所有以02开头的SQLSTATE代码的简写
  SQLEXCEPTION：所有没有被SQLWARNING 或 NOT FOUND捕获的SQLSTATE代码的简写

案例

create procedure p11(in uage int)
begin 
  declare uname varchar(100);
  declare upro varchar(100);
  declare u_cursor cursor for select name, profession from tb_user where age <= uage;

  -- 监控到02000的状态码后，关闭游标后执行exit退出操作。
  declare exit handler for not found close u_cursor; 

  drop table if exists tb_user_pro;
  create table if not exists tb_user_pro(
    id int primary key auto_increment,
    name varchar(100),
    profession varchar(100)
  );

  open u_cursor;
  while true do
    fetch u_cursor into uname,upro;
    insert into tb_user_pro values(null, uname, upro);
  end while;
  close u_cursor;
end;

9.存储函数

存储函数是有返回值的存储过程，存储函数的参数只能是IN类型的
存储函数用的较少，能够使用存储函数的地方都可以用存储过程替换
语法

CREATE FUNCTION 存储函数名称([ 参数列表 ])
RETURNS type [characteristic ...]
BEGIN
  SQL语句
  RETURN 返回值;
END ;

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

案例

--计算从1累加到 n 的值
create function fun1(n int)
returns int deterministic
begin
  declare total int default 0;

  while n > 0 do 
    set total := total + n;
    set n := n - 1;
  end while;

  return total;
end;

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作
使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发（即改变一行就触发一次）
MySql进阶(二)——视图、存储过程、触发器-Nickの博客

创建触发器

CREATE TRIGGER trigger_name
BEFORE|AFTER INSERT|UPDATE|DELETE
ON tb_name FOR EACH ROW     --行级触发器BEGIN
  trigger_stmt;
END;

查看当前数据库中的所有触发器

SHOW TRIGGERS;

删除

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

案例
通过触发器记录tb_user表的数据变更日志，将变更日志插入到日志表user_logs中，包含增加、删除和修改

建表语句：

create table user_logs(
  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 tb_user_insert_trigger
  after insert on tb_user for each row
  begin 
  insert into user_logs(id, operation, operate_time, operate_id, operate_params)values
  (null, 'insert', now(), new.id, concat('插入的数据内容为：id=', new.id, ',name=', new.name, ', phone=', new.phone, ', email=', new.email, ', profession=', new.profession));
end;

-- 修改数据触发器
create trigger tb_user_update_trigger
  after update on tb_user for each row
  begin 
  insert into user_logs(id, operation, operate_time, operate_id, operate_params)values
  (null, 'update', now(), new.id, 
   concat('更新之前的数据：id=', old.id, ',name=', old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession,
    '更新之后的数据：id=', new.id, ',name=', new.name, ', phone=', new.phone, ', email=', new.email, ', profession=', new.profession));
end;

-- 删除数据触发器
create trigger tb_user_delete_trigger
  after delete on tb_user for each row
  begin 
  insert into user_logs(id, operation, operate_time, operate_id, operate_params)values
  (null, 'insert', now(), old.id, 
   concat('删除之前的数据：id=', new.id, ',name=', old.name, ', phone=', old.phone, ', email=', old.email, ', profession=', old.profession));
end;
相关文章

MySql进阶(六)——主从复制&分表分库

3月3日 · 2026年

MySql进阶(五)——锁&事务原理

2月28日 · 2026年

MySql进阶(四)——MySQL日志

2月28日 · 2026年

MySql进阶(三)——存储引擎&EXPLAIN详解

2月25日 · 2026年
0 条回应
©2021版权所有
在线人数：1人 来访统计
新时代
Ado
隐藏