2.3-高级特性

Create by fall on 25 Feb 2024
Recently revised in 25 Feb 2024

视图

view 视图允许用户通过始终如一的接口封装表的结构细节，这样可以避免表结构随着应用的进化而改变。

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

SELECT * FROM myview;

外键

当我们插入数据的时候，如果数据 A 对 B 进行引用，B 不存在时，禁止插入数据。

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
);

-- 此时，如果不存在城市 Berkeley，还要插入关于 Berkeley 的天气数据
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".

事务

它将多个步骤捆绑成了一个单一的、要么全完成要么全不完成的操作。

步骤之间的中间状态对于其他并发事务是不可见的，并且如果有某些错误发生导致事务不能完成，则其中任何一个步骤都不会对数据库造成影响。

一个事务被称为是原子的：从其他事务的角度来看，它要么整个发生要么完全不发生。

-- 比如说，银行的转账功能，简化的操作如下：
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');

我们同样希望能保证一旦一个事务被数据库系统完成并认可，它就被永久地记录下来且即便其后发生崩溃也不会被丢失。一个事务型数据库保证一个事务在被报告为完成之前它所做的所有更新都被记录在持久存储（即磁盘）。

当多个事务并发运行时，每一个都不能看到其他事务未完成的修改。

一个事务所做的更新在它完成之前对于其他事务是不可见的，而之后所有的更新将同时变得可见。

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
-- etc etc
COMMIT;
-- 在事务执行中我们并不想提交，我们可以发出 ROLLBACK 命令而不是 COMMIT 命令，这样所有目前的更新将会被取消

PostgreSQL 实际上将每一个 SQL 语句都作为一个事务来执行。如果我们没有发出 BEGIN 命令，则每个独立的语句都会被加上一个隐式的 BEGIN 以及（如果成功）COMMIT 来包围它。一组被 BEGIN 和 COMMIT 包围的语句也被称为一个事务块。

保存点

保存点允许我们有选择性地放弃事务的一部分而提交剩下的部分。在使用 SAVEPOINT 定义一个保存点后，我们可以在必要时利用 ROLLBACK TO 回滚到该保存点。

在回滚到保存点之后，定义的保存点依然存在，因此我们可以多次回滚到它。

BEGIN;
UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;

窗口函数

一个窗口函数，在一系列与当前行有某种关联的表行上执行一种计算。

窗口函数只允许出现在查询的SELECT列表和ORDER BY子句中。它们不允许出现在其他地方，例如GROUP BY、HAVING和WHERE子句中。

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

按照部门进行分组，然后按照薪资进行降序排序

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
-- rank 不需要显式参数，因为其行为完全由 OVER 子句确定。
--   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

-- 如果缺少 PARTITION BY 则和整个表一样，因此我们为每一个输出行得到的都是相同的结果
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)
-- 如果在此基础上加上 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)

继承

我们创建两个表，一个是城市（cities），另一个是首都（capitals），聪明的我们，想出了以下方案

CREATE TABLE capitals (
  name       text,
  population real,
  elevation  int,    -- (in ft)
  state      char(2)
);

CREATE TABLE non_capitals (
  name       text,
  population real,
  elevation  int     -- (in ft)
);

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

查询的时候，是正常的，当我们需要更新一些行时，就不那么好用了，更好的方案是使用 INHERITS 继承

CREATE TABLE cities (
  name       text,
  population real,
  elevation  int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2) UNIQUE NOT NULL
) INHERITS (cities);
-- capitals 表有一个附加列，state，用于显示对应州缩写。