PostgreSQL教程FG190-PG核心SQL命令速查：DML命令全参数

本文档风哥主要介绍PostgreSQL核心DML（数据操作语言）命令的全参数详解，包括INSERT、UPDATE、DELETE、SELECT等命令的语法和使用方法。风哥教程参考PostgreSQL官方文档SQL Commands内容，适合数据库开发人员和DBA在学习和测试中使用，如果要应用于生产环境则需要自行确认。

Part01-基础概念与理论知识

1.1 PostgreSQL DML命令的概念

DML（Data Manipulation Language）命令是用于操作数据库中数据的SQL命令，包括插入、更新、删除和查询数据。PostgreSQL支持丰富的DML命令，用于管理表中的数据。更多视频教程www.fgedu.net.cn

PostgreSQL DML命令的特点：

用于操作表中的数据
可以在事务中执行
支持复杂的条件和表达式
可以返回操作结果
支持批量操作

1.2 PostgreSQL DML命令分类

PostgreSQL DML命令可以分为以下几类：

插入命令：INSERT – 向表中插入数据
更新命令：UPDATE – 更新表中的数据
删除命令：DELETE – 删除表中的数据
查询命令：SELECT – 查询表中的数据
其他命令：MERGE, COPY等

1.3 PostgreSQL DML命令语法

PostgreSQL DML命令的基本语法结构：

— INSERT命令语法
INSERT INTO table_name [(column1, column2, …)]
VALUES (value1, value2, …)
[RETURNING * | output_expression [AS output_name]];

— UPDATE命令语法
UPDATE table_name
SET column1 = value1, column2 = value2, …
[WHERE condition]
[RETURNING * | output_expression [AS output_name]];

— DELETE命令语法
DELETE FROM table_name
[WHERE condition]
[RETURNING * | output_expression [AS output_name]];

— SELECT命令语法
SELECT [DISTINCT] select_expression [, select_expression, …]
FROM table_reference [, table_reference, …]
[WHERE condition]
[GROUP BY grouping_element [, grouping_element, …]]
[HAVING condition]
[ORDER BY sort_expression [ASC | DESC] [, sort_expression [ASC | DESC], …]]
[LIMIT { number | ALL }]
[OFFSET number [ROW | ROWS]]
[FETCH { FIRST | NEXT } [ number ] { ROW | ROWS } ONLY];

风哥提示：DML命令是数据库操作的核心，掌握这些命令对于数据库开发和维护非常重要。学习交流加群风哥微信: itpux-com

Part02-生产环境规划与建议

2.1 PostgreSQL DML命令最佳实践

PostgreSQL DML命令最佳实践：

# INSERT最佳实践
– 使用批量插入提高性能
– 明确指定列名，避免依赖列顺序
– 使用RETURNING子句获取插入的行
– 考虑使用COPY命令导入大量数据

# UPDATE最佳实践
– 使用WHERE子句限制更新范围
– 避免更新所有行
– 考虑使用RETURNING子句获取更新的行
– 对于大表，分批更新

# DELETE最佳实践
– 使用WHERE子句限制删除范围
– 避免删除所有行
– 考虑使用TRUNCATE TABLE删除所有数据
– 对于大表，分批删除

# SELECT最佳实践
– 只选择需要的列
– 使用LIMIT限制返回行数
– 使用索引加速查询
– 避免在WHERE子句中使用函数

2.2 PostgreSQL DML命令性能考虑

PostgreSQL DML命令性能考虑：

# INSERT性能
– 使用批量插入（多条VALUES）
– 关闭自动提交，使用显式事务
– 考虑使用UNLOGGED TABLE（但会丢失数据）
– 避免在插入时触发过多的触发器

# UPDATE性能
– 只更新必要的列
– 使用索引加速WHERE子句
– 避免更新索引列
– 对于大表，考虑使用pg_repack

# DELETE性能
– 使用索引加速WHERE子句
– 对于大表，使用TRUNCATE TABLE
– 避免删除大量数据时使用DELETE
– 考虑使用分区表，按分区删除

# SELECT性能
– 使用索引加速查询
– 避免使用SELECT *
– 使用EXPLAIN分析查询计划
– 考虑使用物化视图

2.3 PostgreSQL DML命令安全考虑

PostgreSQL DML命令安全考虑：

参数化查询：使用参数化查询，避免SQL注入
权限控制：确保只有授权用户可以执行DML命令
备份：在执行重要的DML命令前备份数据
测试：在测试环境中测试DML命令
监控：监控DML命令的执行情况

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在执行DML命令前，一定要进行充分的测试，确保命令的正确性和安全性，避免对生产环境造成影响。学习交流加群风哥QQ113257174

Part03-生产环境项目实施方案

3.1 PostgreSQL INSERT命令详解

3.1.1 基本INSERT命令

— 基本语法
INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …);

— 示例
INSERT INTO fgedu_fgedus (name, email, age)
VALUES (‘John Doe’, ‘john.doe@fgedu.net.cn’, 30);

3.1.2 批量INSERT命令

