MySQL学习（六）：视图和存储过程以及函数

MySQL学习（六）：视图和存储过程以及函数

1. 视图

---

1.1 视图的作用与特性

视图是数据库中的一种特殊类型的虚拟表，它允许用户以不同的方式组织和查看数据。通过使用视图，数据库管理员可以提供一种更为安全和灵活的数据访问方式

• 精细的权限控制：视图允许管理员创建只包含所需数据的视图，从而限制用户对敏感信息的访问，提升数据的安全性。
• 数据抽象与简化：视图将复杂的查询逻辑封装起来，为用户提供了一个简化的数据访问接口，使得用户可以更加直观地操作数据。
• DML操作的映射：通过视图对数据进行DML操作，这些变更会直接反映到底层数据表中，实现了数据操作的透明化。然而，删除操作通常不会影响底层数据表，除非视图的创建语句中包含了特定的删除逻辑。

1.2 视图的创建

创建视图的语法简洁明了

CREATE VIEW 视图名 AS 查询语句;

更丰富的语法允许指定算法和选项

CREATE [OR REPLACE]
[ALGORITHM = {undefined | merge | temptable}]
VIEW 视图名 [(字段列表)]
AS 查询语句
[WITH [CASCADING] CHECK OPTION];

• ALGORITHM：定义视图的存储算法，undefined表示由数据库系统决定，merge表示视图可以像普通表一样进行操作，temptable表示视图在每次查询时都会重新创建。
• CHECK OPTION：确保视图中的数据不会违反查询语句中的任何条件，提供了一种数据完整性保障。

1.3 视图的查看与维护

• 查看所有表和视图

SHOW TABLES;

• 查看视图属性信息

SHOW TABLE STATUS LIKE '视图名';

• 查看视图的完整定义

SHOW CREATE VIEW '视图名';

1.4 视图的修改与删除

修改视图需要使用CREATE OR REPLACE语句，以确保视图定义的正确性

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

删除视图

DROP VIEW [IF EXISTS] `视图名`;

需要注意的是，删除视图本身不会删除其底层数据表，但如果删除了底层数据表，视图将变为无效。

2. 存储过程与函数

---

2.1 存储过程创建

存储过程是数据库编程中的一种高级特性，它允许开发者将一系列SQL语句封装成一个单元，以便重复使用。

• 定义存储过程

创建存储过程时，需要使用特殊的定界符来标识存储过程的开始和结束。

DELIMITER //
CREATE PROCEDURE 存储过程名()
BEGIN-- 存储过程内的SQL语句DECLARE ...; -- 声明变量-- SQL操作逻辑
END //
DELIMITER ;

• 调用存储过程

存储过程通过CALL语句进行调用，调用时可以传递参数（如果有的话）

CALL 存储过程名(参数1, 参数2, ...);

• 参数类型

  • IN参数：用于传递数据到存储过程中，但不返回任何值。
  • OUT参数：用于从存储过程返回数据。
  • INOUT参数：既可以传递数据到存储过程，也可以从存储过程返回数据。

例如，一个输出参数的存储过程，用于计算员工最低薪资

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGINSELECT MIN(salary) INTO ms FROM employees;
END ;

调用此存储过程并获取最低薪资

CALL show_min_salary(@ms);
SELECT @ms;

2.2 存储函数

存储函数与存储过程类似，但它们的设计目的是返回一个值。

CREATE FUNCTION 函数名(参数 参数类型)
RETURNS 返回值类型
BEGIN-- 函数体-- 返回计算结果RETURN 返回值;
END ;

调用存储函数的方式与调用存储过程类似

SELECT 函数名(参数值);

2.3 查看修改和删除

• 查看存储过程或函数

可以通过SHOW CREATE命令来查看存储过程或函数的详细定义

SHOW CREATE PROCEDURE '存储过程名';
SHOW CREATE FUNCTION '函数名';

• 查看存储过程或函数状态

SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS命令可以用来查看存储过程或函数的当前状态，包括创建时间、修改时间和定义信息

SHOW PROCEDURE STATUS LIKE '存储过程名';
SHOW FUNCTION STATUS LIKE '函数名';

• 查看系统表

information_schema.Routines表提供了关于存储过程和函数的详细信息，包括名称、类型、定义等

SELECT * FROM information_schema.Routines
WHERE routine_name = '存储过程名' OR function_name = '函数名'
AND routine_type = 'PROCEDURE' OR function_type = 'FUNCTION';

• 删除存储过程或函数

当不再需要存储过程或函数时，可以使用DROP命令将其从数据库中删除

DROP PROCEDURE '存储过程名';
DROP FUNCTION '函数名';