PostgreSQL教程

5.1 INSERT语句

基本插入

使用INSERT语句向表中插入数据：

-- 插入单行数据
INSERT INTO users (username, email, first_name, last_name) 
VALUES ('john_doe', 'john@example.com', 'John', 'Doe');

-- 插入多行数据
INSERT INTO users (username, email, first_name, last_name) 
VALUES 
    ('jane_smith', 'jane@example.com', 'Jane', 'Smith'),
    ('bob_wilson', 'bob@example.com', 'Bob', 'Wilson'),
    ('alice_brown', 'alice@example.com', 'Alice', 'Brown');

插入默认值

-- 插入默认值
INSERT INTO users (username, email) 
VALUES ('test_user', 'test@example.com');

-- 显式指定默认值
INSERT INTO users (username, email, created_at) 
VALUES ('test_user2', 'test2@example.com', DEFAULT);

从查询结果插入

-- 从另一个表插入数据
INSERT INTO archived_users (username, email, created_at)
SELECT username, email, created_at 
FROM users 
WHERE created_at < '2023-01-01';

-- 插入查询结果并添加常量值
INSERT INTO user_statistics (user_id, login_count, last_login_date)
SELECT id, 0, CURRENT_DATE 
FROM users 
WHERE is_active = true;

使用RETURNING子句

-- 插入数据并返回生成的ID
INSERT INTO users (username, email, first_name, last_name) 
VALUES ('new_user', 'new@example.com', 'New', 'User')
RETURNING id, username;

-- 插入数据并返回所有字段
INSERT INTO products (name, price, category_id) 
VALUES ('新产品', 99.99, 1)
RETURNING *;

5.2 UPDATE语句

基本更新

使用UPDATE语句修改表中的数据：

-- 更新单个字段
UPDATE users 
SET email = 'newemail@example.com' 
WHERE id = 1;

-- 更新多个字段
UPDATE users 
SET 
    email = 'updated@example.com',
    updated_at = CURRENT_TIMESTAMP
WHERE username = 'john_doe';

-- 更新所有记录（谨慎使用）
UPDATE users 
SET is_active = true;

条件更新

-- 使用复杂条件更新
UPDATE products 
SET price = price * 1.1 
WHERE category_id = 1 AND price < 100;

-- 使用子查询更新
UPDATE users 
SET last_login_date = CURRENT_TIMESTAMP 
WHERE id IN (SELECT user_id FROM login_logs WHERE login_date = CURRENT_DATE);

使用RETURNING子句

-- 更新数据并返回结果
UPDATE users 
SET email = 'updated@example.com' 
WHERE id = 1
RETURNING id, username, email;

5.3 DELETE语句

基本删除

使用DELETE语句从表中删除数据：

-- 删除单条记录
DELETE FROM users WHERE id = 1;

-- 删除多条记录
DELETE FROM users WHERE created_at < '2023-01-01';

-- 删除所有记录（谨慎使用）
DELETE FROM temp_data;

条件删除

-- 使用复杂条件删除
DELETE FROM orders 
WHERE status = 'cancelled' 
  AND created_at < CURRENT_DATE - INTERVAL '30 days';

-- 使用子查询删除
DELETE FROM users 
WHERE id IN (
    SELECT user_id 
    FROM user_sessions 
    WHERE last_activity < CURRENT_DATE - INTERVAL '1 year'
);

使用RETURNING子句

-- 删除数据并返回结果
DELETE FROM users 
WHERE id = 1
RETURNING id, username, email;

截断表

-- 快速删除表中所有数据（比DELETE更快）
TRUNCATE TABLE temp_data;

-- 截断多个表
TRUNCATE TABLE temp_data, log_data;

-- 重启标识序列
TRUNCATE TABLE users RESTART IDENTITY;

5.4 批量操作

批量插入优化

-- 使用COPY命令进行大批量数据导入（更快）
COPY users(username, email, first_name, last_name) 
FROM '/path/to/users.csv' 
DELIMITER ',' 
CSV HEADER;

-- 使用批量插入
INSERT INTO users (username, email, first_name, last_name) 
VALUES 
    ('user1', 'user1@example.com', 'User', 'One'),
    ('user2', 'user2@example.com', 'User', 'Two'),
    ('user3', 'user3@example.com', 'User', 'Three'),
    -- ... 更多数据
    ('user1000', 'user1000@example.com', 'User', 'Thousand');

批量更新优化

-- 使用CASE语句进行批量更新
UPDATE products 
SET price = CASE 
    WHEN category_id = 1 THEN price * 1.1
    WHEN category_id = 2 THEN price * 1.05
    ELSE price
END
WHERE category_id IN (1, 2);

-- 使用临时表进行批量更新
UPDATE users 
SET email = temp_users.new_email
FROM temp_users 
WHERE users.id = temp_users.user_id;

5.5 事务处理

基本事务

-- 开始事务
BEGIN;

-- 执行多个操作
INSERT INTO users (username, email) VALUES ('new_user', 'new@example.com');
INSERT INTO user_profiles (user_id, bio) VALUES (currval('users_id_seq'), 'New user bio');
UPDATE user_statistics SET user_count = user_count + 1;

-- 提交事务
COMMIT;

-- 或者回滚事务
-- ROLLBACK;

保存点

BEGIN;

INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');

-- 设置保存点
SAVEPOINT sp1;

INSERT INTO user_profiles (user_id, bio) VALUES (currval('users_id_seq'), 'User bio');

-- 回滚到保存点
ROLLBACK TO sp1;

-- 继续其他操作
INSERT INTO user_statistics (user_count) VALUES (1);

COMMIT;

5.6 数据导入导出

导出数据

-- 导出表数据到CSV文件
COPY users TO '/path/to/users_export.csv' 
DELIMITER ',' 
CSV HEADER;

-- 导出查询结果
COPY (
    SELECT username, email, created_at 
    FROM users 
    WHERE is_active = true
) TO '/path/to/active_users.csv' 
DELIMITER ',' 
CSV HEADER;

导入数据

-- 从CSV文件导入数据
COPY users(username, email, first_name, last_name) 
FROM '/path/to/users_import.csv' 
DELIMITER ',' 
CSV HEADER;

5.7 最佳实践

性能优化

• 批量操作：尽可能使用批量插入而不是逐条插入
• 索引考虑：在执行大量UPDATE/DELETE操作前考虑临时禁用索引
• 事务控制：合理使用事务，避免长时间持有锁
• WHERE子句：确保UPDATE/DELETE语句包含有效的WHERE条件

安全性

• SQL注入防护：使用参数化查询而不是字符串拼接
• 权限控制：为不同用户分配最小必要权限
• 数据备份：在执行重要数据操作前进行备份

-- 电商订单处理示例
BEGIN;

-- 创建新订单
INSERT INTO orders (user_id, total_amount, status) 
VALUES (1, 299.99, 'pending')
RETURNING id INTO order_id;

-- 添加订单项
INSERT INTO order_items (order_id, product_id, quantity, price) 
VALUES 
    (order_id, 1, 1, 199.99),
    (order_id, 2, 2, 50.00);

-- 更新产品库存
UPDATE products 
SET stock_quantity = stock_quantity - 1 
WHERE id = 1;

UPDATE products 
SET stock_quantity = stock_quantity - 2 
WHERE id = 2;

-- 更新用户统计
UPDATE user_statistics 
SET total_orders = total_orders + 1,
    total_spent = total_spent + 299.99
WHERE user_id = 1;

COMMIT;