创建 使用和删除PostgreSQL数据库的视图

使用以下的SQL命令来创建三个示例表格：

test=# CREATE TABLE stories (id INT, title VARCHAR, time TIMESTAMP);

 

test=# CREATE TABLE authors (id INT, name VARCHAR);

 

test=# CREATE TABLE stories_authors_link (story INT, author INT);

以上命令创建了三个表：一个用于小说标题、一个用于作者姓名，还有一个用于作者与小说的映射。使用列表A中的代码向表格中填充记录：

列表A：

test=# INSERT INTO authors VALUES (1, 'John Doe');

　　test=# INSERT INTO authors VALUES (2, 'James White');

　　test=# INSERT INTO authors VALUES (3, 'Ellen Sue');

　　test=# INSERT INTO authors VALUES (4, 'Gina Haggelstrom');

　　test=# INSERT INTO authors VALUES (5, 'Jane Ki');

　　test=# INSERT INTO stories VALUES

　　(100, 'All Tied Up', '2005-04-01 12:37:00');

　　test=# INSERT INTO stories VALUES

　　(112, 'Into Thin Air...', '2005-04-02 06:54:12');

　　test=# INSERT INTO stories VALUES

　　(127, 'The Oxford Blues', '2005-06-12 18:01:43');

　　test=# INSERT INTO stories VALUES

　　(128, 'Crash!', '2005-03-27 09:12:17');

　　test=# INSERT INTO stories VALUES

　　(276, 'Memories Of Malgudi', '2005-06-09 23:35:57');

　　test=# INSERT INTO stories VALUES

　　(289, 'The Big Surprise', '2005-05-30 08:21:02');

　　test=# INSERT INTO stories VALUES

　　(301, 'Indians and The Cowboy', '2005-04-16 11:19:28');

　　test=# INSERT INTO stories_authors_link VALUES (112, 2);

　　test=# INSERT INTO stories_authors_link VALUES (127, 1);

　　test=# INSERT INTO stories_authors_link VALUES (128, 5);

　　test=# INSERT INTO stories_authors_link VALUES (276, 5);

　　test=# INSERT INTO stories_authors_link VALUES (289, 3);

　　test=# INSERT INTO stories_authors_link VALUES (301, 5);

　　test=# INSERT INTO stories_authors_link VALUES (100, 1);

下一步，假设我们要获取一份关于小说及其作者的完整报告，这最好是通过连接三个表的公用字段来实现，如列表B所示：

列表B:

test=# SELECT s.title, a.name, s.time

　　test-# FROM stories AS s, authors AS a, stories_authors_link AS sa

　　test-# WHERE s.id = sa.story

　　test-# AND a.id = sa.author

　　test-# ORDER BY s.time

　　test-# DESC;

　　title|name|time

　　------------------------+-------------+---------------------

　　The Oxford Blues| John Doe| 2005-06-12 18:01:43

　　Memories Of Malgudi| Jane Ki| 2005-06-09 23:35:57

　　The Big Surprise| Ellen Sue| 2005-05-30 08:21:02

　　Indians and The Cowboy | Jane Ki| 2005-04-16 11:19:28

　　Into Thin Air...| James White | 2005-04-02 06:54:12

　　All Tied Up| John Doe| 2005-04-01 12:37:00

　　Crash!| Jane Ki| 2005-03-27 09:12:17

　　(7 rows)

　　很显然，如果一而再，再而三地输入这么长的查询是非常无效的，

   因此，将查询存储为视图是很有意义的，您可以这样做：

　　test=# CREATE VIEW myview

　　AS SELECT s.title, a.name, s.time

　　FROM stories AS s, authors AS a, stories_authors_link

　　AS sa WHERE s.id = sa.story

　　AND a.id = sa.author ORDER BY s.time DESC;

创建一个视图的语法是CREATE VIEW name AS query，这将在数据库中以name为名称来存储query字符串的查询，您可以通过dv命令来检查输出，如下所示：

test=# dv

 

List of relations

 

Schema | Name | Type | Owner

 

--------+--------+------+-------

 

public | myview | view | pgsql

 

(1 row)

如果要重复使用一个视图，可以运行一个SELECT查询，就像一个正常的表一样，如列表C所示：

列表C：

　　test=# SELECT * FROM myview;

　　title|name|time

　　------------------------+-------------+-------------

　　The Oxford Blues| John Doe| 2005-06-12 18:01:43

　　Memories Of Malgudi| Jane Ki| 2005-06-09 23:35:57

　　The Big Surprise| Ellen Sue| 2005-05-30 08:21:02

　　Indians and The Cowboy | Jane Ki| 2005-04-16 11:19:28

　　Into Thin Air...| James White | 2005-04-02 06:54:12

　　All Tied Up| John Doe| 2005-04-01 12:37:00

　　Crash!| Jane Ki| 2005-03-27 09:12:17

　　(7 rows)

如列表C所示：从视图中进行选择实际上运行了原有的存储查询，很自然地，您可以在SELECT语句中使用SQL操作符来操作一个视图的输出，可以参考列表D中的示例。

列表D：

　　test=# SELECT title, name FROM myview LIMIT 3;

　　title|name

　　---------------------+-----------

　　The Oxford Blues| John Doe

　　Memories Of Malgudi | Jane Ki

　　The Big Surprise| Ellen Sue

　　(3 rows)

　　验证原有的视图已经不存在可以通过dv命令的输出来检查：

　　test=# dv

　　No relations found.

注释：与以上的例子相同，视图提供了一个简便快捷的方式来完成经常使用的SELECT查询，而且还可以简单地获取相同数据的不同视角。