用视图自定义PostgreSQL数据库查询
欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入 PostgreSQL的一个主要特点就是创建自定义“视图”,这些视图仅仅是预先定义的SQL查询,它们存储在数据库中并可以在需要时重复使用。因此,以这种方式储存经常使用的SQL查询比每次都手工输入要更有效
欢迎进入Oracle社区论坛,与200万技术人员互动交流 >>进入
PostgreSQL的一个主要特点就是创建自定义“视图”,这些视图仅仅是预先定义的SQL查询,它们存储在数据库中并可以在需要时重复使用。因此,以这种方式储存经常使用的SQL查询比每次都手工输入要更有效率而且更加灵活,因为通过视图生成的数据集本身就可以通过SQL来操作。
这篇文章主要介绍了如何创建、使用和删除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查询,而且还可以简单地获取相同数据的不同视角。