MySQL 视图（2）

2024-06-01 1538阅读

上一篇：MySQL视图（1）

基于其他视图

案例对 WITH [CASCADED | LOCAL] CHECK OPTION 进行释义

MySQL 视图（2）
（图片来源网络，侵删）

创建视图时，可以基于表 / 多个表，也可以使用 其他视图+表 / 其他视图 + 其他视图 的方式进行组合。

总结

  • 更新视图，同时也会更新基表数据。更新基表，对应的视图数据也会更新。
  • WITH CASCADED CHECK OPTION 会检测当前视图（v3）WHERE 子句约束以及所有底部视图 WHERE 子句约束条件（v1/v2）
  • 当使用 CASCADED 时，不仅需要满足当前视图 WHERE 子句约束，而且无论底部视图是否有 WITH CHECK OPTION 选项，都需要满足 
    drop table t1;
drop view v1,v2,v3;
CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a  4
WITH LOCAL CHECK OPTION;
CREATE VIEW v3 AS SELECT * FROM v2 WHERE a
    • WITH [CASCADED | LOCAL] CHECK OPTION 均会检测多层视图，如下案例，插入 v3 视图数据时，会检测 v1、v2 的数据约束条件
    • with local check option 时，首先需要满足当前视图的条件，然后对于底部视图，也是先看是否有指定的 with check option 选项语句，有的话对应处理，无则不需要满足底部视图的 WHERE 子句条件。 
      drop table t1;
drop view v1,v2,v3;
CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a  4; 
CREATE VIEW v3 AS SELECT * FROM v2 WHERE a  4 
WITH LOCAL CHECK OPTION;
CREATE VIEW v3 AS SELECT * FROM v2 WHERE a  

       

      当前视图没有 check option 约束时，可以更新当前视图数据，但是在当前视图中不显示该数据（参考情况1）。同时需要保证在该视图的 select_statement 中，如果对象是视图，并且也没有 check option 约束限制（参考情况2），有也没事，但是不能限制，才可以正常插入、更新数据（参考情况3）。
       

      -- 情况 1：视图无 check option 选项，关联表
drop table t1;
drop view v1,v2,v3;
CREATE TABLE t1 (a INT);
CREATE VIEW v1 AS SELECT * FROM t1 WHERE a  4; 
CREATE VIEW v3 AS SELECT * FROM v2 WHERE a  4 
WITH CHECK OPTION; 
CREATE VIEW v3 AS SELECT * FROM v2 WHERE a  

       

      基于 WITH 通用表达式
       

      CREATE VIEW t1_view as WITH
  cte1 AS (SELECT 1 as a, 2 as b FROM dual),
  cte2 AS (SELECT 1 as c, 4 as d FROM dual)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
SELECT * FROM t1_view;
+---+---+
| b | d |
+---+---+
| 2 | 4 |
+---+---+
1 row in set (0.00 sec)
CREATE VIEW t1_view as WITH
  cte1 AS (SELECT 1 as a, 2 as b FROM dual),
  cte2 AS (SELECT 1 as c, 4 as d FROM dual)
SELECT * FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;
SELECT * FROM t1_view;
+---+---+---+---+
| a | b | c | d |
+---+---+---+---+
| 1 | 2 | 1 | 4 |
+---+---+---+---+
1 row in set (0.00 sec)
       

       

      基于 CASE WHEN
      • 直接使用不支持，但可以在 SELECT 里面 
        CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `consumer` char(20) DEFAULT NULL,
  `brand` varchar(20) DEFAULT NULL,
  `sal` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO t1 VALUES(1,'人民','苹果',5000);
INSERT INTO t1 VALUES(2,'苏教','小米',-4000);
INSERT INTO t1 VALUES(3,'深教','oppo',0);
CREATE VIEW t1_view AS SELECT id, sal, sign(CASE WHEN sal=0 THEN 200 ELSE sal END ) FROM t1 ORDER BY id;

        基于自定义存储函数

        MySQL :: MySQL 8.0 Reference Manual :: 15.1.23 CREATE VIEW Statement # create view

        单个存储函数

        DELIMITER //
