官方文檔：http://www.postgres.cn/docs/14/

一、什么是 psql？

psql 是 PostgreSQL 提供的交互式終端工具，是與 PostgreSQL 數據庫交互的核心工具。它允許用戶：

• 執行 SQL 查詢：運行各種 SQL 語句

• 管理數據庫對象：創建/修改表、視圖、索引等

• 查看數據庫狀態：檢查性能指標和配置

• 執行腳本文件：批量運行 SQL 命令

• 數據導入導出：處理數據遷移任務

在 psql 中有兩種命令類型：

1. SQL 標準語句（如 SELECT, INSERT, UPDATE 等）

2. psql 內部命令（以反斜杠 \ 開頭）

1. 數據庫管理

命令	功能
\l 或 \list	列出所有數據庫
\c 或 \connect	切換數據庫
CREATE DATABASE	創建新數據庫
DROP DATABASE	刪除數據庫

2. 對象查看與管理

命令	功能
\dt	查看當前數據庫的所有表
\dt schema.*	查看特定模式下的表
\d table_name	查看表結構
\dv	查看視圖
\di	查看索引
\ds	查看序列
\dn	查看schema
\df	查看函數和存儲過程
\du	查看用戶列表

3. 實用內部命令

命令	功能
\conninfo	顯示當前連接信息
\timing	切換SQL執行時間顯示
\x	切換擴展顯示模式
\s	查看命令歷史
\e	在編輯器中編輯查詢
\?	查看所有內部命令幫助
\q	退出psql
\h <command>	查看 SQL 命令的語法幫助???????

二、 連接與基礎信息

連接數據庫

# 通過命令行連接（psql）
psql -h <主機名> -p <端口號> -U <用戶名> -d <數據庫名>

# 示例：連接本地默認端口(5432)的 mydb 數據庫
psql -U postgres -d mydb

# 連接后，如果數據庫需要密碼，會提示你輸入。

三、 用戶與權限管理

1. 創建用戶/角色

```sql
-- 創建具有登錄權限的用戶
CREATE USER dev_user WITH PASSWORD 'secure_password';
-- 或者（USER 和 ROLE 的區別：USER 默認有 LOGIN 權限）
CREATE ROLE read_only_user WITH LOGIN PASSWORD 'readonly_pass';

-- 創建角色（通常用于組權限）
CREATE ROLE developers;
```

2. 修改用戶

-- 修改密碼
ALTER USER dev_user WITH PASSWORD 'new_secure_password';

-- 重命名用戶
ALTER USER dev_user RENAME TO new_username;

-- 設置用戶有效期
ALTER USER dev_user VALID UNTIL '2024-12-31';

3. 刪除用戶

DROP USER dev_user;

4. 權限管理

4.1 數據庫權限：

-- 授予連接數據庫權限
GRANT CONNECT ON DATABASE mydb TO dev_user;

-- 授予創建表權限
GRANT CREATE ON DATABASE mydb TO dev_user;

4.2 模式權限：

-- 授予模式使用權限
GRANT USAGE ON SCHEMA public TO dev_user;

-- 授予模式中所有表的權限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;

-- 授予未來創建表的權限（重要！）
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO read_only_user;

4.3 表權限：

-- 授予特定表的權限
GRANT SELECT, INSERT, UPDATE ON users TO dev_user;
GRANT ALL PRIVILEGES ON posts TO developers;

-- 授予列級權限
GRANT UPDATE (email, phone) ON users TO dev_user;

-- 撤銷權限
REVOKE DELETE ON users FROM dev_user;

4.4 序列權限（自增字段）：

GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO dev_user;

5. 角色成員關系

-- 將用戶添加到角色組
GRANT developers TO dev_user;

-- 從角色組移除用戶
REVOKE developers FROM dev_user;

6. 查看權限信息

-- 查看當前用戶權限
SELECT * FROM information_schema.table_privileges 
WHERE grantee = CURRENT_USER;

-- 查看特定表的權限
\dp users

四、 數據庫操作

1. 創建數據庫

CREATE DATABASE mydb 
WITH OWNER = postgres  -- 指定所有者
     ENCODING = 'UTF8'
     CONNECTION LIMIT = 100;  -- 限制連接數

2. 修改數據庫所有者

ALTER DATABASE mydb OWNER TO new_owner;

3. 刪除數據庫

-- 先確保沒有活躍連接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'mydb';

