数据操作语言(DML)

数据操作语言(DML)概述

数据操作语言（Data Manipulation Language，DML）是SQL语言的核心组成部分，用于操作数据库中的数据。DML命令允许用户对数据库中的记录进行增、删、改操作，是日常数据库操作中最常用的命令类型。

主要的DML命令包括：

INSERT - 向表中插入新记录
UPDATE - 修改表中的现有记录
DELETE - 从表中删除记录
REPLACE - 替换记录（插入或更新）
MERGE - 合并数据（MySQL 8.0+ 通过INSERT...ON DUPLICATE KEY UPDATE实现）

DML的重要性

DML在数据库管理中具有以下重要作用：

数据维护 - 支持数据的日常增删改操作
业务逻辑实现 - 实现应用程序的业务数据操作
数据质量 - 通过约束和事务保证数据一致性
性能优化 - 批量操作和索引优化提高操作效率
数据安全 - 通过权限控制保护数据操作安全

INSERT语句

INSERT语句用于向表中插入新记录，是最基本的DML操作之一。

基本INSERT语法

                        SQL语法
                    

                        -- 插入指定列
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

-- 插入所有列
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

-- 插入多行数据
INSERT INTO table_name (column1, column2, ...)
VALUES 
    (value1, value2, ...),
    (value1, value2, ...),
    ...;
                    

INSERT示例

                        SQL示例
                    

                        -- 插入指定列（推荐方式）
INSERT INTO users (username, email, password, first_name, last_name)
VALUES ('john_doe', 'john@example.com', 'hashed_password', 'John', 'Doe');

-- 插入所有列（需要知道所有列的顺序）
INSERT INTO users
VALUES (NULL, 'jane_smith', 'jane@example.com', 'hashed_pass', 'Jane', 'Smith', '1990-05-15', NOW(), NOW(), 1);

-- 插入默认值
INSERT INTO products (product_name, price)
VALUES ('New Product', DEFAULT);

-- 插入NULL值
INSERT INTO users (username, email, password, birth_date)
VALUES ('test_user', 'test@example.com', 'pass', NULL);

-- 插入表达式结果
INSERT INTO sales (sale_date, amount, tax_rate, total_amount)
VALUES (CURDATE(), 100.00, 0.1, 100.00 * 1.1);
                    

插入多行数据

                        SQL示例
                    

                        INSERT INTO products (product_name, price, category_id)
VALUES 
    ('Laptop', 999.99, 1),
    ('Mouse', 29.99, 1),
    ('Keyboard', 79.99, 1),
    ('Monitor', 199.99, 1);

-- 批量插入用户数据
INSERT INTO users (username, email, password, first_name, last_name)
VALUES 
    ('alice_wonder', 'alice@example.com', 'pass1', 'Alice', 'Wonder'),
    ('bob_builder', 'bob@example.com', 'pass2', 'Bob', 'Builder'),
    ('charlie_brown', 'charlie@example.com', 'pass3', 'Charlie', 'Brown');
                    

INSERT...SELECT语句

可以从另一个表中选择数据并插入到当前表中，常用于数据迁移、备份和报表生成。

                        SQL语法
                    

                        INSERT INTO table1 (column1, column2, ...)
SELECT column1, column2, ...
FROM table2
WHERE condition;
                    

                        SQL示例
                    

                        -- 将活跃用户复制到新表
INSERT INTO active_users (user_id, username, email)
SELECT user_id, username, email
FROM users
WHERE is_active = 1;

-- 从订单表生成月度销售汇总
INSERT INTO monthly_sales (sale_month, total_sales, order_count)
SELECT 
    DATE_FORMAT(order_date, '%Y-%m'),
    SUM(total_amount),
    COUNT(*)
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

-- 插入计算字段
INSERT INTO product_stats (product_id, avg_rating, review_count)
SELECT 
    product_id,
    AVG(rating),
    COUNT(*)
FROM reviews
GROUP BY product_id;
                    

INSERT忽略重复

使用INSERT IGNORE可以忽略插入时的重复键错误，继续插入其他记录。

                        SQL示例
                    

                        INSERT IGNORE INTO users (username, email, password)
VALUES ('john_doe', 'john@example.com', 'new_password');

-- 批量插入忽略重复
INSERT IGNORE INTO categories (category_name, description)
VALUES 
    ('Electronics', 'Electronic devices'),
    ('Books', 'All kinds of books'),
    ('Clothing', 'Fashion and apparel');
                    

