2.4. 向表中添加行

INSERT 用于向表中添加行：

INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');

请注意所有数据类型都使用了相当明了的输入格式。 那些不是简单数字值的常量必需用单引号(')包围， 就象在例子里一样。 date 类型实际上对可接收的格式相当灵活， 不过在本教程里，我们应该坚持使用这里显示的格式。

point 类型要求一个座标对作为输入，如下：

INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');

到目前为止使用的语法要求你记住字段的顺序。一个可选的语法允许你明确地列出字段：

INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

如果你需要，你可以用另外一个顺序列出字段或者是忽略某些字段， 比如说，我们不知道降水量：

INSERT INTO weather (date, city, temp_hi, temp_lo)
    VALUES ('1994-11-29', 'Hayward', 54, 37);

许多开发人员认为明确列出字段要比依赖隐含的顺序是更好的风格。

请输入上面显示的所有命令，这样你在随后的各节中才有可用的数据。

你还可以使用 COPY 从文本文件中装载大量数据。 这么干通常更快，因为 COPY 命令就是为这类应用优化的， 只是比 INSERT 少一些灵活性．比如：

COPY weather FROM '/home/user/weather.txt';

这里源文件的文件名必须是后端服务器可访问的， 而不是客户端可访问的，因为后端服务器直接读取文件。 你可以在 COPY 中读到更多有关 COPY 命令的信息

2.5. 查询一个表

要从一个表中检索数据就是查询这个表。 SQL 的 SELECT 就是做这个用途的。 该语句分为选择列表（列出要返回的字段部分），表列表（列出从中检索数据的表的部分）， 以及可选的条件（声明任意限制的部分）。比如，要检索表 weather 的所有行，键入：

SELECT * FROM weather;

这里 * 是"所有字段" 的缩写。 [1] 因此同样的结果可以用下面语句获得：

SELECT city, temp_lo, temp_hi, prcp, date FROM weather;

而输出应该是：

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
San Francisco |      46 |      50 | 0.25 | 1994-11-27
San Francisco |      43 |      57 |    0 | 1994-11-29
Hayward       |      37 |      54 |      | 1994-11-29
(3 rows)

你可以在选择列表中写任意表达式，而不仅仅是字段列表。比如，你可以：

SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;

这样应该得出：

     city      | temp_avg |    date
---------------+----------+------------
San Francisco |       48 | 1994-11-27
San Francisco |       50 | 1994-11-29
Hayward       |       45 | 1994-11-29
(3 rows)

请注意这里的 AS 子句是如何给输出字段重新命名的。（AS 子句是可选的。）

一个查询可以使用 WHERE 子句"修饰"，声明需要哪些行。 WHERE 子句包含一个布尔（真值）表达式，只有那些布尔表达式为真的行才会被返回。 允许你在条件中使用常用的布尔操作符（AND，OR， 和 NOT）。 比如，下面的查询检索旧金山的下雨天的天气：

SELECT * FROM weather
    WHERE city = 'San Francisco' AND prcp > 0.0;

结果：

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
San Francisco |      46 |      50 | 0.25 | 1994-11-27
(1 row)

你可以要求返回的查询是排好序的：

SELECT * FROM weather
    ORDER BY city;

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
Hayward       |      37 |      54 |      | 1994-11-29
San Francisco |      43 |      57 |    0 | 1994-11-29
San Francisco |      46 |      50 | 0.25 | 1994-11-27

在这个例子里，排序的顺序并非绝对清晰的，因此你可能看到 San Francisco 行随机的排序。 但是如果你使用下面的语句，那么就总是会得到上面的结果

SELECT * FROM weather
    ORDER BY city, temp_lo;

你可以要求查询的结果按照某种顺序排序， 并且消除重复的行输出：

SELECT DISTINCT city
    FROM weather;

     city
---------------
Hayward
San Francisco
(2 rows)

再次声明，结果行的顺序可能是随机的。 你可以组合使用 DISTINCT 和 ORDER BY 来获取一致的结果： [2]

SELECT DISTINCT city
    FROM weather
    ORDER BY city;

Notes
[1]       

虽然 SELECT * 对于把查询的结果翻出来很有用， 我们普遍认为在生产代码中这是很糟糕的风格，因为给表增加一个字段就改变了结果。
[2]       

在一些数据库系统里，包括老版本的 PostgreSQL， DISTINCT 的实现自动对行进行排序，因此 ORDER BY 是多余的。但是这一点并不是 SQL 标准的要求，并且目前的 PostgreSQL 并不保证 DISTINCT 导致数据行被排序。

2.6. 在表之间连接

到目前为止，我们的查询一次只访问了一个表。 查询可以一次访问多个表，或者用某种方式访问一个表，而同时处理该表的多个行。 一个同时访问同一个或者不同表的多个行的查询叫连接（join）查询。 举例来说，比如你想列出所有天气记录以及这些记录相关的城市。 要实现这个目标，我们需要拿 weather表每行的city 字段和cities表所有行的name字段进行比较， 并选取那些这些数值相匹配的行。

    注意: 这里只是一个概念上的模型。改连接通常以比实际比较每个可能的行配对更高效的方式执行， 但这些是用户看不到的。

这个任务可以用下面的查询来实现：

SELECT *
    FROM weather, cities
    WHERE city = name;

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)

