PostgreSQL 與 Java 實戰系列：四、SQL 簡介

Published at 發佈： | Updated at 更新：2025-01-06 | Post views 瀏覽：3,779 次 | Categories 分類：PostgreSQL | Tags 標籤：Database, PostgreSQL | Comments 發佈留言

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

  1. 資料庫系統簡介
  2. PostgreSQL 簡介
  3. 資料庫應用實作
  4. SQL 簡介（本篇）
  5. JDBC 程式撰寫

SQL 起源與語法

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

SQL 語法可分成三類：

  • 資料定義語言（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 現在會回傳符合被執行指令的完整標記。
  • 組態中的執行緒選項已無需使用者自行測試或編輯組態設定檔；執行緒選項現在已會自動偵測。

相關文章

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

ten − one =

返回頂端