CREATE FUNCTION f_test()
returns varchar(20)
BEGIN
	declare a varchar(200);
	SELECT CONCAT('111','222','333') FROM DUAL INTO a;
	return a;
END;
//
DELIMITER ;
CREATE VIEW t1_view AS SELECT f_test();

        存储函数+存储过程

        • 创建自定义函数的视图，以下案例中，不允许在过程中更新自个儿的视图，个人理解是先执行存储过程中的 UPDATE 语句，再创建视图相关数据，或者理解成，作为视图依据的这个结果集，是不允许被更新的。 
          DROP VIEW t9_view;
DROP FUNCTION f_test;
DROP PROCEDURE p_test;
DELIMITER //
CREATE FUNCTION f_test(samplename varchar(200))
RETURNS varchar(13)  
begin
	IF samplename IS NOT NULL then
	CALL p_test(1);
	END IF;
	RETURN 0;
END;
//
CREATE PROCEDURE p_test(in p_in int)
BEGIN
	UPDATE t9_view SET aa = p_in;
END;
//
DELIMITER ;
CREATE VIEW t9_view(aa) AS SELECT f_test('xxxx');
SELECT * FROM t9_view;
ERROR 1424 (HY000): Recursive stored functions and triggers are not allowed.

          • 创建自定义函数的视图，以下案例中，允许在过程中更新其他的表 / 视图 
            DROP VIEW t9_view;
DROP FUNCTION f_test;
DROP PROCEDURE p_test;
DROP TABLE t1;
CREATE TABLE t1 (id int);
INSERT INTO t1 VALUES(1);
DELIMITER //
CREATE FUNCTION f_test(samplename varchar(200))
RETURNS varchar(13)  
begin
	IF samplename IS NOT NULL then
	CALL p_test(3);
	END IF;
	RETURN 0;
END;
//
CREATE PROCEDURE p_test(in p_in int)
BEGIN
	update t1 set id=p_in;
END;
//
DELIMITER ;
CREATE VIEW t9_view(aa) AS SELECT f_test('xxxx');
SELECT * FROM t9_view;
+------+
| aa   |
+------+
| 0    |
+------+
1 row in set (0.01 sec)
mysql> SELECT * FROM t1;
+------+
| id   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

            • 视图可能依赖于存储函数，该函数可能调用其他存储例程。例如，以下视图调用存储函数f（）： 
              CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);
Suppose that f() contains a statement such as this:
IF name IS NULL then
  CALL p1();
ELSE
  CALL p2();
END IF;

              上面是官方案例，下面案例是我自己写的

              CREATE TABLE t1 (
  id int NOT NULL,
  consumer char(20) DEFAULT NULL,
  price varchar(20) DEFAULT NULL,
  sal int DEFAULT NULL,
  datetime1 DATE,
  `datetime2` bigint
);
INSERT INTO t1 VALUES(1,'书局','7000',5000,'2020-09-22',20200923121200);
INSERT INTO t1 VALUES(2,'微山','3000',-4000,'2020-09-21',20200921101159);
INSERT INTO t1 VALUES(3,'深咖','5000',0,'2020-08-21',20190821101159);
DELIMITER //
CREATE FUNCTION f_test(samplename varchar(200))
returns varchar(13)  
begin
	IF samplename IS NOT NULL then
	CALL p_test(4);
	END IF;
	RETURN 4;
END;
//
CREATE PROCEDURE p_test(in p_in int)
begin
	update t1 set id=p_in;
