当前位置： 首页 > news >正文

数据库 DML 语句详解：语法与注意事项

news 2025/6/30 7:18:27

数据库 DML 语句详解：语法与注意事项

DML（Data Manipulation Language，数据操作语言）用于操作数据库中的数据，主要包括 SELECT、INSERT、UPDATE、DELETE 等语句。下面我将详细说明每种操作的语法、使用场景和关键注意事项。

一、SELECT 查询语句

基本语法

SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[ORDER BY column1 [ASC|DESC], ...]
[LIMIT [offset,] row_count];

关键注意事项

  1. 避免 SELECT *

    -- 错误：获取所有列（性能差）
SELECT * FROM employees;-- 正确：只获取需要的列
SELECT id, name, salary FROM employees;

  2. WHERE 子句优化

    • 避免在列上使用函数（索引失效）：
      -- 错误：索引失效
SELECT * FROM orders WHERE YEAR(order_date) = 2023;-- 正确：使用范围查询
SELECT * FROM orders 
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';
    • 避免使用 OR 连接不同列（改用 UNION）：
      -- 低效
SELECT * FROM products 
WHERE category = 'Electronics' OR price > 1000;-- 高效
SELECT * FROM products WHERE category = 'Electronics'
UNION
SELECT * FROM products WHERE price > 1000;

  3. GROUP BY 陷阱

    • 确保 SELECT 列在 GROUP BY 中或使用聚合函数：
      -- 错误（MySQL ONLY_FULL_GROUP_BY 模式）
SELECT department, name, AVG(salary) 
FROM employees 
GROUP BY department;-- 正确
SELECT department, MAX(name) AS sample_name, AVG(salary)
FROM employees 
GROUP BY department;

  4. JOIN 优化

    -- 优先使用 INNER JOIN
SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;-- 避免 WHERE 中的隐式连接
SELECT e.name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.id; -- 已过时语法

二、INSERT 插入语句

基本语法

-- 插入单行
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);-- 插入多行
INSERT INTO table_name (column1, column2, ...)
VALUES (value1a, value2a, ...),(value1b, value2b, ...),...;-- 从查询结果插入
INSERT INTO table_name (column1, column2, ...)
SELECT col1, col2, ...
FROM another_table
[WHERE condition];

关键注意事项

  1. 批量插入性能优化

    -- 单条插入（低效）
INSERT INTO logs (message) VALUES ('Error 1');
INSERT INTO logs (message) VALUES ('Error 2');-- 批量插入（高效）
INSERT INTO logs (message) 
VALUES ('Error 1'), ('Error 2');

  2. 处理主键冲突

    -- 忽略重复插入
INSERT IGNORE INTO users (id, name) 
VALUES (1, 'John'), (2, 'Jane');-- 更新重复项
INSERT INTO users (id, name)
VALUES (1, 'Johnathan')
ON DUPLICATE KEY UPDATE name = VALUES(name);

  3. 自增主键处理

    -- 获取最后插入的ID
INSERT INTO orders (customer_id, amount)
VALUES (1001, 99.99);
SELECT LAST_INSERT_ID(); -- 返回新订单ID

三、UPDATE 更新语句

基本语法

UPDATE table_name
SET column1 = value1, column2 = value2, ...
[WHERE condition]
[ORDER BY ...] 
[LIMIT row_count];

关键注意事项

  1. WHERE 子句必须谨慎

    -- 危险：缺少WHERE会更新全表！
UPDATE employees SET salary = salary * 1.05;-- 安全：明确限定范围
UPDATE employees 
SET salary = salary * 1.05
WHERE department = 'Engineering';

  2. 多表更新

    -- 更新关联表数据
UPDATE employees e
JOIN departments d ON e.department_id = d.id
SET e.salary = e.salary * 1.10
WHERE d.location = 'New York';

  3. 大表更新策略

    -- 分批次更新（避免锁表）
UPDATE large_table 
SET status = 'processed'
WHERE status = 'pending'
LIMIT 1000; -- 每次更新1000行

四、DELETE 删除语句

基本语法

DELETE FROM table_name
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];

关键注意事项

  1. 备份先行

    -- 删除前创建备份
CREATE TABLE deleted_users AS
SELECT * FROM users WHERE status = 'inactive';-- 再执行删除
DELETE FROM users WHERE status = 'inactive';

  2. 级联删除

    -- 删除主表记录及关联记录
DELETE orders, order_items
FROM orders
JOIN order_items ON orders.id = order_items.order_id
WHERE orders.date < '2020-01-01';

  3. 高效删除大量数据

    -- 低效：逐行删除
