全部速查表數據庫管理

postgresql

PostgreSQL 14+

PostgreSQL 快速參考：psql 客戶端指令、DDL／DML 陳述式、join／彙總、索引、交易與備份／還原——附常用參數與實作範例。

36 條命令

連線

psql -h <host> -p <port> -U <user> -d <db>

對遠端或本機資料庫開啟互動式 psql 工作階段。

-h 主機；-p 預設 5432；-U 使用者；-d 資料庫；-W 強制詢問密碼；-f 檔案執行腳本；-c 單一命令

PGPASSWORD=secret psql -h db.example.com -U app -d app_production

psql -U <user> <db> --variable='ON_ERROR_STOP=1' -f <file.sql>

對資料庫執行 SQL 腳本，遇到第一個錯誤就停止。

psql -U app -d app -v ON_ERROR_STOP=1 -f migrations/0001_init.sql

psql 中繼指令

\l[+] \c[onnect] [<db>]

列出資料庫；接著切換到其中之一。

\l+ 含大小／tablespace；\c db user 切換目標

\l+; \c app_dev

\dt[+] [<pattern>]

列出當前 schema（預設 public）中符合模式的資料表。

\dt+ 含大小描述；\dt *.* 所有 schema；\d table 描述資料表

\dt+ public.*

\d <table|view|index|seq|matview>

描述物件結構：欄位、索引、外鍵、註解。

\d orders

\dn / \du / \dv / \di

分別列出 schema／角色／視圖／索引。

\du; \di+ idx_orders_user_id

\timing / \x / \? / \q

切換查詢計時、展開顯示；顯示說明；離開工作階段。

\timing on; \x auto; SELECT * FROM orders LIMIT 2; \q

\copy <table> FROM '<file>' DELIMITER ',' CSV HEADER

從檔案批次載入資料（伺服器端語意，但走用戶端檔案系統）。

\copy orders(order_id,user_id,total,created_at) FROM '/tmp/orders.csv' WITH CSV HEADER

資料庫與 Schema

CREATE DATABASE <db>

建立新資料庫，可指定樣板與各項選項。

OWNER 角色；TEMPLATE template0；ENCODING 'UTF8'；LC_COLLATE

CREATE DATABASE app_dev OWNER app_user ENCODING 'UTF8' TEMPLATE template0

DROP DATABASE <db>

刪除資料庫（不得有連線；常搭配 IF EXISTS 與強制選項）。

DROP DATABASE WITH (FORCE)；postgres 13+

DROP DATABASE IF EXISTS app_old;

CREATE SCHEMA / DROP SCHEMA

為資料表／視圖提供命名空間；屬於預設 search_path 的一部分。

CREATE SCHEMA IF NOT EXISTS audit; DROP SCHEMA staging CASCADE;

資料表與 DDL

CREATE TABLE

定義新資料表，含欄位、型別、預設值與約束。

PRIMARY KEY；FOREIGN KEY ... REFERENCES；UNIQUE；CHECK；INHERITS；PARTITION BY RANGE|LIST

CREATE TABLE orders (id bigserial PRIMARY KEY, user_id int NOT NULL REFERENCES users(id), total numeric(10,2) NOT NULL DEFAULT 0, created_at timestamptz NOT NULL DEFAULT now());

ALTER TABLE

新增／刪除／重新命名欄位，變更型別，加入約束，設定儲存參數。

ADD COLUMN；DROP COLUMN；RENAME TO；ADD CONSTRAINT；ALTER COLUMN ... TYPE

ALTER TABLE orders ADD COLUMN currency char(3) NOT NULL DEFAULT 'USD', ADD CONSTRAINT chk_cur CHECK (currency IN ('USD','EUR','CNY'));

DROP TABLE / TRUNCATE

移除資料表（若被外鍵參照需 CASCADE）；TRUNCATE 可快速清空。

DROP TABLE ... CASCADE；TRUNCATE ... RESTART IDENTITY CASCADE

TRUNCATE TABLE staging.events RESTART IDENTITY CASCADE;

COMMENT ON <table|column> IS '...'

為資料庫物件加上註解——會顯示於 \d+ 與 IDE 提示中。

COMMENT ON COLUMN orders.total IS '稅前小計'

DML

INSERT INTO <table> [(cols)] VALUES (...) [RETURNING ...]

插入一筆或多筆資料；RETURNING 可回傳剛插入的欄位。

INSERT ... SELECT；ON CONFLICT DO NOTHING / DO UPDATE（upsert）；RETURNING

INSERT INTO orders(user_id, total) VALUES (42, 19.95) RETURNING id, created_at;

UPDATE ... SET ... [WHERE ...] [RETURNING ...]

更新符合條件的列；除非刻意，務必加上 WHERE。

UPDATE ... FROM t2 WHERE ...；CTE（WITH ...）+ UPDATE；... RETURNING