END;
//
DELIMITER ;
CREATE VIEW t9_view AS SELECT * FROM t1 WHERE t1.id = f_test(t1.consumer);
SELECT * FROM t9_view;
ERROR 1442 (HY000): Can't update table 't1' in stored function/trigger 
because it is already used by statement which invoked this stored function/trigger.

              总结：

              1）不支持递归存储函数，即创建自定义函数视图，在自定义函数体中调用存储过程，过程会更改该视图相关信息，参考案例 一

              2）创建自定义函数的视图，允许在过程中更新其他的表 / 视图，参考案例 二

              3）综合案例1~3来看，使用函数的方式创建视图，无论是直接 SELECT 函数 还是 以函数作为 WHERE 子句的条件来创建的视图，在函数 / 过程中都不能 INSERT / UPDATE 创建视图时的基础表（insert操作验证过，未写出来）。

              ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}

              Merge：当使用视图时，会把查询视图的语句和创建视图的语句合并起来，形成一条语句，最后再从基表中查询

              TEMPTABLE：先把定义视图的语句执行起来，把数据放在一张系统定义的临时表，然后执行用户的sql语句

              DROP TABLE t1;
CREATE TABLE t1 (c1 INT, c2 int);
insert into t1 values(3,3);
insert into t1 values(4,4);
insert into t1 values(1,1);
insert into t1 values(2,2);
DROP VIEW v_merge;
DROP VIEW v_merge2;
-- 定义为 MERGE
CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t1 WHERE c1  

               

              修改视图
               

              MySQL :: MySQL 8.0 Reference Manual :: 15.1.11 ALTER VIEW Statement
               

              ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
               

               

              select_stmt
              • 是一种 SELECT 语句。它给出了视图的定义。该语句可以从基表或其他视图进行选择。

                column_name_list

                • 视图必须具有唯一的列名，不得有重复，就像基表那样。缺省情况下，由 SELECT 语句检索的列名将用作视图列名。
                • 要想为视图列定义明确的名称，可使用可选的 column_name_list 子句，列出由逗号隔开的 column_name。
                • column_name_list 中的名称数目必须等于 SELECT 语句检索的列数。
                • SELECT 语句检索的列可以是对表列的简单引用。也可以是使用函数、常量值、操作符等的表达式。

                  删除视图

                  当前用户必须在每个视图上有 DROP 权限。

                  MySQL :: MySQL 8.0 Reference Manual :: 15.1.35 DROP VIEW Statement

                  drop_view_stmt:
    DROP VIEW [IF EXISTS] view_name_list [CASCADE | RESTRICT];
view_name_list:
    view_name [, view_name_list]

                  IF EXISTS

                  • 使用 IF EXISTS 关键字可以防止由于视图不存在而出错。

                    view_name_list

                    • 如果 view_name_list 中包含一部分存在和一部分不存在的视图，执行可能报错但是存在的视图依然会被删除。

                      CASCADE | RESTRICT

                      • CASCADE 为级联删除，自动删除依赖此视图的对象。
                        • RESTRICT 为约束删除，如果有依赖对象存在，则拒绝删除此视图。 

                          DROP VIEW v1,v2;
DROP VIEW IF EXISTS v3,v4;

                          可更新和不可更新视图

                          MySQL :: MySQL 8.0 Reference Manual :: 27.5.3 Updatable and Insertable Views

                          https://www.cnblogs.com/zhuchenglin/p/9602569.html

                          要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。还有一些其他构造使视图不可更新。更具体地说，如果视图包含以下任何内容，则该视图不可更新：

                          1. 聚合函数（SUM(), MIN(), MAX(), COUNT()等）。
                          2. DISTINCT
                          3. GROUP BY
                          4. HAVING
                          5. UNION 或 UNION ALL
                          6. select 列表中的子查询 （原话：Subquery in the select list）
                            • 选择列表中的非依赖子查询无法进行插入，但可以进行更新、删除。对于选择列表中的依赖子查询，不允许使用数据更改语句。
                            1. Join 链接
                            2. FROM子句中引用不可更新视图
                            3. WHERE子句中引用FROM子句中表的子查询
                            4. 仅引用文字值（在该情况下，没有要更新的基本表）。
                            5. ALGORITHM = TEMPTABLE（使用临时表总会使视图成为不可更新的）。
                            6. 对基表任意列的多次引用（插入会失败。更新/删除 均正常）

                            视图中生成的列被认为是可更新的，因为它可以指定给它。但是，如果显式更新了此类列，则唯一允许的值是默认值。

                            多表视图有时可能是可更新的，前提是可以使用合并算法(ALGORITHM = MERGE)进行处理。要使其工作，视图必须使用内部联接( inner join)（而不是外部联接或并集，not an outer join or a UNION）。此外，视图定义中只能更新一个表，因此SET子句只能命名视图中某个表中的列。不允许使用UNION ALL的视图，即使它们在理论上是可更新的。

                            INSERT

                            使用 INSERT 语句时，INSERT 语句中含有被合并的视图引用（视图联接视图的引用），如果是视图联接视图的引用（案例中的vjoin），则这个视图的所有组件必须都是可更新的（不是物化）。

                            对于多表组合成的可更新视图，INSERT 是可以正常插入该视图的其中一张表的。

                            CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