DROP DATABASE mydb;

注意：不能在與該數據庫連接的狀態下刪除它。請先連接到另一個數據庫（如 postgres）再執行刪除。

五、 表操作

1. 創建表

CREATE TABLE users (
    id SERIAL PRIMARY KEY,        -- 自增主鍵
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100),
    age INT CHECK (age > 0),      -- 檢查約束
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id), -- 外鍵約束
    title VARCHAR(255) NOT NULL,
    content TEXT,
    is_published BOOLEAN DEFAULT FALSE
);

2. 修改表

-- 添加列
ALTER TABLE users ADD COLUMN full_name VARCHAR(100);

-- 修改列數據類型
ALTER TABLE users ALTER COLUMN full_name TYPE TEXT;

-- 刪除列
ALTER TABLE users DROP COLUMN full_name;

-- 添加約束
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

3. 刪除表

DROP TABLE posts;

六、 CRUD 操作（核心）

1. 插入數據

-- 插入單條數據
INSERT INTO users (username, email, age)
VALUES ('alice', 'alice@example.com', 25);

-- 插入多條數據
INSERT INTO users (username, email, age)
VALUES
    ('bob', 'bob@example.com', 30),
    ('charlie', 'charlie@example.com', 28);

-- 插入時使用 RETURNING 子句獲取生成的值（如自增ID）
INSERT INTO users (username, email) VALUES ('david', 'david@example.com') RETURNING id;

2. 查詢數據

-- 查詢所有列
SELECT * FROM users;

-- 選擇特定字段
SELECT id, first_name, last_name FROM employees;

-- 使用別名
SELECT 
    id AS employee_id,
    CONCAT(first_name, ' ', last_name) AS full_name,
    salary * 1.1 AS new_salary
FROM employees;

-- 基本條件篩選
SELECT * FROM orders WHERE amount > 1000 AND status = 'completed';

-- 日期范圍查詢
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';

-- 模糊查詢 (LIKE, ILIKE 不區分大小寫)
SELECT * FROM users WHERE username LIKE 'a%'; -- 以 'a' 開頭
SELECT * FROM users WHERE email ILIKE '%EXAMPLE.COM';

-- 排序 (ORDER BY)
SELECT * FROM users ORDER BY age DESC; -- 降序
SELECT * FROM users ORDER BY created_at ASC; -- 升序
SELECT * FROM employees ORDER BY department ASC, salary DESC;

-- 標準分頁
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 10 OFFSET 20;  --跳過 20 條記錄，從第 21 條開始，最多返回 10 條

-- 使用更高效的分頁（PostgreSQL 12+）
SELECT * FROM orders
ORDER BY id
OFFSET 20 FETCH NEXT 10 ROWS ONLY;

-- 基本聚合
SELECT 
    COUNT(*) AS total_orders,  --計數
    SUM(amount) AS total_revenue, -- 求和
    AVG(amount) AS average_order  --平均值
FROM orders;

-- 分組統計
SELECT 
    department,
    COUNT(*) AS employee_count,
    ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY department;

-- 分組后過濾
SELECT 
    product_category,
    SUM(quantity) AS total_sold
FROM sales
GROUP BY product_category
HAVING SUM(quantity) > 1000;

3. 更新數據

-- 更新所有行（慎用！）
UPDATE posts SET is_published = TRUE;

-- 更新指定行
UPDATE users SET age = 26 WHERE username = 'alice';

-- 更新多個列
UPDATE users SET email = 'new_email@example.com', age = 27 WHERE id = 1;

4. 刪除數據

-- 刪除所有行（慎用！）
DELETE FROM posts;

-- 刪除指定行
DELETE FROM users WHERE id = 5;

-- 清空表（更高效，但無法回滾）
TRUNCATE TABLE posts;

七、 連接查詢

-- 內連接
SELECT 
    o.order_id,
    c.customer_name,
    o.order_date
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

-- 左連接（包含無訂單的客戶）
SELECT 
    c.customer_name,
    o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- 多表連接
SELECT 
    e.first_name,
    e.last_name,
    d.department_name,
    p.project_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN employee_projects ep ON e.employee_id = ep.employee_id
JOIN projects p ON ep.project_id = p.project_id;

-- 自連接
SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
       w2.city, w2.temp_lo AS low, w2.temp_hi AS high
    FROM weather w1 JOIN weather w2
        ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;
        
     city      | low | high |     city      | low | high
