PostgreSQL 與 Java 實戰系列：四、SQL 簡介 | CyberJos is Blogging. 網路老喬的深聊漫談

本系列是之前應 Newzilla 邀稿而發表刊載的文章，現在在本站重新整理後發表。本篇介紹 SQL 起源與語法、匯入及匯出資料庫，以及舊有 PostgreSQL 資料庫轉移的注意事項。

SQL 起源與語法

表格規劃完成後，開始要建立表格和新增資料。最早關聯式資料庫處理資料的語言是在 1970 年代發展出來的，稱之為「結構化查詢語言」（Structured Query Language）。它可以建立及定義資料庫、表格，修改欄位資訊、資料處理等等。1986 年 ANSI 制定了一套標準的 SQL 規格，並在 1992 年推出第二個版本，也就是目前常看到的 SQL92（SQL2）標準，幾乎目前所有的關聯式資料庫都有支援此一規格，不過都沒有完整支援。在 1999 年發佈的 SQL99 仍在發展當中，比原本的 SQL92 語法更多更豐富。

語法可分成三類：

資料定義語言（Data Definition Language，簡稱 DDL）
用來建立、修改或刪除資料庫、表格、輪廓和索引，例如 CREATE、ALTER 與 DROP
資料操作語言（Data Manipulation Language，簡稱 DML）
用來新增、修改、刪除及查詢資料，例如 SELECT、INSERT、UPDATE 和 DELETE
資料控制語言（Data Control Language，簡稱 DCL）
用來檢索目錄及提供安全性，例如 BEGIN、COMMIT、ROLLBACK、GRANT 和 REVOKE

在文字模式下，你可以使用之前介紹的 psql 指令進入 RDBMS 中直接輸入語法；圖形模式下請先在左側清單中點選「資料庫」中的「guestbook」（也就是早先建立好的資料庫），然後按 Alt+T 叫出「工具」選單，點選「查詢工具」項目，即可輸入 SQL 語法，並且按 F5 熱鍵執行。

下列描述的語法中，全字大寫者表關鍵字詞，全字小寫者為變數，[] 中的字詞是選擇性字詞，{} 表示群組字詞，,... 是重複性的不定量變數，另所有 SQL 語法皆以 ; 結尾，-- 則為註解。

資料庫部份

建立資料庫

由於我們在前面已先行建立名為 guestbook 的資料庫，所以此處只是列出來做為參考：

CREATE DATABASE database_name;

刪除資料庫

DROP DATABASE database_name;

表格部份

建立表格

CREATE TABLE table_name (
  { column_name type [DEFAULT default_expr] -- DEFAULT 是設定欄位預設值
    [column_constraint [, ...]] }
  [, ...]                                   -- 其他欄位
);

type:                     -- 欄位資料型態
  SMALLINT                -- -32768 ~ 32767
| INTEGER                 -- -2147483648 ~ 2147483647
| BIGINT                  -- -9223372036854775808 ~ 9223372036854775807
| NUMERIC[(prec[, sca])]  -- 精確數字，prec 為總位數，sca 為小數位數
| DECIMAL[(prec[, sca])]  -- 等效於 NUMERIC
| REAL                    -- 六位十進位浮點數
| DOUBLE PRECISION        -- 十五位十進位浮點數
| SERIAL                  -- 自動遞增序號，可配合 NOT NULL 及 UNIQUE
| CHAR(n)                 -- 固定長度字串，n 為長度，不足者補空白
| VARCHAR(n)              -- 可變長度字串，n 為長度
| TEXT                    -- 可變長度字串
| DATE                    -- 日期
| TIME                    -- 時間
| TIMESTAMP               -- 日期及時間
| BOOLEAN                 -- 布林值。TRUE, 'y', '1' 或 FALSE, 'n', '0'

column_constraint:
  NOT NULL | NULL               -- 非空值或允許空值
| UNIQUE | PRIMARY KEY          -- 獨一無二的值，或主鍵值
| REFERENCES table_name         -- 外來鍵值參考
  [MATCH FULL | MATCH PARTIAL]  -- 可選擇全部符合或部份符合

洋洋灑灑一大串，看起來頗嚇人的，不過這邊也只寫出常用的部份而已。雖然好像很複雜，但實際上我們只要依照需求來增減就可以了。先從最簡單的星座表格開始：