— 批量插入
INSERT INTO fgedu_fgedus (name, email, age)
VALUES
(‘John Doe’, ‘john.doe@fgedu.net.cn’, 30),
(‘Jane Smith’, ‘jane.smith@fgedu.net.cn’, 25),
(‘Bob Johnson’, ‘bob.johnson@fgedu.net.cn’, 35);

3.1.3 INSERT … RETURNING命令

— 使用RETURNING子句
INSERT INTO fgedu_fgedus (name, email, age)
VALUES (‘John Doe’, ‘john.doe@fgedu.net.cn’, 30)
RETURNING id, name, email;

3.1.4 INSERT … SELECT命令

— 从其他表插入数据
INSERT INTO fgedu_fgedus_archive (id, name, email, age, archived_at)
SELECT id, name, email, age, CURRENT_TIMESTAMP
FROM fgedu_fgedus
WHERE age > 65;

3.2 PostgreSQL UPDATE命令详解

3.2.1 基本UPDATE命令

— 基本语法
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;

— 示例
UPDATE fgedu_fgedus
SET age = 31, updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

3.2.2 UPDATE … RETURNING命令

— 使用RETURNING子句
UPDATE fgedu_fgedus
SET age = age + 1
WHERE id = 1
RETURNING id, name, age;

3.2.3 多表UPDATE命令

— 多表更新
UPDATE fgedu_fgedus u
SET u.age = u.age + 1
FROM fgedu_departments d
WHERE u.department_id = d.id AND d.name = ‘IT’;

3.3 PostgreSQL DELETE命令详解

3.3.1 基本DELETE命令

— 基本语法
DELETE FROM table_name
WHERE condition;

— 示例
DELETE FROM fgedu_fgedus
WHERE id = 1;

3.3.2 DELETE … RETURNING命令

— 使用RETURNING子句
DELETE FROM fgedu_fgedus
WHERE age > 65
RETURNING id, name, age;

3.3.3 多表DELETE命令

— 多表删除
DELETE FROM u
USING fgedu_fgedus u
JOIN fgedu_departments d ON u.department_id = d.id
WHERE d.name = ‘IT’;

3.4 PostgreSQL SELECT命令详解

3.4.1 基本SELECT命令

— 基本语法
SELECT column1, column2, …
FROM table_name
WHERE condition;

— 示例
SELECT id, name, email
FROM fgedu_fgedus
WHERE age > 25;

3.4.2 高级SELECT命令

— 带排序和限制
SELECT id, name, email
FROM fgedu_fgedus
WHERE age > 25
ORDER BY name ASC
LIMIT 10 OFFSET 5;

— 带分组和聚合
SELECT department_id, COUNT(*), AVG(age)
FROM fgedu_fgedus
GROUP BY department_id
HAVING COUNT(*) > 5;

— 带连接
SELECT u.id, u.name, d.name AS department
FROM fgedu_fgedus u
LEFT JOIN fgedu_departments d ON u.department_id = d.id;

风哥提示：DML命令的参数和选项非常丰富，使用时需要根据具体情况选择合适的参数，以达到最佳效果。更多学习教程公众号风哥教程itpux_com

Part04-生产案例与实战讲解

4.1 PostgreSQL INSERT命令实战示例

插入数据到产品表和订单表。

— 插入产品数据
INSERT INTO fgedu_products (name, description, price, stock, category_id)
VALUES
(‘Product A’, ‘Description for Product A’, 100.00, 100, 1),
(‘Product B’, ‘Description for Product B’, 200.00, 50, 1),
(‘Product C’, ‘Description for Product C’, 300.00, 75, 2),
(‘Product D’, ‘Description for Product D’, 400.00, 25, 2),
(‘Product E’, ‘Description for Product E’, 500.00, 10, 3);

— 插入订单数据
INSERT INTO fgedu_orders (customer_id, order_date, total_amount, status)
VALUES
(1, CURRENT_TIMESTAMP, 300.00, ‘pending’),
(2, CURRENT_TIMESTAMP, 600.00, ‘processing’),
(3, CURRENT_TIMESTAMP, 900.00, ‘completed’);

— 插入订单项目数据
INSERT INTO fgedu_order_items (order_id, product_id, quantity, unit_price)
VALUES
(1, 1, 1, 100.00),
(1, 2, 1, 200.00),
(2, 3, 2, 300.00),
(3, 5, 1, 500.00),
(3, 4, 1, 400.00);

4.2 PostgreSQL UPDATE命令实战示例

更新产品库存和订单状态。

— 更新产品库存
UPDATE fgedu_products
SET stock = stock – 1
WHERE id = 1;

— 更新订单状态
UPDATE fgedu_orders
SET status = ‘completed’, updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

— 批量更新产品价格
UPDATE fgedu_products
SET price = price * 1.1
WHERE category_id = 1;

— 使用RETURNING子句
UPDATE fgedu_fgedus
SET age = age + 1
WHERE department_id = 1
RETURNING id, name, age;

4.3 PostgreSQL DELETE命令实战示例

删除过期订单和无效用户。

