DML数据操作语言

# DML数据操作语言

# 概述

DML（Data Manipulation Language，数据操作语言）是SQL的一个子集，用于对数据库中的数据进行增加、删除、修改和查询操作。DML是日常数据库操作中使用最频繁的语句类型。

# 主要DML语句

# 1. INSERT - 插入数据

# 基本插入语法

-- 插入单行数据
INSERT INTO users (username, email, password) 
VALUES ('john_doe', 'john@example.com', 'hashed_password');

-- 插入多行数据
INSERT INTO users (username, email, password) 
VALUES 
    ('alice', 'alice@example.com', 'password1'),
    ('bob', 'bob@example.com', 'password2'),
    ('charlie', 'charlie@example.com', 'password3');

-- 插入所有列（按表定义顺序）
INSERT INTO users 
VALUES (1, 'admin', 'admin@example.com', 'admin_password', NOW(), NOW());

# 高级插入技巧

-- 从其他表插入数据
INSERT INTO users_backup (username, email, created_at)
SELECT username, email, created_at 
FROM users 
WHERE status = 'active';

-- 插入时处理重复键
-- MySQL语法
INSERT IGNORE INTO users (username, email) 
VALUES ('existing_user', 'new@example.com');

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

-- PostgreSQL语法
INSERT INTO users (username, email) 
VALUES ('john', 'john@example.com')
ON CONFLICT (username) 
DO UPDATE SET 
    email = EXCLUDED.email,
    updated_at = NOW();

-- 插入并返回生成的ID
INSERT INTO users (username, email) 
VALUES ('new_user', 'new@example.com');
SELECT LAST_INSERT_ID(); -- MySQL

-- PostgreSQL
INSERT INTO users (username, email) 
VALUES ('new_user', 'new@example.com')
RETURNING id;

# 批量插入优化

-- 大批量插入时的优化策略

-- 1. 使用多值插入
INSERT INTO products (name, price, category_id) VALUES
    ('Product 1', 19.99, 1),
    ('Product 2', 29.99, 1),
    ('Product 3', 39.99, 2),
    -- ... 更多数据
    ('Product 1000', 99.99, 5);

-- 2. 临时禁用索引检查（MySQL）
SET foreign_key_checks = 0;
SET unique_checks = 0;
-- 执行大量插入操作
SET foreign_key_checks = 1;
SET unique_checks = 1;

-- 3. 使用事务
START TRANSACTION;
INSERT INTO large_table VALUES (...);
-- 大量插入操作
COMMIT;

# 2. UPDATE - 更新数据

# 基本更新语法

-- 更新单个字段
UPDATE users 
SET email = 'newemail@example.com' 
WHERE id = 1;

-- 更新多个字段
UPDATE users 
SET 
    email = 'updated@example.com',
    last_login = NOW(),
    login_count = login_count + 1
WHERE username = 'john_doe';

-- 条件更新
UPDATE products 
SET 
    price = price * 0.9,  -- 打9折
    updated_at = NOW()
WHERE category_id = 1 AND stock > 0;

# 高级更新技巧

-- 使用子查询更新
UPDATE users 
SET status = 'vip'
WHERE id IN (
    SELECT user_id 
    FROM orders 
    GROUP BY user_id 
    HAVING SUM(total_amount) > 10000
);

-- 多表关联更新（MySQL）
UPDATE users u
JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders 
    GROUP BY user_id
) o ON u.id = o.user_id
SET u.total_orders = o.order_count;

-- PostgreSQL多表更新语法
UPDATE users 
SET total_orders = order_stats.order_count
FROM (
    SELECT user_id, COUNT(*) as order_count
    FROM orders 
    GROUP BY user_id
) AS order_stats
WHERE users.id = order_stats.user_id;

-- 条件更新（CASE语句）
UPDATE users 
SET status = CASE 
    WHEN last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR) THEN 'inactive'
    WHEN last_login < DATE_SUB(NOW(), INTERVAL 6 MONTH) THEN 'dormant'
    ELSE 'active'
