CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped') DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 基本查询
SELECT * FROM users WHERE age > 18;

-- 聚合查询
SELECT status, COUNT(*) as count, AVG(amount) as avg_amount
FROM orders
GROUP BY status
HAVING count > 5;

-- 子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 分页查询
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;

-- INNER JOIN
SELECT u.username, o.amount
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;

-- 创建索引
CREATE INDEX idx_user_email ON users(email);
CREATE UNIQUE INDEX idx_username ON users(username);

-- 复合索引
CREATE INDEX idx_order_status_date ON orders(status, created_at);

-- 优化前：全表扫描
SELECT * FROM orders
WHERE YEAR(created_at) = 2024
  AND status = 'paid';

-- 优化后：使用索引列和范围查询
SELECT id, user_id, amount, status
FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01'
  AND status = 'paid';

-- 查看执行计划
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 ORDER BY created_at DESC;

-- 优化 JOIN 查询
SELECT u.username, SUM(o.amount) as total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01'
GROUP BY u.id
HAVING total > 10000
ORDER BY total DESC
LIMIT 10;