Mysql-视图和存储过程

视图

1.介绍

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

2.语法

1).创建

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

• CREATE VIEW：用来创建一个新视图。若同名视图已存在，就会报错。
• CREATE OR REPLACE VIEW：若同名视图已存在，就会替换掉旧视图；若不存在，就创建新视图。
• [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]：下面检测选项有解释。

2).查询

查看创建视图语句：SHOW CREATE VIEW 视图名称;
查看视图数据：SELECT * FROM 视图名称 ...... ;

3).修改

方式一：CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

4).删除

DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ... 

3.演示示例：

-- 创建视图
create or replace view stu_v_1 as select id,name from student where id <= 10;-- 查询视图
show create view stu_v_1;select * from stu_v_1;
select * from stu_v_1 where id < 3;-- 修改视图
create or replace view stu_v_1 as select id,name,no from student where id <= 10;alter view stu_v_1 as select id,name from student where id <= 10;-- 删除视图
drop view if exists stu_v_1;

上述我们演示了，视图应该如何创建、查询、修改、删除，那么我们能不能通过视图来插入、更新数据呢？ 接下来，做一个测试。

create or replace view stu_v_1 as select id,name from student where id <= 10;select * from stu_v_1;insert into stu_v_1 values(6,'Tom');insert into stu_v_1 values(17,'Tom22');

执行上述的SQL，我们会发现，id为6和17的数据都是可以成功插入的。 但是我们执行查询，查询出 来的数据，却没有id为17的记录。
因为我们在创建视图的时候，指定的条件为 id<=10, id为17的数据，是不符合条件的，所以没有查询出来，但是这条数据确实是已经成功的插入到了基表中。

如果我们定义视图时，如果指定了条件，然后我们在插入、修改、删除数据时，是否可以做到必须满足条件才能操作，否则不能够操作呢？

答案是可以的，这就需要借助于视图的检查选项了。

4.检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL ，默认值为 CASCADED 。

1.CASCADED

级联。 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。

-- 创建基于students表的视图v1，要求id小于等于20
CREATE OR REPLACE VIEW v1 AS 
SELECT id, name FROM students WHERE id <= 20; 
-- 创建基于v1的视图v2，要求id大于10，并添加cascaded检查选项
CREATE OR REPLACE VIEW v2 AS 
SELECT id, name FROM v1 WHERE id > 10 WITH CASCADED CHECK OPTION; 

此时向 v2 插入数据，需同时满足 id > 10 和 id <= 20 。

2.LOCAL

本地。 比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创 建时未指定检查选项。 则在执行检查时，知会检查v2，不会检查v2的关联视图v1。

-- 基于v1创建视图v3，要求id大于10，添加local检查选项
CREATE OR REPLACE VIEW v3 AS 
SELECT id, name FROM v1 WHERE id > 10 WITH LOCAL CHECK OPTION; 

向 v3 插入数据时，只要满足 id > 10 即可，不用考虑 v1 中 id <= 20 的条件 。

5.视图的更新

若要向视图中插入数据，视图必须满足以下条件：

• 视图必须是单表派生：视图定义必须基于单个表，不能是多表连接或复杂查询。
• 视图必须包含表中所有非空且没有默认值的列：在向视图插入数据时，必须为这些列提供值。
• 视图不能包含聚合函数（如 SUM、COUNT、AVG 等）、GROUP BY、HAVING、DISTINCT 等子句：因为这些操作会对数据进行汇总或筛选，无法明确插入到基础表的具体数据。

示例演示：

create view stu_v_count as select count(*) from student;

上述的视图中，就只有一个单行单列的数据，如果我们对这个视图进行更新或插入的，将会报错。

insert into stu_v_count values(10); # 报错！！！

6.视图作用

1.简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

2.安全

数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。

3.数据独立

视图可帮助用户屏蔽真实表结构变化带来的影响。

7.视图的保存位置

视图的定义信息会存储在 MySQL 的系统表中。

具体地说：
视图定义会存储在 INFORMATION_SCHEMA.VIEWS 表中。

举个例子：
如果你创建了一个名为 my_view 的视图，且所在数据库是 my_database：
视图的定义信息会保存在 INFORMATION_SCHEMA.VIEWS 表中，可以通过以下查询查看：

SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = 'my_database' AND TABLE_NAME = 'my_view';

在服务器文件系统中：

1. MySQL 不会为视图单独创建物理文件。
2. 视图的定义信息是以元数据的形式存储在 MySQL 数据库内部管理的系统表中。
3. 因此，视图只保存定义，而不会保存查询结果的实际数据，每次查询视图时，都会动态地从相关表中读取数据。

存储过程

1.介绍

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以减少数据在数据库和应用服务器之间的传输，提高数据处理的效率。 存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

特点：

• 封装，复用： 可以把某一业务SQL封装在存储过程中，需要用到的时候直接调用即可。
• 可以接收参数，也可以返回数据。
• 减少网络交互，效率提升：如果涉及到多条SQL，每执行一次都是一次网络传输。 而如果封装在存储过程中，我们只需要网络交互一次可能就可以了。

2.基本语法

1).创建

CREATE PROCEDURE 存储过程名称 ([ 参数列表 ])
BEGIN-- SQL语句	
END;

2).调用

CALL 名称 ([ 参数 ]); 

3).查看

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx';  -- 查询指定数据库的存储过程及状态信息,xxx：数据库名称
SHOW CREATE PROCEDURE 存储过程名称 ;  -- 查询某个存储过程的定义

4).删除

DROP PROCEDURE [ IF EXISTS ] 存储过程名称;

注意: 在命令行中，执行创建存储过程的SQL时，需要通过关键字 delim