END;

-- 安全更新（限制更新行数）
UPDATE products 
SET price = price * 1.1 
WHERE category_id = 1 
LIMIT 100;  -- 最多更新100行

# 更新性能优化

-- 1. 使用索引优化WHERE条件
-- 确保WHERE条件中的列有索引
CREATE INDEX idx_users_status ON users(status);
UPDATE users SET last_updated = NOW() WHERE status = 'active';

-- 2. 批量更新
-- 避免在循环中执行UPDATE
-- 好的做法：一次性更新
UPDATE products 
SET price = CASE id
    WHEN 1 THEN 19.99
    WHEN 2 THEN 29.99
    WHEN 3 THEN 39.99
END
WHERE id IN (1, 2, 3);

-- 3. 分批更新大量数据
UPDATE products SET updated_at = NOW() 
WHERE id BETWEEN 1 AND 1000;

UPDATE products SET updated_at = NOW() 
WHERE id BETWEEN 1001 AND 2000;
-- 继续分批处理...

# 3. DELETE - 删除数据

# 基本删除语法

-- 删除特定记录
DELETE FROM users WHERE id = 1;

-- 条件删除
DELETE FROM users 
WHERE status = 'inactive' 
  AND last_login < DATE_SUB(NOW(), INTERVAL 2 YEAR);

-- 删除所有记录（保留表结构）
DELETE FROM temp_table;

-- 限制删除行数
DELETE FROM logs 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
LIMIT 1000;

# 高级删除技巧

-- 使用子查询删除
DELETE FROM users 
WHERE id NOT IN (
    SELECT DISTINCT user_id 
    FROM orders 
    WHERE order_date > DATE_SUB(NOW(), INTERVAL 1 YEAR)
);

-- 多表关联删除（MySQL）
DELETE u 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL  -- 删除没有订单的用户
  AND u.created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- PostgreSQL多表删除语法
DELETE FROM users 
WHERE id IN (
    SELECT u.id 
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE o.user_id IS NULL
      AND u.created_at < NOW() - INTERVAL '1 year'
);

-- 软删除（推荐做法）
-- 不实际删除记录，而是标记为已删除
UPDATE users 
SET 
    deleted_at = NOW(),
    status = 'deleted'
WHERE id = 1;

-- 查询时排除已删除记录
SELECT * FROM users WHERE deleted_at IS NULL;

# 删除性能优化

-- 1. 分批删除大量数据
DELETE FROM large_table 
WHERE created_at < '2023-01-01' 
LIMIT 1000;

-- 重复执行直到没有更多记录被删除

-- 2. 使用TRUNCATE清空表（更快）
TRUNCATE TABLE temp_data;  -- 比DELETE FROM temp_data快得多

-- 3. 删除前备份重要数据
CREATE TABLE users_backup AS 
SELECT * FROM users WHERE status = 'important';

-- 然后执行删除
DELETE FROM users WHERE status != 'important';

# 4. SELECT - 查询数据

# 基本查询语法

-- 查询所有列
SELECT * FROM users;

-- 查询特定列
SELECT id, username, email FROM users;

-- 条件查询
SELECT * FROM users WHERE status = 'active';

-- 排序查询
SELECT * FROM users ORDER BY created_at DESC;

-- 限制结果数量
SELECT * FROM users LIMIT 10;

-- 分页查询
SELECT * FROM users 
ORDER BY id 
LIMIT 10 OFFSET 20;  -- 跳过前20条，取10条

# 复杂查询示例

-- 聚合查询
SELECT 
    status,
    COUNT(*) as user_count,
    AVG(DATEDIFF(NOW(), created_at)) as avg_days_since_registration
FROM users 
GROUP BY status
HAVING user_count > 100;

