PostgreSQL教程FG029-PG数据操作语言（DML）：INSERT/UPDATE/DELETE高级用法
目录大纲
Part01-基础概念与理论知识
1.1 DML语句的概念与特点
数据操作语言（DML）是用于操作数据库中数据的SQL语句，主要包括INSERT、UPDATE和DELETE。DML语句的主要特点包括：
- 修改数据：直接操作表中的数据
- 事务性：操作可以回滚
- 性能影响：可能影响数据库性能
- 锁定：可能会锁定数据行或表
学习交流加群风哥微信: itpux-com
1.2 高级DML操作的应用场景
高级DML操作适合以下场景：
- 批量数据处理：需要一次性处理大量数据
- 复杂数据迁移：需要从一个表迁移数据到另一个表
- 数据同步：需要保持不同表之间的数据同步
- 数据转换：需要在插入或更新时进行数据转换
- 条件更新：需要根据复杂条件更新数据
更多视频教程www.fgedu.net.cn
Part02-生产环境规划与建议
2.1 INSERT高级操作的规划与建议
INSERT高级操作规划建议：
- 批量插入：使用COPY命令或批量INSERT语句提高性能
- 数据验证：在插入前进行数据验证，确保数据质量
- 错误处理：使用ON CONFLICT子句处理冲突
- 性能考虑：避免在插入时触发过多触发器
- 事务管理：对于大量插入，合理设置事务大小
风哥提示：批量插入时，建议使用COPY命令，它比INSERT语句快得多。
2.2 UPDATE与DELETE高级操作的注意事项
UPDATE与DELETE高级操作注意事项：
- 条件检查：确保WHERE子句正确，避免误操作
- 性能影响：大表的UPDATE/DELETE可能会影响性能
- 锁定问题：可能会导致长时间锁定，影响并发
- 事务日志：会生成大量WAL日志，需要考虑空间
- 回滚考虑：大操作可能需要大量回滚空间
建议：
- 分批处理：将大操作分成多个小批次
- 使用索引：确保WHERE子句中的列有索引
- 监控执行：监控长时间运行的UPDATE/DELETE操作
- 备份：在执行大操作前进行备份
更多学习教程公众号风哥教程itpux_com
Part03-生产环境项目实施方案
3.1 INSERT高级用法
3.1.1 批量插入
INSERT INTO fgedu_users (name, email) VALUES
(‘张三’, ‘zhangsan@fgedu.net.cn’),
(‘李四’, ‘lisi@fgedu.net.cn’),
(‘王五’, ‘wangwu@fgedu.net.cn’),
(‘赵六’, ‘zhaoliu@fgedu.net.cn’),
(‘钱七’, ‘qianqi@fgedu.net.cn’);
— 从另一个表插入数据
INSERT INTO fgedu_users_archive (id, name, email, created_at)
SELECT id, name, email, NOW()
FROM fgedu_users
WHERE created_at < '2026-01-01';
-- 使用DEFAULT VALUES插入默认值
INSERT INTO fgedu_logs DEFAULT VALUES;
3.1.2 INSERT ON CONFLICT（UPSERT）
CREATE TABLE fgedu_products (
id SERIAL PRIMARY KEY,
product_code VARCHAR(50) UNIQUE,
name VARCHAR(100),
price DECIMAL(10, 2)
);
— 插入数据，冲突时更新
INSERT INTO fgedu_products (product_code, name, price)
VALUES (‘P001’, ‘手机’, 5999.00)
ON CONFLICT (product_code) DO UPDATE
SET name = EXCLUDED.name,
price = EXCLUDED.price;
— 插入多条数据，冲突时忽略
INSERT INTO fgedu_products (product_code, name, price)
VALUES
(‘P001’, ‘智能手机’, 6999.00),
(‘P002’, ‘电脑’, 9999.00),
(‘P003’, ‘平板’, 3999.00)
ON CONFLICT (product_code) DO NOTHING;
from PostgreSQL视频:www.itpux.com
3.1.3 INSERT RETURNING
INSERT INTO fgedu_users (name, email)
VALUES (‘孙八’, ‘sunba@fgedu.net.cn’)
RETURNING id, name, email;
— 插入多条数据并返回
INSERT INTO fgedu_products (product_code, name, price)
VALUES
(‘P004’, ‘耳机’, 999.00),
(‘P005’, ‘键盘’, 1999.00)
RETURNING id, product_code, name;
3.2 UPDATE高级用法
3.2.1 条件更新
UPDATE fgedu_products
SET price = price * 1.1
WHERE category = ‘电子产品’ AND price < 5000; -- 使用子查询更新 UPDATE fgedu_customers c SET total_orders = ( SELECT COUNT(*) FROM fgedu_orders o WHERE o.customer_id = c.id ); -- 多表关联更新 UPDATE fgedu_orders o SET status = 'completed' FROM fgedu_payments p WHERE o.id = p.order_id AND p.payment_date IS NOT NULL;
3.2.2 UPDATE RETURNING
UPDATE fgedu_products
SET price = price * 0.9
WHERE product_code = ‘P001’
RETURNING id, product_code, name, price;
— 批量更新并返回
UPDATE fgedu_users
SET last_login = NOW()
WHERE last_login < '2026-01-01'
RETURNING id, name, last_login;
学习交流加群风哥QQ113257174
3.2.3 复杂更新操作
UPDATE fgedu_employees
SET salary = CASE
WHEN department = ‘技术部’ THEN salary * 1.15
WHEN department = ‘市场部’ THEN salary * 1.10
ELSE salary * 1.05
END;
— 使用窗口函数进行更新
WITH ranked_employees AS (
SELECT
id,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM fgedu_employees
)
UPDATE fgedu_employees e
SET is_top_performer = (r.rank = 1)
FROM ranked_employees r
WHERE e.id = r.id;
3.3 DELETE高级用法
3.3.1 条件删除
DELETE FROM fgedu_logs
WHERE log_date < '2026-01-01'; -- 使用子查询删除 DELETE FROM fgedu_orders WHERE id IN ( SELECT order_id FROM fgedu_order_items WHERE quantity = 0 ); -- 多表关联删除 DELETE FROM fgedu_order_items oi USING fgedu_orders o WHERE oi.order_id = o.id AND o.status = 'cancelled';
3.3.2 DELETE RETURNING
DELETE FROM fgedu_users
WHERE last_login < '2025-01-01' RETURNING id, name, email, last_login; -- 删除特定条件的数据并返回 DELETE FROM fgedu_products WHERE price < 100 RETURNING id, product_code, name, price;
from PostgreSQL视频:www.itpux.com
3.3.3 批量删除策略
DO $$
DECLARE
batch_size INT := 1000;
deleted_rows INT := 1;
BEGIN
WHILE deleted_rows > 0 LOOP
DELETE FROM fgedu_old_logs
WHERE log_date < '2026-01-01' LIMIT batch_size; GET DIAGNOSTICS deleted_rows = ROW_COUNT; -- 暂停一下，避免系统负载过高 PERFORM pg_sleep(0.1); END LOOP; END; $$ LANGUAGE plpgsql;
Part04-生产案例与实战讲解
4.1 INSERT实战案例
4.1.1 数据迁移
CREATE TABLE fgedu_old_customers (
customer_id INTEGER,
customer_name VARCHAR(100),
customer_email VARCHAR(100),
create_date DATE
);
— 插入测试数据
INSERT INTO fgedu_old_customers (customer_id, customer_name, customer_email, create_date) VALUES
(1, ‘张三’, ‘zhangsan@fgedu.net.cn’, ‘2024-01-01’),
(2, ‘李四’, ‘lisi@fgedu.net.cn’, ‘2024-02-01’),
(3, ‘王五’, ‘wangwu@fgedu.net.cn’, ‘2024-03-01’);
— 创建目标表
CREATE TABLE fgedu_new_customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at TIMESTAMP DEFAULT NOW(),
status VARCHAR(20) DEFAULT ‘active’
);
— 迁移数据（带数据转换）
INSERT INTO fgedu_new_customers (name, email, created_at)
SELECT
customer_name,
LOWER(customer_email), — 转换为小写
create_date::TIMESTAMP
FROM fgedu_old_customers
WHERE customer_email IS NOT NULL;
— 查询迁移结果
SELECT * FROM fgedu_new_customers;
4.1.2 批量数据导入
CREATE TABLE fgedu_sales_data (
id SERIAL PRIMARY KEY,
sale_date DATE NOT NULL,
product_id INTEGER,
quantity INTEGER,
unit_price DECIMAL(10, 2),
total_amount DECIMAL(10, 2)
);
— 方法1：使用COPY命令（最快）
— COPY fgedu_sales_data (sale_date, product_id, quantity, unit_price, total_amount)
— FROM ‘/path/to/sales_data.csv’ DELIMITER ‘,’ CSV HEADER;
— 方法2：使用批量INSERT
INSERT INTO fgedu_sales_data (sale_date, product_id, quantity, unit_price, total_amount)
VALUES
(‘2026-04-01’, 1, 10, 100.00, 1000.00),
(‘2026-04-01’, 2, 5, 200.00, 1000.00),
(‘2026-04-02’, 1, 15, 100.00, 1500.00),
(‘2026-04-02’, 3, 8, 150.00, 1200.00),
(‘2026-04-03’, 2, 12, 200.00, 2400.00),
(‘2026-04-03’, 4, 3, 500.00, 1500.00),
(‘2026-04-04’, 1, 20, 100.00, 2000.00),
(‘2026-04-04’, 3, 10, 150.00, 1500.00);
— 查询导入结果
SELECT * FROM fgedu_sales_data;
风哥提示：对于大量数据导入，COPY命令是最高效的方式。
4.2 UPDATE实战案例
4.2.1 库存更新
CREATE TABLE fgedu_inventory (
product_id INTEGER PRIMARY KEY,
product_name VARCHAR(100),
current_stock INTEGER,
reorder_level INTEGER
);
— 插入测试数据
INSERT INTO fgedu_inventory (product_id, product_name, current_stock, reorder_level) VALUES
(1, ‘手机’, 50, 20),
(2, ‘电脑’, 30, 10),
(3, ‘平板’, 40, 15),
(4, ‘耳机’, 100, 30);
— 创建订单表
CREATE TABLE fgedu_sales_orders (
order_id SERIAL PRIMARY KEY,
product_id INTEGER,
quantity INTEGER,
order_date DATE
);
— 插入订单数据
INSERT INTO fgedu_sales_orders (product_id, quantity, order_date) VALUES
(1, 5, ‘2026-04-01’),
(2, 3, ‘2026-04-01’),
(3, 10, ‘2026-04-02’);
— 更新库存
UPDATE fgedu_inventory i
SET current_stock = i.current_stock – (
SELECT COALESCE(SUM(quantity), 0)
FROM fgedu_sales_orders o
WHERE o.product_id = i.product_id AND o.order_date = ‘2026-04-01’
)
WHERE i.product_id IN (
SELECT DISTINCT product_id
FROM fgedu_sales_orders
WHERE order_date = ‘2026-04-01’
);
— 查询更新后的库存
SELECT * FROM fgedu_inventory;
4.2.2 状态批量更新
CREATE TABLE fgedu_customer_orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
total_amount DECIMAL(10, 2),
status VARCHAR(20) DEFAULT ‘pending’,
payment_date DATE
);
— 插入测试数据
INSERT INTO fgedu_customer_orders (customer_id, order_date, total_amount, status, payment_date) VALUES
(1, ‘2026-04-01’, 1000.00, ‘pending’, ‘2026-04-01’),
(2, ‘2026-04-01’, 2000.00, ‘pending’, NULL),
(3, ‘2026-04-02’, 1500.00, ‘pending’, ‘2026-04-02’),
(4, ‘2026-04-02’, 3000.00, ‘pending’, NULL);
— 批量更新已付款订单的状态
UPDATE fgedu_customer_orders
SET status = ‘paid’
WHERE payment_date IS NOT NULL AND status = ‘pending’;
— 查询更新结果
SELECT * FROM fgedu_customer_orders;
更多视频教程www.fgedu.net.cn
4.3 DELETE实战案例
4.3.1 清理过期数据
CREATE TABLE fgedu_application_logs (
id SERIAL PRIMARY KEY,
log_time TIMESTAMP DEFAULT NOW(),
log_level VARCHAR(20),
message TEXT
);
— 插入测试数据
INSERT INTO fgedu_application_logs (log_time, log_level, message) VALUES
(NOW() – INTERVAL ’30 days’, ‘INFO’, ‘Application started’),
(NOW() – INTERVAL ’25 days’, ‘ERROR’, ‘Database connection failed’),
(NOW() – INTERVAL ’20 days’, ‘INFO’, ‘User logged in’),
(NOW() – INTERVAL ’15 days’, ‘WARNING’, ‘Disk space low’),
(NOW() – INTERVAL ’10 days’, ‘INFO’, ‘Backup completed’),
(NOW() – INTERVAL ‘5 days’, ‘ERROR’, ‘API request failed’),
(NOW(), ‘INFO’, ‘System status check’);
— 清理30天前的日志
DELETE FROM fgedu_application_logs
WHERE log_time < NOW() - INTERVAL '30 days';
-- 清理非错误日志（保留错误日志）
DELETE FROM fgedu_application_logs
WHERE log_level != 'ERROR' AND log_time < NOW() - INTERVAL '15 days';
-- 查询清理结果
SELECT * FROM fgedu_application_logs ORDER BY log_time DESC;
4.3.2 级联删除
CREATE TABLE fgedu_departments (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
— 创建员工表（带外键）
CREATE TABLE fgedu_dept_employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department_id INTEGER REFERENCES fgedu_departments(id) ON DELETE CASCADE
);
— 插入测试数据
INSERT INTO fgedu_departments (name) VALUES
(‘技术部’),
(‘市场部’),
(‘财务部’);
INSERT INTO fgedu_dept_employees (name, department_id) VALUES
(‘张三’, 1),
(‘李四’, 1),
(‘王五’, 2),
(‘赵六’, 3);
— 删除部门（会级联删除相关员工）
DELETE FROM fgedu_departments WHERE id = 1;
— 查询结果
SELECT * FROM fgedu_departments;
SELECT * FROM fgedu_dept_employees;
学习交流加群风哥微信: itpux-com