CREATE VIEW vup AS SELECT * FROM t2;
CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;
--此语句无效，因为联接视图的一个组件不可更新
INSERT INTO vjoin (c) VALUES (1);
-- 本声明有效；该视图不包含物化组件
INSERT INTO vup (c) VALUES (1);

                            UPDATE

                            使用 UPDATE 语句更新的，有可能是被合并的一个或多个表的视图引用。如果更新的是一个视图联接视图，则必须保证联接视图中至少一个组件（其中一个视图）是可以更新的，这里跟 INSERT 不同。

                            一个多表更新语句，语句更新表引用的必须是基表或可更新视图的引用，不可更新表的引用可能是物化视图或者派生表。

                            CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
CREATE VIEW vup AS SELECT * FROM t2;
CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;
-- 本声明有效，列c来自联接视图的可更新部分：
UPDATE vjoin SET c=c+1;
-- 本声明无效，列x来自不可更新的部分：
UPDATE vjoin SET x=x+1;
-- 本声明有效，多表更新的更新表引用是一个可更新视图（vup）：
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET c=c+1;
-- 本声明无效，它尝试更新一个物化派生表：
UPDATE vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON ...
SET s=s+1;

                            DELETE

                            单表或多表被 DELETE 语句删除，前提是必须是合并视图，联接视图则不被允许（与 INSERT / UPDATE 不同）。

                            CREATE TABLE t1 (x INTEGER);
CREATE TABLE t2 (c INTEGER);
CREATE VIEW vmat AS SELECT SUM(x) AS s FROM t1;
CREATE VIEW vup AS SELECT * FROM t2;
CREATE VIEW vjoin AS SELECT * FROM vmat JOIN vup ON vmat.s=vup.c;
--此语句无效，因为该视图是联接视图（the view is a join view）：
DELETE FROM vjoin WHERE c=1;
-- 此语句有效，因为该视图是一个被合并的（可更新）视图：
DELETE FROM vup WHERE c=2;
-- 此语句有效，因为它从一个被合并的（可更新）视图中删除：
DELETE vup FROM vup JOIN (SELECT SUM(x) AS s FROM t1) AS dt ON dt.s=vup.c;

                            如果并非所有列都是简单列引用（例如，如果视图包含表达式或复合表达式的列），则视图不可插入。虽然这样的视图是不可插入的，但如果只更新非表达式的列，则它是可更新的。考虑以下观点：

                            CREATE VIEW v AS SELECT col1, 1 AS col2 FROM t;
-- 可以更新
UPDATE v SET col1 = 0;
-- 此视图不可插入，因为col2是一个表达式。
UPDATE v SET col2=0；

VPS购买请点击我

免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们，邮箱：ciyunidc@ciyunshuju.com。本站只作为美观性配图使用,无任何非法侵犯第三方意图,一切解释权归图片著作权方,本站不承担任何责任。如有恶意碰瓷者,必当奉陪到底严惩不贷!

相关阅读

目录[+]