数据操作语言(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

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

事务中的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;
                    

性能优化技巧

批量操作

                        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;
-- 继续直到完成
                    

使用索引优化

                        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;
                    

错误处理和最佳实践

常见错误处理

                        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查询和数据检索
事务 - 深入学习事务管理和ACID特性
存储过程 - 学习在存储过程中使用DML
性能优化 - 学习DML操作的性能调优