[TOC]
一、视图（View）
1.1 视图概述
视图是一种虚拟存在的表，其数据并不实际存储在数据库中，行和列的数据源于定义视图的查询所使用的表，且在使用视图时动态生成。通俗来讲，视图仅保存查询的SQL逻辑，而非查询结果，因此创建视图的核心在于构建该SQL查询语句。
1.2 视图操作
创建视图
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]查询视图
查看创建视图语句：
SHOW CREATE VIEW 视图名称;查看视图数据：
SELECT * FROM 视图名称;
修改视图
ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]删除视图
DROP VIEW [IF EXISTS] 视图名称 [,视图名称]...
1.3 视图的检查选项（WITH CHECK OPTION）
当使用该子句创建视图时，MySQL会对通过视图进行的插入、更新、删除操作的每一行进行检查，确保其符合视图定义。
假设存在一个 employees 表，包含 id、name、department和 salary字段。创建一个仅显示销售部门（department = 'Sales'）的视图：
CREATE VIEW sales_employees AS
SELECT * FROM employees
WHERE department = 'Sales'
WITH CHECK OPTION; -- 添加检查选项如果尝试通过视图插入一条非销售部门的记录，由于 department = 'HR'不满足视图的过滤条件，插入会被拒绝，并抛出错误。：
INSERT INTO sales_employees (name, department, salary)
VALUES ('John Doe', 'HR', 5000); -- ❌ 操作会失败检查范围通过CASCADED（默认，级联检查依赖视图规则）和LOCAL（仅检查当前视图规则）控制。
1.4 视图的可更新性
视图可更新的前提是其行与基础表的行存在一对一关系。若视图包含聚合函数、DISTINCT、GROUP BY、HAVING、UNION等，该视图不可更新。
1.5 视图的作用
简化操作：将常用查询定义为视图，避免重复编写复杂条件。
增强安全性：通过视图限制用户可见数据，实现行和列级别的权限控制。 (可以实现一些开会员或三天可见等功能)
数据独立性：屏蔽真实表结构变化对应用的影响。
1.6 案例
需求1：屏蔽
tb_user表的手机号和邮箱字段CREATE VIEW v_user_basic AS SELECT id, name, age FROM tb_user;需求2：简化三张表联查学生课程信息
CREATE VIEW v_student_course AS SELECT s.name, c.course_name, sc.score FROM student s JOIN student_course sc ON s.id = sc.student_id JOIN course c ON sc.course_id = c.id;
二、存储过程（Procedure）
2.1 存储过程概述
存储过程是预先编译并存储在数据库中的SQL语句集合，可简化开发、减少数据传输并提升处理效率，本质是SQL层面的代码封装与重用。
2.2 存储过程特点
封装与复用：将复杂逻辑封装为独立模块，可重复调用。
参数支持：可接收输入参数（IN）、输出参数（OUT）和输入输出参数（INOUT）。
高效性：减少应用与数据库的网络交互，提升执行效率。
2.3 存储过程操作
创建存储过程
DELIMITER // CREATE PROCEDURE 存储过程名称([IN/OUT/INOUT 参数名 参数类型]) BEGIN -- SQL语句 END // DELIMITER ;调用存储过程
CALL 存储过程名称 ([参数]);查看存储过程
查询指定数据库的存储过程：
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = '数据库名';查看存储过程定义：
SHOW CREATE PROCEDURE 存储过程名称;
删除存储过程
DROP PROCEDURE [IF EXISTS] 存储过程名称;
2.4 变量类型
系统变量：由MySQL服务器提供，分为全局变量（GLOBAL）和会话变量（SESSION）。
-- 查看所有系统变量 SHOW [SESSION | GLOBAL] VARIABLES; -- 可以通过 LIKE 模糊匹配方式查找变量 SHOW [SESSION | GLOBAL] VARIABLES LIKE '......'; -- 查看指定变量的值 SELECT @@[SESSION | GLOBAL]系统变量名; -- 设置系统变量（两种等效语法） SET [SESSION | GLOBAL] 系统变量名 = 值; SET @@[SESSION | GLOBAL]系统变量名 = 值;注意:
如果没有指定SESSION/GLOBAL，默认是SESSION会话变量
mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在
/etc/my.cnf中配置
用户定义变量：无需声明，作用域为当前连接(会话)，以
@开头。赋值：
-- 声明变量并赋值 SET @var_name = expr [, @var_name = expr] ...; SET @var_name := expr [, @var_name := expr] ...; -- 在 SELECT 语句中声明变量并赋值 SELECT @var_name := expr [, @var_name := expr] ...;使用：
-- 将查询结果存入变量 SELECT 字段名 INTO @var_name FROM 表名; -- 查询变量值 SELECT @var_name;注意:
用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。
局部变量：在存储过程内声明，作用域为所在
BEGIN...END块。-- DECLARE 变量名 变量类型 [DEFAULT ... ] ; DECLARE grade VARCHAR(20) DEFAULT '不及格';
2.5 流程控制语句
IF语句：条件判断
create procedure pScore(in score int, out result varchar(10)) begin if score >= 85 then set result = '优秀'; elseif score >= 60 then set result = '及格'; else set result = '不及格'; end if; end; call pScore(98, @result) select @resultCASE语句：多条件分支
create procedure pQuarter(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;循环语句：包括
WHILE（条件满足时执行）、REPEAT（先执行再判断条件）、LOOP（配合LEAVE/ITERATE实现灵活循环）。While
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;Repeat
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;Loop
create procedure p10(in n int) begin declare total int default 0; sum:loop if n<=0 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;
2.6 游标（CURSOR）
用于存储查询结果集，在存储过程中循环处理数据，步骤包括
声明：
DECLARE 游标名称 CURSOR FOR 查询语句;打开：
OPEN 游标名称;获取记录：
FETCH 游标名称 INTO 变量 [,变量];关闭：
CLOSE 游标名称;
create procedure pTestCursor(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;
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;该程序通过 while true 循环持续从游标中获取数据，将获取到的 name、profession 插入到 tb_user_pro 表 ，但此代码存在问题：因为没有游标遍历结束的判断条件，会导致循环无法正常终止，一直执行插入直到报错（游标取完数据后继续 fetch 会触发错误 ），实际使用需补充游标遍历结束的判断逻辑（比如结合 DECLARE CONTINUE HANDLER 处理游标遍历完成的情况
2.7 条件处理程序（Handler）
定义流程控制中遇到问题的处理步骤，如：
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement;参数说明：
handler_action:
CONTINUE -- 发生异常时继续执行当前程序（跳过异常语句，执行后续逻辑）
EXIT -- 发生异常时终止执行当前程序（退出当前 BEGIN...END 块）
condition_value:
SQLSTATE 'sqlstate_value' -- 特定 SQLSTATE 状态码，如 '02000'
SQLWARNING -- 所有以 '01' 开头的 SQLSTATE 代码的简写
NOT FOUND -- 所有以 '02' 开头的 SQLSTATE 代码的简写
SQLEXCEPTION -- 所有未被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 代码的简写https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
使用示例（处理**游标遍历结束的情况）**
create procedure pTestCursor(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;
declare exit handler for SQLSTATE '02000' 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;三、存储函数（Function）
3.1 存储函数概述
存储函数是有返回值的存储过程，参数仅支持IN类型，语法如下：
CREATE FUNCTION 函数名称([参数列表])
RETURNS 数据类型 [CHARACTERISTIC...]
BEGIN
-- SQL语句
RETURN 结果;
END;3.2 案例
需求：计算1到n的累加和
CREATE FUNCTION func_sum(n INT) RETURNS INT BEGIN DECLARE sum INT DEFAULT 0; WHILE n > 0 DO SET sum = sum + n; SET n = n - 1; END WHILE; RETURN sum; END;
四、触发器（Trigger）
4.1 触发器概述
触发器是与表相关的数据库对象，在INSERT、UPDATE、DELETE操作前或后自动触发执行SQL语句，用于数据完整性校验、日志记录等。通过OLD和NEW引用变化前后的数据：
INSERT触发器：
NEW表示新增数据。UPDATE触发器：
OLD表示修改前数据，NEW表示修改后数据。DELETE触发器：
OLD表示删除前数据。
4.2 触发器操作
创建触发器
CREATE TRIGGER tri_user_log AFTER INSERT ON tb_user FOR EACH ROW BEGIN INSERT INTO user_logs(operation, operate_time, operate_id, operate_params) VALUES('insert', NOW(), NEW.id, CONCAT('name:', NEW.name, ',age:', NEW.age)); END;查看触发器：
SHOW TRIGGERS;删除触发器：
DROP TRIGGER [数据库名.]触发器名称;
4.3 案例
需求：记录
tb_user表的增删改日志CREATE TRIGGER tri_user_update_log AFTER UPDATE ON tb_user FOR EACH ROW BEGIN INSERT INTO user_logs(operation, operate_time, operate_id, operate_params) VALUES('update', NOW(), OLD.id, CONCAT('old_name:', OLD.name, '→new_name:', NEW.name)); END;
五、总结
合理使用这四类对象可显著提升数据库开发的效率与安全性，实现复杂业务逻辑的高效管理。
评论