INSERT...ON DUPLICATE KEY UPDATE

当遇到重复键时更新记录而不是插入新记录，常用于"upsert"操作。

                        SQL示例
                    

                        INSERT INTO users (username, email, login_count)
VALUES ('john_doe', 'john@example.com', 1)
ON DUPLICATE KEY UPDATE 
    login_count = login_count + 1,
    last_login = NOW();

-- 更新产品库存
INSERT INTO products (product_id, product_name, stock_quantity)
VALUES (123, 'Laptop', 10)
ON DUPLICATE KEY UPDATE 
    stock_quantity = stock_quantity + VALUES(stock_quantity),
    updated_at = NOW();

-- 使用VALUES()函数引用插入的值
INSERT INTO settings (setting_key, setting_value)
VALUES ('maintenance_mode', 'enabled')
ON DUPLICATE KEY UPDATE 
    setting_value = VALUES(setting_value);
                    

获取插入的ID

在插入自增主键记录后，可以使用LAST_INSERT_ID()函数获取插入的ID。

                        SQL示例
                    
                        INSERT INTO users (username, email, password)
VALUES ('new_user', 'new@example.com', 'password');

SELECT LAST_INSERT_ID();  -- 返回新插入用户的ID

-- 在应用程序中使用
-- $user_id = mysqli_insert_id($connection); // PHP
-- let user_id = result.insertId; // Node.js

批量导入数据：LOAD DATA INFILE

LOAD DATA INFILE 是MySQL中导入文本文件最快的方法，适用于批量数据导入。

                        SQL示例
                    

                        -- 从CSV文件导入数据
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(username, email, password);

-- 从客户端本地文件导入（需要启用 LOCAL 选项）
LOAD DATA LOCAL INFILE 'data.csv'
INTO TABLE users ...
                    

安全提示：使用 LOAD DATA LOCAL INFILE 可能存在安全风险，默认情况下可能被禁用。请谨慎配置。

UPDATE语句

UPDATE语句用于修改表中的现有记录，可以根据条件更新单个或多个记录。

基本UPDATE语法

                        SQL语法
                    
                        UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

重要安全提示：UPDATE语句如果没有WHERE条件，将会更新表中的所有记录！请务必小心使用。建议在执行UPDATE前先用SELECT验证WHERE条件。

UPDATE示例

                        SQL示例
                    

                        -- 更新单个用户的邮箱
UPDATE users
SET email = 'new_email@example.com'
WHERE username = 'john_doe';

-- 更新多个列
UPDATE users
SET 
    first_name = 'Jonathan',
    last_name = 'Doerson',
    updated_at = NOW()
WHERE user_id = 1;

-- 基于表达式更新
UPDATE products
SET 
    price = price * 0.9,  -- 打9折
    stock_quantity = stock_quantity - 1
WHERE product_id = 5;

-- 批量更新
UPDATE users
SET is_active = 0
WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 使用CASE条件更新
UPDATE products
SET price = 
    CASE 
        WHEN category_id = 1 THEN price * 1.1  -- 电子产品涨价10%
        WHEN category_id = 2 THEN price * 0.9  -- 书籍降价10%
        ELSE price  -- 其他类别价格不变
    END;
                    

UPDATE...JOIN语句

可以使用JOIN在UPDATE语句中基于其他表的值来更新数据。

                        SQL示例
                    

                        -- 根据类别表更新产品价格
UPDATE products p
JOIN categories c ON p.category_id = c.category_id
SET p.price = p.price * 1.1  -- 涨价10%
WHERE c.category_name = 'Electronics';

-- 更新用户最后订单日期
UPDATE users u
JOIN (
    SELECT user_id, MAX(order_date) AS last_order_date
    FROM orders
    GROUP BY user_id
) o ON u.user_id = o.user_id
SET u.last_order_date = o.last_order_date;

-- 多表连接更新
UPDATE order_items oi
JOIN orders o ON oi.order_id = o.order_id
JOIN products p ON oi.product_id = p.product_id
SET oi.unit_price = p.price
WHERE o.status = 'pending';
                    

UPDATE限制更新行数

                        SQL示例
                    

                        -- 只更新前10条记录
UPDATE products
SET featured = 1
WHERE category_id = 1
ORDER BY price DESC
LIMIT 10;

