PostgreSQL CRUD操作指南

发布时间: 2025-04-08 09:43

↑

# PostgreSQL CRUD操作指南

本文将详细介绍PostgreSQL数据库中的CRUD（Create、Read、Update、Delete）操作，包括基本语法、常用示例和最佳实践，帮助你掌握数据库的基本操作技能。

## 创建数据（Create）

### 1. INSERT语句基本语法

```sql
-- 基本插入语法
INSERT INTO table_name (column1, column2, ...) 
VALUES (value1, value2, ...);

-- 插入多行数据
INSERT INTO table_name (column1, column2) VALUES 
    (value1, value2),
    (value3, value4),
    (value5, value6);

-- 使用默认值和表达式
INSERT INTO products (name, price, created_at) 
VALUES ('Product A', 29.99, CURRENT_TIMESTAMP);
```

### 2. 从其他表插入数据

```sql
-- 使用SELECT语句插入数据
INSERT INTO backup_table 
SELECT * FROM original_table 
WHERE created_at < '2023-01-01';

-- 使用WITH子句插入数据
WITH moved_rows AS (
    DELETE FROM original_table 
    WHERE status = 'archived' 
    RETURNING *
)
INSERT INTO archive_table 
SELECT * FROM moved_rows;
```

### 3. 处理冲突

```sql
-- ON CONFLICT处理
INSERT INTO users (email, name) 
VALUES ('user@example.com', 'John Doe')
ON CONFLICT (email) DO UPDATE 
    SET name = EXCLUDED.name;

-- 忽略冲突
INSERT INTO logs (event_id, message)
VALUES (123, 'test event')
ON CONFLICT DO NOTHING;
```

## 查询数据（Read）

### 1. 基本SELECT语句

```sql
-- 基本查询
SELECT * FROM users;

-- 指定列查询
SELECT first_name, last_name, email 
FROM users;

-- 使用WHERE条件
SELECT * FROM products 
WHERE price > 100 AND category = 'electronics';
```

### 2. 连接查询

```sql
-- INNER JOIN
SELECT o.order_id, u.name, o.total_amount
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id;

-- LEFT JOIN
SELECT p.name, c.comment
FROM products p
LEFT JOIN comments c ON p.product_id = c.product_id;

-- 多表连接
SELECT o.order_id, u.name, p.product_name
FROM orders o
JOIN users u ON o.user_id = u.user_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;
```

### 3. 分组和聚合

```sql
-- GROUP BY基本用法
SELECT category, COUNT(*) as count, AVG(price) as avg_price
FROM products
GROUP BY category;

-- HAVING子句
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 5;

-- 窗口函数
SELECT 
    product_name,
    price,
    category,
    RANK() OVER (PARTITION BY category ORDER BY price DESC) as price_rank
FROM products;
```

## 更新数据（Update）

### 1. 基本UPDATE语句

```sql
-- 基本更新
UPDATE users
SET status = 'active'
WHERE last_login > '2023-01-01';

-- 更新多个字段
UPDATE products
SET 
    price = price * 1.1,
    updated_at = CURRENT_TIMESTAMP
WHERE category = 'electronics';
```

### 2. 使用子查询更新

```sql
-- 使用子查询更新
UPDATE orders
SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items
    WHERE order_items.order_id = orders.order_id
);

-- 使用FROM子句更新
UPDATE products p
SET stock_count = p.stock_count - oi.quantity
FROM order_items oi
WHERE p.product_id = oi.product_id
    AND oi.order_id = 12345;
```

### 3. 返回更新的数据

```sql
-- 使用RETURNING子句
UPDATE users
SET last_login = CURRENT_TIMESTAMP
WHERE user_id = 123
RETURNING user_id, last_login;
```

## 删除数据（Delete）

### 1. 基本DELETE语句

```sql
-- 基本删除
DELETE FROM users
WHERE status = 'inactive';

-- 使用USING子句
DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.order_id
    AND o.status = 'cancelled';
```

### 2. 批量删除

```sql
-- 使用子查询删除
DELETE FROM products
WHERE product_id IN (
    SELECT product_id
    FROM order_items
    GROUP BY product_id
    HAVING SUM(quantity) = 0
);

-- 删除并归档
WITH deleted_rows AS (
    DELETE FROM orders
    WHERE created_at < '2023-01-01'
    RETURNING *
)
INSERT INTO archived_orders
SELECT * FROM deleted_rows;
```

## 事务处理

### 1. 基本事务

```sql
-- 开始事务
BEGIN;

-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- 提交或回滚
COMMIT;
-- 或
ROLLBACK;
```

### 2. 保存点

```sql
-- 使用保存点
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
    SAVEPOINT my_savepoint;
    
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
    -- 如果出现错误
    ROLLBACK TO my_savepoint;
    
    -- 继续其他操作
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 3;
COMMIT;
```

## 最佳实践

### 1. 性能优化

```sql
-- 批量插入而不是多次单条插入
INSERT INTO logs (event_type, message) VALUES 
    ('info', 'log1'),
    ('info', 'log2'),
    ('error', 'log3');

-- 使用适当的索引
CREATE INDEX idx_users_email ON users(email);

-- 使用EXPLAIN分析查询性能
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending';
```

### 2. 安全性考虑

```sql
-- 使用参数化查询而不是字符串拼接
-- 好的做法（使用预处理语句）：
PREPARE user_query AS 
    SELECT * FROM users WHERE user_id = $1;
EXECUTE user_query(123);

-- 使用适当的权限控制
GRANT SELECT, INSERT ON orders TO app_user;
REVOKE DELETE ON orders FROM app_user;
```

### 3. 数据完整性

```sql
-- 使用约束确保数据完整性
CREATE TABLE orders (
    order_id serial PRIMARY KEY,
    user_id integer REFERENCES users(user_id),
    total_amount decimal(10,2) CHECK (total_amount >= 0),
    status varchar(20) DEFAULT 'pending'
);

-- 使用触发器维护数据一致性
CREATE TRIGGER update_order_total
    AFTER INSERT OR UPDATE OR DELETE ON order_items
    FOR EACH ROW
    EXECUTE FUNCTION update_order_total();
```

## 总结

掌握CRUD操作是使用PostgreSQL数据库的基础。在实际应用中，应该注意：

1. 使用合适的SQL语句和查询优化
2. 正确处理事务和并发
3. 确保数据安全和完整性
4. 遵循最佳实践提高性能

通过合理使用这些操作，可以构建高效、可靠的数据库应用。在开发过程中，要根据具体场景选择合适的操作方式，并注意性能优化和安全性考虑。

元素码农