创建和管理视图

背景信息

当用户需要数据库中一张或多张表的某些字段的数据组合，而又不想每次键入这些查询时，用户就可以定义一个视图，以便解决这个问题。

视图与基本表不同，不是物理上实际存在的，是一个虚表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。若基本表中的数据发生变化，从视图中查询出的数据也随之改变。从这个意义上讲，视图就像一个窗口，透过它可以看到数据库中用户感兴趣的数据及变化。视图每次被引用的时候都会运行一次。

功能描述

Vastbase对视图的管理包含如下功能：

• 创建视图。
• 查询视图。
• 对视图进行插入、更新和删除。

示例

• 创建视图

  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;
  

  

  当CREATE VIEW中存在OR REPLACE时，表示若以前存在该视图就进行替换，但新查询不能改变原查询的列定义，包括顺序、列名、数据类型、类型精度等，只可在列表末尾添加其他的列。

• 查询视图

  SELECT * FROM t_normal2; 
  \d+ t_normal2
  

  返回结果如下：

   id | col_varying | col_varchar |        col_character         |           col_char           |  col_text  | col_name
  ----+-------------+-------------+------------------------------+------------------------------+------------+----------
  1 | 测试abc123  | 测试abc123  | 测试abc123                   | 测试abc123                   | 测试abc123 | NAME
  (1 row)
  
      
                           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
  

• 向视图中插入数据

  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)
  

• 更新视图数据

  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)
  

• 删除视图数据

  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 VIEW v_normal2;