观察结果集的两个方面：

    *

      没有城市Hayward的结果行。这是因为在 cities 表里面没有 Hayward的匹配行，所以连接忽略 weather表里的不匹配行。我们稍后将看到如何修补这个毛病。
    *

      有两个字段包含城市名字。这是正确的， 因为 weather 和 cities 表的字段是接在一起的。不过，实际上我们不想要这些， 因此你将可能希望明确列出输出字段而不是使用 *：

      SELECT city, temp_lo, temp_hi, prcp, date, location
          FROM weather, cities
          WHERE city = name;

练习：. 看看省略 WHERE 子句的语义是什么。

因为这些字段的名字都不一样，所以分析器自动找出它们属于哪个表， 但是在连接查询里使用字段全称是很好的风格：

SELECT        weather.city, weather.temp_lo, weather.temp_hi,
        weather.prcp, weather.date, cities.location
    FROM weather, cities
    WHERE cities.name = weather.city;

到目前为止，这种类型的连接查询也可以用下面这样的形式写出来：

SELECT *
    FROM weather INNER JOIN cities ON (weather.city = cities.name);

这个语法并非象上面那个那么常用，我们在这里写出来是为了让你更容易了解后面的主题。

现在我们将看看如何能把Hayward记录找回来。 我们想让查询干的事是扫描 weather 表， 并且对每一行都找出匹配的 cities 表里面的行。 如果我们没有找到匹配的行，那么我们需要一些"空值"代替cities表的字段。 这种类型的查询叫外连接。 （我们在此之前看到的连接都是内部连接。）这样的命令看起来象这样：

SELECT *
    FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
Hayward       |      37 |      54 |      | 1994-11-29 |               |
San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(3 rows)

这个查询是一个左手边外连接， 因为在连接操作符（译注∶LEFT OUTER JOIN）左手边的表中的行在输出中至少要出现一次， 而在右手边的行将只输出那些与左手边行有对应匹配的行。 如果输出的左手边表的行没有对应匹配的右手边表的行，那么在右手边行的字段将填充空（NULL）。

练习：. 还有右连接和全连接。试着找出来它们能干什么。

我们也可以把一个表和自己连接起来。这叫做自连接。 比如，假设我们想找出那些在其它天气记录的温度范围之外的天气记录。 这样我们就需要拿 weather 表里每行的 temp_lo 和 temp_hi 字段与 weather 表里其它行的 temp_lo 和 temp_hi 字段进行比较。我们可以用下面的查询实现这个目标：

SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
    FROM weather W1, weather W2
    WHERE W1.temp_lo < W2.temp_lo
    AND W1.temp_hi > W2.temp_hi;

     city      | low | high |     city      | low | high
---------------+-----+------+---------------+-----+------
San Francisco |  43 |   57 | San Francisco |  46 |   50
Hayward       |  37 |   54 | San Francisco |  46 |   50
(2 rows)

在这里我们把weather表重新标记为 W1 和 W2 以区分连接的左手边和右手边。 你还可以用这样的别名在其它查询里节约一些敲键，比如：

SELECT *
    FROM weather w, cities c
    WHERE w.city = c.name;

你以后会经常碰到这样的缩写的．

2.7. 聚集函数

和大多数其它关系数据库产品一样， PostgreSQL 支持聚集函数。 一个聚集函数从多个输入行中计算出一个结果。 比如，我们有在一个行集合上计算 count（数目）， sum（和），avg（均值）， max（最大值）和min（最小值）的函数。

比如，我们可以用下面的语句找出所有记录中低温中的最高温度

SELECT max(temp_lo) FROM weather;

max
-----
  46
(1 row)

如果我们想知道该读数发生在哪个城市，我们可以用

SELECT city FROM weather WHERE temp_lo = max(temp_lo);     WRONG

不过这个方法不能运转，因为聚集 max 不能用于 WHERE 子句中。 （存在这个限制是因为 WHERE 子句决定哪些行可以进入聚集阶段；因此它必需在聚集函数之前计算。） 不过，我们通常都可以用其它方法实现我们的目的；这里我们就可以使用子查询：

SELECT city FROM weather
    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

     city
---------------
San Francisco
(1 row)

这样做是 OK 的，因为子查询是一次独立的计算，它独立于外层的查询计算出自己的聚集。

聚集同样也常用于 GROUP BY 子句。比如， 我们可以获取每个城市低温的最高值

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city;

     city      | max
---------------+-----
Hayward       |  37
San Francisco |  46
(2 rows)

这样给我们每个城市一个输出。 每个聚集结果都是在匹配该城市的行上面计算的。 我们可以用 HAVING 过滤这些分组：

SELECT city, max(temp_lo)
    FROM weather
    GROUP BY city
    HAVING max(temp_lo) < 40;

  city   | max
---------+-----
Hayward |  37
(1 row)

这样就只给出那些 temp_lo 数值曾经有低于 40 度温度的城市。 最后，如果我们只关心那些名字以 "S" 开头的城市，我们可以用

SELECT city, max(temp_lo)
    FROM weather
    WHERE city LIKE 'S%'(1)
    GROUP BY city
    HAVING max(temp_lo) < 40;

(1)
    LIKE 做模式匹配，在 Section 9.7 里有解释。

理解聚集和SQL的 WHERE 以及 HAVING 子句之间的关系对我们非常重要。 WHERE 和 HAVING 的基本区别如下： WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而 HAVING 在分组和聚集之后选取分组的行。 因此，WHERE 子句不能包含聚集函数； 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。 相反，HAVING 子句总是包含聚集函数。 （严格说来，你可以写不使用聚集的 HAVING 子句， 但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。）

