MySQL学习笔记7——视图和存储过程

一、视图

1、视图的作用

视图是一种虚拟表，我们可以把一段查询语句作为视图存储在数据库中，在需要的时候，可以把视图看做一个表， 对里面的数据进行查询。

创建视图语法结构：

CREATE [OR REPLACE]
VIEW 视图名称[(字段列表)]
AS 查询语句

现在，假设我们要查询一下商品的每日销售明细，这就要从销售流水表(demo.mytrans) 和商品信息表(demo.goodmaster)中获取到销售数据和对应的商品信息数据：

销售流水表（mytrans）：

商品信息表（goodmaster）：

在不使用视图的情况下，我们可以通过对销售流水表和商品信息表进行关联查询，得到每天商品销售统计的结果，包括销售日期、商品名称、每天销售数量的合计和每天销售金额的合计，如下所示：

SELECT
a.transdate,
a.itemnumber,
b.goodsname,
SUM(a.salesquantity) AS quantity, -- 统计销售数量
SUM(a.salesvalue) AS salesvalue -- 统计销售金额
FROM
demo.mytrans AS a
LEFT JOIN		-- 连接查询
demo.goodmaster AS b ON (a.itemnumber = b.itemnumber)
GROUP BY a.transdate , a.itemnumber;

在实际项目中，我们发现，每日商品销售查询使用的频次很高，而且经常需要以这个查询的结果为基础，进行更进一步的统计。

举个例子，超市经营者要查一下“每天商品的销售数量和当天库存数量的对比”，如果用一个SQL语句查询，就会比较复杂。历史库存表（inventoryhist）如下所示：

接下来我们的查询步骤会使用到子查询和派生表

• 子查询：就是嵌套在另一个查询中的查询。
• 派生表：如果我们在查询中把子查询的结果作为一个表来使用，这个表就是派生表。

这个查询的具体步骤是:

1. 通过子查询获得单品销售统计的查询结果;
2. 把第一步中的查询结果作为一个派生表，跟历史库存表进行连接，查询获得包括销售日期、商品名称、销售数量和历史库存数量在内的最终结果。

SELECT
a.transdate,
a.itemnumber,
a.goodsname,
a.quantity, 		-- 获取单品销售数量
b.invquantity		-- 获取历史库存数量
FROM
(SELECT					-- 子查询，统计单品销售
a.transdate,
a.itemnumber,
b.goodsname,
SUM(a.salesquantity) AS quantity,
SUM(a.salesvalue) AS salesvalue 
FROM
demo.mytrans AS a
LEFT JOIN
demo.goodmaster AS b ON (a.itemnumber = b.itemnumber)
GROUP BY a.transdate , a.itemnumber
) AS a -- 派生表，与历史库存进行连接
LEFT JOIN
demo.inventoryhist AS b
ON (a.transdate=b.invdate AND a.Itemnumber=b.Itemnumber);

可以看到，这个查询语句是比较复杂的，可读性和可维护性都比较差。针对这种情况，我们就可以使用视图。

我们可以把商品的每日销售统计查询做成一个视图,存储在数据库里，代码如下所示:

CREATE VIEW demo.trans_goodmaster AS		-- 创建视图
SELECT
a.transdate,
a.itemnumber,
b.goodsname,
SUM(a.salesquantity) AS quantity, -- 统计销售数量
SUM(a.salesvalue) AS salesvalue -- 统计销售金额
FROM
demo.mytrans AS a
LEFT JOIN		-- 连接查询
demo.goodmaster AS b ON (a.itemnumber = b.itemnumber) 	-- 与商品信息表关联
GROUP BY a.transdate , a.itemnumber;	-- 按照销售日期和商品编号分组

这样一来，我们每次需要查询每日商品销售数据的时候，就可以直接查询视图，不需要再写一个复杂的关联查询语句了。

我们来试试用一个查询语句直接从视图中进行查询：

SELECT * FROM demo.trans_goodmaster

如果我们要进一步查询"每日单品销售的数量与当日的库存数量的对比”，就可以把刚刚定义的视图作为一个数据表来使用。我们把它跟历史库存表连接起来，来获取销售数量和历史库存数量。就像下面的代码这样，查询就简单多了:

SELECT
a.transdate,
a.itemnumber,
a.goodsname,
a.quantity, 		-- 获取单品销售数量
b.invquantity		-- 获取历史库存数量
FROM
demo.trans_goodmaster AS a
LEFT JOIN
demo.inventoryhist AS b
ON (a.transdate=b.invdate AND a.Itemnumber=b.Itemnumber);

结果显示，这里的查询结果和我们刚刚使用派生表的查询结果是一样的。 但是，使用视图的查询语句明显简单多了，可读性更好，也更容易维护。

2、如何操作视图和视图中的数据

创建完了视图，我们还经常需要对视图进行一些操作，比如修改、查看和删除视图。同时，我们可能还需要修改视图中的数据。

操作视图：

-- 查看视图：
DESCRIBE 视图名;
-- 删除视图
DROP VIEW 视图名;

操作视图中的数据：

视图本身是一个虚拟表， 所以，对视图中的数据进行插入、修改和删除操作，实际都是通过对实际数据表的操作来实现的。

1、在视图中插入数据
假设商品信息表中的规格字段(specification) 被删除了，当我们尝试用INSERT INTO语句向demo.view_goodmaster视图中插入一条记录的时候，就会提示错误；

这是因为，只有视图中的字段跟实际数据表中的字段完全一样，MySQL才允许通过视图插入数据。刚刚的视图中包含了实际数据表所没有的字段"specification" ，所以在插入数据时，系统就会提示错误。

2、删除视图中的数据

DELETE FROM demo.view_goodmaster
WHERE itemnumber = 5;

3、修改视图中的数据

UPDATE demo.VIEW_goodmaster
SET salesprice=100
WHERE itemnumber=1;

可以发现视图和实际数据表都别修改了，所以不建议对视图进行更新操作。有一些情况下视图时不允许被更新的：

• 视图中包含聚合函数：如果视图中包含SUM(), COUNT(), AVG(), MAX(), MIN()等聚合函数，则通常不允许直接更新。
• 视图中包含连接（JOIN）操作：当视图是由多个表通过连接操作创建时，通常不能直接更新。
• 视图中包含DISTINCT、GROUP BY、HAVING子句：这些查询元素通常也会使视图不可更新。
• 视图中包含子查询：某些数据库系统不允许直接更新包含子查询的视图。
• 视图基于不可更新的视图：如果视图是基于另一个不可更新的视图创建的，那么它本身也将是不可更新的。
• 数据库管理系统的限制：不同的数据库管理系统对视图更新的支持程度不同。例如，MySQL的视图在某些情况下是不可更新的。

3、视图的优缺点

优点：

• 我们可以把视图看成一张表来进行查询， 所以在使用视图的时候，我们不用考虑视图本身是如何获取数据的，里面有什么逻辑，包括了多少个表，有哪些关联操作，而是可以直接使用。
• 视图存储的是查询语句。所以在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源。
• 视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表。用户不需要查询数据表，可以直接通过视图获取数据表中的信息。这样既提高了数据表的安全性，同时也通过视图把用户实际需要的信息汇总在了一起， 查询起来很轻松。
• 视图的数据结构相对独立，即便实际数据表的结构发生变化，我们也可以通过修改定义视图的查询语句，让查询结果集里的字段保持不变。这样一来, 针对视图的查询就不受实际数据表结构变化的影响了。

缺点：我们在实际数据表的基础上创建了视图，如果实际数据表的结构变更了，我们就需要及时对相关的视图进行相应的维护。特别是当视图是由视图生成的时候，维护会变得比较复杂。

总结：

二、存储过程

MySQL 存储过程是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字和参数值来调用执行它。存储过程可以看作是对一系列 SQL 操作的封装和重用，它允许用户将复杂的 SQL 逻辑封装起来，通过简单的调用即可执行。

这样一来，不仅执行效率非常高，而且客户端不需要把所有的SQL语句通过网络发给服务器，减少了SQL语句暴露在网上的风险，也提高了数据查询的安全性。

存储过程的优点就是执行效率高，而且更加安全，不过，它也有着自身的缺点，那就是开发和调试的成本比较高，而且不太容易维护。

1、如何创建存储过程

