打印

PostgreSQL学习文档

海阔天空笑傲江�

中级会员

一星会员

Rank: 2

社区积分: 76
技术积分: 377

大中小 31楼

2007-1-2 19:55 只看该作者

5.8. 模式

一个 PostgreSQL 数据库集群包含一个或多个命名的数据库。用户和用户组在整个集群的范围内是共享的，但是其它数据并不是共享的。任何给定的与服务器的客户连接都只能访问在一个数据库里的数据，就是那个在连接请求里声明的。

注意: 一个集群的用户并不一定要有访问集群内所有数据库的权限。共享用户名的意思是不能有同名用户，也就是，在同一个集群里的两个数据库里都有叫 joe 的用户；但是系统可以配置成只允许 joe 访问某些数据库。

一个数据库包含一个或多个命名的模式，模式又包含表。模式还包含其它命名的对象，包括数据类型，函数，以及操作符。同一个对象名可以在不同的模式里使用而不会导致冲突；比如，schema1 和 myschema 都可以包含叫做 mytable 的表。和数据库不同，模式不是严格分离的：一个用户可以访问他所连接的数据库中的任意模式中的对象，只要他有权限。

我们需要模式的原因有好多：

*

   允许多个用户使用一个数据库而不会干扰其它用户。
*

   把数据库对象组织成逻辑组，让它们更便于管理。
*

   第三方的应用可以放在不同的模式中，这样它们就不会和其它对象的名字冲突。

模式类似于操作系统层次的目录，只不过模式不能嵌套。
5.8.1. 创建一个模式

要创建一个模式，使用命令 CREATE SCHEMA。给出你选择的模式名字。比如：

CREATE SCHEMA myschema;

要创建或者访问在模式中的对象，写出一个受修饰的名字，这个名字包含模式名以及表（对象名），它们之间用一个句点分开：

schema.table

（出于简化，我们将只讨论表，这个概念适用于所有其它有名对象类型，比如类型和函数。）

实际上，更一般的语法是

database.schema.table

这个语法也可以使用，但目前它只是为了和 SQL 标准形式上兼容。如果你写了一个数据库名，那么它必须和你当前连接的数据库同名。

要在新模式里创建一个表，用

CREATE TABLE myschema.mytable (
...
);

这样的方法在任何需要一个表名字的地方都可以用，包括修改表的命令和我们在下一章要讲的数据访问命令。

如果一个模式是空的（所有它里面的对象都已经删除），那么删除一个模式的命令

DROP SCHEMA myschema;

要删除一个包含所有对象的模式，使用

DROP SCHEMA myschema CASCADE;

参阅 Section 5.10 获取躲藏在这些动作背后的东西的一般机制的描述。

通常你想创建一个别人拥有的模式（因为这是一种限制你的用户在定义良好的模式中的活动的方法）。其语法如下：

CREATE SCHEMA schemaname AUTHORIZATION username;

你甚至可以省略模式名字，这时模式名将和用户名同名。参阅 Section 5.8.6 获取这种情况的适用场合。

以 pg_ 开头的模式名是保留给系统使用的，用户不能创建这样的名字。
5.8.2. Public 模式

在前面的小节里，我们没有声明任何模式名字就创建了表。缺省时，这样的表（以及其他对象）都自动放到一个叫做"public" 的模式中去了。每个新数据库都包含一个这样的模式。因此，下面的命令是等效的：

CREATE TABLE products ( ... );

和

CREATE TABLE public.products ( ... );

5.8.3. 模式搜索路径

全称的名字写起来非常费劲，并且我们最好不要在应用里直接写上特定的模式名。因此，表通常都是用未修饰的名字引用的，这样的名字里只有表名字。系统通过查找一个搜索路径来判断一个表究竟是哪个表，这个路径是一个需要查找的模式列表。在搜索路径里找到的第一个表将被当作选定的表。如果在搜索路径中没有匹配表，那么就报告一个错误，即使匹配表的名字在数据库其它的模式中存在也如此。

在搜索路径中的第一个模式叫做当前模式。除了是搜索的第一个模式之外，它还是在 CREATE TABLE 没有声明模式名的时候，新建表所在的地方。

要显示当前搜索路径，使用下面的命令：

SHOW search_path;

