一、簡介
在之前的章節(jié)里我們已經(jīng)涉及了使用SQL在PostgreSQL中存儲和訪問數(shù)據(jù)的基礎(chǔ)知識?，F(xiàn)在我們將要討論SQL中一些更高級的特性，這些特性有助于簡化管理和防止數(shù)據(jù)丟失或損壞。最后，我們還將介紹一些PostgreSQL擴(kuò)展。
本章有時將引用教程（三）中的例子并對其進(jìn)行改變或改進(jìn)以便于閱讀本章。本章中的某些例子可以在教程目錄的advanced.sql文件中找到。該文件也包含一些樣例數(shù)據(jù)，在這里就不在贅述（查看教程（三）第一節(jié)了解如何使用該文件）。
二、視圖
回想一下教程（三）第六節(jié)中的查詢。假設(shè)天氣記錄和城市位置的組合列表對我們的應(yīng)用有用，但我們又不想每次需要使用它時都敲入整個查詢。我們可以在該查詢上創(chuàng)建一個視圖，這會給該查詢一個名字，我們可以像使用一個普通表一樣來使用它：
創(chuàng)建視圖：
CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name;
查看當(dāng)前數(shù)據(jù)庫的表：
\d
查詢視圖的數(shù)據(jù)：
select * from myview;
對視圖的使用是成就一個好的SQL數(shù)據(jù)庫設(shè)計的關(guān)鍵方面。視圖允許用戶通過始終如一的接口封裝的結(jié)構(gòu)細(xì)節(jié)，這樣可以避免表結(jié)構(gòu)隨著應(yīng)用的進(jìn)行而變化。
視圖幾乎可以用在任何可以使用表的地方。在其他視圖基礎(chǔ)上創(chuàng)建視圖也并不少見。
三、外鍵
回想教程（三）中的weather和cities表?？紤]以下問題：我們希望確保在cities表中有相應(yīng)項之前任何人都不能在weather表中插入行。這叫做維持?jǐn)?shù)據(jù)的引用完整性。在過分簡化的數(shù)據(jù)庫系統(tǒng)中，可以通過線檢查cities表中是否有匹配的記錄存在，然后決定應(yīng)該接受還是拒絕即將插入weather表的行。這種方法有一些問題且并不方便，于是PostgreSQL可以為我們來解決。
新的表定義如下：
創(chuàng)建cities表：
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
創(chuàng)建weather表：
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
查看表：
\d
現(xiàn)在嘗試插入一個非法的記錄：
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ù)應(yīng)用來調(diào)整。我們不會再這個教程里更深入地介紹，讀者可以參考第?5?章中的信息。正確使用外鍵無疑會提高數(shù)據(jù)庫應(yīng)用的質(zhì)量，因此強(qiáng)烈建議用戶學(xué)會如何使用它們。
四、事務(wù)
事務(wù)是所有數(shù)據(jù)庫系統(tǒng)的基礎(chǔ)概念。事務(wù)最重要的一點是它將多個步驟捆綁成了一個單一的、要么全完成要么全不完成的操作。步驟之間的中間狀態(tài)對于其他并發(fā)事務(wù)是不可見的，并且如果有某些錯誤發(fā)生導(dǎo)致事務(wù)不能完成，則其中任何一個步驟都不會對數(shù)據(jù)庫造成影響。
例如，考慮一個保存著多個客戶賬戶余額和支行總存款額的銀行數(shù)據(jù)庫。假設(shè)我們希望記錄一筆從Alice的賬戶到Bob的賬戶的額度為100美金的轉(zhuǎn)賬。在最大程度地簡化后，涉及到的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é)在這里并不重要，關(guān)鍵點是為了完成這個相當(dāng)簡單的操作涉及到多個獨立的更新。我們的銀行職員希望確保這些更新要么全部發(fā)生，或者全部不發(fā)生。當(dāng)然不能發(fā)生因為系統(tǒng)錯誤導(dǎo)致Bob收到100美元而Alice并未被扣款的情況。Alice當(dāng)然也不希望自己被扣款而Bob沒有收到錢。我們需要一種保障，當(dāng)操作中途某些錯誤發(fā)生時已經(jīng)執(zhí)行的步驟不會產(chǎn)生效果。將這些更新組織成一個事務(wù)就可以給我們這種保障。一個事務(wù)被稱為是原子的：從其他事務(wù)的角度來看，它要么整個發(fā)生要么完全不發(fā)生。
我們同樣希望能保證一旦一個事務(wù)被數(shù)據(jù)庫系統(tǒng)完成并認(rèn)可，它就被永久地記錄下來且即便其后發(fā)生崩潰也不會被丟失。例如，如果我們正在記錄Bob的一次現(xiàn)金提款，我們當(dāng)然不希望他剛走出銀行大門，對他賬戶的扣款就消失。一個事務(wù)型數(shù)據(jù)庫保證一個事務(wù)在被報告為完成之前它所做的所有更新都被記錄在持久存儲（即磁盤）。
事務(wù)型數(shù)據(jù)庫的另一個重要性質(zhì)與原子更新的概念緊密相關(guān)：當(dāng)多個事務(wù)并發(fā)運(yùn)行時，每一個都不能看到其他事務(wù)未完成的修改。例如，如果一個事務(wù)正忙著總計所有支行的余額，它不會只包括Alice的支行的扣款而不包括Bob的支行的存款，或者反之。所以事務(wù)的全做或全不做并不只體現(xiàn)在它們對數(shù)據(jù)庫的持久影響，也體現(xiàn)在它們發(fā)生時的可見性。一個事務(wù)所做的更新在它完成之前對于其他事務(wù)是不可見的，而之后所有的更新將同時變得可見。
在PostgreSQL中，開啟一個事務(wù)需要將SQL命令用BEGIN和COMMIT命令包圍起來。因此我們的銀行事務(wù)看起來會是這樣：
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- etc etc
COMMIT;
如果，在事務(wù)執(zhí)行中我們并不想提交（或許是我們注意到Alice的余額不足），我們可以發(fā)出ROLLBACK命令，而不是COMMIT命令，這樣所有目前的更新將會被取消。
PostgreSQL實際上將每一個SQL語句都作為一個事務(wù)來執(zhí)行。如果我們沒有發(fā)出BEGIN命令，則每個獨立的語句都會被加上一個隱式的BEGIN以及（如果成功）COMMIT來包圍它。一組被BEGIN和COMMIT包圍的語句也被稱為一個事務(wù)塊。
Notes：
某些客戶端庫會自動發(fā)出BEGIN和COMMIT命令，因此我們可能會在不被告知的情況下得到事務(wù)塊的效果。具體請查看所使用的接口文檔。
也可以利用保存點來以更細(xì)的粒度開控制一個事務(wù)中的語句。保存點允許我們有選擇性地放棄事務(wù)的一部分而提交剩下的部分。在使用SAVEPOINT定義一個保存點后，我們可以在必要時利用ROLLBACK TO回滾到該保存點。該事務(wù)中位于保存點和回滾點之間的數(shù)據(jù)庫修改都會被放棄，但是早于該保存點的修改則會被保存。
在回滾到保存點之后，它的定義依然存在，因此我們可以多次回滾到它。反過來，如果確定不再需要回滾到特定的保存點，它可以被釋放以便系統(tǒng)釋放一些資源。記住不管是釋放保存點還是回滾到保存點都會釋放定義在該保存點之后的所有其他保存點。
所有這些都發(fā)生在一個事務(wù)塊內(nèi)，因此這些對于其他數(shù)據(jù)庫會話都不可見。當(dāng)提交整個事務(wù)塊時，被提交的動作將作為一個單元變得對其他會話可見，而被回滾的動作則永遠(yuǎn)不會變得可見。
記住那個銀行數(shù)據(jù)庫，假設(shè)我們從Alice的賬戶扣款100美元，然后存款到Bob的賬戶，結(jié)果直到最后才發(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';
-- oops ... forget that and use Wally's account
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;當(dāng)然，這個例子是被過度簡化的，但是在一個事務(wù)塊中使用保存點存在很多種控制可能性。此外，ROLLBACK TO是唯一的途徑來重新控制一個由于錯誤被系統(tǒng)置為中斷狀態(tài)的事務(wù)塊，而不是完全回滾它并重新啟動。
五、窗口函數(shù)
一個窗口函數(shù)在一系列與當(dāng)前行有某種關(guān)聯(lián)的表行上執(zhí)行一種計算。這與一個聚集函數(shù)所完成的計算有可比之處。但是窗口函數(shù)并不會使多行被聚集成一個單獨的輸出行，這與通常的非窗口聚集函數(shù)不同。取而代之，行保留它們獨立的標(biāo)識。在這些現(xiàn)象背后，窗口函數(shù)可以訪問的不僅僅是查詢結(jié)果的當(dāng)前行。
下面是一個例子用于展示如何將每一個員工的薪水與他/她所在部門的平均薪水進(jìn)行比較：
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，并且表中每一行都有一個輸出行。第四列表示對與當(dāng)前行具有相同depname值的所有表行取得平均值（這實際和非窗口avg聚集件數(shù)是相同的函數(shù)，但是OVER子句使得它被當(dāng)做一個窗口函數(shù)處理并在一個格式的窗口幀上計算。）。
一個窗口函數(shù)調(diào)用總是包含一個直接跟在窗口函數(shù)名及其參數(shù)之后的OVER子句。這使得它從句法上和一個普通函數(shù)或非窗口函數(shù)區(qū)分開來。OVER子句決定究竟查詢中的哪些行被分離出來由窗口函數(shù)處理。OVER子句中的PARTITION BY子句指定了將具有相同PARTITION BY表達(dá)式值的行分到組或者分區(qū)。對于每一行，窗口函數(shù)都會在當(dāng)前行同一分區(qū)的行上進(jìn)行計算。
我們可以通過OVER上的ORDER BY控制窗口函數(shù)處理行的順序（窗口的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ù)在當(dāng)前行的分區(qū)內(nèi)按照ORDER BY子句的順序為每一個可區(qū)分的ORDER BY值產(chǎn)生了一個數(shù)字等級。rank不需要顯式的參數(shù)，因為它的行為完全決定于OVER子句。
一個窗口函數(shù)所考慮的行屬于那些通過查詢的FROM子句產(chǎn)生并通過WHERE、GROUP BY、HAVING過濾的“虛擬表”。例如，一個由于不滿足WHERE條件被刪除的行是不會被任何窗口函數(shù)所見的。在一個查詢中可以包含多個窗口函數(shù)，每個窗口函數(shù)都可以用不同的OVER子句來按不同方式劃分?jǐn)?shù)據(jù)，但是它們都作用在由虛擬表定義的同一個行集上。
我們已經(jīng)看到如果行的順序不重要時ORDER BY可以忽略。PARTITION BY同樣也可以被忽略，在這種情況下會產(chǎn)生一個包含所有行的分區(qū)。
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，窗口幀和分區(qū)一樣，而如果缺少PARTITION BY則和整個表一樣。換句話說，每個合計都會在整個表上進(jìn)行，這樣我們?yōu)槊恳粋€輸出行得到的都是相同的結(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)前行相同的行（注意相同薪水行的結(jié)果）。
窗口函數(shù)只允許出現(xiàn)在查詢的SELECT列表和ORDER BY子句中。它們不允許出現(xiàn)在其他地方，例如GROUP BY、HAVING和WHERE子句中。這是因為窗口函數(shù)的執(zhí)行邏輯是在處理完這些子句之后。另外，窗口函數(shù)在非窗口聚集函數(shù)之后執(zhí)行。這意味著可以在窗口函數(shù)的參數(shù)中包括一個聚集函數(shù)，但反過來不行。
如果需要在窗口計算執(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)層查詢中rank低于3的結(jié)果。
當(dāng)一個查詢涉及到多個窗口函數(shù)時，可以將每一個分別寫在一個獨立的OVER子句中。但如果多個函數(shù)要求同一個窗口行為時，這種做法是冗余的而且容易出錯的。替代方案是，每一個窗口行為可以被放在一個命名的WINDOW子句中，然后在OVER中引用它。例如：
SELECT sum(salary) OVER w, avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);六、繼承
繼承是面向?qū)ο髷?shù)據(jù)庫中的概念。它展示了數(shù)據(jù)庫設(shè)計的新的可能性。
讓我們創(chuàng)建兩個表：表cities和表capitals。自然地，首都也是城市，所以我們需要有某種方式能夠在列舉所有城市的時候也隱式地包含首都。如果真的聰明，我們會設(shè)計如下的模式：
CREATE TABLE capitals (
name text,
population real,
altitude int, -- (in ft)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
altitude int -- (in ft)
);
CREATE VIEW view_cities AS
SELECT name, population, altitude FROM capitals
UNION
SELECT name, population, altitude FROM non_capitals;這個模式對于查詢而言工作正常，但是當(dāng)我們需要更新一些行時它就變得不好用了。
更好的方案是：
# 創(chuàng)建表cities
CREATE TABLE cities (
name text,
population real,
altitude int -- (in ft)
);
# 創(chuàng)建表 capitals（繼承cities）
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
# 插入測試數(shù)據(jù)
insert into cities values('Las Vegas', 1, 2174);
insert into cities values('Mariposa', 2, 1953);
insert into cities values('Madison', 3, 845);
insert into capitals values('MIaMI', 4, 2345, 'ma');
insert into capitals values('Dalas', 5, 3355, 'dl');在這種情況下，一個capitals的行從它的父親cities繼承了所有列（name、population和altitude）。列name的類型是text，一種用于變長字符串的本地PostgreSQL類型。州首都有一個附加列state用于顯示它們的州。在PostgreSQL中，一個表可以從0個或者多個表繼承。
例如，如下查詢可以尋找所有海拔500尺以上的城市名稱，包括州首都：
SELECT name, altitude
FROM cities
WHERE altitude > 500;
它的返回結(jié)果：
? ?name ? ?| altitude?
-----------+----------
?Las Vegas | ? ? 2174
?Mariposa ?| ? ? 1953
?Madison ? | ? ? ?845
?MIaMI ? ? | ? ? 2345
?Dalas ? ? | ? ? 3355
(5 rows)
在另一方面，下面的查詢可以查找所有海拔高于500尺且不是州首府的城市：
SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;
? ?name ? ?| altitude?
-----------+----------
?Las Vegas | ? ? 2174
?Mariposa ?| ? ? 1953
?Madison ? | ? ? ?845
(3 rows)
其中cities之前的ONLY用于指示查詢只在cities表上進(jìn)行而不會涉及到繼承層次中位于cities之下的其他表。很多我們已經(jīng)討論過的命令 —?SELECT、UPDATE?和DELETE?— 都支持這個ONLY記號。
Notes：
? ? ? ? 盡管繼承很有用，但是它還未與唯一約束或外鍵集成，這也限制了它的可用性。
七、小結(jié)
PostgreSQL中有很多特性在這個面向SQL新用戶的教程中并未觸及。有關(guān)這些特性的更多詳情將在本書的后續(xù)部分進(jìn)行討論。文章來源：http://www.zghlxwxcb.cn/news/detail-833472.html
如果需要更多介紹材料，請訪問 PostgreSQL?官方網(wǎng)站來獲得更多資源鏈接。文章來源地址http://www.zghlxwxcb.cn/news/detail-833472.html
到了這里，關(guān)于PostgreSQL教程（四）：高級特性的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
