创建和管理视图
背景信息
当用户需要组合数据库中一张或多张表的某些字段的数据，而又不想每次键入这些查询时，就可以定义一个视图，以解决这个问题。
视图与基本表不同，不是物理上实际存在的，是一个虚表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。若基本表中的数据发生变化，从视图中查询出的数据也随之改变。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据及变化。视图每次被引用的时候都会运行一次。
功能描述
VexDB 数据库对视图的管理包含如下功能：
- 创建视图。
- 查询视图。
- 对简单视图进行插入、更新和删除数据的操作。
- 支持视图基表更新时自动刷新视图，在查询一次视图后刷新视图定义。支持场景如下：
- 基表增加字符串类型列的宽度。
- 修改基表列类型。
- 在基表删除列后重新添加被删除的列。
说明- 当视图中SELECT的目标不是基表而是表达式时，如果基表字段类型修改导致表达式结果类型变化，视图不会自动刷新。
- 基表删除字段后导出数据，导入数据后在基表添加被删除字段，此场景视图无法自动刷新。
- 使用SELECT * 创建的视图，在基表变更后，使用\d+命令查看视图的定义时会出错。
- 使用SELECT * 创建的视图不支持自动刷新。使用SELECT * 创建的视图在基表变更后，使用SELECT查询视图将自动重新编译，重新编译后的视图targetlist会变化。不建议使用SELECT * 创建视图。
- 支持对视图依赖的对象进行DDL操作，除上述视图自动刷新场景外，对象修改后视图变为Invalid状态，需重新编译或重新创建视图。支持的对象如下：
- 表、分区表
- 视图
- 函数
- 类型
- 操作符
注意事项
支持将无效视图导出为FORCE VIEW，因FORCE VIEW的局限性，可能导致复杂的无效视图导入失败。
示例
示例1： 创建视图
- 创建测试表，并向表中插入数据。
CREATE TABLE t_normal2(id INT, col_varying CHARACTER VARYING(30), col_varchar VARCHAR(30), col_character CHARACTER(30), col_char CHAR(30), col_text TEXT, col_name NAME); INSERT INTO t_normal2 (id, col_varying, col_varchar, col_character, col_char, col_text, col_name) VALUES(1,'测试abc123','测试abc123','测试abc123','测试abc123','测试abc123','NAME');
当CREATE VIEW中存在OR REPLACE时，表示若以前存在该视图就进行替换，但新查询不能改变原查询的列定义，包括顺序、列名、数据类型、类型精度等，只可在列表末尾添加其他的列。 - 创建视图。
CREATE OR REPLACE VIEW v_normal2 AS SELECT * FROM t_normal2; - 清理测试数据。
DROP TABLE t_normal2 CASCADE;
示例2： 查询视图
- 创建视图。
CREATE TABLE t_normal2(id INT, col_varying CHARACTER VARYING(30), col_varchar VARCHAR(30), col_character CHARACTER(30), col_char CHAR(30), col_text TEXT, col_name NAME); INSERT INTO t_normal2 (id, col_varying, col_varchar, col_character, col_char, col_text, col_name) VALUES(1,'测试abc123','测试abc123','测试abc123','测试abc123','测试abc123','NAME'); CREATE OR REPLACE VIEW v_normal2 AS SELECT * FROM t_normal2; - 查询视图。
SELECT * FROM t_normal2;
返回结果如下：id | col_varying | col_varchar | col_character | col_char | col_text | col_name ----+-------------+-------------+------------------------------+------------------------------+------------+---------- 1 | 测试abc123 | 测试abc123 | 测试abc123 | 测试abc123 | 测试abc123 | NAME (1 row) - 查看视图定义。
\d+ t_normal2;
返回结果如下：Table "public.t_normal2" Column | Type | Modifiers | Storage | Stats target | Description --------------+---------------+-----------+----------+--------------+------------- id | integer | | plain | | col_varying | varchar(30) | | extended | | col_varchar | varchar(30) | | extended | | col_character | character(30) | | extended | | col_char | character(30) | | extended | | col_text | text | | extended | | col_name | name | | plain | | Has OIDs: no Options: orientation=row, compression=no, fillfactor=80 - 清理测试数据。
DROP TABLE t_normal2 CASCADE;
示例3： 向视图中插入数据
- 创建视图。
CREATE TABLE t_normal2(id INT, col_varying CHARACTER VARYING(30), col_varchar VARCHAR(30), col_character CHARACTER(30), col_char CHAR(30), col_text TEXT, col_name NAME); INSERT INTO t_normal2 (id, col_varying, col_varchar, col_character, col_char, col_text, col_name) VALUES(1,'测试abc123','测试abc123','测试abc123','测试abc123','测试abc123','NAME'); CREATE OR REPLACE VIEW v_normal2 AS SELECT * FROM t_normal2; - 向视图中插入数据。
INSERT INTO v_normal2(id,col_varying,col_varchar,col_character,col_char,col_text,col_name) VALUES(2,'测试abc123','测试abc123','测试abc123','测试abc123','测试abc123','NAME'); SELECT * FROM t_normal2;
返回结果如下：id | col_varying | col_varchar | col_character | col_char | col_text | col_name ----+-------------+-------------+------------------------------+------------------------------+------------+---------- 1 | 测试abc123 | 测试abc123 | 测试abc123 | 测试abc123 | 测试abc123 | NAME 2 | 测试abc123 | 测试abc123 | 测试abc123 | 测试abc123 | 测试abc123 | NAME (2 rows) - 清理测试数据。
DROP TABLE t_normal2 CASCADE;
示例4： 更新视图数据
- 创建视图。
CREATE TABLE t_normal2(id INT, col_varying CHARACTER VARYING(30), col_varchar VARCHAR(30), col_character CHARACTER(30), col_char CHAR(30), col_text TEXT, col_name NAME); INSERT INTO t_normal2 (id, col_varying, col_varchar, col_character, col_char, col_text, col_name) VALUES(1,'测试abc123','测试abc123','测试abc123','测试abc123','测试abc123','NAME'); INSERT INTO t_normal2(id, col_varying, col_varchar, col_character, col_char, col_text, col_name) VALUES(2,'测试abc123','测试abc123','测试abc123','测试abc123','测试abc123','NAME'); SELECT * FROM t_normal2; CREATE OR REPLACE VIEW v_normal2 AS SELECT * FROM t_normal2; - 更新视图数据。
UPDATE v_normal2 SET col_varying = '更新def456'; SELECT * FROM t_normal2;
返回结果如下：id | col_varying | col_varchar | col_character | col_char | col_text | col_name ----+-------------+-------------+------------------------------+------------------------------+------------+---------- 1 | 更新def456 | 测试abc123 | 测试abc123 | 测试abc123 | 测试abc123 | NAME 2 | 更新def456 | 测试abc123 | 测试abc123 | 测试abc123 | 测试abc123 | NAME (2 rows) - 清理测试数据。
DROP TABLE t_normal2 CASCADE;
示例5： 删除视图数据
- 创建视图。
CREATE TABLE t_normal2(id INT, col_varying CHARACTER VARYING(30), col_varchar VARCHAR(30), col_character CHARACTER(30), col_char CHAR(30), col_text TEXT, col_name NAME); INSERT INTO t_normal2 (id, col_varying, col_varchar, col_character, col_char, col_text, col_name) VALUES(1,'测试abc123','测试abc123','测试abc123','测试abc123','测试abc123','NAME'); INSERT INTO t_normal2(id, col_varying, col_varchar, col_character, col_char, col_text, col_name) VALUES(2,'测试abc123','测试abc123','测试abc123','测试abc123','测试abc123','NAME'); SELECT * FROM t_normal2; CREATE OR REPLACE VIEW v_normal2 AS SELECT * FROM t_normal2; - 删除视图数据。
DELETE FROM v_normal2 WHERE id = 1; SELECT * FROM v_normal2; SELECT * FROM t_normal2;
返回结果如下：id | col_varying | col_varchar | col_character | col_char | col_text | col_name ----+-------------+-------------+------------------------------+------------------------------+------------+---------- 2 | 更新def456 | 测试abc123 | 测试abc123 | 测试abc123 | 测试abc123 | NAME (1 row) id | col_varying | col_varchar | col_character | col_char | col_text | col_name ----+-------------+-------------+------------------------------+------------------------------+------------+---------- 2 | 更新def456 | 测试abc123 | 测试abc123 | 测试abc123 | 测试abc123 | NAME (1 row) - 清理测试数据。
DROP TABLE t_normal2 CASCADE;
示例6： 删除视图
- 创建视图。
CREATE TABLE t_normal2(id INT, col_varying CHARACTER VARYING(30), col_varchar VARCHAR(30), col_character CHARACTER(30), col_char CHAR(30), col_text TEXT, col_name NAME); INSERT INTO t_normal2 (id, col_varying, col_varchar, col_character, col_char, col_text, col_name) VALUES(1,'测试abc123','测试abc123','测试abc123','测试abc123','测试abc123','NAME'); INSERT INTO t_normal2(id, col_varying, col_varchar, col_character, col_char, col_text, col_name) VALUES(2,'测试abc123','测试abc123','测试abc123','测试abc123','测试abc123','NAME'); SELECT * FROM t_normal2; CREATE OR REPLACE VIEW v_normal2 AS SELECT * FROM t_normal2; - 删除视图。
DROP VIEW v_normal2; - 清理测试数据。
DROP TABLE t_normal2;
示例7： 基表数据长度修改后，视图更新定义。
- 创建测试表。
CREATE TABLE t1( c1 int, c2 char(20), c3 varchar(20), c4 varchar2(30), c5 nvarchar(20), c6 nvarchar2(20), c7 character(10), c8 character varying(30) ); - 创建基于 t1 表的视图 view_1_1。
CREATE VIEW view_1_1 AS SELECT * FROM t1; - 查看视图定义。
\d+ view_1_1;
返回结果如下：View "public.view_1_1" Column | Type | Modifiers | Storage | Description | Attalias --------+---------------+-----------+----------+-------------+---------- c1 | integer | | plain | | c2 | character(20) | | extended | | c3 | varchar(20) | | extended | | c4 | varchar(30) | | extended | | c5 | nvarchar2(20) | | extended | | c6 | nvarchar2(20) | | extended | | c7 | character(10) | | extended | | c8 | varchar(30) | | extended | | View definition: SELECT * FROM t1; - 修改基表类型长度。
ALTER TABLE t1 MODIFY c2 char(30); ALTER TABLE t1 MODIFY c3 varchar(30); ALTER TABLE t1 MODIFY c4 varchar2(20); ALTER TABLE t1 MODIFY c5 nvarchar(30); ALTER TABLE t1 MODIFY c6 nvarchar2(30); ALTER TABLE t1 MODIFY c7 character(20); ALTER TABLE t1 MODIFY c8 character varying(20); - 查看视图定义。
\d+ view_1_1;
返回结果如下：View "public.view_1_1" Column | Type | Modifiers | Storage | Description | Attalias --------+---------------+-----------+----------+-------------+---------- c1 | integer | | plain | | c2 | character(20) | | extended | | c3 | varchar(20) | | extended | | c4 | varchar(30) | | extended | | c5 | nvarchar2(20) | | extended | | c6 | nvarchar2(20) | | extended | | c7 | character(10) | | extended | | c8 | varchar(30) | | extended | | View definition: SELECT * FROM t1;
查询到的视图定义与修改表前的定义一致，这是由于在修改表后，需要在视图上执行一次查询才能够刷新视图。 - 查询视图。
SELECT * FROM view_1_1; - 重新查看视图定义。
\d+ view_1_1;
返回结果如下：View "public.view_1_1" Column | Type | Modifiers | Storage | Description | Attalias --------+---------------+-----------+----------+-------------+---------- c1 | integer | | plain | | c2 | character(30) | | extended | | c3 | varchar(30) | | extended | | c4 | varchar(20) | | extended | | c5 | nvarchar2(30) | | extended | | c6 | nvarchar2(30) | | extended | | c7 | character(20) | | extended | | c8 | varchar(20) | | extended | | View definition: SELECT t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8 FROM t1; - 清理测试数据。
DROP TABLE t1; DROP VIEW view_1_1;