在缺省的设置中，返回下面的东西：

search_path
--------------
$user,public

第一个元素声明将要搜索一个和当前用户同名的模式。因为还没有这样的模式存在，所以这条记录被忽略。第二个元素指向我们已经看过的公共模式。

搜索路径中存在的第一个模式是创建新对象的缺省位置。这就是为什么缺省的对象都会创建在 public 模式里的原因。如果在任何其它环境中引用对象，而且没有用模式修饰（表修改，数据变更，或者查询命令），那么系统会遍历搜索路径，直到找到一个匹配的对象。因此，在缺省的配置里，任何未修饰的访问同样也只能引用 public 模式。

要把新的模式放到路径中来，我们用

SET search_path TO myschema,public;

（我们在这里省略了 $user 是因为我们并不是立即需要它。）然后我们就可以不用加模式修饰访问表了：

DROP TABLE mytable;

同样，因为 myschema 是路径中的第一个元素，新对象缺省时将创建在这里。

我们也可以写成

SET search_path TO myschema;

然后我们如果不明确修饰的话，就不能再访问 public 模式了。 public 模式没有任何特殊之处，只不过它缺省时就存在。我们也可以把它删除了。

又见 Section 9.19 获取其它访问模式搜索路径的方法。

搜索路径对于数据类型名，函数名以及操作符名的运作方式和表名字完全相同。数据类型和函数名可以象表名字一样加以修饰。如果你需要在表达式里写一个有修饰的操作符名字，我们有一个特殊的要求：你必须这么写

OPERATOR(schema.operator)

这样是为了避免语法歧义。下面是一个例子

SELECT 3 OPERATOR(pg_catalog.+) 4;

实际上我们通常依赖搜索路径寻找操作符，这样就不用写这么难看的东西了。
5.8.4. 模式和权限

缺省时，用户看不到模式中不属于他们所有的对象。为了让他们看得见，模式的所有者需要在模式上赋予 USAGE 权限。为了让用户使用模式中的对象，我们可能需要赋予额外的权限，只要是适合该对象的。

用户也可以允许在别人的模式里创建对象。要允许这么做，我们需要赋予在该模式上的 CREATE 权限。请注意，缺省时，每个人都在 public 模式上有 CREATE 权限。这样就允许所有可以连接到指定数据库上的用户在这里创建对象。如果你不允许这么做，你可以撤销这个权限：

REVOKE CREATE ON public FROM PUBLIC;

（第一个 "public" 是模式，第二个 "public" 意思是"所有用户"。第一句里它是个标识符，而第二句里是个关键字，所以有不同的大小写；记住我们在 Section 4.1.1 里面说过的原则。）
5.8.5. 系统表模式

除了 public 和用户创建的模式之外，每个数据库都包含一个 pg_catalog 模式，它包含系统表和所有内置数据类型，函数和操作符。pg_catalog 总是搜索路径中的一部分。如果它没有明确出现在路径中，那么它会隐含地在路径里地模式之前搜索。这样就保证了内置地名字总是可以搜索地。不过，你可以明确地把pg_catalog 放在你的搜索路径的后面，如果你想用用户定义的名字覆盖内置的名字的话。

在 PostgreSQL 版本 7.3 之前，以 pg_ 开头的表名字是保留的。这个规则现在不再是正确的了：如果必要，你可以创建这样的表名字，只要是在非系统模式里。不过，我们最好还是不要使用这样的名字，以保证自己将来不会和新版本冲突：那些版本也许会定义一些和你的表同名的表。（在缺省搜索路径中，一个对你的表的无修饰引用将解析为系统表。）系统表将继续遵循以pg_ 开头的传统，因此，只要你的表不是以pg_ 开头，就不会和无修饰的用户表名字冲突。
5.8.6. 使用方式

模式可以以多种方式组织你的数据。下面是一些建议使用的模式，它们也很容易在缺省配置中得到支持：

*

   如果你没有创建任何模式，那么所有用户隐含都访问public模式。这样就模拟了还没有模式的时候的情景。这种设置建议主要用在只有一个用户或者数据库里只有几个合作用户的情形。这样的设置也允许我们平滑地从无模式的环境过渡。