-- 关联查询
SELECT 
    u.username,
    u.email,
    COUNT(o.id) as order_count,
    SUM(o.total_amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.username, u.email
ORDER BY total_spent DESC;

-- 子查询
SELECT *
FROM products
WHERE price > (
    SELECT AVG(price) 
    FROM products 
    WHERE category_id = 1
);

-- 窗口函数
SELECT 
    username,
    total_amount,
    ROW_NUMBER() OVER (ORDER BY total_amount DESC) as rank,
    SUM(total_amount) OVER (ORDER BY total_amount DESC) as running_total
FROM (
    SELECT 
        u.username,
        SUM(o.total_amount) as total_amount
    FROM users u
    JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.username
) user_totals;

# 实战案例

# 案例1：电商订单管理系统

-- 创建测试数据
INSERT INTO users (username, email, status) VALUES
    ('alice', 'alice@example.com', 'active'),
    ('bob', 'bob@example.com', 'active'),
    ('charlie', 'charlie@example.com', 'inactive');

INSERT INTO products (name, price, stock, category_id) VALUES
    ('iPhone 15', 999.99, 100, 1),
    ('MacBook Pro', 1999.99, 50, 1),
    ('AirPods', 199.99, 200, 1);

-- 创建订单
INSERT INTO orders (user_id, order_number, status, total_amount) VALUES
    (1, 'ORD001', 'pending', 999.99),
    (1, 'ORD002', 'paid', 199.99),
    (2, 'ORD003', 'paid', 1999.99);

-- 添加订单项
INSERT INTO order_items (order_id, product_id, quantity, unit_price, total_price) VALUES
    (1, 1, 1, 999.99, 999.99),
    (2, 3, 1, 199.99, 199.99),
    (3, 2, 1, 1999.99, 1999.99);

-- 更新库存（订单支付后）
UPDATE products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
SET p.stock = p.stock - oi.quantity
WHERE o.status = 'paid' AND o.id = 2;

-- 计算用户总消费并更新用户等级
UPDATE users u
JOIN (
    SELECT 
        user_id,
        SUM(total_amount) as total_spent
    FROM orders 
    WHERE status = 'paid'
    GROUP BY user_id
) order_summary ON u.id = order_summary.user_id
SET u.user_level = CASE 
    WHEN order_summary.total_spent >= 5000 THEN 'platinum'
    WHEN order_summary.total_spent >= 1000 THEN 'gold'
    WHEN order_summary.total_spent >= 100 THEN 'silver'
    ELSE 'bronze'
END;

-- 查询热销产品
SELECT 
    p.name,
    p.price,
    SUM(oi.quantity) as total_sold,
    SUM(oi.total_price) as total_revenue
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'paid'
  AND o.order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY p.id, p.name, p.price
ORDER BY total_sold DESC
LIMIT 10;

-- 删除取消的订单
DELETE FROM order_items 
WHERE order_id IN (
    SELECT id FROM orders 
    WHERE status = 'cancelled' 
      AND created_at < DATE_SUB(NOW(), INTERVAL 7 DAY)
);

DELETE FROM orders 
WHERE status = 'cancelled' 
  AND created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);

# 案例2：用户行为分析系统

-- 创建用户行为日志表
CREATE TABLE user_activities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    activity_type ENUM('login', 'logout', 'view_product', 'add_to_cart', 'purchase'),
    activity_data JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_activity_type (activity_type),
    INDEX idx_created_at (created_at)
);

-- 记录用户活动
INSERT INTO user_activities (user_id, activity_type, activity_data, ip_address) VALUES
    (1, 'login', '{"device": "mobile", "location": "New York"}', '192.168.1.1'),
    (1, 'view_product', '{"product_id": 1, "duration": 30}', '192.168.1.1'),
    (1, 'add_to_cart', '{"product_id": 1, "quantity": 1}', '192.168.1.1'),
    (2, 'login', '{"device": "desktop", "location": "California"}', '192.168.1.2');

-- 分析用户活跃度
SELECT 
    DATE(created_at) as activity_date,
    COUNT(DISTINCT user_id) as active_users,
    COUNT(*) as total_activities,
    COUNT(CASE WHEN activity_type = 'login' THEN 1 END) as logins,
    COUNT(CASE WHEN activity_type = 'purchase' THEN 1 END) as purchases
