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

发布于：2025-07-02 ⋅ 阅读:(26) ⋅ 点赞:(0)

数据库 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;
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
      ROLLBACK;
      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） 并发性差
    间隙锁 锁定范围（防止幻读） 可能降低并发

七、各数据库差异

特性 MySQL PostgreSQL SQL Server
分页语法 LIMIT 10 OFFSET 20 LIMIT 10 OFFSET 20 OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
插入返回ID LAST_INSERT_ID() RETURNING id OUTPUT INSERTED.id
UPSERT ON DUPLICATE KEY UPDATE ON CONFLICT DO UPDATE MERGE
批量导入 LOAD DATA INFILE COPY BULK INSERT

总结：DML 操作黄金法则

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

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

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布