在前面的例子里，我们可以在 WHERE 里应用城市名称限制，因为它不需要聚集。 这样比在 HAVING 里增加限制更加高效，因为我们避免了为那些未通过 WHERE 检查的行进行分组和聚集计算。

2.8. 更新

你可以用 UPDATE 命令更新现有的行。 假设你发现所有 11 月 28 日的温度计数都低了两度，那么你就可以用下面的方式更新数据：

UPDATE weather
    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
    WHERE date > '1994-11-28';

看看数据的新状态：

SELECT * FROM weather;

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
San Francisco |      46 |      50 | 0.25 | 1994-11-27
San Francisco |      41 |      55 |    0 | 1994-11-29
Hayward       |      35 |      52 |      | 1994-11-29
(3 rows)
2.9. 删除

数据行可以用 DELETE 命令从表中删除。 假设你对Hayward的天气不再感兴趣，那么你可以用下面的方法把那些行从表中删除：

DELETE FROM weather WHERE city = 'Hayward';

所有属于Hayward的天气记录都将被删除。

SELECT * FROM weather;

     city      | temp_lo | temp_hi | prcp |    date
---------------+---------+---------+------+------------
San Francisco |      46 |      50 | 0.25 | 1994-11-27
San Francisco |      41 |      55 |    0 | 1994-11-29
(2 rows)

我们用下面形式的语句的时候一定要小心

DELETE FROM tablename;

如果没有条件，DELETE 将从指定表中删除所有行，把它清空。做这些之前系统不会请求你确认！

3.1. 介绍

在前面几章里，我们介绍了使用 SQL 存储和访问在 PostgreSQL 里的数据的基本方法。 我们现在将讨论一些 SQL 更高级的特性，这些特性可以简化管理和避免你的数据的丢失或损坏。 最后，我们将看看一些 PostgreSQL 的扩展。

本章将不时引用在 Chapter 2 里的例子， 并且对它们进行修改和提高，因此如果你已经看过那章会更好。 本章的一些例子也可以在教程目录里的 advanced.sql 文件里找到。 这个文件还包括一些要装载的例子数据，这些数据没有在这里介绍。 （请参考 Section 2.1 获取如何使用该文件的方法。）
3.2. 视图

回头看看在 Section 2.6 里的查询。 假设你的应用对天气记录和城市位置的组合列表特别感兴趣， 而你又不想每次键入这些查询。那么你可以在这个查询上创建一个视图， 它给这个查询一个名字，你可以像普通表那样引用它。

CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;

SELECT * FROM myview;

自由地运用视图是好的 SQL 数据库设计的一个关键要素。 视图允许我们把表结构的细节封装起来，这些表可能因你的应用的进化而变化， 而这些变化却可以躲在一个一致的接口后面。

视图几乎可以在一个真正的表可以使用的任何地方使用。 在其它视图上面再建造视图也并非罕见。

3.3. 外键

回忆一下 Chapter 2 里的 weather 和 cities 表。考虑一下下面的问题：你想确保没有人可以在 weather 表里插入一条在 cities 表里没有匹配记录的数据行。 这就叫维护你的表的参考完整性。 在简单的数据库系统里，实现（如果也叫实现）这个特性的方法 通常是先看看 cities 表里是否有匹配的记录， 然后插入或者拒绝新的 weather 记录。 这个方法有许多问题，而且非常不便，因此 PostgreSQL 可以为你做这些。

新的表声明看起来会象下面这样：

CREATE TABLE cities (
        city            varchar(80) primary key,
        location        point
);

CREATE TABLE weather (
        city            varchar(80) references cities(city),
        temp_lo         int,
        temp_hi         int,
        prcp            real,
        date            date
);

然后我们试图插入一条非法的记录：

INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');

ERROR:  insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL:  Key (city)=(Berkeley) is not present in table "cities".

外键的行为可以为你的应用仔细调节。在这份教程里我们就不再多说了，而是请你参考Chapter 5获取更多的信息。 正确使用外键无疑将改进你的数据库应用，所以我们强烈建议你学习它们。
3.4. 事务

事务是所有数据库系统的一个基本概念。 一次事务的要点就是它把多个步骤捆绑成了一个单一的，不成功则成仁的操作。 其它并发的事务是看不到在这些步骤之间的中间状态的，并且如果发生了一些问题， 导致该事务无法完成，那么所有这些步骤都完全不会影响数据库。

比如，假设一个银行的数据库包含各种客户帐户的余额，以及每个分行的总余额。 假设我们要记录一次从 Alice 的帐户到 Bob 的帐户的金额为 $100.00 的支付动作。那么，完成这个任务的简单到极点的 SQL 命令象下面这样

UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
    WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');

这些命令的细节在这儿并不重要；重要的是这里牵涉到了好几个独立的更新来完成这个相当简单的操作。 我们的银行官员会希望要么所有这些更新都生效，要么全部不起作用。 我们当然不希望一次系统崩溃就导致 Bob 收到 100 块不是 Alice 支付的钱， 也不希望 Alice 老是不花钱从 Bob 那里拿到物品。我们需要保证：如果在操作的过程中出了差错， 那么所有这些步骤都不会发生效果。把这些更新组合成一个事务就给予我们这样的保证。 事务被认为是原子的：从其它事务的角度来看，它要么是全部发生，要么完全不发生。

我们还需要保证：一旦一个事务完成并且得到数据库系统的认可， 那么它必须被真正永久地存储，并且不会在随后的崩溃中消失。 比如，如果我们记录到了一个 Bob 撤单的动作， 那么我们不希望仅仅在他走出银行大门之后的一次崩溃就会导致对他的帐户的扣减动作消失。 一个事务型数据库保证一个事务所做的所有更新在事务发出完成响应之前都记录到永久的存储中（也就是磁盘）。