*

   你可以为每个用户创建一个模式，名字和用户相同。要记得缺省的搜索路径从$user开始，它会解析为用户名。因此，如果每个用户都有一个独立的模式，那么他们缺省时访问他们自己的模式。

   如果你使用了这样的设置，那么你可能还想撤销对public模式的访问（或者一并删除了它），因此，用户就真的限制于他们自己的模式了。
*

   要安装共享的应用（被所有人使用的表，第三方提供的额外的函数等等），我们可以把它们放到独立的模式中。只要记得给访问它们的用户赋予合适的权限就可以了。然后用户就可以通过用一个模式名修饰这些名字来使用这些额外的对象，或者他们可以把额外的模式放到他们的搜索路径中，由他们定。

5.8.7. 移植性

在 SQL 标准里，在同一个模式里的对象由不同的用户所有的概念是不存在的。而且，有些实现不允许你创建和它们的所有者不同名的模式。实际上，模式和用户的概念在那些只实现了标准中规定的基本模式支持的数据库系统里几乎是一样的。因此，许多用户考虑对名字加以修饰，使它们真正由 username.tablename组成。如果你为每个用户都创建了一个模式，这实际上就是 PostgreSQL 的行为。

同样，在 SQL 标准里也没有 public 模式的概念。为了最大限度地遵循标准，你不应该使用（可能甚至是应该删除） public 模式。

当然，有些 SQL 数据库系统可能根本没有实现模式，或者是通过允许（可能是有限制的）跨数据库访问来提供模式的支持。如果你需要在这些系统上干活，那么最大限度的移植性来自根本不用模式。

TOP

海阔天空笑傲江�

中级会员

一星会员

Rank: 2

社区积分: 76
技术积分: 377

大中小 32楼

2007-1-2 19:56 只看该作者

5.9. 其它数据库对象

在关系结构里，表是核心的对象，因为它们保存你的数据。但是它们并非存在于数据库中的唯一对象。我们可以创建许多其它类型的对象来让我们对数据的使用和管理变得更方便。我们没有在这一章里讨论这些对象，但是我们在这里会给你一个列表，这样你就知道什么是可能的。

*

   视图
*

   函数，操作符，数据类型，域
*

   触发器和重写规则

这些主题的相信信息在 Part V 里。
5.10. 依赖性追踪

如果你创建了一个包含许多表，并且带有外键约束，视图，触发器，函数等等的复杂数据库结构。那么你就会在对象之间隐含地创建了一个依赖性的网络。比如，一个带有外键约束的表依赖于它引用的表。

为了保证整个数据库结构的完整性，PostgreSQL 保证你无法删除那些还有其它对象依赖的对象。比如，试图删除我们在 Section 5.4.5 里考虑的产品表，这个时候订单表仍然依赖它，这样的删除动作是不能成功的，会有类似下面的错误信息出现：

DROP TABLE products;

NOTICE:  constraint orders_product_no_fkey on table orders depends on table products
ERROR:  Cannot drop table products because other objects depend on it
      Use DROP ... CASCADE to drop the dependent objects too

这个错误信息包含一个有用的提示：如果你不想惹来分别删除所有依赖对象的麻烦，你可以运行

DROP TABLE products CASCADE;

然后所有被依赖的对象都将被删除。在这种情况下，它并不删除订单表，它只删除外键约束。（如果你想检查 DROP ... CASCADE 会干什么，运行不带 CASCADE 的 DROP然后阅读 NOTICE 信息。）

PostgreSQL 里的所有删除命令都支持声明 CASCADE。当然，具体的依赖性实体取决于对象的类型。你也可以写 RESTRICT，而不是 CASCADE，以获取缺省的行为，这个时候限制于删除那些其它对象依赖的对象。

注意: 根据 SQL 标准，要求至少声明 RESTRICT 或者 CASCADE 中的一个。实际上没有哪种数据库系统是这么实现的，但是缺省的行为是 RESTRICT 还是 CASCADE 则因系统而异。

注意: 在 PostgreSQL 7.3 之前的外键约束依赖性和序列字段依赖性在升级过程中都不会得到维护或者创建。所有其它的依赖性类型在升级过程中都将得到恰当的创建。

TOP

海阔天空笑傲江�

中级会员

一星会员

Rank: 2

社区积分: 76
技术积分: 377

