PosegreSQL基礎回顧(第 6 章數據操縱、第 7 章查詢)

來源：http://www.postgres.cn/docs/11/ 6.4. 從修改的行中返回數據有時在修改行的操作過程中獲取數據很有用。INSERT、 UPDATE和DELETE命令都有一個支持這個的可選的 RETURNING子句。使用RETURNING 可以避免執行額外的資料庫查詢來收集數據 ...

來源：http://www.postgres.cn/docs/11/

6.4. 從修改的行中返回數據

有時在修改行的操作過程中獲取數據很有用。INSERT、 UPDATE和DELETE命令都有一個支持這個的可選的 RETURNING子句。使用RETURNING 可以避免執行額外的資料庫查詢來收集數據，並且在否則難以可靠地識別修改的行時尤其有用。

如果目標表上有觸發器(第 39 章)，可用於RETURNING 的數據是被觸發器修改的行。因此，檢查由觸發器計算的列是 RETURNING的另一個常見用例。

7.2.1.1. 連接表

交叉連接

T1 CROSS JOIN T2

對來自於T1和T2的行的每一種可能的組合（即笛卡爾積），連接表將包含這樣一行：它由所有T1裡面的列後面跟著所有T2裡面的列構成。如果兩個表分別有 N 和 M 行，連接表將有 N * M 行。FROM T1 CROSS JOIN T2等效於FROM T1 INNER JOIN T2 ON TRUE（見下文）。它也等效於FROM T1,T2。

註意

當多於兩個表出現時，後一種等效並不嚴格成立，因為JOIN比逗號綁得更緊。例如FROM T1 CROSS JOIN T2 INNER JOIN T3 ON condition和FROM T1,T2 INNER JOIN T3 ON condition並不完全相同，因為第一種情況中的condition可以引用T1，但在第二種情況中卻不行。

條件連接

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

INNER和OUTER對所有連接形式都是可選的。INNER是預設；LEFT、RIGHT和FULL指示一個外連接。

連接條件在ON或USING子句中指定，或者用關鍵字NATURAL隱含地指定。連接條件決定來自兩個源表中的哪些行是“匹配”的，這些我們將在後文詳細解釋。

可能的條件連接類型是：

INNER JOIN: 對於 T1 的每一行 R1，生成的連接表都有一行對應 T2 中的每一個滿足和 R1 的連接條件的行。
LEFT OUTER JOIN: 首先，執行一次內連接。然後，為 T1 中每一個無法在連接條件上匹配 T2 里任何一行的行返回一個連接行，該連接行中 T2 的列用空值補齊。因此，生成的連接表裡為來自 T1 的每一行都至少包含一行。
RIGHT OUTER JOIN: 首先，執行一次內連接。然後，為 T2 中每一個無法在連接條件上匹配 T1 里任何一行的行返回一個連接行，該連接行中 T1 的列用空值補齊。因此，生成的連接表裡為來自 T2 的每一行都至少包含一行。
FULL OUTER JOIN: 首先，執行一次內連接。然後，為 T1 中每一個無法在連接條件上匹配 T2 里任何一行的行返回一個連接行，該連接行中 T2 的列用空值補齊。同樣，為 T2 中每一個無法在連接條件上匹配 T1 里任何一行的行返回一個連接行，該連接行中 T1 的列用空值補齊。

USING是個縮寫符號，它允許你利用特殊的情況：連接的兩端都具有相同的連接列名。它接受共用列名的一個逗號分隔列表，並且為其中每一個共用列構造一個包含等值比較的連接條件。例如用USING (a, b)連接T1和T2會產生連接條件ON T1.a = T2.a AND T1.b = T2.b。

更進一步，JOIN USING的輸出會廢除冗餘列：不需要把匹配上的列都列印出來，因為它們必須具有相等的值。不過JOIN ON會先產生來自T1的所有列，後面跟上所有來自T2的列；而JOIN USING會先為列出的每一個列對產生一個輸出列，然後先跟上來自T1的剩餘列，最後跟上來自T2的剩餘列。

最後，NATURAL是USING的縮寫形式：它形成一個USING列表，該列表由那些在兩個表裡都出現了的列名組成。和USING一樣，這些列只在輸出表裡出現一次。如果不存在公共列，NATURAL JOIN的行為將和JOIN ... ON TRUE一樣產生交叉集連接。

註意

USING對於連接關係中的列改變是相當安全的，因為只有被列出的列會被組合成連接條件。NATURAL的風險更大，因為如果其中一個關係的模式改變會導致出現一個新的匹配列名，就會導致連接將新列也組合成連接條件。

例

7.2.1.2. 表和列別名

別名成為當前查詢的表引用的新名稱 — 我們不再能夠用該表最初的名字引用它了。因此，下麵的用法是不合法的：