事务型数据库的另外一个重要的性质和原子更新的概念关系密切： 当多个事务并发地运行的时候，那么每个事务都不应看到其它事务所做的未完成的变化。 比如，如果一个事务正忙着计算所有分行的余额总和， 那么它不应该包括来自 Alice 的分行的扣帐和来自 Bob 分行的入帐，反之亦然。 所以事务必须是黑白分明的，不仅仅体现在它们在数据库上产生的永久影响出发，而且体现在它们运转时的自身的可视性上。 一个打开的事务做的更新在它完成之前是其它事务无法看到的，而到提交的时候所有更新同时可见。

在 PostgreSQL 里，一个事务是通过把 SQL 命令用 BEGIN 和 COMMIT 命令包围实现的。 因此我们的银行事务实际上看起来象下面这样

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- 等等
COMMIT;

如果在该事务的过程中，我们决定不做提交（可能是我们刚发现 Alice 的余额是负数）， 那么我们可以发出 ROLLBACK 命令而不是 COMMIT 命令，那么到目前为止我们的所有更新都会被取消。

PostgreSQL 实际上把每个 SQL 语句当做在一个事务中执行的来看待。 如果你没有发出 BEGIN 命令，那么每个独立的语句都有一个隐含的 BEGIN 和（如果成功的话） COMMIT 语句包围在周围。 一组包围在 BEGIN 和 COMMIT 语句中间的语句有时候被称做事务块。

    注意: 一些客户库自动发出 BEGIN 和 COMMIT， 因此你可能不需要特意请求就可以获取事务块的效果。查看你使用的接口的文档。

我们可以通过使用 savepoints 的方法，在一个事务里更加精细地控制其中的语句。 保存点允许你有选择性地抛弃事务中的某些部分，而提交其它剩下的。 在用 SAVEPOINT 定义了一个保存点后，如果需要，你可以使用 ROLLBACK TO 回滚到该保存点。 则该事务在定义保存点到回滚到它之间的所有数据库更改都被抛弃，但是在保存点之前的修改将被保留。

在回滚到一个保存点之后，这个保存点仍然保存着其定义，所以你可以回滚到这个位置好几次。 当然，如果你确信你不需要再次回滚到一个保存点，那么你可以释放它，这样系统可以释放一些资源。 要记住：释放或者回滚到一个保存点都会自动释放在其后定义的所有保存点。

所有这些都发生在一个事务块内部，所以所有这些都不可能被其它事务会话看到。 当且仅当你提交了这个事务块，这些提交了的动作才能以一个但愿的方式被其它会话看到， 而回滚的动作完全不会再被看到。

还记得我们的银行数据库吗？假设我们从 Alice 的帐户上消费 $100.00， 然后给 Bob 的帐户进行贷记加款，稍后我们发现我们应该给 Wally 的账号贷记加款。 那么我们可以像下面这样的保存点来做：

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- 呀！加错钱了，应该用 Wally 的账号
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;

这个例子当然是实在太简单了，但是通过使用保存点，我们可以对事务块有大量的控制。 并且，ROLLBACK TO 是除了事务全部回滚，重新来过之外的唯一可用的， 用于重新控制一个因错误而被系统置于退出状态下的事务的方法。

3.5. 继承

继承是面向对象的数据库的概念。它开启了数据库设计新的有趣的可能性大门。

让我们创建两个表：一个表 cities 和一个表 capitals。自然，首府（capital）也是城市（cities）， 因此在列出所有城市时你想要某种方法隐含地显示首府。 如果你已经很高明了，那么你可能会创造类似下面这样的模式：

CREATE TABLE capitals (
    name            text,
    population      real,
    altitude        int,    -- (单位是英尺)
    state           char(2)
);

CREATE TABLE non_capitals (
    name            text,
    population      real,
    altitude        int     -- (单位是英尺)
);

CREATE VIEW cities AS
    SELECT name, population, altitude FROM capitals
        UNION
    SELECT name, population, altitude FROM non_capitals;

如果只是查询，那么这个方法运转得很好，比如如果你需要更新某几行， 那这个方法就很难看了。

一种更好的方法是：

CREATE TABLE cities (
    name            text,
    population      real,
    altitude        int     -- (单位是英尺)
);

CREATE TABLE capitals (
    state           char(2)
) INHERITS (cities);

在这个例子里，capitals 的一行继承所有来自它的父表， cities 的所有字段（name， population，和 altitude）。 字段 name 的类型是 text， 是 PostgreSQL 用于变长字符串的固有类型。 州首府有一个额外的字段，州，显示所处的州。在 PostgreSQL 里，一个表可以从零个或者更多其它表中继承过来。

比如，下面的查询找出所有海拔超过 500 英尺的城市的名字， 包括州首府：

SELECT name, altitude
    FROM cities
    WHERE altitude > 500;

它返回：

   name    | altitude
-----------+----------
Las Vegas |     2174
Mariposa  |     1953
Madison   |      845
(3 rows)

另外一方面，下面的查询找出所有不是州首府并且位于海拔大于或等于 500 英尺的城市：

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

   name    | altitude
-----------+----------
Las Vegas |     2174
Mariposa  |     1953
(2 rows)

