從PostgreSQL使用手冊整理的一些筆記(2)

日期：

以下內容節錄PostgreSQL使用手冊
中文參考
原文參考

Chapter 7. Queries

最簡單的形式：
SELECT * FROM table1;
SELECT a, b + c FROM table1;
如果要當calculator可省略table，如下：
SELECT 3 * 4;
Table expression 包含 FROM 也可以包含WHERE, GROUP BY, HAVING.
如果多張Table在FROM裡面，the  tables are cross-joined.
Joined Tables
T1 join_type T2 [ join_condition ]
T1 CROSS JOIN T2
T1 INNER JOIN T2
T1 LEFT OUTER OIN T2
T1 RIGHT OUTER JOIN T2
T1 FULL OUTER JOIN T2 /*有的都抓，有缺當缺值*/
ON 後通常接condition
USING (a, b) 簡化the join condition ON T1.a = T2.a AND T1.b =T2.b
SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
SELECT * FROM t1 INNER JOIN t2 USING (num);
差別在第一種列出兩者的num，後者只列出一次num
SELECT * FROM t1 NATURAL INNER JOIN t2;
NATURAL JOIN 跟 INNER JOIN 差別在 NATURAL JOIN 有一樣欄位名稱才能用
例如：
A Table 有 i ii iii 三個欄位
B Table 有 iii iv v 三個欄位
SELECT A.ii, B.iv
FROM A
NATURAL  JOIN B
會出現兩者iii重疊到的ii及iv
結果等同於
SELECT A.ii, B.iv
FROM A
INNER JOIN B
ON A.iii=B.iii;
順便查LEFT JOIN跟LEFT OUTER JOIN有沒有差?
LEFT JOIN and LEFT OUTER JOIN are the same. https://www.dofactory.com/sql/left-outer-join
LEFT JOIN 是 LEFT OUTER JOIN 的簡寫
SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
SELECT * FROM t1 LEFT JOIN t2 USING (num);
一樣只差在第一行的指令結果會多一欄
WHERE 就不贅述了
但下方的三個範例或的的結果是相同的可參考：
FROM a, b WHERE a.id = b.id AND b.val > 5
FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
FROM a NATURAL JOIN b WHERE b.val > 5
附上一種用法：
SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
另外是關於GROUP BY & HAVING的用法，下方範例應該是淺顯易懂，不同於WHERE，HAVING使用在GROUP BY的時候：
=> SELECT x, sum(y) FROM test1 GROUP BY x;
 x | sum
---+-----
 a |   4
 b |   5
 c |   2
(3 rows)
=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

=> SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)


關於SET的使用可參考：
=> SELECT * FROM items_sold;
 brand | size | sales
-------+------+-------
 Foo   | L |  10
 Foo   | M |  20
 Bar   | M |  15
 Bar   | L |  5
(4 rows)

=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
 brand | size | sum
-------+------+-----
 Foo   | |  30
 Bar   | |  20
          | L | 15
          | M | 35
          | | 50
另一個CUBE的用法可參考等同於GROUPING SETS的結果，如下：
CUBE ( a, b, c )
等於
GROUPING SETS (
    ( a, b, c ),
    ( a, b    ),
    ( a,    c ),
    ( a      ),
    ( b, c ),
    ( b    ),
    (    c ),
    (      )
)
或者
CUBE ( (a, b), (c, d) )
等同於
GROUPING SETS (
    ( a, b, c, d ),
    ( a, b       ),
    (    c, d ),
    (         )
)
ROLLUP的使用則如下：
ROLLUP ( a, (b, c), d )
等同於
GROUPING SETS (
    ( a, b, c, d ),
    ( a, b, c    ),
    ( a         ),
    (         )
)
Multiple Grouping items
GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))
等同於
GROUP BY GROUPING SETS (
    (a, b, c, d), (a, b, c, e),
    (a, b, d),    (a, b, e),
    (a, c, d),    (a, c, e),
    (a, d),       (a, e)
)


Alias
以a代替my_table這張表，b代替your_table這張表，用法與SAS中PROC SQL以t1,t2表示的方式相同

SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...

SELECT LIST ITEMS
SELECT a "value", b + c AS sum FROM …
等同於
SELECT a AS “value”, b + c AS sum FROM …
如果新命的名稱是關鍵字，需用雙引號表示，避免日後新增關鍵字，因此建議AS後或欄位名稱都用雙引號標示

Combining Query
query1 UNION [ALL] query2　
聯集，不加ALL會DISTINCT
query1 INTERSECT [ALL] query2
交集，不加ALL會DISTINCT
query1 EXCEPT [ALL]query2
回傳query1的結果中不包含在query2的，不加ALL會DISTINCT

Order By
可以是輸出的欄位或編號
SELECT a + b AS sum, c FROM table1 ORDER BY sum;
SELECT a, max(b) FROM table1 GROUP BY a ORDER BY 1;

LIMIT & OFFSET
限制回傳行數
SELECT select_list
  FROM table_expression
  [ ORDER BY ... ]
  [ LIMIT { number | ALL } ] [ OFFSET number]
LIMIT在number行前，LIMIT ALL等同於沒寫LIMIT
OFFSET表示跳過number行，OFFSET等同於沒寫OFFSET

VALUE的使用．一個括號內表示一個資料列，所以下方範例有三列資料列：
=> SELECT * FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t (num,letter);
num | letter
-----+--------
　 1 | one
　  2 | two
　  3 | three
(3 rows)

VALUES (1, 'one'), (2, 'two'), (3, 'three');
等同於
SELECT 1 AS column1, 'one' AS column2
UNION ALL
SELECT 2, 'two'
UNION ALL
SELECT 3, 'three';


留言

張貼留言