FROM user_activities
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(created_at)
ORDER BY activity_date DESC;

-- 更新用户最后活动时间
UPDATE users u
JOIN (
    SELECT 
        user_id,
        MAX(created_at) as last_activity
    FROM user_activities
    GROUP BY user_id
) ua ON u.id = ua.user_id
SET u.last_activity_at = ua.last_activity;

-- 清理旧的活动日志（保留最近3个月）
DELETE FROM user_activities 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH)
LIMIT 10000;

# 案例3：库存管理系统

-- 创建库存变动记录表
CREATE TABLE inventory_movements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    movement_type ENUM('in', 'out', 'adjustment'),
    quantity INT NOT NULL,
    reason VARCHAR(100),
    reference_id INT, -- 关联订单ID或采购ID
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_product_id (product_id),
    INDEX idx_created_at (created_at)
);

-- 入库操作
INSERT INTO inventory_movements (product_id, movement_type, quantity, reason, created_by) VALUES
    (1, 'in', 100, 'Purchase order #PO001', 1),
    (2, 'in', 50, 'Purchase order #PO001', 1),
    (3, 'in', 200, 'Purchase order #PO002', 1);

-- 更新产品库存
UPDATE products p
JOIN (
    SELECT 
        product_id,
        SUM(CASE WHEN movement_type = 'in' THEN quantity ELSE -quantity END) as net_movement
    FROM inventory_movements
    WHERE created_at >= '2024-01-01'
    GROUP BY product_id
) im ON p.id = im.product_id
SET p.stock = p.stock + im.net_movement;

-- 出库操作（销售）
START TRANSACTION;

-- 检查库存是否充足
SELECT stock FROM products WHERE id = 1 FOR UPDATE;

-- 如果库存充足，执行出库
INSERT INTO inventory_movements (product_id, movement_type, quantity, reason, reference_id, created_by)
VALUES (1, 'out', 2, 'Sale order #ORD001', 1, 1);

UPDATE products 
SET stock = stock - 2 
WHERE id = 1 AND stock >= 2;

COMMIT;

-- 库存预警查询
SELECT 
    p.id,
    p.name,
    p.stock,
    p.min_stock,
    (p.min_stock - p.stock) as shortage
FROM products p
WHERE p.stock <= p.min_stock
ORDER BY shortage DESC;

-- 库存周转率分析
SELECT 
    p.name,
    p.stock as current_stock,
    COALESCE(sales.total_sold, 0) as sold_last_30_days,
    CASE 
        WHEN p.stock > 0 THEN COALESCE(sales.total_sold, 0) / p.stock
        ELSE 0
    END as turnover_ratio
FROM products p
LEFT JOIN (
    SELECT 
        product_id,
        SUM(quantity) as total_sold
    FROM inventory_movements
    WHERE movement_type = 'out'
      AND created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY product_id
) sales ON p.id = sales.product_id
ORDER BY turnover_ratio DESC;

# 事务处理

# 基本事务操作

-- 开始事务
START TRANSACTION;
-- 或者使用
BEGIN;

-- 执行多个DML操作
INSERT INTO orders (user_id, total_amount) VALUES (1, 100.00);
SET @order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity, unit_price) 
VALUES (@order_id, 1, 2, 50.00);

UPDATE products SET stock = stock - 2 WHERE id = 1;

-- 提交事务
COMMIT;

-- 或者回滚事务
-- ROLLBACK;

# 事务隔离级别

-- 设置事务隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 不同隔离级别的示例

-- READ UNCOMMITTED（读未提交）
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM products WHERE id = 1;
-- 可以读取其他事务未提交的数据

-- READ COMMITTED（读已提交）
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM products WHERE id = 1;
-- 只能读取已提交的数据

-- REPEATABLE READ（可重复读）
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM products WHERE id = 1;
-- 同一事务中多次读取结果一致

-- SERIALIZABLE（串行化）
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM products WHERE id = 1;
-- 最高隔离级别，完全串行化执行

# 锁机制

