3.1 介紹

我們現(xiàn)在將討論一些SQL更高級的特性纤泵， 這些特性可以簡化管理和避免數(shù)據(jù)的丟失或損壞。最后镜粤，我們將看看一些PostgreSQL 的擴(kuò)展捏题。

3.2 視圖

假設(shè)你的應(yīng)用對天氣記錄和城市位置的 組合列表特別感興趣，而你又不想每次鍵入這些查詢繁仁。那么你可以在這個查詢上創(chuàng)建一個視圖涉馅， 它給這個查詢一個名字，你可以像普通表那樣引用它黄虱。

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

SELECT * FROM myview;

自由地運用視圖是好的 SQL 數(shù)據(jù)庫設(shè)計的一個關(guān)鍵要素稚矿。視圖允許我們把表結(jié)構(gòu)的細(xì)節(jié)封裝起來， 這些表可能隨你的應(yīng)用進(jìn)化而變化捻浦，但這些變化卻可以隱藏在一個一致的接口后面晤揣。

視圖幾乎可以在一個真正的表可以使用的任何地方使用。在其它視圖上面再創(chuàng)建視圖也并非罕見朱灿。

3.3 外健

考慮下面的問題：你想確保沒有人可以在weather表里插入一條在cities 表里沒有匹配記錄的數(shù)據(jù)行昧识。這就叫維護(hù)表的參照完整性。

在簡單的數(shù)據(jù)庫系統(tǒng)里盗扒， 實現(xiàn)(如果也叫實現(xiàn))這個特性的方法通常是先看看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".

正確使用外鍵無疑將改進(jìn)你的數(shù)據(jù)庫應(yīng)用，所以我們強(qiáng)烈建議你學(xué)習(xí)它們池户。

3.4 事務(wù)

事務(wù)是所有數(shù)據(jù)庫系統(tǒng)的一個基本概念咏雌。一次事務(wù)的要點就是把多個步驟捆綁成一個單一的、 不成功則成仁的操作校焦。其它并發(fā)的事務(wù)是看不到在這些步驟之間的中間狀態(tài)的赊抖，并且如果發(fā)生了一些問題， 導(dǎo)致該事務(wù)無法完成寨典，那么所有這些步驟都完全不會影響數(shù)據(jù)庫氛雪。

比如，假設(shè)一個銀行的數(shù)據(jù)庫包含各種客戶帳戶的余額凝赛，以及每個分行的總余額注暗。 假設(shè)我們要記錄一次從 Alice 的帳戶到 Bob 的帳戶的金額為 $100.00 的支付動作。 那么墓猎，完成這個任務(wù)的簡單到極點的 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');

這些命令的細(xì)節(jié)在這兒并不重要捆昏；重要的是這里牽涉到了好幾個獨立的更新來完成這個相當(dāng)簡單的操作。 銀行官員會希望要么所有這些更新全部生效毙沾，要么全部不起作用骗卜。我們當(dāng)然不希望一次系統(tǒng)崩潰就導(dǎo)致 Bob 收到 100 塊不是 Alice 支付的錢，也不希望 Alice 老是花錢卻不能從 Bob 那里拿到物品左胞。

我們需要保證：如果在操作的過程中出了差錯寇仓，那么所有這些步驟都不會發(fā)生效果。 把這些更新組合成一個事務(wù)就給予我們這樣的保證烤宙。事務(wù)被認(rèn)為是原子的： 從其它事務(wù)的角度來看遍烦，它要么是全部發(fā)生，要么完全不發(fā)生躺枕。**

我們還需要保證：一旦一個事務(wù)完成并且得到數(shù)據(jù)庫系統(tǒng)的認(rèn)可服猪，那么它必須被真正永久地存儲， 并且不會在隨后的崩潰中消失拐云。比如罢猪，如果我們記錄到了一個 Bob 撤單的動作， 那么我們不希望僅僅在他走出銀行大門之后的一次崩潰就會導(dǎo)致對他的帳戶的扣減動作消失叉瘩。 一個事務(wù)型數(shù)據(jù)庫保證一個事務(wù)所做的所有更新在事務(wù)發(fā)出完成響應(yīng)之前都記錄到永久的存儲中(也就是磁盤)膳帕。

事務(wù)型數(shù)據(jù)庫的另外一個重要的性質(zhì)和原子更新的概念關(guān)系密切：當(dāng)多個事務(wù)并發(fā)地運行的時候， 每個事務(wù)都不應(yīng)看到其它事務(wù)所做的未完成的變化薇缅。比如危彩，如果一個事務(wù)正忙著計算所有分行的余額總和攒磨， 那么它不應(yīng)該包括來自 Alice 的分行的扣帳和來自 Bob 分行的入帳，反之亦然恬砂。所以事務(wù)必須是黑白分明的咧纠， 不僅僅體現(xiàn)在它們在數(shù)據(jù)庫上產(chǎn)生的永久影響上，而且體現(xiàn)在它們運轉(zhuǎn)時的自身的可視性上泻骤。 一個打開的事務(wù)所做的更新在它完成之前是無法被其它事務(wù)看到的，而到提交的時候所有更新同時可見梧奢。

