从零开始学PostgreSQL (十四)：高级功能

目录

1. 简介

2. 视图

3. 外键

4. 事务

5. 窗口函数

6. 继承

7. 结论

简介

PostgreSQL是一个强大且开源的关系型数据库管理系统，以其稳定性、功能丰富性和对SQL标准的广泛支持而闻名。它不仅提供了传统的关系型数据库功能，如事务处理、外键约束和视图，还引入了许多高级特性，如窗口函数、事务和复杂的查询语言扩展。PostgreSQL的设计理念强调数据完整性和一致性，使其成为需要高性能和高可靠性的应用程序的理想选择。

视图

假设天气记录与城市位置的组合列表对你的应用程序尤为重要，但你不想每次需要时都重新输入相同的查询语句。这时，你可以通过创建一个视图来解决这个问题，视图本质上是对查询命名，之后你可以像引用普通表一样引用这个查询：

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

接着，你可以像查询普通表一样查询这个视图：

SELECT * FROM myview;

充分运用视图是良好SQL数据库设计的关键组成部分。视图允许你将表结构的细节封装起来，这些细节可能会随着应用的发展而变化，但通过视图提供了一致的接口。

视图几乎可以在任何可以使用真实表的地方使用。基于其他视图构建新的视图也并不罕见，这有助于进一步抽象和封装数据，使其更易于管理和使用。

视图数据是否可以更改？

简单视图：

• 如果视图基于单个基础表，并且没有使用聚合函数、DISTINCT、GROUP BY、HAVING、UNION、INTERSECT、EXCEPT等操作，那么通常这个视图是可以更新的。
• 视图的列应该直接映射到基础表的列，即没有使用表达式或常量来生成视图的列。

复合视图：

• 如果视图涉及到多个表的连接（JOIN）或者包含了上述提到的复杂操作，那么默认情况下视图是不可更新的。
• 即使在一些特定条件下，通过使用WITH CHECK OPTION，你仍然可以对某些复合视图进行更新，但是这要求更新操作必须满足视图定义中的所有条件。

WITH CHECK OPTION：

• 当创建视图时，可以使用WITH CHECK OPTION子句来限制对视图的INSERT和UPDATE操作，使其必须满足视图定义中的WHERE子句条件。
• 这对于维护数据一致性特别有用，确保通过视图进行的任何更改都不会违反视图的定义逻辑。

INSERT/UPDATE/DELETE操作：

• 对于可更新的视图，你可以执行INSERT、UPDATE和DELETE操作，就像对基础表一样。
• PostgreSQL会将这些操作转换为对基础表的操作，同时保持视图定义的逻辑。

外键

回顾我们在第二章中介绍过的 weather 和 cities 表。现在，考虑这样一个问题：你希望确保在 weather 表中插入的每一行都有一个对应的 cities 表中的条目。这被称为维护数据的参照完整性。在一些基础的数据库系统中，实现这一点（如果有的话）通常需要先检查 cities 表是否存在相匹配的记录，然后根据检查结果决定是否插入或拒绝新的 weather 记录。这种方法存在诸多问题且极为不便，因此 PostgreSQL 提供了自动化处理这一过程的功能。

以下是使用外键约束更新表格定义的方式：

CREATE TABLE cities (
        name     varchar(80) PRIMARY KEY,
        location point
);