-- 共享锁（读锁）
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁（写锁）
SELECT * FROM products WHERE id = 1 FOR UPDATE;

-- 实际应用示例：防止超卖
START TRANSACTION;

-- 锁定产品记录
SELECT stock FROM products WHERE id = 1 FOR UPDATE;

-- 检查库存
IF (stock >= requested_quantity) THEN
    -- 减少库存
    UPDATE products SET stock = stock - requested_quantity WHERE id = 1;
    
    -- 创建订单
    INSERT INTO orders (user_id, product_id, quantity) 
    VALUES (user_id, 1, requested_quantity);
    
    COMMIT;
ELSE
    ROLLBACK;
END IF;

# 性能优化技巧

# 1. 批量操作优化

-- 批量插入优化
-- 不好的做法：逐条插入
INSERT INTO logs (user_id, action) VALUES (1, 'login');
INSERT INTO logs (user_id, action) VALUES (2, 'logout');
-- ... 重复很多次

-- 好的做法：批量插入
INSERT INTO logs (user_id, action) VALUES
    (1, 'login'),
    (2, 'logout'),
    (3, 'view_page'),
    -- ... 一次插入多条
    (1000, 'purchase');

-- 批量更新优化
-- 使用CASE语句进行批量更新
UPDATE products 
SET price = CASE id
    WHEN 1 THEN 19.99
    WHEN 2 THEN 29.99
    WHEN 3 THEN 39.99
    ELSE price
END
WHERE id IN (1, 2, 3);

# 2. 索引优化

-- 确保WHERE条件中的列有索引
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- 复合索引的使用
-- 这个查询可以有效使用上面的复合索引
SELECT * FROM orders 
WHERE user_id = 123 
  AND order_date >= '2024-01-01';

-- 避免在WHERE条件中使用函数
-- 不好的做法
SELECT * FROM orders WHERE YEAR(order_date) = 2024;

-- 好的做法
SELECT * FROM orders 
WHERE order_date >= '2024-01-01' 
  AND order_date < '2025-01-01';

# 3. 查询优化

-- 使用LIMIT限制结果集
SELECT * FROM large_table ORDER BY created_at DESC LIMIT 100;

-- 避免SELECT *，只查询需要的列
-- 不好的做法
SELECT * FROM users WHERE status = 'active';

-- 好的做法
SELECT id, username, email FROM users WHERE status = 'active';

-- 使用EXISTS代替IN（在某些情况下更高效）
-- 使用EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id 
      AND o.status = 'paid'
);

-- 使用适当的JOIN类型
-- INNER JOIN（只返回匹配的记录）
SELECT u.username, o.order_number
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN（返回左表所有记录）
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username;

# 常见错误和解决方案

# 1. 数据完整性错误

-- 外键约束错误
-- 错误：插入不存在的外键值
INSERT INTO orders (user_id, total_amount) VALUES (999, 100.00);
-- Error: Cannot add or update a child row: a foreign key constraint fails

-- 解决方案：先检查外键是否存在
INSERT INTO orders (user_id, total_amount)
SELECT 999, 100.00
WHERE EXISTS (SELECT 1 FROM users WHERE id = 999);

-- 唯一约束错误
-- 错误：插入重复的唯一值
INSERT INTO users (username, email) VALUES ('john', 'existing@example.com');
-- Error: Duplicate entry 'existing@example.com' for key 'email'

-- 解决方案：使用INSERT IGNORE或ON DUPLICATE KEY UPDATE
INSERT IGNORE INTO users (username, email) VALUES ('john', 'existing@example.com');

-- 或者
INSERT INTO users (username, email) VALUES ('john', 'existing@example.com')
ON DUPLICATE KEY UPDATE username = VALUES(username);

# 2. 性能问题

-- 问题：没有使用索引的查询
SELECT * FROM large_table WHERE some_column = 'value';

-- 解决方案：添加索引
CREATE INDEX idx_some_column ON large_table(some_column);

