1、视图

（1）什么是视图

视图是虚拟表，是基于查询结果的可视化表，视图的作用有：①简化复杂查询 ②限制数据访问 ③提供数据独立性 ④汇总数据

（2）怎么创建视图

创建视图 CREATE OR REPLACE VIEW 视图名 AS 查询语句;

我还是以举例进行说明：

-- 1. 创建 departments 表（部门表）
CREATE TABLE departments (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL COMMENT '部门名称'
);-- 2. 创建 employees 表（员工表）
CREATE TABLE employees (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL COMMENT '员工姓名',dept_id INT COMMENT '部门ID，外键',salary DECIMAL(10,2) COMMENT '薪资',FOREIGN KEY (dept_id) REFERENCES departments(id)
);-- 3. 插入示例数据
-- 插入部门
INSERT INTO departments (name) VALUES 
('技术部'),
('销售部'),
('人事部');-- 插入员工
INSERT INTO employees (name, dept_id, salary) VALUES
('Alice', 1, 8000.00),
('Bob', 1, 9000.00),
('Charlie', 2, 7000.00),
('David', 2, 7500.00),
('Eve', 3, 6000.00);-- 创建视图 CREATE OR REPLACE VIEW 视图名 AS 查询语句;
CREATE OR REPLACE VIEW v_employee_dept AS 
SELECT e.id,e.NAME AS 员工,e.salary AS 薪资,d.NAME AS 部门
FROM employees AS e JOIN departments AS d 
ON e.dept_id = d.id-- 查询视图：SELECT * FROM 视图名;
SELECT * FROM v_employee_dept

通过语句，视图就创建成功了，视图结果如图所示：

（3）视图管理

视图管理其实也就是对视图进行修改，更新，删除，重命名等操作

①修改

首先我们可以对视图进行修改

ALTER TABLE employees
ADD COLUMN hire_date DATE COMMENT '员工入职日期';-- 修改视图
CREATE OR REPLACE VIEW v_employee_dept AS
SELECT e.id, e.name, d.name AS dept_name, e.salary, e.hire_date
FROM employees e INNER JOIN departments d ON e.dept_id = d.id;

前两句话是将我们的employees表添加了一个列

后两句话相当于将视图重新创建了一份，可以看到上面加入的hire_date已经加入到了视图中

②更新

UPDATE v_employee_dept SET salary = 10000 WHERE name = 'Eve';

EVe的薪资被我们修改为了10000

③查询

查询就是每一次运行SQL语句后，我们都可以运行一次查询语句来观察我们的行为成功没有

SELECT * FROM v_employee_dept;

④重命名视图

-- RENAME TABLE 旧视图名 TO 新视图名;
RENAME TABLE v_employee_dept TO new_v_employee_dept

这时候我们就需要查看视图时，就需要查看后者的名称才可以查到，查询之前的则会报错

⑤删除视图

使用下面语句即可删除视图，可以通过再次查询看是否删除成功

DROP VIEW new_v_employee_dept;

2、存储

（1）存储过程

存储过程是一组预编译的SQL语句，保存在数据库中，可通过名称调用

存储过程的优点：①提高性能 ②简化复杂操作 ③增强安全性 ④减少网络流量

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE get_employee_by_dept(IN dept_name VARCHAR(50))
BEGINSELECT e.id, e.name, e.salaryFROM employees e INNER JOIN departments d ON e.dept_id = d.idWHERE d.name = dept_name;
END //
DELIMITER ;-- 调用存储过程
CALL get_employee_by_dept('技术部');

其实存储过程就相当于调用函数，在外面传入数据以进行重复的操作，这里就是读取employees表中的数据，读取的是技术部有关人员的信息

所以我们创建存储过程的公式如下

DELIMITER // 更改语句结束符为 //，避免 MySQL 过早解析 ; 导致错误
CREATE PROCEDURE ... 创建一个名为 get_employee_by_dept 的存储过程
IN dept_name VARCHAR(50) 定义输入参数：部门名称
BEGIN ... END 存储过程的执行体
CALL ... 调用存储过程并传参

（2）存储函数

存储函数是返回值的存储过程，可以在SQL语句中调用

