视图与基本表不同，是一个虚拟的表。数据库中仅存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。
学习重点:
CREATE VIEW
ALTER VIEW

0. 进入系统

su - omm
gsql -r

1. 为系统表PG_DATABASE创建视图，重命名视图并修改owner为jim,

PG_DATABASE 是系统的所有数据库的元信息, 超多参数, 值得学习.
先来看看内容吧:

\d PG_DATABASE

• 回显

 datname          | name      | not null
 datdba           | oid       | not null
 encoding         | integer   | not null
 datcollate       | name      | not null
 datctype         | name      | not null
 datistemplate    | boolean   | not null
 datallowconn     | boolean   | not null
 datconnlimit     | integer   | not null
      Table "pg_catalog.pg_database"
      Column      |   Type    | Modifiers
------------------+-----------+-----------
 datlastsysoid    | oid       | not null
 datfrozenxid     | xid32     | not null
 dattablespace    | oid       | not null
 datcompatibility | name      | not null
 datacl           | aclitem[] |
 datfrozenxid64   | xid       |
Indexes:
    "pg_database_datname_index" UNIQUE, btree (datname) TABLESPACE pg_global, tablespace "pg_
global"
    "pg_database_oid_index" UNIQUE, btree (oid) TABLESPACE pg_global, tablespace "pg_global"
Replica Identity: NOTHING
Tablespace: "pg_global"

创建视图并查看

CREATE VIEW pg_view AS SELECT * from pg_database;

• 回显

CREATE VIEW

select * from pg_view

• 回显

  datname  | datdba | encoding | datcollate  |  datctype   | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | dattablespace | datcompatibility |        datacl | datfrozenxid64
-----------+--------+----------+-------------+-------------+---------------+--------------+--
------------+---------------+--------------+---------------+------------------+--------------
--------+----------------
 template1 |     10 |        7 | en_US.UTF-8 | en_US.UTF-8 | t             | t            |
         -1 |         14176 | 0            |          1663 | PG               | {=c/omm,omm=C
Tc/omm} |           8176
 omm       |     10 |        7 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |
         -1 |         14176 | 7891         |          1663 | PG               |
        |           7891
 template0 |     10 |        7 | en_US.UTF-8 | en_US.UTF-8 | t             | f            |
         -1 |         14176 | 0            |          1663 | PG               | {=c/omm,omm=C
Tc/omm} |           7891
 postgres  |     10 |        7 | en_US.UTF-8 | en_US.UTF-8 | f             | t            |
         -1 |         14176 | 0            |          1663 | PG               |
        |           8461
(4 rows)

创建用户jim并设置为VIEW的OWNER

CREATE USER jim IDENTIFIED BY '123@Modb';
ALTER VIEW stu_v OWNER TO jim;
SELECT * FROM pg_views;

• 回显

 public             | pg_view                                | jim       | SELECT  * FROM pg_database;

2. 创建一个用户表student，并在用户表上创建视图，修改视图schema;

创建用户表student并插入数据

CREATE schema stu_s;
CREATE TABLE stu_s.student(
id integer,
name char(20),
location char(40)
);
CREATE VIEW stu_v AS select * from stu_s.student WHERE id < 100;

• 回显

CREATE VIEW

insert into stu_s.student values(12,'Act','Beijing');
insert into stu_s.student values(112,'Act','Beijing');
select * from stu_s.student;

• 回显

omm=#
 id  |         name         |                 location
-----+----------------------+------------------------------------------
  12 | Act                  | Beijing
 112 | Act                  | Beijing
(2 rows)

查看 view 的数据

select * from stu_v;

• 回显

omm=#  id |         name         |                 location
----+----------------------+------------------------------------------
 12 | Act                  | Beijing
(1 row)

3. 使用pg_views查看视图信息

select * from pg_views;

• 回显

 public             | pg_view                                | jim       | SELECT  * FROM pg_database;
 public             | stu_v                                  | omm       | SELECT  * FROM stu_s.student WHERE (student.id < 100);

4. 删除视图、表、用户

DROP VIEW pg_view;
DROP VIEW stu_v;
DROP TABLE stu_s.student;
DROP USER jim;

• 回显

DROP VIEW
DROP VIEW
DROP TABLE
DROP ROLE