-- 问题：N+1查询问题
-- 不好的做法：在循环中执行查询
-- 伪代码：
-- for each user:
--     SELECT * FROM orders WHERE user_id = user.id;

-- 好的做法：使用JOIN一次性获取所有数据
SELECT u.*, o.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

# 3. 事务问题

-- 问题：长时间运行的事务
-- 不好的做法
START TRANSACTION;
-- 大量的数据操作
UPDATE large_table SET column1 = 'value' WHERE condition;
-- ... 更多操作
COMMIT;

-- 好的做法：分批处理
START TRANSACTION;
UPDATE large_table SET column1 = 'value' 
WHERE condition LIMIT 1000;
COMMIT;

-- 重复执行直到所有数据处理完成

# 最佳实践

# 1. 安全性

-- 使用参数化查询防止SQL注入
-- 不好的做法（容易SQL注入）
-- SELECT * FROM users WHERE username = '" + userInput + "';

-- 好的做法（使用预处理语句）
PREPARE stmt FROM 'SELECT * FROM users WHERE username = ?';
SET @username = 'john';
EXECUTE stmt USING @username;
DEALLOCATE PREPARE stmt;

-- 最小权限原则
-- 为应用程序创建专用数据库用户
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_user'@'localhost';

# 2. 数据一致性

-- 使用事务确保数据一致性
START TRANSACTION;

-- 转账操作示例
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- 检查操作是否成功
IF (ROW_COUNT() = 2) THEN
    COMMIT;
ELSE
    ROLLBACK;
END IF;

# 3. 可维护性

-- 使用有意义的别名
SELECT 
    u.username as user_name,
    u.email as user_email,
    COUNT(o.id) as total_orders,
    SUM(o.total_amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;

-- 添加注释说明复杂逻辑
-- 计算用户的生命周期价值（LTV）
SELECT 
    user_id,
    -- 总订单金额
    SUM(total_amount) as total_revenue,
    -- 平均订单价值
    AVG(total_amount) as avg_order_value,
    -- 订单频率（每月）
    COUNT(*) / (DATEDIFF(MAX(order_date), MIN(order_date)) / 30) as monthly_frequency,
    -- 预估LTV（简化计算）
    SUM(total_amount) * 1.5 as estimated_ltv
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

# 总结

DML操作是数据库日常使用中最频繁的操作，掌握好DML语句的使用技巧对于数据库性能和数据完整性至关重要：

INSERT操作：合理使用批量插入、处理重复键冲突
UPDATE操作：注意WHERE条件、使用索引、避免全表更新
DELETE操作：考虑软删除、分批删除大量数据
SELECT操作：优化查询条件、合理使用索引、避免不必要的数据传输
事务处理：确保数据一致性、选择合适的隔离级别
性能优化：批量操作、索引优化、查询优化
安全性：防止SQL注入、使用最小权限原则

在实际应用中，应该根据具体的业务场景选择合适的DML操作策略，并持续监控和优化数据库性能。

DDL数据定义语言 DQL数据查询语言

Jeskson-微服务分布式

DML数据操作语言

# DML数据操作语言

# 概述

# 主要DML语句

# 1. INSERT - 插入数据

# 基本插入语法

# 高级插入技巧

# 批量插入优化

# 2. UPDATE - 更新数据

# 基本更新语法

# 高级更新技巧

# 更新性能优化

# 3. DELETE - 删除数据

# 基本删除语法

# 高级删除技巧

# 删除性能优化

# 4. SELECT - 查询数据

# 基本查询语法

# 复杂查询示例

# 实战案例

# 案例1：电商订单管理系统

# 案例2：用户行为分析系统

# 案例3：库存管理系统

# 事务处理

# 基本事务操作

# 事务隔离级别

# 锁机制

# 性能优化技巧

# 1. 批量操作优化

# 2. 索引优化

# 3. 查询优化

# 常见错误和解决方案

# 1. 数据完整性错误

# 2. 性能问题

# 3. 事务问题

# 最佳实践

# 1. 安全性

# 2. 数据一致性

# 3. 可维护性

# 总结