大中小 33楼

2007-1-2 19:56 只看该作者

6.1. 插入数据

在创建一个表的时候，它里面没有数据。在数据库可以有点用之前要做的第一件事就是向里面插入数据。数据在概念上是每次一行地插入的。我们当然可以每次插入多行，但是的确没有办法一次插入少于一行的数据。即使你只知道几个字段的数值，那么你也必须创建一个完整的行。

要创建一个新行，我们使用 INSERT 命令。这条命令要求提供表名字和给表中每个字段提供一个数值。比如，假设来自 Chapter 5 的产品表：

CREATE TABLE products (
product_no integer,
name text,
price numeric
);

下面是一个向表中插入一行的例子：

INSERT INTO products VALUES (1, 'Cheese', 9.99);

数据值是按照这些字段在表中出现的顺序列出的，并且用逗号分隔。通常，数据值是文本（常量），但也允许使用标量表达式。

上面的语法的缺点是你必须知道表中字段的顺序。要避免这个问题，你也可以明确地列出字段。比如，下面的两条命令都有和上面那条命令一样的效果：

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

许多用户认为明确列出字段名字是个好习惯。

如果你没有获得所有字段的数值，那么你可以省略其中的一些。这个时候，这些字段将被填充为它们的缺省数值。比如，

INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
INSERT INTO products VALUES (1, 'Cheese');

第二种形式是 PostgreSQL 的一个扩展。它从左开始填充我们给出的数值个数的字段数，其他的将是缺省。

为了保持清晰，你也可以明确要求缺省值，用于独立的字段或者用于整个行：

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
INSERT INTO products DEFAULT VALUES;

提示: 要进行"批量装载"，也就是说，插入大量数据，我们可以看看 COPY 命令。它不像 INSERT 命令那么灵活，但是更高效。

TOP

海阔天空笑傲江�

中级会员

一星会员

Rank: 2

社区积分: 76
技术积分: 377

大中小 34楼

2007-1-2 19:56 只看该作者

6.2. 更新数据

修改已经存储在数据库中的数据的行为叫做更新。你可以更新独立的行，也可以更新表中所有的行，还可以更新其中的一部分行。我们可以独立地更新每个字段，而其他的字段则不受影响。

要执行一次更新，你需要三种信息：

1.

   表的名字和要更新的字段名，
2.

   字段的新数值，
3.

   要更新的是哪行。

我们在 Chapter 5 里说过，SQL 通常并不为数据行提供唯一标识。因此我们无法直接声明需要更新哪一行。但是，我们可以通过声明一个要更新的行必须满足的条件。只有在表里面存在主键的时候（不管你叫它什么），我们才能可靠地指定一个独立的行，方法是选取一个匹配主键的行。图形化的数据库访问工具依赖这个东西来让我们可以独立地更新某些行。

比如，这条命令更新价格为5的所有产品的的价格为10：

UPDATE products SET price = 10 WHERE price = 5;

这样做可能导致零行，一行或者更多数据行被更新。如果我们试图做一个不匹配任何数据行的更新，那也不算错误。

让我们仔细看看这个命令。首先是关键字 UPDATE，然后跟着表名字。和平常一样，表名字也可以是用模式修饰的，否则就会从路径中把它找出来。然后是关键字 SET，后面跟着字段名，一个等号以及新的字段数值。新的字段数值可以是任意标量表达式，而不仅仅是常量。比如，如果你想把所有产品的价格提高 10%，你可以用：

UPDATE products SET price = price * 1.10;

如你所见，用于新值的表达式也可以引用行中现有的数值。我们还忽略了 WHERE 子句。如果我们忽略了这个子句，那么就意味着表中的所有行都要更新。如果出现了WHERE 子句，那么只有匹配它后面的条件的行被更新。请注意在 SET 子句中的等号是一个赋值，而在 WHERE 子句中的等号是比较，不过这样并不会导致任何歧义。当然条件 WHERE 不一定非得是相等测试。许多其他操作符也都可以使用（参阅 Chapter 9）。但是表达式必须得出一个布尔结果。

你还可以在一个 UPDATE 命令中更新更多的字段，方法是在 SET 子句中列出更多赋值。比如：

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

TOP

海阔天空笑傲江�

中级会员

一星会员

Rank: 2

社区积分: 76
技术积分: 377

大中小 35楼

2007-1-2 19:56 只看该作者

6.3. 删除数据

到目前为止我们已经解释了如何向表中增加数据以及如何改变数据。剩下的是讨论如何删除不再需要的数据。和前面增加数据一样，删除数据也必须是从表中整行整行地删除。在前面的一节里我们解释了 SQL不提供直接访问独立行的方法。因为，删除行只能是通过声明被删除行必须匹配的条件的情况下进行。如果你在表上有一个主键，那么你可以声明准确的行。但是你也可以删除匹配条件的行组，或者你可以一次从表中删除所有行。

我们使用 DELETE 命令删除行：它的语法和 UPDATE 命令非常类似。比如，要从产品表中删除所有价格为10的产品，用

DELETE FROM products WHERE price = 10;

如果你只是写到

DELETE FROM products;

那么表中所有行都会被删除！程序员一定要注意。

TOP

海阔天空笑傲江�

中级会员

一星会员

Rank: 2

社区积分: 76
技术积分: 377

大中小 36楼

2007-1-2 19:57 只看该作者

查询以后学习

TOP

linuxaihao

高级会员

四星会员

Rank: 5 Rank: 5

社区积分: 155
技术积分: 2100

大中小 37楼

2007-1-3 14:19 只看该作者

7.1. 概述

从数据库中检索数据的过程或命令叫做查询。在 SQL 里 SELECT 命令用于声明查询。 SELECT 命令的通用语法是

SELECT select_list FROM table_expression [sort_specification]

下面几节描述选择列表，表表达式，和排序声明的细节。

最简单的查询的形式如下

SELECT * FROM table1;

假设有一个表叫做table1，这条命令将从table1中检索所有行和所有字段。（检索的方法取决于客户端应用。比如，psql 程序将在屏幕上显示一个 ASCII 艺术构成的表格，客户端库将提供检索独立行和字段的函数。）选择列表声明 * 意味着所有表表达式提供的字段。一个选择列表也可以选择可用字段的一个子集或者在检索它们之前对字段进行计算；比如，如果table1有叫做a，b，和c的字段（可能还有其他），那么你可以用下面的查询：

SELECT a, b + c FROM table1;

（假设b和c都是数字数据类型）。参阅 Section 7.3 获取更多细节。

FROM table1 是一种非常简单的表表达式：它只读取了一个表。通常，表表达式可以是基本表，连接，和子查询的复杂构造。但你也可以省略表表达式记录而用SELECT命令当做一个计算器：

SELECT 3 * 4;

如果选择列表里的表达式返回变化的结果，那么这个东西就更有用了。比如，你可以用这个方法调用函数：

SELECT random();

TOP

linuxaihao

高级会员

四星会员

Rank: 5 Rank: 5

社区积分: 155
技术积分: 2100

大中小 38楼

2007-1-3 14:20 只看该作者

7.2. 表表达式

表表达式计算一个表。该表表达式包含一个FROM子句，该子句可以根据需要选用WHERE，GROUP BY，和HAVING 子句。大部分的表表达式只是指向磁盘上的一个表，一个所谓的基本表，但是我们可以用更复杂的表表达式以各种方法修改或组合基本表。

表表达式里的WHERE，GROUP BY，和 HAVING 子句声明一系列对源自 FROM 子句的表的转换操作。所有这些转换最后生成一个虚拟表，提供传递给选择列表计算查询输出行的数据行。
7.2.1. FROM 子句

FROM 子句从一个用逗号分隔的表引用列表中的一个或更多个其它表中生成一个表。

FROM table_reference [, table_reference [, ...]]

表引用可以是一个表名字（可能有模式修饰）或者是一个生成的表，比如子查询，一个表连接，或者这些东西的复杂组合。如果在FROM子句中列出了多于一个表，那么它们被 cross join （见下文）形成一个派生表，该表可以进行 WHERE，GROUP BY 和 HAVING 子句的转换处理，并最后生成所有表表达式的结果。

如果一个表引用是一个简单的表名字并且它是表继承级别中的超级表，那么该表的行包括所有它的后代子表的行，除非你在该表名字前面加ONLY关键字。这样的话，这个引用就只生成出现在命名表中的列 — 任何在子表中追加的列都会被忽略。
7.2.1.1. 连接表

