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

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

news 2025/6/30 18:38:03

数据库 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.lryc.cn/news/577093.html

相关文章：

  • 训练营总结
  • C++高效结合主流工具：现代系统底层动力
  • 浅谈AI大模型-MCP
  • 基于SSM + JSP 的宿舍管理系统
  • JVM基础--JVM的组成
  • AlpineLinux安装部署elasticsearch
  • STM32——HAL库总结
  • 详解快速排序
  • http相关网络问题面试怎么答
  • 矩阵的逆 线性代数
  • Vue中keep-alive结合router实现部分页面缓存
  • 【NLP 实战】蒙古语情感分析：从 CNN 架构设计到模型训练的全流程解析（内附项目源码及模型成果）
  • 【Flask开发】嘿马文学web完整flask项目第2篇：2.用户认证,Json Web Token(JWT)【附代码文档】
  • Nginx漏洞处理指南
  • python pyecharts 数据分析及可视化（2）
  • Prompt工程标准化在多模型协同中的作用
  • swift-19-从OC到Swift、函数式编程
  • 设计模式 | 过滤器模式
  • MySQL之全场景常用工具链
  • MyBatis批量删除
  • 【系统分析师】2021年真题：案例分析-答案及详解
  • CppCon 2017 学习:Type Punning in C++17 Avoiding Pun-defined Behavior
  • 【硬核数学】2.4 驯服“梯度下降”：深度学习中的优化艺术与正则化技巧《从零构建机器学习、深度学习到LLM的数学认知》
  • Python爬虫：Requests与Beautiful Soup库详解
  • ISP Pipeline（9）：Noise Filter for Chroma 色度去噪
  • node js入门，包含express,npm管理
  • 用户行为序列建模（篇八）-【阿里】DIEN
  • ROS常用的路径规划算法介绍
  • 在Linux系统中部署Java项目
  • 爪形行列式