在PostgreSQL里狱掂，一個事務(wù)是通過把 SQL 命令用BEGIN和COMMIT 命令包圍實現(xiàn)的。因此我們的銀行事務(wù)實際上看起來像下面這樣：

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

如果在該事務(wù)的過程中亲轨，我們決定不做提交(可能是我們剛發(fā)現(xiàn) Alice 的余額是負(fù)數(shù))趋惨， 那么我們可以發(fā)出ROLLBACK而不是COMMIT命令， 那么到目前為止我們的所有更新都會被取消惦蚊。

PostgreSQL 實際上把每個 SQL 語句當(dāng)做在一個事務(wù)中執(zhí)行來看待器虾。 如果你沒有發(fā)出BEGIN命令，那么每個獨立的語句都被一個隱含的BEGIN 和(如果成功的話)COMMIT包圍蹦锋。一組包圍在BEGIN和COMMIT 之間的語句有時候被稱做事務(wù)塊兆沙。

注意: 一些客戶端庫自動發(fā)出BEGIN和COMMIT， 因此你可能不需要特意請求就可以獲得事務(wù)塊的效果莉掂。查看你使用的接口的文檔葛圃。

我們可以通過使用保存點的方法，在一個事務(wù)里更加精細(xì)地控制其中的語句憎妙。 保存點允許你選擇性地拋棄事務(wù)中的某些部分库正，而提交剩下的部分。在用SAVEPOINT 定義了一個保存點后厘唾，如果需要褥符，你可以使用ROLLBACK TO回滾到該保存點。 則該事務(wù)在定義保存點到 ROLLBACK TO 之間的所有數(shù)據(jù)庫更改都被拋棄抚垃， 但是在保存點之前的修改將被保留喷楣。

在回滾到一個保存點之后，這個保存點仍然保存著其定義讯柔，所以你可以回滾到這個位置好幾次抡蛙。 當(dāng)然，如果你確信你不需要再次回滾到一個保存點魂迄，那么你可以釋放它粗截，這樣系統(tǒng)可以釋放一些資源。 要記椎肪妗：釋放或者回滾到一個保存點都會自動釋放在其后定義的所有保存點熊昌。

所有這些都發(fā)生在一個事務(wù)塊內(nèi)部绽榛，所以所有這些都不可能被其它事務(wù)會話看到。 當(dāng)且僅當(dāng)你提交了這個事務(wù)塊婿屹，這些提交了的動作才能以一個單元的方式被其它會話看到灭美， 而回滾的動作完全不會被看到。

記得我們的銀行數(shù)據(jù)庫嗎? 假設(shè)我們從 Alice 的帳戶上消費 $100.00 昂利，然后給 Bob 的帳戶進(jìn)行加款届腐， 稍后我們發(fā)現(xiàn)我們應(yīng)該給 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';
-- 呀蜂奸！加錯錢了犁苏，應(yīng)該用 Wally 的賬號
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Wally';
COMMIT;

這個例子當(dāng)然是實在太簡單了，但是通過使用保存點扩所，我們可以對事務(wù)塊有大量的控制围详。 并且，ROLLBACK TO是除了事務(wù)全部回滾祖屏，重新來過之外助赞， 唯一可以用于重新控制一個因錯誤而被系統(tǒng)置于退出狀態(tài)事務(wù)的方法。

3.5 窗口函數(shù)

窗口函數(shù)在和當(dāng)前行相關(guān)的一組表行上執(zhí)行計算袁勺。 這相當(dāng)于一個可以由聚合函數(shù)完成的計算類型雹食。但不同于常規(guī)的聚合函數(shù)， 使用的窗口函數(shù)不會導(dǎo)致行被分組到一個單一的輸出行魁兼；行保留其獨立的身份婉徘。 在后臺，窗口函數(shù)能夠訪問的不止查詢結(jié)果的當(dāng)前行咐汞。

這里是一個例子盖呼，說明如何比較每個員工的工資和在他或她的部門的平均工資：

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值的表行的平均值作為當(dāng)前值几晤。 （這實際上與標(biāo)準(zhǔn)avg聚合函數(shù)的功能相同， 但是OVER子句使其被視為一個窗口函數(shù)并在一組合適的行上執(zhí)行計算植阴。）

窗口函數(shù)的調(diào)用總是包含一個OVER子句蟹瘾，后面直接跟著窗口函數(shù)的名稱和參數(shù)。 這是它在語法上區(qū)別于普通函數(shù)或聚合功能的地方掠手。 OVER子句決定如何將查詢的行進(jìn)行拆分以便給窗口函數(shù)處理憾朴。 OVER子句內(nèi)的PARTITION BY列表指定將行劃分成組或分區(qū)， 組或分區(qū)共享相同的PARTITION BY表達(dá)式的值喷鸽。 對于每一行众雷，窗口函數(shù)在和當(dāng)前行落在同一個分區(qū)的所有行上進(jìn)行計算。

