如何從數(shù)據(jù)庫中檢索數(shù)據(jù)
索數(shù)據(jù)的過程或命令叫做查詢
SELECT命令的一般語法是
[WITHwith_queries] SELECTselect_listFROMtable_expression[sort_specification]
通常，表表達(dá)式可以是基本表、連接和子查詢組成的復(fù)雜結(jié)構(gòu)
表表達(dá)式
1. 表表達(dá)式計(jì)算一個表。包含一個FROM子句，該子句后面可以根據(jù)需要選用WHERE、GROUP BY和HAVING子句，指定一系列對源自FROM子句的表的轉(zhuǎn)換操作，所有這些轉(zhuǎn)換最后生成一個虛擬表，它提供行傳遞給選擇列表計(jì)算查詢的輸出行。
2. 如果在FROM子句中引用了多于一個表， 那么它們被交叉連接（即構(gòu)造它們的行的笛卡爾積）
3. 連接表
T1join_typeT2[join_condition]
在JOIN子句周圍可以使用圓括號來控制連接順序。如果不使用圓括號，JOIN子句會從左至右嵌套。
交叉連接——自于T1和T2的行的每一種可能的組合（即笛卡爾積），連接表將包含這樣一行：它由所有T1里面的列后面跟著所有T2里面的列構(gòu)成。如果兩個表分別有 N 和 M 行，連接表將有 N * M 行。
T1?CROSS JOIN?T2
FROM?T1?CROSS JOIN?T2? <=>??FROM?T1?INNER JOIN?T2?ON TRUE? <=>? FROM?T1,T2
條件連接
T1{ [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINT2ONboolean_expression
T1{ [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINT2USING (join column list)
T1NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINT2
ON子句是最常見的連接條件的形式：它接收一個和WHERE子句里用的一樣的布爾值表達(dá)式。 如果兩個分別來自T1和T2的行在ON表達(dá)式上運(yùn)算的結(jié)果為真，那么它們就算是匹配的行。
USING是個縮寫符號，它允許你利用特殊的情況：連接的兩端都具有相同的連接列名。它接受共享列名的一個逗號分隔列表，并且為其中每一個共享列構(gòu)造一個包含等值比較的連接條件。例如用USING (a, b)連接T1和T2會產(chǎn)生連接條件ON?T1.a =?T2.a AND?T1.b =?T2.b。
JOIN USING的輸出會廢除冗余列：不需要把匹配上的列都打印出來，因?yàn)樗鼈儽仨毦哂邢嗟鹊闹怠２贿^JOIN ON會先產(chǎn)生來自T1的所有列，后面跟上所有來自T2的列；而JOIN USING會先為列出的每一個列對產(chǎn)生一個輸出列，然后先跟上來自T1的剩余列，最后跟上來自T2的剩余列。
NATURAL是USING的縮寫形式：它形成一個USING列表， 該列表由那些在兩個表里都出現(xiàn)了的列名組成。和USING一樣，這些列只在輸出表里出現(xiàn)一次。 如果不存在公共列，NATURAL JOIN的行為將和?JOIN ... ON TRUE一樣，產(chǎn)生交叉連接。
4. 表和列別名
FROM?table_reference [AS]?alias
別名成為當(dāng)前查詢的表引用的新名稱 — 不能用該表最初的名字引用它
如果一個表引用是一個子查詢，則必須要使用一個別名
5.子查詢——指定了一個派生表，它必須被包圍在圓括弧里并且必須被賦予一個表別名
FROM (SELECT * FROM table1) AS alias_name
一個子查詢也可以是一個VALUES列表：
FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
? ? AS names(first, last)//這里要求一個表別名
6.表函數(shù)——生成一個行集合的函數(shù)，這個集合可以是由基本數(shù)據(jù)類型（標(biāo)量類型）組成， 也可以是由復(fù)合數(shù)據(jù)類型（表行）組成
使用ROWS FROM語法將平行列返回的結(jié)果組合成表函數(shù)
7.where 子句——在完成對FROM子句的處理之后，生成的虛擬表的每一行都會對根據(jù)搜索條件進(jìn)行檢查
8.GROUP BY和HAVING子句
通過了WHERE過濾器之后，生成的輸入表可以使用GROUP BY子句進(jìn)行分組，然后用HAVING子句刪除一些分組行
GROUP BY?子句被用來把表中在所列出的列上具有相同值的行分組在一起
GROUPING SETS的每一個子列表可以指定一個或者多個列或者表達(dá)式
9. 窗口函數(shù)處理
窗口函數(shù)將在任何分組、聚集和HAVING過濾被執(zhí)行之后被計(jì)算
當(dāng)多個窗口函數(shù)被使用，所有在窗口定義中有句法上等效的PARTITION BY和ORDER BY子句的窗口函數(shù)被保證在數(shù)據(jù)上的同一趟掃描中計(jì)算。因此它們將會看到相同的排序順序，即使ORDER BY沒有唯一地決定一個順序。但是，對于具有不同PARTITION BY或ORDER BY定義的函數(shù)的計(jì)算沒有這種保證
選擇列表
在SELECT命令里的表表達(dá)式構(gòu)造了一個中間的虛擬表， 方法可能有組合表、視圖、消除行、分組等等。這個表最后被選擇列表傳遞下去處理。選擇列表判斷中間表的哪個列是實(shí)際輸出
1. 選擇列表項(xiàng)
值表達(dá)式為結(jié)果的每一行進(jìn)行一次計(jì)算，對任何列引用替換行的值。選擇列表中的這個表達(dá)式并非一定要引用來自FROM子句中表表達(dá)式里面的列，例如它也可以是任意常量算術(shù)表達(dá)式
2. 列標(biāo)簽
為了防止未來可能的關(guān)鍵詞增加，我們推薦總是寫AS或者用雙引號修飾輸出列名
3.DISTINCT——在處理完選擇列表之后，結(jié)果表可以可選的刪除重復(fù)行
可以用任意表達(dá)式來判斷什么行可以被認(rèn)為是可區(qū)分的
ELECT DISTINCT ON (expression[,expression...])select_list...
組合查詢
兩個查詢的結(jié)果可以用集合操作并、交、差進(jìn)行組合
query1?UNION? [ALL]?query2
query1?INTERSECT? [ALL]?query2
query1?EXCEPT? [ALL]?query2
集合操作也可以嵌套和級連
query1?UNION?query2?UNION?query3
<=>
(query1?UNION?query2) UNION?query3
1. UNION有效地把query2的結(jié)果附加到query1的結(jié)果上（不過我們不能保證這就是這些行實(shí)際被返回的順序）。此外，它將刪除結(jié)果中所有重復(fù)的行， 就象DISTINCT做的那樣，除非你使用了UNION ALL
2. INTERSECT返回那些同時存在于query1和query2的結(jié)果中的行，除非聲明了INTERSECT ALL， 否則所有重復(fù)行都被消除。
3. EXCEPT返回所有在query1的結(jié)果中但是不在query2的結(jié)果中的行（有時侯這叫做兩個查詢的差）。同樣的，除非聲明了EXCEPT ALL，否則所有重復(fù)行都被消除。
行排序
在一個查詢生成一個輸出表之后（在處理完選擇列表之后），可以選擇性地對它進(jìn)行排序
ORDER BY子句指定了排序順序
SELECT?select_list
? ??FROM?table_expression?
? ? ORDER BY?sort_expression1?[ASC | DESC] [NULLS { FIRST | LAST }]
? ? [,sort_expression2[ASC | DESC] [NULLS { FIRST | LAST }] ...]
當(dāng)多于一個表達(dá)式被指定，后面的值將被用于排序那些在前面值上相等的行
順序選項(xiàng)是對每一個排序列獨(dú)立考慮的
ORDER BY x, y DESC <=> ORDER BY x ASC, y DESC
一個sort_expression也可以是列標(biāo)簽或者一個輸出列的編號
SELECT a + b AS sum, c FROM table1 ORDER BY sum;
<=>
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;
一個輸出列的名字必須孤立，即它不能被用在一個表達(dá)式中
SELECT a + b AS sum, c FROM table1 ORDER BY sum + c; -- 錯誤
ORDER BY可以被應(yīng)用于UNION、INTERSECT或EXCEPT組合的結(jié)果，但是在這種情況中它只被允許根據(jù)輸出列名或編號排序，而不能根據(jù)表達(dá)式排序
LIMIT和OFFSET
LIMIT和OFFSET允許只檢索查詢剩余部分產(chǎn)生的行的一部分
SELECTselect_list
FROM?table_expression
[ ORDER BY ... ]?
?[LIMIT {number| ALL }] [OFFSETnumber]
OFFSET說明在開始返回行之前忽略多少行
先忽略O(shè)FFSET行再返回LIMIT個行
?VALUES列表
VALUES提供了一種生成“常量表”的方法，它可以被使用在一個查詢中而不需要實(shí)際在磁盤上創(chuàng)建一個表
VALUES (expression[, ...] ) [, ...]
VALUES (1, 'one'), (2, 'two'), (3, 'three');
<=>
SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';
通常最好使用表別名列表來重寫這些默認(rèn)的名字
=> SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
num | letter
-----+--------
? 1 | one
? 2 | two
? 3 | three
(3 rows)
WITH查詢（公共表表達(dá)式）
定義只在一個查詢中存在的臨時表。在WITH子句中的每一個輔助語句可以是一個SELECT、INSERT、UPDATE或DELETE，并且WITH子句本身也可以被附加到一個主語句，主語句也可以是SELECT、INSERT、UPDATE或DELETE
1.?WITH中SELECT的基本價值是將復(fù)雜的查詢分解稱為簡單的部分
WITH regional_sales AS (
? ? ? ? SELECT region, SUM(amount) AS total_sales
? ? ? ? FROM orders
? ? ? ? GROUP BY region
? ? ), top_regions AS (
? ? ? ? SELECT region
? ? ? ? FROM regional_sales
? ? ? ? WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
? ? )
SELECT region,
? ? ? product,
? ? ? SUM(quantity) AS product_units,
? ? ? SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
通過使用RECURSIVE，一個WITH查詢可以引用它自己的輸出
WITH RECURSIVE t(n) AS (
? ? VALUES (1)
? UNION ALL
? ? SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;//計(jì)算從1到100的整數(shù)合的查詢：
一個遞歸WITH查詢的通常形式總是一個非遞歸項(xiàng)，然后是UNION（或者UNION ALL），再然后是一個遞歸項(xiàng)，其中只有遞歸項(xiàng)能夠包含對于查詢自身輸出的引用。
遞歸查詢通常用于處理層次或者樹狀結(jié)構(gòu)的數(shù)據(jù)
2.WITH中的數(shù)據(jù)修改語句——INSERT、UPDATE或DELETE
WITH中的數(shù)據(jù)修改語句通常具有RETURNING子句
WITH中的數(shù)據(jù)修改語句只被執(zhí)行一次，并且總是能結(jié)束，而不管主查詢是否讀取它們所有（或者任何）的輸出。WITH中的子語句被和每一個其他子語句以及主查詢并發(fā)執(zhí)行。
RETURNING數(shù)據(jù)是在不同WITH子語句和主查詢之間傳達(dá)改變的唯一方法
WITH t AS (
? ? UPDATE products SET price = price * 1.05
? ? RETURNING *
)
SELECT * FROM products;//外層SELECT可以返回在UPDATE動作之前的原始價格
WITH t AS (
? ? UPDATE products SET price = price * 1.05
? ? RETURNING *
)
SELECT * FROM t;//外部SELECT將返回更新過的數(shù)據(jù)。
