视图函数与过程

# 视图函数与过程

# 视图

在 MySQL 中，视图（View）是一个虚拟的表，其内容由查询定义。视图的内容并不在数据库中实际存储，而是存储在数据字典中。当用户对视图进行查询时，数据库会动态地执行定义视图的 SELECT 语句，并返回结果。视图可以包含表中的所有行，也可以只包含满足特定条件的行。

虚拟表：视图是基于 SQL 语句的结果集的可视化表示，它本身不存储数据，而是根据查询动态生成数据。
简化复杂查询：通过创建视图，可以将复杂的 SQL 查询封装起来，使得用户可以通过查询视图来间接执行复杂的查询，从而简化数据检索操作。
增加数据安全性：通过视图，可以控制用户对数据的访问权限。例如，可以创建一个只包含某些列或行的视图，并授权用户查询该视图，而不是直接查询表，从而保护表中的数据不被未授权访问。
逻辑数据独立性：当数据库表结构发生变化时（如增加列、删除列或修改列名），只要视图定义的查询能够正确执行，用户通过视图查询数据就不会受到影响。这提高了数据库的逻辑数据独立性。

# 创建视图

CREATE VIEW 视图名称 AS  
SELECT 列名称  
FROM 表名称  
WHERE 条件;


CREATE VIEW high_salaries AS  
SELECT name, salary  
FROM employees  
WHERE salary > 5000;

# 使用视图

SELECT * FROM high_salaries;

# 更新视图

虽然视图是基于查询的虚拟表，但在某些情况下，也可以对视图进行更新操作（如插入、更新、删除）。然而，并非所有视图都是可更新的，视图的更新操作受到以下条件的限制：

视图中包含的列必须是表中唯一存在的列（即没有使用聚合函数或计算列）
视图中的行与基础表中的行之间必须能够一对一地映射
视图中没有使用 GROUP BY 或 DISTINCT 子句
视图中的列没有被嵌套查询引用

如果视图定义中包含了以下任何一项，那么它通常被认为是不可更新的：

聚合函数（如 SUM、AVG、COUNT 等）
DISTINCT 关键字
GROUP BY 子句
使用了 JOIN 操作的复杂查询（尽管在某些情况下，简单的 JOIN 视图也可能是可更新的，但这取决于具体的数据库实现和查询的复杂性）
子查询在 SELECT 列表中（除非这些子查询不引用表中的任何行）
UNION 或 UNION ALL 操作符
某些类型的表达式（如计算字段）

# 删除视图

drop view if exists `high_salaries`

# 查看视图

如果给你一个数据库，你怎么知道在这个数据库中是否有视图呢？

show full tables from `hrs` where `table_type` = 'VIEW';

怎么查看视图的结构呢？

desc `view_name`;


show create view `view_name`;

# 函数

在 mysql 中，函数分为内置函数（系统函数），用户自定义函数（UDF）

# 内置函数

字符串函数：concat(),length(),substring(),replace()等，用于处理字符串函数
数学函数：abs(),ceil(),floor(),round()等，用于执行数学运算
日期和时间函数：now(),curdate(),date_format(),datediff()等，用于处理日期和时间数据
聚合函数：sum(),avg(),count(),max(),min()等，用于对数据进行汇总
条件函数：如if(),case语句等，用于在查询中实现条件逻辑

# 用户自定义函数

UDF 需要使用 C 或 C++ 编写，并编译成动态链接库（DLL 或 so 文件），然后在 MySQL 服务器上注册。

以下是自定义函数：

delimiter $$

create function fn_truncate_string(
    content varchar(10000),
    max_length int unsigned
) returns varchar(10000) no sql
begin
    declare result varchar(10000) default content;
    if char_length(content) > max_length then
        set result = left(content, max_length);
        set result = concat(result, '……');
    end if;
    return result;
end $$

delimiter ;

函数声明后面的no sql是声明函数体并没有使用 SQL 语句；如果函数体中需要通过 SQL 读取数据，需要声明为reads sql data。

定义函数前后的delimiter命令是为了修改终止符（定界符），因为函数体中的语句都是用;表示结束，如果不重新定义定界符，那么遇到的;的时候代码就会被截断执行，显然这不是我们想要的效果。

调用函数：

select fn_truncate_string('和我在成都的街头走一走，直到所有的灯都熄灭了也不停留', 10) as short_string;

# 存储过程

在MySQL中，存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，它存储在数据库中，可以通过指定的名称并传递参数（如果有的话）来被调用执行。存储过程可以被视为数据库中的一个程序，它封装了处理数据的逻辑，以便可以重复使用。

存储过程的特点：

封装性：存储过程将复杂的SQL逻辑封装在一个单元中，简化了复杂操作的使用
可重用性：一旦创建，存储过程就可以在多个地方被调用，无需重复编写相同的SQL代码
安全性：通过限制对存储过程的访问权限，可以更安全地控制对数据库的操作
减少网络流量：由于存储过程在数据库服务器上执行，因此可以减少客户端和服务器之间的数据传输量
性能优化：数据库可以对存储过程进行优化，因为它们的执行计划可以在第一次执行时被缓存并重用

# 创建存储过程

CREATE PROCEDURE procedure_name (  
    [IN | OUT | INOUT] parameter_name datatype,  
    ...  
)  
BEGIN  
    -- SQL语句  
    -- 可以是DDL、DML、DCL语句  
    -- 可以使用变量、条件语句、循环等  
END;

举例1

DELIMITER //  
  
CREATE PROCEDURE AddNumbers(IN num1 INT, IN num2 INT, OUT result INT)  
BEGIN  
    SET result = num1 + num2;  
END //  
  
DELIMITER ;  
  
-- 调用存储过程  
CALL AddNumbers(5, 10, @sum);  
  
-- 查询输出参数的值  
SELECT @sum AS SumResult;

举例2

delimiter $$

create procedure sp_upgrade_salary()
begin
    declare flag boolean default 1;
    -- 定义一个异常处理器
    declare continue handler for sqlexception set flag=0;

    -- 开启事务环境
    start transaction;
    
    update tb_emp set sal=sal+300 where dno=10;
    update tb_emp set sal=sal+800 where dno=20;
    update tb_emp set sal=sal+500 where dno=30;

    -- 提交或回滚事务
    if flag then
        commit;
    else
        rollback;
    end if;
end $$

delimiter ;


call sp_upgrade_salary();

# UDF与存储过程的区别

返回值：用户自定义函数必须有返回值，存储过程通常不直接返回值
调用方式：存储过程使用 call 语句进行调用，而用户自定义函数可以在任何使用函数的地方调用，如select语句等

← DCL 索引 →