这里的 cities 前面的 ONLY 指示系统只对 cities 表运行查询，而不包括继承级别中低于 cities 的表。 许多我们已经讨论过的命令 — SELECT, UPDATE 和 DELETE — 支持这个 ONLY 表示法。

    注意: 尽管继承经常是有用的，但是它还没有集成唯一约束或者外键，因此制约了其实用性。 参阅 Section 5.5 获取更多细节。

3.6. 结论

PostgreSQL 有许多这份教程里没有谈到的特性， 因为这份教程主要是面向新 SQL 用户的。这些特性在本书剩余部分将有更详细的介绍。

如果你觉得自己需要更多介绍性材料，请访问 PostgreSQL网站 获取更多资源的联接。
4.1. 词法结构

SQL 输入由一系列命令组成。 一条命令是由一系列记号构成， 用一个分号（";"）结尾。 输入流的终止也结束一条命令。那些记号是合法的取决于特定命令的语法。

记号可以是一个关键字， 一个标识符，一个 引号包围的标识符， 一个文本（或常量），或者是特殊的字符符号。 记号通常由空白分隔（空格，tab，换行符），但如果不存在混淆的时候也可以不用 （通常只是一个特殊字符与一些其它记号类型相联的时候）。

另外，在 SQL 输入里可以有注释。 它们不是记号，它们实际上等效于空白。

比如，下列命令是（语法上）合法的 SQL 输入：

SELECT * FROM MY_TABLE;
UPDATE MY_TABLE SET A = 5;
INSERT INTO MY_TABLE VALUES (3, 'hi there');

这里是三条命令的序列，每条一行（尽管并不要求这么做； 多条命令可以在一行里，并且命令可以合理地分裂成多个行）。

如果从哪些记号标识命令，哪些是操作数或参数的角度考虑， SQL 语法并不是非常一致。通常头几个记号是命令名字， 因此上面的例子我们通常可以说是一个"SELECT"， 一个"UPDATE"，和一个"INSERT"命令。 不过， UPDATE 命令总是要求一个 SET 在某个位置出现，并且这个变体的 INSERT 还要求有一个 VALUES 才完整。每条命令的准确语法规则都在 Part VI 里描写。
4.1.1. 标识符和关键字

象上面的例子里的 SELECT，UPDATE， 或 VALUES 这样的记号都是关键字的例子， 也就是那些在 SQL 语言里有固定含义的单词。 记号 MY_TABLE 和 A 是标识符的例子。 根据使用它们的命令的不同，它们标识表，字段，或者其它数据库对象的名字。 因此，有时候只是简单地叫它们"名字"。 关键字和标识符有着同样的词法结构，意思是我们在没有认识这种语言之前是无法区分一个记号是标识符还是名字。 你可以在 Appendix C 里找到一个关键字的完整列表。

SQL 标识符和关键字必须以一个字母开头 （a-z 以及带可区别标记的字母以及非拉丁字母 ）或下划线开头 （_）开头。标识符和关键字里随后的字符可以是字母，数字（0-9）， 或者下划线，但 SQL 标准不会定义包含数字或者以下划线开头或结尾的关键字。

系统使用不超过 NAMEDATALEN-1 个字符作为标识符； 你可以在命令中写更长的名字，但它们会被截断。缺省时， NAMEDATALEN 是 64，因此标识符最大长度是 63 如果觉得这个限制有问题，那么你可以在 src/include/postgres_ext.h 里修改 NAMEDATALEN 来改变它。

标识符和关键字名字都是大小写无关的。因此

UPDATE MY_TABLE SET A = 5;

也可以等效地写成

uPDaTE my_TabLE SeT a = 5;

一种好习惯是把关键字写成大写，而名字等用小写。

UPDATE my_table SET a = 5;

还有第二种标识符：分隔标识符 或引号包围的标识符。 它是通过在双引号（" ） 里包围任意字符序列形成的。 分隔标识符总是一个标识符，而不是关键字。因此，你可以用 "SELECT" 表示一个字段名字或者名字叫 "SELECT" 的表，而一个没有引号的 SELECT 将被当做一条命令的一部分，因此如果把它当做一个表的名字或者字段名字用的话就会产生一个分析错误。 上面的例子可以用引起的标识符这么写：

UPDATE "my_table" SET "a" = 5;

引号包围的标识符可以包含除引号本身以外的任何其它字符。 要包含一个双引号，我们可以写两个双引号。 这样我们就可以构造那些原本是不允许的表或者字段名字， 比如那些包含空白或与号的名字。但长度限制依旧。

把一个标识符用引号包围的起来同时也令它大小写相关，而没有引号包围起来的名字总是转成小写。 比如，我们认为标识符 FOO，foo 和 "foo" 是一样的 PostgreSQL名字， 但 "Foo" 和 "FOO" 与上面三个以及它们之间都是不同的。 （PostgreSQL 里对未加引号的名子总是转换成小写， 这和 SQL 是不兼容的，SQL 里要求未用引号包围起来的名字总是转成大写。 因此 foo 等于 "FOO"。 如果你想写可移植的程序，那么我们建议你要么就总是引号包围的某个名字，要么就坚决不引。）
4.1.2. 常量

在 PostgreSQL 里有三种隐含类型的常量： 字符串，位串，和数值。 常量也可以声明为明确的类型，这样就可以使用更准确的表现形式以及可以通过系统更有效地处理。 这些候选的在后面的小节描述。
4.1.2.1. 字符串常量