UPDATE orders SET status='paid' WHERE user_id = 42 AND created_at < now() - interval '7 days' RETURNING id;

DELETE FROM ... [WHERE ...] [RETURNING ...]

刪除符合條件的列；要清空整張表時 TRUNCATE 更快。

DELETE ... USING t2 WHERE ...；RETURNING

DELETE FROM orders WHERE status='cancelled' AND created_at < now() - interval '30 days' RETURNING id;

查詢與篩選

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT N OFFSET M

讀取資料列，搭配篩選、排序與分頁。

WHERE col op ANY/ALL(array)；LIMIT n OFFSET n；FETCH FIRST n ROWS ONLY；FOR UPDATE／SHARE 鎖定

SELECT id, status, total FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10 OFFSET 20;

DISTINCT / GROUP BY / HAVING

去除重複、依欄位彙總，再對彙總結果做篩選。

GROUP BY ROLLUP(a,b)；GROUPING SETS；HAVING 對彙總過濾；FILTER (WHERE ...) 保留 NULL

SELECT user_id, count(*) FILTER (WHERE status='paid') AS paid, sum(total) AS gmv FROM orders GROUP BY user_id HAVING count(*) >= 3;

連接

[INNER | LEFT | RIGHT | FULL] JOIN ... ON ...

合併多張資料表的列；LEFT JOIN 會保留左側無對應的列。

JOIN LATERAL；NATURAL JOIN；USING(col) 取代 ON；CROSS JOIN 笛卡兒積

SELECT u.email, count(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.email;

WITH cte AS (...) SELECT ...

把子查詢具體化為 CTE；遞迴 CTE 適用於階層／圖形結構。

WITH RECURSIVE tree AS (SELECT id, parent_id, name FROM cats WHERE parent_id IS NULL UNION ALL SELECT c.id, c.parent_id, c.name FROM cats c JOIN tree t ON c.parent_id = t.id) SELECT * FROM tree;

彙總

count() / sum() / avg() / min() / max() / array_agg() / string_agg()

內建的彙總函式；搭配 FILTER 子句或 GROUP BY 很好用。

count／sum 內可加 DISTINCT；array_agg／string_agg 內可加 ORDER BY

SELECT date_trunc('day', created_at) AS day, sum(total) AS gmv FROM orders GROUP BY day ORDER BY day DESC LIMIT 7;

索引與視圖

CREATE INDEX ...

加速 WHERE／ORDER BY；partial index 可針對熱點子集。

CONCURRENTLY（不取獨佔鎖）；INCLUDE (a,b) 涵蓋式；USING btree|gin|gist|hash；partial WHERE

CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders(user_id, created_at DESC) INCLUDE (total);

EXPLAIN [ANALYZE] <query>

顯示查詢計畫；ANALYZE 會實際執行並回報時間與列數。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)；FORMAT JSON / TEXT / YAML

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

VACUUM / ANALYZE / REINDEX

維護儲存與統計：回收死 tuple、更新 planner 統計、重建索引。

VACUUM (FULL) 重寫整張表；ANALYZE 更新統計；REINDEX 重建索引；平時由 autovacuum 處理

VACUUM (ANALYZE) orders;

CREATE VIEW / MATERIALIZED VIEW

儲存查詢定義；materialized view 會實際快取資料列（需 refresh）。

CREATE OR REPLACE VIEW；MATERIALIZED VIEW ... WITH NO DATA；REFRESH MATERIALIZED VIEW CONCURRENTLY

CREATE MATERIALIZED VIEW mv_user_orders AS SELECT user_id, count(*) FROM orders GROUP BY user_id; CREATE UNIQUE INDEX ON mv_user_orders(user_id);

交易

BEGIN / COMMIT / ROLLBACK

把多個陳述式組成一個原子工作單元（ACID）。

BEGIN ISOLATION LEVEL SERIALIZABLE；COMMIT／ROLLBACK AND CHAIN；SAVEPOINT 標籤

BEGIN ISOLATION LEVEL REPEATABLE READ; UPDATE orders SET ... WHERE ...; ROLLBACK ON ERROR; COMMIT;

SELECT ... FOR UPDATE / FOR SHARE

在交易結束前鎖定選取的列；防止並發更新。

FOR UPDATE / SHARE；NOWAIT 或 SKIP LOCKED 適合做佇列

SELECT id FROM jobs WHERE status='queued' ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

備份與還原

pg_dump <db> -Fc -f <file.dump>

將資料庫匯出為 custom 格式檔案（支援平行還原與選擇性還原）。

-Fc custom；-Ft tar；-Fp 純 SQL；--schema=；--exclude-table=；-j 平行（搭配 -Fd）

pg_dump -Fc -d app -f /backup/app-$(date +%F).dump

pg_restore -d <db> <file.dump>