一个连接表是根据特定的连接类型的规则从两个其它表（真实表或生成表）中派生的表。我们支持内连接，外连接和交叉连接类型。

连接类型

交叉连接

T1 CROSS JOIN T2

对每个从 T1 和 T2 来的行的组合，生成的表将包含这样一行：它包含所有 T1 里面的字段后面跟着所有 T2 里面的字段。如果两表分别有 N 和 M 行，连接成的表将有 N * M 行。

FROM T1 CROSS JOIN T2 等效于 FROM T1, T2。它还等效于 FROM T1 INNER JOIN T2 ON TRUE（见下文）。
条件连接(join)

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

INNER 和 OUTER 对所有连接（join）类型都是可选的。 INNER 是缺省；LEFT，RIGHT，和 FULL 隐含外连接。

连接条件在ON或USING子句里声明，或者用关键字NATURAL隐含地声明。连接条件判断来自两个源表中的那些行是"匹配"的，这些我们将在下面详细解释。

ON子句是最常见的连接条件的类型：它接收一个和WHERE子句里用的一样的布尔值表达式。如果两个分别来自T1和T2的行在ON表达式上运算的结果为真，那么它们就算是匹配的行。

USING是个缩写的概念：它接收一个用逗号分隔的字段名字列表，这些字段必须是连接表共有的，最终形成一个连接条件，表示这些字段对必须相同。最后，JOIN USING 的输出会为每一对相等的输入字段输出一个字段，后面跟着来自各个表的所有其它字段。因此，USING (a, b, c) 等效于 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) 只不过是如果使用了ON，那么在结果里 a，b，和 c字段每个都会有两个，而用USING的时候每个字段就只会有一个。

最后，NATURAL 是 USING 的缩写形式：它形成一个 USING 列表，该列表由那些在两个表里都出现了的字段名字组成。和USING一样，这些字段只在输出表里出现一次。

条件连接可能的类型是：

INNER JOIN

      对于 T1 的每一行 R1，生成的连接表都有一行对应 T2 中的每一个满足和 R1 的连接条件的行。
LEFT OUTER JOIN

      首先，执行一次内连接。然后，为 T1 里那些和 T2 里任何一行都不满足连接条件的行返回一个连接行，同时该连接行里对应 T2 的列用空值补齐。因此，生成的连接表里无条件地包含来自 T1 里的每一行至少一个副本。
RIGHT OUTER JOIN

      首先，执行一次内连接。然后，为 T2 里那些和 T1 里任何一行都不满足连接条件的行返回一个连接行，同时该连接行里对应 T1 的列用空值补齐。因此，生成的连接表里无条件地包含来自 T2 里的每一行。
FULL OUTER JOIN

      首先，执行一次内连接。然后，为 T1 里那些和 T2 里任何一行都不满足连接条件的行返回一个连接行，同时该连接行里对应 T2 的列用空值补齐。同样，为 T2 里那些和 T1 里的任何行都不满足连接条件的行返回一个连接行，该行里对应 T1 的列用空值补齐。

如果 T1 和 T2 有一个或者都是可以连接的表，那么所有类型的连接都可以串在一起或嵌套在一起。你可以在JOIN子句周围使用圆括弧来控制连接顺序，如果没有圆括弧，那么JOIN子句是从左向右嵌套的。

TOP

linuxaihao

高级会员

四星会员

Rank: 5 Rank: 5

社区积分: 155
技术积分: 2100

大中小 39楼

2007-1-3 14:20 只看该作者

为了解释这些问题，假设我们有一个表 t1

num | name
-----+------
1 | a
2 | b
3 | c

和 t2

num | value
-----+-------
1 | xxx
3 | yyy
5 | zzz

然后我们用不同的连接方式可以获得各种结果：