CREATE TABLE astro (
  astroIndex SERIAL NOT NULL PRIMARY KEY,  -- 編號：遞增、非空、主鍵
  name CHAR(3) NOT NULL);                  -- 名稱：固定長度、非空

接下來看複雜一些的會員表格：

CREATE TABLE member (
  memberIndex SERIAL NOT NULL PRIMARY KEY,   -- 編號：遞增、非空、主鍵
  login VARCHAR(15) NOT NULL UNIQUE,         -- 帳號：可變長、非空、唯一
  password VARCHAR(15) NOT NULL,             -- 密碼：可變長、非空
  sexy INTEGER DEFAULT 0,                    -- 性別：整數、預設 0 為男
  email VARCHAR(100),                        -- 電子郵件：可變長度
  astro INTEGER REFERENCES astro MATCH FULL, -- 星座代號：外來鍵值
  jointime TIMESTAMP NOT NULL,               -- 註冊時間：非空
  priv INTEGER DEFAULT 0);                   -- 權限：預設 0 為使用者

最後是留言表格：

CREATE TABLE note (
  noteIndex SERIAL NOT NULL PRIMARY KEY,      -- 編號：遞增、非空、主鍵
  member INTEGER REFERENCES member MATCH FULL,-- 會員代號：外來鍵值
  content TEXT NOT NULL,                      -- 內容：可變、非空、唯一
  posttime TIMESTAMP NOT NULL);               -- 時間：非空

刪除表格

DROP TABLE table_name;

資料處理部份

新增資料

INSERT INTO table_name [ ( column [, ...] ) ]
  VALUES ( expression [, ...] );

INSERT 子句中你可以指定欄位，那麼其後的值就需要以欄位的前後順序來填寫，以星座欄位為例：

INSERT INTO astro VALUES (1, '牧羊座');               -- 不指定欄位
INSERT INTO astro (astroIndex, name) VALUES (2, '金牛座');
INSERT INTO astro (name, astroIndex) VALUES ('雙子座', 3);  -- 換次序

INSERT INTO astro (name) VALUES ('巨蟹座');
  -- 可選擇只輸入其中幾個欄位，資料庫會自動填入剩餘欄位。本例中編號
  --   的資料型態是SERIAL，所以資料庫會自動為其遞增設值。請注意省略
  --   輸入的欄位在創建表格時需有設定 DEFAULT 子句，或是為 SERIAL。

查詢資料

查詢資料使用的是 SELECT 語法，它的變化相當豐富，在這裡我們列出幾個最基本且常用的部份：