-- 创建存储函数
-- 设置全局参数：允许用户创建存储函数
-- 当 MySQL 开启了二进制日志（binlog）时，出于复制安全考虑，默认限制创建不确定的函数。
-- 将此参数设为 TRUE 后，MySQL 会信任函数创建者，不会检查函数是否为确定性（DETERMINISTIC），
-- 从而允许创建函数，避免出现 "FUNCTION does not exist" 或权限拒绝的问题。
SET GLOBAL log_bin_trust_function_creators = TRUE;-- 更改语句结束符
-- 默认情况下，MySQL 使用分号（;）作为语句结束符。
-- 但在定义存储函数或存储过程中，函数体内部也包含分号，
-- 因此需要临时将结束符改为其他符号（如 //），以避免 MySQL 过早解析语句。
DELIMITER //-- 创建一个名为 get_dept_avg_salary 的存储函数
-- 函数接收一个输入参数 dept_name（部门名称），类型为 VARCHAR(50)
-- 返回值类型为 DECIMAL(10,2)，表示最多 10 位数字，保留 2 位小数（如 9500.00）
-- DETERMINISTIC 表示该函数是“确定性的”：对于相同的输入，总是返回相同的结果（本例中成立）
CREATE FUNCTION get_dept_avg_salary(dept_name VARCHAR(50))
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN-- 声明一个局部变量 avg_sal，用于存储查询到的平均薪资DECLARE avg_sal DECIMAL(10,2);-- 查询指定部门的平均薪资，并将结果存入变量 avg_sal-- 通过 INNER JOIN 关联 employees 和 departments 表，根据 dept_id 匹配-- 筛选出部门名称等于输入参数 dept_name 的员工，计算其 salary 的平均值SELECT AVG(e.salary) INTO avg_salFROM employees eINNER JOIN departments d ON e.dept_id = d.idWHERE d.name = dept_name;-- 返回计算得到的平均薪资值-- 如果没有匹配的员工，AVG() 返回 NULL，函数也将返回 NULLRETURN avg_sal;
END //-- 恢复默认的语句结束符为分号（;）
-- 完成函数定义后，将分隔符改回 ;，以便后续 SQL 语句正常执行
DELIMITER ;-- 调用存储函数：获取“技术部”的平均薪资
-- 使用 SELECT 语句调用函数，传入参数 '技术部'
-- 函数执行后返回该部门所有员工的平均工资
SELECT get_dept_avg_salary('技术部');

（3）触发器

触发器就是在表上定义的特殊存储过程，就是MySQL服务器在满足特定事件（INSERT/UPDATE/DELETE）并满足给定表时自动执行的一段SQL代码

要素	取值/说明
触发事件	INSERT、UPDATE、DELETE
触发时机	BEFORE（语句真正修改数据前）或 AFTER（语句已改完提交前）
作用表	只能建在基表（不能建在视图或临时表）

创建触发器

创建只有一个执行语句的触发器

create trigger 触发器名 before|after 触发事件
on 表名 for each row
执行语句;

创建有多个执行语句的触发器

create trigger 触发器名 before|after 触发事件
on 表名 for each row
begin执行语句列表
end;

可以根据实例来加深印象

-- 创建日志表 employee_log，用于记录员工表的操作日志
-- 该表将存储对 employees 表的增删改操作记录
CREATE TABLE employee_log (id INT PRIMARY KEY AUTO_INCREMENT, -- 日志ID，主键，自动递增operation VARCHAR(20), -- 操作类型，如 'INSERT'、'UPDATE'、'DELETE'employee_id INT, -- 被操作的员工ID，对应 employees 表的 idoperation_time DATETIME -- 操作发生的时间，精确到秒
);-- 更改语句结束符
-- 由于触发器定义中包含分号（;），需要临时将结束符改为其他符号（如 //）
-- 避免 MySQL 将内部语句误认为整个 CREATE TRIGGER 语句的结束
DELIMITER //-- 创建触发器：after_employee_insert
-- 触发器名称：after_employee_insert
-- 触发时机：AFTER INSERT（在向 employees 表插入新记录之后）
-- 触发对象：employees 表
-- 触发粒度：FOR EACH ROW（每插入一行数据，触发一次）
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN-- 触发器执行的操作：向日志表插入一条记录-- 记录内容包括：-- operation: 操作类型，固定为 'INSERT'-- employee_id: 新插入的员工ID，使用 NEW.id 获取（NEW 代表新行）-- operation_time: 当前时间，使用 NOW() 函数获取INSERT INTO employee_log (operation, employee_id, operation_time)VALUES ('INSERT', NEW.id, NOW());
END //-- 恢复默认的语句结束符为分号（;）
-- 完成触发器定义后，将分隔符改回 ;，以便后续 SQL 语句正常执行
DELIMITER ;-- 测试触发器：向 employees 表插入一条新员工记录
-- 插入姓名为 '孙八'，部门ID为 1，薪资为 9500 的员工
-- 此操作将触发上面定义的 after_employee_insert 触发器
INSERT INTO employees (name, dept_id, salary) VALUES ('孙八', 1, 9500);-- 查询日志表，查看触发器是否成功记录了操作
-- 应该能看到一条 operation 为 'INSERT'，employee_id 为新插入员工ID，时间接近当前时间的记录
SELECT * FROM employee_log;