CREATE TABLE weather (
        city      varchar(80) REFERENCES cities(name),
        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".

外键的行为可以根据你的应用程序需求进行精细调整。在本教程中，我们仅展示了这个简单的例子，但更多关于外键的信息可以在第五章中找到。合理使用外键绝对能显著提高你的数据库应用程序的质量，因此强烈建议你深入学习这一主题。

整理与总结：

• 外键概念：外键（Foreign Key）是一种关系数据库中用于维护两个表之间关联的机制，它确保了数据的参照完整性。
• 外键使用案例：通过将 weather 表中的 city 字段定义为参照 cities 表中 name 字段的外键，可以自动阻止向 weather 表中插入不存在于 cities 表中的城市名称。
• 错误处理：尝试插入不匹配外键约束的数据时，PostgreSQL 将返回错误信息，指出违反了外键约束，并提供详细的错误细节。
• 行为调整：外键的行为可以依据具体需求进行调整，例如在删除或更新主表中的记录时对外键表的影响策略。
• 质量提升：合理使用外键可以显著增强数据库应用程序的健壮性和数据一致性，建议深入学习和应用外键相关的知识和最佳实践。

事务

事务（Transactions）是所有数据库系统中的基本概念。事务的核心在于将多个步骤捆绑成一个不可分割的操作。在各步骤之间的中间状态对其他并发事务是不可见的，如果发生某种故障导致事务无法完成，则事务中的任何步骤都不会影响数据库。

例如，假设银行数据库存储了不同客户账户的余额以及分支行的总存款余额。如果我们想记录一笔从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收到未从Alice账户扣除的$100.00，否则Alice也不会满意她被扣款而Bob却没有收到。我们需要保证如果在操作中途出现问题，已经执行的步骤不会生效。将更新分组为一个事务提供了这种保证。事务具有原子性：从其他事务的角度来看，它要么完全发生，要么根本不发生。

我们还需要一个保证，即一旦事务被数据库系统确认完成，它确实已经被永久记录下来，即使随后发生崩溃也不会丢失。例如，如果我们在记录Bob的现金取款，我们不希望在他离开银行后他的账户扣款会因系统崩溃而消失。事务型数据库保证事务的所有更新在报告完成前都会被记录在永久存储（即磁盘上）。

另一个与原子更新紧密相关的事务数据库的重要属性是，在多个事务并发运行时，每一个事务都不应该能够看到其他事务未完成的变化。例如，如果一个事务正在忙于汇总所有分支行的余额，那么它不应该只包括Alice所在分行的借记，而不包括Bob所在分行的贷记，反之亦然。因此，事务必须在对数据库的永久影响以及其发生时的可见性方面都是全有或全无的。一个打开的事务至今为止所做的更新直到事务完成才对其他事务可见，在那之后所有的更新会同时变得可见。

在PostgreSQL中，事务通过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），可以在更精细的粒度上控制事务中的语句。保存点允许你选择性地撤销事务的部分，而保留其余部分。在定义保存点后，如果需要，可以使用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是在错误导致事务块进入中止状态后重新获得控制的唯一方式，避免完全回滚并重新开始。

窗口函数

窗口函数在数据库查询中提供了一种强大的能力，允许你在与当前行相关的行集合上执行计算，这些计算类似于聚合函数的工作，但与之不同的是，窗口函数保留了每一行的独立性，不会将数据行组合成单个输出行。以下是窗口函数的关键概念和使用要点：

基础概念：

• 窗口函数能够在与当前行相关的行集合上执行计算，这个集合被称为窗口帧。
• 它们可以执行如平均值、排名、累计和等计算，但与普通的聚合函数不同，窗口函数不会消除原始数据的行。

语法结构：

• 窗口函数调用总是包含OVER子句，该子句紧随函数名和参数之后，用于指定函数作用的窗口范围。
• PARTITION BY子句用于将行数据划分为不同的分区，每个分区内的数据独立进行计算。
• ORDER BY子句用于控制窗口函数处理数据的顺序，即使输出结果的顺序与ORDER BY指定的顺序不同。

窗口帧：

• 窗口帧定义了当前行计算时考虑的行集合。默认情况下，窗口帧包含当前分区中从开始到当前行的所有行，加上任何与当前行相同的后续行。
• 通过ROWS BETWEEN和RANGE BETWEEN可以进一步定制窗口帧的范围。

使用场景：

• 比较当前行与同组内其他行的值，如计算每个部门员工薪水的排名。
• 执行累计计算，如计算销售额的累计总和。
• 计算移动平均数、百分位数等统计指标。

限制与注意事项：

• 窗口函数只能出现在SELECT列表和ORDER BY子句中，不能用于WHERE、GROUP BY或HAVING子句。
• 窗口函数在非窗口聚合函数之后执行，意味着可以在窗口函数的参数中使用非窗口聚合函数。

高级用法：

• 可以在WINDOW子句中命名窗口框架，然后在多个OVER子句中引用，避免重复定义相同的窗口行为。

#使用sum()函数：
SELECT salary, sum(salary) OVER () FROM empsalary;
#求和是从第一个（最低）薪水到当前薪水，包括当前薪水的任何重复项（注意重复薪水的结果）
SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;
#执行窗口计算后过滤或分组行，可以使用子查询
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;
#当查询涉及多个窗口函数时，为每个函数编写单独的OVER子句是冗余且容易出错的，尤其是当几个函数想要相同的窗口行为时。相反，可以为每个窗口行为在WINDOW子句中命名，然后在OVER中引用。例如：
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

Inheritance 

在PostgreSQL中，继承是一种数据库设计模式，来源于面向对象数据库的概念，它允许一个表（子表）从另一个表（父表）继承列和属性，从而提供了一种更灵活的数据组织方式。以下是继承的关键点：

基本概念：

• 一个子表可以继承自一个或多个父表，从而获取父表的所有列和属性。
• 子表可以有自己的额外列，这些列不会在父表中出现。

查询与更新：

• 当从一个继承树的表中查询数据时，PostgreSQL会搜索整个继承树，除非使用ONLY关键字来限制查询范围。
• 更新和删除操作也可以作用于整个继承树，或者通过ONLY限定在特定表上。

示例：

• 创建一个cities表作为父表，然后创建一个capitals表作为子表，capitals继承自cities，并添加一个state列来表示州的缩写。

查询示例：

• 查询所有位于海拔超过500英尺的城市，包括州首府，只需从cities表查询即可。
• 查询所有非州首府并且海拔超过500英尺的城市，需要使用FROM ONLY cities。

限制与注意事项：

• 继承目前没有与唯一约束或外键完全集成，这限制了它的功能性和适用场景。
• 继承的使用需要仔细规划，以避免潜在的复杂性和不必要的数据冗余。

CREATE TABLE cities (
  name       text,
  population real,
  elevation  int     -- (in ft)
);
CREATE TABLE capitals (
  state      char(2) UNIQUE NOT NULL
) INHERITS (cities);
#查询可以找出所有位于海拔超过500英尺的城市名称，包括州的首都：
SELECT name, elevation
  FROM cities
  WHERE elevation > 500;
#查询可以找出所有非州首府并且海拔超过500英尺的城市：
SELECT name, elevation
    FROM ONLY cities
    WHERE elevation > 500;

结论

PostgreSQL提供了一系列强大的功能，使其能够处理从简单的到非常复杂的数据管理需求。这些高级特性如视图、外键、事务、窗口函数和继承，使得PostgreSQL成为一个非常灵活且功能全面的数据库解决方案。无论是对于开发者还是数据库管理员来说，掌握这些特性都是非常有价值的。