-- 分批更新大量数据
UPDATE large_table
SET processed = 1
WHERE processed = 0
LIMIT 1000;
                    

获取受影响的行数

执行UPDATE后，可以获取受影响的行数来确认操作结果。

                        SQL示例
                    

                        UPDATE users 
SET last_login = NOW() 
WHERE user_id = 1;

-- 在应用程序中获取受影响行数
-- $affected_rows = mysqli_affected_rows($connection); // PHP
-- let affectedRows = result.affectedRows; // Node.js

SELECT ROW_COUNT();  -- 返回最近操作影响的行数
                    

DELETE语句

DELETE语句用于从表中删除记录，可以根据条件删除单个或多个记录。

基本DELETE语法

                        SQL语法
                    
                        DELETE FROM table_name
WHERE condition;

重要安全提示：DELETE语句如果没有WHERE条件，将会删除表中的所有记录！请务必小心使用。建议在执行DELETE前先用SELECT验证WHERE条件，并考虑使用事务或备份。

DELETE示例

                        SQL示例
                    

                        -- 删除特定用户
DELETE FROM users
WHERE username = 'inactive_user';

-- 删除多条记录
DELETE FROM products
WHERE stock_quantity = 0;

-- 删除所有非活跃用户
DELETE FROM users
WHERE is_active = 0;

-- 删除过期数据
DELETE FROM session_data
WHERE expiry_date < NOW();

-- 使用子查询删除
DELETE FROM products
WHERE category_id IN (
    SELECT category_id FROM categories 
    WHERE is_active = 0
);
                    

DELETE...JOIN语句

可以使用JOIN在DELETE语句中基于其他表的值来删除数据。

                        SQL示例
                    

                        -- 删除没有订单的用户
DELETE u FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

-- 删除特定类别的产品
DELETE p FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE c.category_name = 'Discontinued';

-- 多表删除
DELETE o, oi
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date < '2020-01-01';
                    

DELETE限制删除行数

                        SQL示例
                    

                        -- 只删除前1000条记录
DELETE FROM audit_logs
WHERE log_date < DATE_SUB(NOW(), INTERVAL 1 YEAR)
ORDER BY log_date ASC
LIMIT 1000;

-- 分批删除大量数据
DELETE FROM large_table
WHERE condition
LIMIT 1000;  -- 重复执行直到没有更多数据
                    

REPLACE语句

REPLACE语句类似于INSERT，但如果记录已存在（基于主键或唯一索引）则先删除再插入。

                        SQL语法
                    
                        REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

                        SQL示例
                    

                        -- 替换用户信息
REPLACE INTO users (user_id, username, email, password)
VALUES (1, 'john_doe', 'new_email@example.com', 'new_password');

-- 如果user_id=1存在则替换，否则插入
REPLACE INTO settings (setting_key, setting_value)
VALUES ('site_name', 'My New Site');
                    

REPLACE vs INSERT...ON DUPLICATE KEY UPDATE：

REPLACE会删除旧记录并插入新记录，自增ID会改变
INSERT...ON DUPLICATE KEY UPDATE会更新现有记录，自增ID不变
REPLACE会触发DELETE和INSERT触发器
INSERT...ON DUPLICATE KEY UPDATE只触发UPDATE触发器
通常推荐使用INSERT...ON DUPLICATE KEY UPDATE

关于 INSERT DELAYED (已弃用)

MySQL 5.6 之前支持的 INSERT DELAYED 语法已在 MySQL 8.0 中移除，不再生效。如需异步插入，应在应用层实现队列机制。

事务中的DML操作

在事务中执行DML操作可以保证数据的一致性，要么全部成功，要么全部失败。

                        SQL示例
                    

                        -- 开始事务
START TRANSACTION;

-- 执行多个DML操作
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 99.99, 'pending');

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 5, 1, 99.99);

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE product_id = 5;

-- 提交事务
COMMIT;

-- 如果发生错误，可以回滚
-- ROLLBACK;
                    

DML 与锁定

不同的DML操作会产生不同类型的锁，影响并发性能。

INSERT：对插入的行加排他锁（X锁），并对自增计数器加特殊的AUTO-INC锁。
UPDATE/DELETE：对扫描到的所有行加排他锁（X锁），直到事务结束。
SELECT ... FOR UPDATE：对读取的行加排他锁（X锁）。
SELECT ... LOCK IN SHARE MODE：对读取的行加共享锁（S锁）。