SELECT { * | expression [ AS new_column_name ] [, ...] }
    [ FROM table_name [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression ]
    [ HAVING condition [, ...] ]
    [ ORDER BY expression [ ASC | DESC ] ]
    [ LIMIT count ]
    [ OFFSET start ];

SELECT 看起來很複雜，底下會針對各個子句一一介紹。首先是最簡易的語法，可以查詢出表格中所有的記錄，並且會將所有欄位顯示出來（* 表所有欄位）：

SELECT * FROM astro;

如果要顯示特定欄位的話，可以直接指定欄位名稱：

SELECT name FROM astro;  -- name 為 astro 表格的欄位

條件查詢可以使用 WHERE 子句，後面接條件式：

SELECT * FROM astro WHERE astroIndex < 4;
  -- 顯示 astroIndex 欄位的值小於 4 的記錄
SELECT * FROM astro WHERE astroIndex >= 10;
  -- 顯示 astroIndex 欄位的值大於等於 10 的記錄
  -- 同類的運算子有 <, >, =, <=, >=, <> (不等於)
SELECT * FROM astro WHERE name like '雙%';
  -- 搜尋 name 欄位中有'雙'字開頭的記錄，% 表任意字元 (需使用 like)
SELECT * FROM astro WHERE name like '雙__';
  -- 搜尋 name 欄位中有'雙'字開頭且其後接兩個字元的記錄，_ 表單一字元
SELECT * FROM astro WHERE astroIndex between 1 and 6;
  -- 顯示 astroIndex 從 1 到 6 間的資料。用法：between 開頭 and 結尾
SELECT * FROM astro WHERE astroIndex in (1, 5, 9);
  -- 顯示 astroIndex 數值為 1, 5 和 9 的資料。用法：in (num [, ...])
SELECT * FROM astro WHERE name like '雙%' or name like '天%';
  -- 搜尋 name 欄位中有'雙'字或'天'字開頭的記錄
  -- 同類的運算子有 and, or, not

ORDER BY 子句使用在排序檢視上，其後加上欲排序的欄位名稱：

SELECT * FROM member ORDER BY login;
  -- 以 login 欄位遞增排序來顯示所有 member 表格中的記錄
SELECT * FROM note ORDER BY noteIndex DESC;
  -- 以 noteIndex 欄位遞減排序來顯示所有 note 表格中的記錄
  -- 在留言版會常用此一方法，以保持最新的記錄會最先顯示
  -- ASC 為遞增排序，DESC 為遞減排序

LIMIT 子句可以限定每次要顯示幾筆記錄，其後加上欲顯示的數目，OFFSET 則是欲先行略去的偏移筆數：

SELECT * FROM note ORDER BY noteIndex DESC LIMIT 10;
  -- 以 noteIndex 欄位遞減來顯示 note 表格中的十筆記錄
SELECT * FROM note ORDER BY noteIndex DESC LIMIT 10 OFFSET 5;
  -- 以 noteIndex 欄位遞減來顯示 note 表格中略去前五筆後的十筆記錄

GROUP BY 子句主要用在群組上，也就是其欄位中重複的值只會出現一次，而忽略其他同樣的值：

SELECT member,max(posttime) FROM note GROUP BY member
  ORDER BY max(posttime);
  -- 搜尋 note 中的會員代碼欄位，以及其相對應的最大（意即最後）發文時間
  -- 接著使用 GROUP BY 子句讓每個會員代碼只出現一次，最後再以發文時間排序
  -- 此例可找出每個會員最後發文時間
SELECT member,now() - max(posttime) FROM note GROUP BY member
  ORDER BY max(posttime);
  -- 功能同上，但這裡用 now() 找出現在時間後，減去每個會員最後發文時間
  -- 如此可得知每個會員上次發文後距離現在隔有多長時間

HAVING 子句需和 GROUP BY 子句合用，表條件查詢。WHERE 子句也同樣是在做條件查詢，但它和 WHERE 子句
所不同的地方在於，所有記錄會先交給 WHERE 子句過濾後，再交由 GROUP BY 子句處理，接下來才會輪到 HAVING
子句去做進一步的過濾：

SELECT member,now() - max(posttime) FROM note GROUP BY member
  HAVING member > 20 ORDER BY max(posttime);
  -- 功能同上，此處多加上的 HAVING 表示只顯示會員代碼大於 20 的記錄

修改資料

UPDATE 語法用來修改資料，你可以一次修改表格中所有記錄，或是使用 WHERE 去過濾欲修改的資料：

UPDATE table_name SET column = { expression | DEFAULT } [, ...]
    [ WHERE condition ];  -- 不加上 WHERE 子句的話，會將表格所有記錄做修改

例如我們要將全部 member 表格中的使用者權限皆設為 0，可以使用下列語法：

UPDATE member SET priv=0;
UPDATE member SET priv=0, email='';
  -- 你也可以使用逗號分隔的方式來同時設定多個欄位

如果只想更新「符合某些特殊條件」的資料，可以使用 WHERE 子句：

UPDATE member SET priv=1 WHERE login='Tom';
  -- 將會員 Tom 的權限調整至 1 (管理者)

刪除資料

DELETE FROM table [ WHERE condition ];
  -- 不加上 WHERE 子句的話，會將表格所有記錄刪除

將所有留言刪除：

DELETE FROM note;

刪除會員代號 10 的所有留言：

DELETE FROM note WHERE member=10;

匯入及匯出資料庫

資料庫在運行一段時間後，可能會遭遇到一些天災人禍的意外而造成資料損毀，這個時候我們只能使用預先的備份來回復整個系統。底下介紹的就是匯出及匯入的指令，首先要來看的是匯出，使用 pg_dump 指令：

pg_dump [Option] [Database]

常用參數解說：
  -f, --file=FILENAME      輸出檔名
  -F, --format=c|t|p       輸出格式 (c 自訂, t Tar 檔, p 純文字)
  -i, --ignore-version     忽略版本差異
  -v, --verbose            多訊息顯示
  -a, --data-only          只匯出資料
  -b, --blobs              匯出時包括大型物件
  -c, --clean              先行匯出刪除語法，其後再匯出創建語法
  -d, --inserts            使用 INSERT 語法代替 COPY 語法
  -s, --schema-only        只匯出資料庫架構

你可以依照需要來增減參數。底下是我個人的使用方法，guestbook 是資料庫名稱，gb-20040910.txt 是欲匯
出的文字檔名：

pg_dump -f gb-20040910.txt -i -c -d guestbook

另外如果是要匯出全部資料庫的話，那麼可以使用 pg_dumpall 指令，常用參數和 pg_dump 的使用方法相同。

如果要將 SQL 語法的純文字檔匯入資料庫的話，我們使用的是 psql 指令：

psql -f gb-20040910.txt -U gbadmin
# -f 代表欲匯入的檔案名稱
# -U 代表指定資料庫使用者所擁有的權限來匯入資料

或者你要將 pg_dump 輸出格式為 c（自訂）或 t（Tar檔）的檔案匯入時，可以使用 pg_restore 指令將其回存至資料庫：

pg_restore [Option] [File]

常用參數解說：
  -d, --dbname=NAME        欲匯入的資料庫名稱
  -f, --file=FILENAME      輸出檔名
  -F, --format=c|t         指定輸入檔案的格式 (c 自訂, t Tar 檔)
  -i, --ignore-version     忽略版本差異
  -v, --verbose            多訊息顯示
  -a, --data-only          只匯入資料
  -c, --clean              先行匯入刪除語法，其後再匯入創建語法
  -I, --index=NAME         只匯入指定的索引
  -P, --function=NAME(arg) 只匯入指定的函式
  -s, --schema-only        只匯入資料庫架構
  -t, --table=NAME         只匯入指定表格的定義和記錄
  -T, --trigger=NAME       只匯入指定的觸發器

用法：
pg_dump -d guestbook -F t -i -c gb-20040910.tar

舊有 PostgreSQL 資料庫轉移注意事項

如果你之前曾經使用或調校過 PostgreSQL 7.x，並且想要將資料庫轉移到 PostgreSQL 8.0 的話，那麼下列事項請仔細檢查，以避免更換資料庫時失敗而造成資料出錯：

組態檔參數 virtual_host 及 tcpip_socket 由 listen_addresses 取代。
參數 SortMem 及 VacuumMem 更名為 work_mem 和 maintenance_work_mem 以反映它們的實際應用。
參數 log_pid、log_timestamp 和 log_source_port 由更具彈性的 log_line_prefix 所取代。
參數 syslog 由較合理的 log_destination 參數去控制日誌輸出目的地。
參數 log_statement 修改至可選擇性地去記錄資料庫變更或資料定義敘述。
參數 max_expr_depth 由 max_stack_depth 取代。它會測量出實體堆疊尺寸而不是運算過程中的巢狀深度。這可以避免連線終端機因遞迴函式而產生的堆疊溢位。
函式 length() 不會將資料型態 CHAR(n) 中字尾空白的部份列入計算了。
將整數轉型為 BIT(N) 時會選擇整數右側 N 位元，而非早期的左側 N 位元。
將空字串指定給 oid、float4 和 float8 型態時，伺服器將會發出警告。下個版本中這個項目會改為發出錯誤訊息。
函式 extract() (也稱為 date_part) 現在會回傳適當的西元前日期。
psql 的 copy 命令現在為讀寫「查詢標準輸入/輸出」，而非 psql 程式的標準輸入/輸出。早期的動作可以經由新的 pstdin/pstdout 參數來存取。
JDBC 客戶端介面已從核心版本中移除，現在搬移至 https://jdbc.postgresql.org。TCL 客戶端介面也移除了。你可以在 http://gborg.postgresql.org 下找到幾個 TCL 介面。
伺服器現在使用自身的時區資料庫，而不採用作業系統的內建支援。它為跨平台提供了一致的行為。在大多數情況下，應該會有一點點顯著的差異存在於時區處理中，SET/SHOW 使用的時區名稱可能會和您的平台所使提供的有所差異。
EXECUTE 現在會回傳符合被執行指令的完整標記。
組態中的執行緒選項已無需使用者自行測試或編輯組態設定檔；執行緒選項現在已會自動偵測。