SQL 里的一个字串文本是用单引号（'）包围的任意字符序列， 比如，'This is a string'。 这种声明字串常量的方法是 SQL 标准定义的。 在这种类型的字串常量里嵌入单引号的标准兼容的做法是敲入两个连续的单引号比如，'Dianne''s horse'。 另外，PostgreSQL 允许用用一个反斜杠（"\"）来逃逸单引号， 因此同一个字串可以写成'Dianne\'s horse'。

另外一个 PostgreSQL 扩展是还可以使用 C-风格的反斜杠逃逸： \b 是一个退格，\f 是一个进纸，\n 是一个换行符， \r 是一个回车，\t 是一个水平制表符，而\xxx， 这里 xxx 是一个八进制数，是对应 ASCII 码的字符。任何其它跟在反斜杠后面的字符都当做文本看待。 因此，要在字符串常量里包含反斜杠，你可以写两个反斜杠。

编码为零的字符不能出现在字符串常量中。

两个只是通过至少有一个换行符的空白分隔的字符串常量会被连接在一起，并当做它们是写成一个常量处理。 比如：

SELECT 'foo'
'bar';

等效于

SELECT 'foobar';

而

SELECT 'foo'      'bar';

是非法的语法，（这个略微有些怪异的行为是 SQL 声明的； PostgreSQL 遵循标准。）
4.1.2.2. 美元符包围字串常量

尽管声明字串常量的标准方法通常都很方便，但是如果字串包含很多单引号或者反斜杠， 那么理解字串的内容可能就会变得很苦涩，因为每个单引号都要加倍。 为了让这种场合下的查询更具可读性，PostgreSQL 允许另外一种称作"美元符包围"的字串常量声明办法。 一个通过美元符包围声明的字串常量由一个美元符号（$），一个可选的零个或多个字符"记号"， 另外一个美元符号，一个组成字串常量的任意字符的序列，一个美元符号，以及一个和开始这个美元符包围的记号相同的记号，和一个美元符号组成。 比如，下面是两个不同的方法，用美元符包围声明了前面的例子：

$$Dianne's horse$$
$SomeTag$Dianne's horse$SomeTag$

请注意，在美元符包围的字串里，单引号可以不用逃逸使用。 实际上，在一个美元符包围的字串里，没有什么字符需要逃逸： 字串内容总是按照字面内容写。反斜杠不是特殊的， 美元符自己也不是特殊的，除非它们和开标签的一部分匹配。

我们可以通过在不同嵌套级别使用不同的美元符引号字串常量来实现嵌套。 最常见的是写函数定义的时候。比如：

$function$
BEGIN
    RETURN ($1 ~ $q$[\t\r\n\v\\]$q$);
END;
$function$

这里，序列 $q$[\t\r\n\v\\]$q$ 表示一个美元符包围的字串文本 [\t\r\n\v\\]， 在函数体被 PostgreSQL 执行的时候，它讲被识别出来。 但是因为这个序列不匹配外层的美元符分隔符$function$， 那么只要考虑外层字串，那么它只是常量里面的一些额外的字符而已。

如果有标签的话，一个美元符包围的字串遵循和无引号包围的标识符相同的规则， 只是它不能包含美元符。标签是大小写相关的，因此 $tag$String content$tag$ 是正确的，而 $TAG$String content$tag$ 不对。

一个后面跟着关键字或者标识符的美元包围的字串必须用空白隔开； 否则美元符包围分隔符将会被认为前面标识符的一部分。

美元符包围不是 SQL 标准，但是在写复杂的字串文本的时候，它通常比标准的单引号语法更方便。 尤其是在其它常量里表现字串常量的时候更有用，比如经常在过程函数定义里面的。 如果用单引号语法，每个上面例子里的反斜杠都必须写四个，它们在作为字串文本分析的时候会减少为两个， 然后在函数执行的时候在内层字串常量里会再次被解析为一个。
4.1.2.3. 位串常量

位串常量看起来很象在开引号前面有一个 B （大写或小写）的普通字符串（它们之间没有空白）， 比如 B'1001'。位串常量里可以用的字符只有 0 和 1。

另外，位串常量可以用十六进制表示法声明，方法是使用前缀的 X （大写或者小写），比如，X'1FF'。 这种表示法等效于一个每个十六进制位四个二进制位的位串常量。

两种形式的位串常量都可以象普通字串常量那样跨行连续。 美元符包围不能用于位串常量。
4.1.2.4. 数值常量

数值常量接受下列通用的形式：

digits
digits.[digits][e[+-]digits]
[digits].digits[e[+-]digits]
digitse[+-]digits

这里的 digits 是一个或多个十进制位（0 到 9）。 如果有小数点，那么至少有一位在小数点前面或后面。如果出现了指数分隔符（e），那么至少有一个位跟在它后面。 在常量里不能有空格或者其他字符嵌入在内。 请注意任何前导地正号或者负号实际上都不认为是常量的一部分； 它是施加于常量的一个操作符。

这里是一些合法的数值常量的例子：

42
3.5
4.
.001
5e2
1.925e-3

如果一个数值常量既不包含小数点，也不包含指数操作符， 那么如果它的数值可以放在integer类型中（32位）， 则认为它是integer类型；如果它的数值可以放在 bigint中（64位），则认为它是 bigint； 否则认为它是 numeric类型。包含小数点和/或指数操作符的常量总是被认为是numeric类型。

给一个数值常量赋予初始数据类型只是类型解析算法的开端。 在大多数情况下该常量会根据环境被自动强制转换成最合适的类型。 必要时，你可以通过强制类型转换把一个数值解析成特定的数据类型。 比如，你可以强制要求把一个数值当作类型real（float4）来看，方法时这么写：