DELETE FROM old_logs WHERE created_at < '2022-01-01';-- 高效：使用分区或批量删除
-- 方法1：分批删除
DELETE FROM old_logs 
WHERE created_at < '2022-01-01'
LIMIT 1000;-- 方法2：重建表（更快）
CREATE TABLE new_logs AS
SELECT * FROM old_logs 
WHERE created_at >= '2022-01-01';DROP TABLE old_logs;
RENAME TABLE new_logs TO old_logs;

五、事务控制（ACID 保证）

基本语法

START TRANSACTION;-- 执行DML操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;-- 提交或回滚
COMMIT; -- 确认更改
-- 或
ROLLBACK; -- 撤销更改

关键注意事项

  1. 保持事务简短

    • 长时间事务会锁定资源，影响并发性能

  2. 设置合适的事务隔离级别

    -- 设置读已提交（避免脏读）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

  3. 错误处理

    START TRANSACTION;
BEGINDECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINROLLBACK;RESIGNAL;END;-- DML操作INSERT INTO ...;UPDATE ...;COMMIT;
END;

六、安全与性能最佳实践

  1. 防 SQL 注入

    # Python 示例（错误）
query = f"SELECT * FROM users WHERE name = '{user_input}'"# 正确：使用参数化查询
cursor.execute("SELECT * FROM users WHERE name = %s", (user_input,))

  2. 索引优化

    • WHERE、JOIN、ORDER BY 条件列创建索引
    • 避免在索引列上使用函数

  3. 执行计划分析

    EXPLAIN SELECT * FROM orders 
WHERE customer_id = 123 AND status = 'shipped';

  4. 锁机制理解

    锁类型描述影响
    行级锁锁定单行（InnoDB）并发性好
    表级锁锁定整表（MyISAM）并发性差
    间隙锁锁定范围（防止幻读）可能降低并发

七、各数据库差异

特性MySQLPostgreSQLSQL Server
分页语法LIMIT 10 OFFSET 20LIMIT 10 OFFSET 20OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
插入返回IDLAST_INSERT_ID()RETURNING idOUTPUT INSERTED.id
UPSERTON DUPLICATE KEY UPDATEON CONFLICT DO UPDATEMERGE
批量导入LOAD DATA INFILECOPYBULK INSERT

总结：DML 操作黄金法则

  1. SELECT 前先 EXPLAIN - 分析查询性能
  2. UPDATE/DELETE 必带 WHERE - 避免全表操作
  3. 大操作分批次执行 - 防止锁表阻塞
  4. 关键操作使用事务 - 保证数据一致性
  5. 生产环境先备份 - 数据无价
  6. 参数化防注入 - 安全第一

通过遵循这些语法规则和注意事项，可以确保 DML 操作高效、安全地执行，同时维护数据库的完整性和性能。

http://www.lqws.cn/news/565561.html

相关文章：

  • Android大图加载优化：BitmapRegionDecoder深度解析与实战
  • 【分布式 ID】生成唯一 ID 的几种方式
  • 面试150 螺旋矩阵
  • 模拟工作队列 - 华为OD机试真题(JavaScript卷)
  • llama.cpp学习笔记：后端加载
  • Windows系统安装鸿蒙模拟器
  • 接口自动化测试（Python+pytest+PyMySQL+Jenkins）
  • OpenLayers 全屏控件介绍
  • Wpf布局之StackPanel！
  • Mac电脑手动安装原版Stable Diffusion，开启本地API调用生成图片
  • 在Mac上查找并删除Java 21.0.5
  • 【Canvas与标志】圆规脚足球俱乐部标志
  • Spring Cloud Gateway 实战：从网关搭建到过滤器与跨域解决方案
  • 浮油 - 3 相分层和自由表面流 CFX 模拟
  • 医疗AI智能基础设施构建：向量数据库矩阵化建设流程分析
  • js 基础
  • PCB工艺学习与总结-20250628
  • JVM——垃圾回收
  • Kafka4.0初体验
  • 系统架构设计师备考之架构设计专业知识
  • 软考 系统架构设计师系列知识点之杂项集萃（100）
  • TCP/UDP协议深度解析（三）：TCP流量控制的魔法—滑动窗口、拥塞控制与ACK的智慧
  • Cursor 教程：用 Cursor 创建第一个 Java 项目
  • Webpack 中的 Loader 和 Plugin 全面详解
  • 全新大模型开源，腾讯（int4能打DeepSeek） Vs 谷歌（2GB运行多模态）
  • 【GESP 四级】一个程序掌握大部分知识点
  • 学习使用dotnet-dump工具分析.net内存转储文件（3）
  • 深入理解Mysql索引底层数据结构和算法
  • NeRF-Lidar实景重建：大疆Mavic 4 Pro低成本建模方案（2025实战指南）
  • 当SAM遇到声纳图像时之论文阅读