理解锁机制有助于避免死锁和优化并发性能。发生死锁时，MySQL会自动回滚其中一个事务。

性能优化技巧

批量操作

                        SQL示例
                    

                        -- 批量插入（比单条插入快很多）
INSERT INTO products (product_name, price)
VALUES 
    ('Product 1', 10.00),
    ('Product 2', 20.00),
    ('Product 3', 30.00),
    ...  -- 可以插入数百甚至数千条
    ('Product N', 100.00);

-- 分批更新大量数据
UPDATE large_table
SET processed = 1
WHERE id BETWEEN 1 AND 1000;

UPDATE large_table
SET processed = 1
WHERE id BETWEEN 1001 AND 2000;
-- 继续直到完成
                    

使用事务批量提交

对于大量的连续DML操作，使用显式事务并定期提交可以显著提高性能（减少磁盘刷新次数）。

                        START TRANSACTION;
INSERT INTO log_table VALUES (...);  -- 重复1000次
COMMIT;  -- 每1000条提交一次
                    

使用索引优化

                        SQL示例
                    

                        -- 为WHERE条件中的列创建索引
CREATE INDEX idx_user_email ON users (email);
CREATE INDEX idx_product_category ON products (category_id);

-- 现在这些查询会更快
UPDATE users SET last_login = NOW() WHERE email = 'user@example.com';
DELETE FROM products WHERE category_id = 5;
                    

处理死锁

当两个或多个事务互相等待对方释放锁时，就会发生死锁。MySQL会自动检测并回滚其中一个事务。应用层应实现重试机制。

                        伪代码示例
                    

                        -- 应用层重试逻辑（伪代码）
DECLARE retry_count DEFAULT 3;
WHILE retry_count > 0:
    BEGIN
        START TRANSACTION;
        -- 执行DML操作
        COMMIT;
        BREAK;
    EXCEPTION WHEN deadlock:
        ROLLBACK;
        retry_count = retry_count - 1;
        WAIT RANDOM() * 0.1;
    END
                    

错误处理和最佳实践

常见错误处理

                        SQL示例
                    

                        -- 检查重复键错误
INSERT IGNORE INTO users (username, email)
VALUES ('existing_user', 'user@example.com');

-- 使用事务保证数据一致性
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
                    

最佳实践

总是使用WHERE条件 - 避免意外更新或删除所有记录
先SELECT后UPDATE/DELETE - 验证WHERE条件是否正确
使用事务 - 保证相关操作的原子性
批量操作 - 减少数据库往返次数
备份重要数据 - 在执行大规模更新或删除前备份数据
测试环境验证 - 在生产环境执行前先在测试环境验证
监控性能 - 监控DML操作的性能影响

实际应用场景

用户注册流程

                        SQL示例
                    

                        -- 用户注册
START TRANSACTION;

INSERT INTO users (username, email, password, first_name, last_name)
VALUES ('newuser123', 'newuser@example.com', 'hashed_password', 'John', 'Doe');

SET @new_user_id = LAST_INSERT_ID();

-- 创建用户配置
INSERT INTO user_settings (user_id, theme, language, notifications_enabled)
VALUES (@new_user_id, 'light', 'en', 1);

-- 记录注册事件
INSERT INTO user_events (user_id, event_type, event_data)
VALUES (@new_user_id, 'registration', '{"source": "web"}');

COMMIT;
                    

订单处理流程

                        SQL示例
                    

                        -- 创建订单
START TRANSACTION;

INSERT INTO orders (user_id, total_amount, status, shipping_address)
VALUES (123, 199.98, 'pending', '123 Main St');

SET @new_order_id = LAST_INSERT_ID();

-- 添加订单项
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES 
    (@new_order_id, 5, 1, 99.99),
    (@new_order_id, 8, 2, 49.99);

-- 更新库存
UPDATE products 
SET stock_quantity = stock_quantity - 1 
WHERE product_id = 5;

UPDATE products 
SET stock_quantity = stock_quantity - 2 
WHERE product_id = 8;

COMMIT;
                    

下一步学习

掌握了数据操作语言后，您可以继续学习：

数据查询语言(DQL) - 学习SELECT查询和数据检索
数据控制语言(DCL) - 学习权限管理和访问控制
约束 - 学习如何保证数据完整性