简介
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数据库设计的关键组成部分。视图允许你将表结构的细节封装起来,这些细节可能会随着应用的发展而变化,但通过视图提供了一致的接口。
视图几乎可以在任何可以使用真实表的地方使用。基于其他视图构建新的视图也并不罕见,这有助于进一步抽象和封装数据,使其更易于管理和使用。
视图数据是否可以更改?
简单视图:
复合视图:
WITH CHECK OPTION:
INSERT/UPDATE/DELETE操作:
外键
回顾我们在第二章中介绍过的 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".
外键的行为可以根据你的应用程序需求进行精细调整。在本教程中,我们仅展示了这个简单的例子,但更多关于外键的信息可以在第五章中找到。合理使用外键绝对能显著提高你的数据库应用程序的质量,因此强烈建议你深入学习这一主题。
整理与总结:
事务
事务(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是在错误导致事务块进入中止状态后重新获得控制的唯一方式,避免完全回滚并重新开始。
窗口函数
窗口函数在数据库查询中提供了一种强大的能力,允许你在与当前行相关的行集合上执行计算,这些计算类似于聚合函数的工作,但与之不同的是,窗口函数保留了每一行的独立性,不会将数据行组合成单个输出行。以下是窗口函数的关键概念和使用要点:
基础概念:
语法结构:
窗口帧:
使用场景:
限制与注意事项:
高级用法:
#使用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中,继承是一种数据库设计模式,来源于面向对象数据库的概念,它允许一个表(子表)从另一个表(父表)继承列和属性,从而提供了一种更灵活的数据组织方式。以下是继承的关键点:
基本概念:
查询与更新:
示例:
查询示例:
限制与注意事项:
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成为一个非常灵活且功能全面的数据库解决方案。无论是对于开发者还是数据库管理员来说,掌握这些特性都是非常有价值的。