在 PostgreSQL 中，資料表的管理主要使用 **DDL（Data Definition Language）** 指令，包括： - `CREATE TABLE`：建立資料表 - `ALTER TABLE`：修改資料表 - `DROP TABLE`：刪除資料表 - `TRUNCATE TABLE`：刪除資料表內所有資料，但保留表結構 以下以 `users` 與 `orders` 為例。 --- ## 1. 建立資料表：`CREATE TABLE` ### 基本語法 ```sql CREATE TABLE table_name ( column_name data_type [constraint], column_name data_type [constraint] ); ``` ### 範例 ```sql CREATE TABLE users ( user_id BIGSERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(255) NOT NULL UNIQUE, age INTEGER CHECK (age >= 0), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` 說明： - `BIGSERIAL`：自動遞增的整數欄位 - `PRIMARY KEY`：主鍵，不能重複且不可為 `NULL` - `NOT NULL`：欄位不可為空值 - `UNIQUE`：欄位值不可重複 - `CHECK`：資料必須符合指定條件 - `DEFAULT`：未提供值時使用預設值 ### 建立具有外鍵的資料表 ```sql CREATE TABLE orders ( order_id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, amount NUMERIC(10, 2) NOT NULL CHECK (amount >= 0), order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(user_id) ); ``` 外鍵確保 `orders.user_id` 必須對應到 `users.user_id` 中已存在的資料。 ### 避免資料表已存在時發生錯誤 ```sql CREATE TABLE IF NOT EXISTS logs ( log_id BIGSERIAL PRIMARY KEY, message TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` --- ## 2. 修改資料表：`ALTER TABLE` ### 2.1 新增欄位 ```sql ALTER TABLE users ADD COLUMN phone VARCHAR(20); ``` 新增欄位並設定預設值： ```sql ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active'; ``` 如果欄位不可為空，通常應先設定預設值或填入既有資料： ```sql ALTER TABLE users ADD COLUMN country VARCHAR(50) DEFAULT 'Taiwan'; ALTER TABLE users ALTER COLUMN country SET NOT NULL; ``` --- ### 2.2 修改欄位名稱 ```sql ALTER TABLE users RENAME COLUMN username TO user_name; ``` --- ### 2.3 修改資料表名稱 ```sql ALTER TABLE users RENAME TO customers; ``` --- ### 2.4 修改欄位資料型態 例如，將 `age` 從整數改為文字： ```sql ALTER TABLE users ALTER COLUMN age TYPE VARCHAR(3) USING age::VARCHAR; ``` 如果資料型態可直接轉換，也可以使用： ```sql ALTER TABLE users ALTER COLUMN amount TYPE NUMERIC(12, 2); ``` `USING` 用於指定資料轉換方式。 --- ### 2.5 設定或移除預設值 設定預設值： ```sql ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active'; ``` 移除預設值： ```sql ALTER TABLE users ALTER COLUMN status DROP DEFAULT; ``` --- ### 2.6 設定或移除 `NOT NULL` 設定不可為空： ```sql ALTER TABLE users ALTER COLUMN email SET NOT NULL; ``` 移除不可為空限制： ```sql ALTER TABLE users ALTER COLUMN email DROP NOT NULL; ``` 注意：設定 `NOT NULL` 前，欄位中不能有既存的 `NULL` 值。 --- ### 2.7 新增約束條件 新增唯一約束： ```sql ALTER TABLE users ADD CONSTRAINT uq_users_phone UNIQUE (phone); ``` 新增檢查約束： ```sql ALTER TABLE users ADD CONSTRAINT chk_users_age CHECK (age >= 0); ``` 新增外鍵： ```sql ALTER TABLE orders ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(user_id); ``` --- ### 2.8 移除約束條件 ```sql ALTER TABLE users DROP CONSTRAINT uq_users_phone; ``` 移除外鍵： ```sql ALTER TABLE orders DROP CONSTRAINT fk_orders_user; ``` 如果不確定約束名稱，可以在 `psql` 使用： ```sql \d users ``` 或查詢系統目錄。 --- ### 2.9 移除欄位 ```sql ALTER TABLE users DROP COLUMN phone; ``` 如果欄位不存在時不想報錯： ```sql ALTER TABLE users DROP COLUMN IF EXISTS phone; ``` 若該欄位被其他物件相依，可能需要： ```sql ALTER TABLE users DROP COLUMN phone CASCADE; ``` `CASCADE` 會一併刪除相依的物件，使用時需特別小心。 --- ## 3. 刪除資料表：`DROP TABLE` ### 刪除單一資料表 ```sql DROP TABLE users; ``` ### 資料表不存在時不報錯 ```sql DROP TABLE IF EXISTS users; ``` ### 同時刪除相依物件 ```sql DROP TABLE users CASCADE; ``` 也可以指定多個資料表： ```sql DROP TABLE users, orders; ``` 若 `orders` 有外鍵參考 `users`，刪除順序或使用 `CASCADE` 需要特別注意。 --- ## 4. 清除資料但保留資料表：`TRUNCATE` 如果只想刪除表內所有資料，而保留資料表結構： ```sql TRUNCATE TABLE users; ``` 同時重設自動遞增序號： ```sql TRUNCATE TABLE users RESTART IDENTITY; ``` 連同相依外鍵資料表一起清除： ```sql TRUNCATE TABLE users CASCADE; ``` `TRUNCATE` 通常比逐筆使用 `DELETE` 更快，但會刪除整張表的資料，必須謹慎使用。 --- ## 5. 使用交易確保操作安全 DDL 指令可以放在交易中執行： ```sql BEGIN; ALTER TABLE users ADD COLUMN last_login TIMESTAMP; -- 確認無誤後 COMMIT; ``` 如果發現錯誤，可以復原： ```sql ROLLBACK; ``` --- ## 6. 常見完整範例 ```sql -- 建立部門表 CREATE TABLE departments ( department_id SERIAL PRIMARY KEY, department_name VARCHAR(100) NOT NULL UNIQUE ); -- 建立員工表 CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, employee_name VARCHAR(100) NOT NULL, salary NUMERIC(12, 2) CHECK (salary >= 0), department_id INTEGER, hired_at DATE DEFAULT CURRENT_DATE, CONSTRAINT fk_employee_department FOREIGN KEY (department_id) REFERENCES departments(department_id) ); -- 新增欄位 ALTER TABLE employees ADD COLUMN email VARCHAR(255); -- 新增唯一約束 ALTER TABLE employees ADD CONSTRAINT uq_employee_email UNIQUE (email); -- 修改欄位名稱 ALTER TABLE employees RENAME COLUMN employee_name TO full_name; -- 刪除欄位 ALTER TABLE employees DROP COLUMN salary; -- 刪除資料表 DROP TABLE employees; ``` 實務上，執行 `DROP TABLE`、`DROP COLUMN`、`TRUNCATE` 或帶有 `CASCADE` 的指令前，建議先備份資料，並確認是否有其他資料表、檢視表或應用程式依賴該物件。