3.1 介绍

我们现在将讨论一些SQL更高级的特性， 这些特性可以简化管理和避免数据的丢失或损坏。最后，我们将看看一些PostgreSQL 的扩展。

3.2 视图

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

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 外健

考虑下面的问题：你想确保没有人可以在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".

正确使用外键无疑将改进你的数据库应用，所以我们强烈建议你学习它们。

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， 因此你可能不需要特意请求就可以获得事务块的效果。查看你使用的接口的文档。

我们可以通过使用保存点的方法，在一个事务里更加精细地控制其中的语句。 保存点允许你选择性地抛弃事务中的某些部分，而提交剩下的部分。在用SAVEPOINT 定义了一个保存点后，如果需要，你可以使用ROLLBACK TO回滚到该保存点。 则该事务在定义保存点到 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 窗口函数

窗口函数在和当前行相关的一组表行上执行计算。 这相当于一个可以由聚合函数完成的计算类型。但不同于常规的聚合函数， 使用的窗口函数不会导致行被分组到一个单一的输出行；行保留其独立的身份。 在后台，窗口函数能够访问的不止查询结果的当前行。

这里是一个例子，说明如何比较每个员工的工资和在他或她的部门的平均工资：

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

  depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

前三输出列直接来自表empsalary，并有一个针对表中的每一行的输出行。 第四列将代表所有含有相同的depname值的表行的平均值作为当前值。 （这实际上与标准avg聚合函数的功能相同， 但是OVER子句使其被视为一个窗口函数并在一组合适的行上执行计算。）

窗口函数的调用总是包含一个OVER子句，后面直接跟着窗口函数的名称和参数。 这是它在语法上区别于普通函数或聚合功能的地方。 OVER子句决定如何将查询的行进行拆分以便给窗口函数处理。 OVER子句内的PARTITION BY列表指定将行划分成组或分区， 组或分区共享相同的PARTITION BY表达式的值。 对于每一行，窗口函数在和当前行落在同一个分区的所有行上进行计算。

你还可以使用窗口函数OVER内的ORDER BY来控制行的顺序。 （ORDER BY窗口甚至不需要与行的输出顺序相匹配。）下面是一个例子：

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;

  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)

正如此处所示，rank函数按照由ORDER BY子句定义的顺序， 在当前行的分区里为每个不同的ORDER BY值产生了一个数值排名。 rank 不需要明确的参数，因为它的行为完全取决于OVER子句。

窗口函数的行来自查询的FROM子句产生，并且如果有的话， 经过WHERE,GROUP BY和HAVING子句过滤的"虚拟表"。 比如，被移除掉的行，因为不符合WHERE条件，所以是不能被任何窗口函数可见的。 一个查询可以包含多个窗口函数，通过不同的OVER子句用不同的方式分割数据， 但是他们都作用在这个虚拟表定义的同一个行集合。

我们已经看到了，如果行排序并不重要，ORDER BY可以省略。 在只有一个包含了所有行的分区情况下，也可以省略PARTITION BY。

还有一个与窗口函数相关的重要的概念：对于每一行，有在其分区范围内的行集， 又称为它的window frame。许多（但不是全部）窗口函数，只作用于window frame中的行上， 而不是整个分区。默认情况下，如果使用ORDER BY， 那么这个frame包含从分区开始到当前行的所有行，以及那些当前行后面的，根据ORDER BY 子句等于当前行的所有行，如果省略ORDER BY，那么，frame默认包含分区中的所有行。 [1] 下面是一个使用sum的例子：

SELECT salary, sum(salary) OVER () FROM empsalary;

 salary |  sum  
--------+-------
   5200 | 47100
   5000 | 47100
   3500 | 47100
   4800 | 47100
   3900 | 47100
   4200 | 47100
   4500 | 47100
   4800 | 47100
   6000 | 47100
   5200 | 47100
(10 rows)

如上，因为在OVER子句中没有使用ORDER BY，因此， window frame与分区(不使用PARTITION BY时即整个表)相同；换句话说， 每一次sum求和都是使用表中所有的salary，所以我们得到的每个输出行的结果相同。 但是，如果我们添加ORDER BY子句，我们会得到不同的结果：

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

 salary |  sum  
--------+-------
   3500 |  3500
   3900 |  7400
   4200 | 11600
   4500 | 16100
   4800 | 25700
   4800 | 25700
   5000 | 30700
   5200 | 41100
   5200 | 41100
   6000 | 47100
(10 rows)

这里的总和是从第一个（最低）工资到当前一个，包括任何当前重复的（注意重复薪金的结果）。

窗口函数仅允许在查询的SELECT列表和ORDER BY子句中使用。 在其他地方禁止使用，比如GROUP BY,HAVING和WHERE子句。 这是因为它们逻辑上在处理这些子句之后执行。此外，窗口函数在标准聚合函数之后执行。 这意味在一个窗口函数的参数中包含一个标准聚合函数的调用是有效的，但反过来不行。

执行窗口计算后，如果有必要对行进行过滤或分组，你可以使用子查询。例如：

SELECT depname, empno, salary, enroll_date
FROM
  (SELECT depname, empno, salary, enroll_date,
          rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
     FROM empsalary
  ) AS ss
WHERE pos < 3;

上面的查询只显示内部查询结果中rank小于3的行。

当查询涉及多个窗口函数时，可以写成每一个都带有单独的OVER子句， 但是，如果期待为多个窗口函数采用相同的窗口行为，这样做就会产生重复，并且容易出错。 作为代替，每个窗口行为可以在WINDOW子句中进行命名，然后再被OVER引用。 例如：

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

3.6 继承

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

让我们创建两个表：一个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用于变长字符串的固有类型。州首府有一个额外的字段 state显示其所处的州。在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表示法。

注意: 尽管继承经常是有用的，但是它还没有集成唯一约束或者外键，因此制约了其实用性。