=> SELECT * FROM t1 CROSS JOIN t2;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
1 | a | 3 | yyy
1 | a | 5 | zzz
2 | b | 1 | xxx
2 | b | 3 | yyy
2 | b | 5 | zzz
3 | c | 1 | xxx
3 | c | 3 | yyy
3 | c | 5 | zzz
(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
num | name | value
-----+------+-------
1 | a | xxx
3 | c | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
num | name | value
-----+------+-------
1 | a | xxx
3 | c | yyy
(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b |    |
3 | c | 3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
num | name | value
-----+------+-------
1 | a | xxx
2 | b |
3 | c | yyy
(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
   |    | 5 | zzz
(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b |    |
3 | c | 3 | yyy
   |    | 5 | zzz
(4 rows)

用 ON 声明的连接条件也可以包含与连接不直接相关的条件。这种功能可能对某些查询很有用，但是需要我们仔细想清楚。比如：

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b |    |
3 | c |    |
(3 rows)

TOP

linuxaihao

高级会员

四星会员

Rank: 5 Rank: 5

社区积分: 155
技术积分: 2100

大中小 40楼

2007-1-3 14:21 只看该作者

7.2.1.2. 表和列别名

你可以给一个表或复杂表引用一个临时的名字，用于剩下的查询中引用那些派生的表。这样做叫做表别名。

要创建一个表别名，我们可以写：

FROM table_reference AS alias

或者

FROM table_reference alias

AS 关键字目前没啥特别的含义。 alias 可以是任意标识符。

表别名的典型应用是给长表名赋予比较短的标识符，好让连接子句更好读一些。比如：

SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;

别名成为当前查询的表引用的新名称 — 我们不再能够用该表最初的名字引用它了。因此

SELECT * FROM my_table AS m WHERE my_table.a > 5;

是不合法的 SQL 语法。这里将发生的事情（这是 PostgreSQL对标准的扩展）是在 FROM 子句里面隐含地增加了一个表引用，因此这个查询将会象下面这样处理

SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5;

这样会生成一个交叉连接，通常可不是你想要的。

圆括弧用于解决歧义．下面的语句将把别名 b 赋予连接的结果，这是和前面的例子不同的：

SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

另外一种形式的除了给表别名还给该表的字段赋予了临时名字：

FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )

如果声明的字段别名比表里实际的字段少，那么后面的字段就没有重命名．这个语法对于自连接或子查询特别有用．

如果用这些形式中的任何一种给一个JOIN子句的输出附加了一个别名，那么该别名就在JOIN里隐藏了其原始的名字。比如

SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...

是合法 SQL，但是

SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

是不合法的：表别名 a 在别名c外面是看不到的。
7.2.1.3. 子查询

声明一个派生表的子查询必须包围在圆括弧里并且必须赋予一个别名。（参阅 Section 7.2.1.2．）比如：

FROM (SELECT * FROM table1) AS alias_name

这个例子等效于 FROM table1 AS alias_name。更有趣的例子是在子查询里面有分组或聚集的时候，这个时候子查询不能归纳成一个简单的连接。
7.2.1.4. 表函数

表函数是那些生成一个行集合的函数，这个集合可以是由基本数据类型（标量类型）组成，也可以是由符合数据类型（表的行）组成。他们的用法类似一个表，视图，或者在查询的FROM子句里的子查询。表函数返回的字段可以像一个表，视图，或者子查询字段那样包含在 SELECT，JOIN，或者 WHERE 子句里。

如果一个表函数返回一个基本数据类型，那么单列的结果数据是以函数的名字命名的。如果函数返回一个复合类型，那么结果字段的名字和该类型的每个独立属性的名字相同。

表函数可以在 FROM 子句中取一个别名，但你也可以不给它别名。如果一个函数在 FROM 子句中没有别名，那么将使用函数名作为结果表的名字。

例子：

CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
WHERE foosubid IN (select foosubid from getfoo(foo.fooid) z
where z.fooid = foo.fooid);

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;

有时侯，把一个函数定义成根据不同的调用方法可以返回不同的字段是很有用的。为了支持这些，表函数可以声明为返回伪类型 record。如果在查询里使用这样的函数，那么我们必须在查询中声明预期的行结构，这样系统才知道如何分析和规划该查询。让我们看看下面的例子：

SELECT *
FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';

dblink 函数执行一个远程的查询（参阅 contrib/dblink）。它声明为返回 record，因为它可能会被用于任何类型的查询。实际的字段集必须在调用它的查询中声明，这样分析器才知道类似 * 这样的东西应该扩展成什么样子。

TOP