— 删除过期订单
DELETE FROM fgedu_orders
WHERE order_date < CURRENT_TIMESTAMP - INTERVAL '30 days'; -- 删除无效用户 DELETE FROM fgedu_fgedus WHERE email IS NULL OR email = ''; -- 使用RETURNING子句 DELETE FROM fgedu_products WHERE stock = 0 RETURNING id, name, stock; -- 截断表（删除所有数据） TRUNCATE TABLE fgedu_order_items; TRUNCATE TABLE fgedu_orders;

4.4 PostgreSQL SELECT命令实战示例

查询产品、订单和用户数据。

— 查询产品列表
SELECT id, name, price, stock
FROM fgedu_products
ORDER BY price DESC;

— 查询订单详情
SELECT o.id, o.order_date, o.total_amount, o.status,
c.name AS customer_name
FROM fgedu_orders o
JOIN fgedu_customers c ON o.customer_id = c.id
WHERE o.status = ‘completed’;

— 查询订单项目
SELECT o.id AS order_id, p.name AS product_name, oi.quantity, oi.unit_price
FROM fgedu_order_items oi
JOIN fgedu_orders o ON oi.order_id = o.id
JOIN fgedu_products p ON oi.product_id = p.id
WHERE o.id = 1;

— 统计查询
SELECT c.name AS category, COUNT(*) AS product_count, AVG(p.price) AS avg_price
FROM fgedu_products p
JOIN fgedu_categories c ON p.category_id = c.id
GROUP BY c.name
HAVING COUNT(*) > 1
ORDER BY product_count DESC;

风哥教程针对风哥教程针对风哥教程针对生产环境建议：在执行DML命令时，一定要注意数据的完整性和一致性，避免误操作导致数据丢失或损坏。from PostgreSQL视频:www.itpux.com

Part05-风哥经验总结与分享

5.1 PostgreSQL DML命令速查表

PostgreSQL DML命令速查表：

— 插入数据
INSERT INTO table_name (columns) VALUES (values);
INSERT INTO table_name (columns) VALUES (values1), (values2), …;
INSERT INTO table_name SELECT … FROM other_table;

— 更新数据
UPDATE table_name SET column = value WHERE condition;
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
UPDATE table_name SET column = value RETURNING *;

— 删除数据
DELETE FROM table_name WHERE condition;
DELETE FROM table_name RETURNING *;
TRUNCATE TABLE table_name;

— 查询数据
SELECT * FROM table_name;
SELECT columns FROM table_name WHERE condition;
SELECT columns FROM table_name ORDER BY column;
SELECT columns FROM table_name LIMIT 10;
SELECT columns FROM table_name GROUP BY column HAVING condition;
SELECT columns FROM table1 JOIN table2 ON table1.column = table2.column;

5.2 PostgreSQL DML命令常见问题排查

PostgreSQL DML命令常见问题排查：

# 1. INSERT失败
# 检查约束
$ psql -d fgedudb -U fgedu -c “INSERT INTO fgedu_fgedus (name, email) VALUES (‘John’, ‘john@fgedu.net.cn’);”

# 检查数据类型
$ psql -d fgedudb -U fgedu -c “INSERT INTO fgedu_fgedus (name, age) VALUES (‘John’, ‘thirty’);”

# 检查权限
$ psql -d fgedudb -U fgedu -c “INSERT INTO fgedu_fgedus (name, email) VALUES (‘John’, ‘john@fgedu.net.cn’);”

# 2. UPDATE失败
# 检查WHERE子句
$ psql -d fgedudb -U fgedu -c “UPDATE fgedu_fgedus SET age = 30 WHERE id = 999;”

# 检查约束
$ psql -d fgedudb -U fgedu -c “UPDATE fgedu_fgedus SET email = NULL WHERE id = 1;”

# 3. DELETE失败
# 检查WHERE子句
$ psql -d fgedudb -U fgedu -c “DELETE FROM fgedu_fgedus WHERE id = 999;”

# 检查外键约束
$ psql -d fgedudb -U fgedu -c “DELETE FROM fgedu_categories WHERE id = 1;”

# 4. SELECT失败
# 检查表名和列名
$ psql -d fgedudb -U fgedu -c “SELECT * FROM non_existent_table;”

# 检查权限
$ psql -d fgedudb -U fgedu -c “SELECT * FROM fgedu_fgedus;”

5.3 PostgreSQL DML命令优化建议

PostgreSQL DML命令优化建议：

批量操作：使用批量INSERT和UPDATE提高性能
索引使用：为WHERE子句中的列创建索引
事务管理：合理使用事务，避免长时间锁定
查询优化：使用EXPLAIN分析查询计划，优化SQL语句
分区表：对于大表，使用分区表提高DML性能
统计信息：定期更新统计信息，确保查询优化器做出正确的决策

风哥提示：DML命令是数据库操作的核心，掌握这些命令的使用方法和最佳实践，可以提高数据库操作的效率和安全性。建议在执行DML命令前，充分了解命令的影响，避免对生产环境造成不必要的损失。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html