Mysql-视图，函数，存储过程，触发器

---

一、视图

select语句执行后的结果集

• 特性：对多张表的引用，一张虚拟表，只存储查询方法，不存储具体数据。
• 作用：
  简化查询操作，增强可读性
  用户权限可以设定到视图级别（表数据的行、列级别权限控制）
• 场景：

# 临时禁用外键约束检查
SET FOREIGN_KEY_CHECKS=0;-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS course;
CREATE TABLE course (sid int(11) NOT NULL AUTO_INCREMENT,sname varchar(32) NOT NULL,PRIMARY KEY (sid)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '语文');
INSERT INTO `course` VALUES ('2', '数学');
INSERT INTO `course` VALUES ('3', '英语');
INSERT INTO `course` VALUES ('4', '物理');
INSERT INTO `course` VALUES ('5', '');-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS student;
CREATE TABLE student (id int(11) NOT NULL AUTO_INCREMENT,name varchar(32) NOT NULL,course_id int(11) NOT NULL,PRIMARY KEY (id),KEY fk_student_course (course_id),CONSTRAINT fk_student_course FOREIGN KEY (course_id) REFERENCES course (sid)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '小飞', '1');
INSERT INTO `student` VALUES ('2', 'hukey', '2');
INSERT INTO `student` VALUES ('3', '小王', '3');
INSERT INTO `student` VALUES ('4', '阿狗', '4');

查询小飞上的所有课程的相关信息
id, name, sname

select id,name,sname from student left join course on student.course_id = course.sid where name=“小飞”;

1. 创建视图

create view view_student_course as ( select id,name,sname from student left join course on student.course_id = course.sid where name=“小飞”);

2. 使用视图

查询
select * from view_student_course;

创建所有学生的视图并用这个视图查看"小飞"的信息

root@test 10: 04>create view view_student_course_all as ( select id,name,sname from student left join couurse on student.course_id = course.sid);
Query OK, 0 rows affected (0.01 sec)root@test 10: 05>select *  from view_student_course_all;
+----+--------+--------+
| id | name   | sname  |
+----+--------+--------+
|  1 | 小飞   | 语文   |
|  2 | hukey  | 数学   |
|  3 | 小王   | 英语   |
|  4 | 阿狗   | 物理   |
+----+--------+--------+
4 rows in set (0.00 sec)root@test 10: 05>select *  from view_student_course_all where name="小飞";
+----+--------+--------+
| id | name   | sname  |
+----+--------+--------+
|  1 | 小飞   | 语文   |
+----+--------+--------+
1 row in set (0.00 sec)

---

3. 查看视图

查看有哪些视图
show table status where comment="view"\G

查看视图的创建语句