你還可以使用窗口函數(shù)OVER內(nèi)的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函數(shù)按照由ORDER BY子句定義的順序， 在當(dāng)前行的分區(qū)里為每個不同的ORDER BY值產(chǎn)生了一個數(shù)值排名编兄。 rank 不需要明確的參數(shù)轩性，因為它的行為完全取決于OVER子句。

窗口函數(shù)的行來自查詢的FROM子句產(chǎn)生狠鸳，并且如果有的話揣苏， 經(jīng)過WHERE,GROUP BY和HAVING子句過濾的"虛擬表"。 比如碰煌，被移除掉的行舒岸，因為不符合WHERE條件，所以是不能被任何窗口函數(shù)可見的芦圾。 一個查詢可以包含多個窗口函數(shù)，通過不同的OVER子句用不同的方式分割數(shù)據(jù)俄认， 但是他們都作用在這個虛擬表定義的同一個行集合个少。

我們已經(jīng)看到了，如果行排序并不重要眯杏，ORDER BY可以省略夜焦。 在只有一個包含了所有行的分區(qū)情況下，也可以省略PARTITION BY岂贩。

還有一個與窗口函數(shù)相關(guān)的重要的概念：對于每一行茫经，有在其分區(qū)范圍內(nèi)的行集， 又稱為它的window frame萎津。許多（但不是全部）窗口函數(shù)卸伞，只作用于window frame中的行上楞卡， 而不是整個分區(qū)欲间。默認(rèn)情況下，如果使用ORDER BY烘绽， 那么這個frame包含從分區(qū)開始到當(dāng)前行的所有行颈渊，以及那些當(dāng)前行后面的遂黍，根據(jù)ORDER BY 子句等于當(dāng)前行的所有行，如果省略ORDER BY俊嗽，那么雾家，frame默認(rèn)包含分區(qū)中的所有行。 [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與分區(qū)(不使用PARTITION BY時即整個表)相同；換句話說唬党， 每一次sum求和都是使用表中所有的salary鹃共，所以我們得到的每個輸出行的結(jié)果相同。 但是驶拱，如果我們添加ORDER BY子句霜浴，我們會得到不同的結(jié)果：

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)

這里的總和是從第一個（最低）工資到當(dāng)前一個，包括任何當(dāng)前重復(fù)的（注意重復(fù)薪金的結(jié)果）蓝纲。

窗口函數(shù)僅允許在查詢的SELECT列表和ORDER BY子句中使用阴孟。 在其他地方禁止使用，比如GROUP BY,HAVING和WHERE子句税迷。 這是因為它們邏輯上在處理這些子句之后執(zhí)行永丝。此外，窗口函數(shù)在標(biāo)準(zhǔn)聚合函數(shù)之后執(zhí)行箭养。 這意味在一個窗口函數(shù)的參數(shù)中包含一個標(biāo)準(zhǔn)聚合函數(shù)的調(diào)用是有效的慕嚷，但反過來不行。

執(zhí)行窗口計算后毕泌，如果有必要對行進(jìn)行過濾或分組喝检，你可以使用子查詢。例如：

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;

上面的查詢只顯示內(nèi)部查詢結(jié)果中rank小于3的行撼泛。

當(dāng)查詢涉及多個窗口函數(shù)時挠说，可以寫成每一個都帶有單獨的OVER子句， 但是愿题，如果期待為多個窗口函數(shù)采用相同的窗口行為损俭，這樣做就會產(chǎn)生重復(fù)，并且容易出錯潘酗。 作為代替杆兵，每個窗口行為可以在WINDOW子句中進(jìn)行命名，然后再被OVER引用崎脉。 例如：

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

3.6 繼承

繼承是面向?qū)ο蟮臄?shù)據(jù)庫的概念拧咳。它開啟了數(shù)據(jù)庫設(shè)計的有趣的新的可能性。

讓我們創(chuàng)建兩個表：一個cities表和一個capitals表囚灼。 自然骆膝，首府(capital)也是城市(cities)，因此在列出所有城市時你想要某種方法隱含地顯示首府灶体。 如果你已經(jīng)很高明了阅签，那么你可能會創(chuàng)造類似下面這樣的模式：

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;

如果只是查詢，那么這個方法運轉(zhuǎn)得很好蝎抽，但是如果你需要更新某幾行政钟，那這個方法就很難看了路克。

一種更好的方法是：

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指示系統(tǒng)只對cities 表運行查詢，而不包括繼承級別中低于cities的表倒戏。 許多我們已經(jīng)討論過的命令—SELECT, UPDATE和 DELETE—都支持這個ONLY表示法怠噪。

注意: 盡管繼承經(jīng)常是有用的，但是它還沒有集成唯一約束或者外鍵杜跷，因此制約了其實用性傍念。