還原 custom／tar／dir 格式的備份；可選擇只還原需要的部分。

--clean 先刪除物件；--if-exists；--schema=；--table=；--jobs=N；--no-owner；--single-transaction

pg_restore -d app_dev --clean --if-exists --jobs=4 /backup/app.dump

psql -d <db> -f <file.sql>

還原純 SQL 格式的備份（pg_dump -Fp 的輸出）。

psql -U app -d app_new -v ON_ERROR_STOP=1 -f /backup/app.sql

使用者與權限

CREATE ROLE / ALTER ROLE

角色可登入（LOGIN）並持有物件；可逐角色授與屬性。

CREATE ROLE reporting LOGIN PASSWORD '...' NOSUPERUSER NOCREATEDB; ALTER ROLE app SET statement_timeout = '5s'

CREATE USER <name>

`CREATE ROLE ... LOGIN` 的簡寫——建立一個可登入的使用者（預設不授予任何權限）。

[SUPERUSER|NOSUPERUSER]；CREATEDB|NOCREATEDB；CREATEROLE；INHERIT|NOINHERIT；PASSWORD '...'；VALID UNTIL '時間戳'；IN ROLE 組名

CREATE USER app_reader WITH PASSWORD 's3cr3t' NOCREATEDB NOCREATEROLE VALID UNTIL '2026-12-31';

GRANT / REVOKE

授與資料表、schema、資料庫、函式上的權限；亦可到欄位層級。

GRANT SELECT, INSERT ON TABLE ...；GRANT USAGE ON SCHEMA ...；WITH GRANT OPTION

GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

\dt <schema>.* / pg_hba.conf

調整 search_path 與可見物件（\dt public.*）；pg_hba.conf 控制誰能連線。

SHOW search_path；ALTER ROLE app IN DATABASE app SET search_path = app, public；pg_hba.conf hostssl all all 0.0.0.0/0 scram-sha-256

ALTER ROLE app SET search_path = app, public;

商標與使用聲明

本批命令列工具速查手冊為開源社群參考資料。文中涉及的各工具的名稱、標識、商標（如 Git™、Docker™、Kubernetes®、kubectl、PostgreSQL®、MySQL®、Redis™、MongoDB®、Linux™、PowerShell™、Vim™ 等）均歸對應權利人所有，此處僅為識別與參考之目的使用。

本速查手冊的全部內容均為獨立編寫的簡化自包含參考，**與任何官方文件不存在隸屬、被認可或引用關係**。命令列表、參數說明、範例均為作者原創簡化表達；如需權威資訊，請參閱各工具的官方文件。

命令語法與參數可能在不同工具版本中有所差異。所示版本標籤對應穩定發佈版本；其他版本行為可能有所不同。本速查手冊**不代表**商標權利人的任何偏好或推薦。

如商標或版權方認為本站部分內容存在權益侵害，請發送電郵至[email protected]，我們在收到有效權屬證明後，會在合理期限內修改或移除相關內容。

本網站不對因使用本站資訊而產生的任何直接或間接損失承擔法律責任。

關於 PostgreSQL

PostgreSQL 係一個開源嘅物件關聯式資料庫管理系統，源自 1986 年 UC Berkeley 嘅 POSTGRES 計劃（Michael Stonebraker），1996 年開放原始碼，1997 年正式改名為 PostgreSQL。當前穩定版係 PostgreSQL 16+，由全球社群以 PostgreSQL 授權（類 BSD 寬鬆授權）每年發佈。PostgreSQL 率先實現並推廣咗好多後來變成標準嘅功能：MVCC（多版本並行控制）令讀寫唔再互鎖、先寫紀錄（WAL）確保崩潰後安全、使用者自訂類型同函式、表格繼承、豐富嘅索引（B-tree、hash、GiST、GIN、BRIN，加上表達式索引同部分索引）。佢支援幾乎全部 SQL:2003 標準，包括窗口函式、CTE、遞迴查詢、`LATERAL` 連接；PostGIS、pg_trgm、pgvector、pgcrypto 等擴展喺唔 fork 伺服器嘅前提下補齊咗地理空間、模糊文字、向量相似度、密碼學嘅能力。交易型 OLTP、JSONB 文件儲存、地理空間應用、全文搜尋嘅場景都應該用佢；任何效能問題都由 `EXPLAIN (ANALYZE, BUFFERS)` 入手。PostgreSQL 係本地服務——連線留喺你嘅網絡入，幾乎永遠唔應該將通訊埠開放到公網。

速查頁版本 1.0.0

postgresql

連線

psql 中繼指令

資料庫與 Schema

資料表與 DDL

DML

查詢與篩選

連接

彙總

索引與視圖

交易

備份與還原

使用者與權限

相關命令速查

mysql

redis cli

mongodb

商標與使用聲明