REAL '1.23'  -- 字串风格
'1.23'::REAL -- PostgreSQL （历史原因）风格
     

这些实际上只是下面讨论的通用转换的特例。
4.1.2.5. 其它类型的常量

任意类似的常量可以用下列表示法中的任何一种来输入：

type 'string'
'string'::type
CAST ( 'string' AS type )

在字串常量的文本将传递给那种叫 type 的类型的输入转换过程。 结果是这种类型的一个常量。如果不存在该常量所属类型的歧义， 那么明确的类型映射可以省略（比如，当你把它直接赋予一个表字段的时候）， 这种情况下它会自动转换。

字串常量可以用普通 SQL 表示法或者美元符包围来书写。

我们还可以用函数样的语法来声明类型转换：

typename ( 'string' )

不过并非所有类型名可以这样使用；参阅 Section 4.2.8 获取细节。

::，CAST()，和函数调用语法也可以用于声明任意表达式的运行时类型转换， 如 Section 4.2.8 中讨论的那样。 但是 type 'string' 的形式只能用于声明一个文本常量的类型。 type 'string' 的另外一个限制是它不能用于数组类型；要用 :: 或者 CAST() 声明一个数组常量的类型。
4.1.3. 操作符

一个操作符是最多 NAMEDATALEN-1 （缺省 63 个字符）个下列字符的序列：

+ - * / < > = ~ ! @ # % ^ & | ` ?
不过，对操作符名字有几个限制：

    *

      -- 和 /* 不能出现在操作符名字中的任何地方，因为它们会被当做注释开始对待。
    *

      多字符操作符不能以 + 或 - 结束， 除非其名字至少还包含下列操作符之一：

      ~ ! @ # % ^ & | ` ?
      比如，@- 是允许的操作符名字， 但 *- 不是。这个限制允许 PostgreSQL 在不要求记号之间有空白的情况下分析 SQL 兼容的查询。

当你使用非 SQL 标准的操作符名字的时候，你通常需要用空白分隔相邻的操作符以避免歧义。 比如，如果你定义了一个叫 "@" 的左单目操作符，那么你就不能写 X*@Y；而是要写成 X* @Y 以确保 PostgreSQL 把它读成两个操作符，而不是一个。
4.1.4. 特殊字符

有些非字母数字字符有一些特殊含义，因此不能用做操作符。 它们的用法的细节可以在相应的描述语法元素的地方找到。 本节只是描述它们的存在和概括一下这些字符的目的。

    *

      美元符号（$）后面跟着数字用于在一个函数体定义或者准备好的语句中 表示参数的位置。在其他环境里美元符号可能是一个标识符名字或者是一个美元符包围的字串常量的一部分。
    *

      圆括弧（()）用于分组和强制优先级的时候含义与平常一样。 有些场合里圆括弧是作为一个特定 SQL 命令的固定语法的一部分要求的。
    *

      方括弧（[]）用于选取数组元素。 参阅 Section 8.10 获取更多信息。
    *

      逗号（,在一些语法构造里用于分隔一个列表的元素。
    *

      分号（;）结束一条 SQL 命令。 它不能出现在一条命令里的任何地方，除非引号包围的来当做字符串常量或者标识符用。
    *

      冒号 （:）用于从数组中选取"片段"。（参阅 Section 8.10。）在一些 SQL 方言里（比如嵌入 SQL ）， 冒号用于前缀变量名。
    *

      星号 （* 在某些环境里表示一个表行或者一个符合类型值的全部字段。 在用作聚集函数 COUNT 的参数时还有特殊含义。
    *

      句点 （.用在数字常量里，并用于分隔模式，表和字段名字。

4.1.5. 注释

注释是任意以双划线开头并延伸到行尾的任意字符序列，比如：

-- 这是标准的 SQL92 注释

另外，还可以使用 C-风格的块注释：

/* 多行注释
* 可以嵌套∶/* 嵌套的块注释 */
*/

这里注释以 /* 开头并扩展到对应的 */。这些块注释可以嵌套，就象 SQL99 里说的那样， 但和 C 不一样，因此我们可以注释掉一大块已经包含块注释的代码。

注释在进一步的语法分析之前被从输入流删除并用空白代替。
4.1.6. 词法优先级

Table 4-1 显示了 PostgreSQL 里面的操作符的优先级和关联性。 大多数操作符都有相同的优先级并且都是左关联的。 这种情况可能会有不那么直观的行为；比如，布尔操作符 < 和 > 和布尔操作符 <= 和 >= 之间有着不同的优先级。同样，当你把双目和单目操作符组合使用的时候， 有时候也需要加圆括弧。比如

SELECT 5 ! - 6;

会被分析成

SELECT 5 ! (- 6);

因为分析器不知道 ! 定义成了后缀操作符， 而不是中缀操作符。— 知道的时候只能是太晚了 — 要在本例中获得你需要的特性，你要写成

SELECT (5 !) - 6;

这是我们为扩展性付出的代价。

Table 4-1. 操作符优先级（递减）
操作符/元素        关联性        描述
.        左        表/字段名分隔符
::        左        PostgreSQL-特有的类型转换操作符
[ ]        左        数组元素选则
-        右        单目负号
^        左        幂操作
* / %        左        乘，除，模
+ -        左        加，减
IS                 IS TRUE, IS FALSE, IS UNKNOWN, IS NULL
ISNULL                 测试是否为空值
NOTNULL                 测试是否为非空值
（任何其它的）        左        所有其它的本地和用户定义操作符
IN                 集合成员
BETWEEN                 范围包含
OVERLAPS                 时间间隔重叠
LIKE ILIKE SIMILAR                 字符串模式匹配
< >                 小于，大于
=        右        等于，赋值
NOT        右        逻辑反
AND        左        逻辑与
OR        左        逻辑或