SELECT * FROM my_table AS m WHERE my_table.a > 5;    -- 錯誤

表別名主要用於簡化符號，但是當把一個表連接到它自身時必須使用別名，例如：

SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;

7.2.1.3. 子查詢

子查詢指定了一個派生表，它必須被包圍在圓括弧里並且必須被賦予一個表別名。

一個子查詢也可以是一個VALUES列表：FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow')) AS names(first, last)

再次的，這裡要求一個表別名。為VALUES列表中的列分配別名是可選的，但是選擇這樣做是一個好習慣。

7.2.1.4. 表函數

表函數是那些生成一個行集合的函數，這個集合可以是由基本數據類型（標量類型）組成，也可以是由複合數據類型（表行）組成。

7.2.3. `GROUP BY`和`HAVING`子句

在嚴格的 SQL 里，GROUP BY只能對源表的列進行分組，但PostgreSQL把這個擴展為也允許GROUP BY去根據選擇列表中的列分組。也允許對值表達式進行分組，而不僅是簡單的列名。

7.2.4. `GROUPING SETS`、`CUBE`和`ROLLUP`

使用分組集的概念可以實現比上述更加複雜的分組操作。由 FROM和WHERE子句選出的數據被按照每一個指定的分組集單獨分組，按照簡單GROUP BY子句對每一個分組計算聚集，然後返回結果。

7.2.5. 視窗函數處理

如果查詢包含任何視窗函數（見第 3.5 節、第 9.21 節和第 4.2.8 節），這些函數將在任何分組、聚集和HAVING過濾被執行之後被計算。也就是說如果查詢使用了任何聚集、GROUP BY或HAVING，則視窗函數看到的行是分組行而不是來自於FROM/WHERE的原始表行。

7.3.3. `DISTINCT`

如果兩行里至少有一個列有不同的值，那麼我們認為它是可區分的。空值在這種比較中被認為是相同的。

7.4. 組合查詢

兩個查詢的結果可以用集合操作並、交、差進行組合。

為了計算兩個查詢的並、交、差，這兩個查詢必須是“並操作相容的”，也就意味著它們都返回同樣數量的列，並且對應的列有相容的數據類型，如第 10.5 節中描述的那樣。

7.5. 行排序

NULLS FIRST和NULLS LAST選項將可以被用來決定在排序順序中，空值是出現在非空值之前或者出現在非空值之後。

預設情況下，排序時空值被認為比任何非空值都要大，即NULLS FIRST是DESC順序的預設值，而不是NULLS LAST的預設值。

註意順序選項是對每一個排序列獨立考慮的。例如ORDER BY x, y DESC表示ORDER BY x ASC, y DESC，而和ORDER BY x DESC, y DESC不同。

ORDER BY可以被應用於UNION、INTERSECT或EXCEPT組合的結果，但是在這種情況中它只被允許根據輸出列名或編號排序，而不能根據表達式排序。

7.6. `LIMIT`和`OFFSET`

LIMIT ALL的效果和省略LIMIT子句一樣，就像是LIMIT帶有 NULL 參數一樣。

OFFSET說明在開始返回行之前忽略多少行。OFFSET 0的效果和省略OFFSET子句是一樣的，就像是OFFSET帶有 NULL 參數一樣。

如果OFFSET和LIMIT都出現了，那麼在返回LIMIT個行之前要先忽略OFFSET行。

如果使用LIMIT，那麼用一個ORDER BY子句把結果行約束成一個唯一的順序是很重要的。否則你就會拿到一個不可預料的該查詢的行的子集。你要的可能是第十到第二十行，但以什麼順序的第十到第二十？除非你指定了ORDER BY，否則順序是不知道的。

7.8. `WITH`查詢（公共表表達式）

WITH提供了一種方式來書寫在一個大型查詢中使用的輔助語句。這些語句通常被稱為公共表表達式或CTE，它們可以被看成是定義只在一個查詢中存在的臨時表。在WITH子句中的每一個輔助語句可以是一個SELECT、INSERT、UPDATE或DELETE，並且WITH子句本身也可以被附加到一個主語句，主語句也可以是SELECT、INSERT、UPDATE或DELETE。

7.8.1. `WITH`中的`SELECT`

遞歸：可選的RECURSIVE修飾符將WITH從單純的句法便利變成了一種在標準SQL中不能完成的特性。通過使用RECURSIVE，一個WITH查詢可以引用它自己的輸出。

7.8.2. `WITH`中的數據修改語句

你可以在WITH中使用數據修改語句（INSERT、UPDATE或DELETE）。

如果一個WITH中的數據修改語句缺少一個RETURNING子句，則它形不成臨時表並且不能在剩餘的查詢中被引用。

PosegreSQL基礎回顧(第 6 章 數據操縱、第 7 章 查詢)