CREATE PROCEDURE 存储过程名 ([IN | OUT | INOUT] 参数名称 类型) 程序体

举例：

DELIMITER //  
CREATE PROCEDURE SimpleProcedure(IN param1 INT, OUT param2 VARCHAR(255))  
BEGIN  
    -- 这里是存储过程的主体，可以包含任意的 SQL 语句  
    SET param2 = CONCAT('Hello, ', param1);  
END //  
DELIMITER ;

在上面的示例中，我们创建了一个名为 SimpleProcedure 的存储过程，它接受一个输入参数 param1（类型为 INT）和一个输出参数 param2（类型为 VARCHAR(255)）。存储过程的主体部分使用 SET 语句将输出参数的值设置为 'Hello, ’ 和输入参数的拼接结果。

在创建存储过程时，我们使用了 DELIMITER 语句来更改语句分隔符。这是因为存储过程中可能包含多条 SQL 语句，而默认的语句分隔符是分号（;）。通过更改分隔符，我们可以确保整个存储过程被视为一个单独的语句块进行解析和执行。在存储过程定义结束后，再将分隔符恢复为默认的分号。

存储过程的参数

存储过程可以有参数，也可以没有参数。一般来说，当我们通过客户端或者应用程序调用存储过程的时候，如果需要与存储过程进行数据交互，就需要设置参数。否则，就不用设置参数。

参数有3种，分别是IN、OUT和INOUT。

• IN表示输入的参数，存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是IN，表示输入参数。
• OUT表示输出的参数，存储过程在执行的过程中，把某个计算结果值赋给这个参数，执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
• INOUT表示这个参数既可以作为输入参数，又可以作为输出参数使用。

存储过程的程序体

程序体中包含的是存储过程需要执行的SQL语句，一般通过关键字BEGIN表示SQL语句的开始，通过END表示SQL语句的结束。

查看存储过程

我们可以通过SHOW CREATE PROCEDURE存储过程名称，来查看刚刚创建的存储过程：

SHOW CREATE PROCEDURE SimpleProcedure;

2、调用存储过程

要调用前面创建的 SimpleProcedure 存储过程，需要按照以下步骤进行：

• 设置会话变量：对于输出参数 param2，需要先声明一个会话变量来接收它的值。在 MySQL 中，你可以使用 @ 符号来声明用户定义的会话变量。
• 调用存储过程：使用 CALL 语句来调用存储过程，并传递必要的参数值。
• 检索输出参数的值：调用存储过程后，你可以通过查询之前设置的会话变量来获取输出参数的值。

-- 声明一个会话变量来接收输出参数的值  
SET @output_value = '';  
  
-- 调用存储过程，传入一个输入参数值，并接收输出参数的值  
CALL SimpleProcedure(123, @output_value);  
  
-- 查询会话变量的值，以检索存储过程的输出  
SELECT @output_value AS output_param;

3、修改和删除存储过程

在 MySQL 中，你不能直接修改一个已经存在的存储过程。需要先删除原有的存储过程，然后创建一个新的存储过程来替换它。这样做的原因是 SQL 本身并没有提供直接修改存储过程的语法。

-- 删除原有的存储过程：
DROP PROCEDURE IF EXISTS SimpleProcedure;

这条语句会检查 SimpleProcedure 存储过程是否存在，如果存在则删除它。IF EXISTS 是可选的，用于避免在存储过程不存在时引发错误。

-- 创建新的存储过程：
DELIMITER //  
CREATE PROCEDURE SimpleProcedure(IN param1 INT)  
BEGIN  
    -- 我们可以简单地将输入参数的值插入到某个表中
    INSERT INTO some_table (some_column) VALUES (param1);  
END //  
DELIMITER ;

-- 调用这个存储过程
CALL SimpleProcedure(123);

注意事项：

• 在执行 DROP PROCEDURE 之前，请确保没有其他数据库会话正在使用该存储过程，否则可能会导致错误。
• 修改或删除存储过程可能会影响依赖于这些过程的应用程序或脚本，因此在执行这些操作之前，最好先通知相关团队或进行充分的测试。
• 在生产环境中，对存储过程的修改和删除应该谨慎进行，并遵循适当的变更管理流程。