SQL 資料管理
在 PostgreSQL 中，管理資料表內的資料主要使用以下 SQL 指令：
- `INSERT`：新增資料
- `UPDATE`：修改資料
- `DELETE`：刪除資料
- `SELECT`：查詢與確認資料
以下以 `employees` 資料表為例：
```sql
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
salary NUMERIC(10, 2),
department VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
```
## 1. 新增資料：INSERT
### 新增單筆資料
```sql
INSERT INTO employees (name, email, salary, department)
VALUES ('王小明', 'ming@example.com', 50000, 'IT');
```
### 新增多筆資料
```sql
INSERT INTO employees (name, email, salary, department)
VALUES
('陳美華', 'mei@example.com', 60000, 'HR'),
('林志強', 'chiang@example.com', 55000, 'Sales');
```
### 新增後取得自動產生的資料
PostgreSQL 支援 `RETURNING`：
```sql
INSERT INTO employees (name, email, salary, department)
VALUES ('李大華', 'hua@example.com', 58000, 'Finance')
RETURNING id, name, created_at;
```
### 避免唯一鍵重複
如果 `email` 已存在，可以忽略這筆資料：
```sql
INSERT INTO employees (name, email, salary, department)
VALUES ('王小明', 'ming@example.com', 50000, 'IT')
ON CONFLICT (email) DO NOTHING;
```
也可以在發生衝突時更新資料：
```sql
INSERT INTO employees (name, email, salary, department)
VALUES ('王小明', 'ming@example.com', 52000, 'IT')
ON CONFLICT (email)
DO UPDATE SET
name = EXCLUDED.name,
salary = EXCLUDED.salary,
department = EXCLUDED.department;
```
`EXCLUDED` 代表這次準備插入、但與現有資料衝突的值。
---
## 2. 修改資料：UPDATE
### 修改指定資料
```sql
UPDATE employees
SET salary = 55000
WHERE id = 1;
```
### 同時修改多個欄位
```sql
UPDATE employees
SET
salary = 60000,
department = 'Management'
WHERE id = 1;
```
### 依條件修改多筆資料
```sql
UPDATE employees
SET salary = salary * 1.05
WHERE department = 'IT';
```
這會將 IT 部門所有員工的薪資調高 5%。
### 使用其他欄位計算新值
```sql
UPDATE employees
SET salary = salary + 3000
WHERE salary < 50000;
```
### 修改後查看結果
```sql
UPDATE employees
SET salary = 65000
WHERE id = 1
RETURNING *;
```
### 設定欄位為 NULL
```sql
UPDATE employees
SET department = NULL
WHERE id = 1;
```
但如果欄位設定了 `NOT NULL`，就不能指定為 `NULL`。
> 注意：`UPDATE` 如果省略 `WHERE`，會修改整個資料表的所有資料。
```sql
-- 危險：會修改所有員工
UPDATE employees
SET salary = 0;
```
---
## 3. 刪除資料：DELETE
### 刪除指定資料
```sql
DELETE FROM employees
WHERE id = 1;
```
### 依條件刪除多筆資料
```sql
DELETE FROM employees
WHERE department = 'Temporary';
```
### 刪除後取得被刪除的資料
```sql
DELETE FROM employees
WHERE id = 2
RETURNING *;
```
### 刪除全部資料
```sql
DELETE FROM employees;
```
這會刪除資料，但通常不會重設 `SERIAL` 或 `IDENTITY` 的流水號。
> 注意：`DELETE` 如果省略 `WHERE`，會刪除資料表中的所有資料。
若確定要清空資料表，也可以使用：
```sql
TRUNCATE TABLE employees;
```
若要同時重設自動編號：
```sql
TRUNCATE TABLE employees RESTART IDENTITY;
```
如果有外鍵參照其他資料表，可能需要：
```sql
TRUNCATE TABLE employees RESTART IDENTITY CASCADE;
```
使用 `CASCADE` 時要特別小心，因為可能連相關資料表的資料一併清除。
---
## 4. 查詢資料確認結果：SELECT
```sql
SELECT *
FROM employees;
```
查詢特定欄位：
```sql
SELECT id, name, salary
FROM employees
WHERE department = 'IT';
```
排序：
```sql
SELECT *
FROM employees
ORDER BY salary DESC;
```
限制筆數：
```sql
SELECT *
FROM employees
LIMIT 10;
```
---
## 5. 使用交易確保操作安全
對重要的新增、修改或刪除操作，可以使用交易：
```sql
BEGIN;
UPDATE employees
SET salary = salary * 1.1
WHERE department = 'IT';
-- 確認結果
SELECT *
FROM employees
WHERE department = 'IT';
-- 確定無誤後提交
COMMIT;
```
如果發現操作錯誤，可以復原：
```sql
BEGIN;
DELETE FROM employees
WHERE department = 'Temporary';
-- 發現刪錯時
ROLLBACK;
```
- `COMMIT`：正式儲存變更
- `ROLLBACK`：取消交易中的變更
---
## 常用指令總結
```sql
-- 新增
INSERT INTO employees (name, email, salary)
VALUES ('張三', 'zhang@example.com', 45000);
-- 修改
UPDATE employees
SET salary = 48000
WHERE email = 'zhang@example.com';
-- 刪除
DELETE FROM employees
WHERE email = 'zhang@example.com';
-- 查詢
SELECT *
FROM employees;
```
實務上執行 `UPDATE` 或 `DELETE` 前，建議先使用相同的 `WHERE` 條件執行 `SELECT` 確認目標資料，避免因條件錯誤而修改或刪除大量資料。
相關學習地圖、教學課程
Python 資料工程