root@test 10: 08>show create view view_student_course_all\G
*************************** 1. row ***************************View: view_student_course_allCreate View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_student_course_all` AS select `student`.`id` AS `id`,`student`.`name` AS `name`,`course`.`sname` AS `sname` from (`student` left join `course` on((`student`.`course_id` = `course`.`sid`)))
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

---

4. 修改视图

alter view `视图名字`AS (sql);

root@test 10: 22>alter view view_student_course_all as (select * from student);
Query OK, 0 rows affected (0.00 sec)root@test 10: 22>select * from view_student_course_all;
+----+--------+-----------+
| id | name   | course_id |
+----+--------+-----------+
|  1 | 小飞   |         1 |
|  2 | hukey  |         2 |
|  3 | 小王   |         3 |
|  4 | 阿狗   |         4 |
+----+--------+-----------+

---

5. 删除视图

drop view `视图名字`;

drop view view_student_course;

---

二、函数

是可以接收参数并返回一个值的SQL代码

1. 定义函数

自定义函数: 计算折扣价=> 单价，折扣

delimiter //
create function fun_price(price decimal(10,2), rate decimal(3,2)) returns decimal(10,2) DETERMINISTIC
begin declare result decimal(10,2);set result = price*rate;return result;
end //
delimiter ;

DETERMINISTIC => 声明该函数是确定性函数，即输入相同时，输出结果也相同

使用函数

select pname, fun_price(price,0.9) from product;

---

2. 查看函数

show function status\G

查看函数的创建语句

root@test 10: 40>show create function fun_price\G
*************************** 1. row ***************************Function: fun_pricesql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONCreate Function: CREATE DEFINER=`root`@`localhost` FUNCTION `fun_price`(price decimal(10,2), rate decimal(3,2)) RETURNS decimal(10,2)DETERMINISTIC
begin declare result decimal(10,2);set result = price*rate;return result;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ciDatabase Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

---

3. 删除函数

drop function 函数名;

没有修改函数的方法 => 删除后重新建

---

三、存储过程

一组可编程函数，为了完成特定功能的SQL语句集，经过编译保存在数据中，用户可以通过存储过程的名字，参数来调用执行

• 封装：将重复性执行的内容写到存储过程，简化SQL调用
• 统一接口，确保数据安全

视图VS存储过程

DELIMITER 是一个特殊的命令，用于修改 SQL 语句的分隔符。避免在包含多条 SQL 语句的代码中块分号（;）被误判为语句结束符

delimiter //
create procedure procedure_name([arg_list])
begin ---SQL
end //
delimiter ;

1. 创建存储过程

创建一个存储过程 product表中所有产品信息

delimiter //
create procedure get_all_products()
begin select * from product;
end //
delimiter ;

调用

call get_all_products()

创建一个存储过程，根据产品cid查询产品信息
注意：参数名不要跟字段同名

delimiter //
create procedure get_all_products01(in category char(4))
begin select * from product where cid=category;
end //
delimiter ;call get_all_products01('c002');

创建一个存储过程，获取产品最高价格，通过输出返回参数

delimiter //
create procedure get_all_products02(out max_price double)
begin select max(price) into max_price from product;
end //
delimiter ;# 初始化变量
set @max_price_value = 0;
# 调用存储过程，传入变量（存储过程会修改该变量的值）
call get_all_products02(@max_price_value);
# 查询变量的值，显示存储过程返回的结果
select @max_price_value;

---

2. 查看存储过程

show procedure status \G

查看存储过程创建语句

root@test 11: 28>show create procedure get_all_products02\G
*************************** 1. row ***************************Procedure: get_all_products02sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONCreate Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `get_all_products02`(out max_price double)
begin select max(price) into max_price from product;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ciDatabase Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

3. 删除存储过程

drop procedure get_all_products02

---

四、触发器

在满足条件时触发对应的执行语句

• 触发条件：insert,update,delete
• 触发时间：前，后
• 触发频率：每一执行
• 触发对象：表

转账表 -> 日志表

触发器问题：
1.后台执行,有时候忘记了…
2. 资源消耗

create trigger trigger_name trigger_time trigger_event on table_name for each row

1. 创建触发器

记录表的插入操作

root@test 11: 29>create table time (
-> id int primary key auto_increment,
-> t datetime);
Query OK, 0 rows affected (0.02 sec)

root@test 11: 49>delimiter //
create trigger trig1 after insert on product for each row
begin
insert into time (t) values(now());
end //
delimiter ;

插入数据验证触发器

root@test 11: 51>insert into product values(14, 'test', 100, 'c003');
Query OK, 1 row affected (0.01 sec)root@test 11: 54>select * from time;
+----+---------------------+
| id | t                   |
+----+---------------------+
|  1 | 2025-07-26 11:54:25 |
+----+---------------------+
1 row in set (0.01 sec)

打卡表
id,date,name,start,end,status
2025/07/26 ,cici, 9:30, NULL,
staus => 触发器

---

2. 查看触发器

root@test 12: 00>show triggers \G
*************************** 1. row ***************************Trigger: trig1Event: INSERTTable: productStatement: insert into time (t) values(now())Timing: AFTERCreated: 2025-07-26 11:52:47.74sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONDefiner: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ciDatabase Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

查看创建触发器语句

root@test 12: 01>show create trigger trig1\G
*************************** 1. row ***************************Trigger: trig1sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `trig1` AFTER INSERT ON `product` FOR EACH ROW insert into time (t) values(now())character_set_client: utf8mb4collation_connection: utf8mb4_0900_ai_ciDatabase Collation: utf8mb4_0900_ai_ciCreated: 2025-07-26 11:52:47.74
1 row in set (0.00 sec)

3. 删除触发器

drop trigger trig1

---

总结

工具	核心能力	触发方式	典型用途
视图	简化查询、数据隔离	被动查询（SELECT）	封装复杂查询、限制数据访问
函数	计算并返回单个值	主动调用（嵌入 SQL）	封装计算逻辑、格式化数据
存储过程	执行复杂流程、事务处理	主动调用（CALL）	业务逻辑处理、批量操作
触发器	自动响应数据变更	事件触发（INSERT等）	数据校验、日志记录、跨表同步