---------------+-----+------+---------------+-----+------
 San Francisco |  43 |   57 | San Francisco |  46 |   50
 Hayward       |  37 |   54 | San Francisco |  46 |   50
(2 rows)

八、 高級查詢技巧

1. 子查詢應用

-- WHERE子句中的子查詢
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- FROM子句中的子查詢（派生表）
SELECT 
    dept_stats.department,
    dept_stats.avg_salary
FROM (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_stats
WHERE dept_stats.avg_salary > 70000;

-- CTE（公用表表達式）
WITH regional_sales AS (
    SELECT 
        region,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
)
SELECT 
    region,
    total_sales
FROM regional_sales
WHERE total_sales > 100000;

2. 窗口函數高級應用

-- 排名與分區
SELECT 
    department,
    first_name,
    last_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- 移動平均
SELECT 
    order_date,
    daily_sales,
    AVG(daily_sales) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS weekly_avg
FROM daily_sales;

九、 事務管理

事務確保一系列操作要么全部成功，要么全部失敗。

BEGIN; -- 開始事務

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 如果此時檢查無誤，則提交
COMMIT;

-- 如果發現錯誤，則回滾
-- ROLLBACK;

十、 索引（優化查詢速度）

1. 創建索引

-- 在經常用于查詢條件的列上創建索引
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_posts_user_id ON posts (user_id);

-- 復合索引
CREATE INDEX idx_users_name_age ON users (username, age);

2. 查看索引

\d users -- 在表結構中會顯示索引

十一、 常用函數與技巧

1. 字符串函數

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;
SELECT UPPER(username), LOWER(email) FROM users;
SELECT LENGTH(title) FROM posts;

2. 日期/時間函數

SELECT NOW(); -- 當前時間戳
SELECT CURRENT_DATE; -- 當前日期
SELECT EXTRACT(YEAR FROM created_at) AS year FROM posts; -- 提取年份
SELECT created_at::DATE FROM posts; -- 類型轉換，只取日期部分

3. 條件表達式

-- CASE WHEN
SELECT username,
       CASE WHEN age < 20 THEN 'Teen'
            WHEN age < 30 THEN 'Twenties'
            ELSE 'Adult'
       END AS age_group
FROM users;

-- COALESCE (返回第一個非NULL值)
SELECT COALESCE(full_name, username) AS display_name FROM users;

---

十二、 安全最佳實踐

1. 遵循最小權限原則

-- 只授予必要的權限
GRANT SELECT ON sensitive_table TO reporting_user;
REVOKE DELETE ON important_table FROM app_user;

2. 使用視圖保護敏感數據

CREATE VIEW user_public_info AS
SELECT id, username, created_at FROM users;

GRANT SELECT ON user_public_info TO public_user;

3. 定期審計權限

-- 查看所有用戶權限
SELECT * FROM information_schema.role_table_grants;

-- 查看數據庫權限
SELECT datname, datacl FROM pg_database;

4. 密碼策略

-- 設置密碼有效期
ALTER USER app_user VALID UNTIL '2024-12-31';

-- 強制密碼更改
ALTER USER app_user WITH PASSWORD 'new_pass';

十三、 常用維護命令

1. 查看活躍連接

SELECT * FROM pg_stat_activity;

2. 終止連接

SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE datname = 'mydb';

3. 查看鎖信息

SELECT * FROM pg_locks;

---

權限管理要點總結

1. 權限層次：集群 → 數據庫 → 模式 → 表/視圖/序列 → 列

2. 關鍵命令：GRANT / REVOKE / ALTER DEFAULT PRIVILEGES

3. 權限類型：

   • SELECT / INSERT / UPDATE / DELETE
   • CREATE / CONNECT / USAGE / TEMPORARY
   • EXECUTE (函數) / USAGE (序列)

4. 最佳實踐：

   • 使用角色組管理權限
   • 及時撤銷不必要的權限
   • 定期審計用戶權限
   • 使用視圖限制數據訪問
   • 遵循最小權限原則

總結

• 核心四步：CREATE -> INSERT -> SELECT -> UPDATE/DELETE。
• 查詢是靈魂：熟練掌握 WHERE, JOIN, GROUP BY, ORDER BY, LIMIT。
• 安全第一：使用 WHERE 條件時務必小心，避免誤刪或誤更新。善用事務。
• 性能優化：在合適的列上創建索引。