请注意操作符优先级也适用于和上面提到的同名的内置操作符用户定义操作符。 比如，如果你为一些客户数据类型定义一个 "+" 操作符， 那么它和内置的 "+" 操作符有同样的优先级，不管你干了什么。

如果在 OPERATOR 语法里使用了模式修饰的操作符名， 比如

SELECT 3 OPERATOR(pg_catalog.+) 4;

那么 OPERATOR 构造就会有 Table 4-1 表里面为"任何其它"操作符显示的缺省优先级。 不管什么特定的操作符出现在 OPERATOR()里，都是这样。

4.2. 值表达式

值表达式用在各种语法环境中，比如在 SELECT 命令的目标列表中，在 INSERT 或 UPDATE 中用做新的列值，或者在许多命令中的搜索条件中使用。 我们有时候把值表达式的结果叫做标量， 以便与一个表表达式的结果相区别（是一个表）。因此值表达式也叫做标量表达式 （或者更简单的表达式）。表达式语法允许对来自基本部分的数值进行算术，逻辑，集合，和其它操作的运算。

值表达式是下列内容之一：

    *

      一个常量或者文本值。
    *

      一个字段引用。
    *

      一个位置参数引用，在函数声明体中。
    *

      一个操作符调用。
    *

      一个函数调用。
    *

      一个聚集表达式。
    *

      一个类型转换。
    *

      一个标量子查询。
    *

      一个行构造器。
    *

      另外一个在圆括弧里面的值表达式，可以用于子表达式分组和覆盖优先级。

除了这个列表以外，还有许多构造可以归类为表达式，但是不遵循任何通用的语法规则。 它们通常有函数或操作符的语义，并且在 Chapter 9 里合适的位置描述。 一个例子是 IS NULL 子句。

我们已经在 Section 4.1.2 里有讨论过的内容了。下面的节讨论剩下的选项。
4.2.1. 字段引用

一个字段可以用下面形式的引用：

correlation.columnname

correlation 是一个表的名字（可能有模式修饰）， 或者是用FROM子句这样的方法定义的表的别名，或者是关键字 NEW 或 OLD。 （NEW和 OLD只能出现在一条改写规则中， 而其他相关的名字可以用于任意 SQL 语句中。） 如果在当前查询中所使用的所有表中，该字段名字是唯一的， 那么这个相关名字和分隔用的点就可以省略。 （又见 Chapter 7。）
4.2.2. 位置参数

位置参数引用用于标识从外部给一个 SQL 语句的一个参数。 参数用于 SQL 函数定义语句和准备好的查询。 有些客户端库还支持在 SQL 命令字串外边声明数据值，这种情况下参数用于引用 SQL 字串行外的数据。 一个参数的形式如下：

$number

比如，看看一个函数 dept 的定义， 如下

CREATE FUNCTION dept(text) RETURNS dept
  AS $$ SELECT * FROM dept WHERE name = $1 $$
  LANGUAGE SQL;

在函数被调用的时候这里的 $1 将被第一个函数的参数代替。
4.2.3. 下标

如果一个表达式生成一个数组类型的数值，那么我们可以通过写下面这样的表达式来声明数组值的元素

expression[subscript]

如果是多个相邻的元素（一个"数组片断"）可以用下面的方法抽取

expression[lower_subscript:upper_subscript]

（在这里，方括弧 [ ] 的意思是按照字面文本的方式出现。） 每个subscript自己都是一个表达式，它必须生成一个整数值。

通常，数组 expression 必须用圆括弧包围， 但如果要进行脚标计算的表达式只是一个字段引用或者一个位置参数，那么圆括弧可以省略。 同样，如果源数组是多维的，那么多个脚标可以连接在一起。比如，

mytable.arraycolumn[4]
mytable.two_d_column[17][34]
$1[10:42]
(arrayfunction(a,b))[42]

最后一个例子里的圆括弧是必须的。参阅 Section 8.10 获取有关数组的更多信息。
4.2.4. 字段选择

如果一个表达式生成一个复合类型（行类型），那么用下面的方法可以抽取一个指定的字段

expression.fieldname

通常，行 expression 必须用圆括弧包围， 但是如果要选取的表达式只是一个表引用或者位置参数，可以省略圆括弧。 比如

mytable.mycolumn
$1.somecolumn
(rowfunction(a,b)).col3

（因此，一个全称的字段引用实际上只是一个字段选择语法的特例。）
4.2.5. 操作符调用

操作符调用有三种语法∶

expression operator expression (双目中缀操作符)
operator expression (单目前缀操作符)
expression operator (单目后缀操作符)

这里的 operator 记号遵循语法规则： Section 4.1.3， 或者是记号：AND， OR，和 NOT 之一。 或者是一个被修饰的操作符名

OPERATOR(schema.operatorname)

具体存在哪个操作符以及它们是单目还是双目取决于系统或用户定义了什么操作符。Chapter 9 描述了内置的操作符。
4.2.6. 函数调用

函数调用的语法是合法函数名字（可能有模式名修饰）， 后面跟着在圆括弧里的它的参数列表：

function ([expression [, expression ... ]] )

比如，下面的代码计算 2 的平方根：

sqrt(2)

内置函数的列表在 Chapter 9 里。 其它函数可以由用户添加。