examples.php

<?php
require_once 'Database.php';

use SimpleDatabase\Database;
use SimpleDatabase\DB;

// ============================================================================
// 数据库配置示例
// ============================================================================

// 方式1：直接配置
$db = new Database();
$db->setConnectionParams('localhost', 'test_db', 'root', '');
$db->connect();

// 方式2：使用全局常量（推荐）
define('DB_HOST', 'localhost');
define('DB_NAME', 'test_db');
define('DB_USER', 'root');
define('DB_PASS', '');

// ============================================================================
// 创建测试数据表
// ============================================================================

echo "<h2>创建测试数据表</h2>";

// 创建users表
$createUsersSQL = "
CREATE TABLE IF NOT EXISTS `users` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(100) NOT NULL,
    `email` varchar(150) NOT NULL,
    `status` enum('active','inactive','banned') DEFAULT 'active',
    `age` int(11) DEFAULT NULL,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `email` (`email`),
    KEY `idx_status` (`status`),
    KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建profiles表
CREATE TABLE IF NOT EXISTS `profiles` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `bio` text,
    `avatar` varchar(255) DEFAULT NULL,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `fk_user_id` (`user_id`),
    CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建orders表
CREATE TABLE IF NOT EXISTS `orders` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `amount` decimal(10,2) NOT NULL,
    `status` enum('pending','completed','cancelled') DEFAULT 'pending',
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    KEY `idx_status` (`status`),
    CONSTRAINT `orders_fk_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
";

try {
    // 使用数据库实例执行建表语句
    $db = new Database();
    $db->setConnectionParams('localhost', 'test_db', 'root', '');
    $db->connect();

    $statements = explode(';', $createUsersSQL);
    foreach ($statements as $statement) {
        $statement = trim($statement);
        if (!empty($statement)) {
            $db->unprepared($statement);
        }
    }

    echo "<p style='color: green;'>✅ 数据表创建成功！</p>";
} catch (Exception $e) {
    echo "<p style='color: red;'>❌ 创建数据表失败：" . $e->getMessage() . "</p>";
}

// ============================================================================
// 基础查询示例
// ============================================================================

echo "<h2>基础查询示例</h2>";

// 清空测试数据
DB::statement('DELETE FROM orders');
DB::statement('DELETE FROM profiles');
DB::statement('DELETE FROM users');

// 插入测试数据
echo "<h3>1. 插入测试数据</h3>";

$users = [
    ['name' => '张三', 'email' => 'zhangsan@example.com', 'status' => 'active', 'age' => 25],
    ['name' => '李四', 'email' => 'lisi@example.com', 'status' => 'active', 'age' => 30],
    ['name' => '王五', 'email' => 'wangwu@example.com', 'status' => 'inactive', 'age' => 28],
    ['name' => '赵六', 'email' => 'zhaoliu@example.com', 'status' => 'banned', 'age' => 35],
    ['name' => '钱七', 'email' => 'qianqi@example.com', 'status' => 'active', 'age' => 22]
];

foreach ($users as $userData) {
    $userId = DB::table('users')->insertGetId($userData);
    echo "<p>插入用户：{$userData['name']}，ID：{$userId}</p>";

    // 为每个用户创建档案
    DB::table('profiles')->insert([
        'user_id' => $userId,
        'bio' => "我是{$userData['name']}，今年{$userData['age']}岁。",
        'avatar' => 'default.jpg'
    ]);

    // 为活跃用户创建订单
    if ($userData['status'] === 'active') {
        for ($i = 1; $i <= rand(1, 3); $i++) {
            DB::table('orders')->insert([
                'user_id' => $userId,
                'amount' => rand(50, 500) + 0.99,
                'status' => ['pending', 'completed'][rand(0, 1)]
            ]);
        }
    }
}

echo "<h3>2. 基础查询操作</h3>";

// 查询所有用户
$allUsers = DB::table('users')->get();
echo "<p><strong>所有用户数量：</strong>" . count($allUsers) . "</p>";

// 查询第一条记录
$firstUser = DB::table('users')->first();
echo "<p><strong>第一个用户：</strong>{$firstUser['name']} ({$firstUser['email']})</p>";

// 根据ID查找
$user = DB::table('users')->find(1);
echo "<p><strong>ID为1的用户：</strong>{$user['name']}</p>";

// 统计活跃用户数量
$activeCount = DB::table('users')->where('status', 'active')->count();
echo "<p><strong>活跃用户数量：</strong>{$activeCount}</p>";

echo "<h3>3. 条件查询</h3>";

// WHERE条件
$activeUsers = DB::table('users')
    ->where('status', 'active')
    ->where('age', '>', 25)
    ->get();

echo "<p><strong>年龄大于25岁的活跃用户：</strong></p>";
echo "<ul>";
foreach ($activeUsers as $user) {
    echo "<li>{$user['name']} - {$user['age']}岁</li>";
}
echo "</ul>";

// 数组条件
$usersByStatus = DB::table('users')->where([
    'status' => 'active'
])->get();

echo "<p><strong>活跃用户列表：</strong></p>";
echo "<ul>";
foreach ($usersByStatus as $user) {
    echo "<li>{$user['name']} - {$user['status']}</li>";
}
echo "</ul>";

// OR条件
$specialUsers = DB::table('users')
    ->where('status', 'inactive')
    ->orWhere('age', '>', 30)
    ->get();

echo "<p><strong>非活跃或年龄大于30的用户：</strong></p>";
echo "<ul>";
foreach ($specialUsers as $user) {
    echo "<li>{$user['name']} - {$user['status']} - {$user['age']}岁</li>";
}
echo "</ul>";

// IN查询
$selectedUsers = DB::table('users')
    ->whereIn('id', [1, 3, 5])
    ->get();

echo "<p><strong>ID为1,3,5的用户：</strong></p>";
echo "<ul>";
foreach ($selectedUsers as $user) {
    echo "<li>{$user['name']} (ID: {$user['id']})</li>";
}
echo "</ul>";

// NULL查询（假设有deleted_at字段）
// $usersWithoutDelete = DB::table('users')
//     ->whereNull('deleted_at')
//     ->get();

// BETWEEN查询
$middleAgeUsers = DB::table('users')
    ->whereBetween('age', [25, 30])
    ->get();

echo "<p><strong>年龄在25-30之间的用户：</strong></p>";
echo "<ul>";
foreach ($middleAgeUsers as $user) {
    echo "<li>{$user['name']} - {$user['age']}岁</li>";
}
echo "</ul>";

echo "<h3>4. 排序和限制</h3>";

// 排序
$sortedUsers = DB::table('users')
    ->orderBy('age', 'desc')
    ->orderBy('name', 'asc')
    ->get();

echo "<p><strong>按年龄降序、姓名升序排列：</strong></p>";
echo "<ul>";
foreach ($sortedUsers as $user) {
    echo "<li>{$user['name']} - {$user['age']}岁</li>";
}
echo "</ul>";

// 限制和偏移（分页）
$page1Users = DB::table('users')
    ->orderBy('id')
    ->limit(2)
    ->offset(0)
    ->get();

echo "<p><strong>第一页用户（前2条）：</strong></p>";
echo "<ul>";
foreach ($page1Users as $user) {
    echo "<li>{$user['name']}</li>";
}
echo "</ul>";

$page2Users = DB::table('users')
    ->orderBy('id')
    ->limit(2)
    ->offset(2)
    ->get();

echo "<p><strong>第二页用户（第3-4条）：</strong></p>";
echo "<ul>";
foreach ($page2Users as $user) {
    echo "<li>{$user['name']}</li>";
}
echo "</ul>";

echo "<h3>5. JOIN查询</h3>";

// 内连接
$usersWithProfiles = DB::table('users')
    ->join('profiles', 'users.id', '=', 'profiles.user_id')
    ->select('users.*', 'profiles.bio', 'profiles.avatar')
    ->where('users.status', 'active')
    ->get();

echo "<p><strong>有档案的活跃用户：</strong></p>";
echo "<ul>";
foreach ($usersWithProfiles as $user) {
    echo "<li>{$user['name']} - {$user['bio']}</li>";
}
echo "</ul>";

// 左连接统计订单
$usersWithOrderCount = DB::table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.name', 'COUNT(orders.id) as order_count')
    ->groupBy('users.id', 'users.name')
    ->orderBy('order_count', 'desc')
    ->get();

echo "<p><strong>用户订单统计：</strong></p>";
echo "<ul>";
foreach ($usersWithOrderCount as $user) {
    echo "<li>{$user['name']} - {$user['order_count']}个订单</li>";
}
echo "</ul>";

// ============================================================================
// 数据操作示例
// ============================================================================

echo "<h2>数据操作示例</h2>";

echo "<h3>1. 更新数据</h3>";

// 更新单个用户
$affected = DB::table('users')
    ->where('id', 1)
    ->update([
        'age' => 26,
        'status' => 'active'
    ]);

echo "<p>更新了 {$affected} 条记录（ID为1的用户年龄改为26岁）</p>";

// 批量更新
$affected = DB::table('users')
    ->where('status', 'active')
    ->where('age', '<', 25)
    ->update([
        'status' => 'young_active'
    ]);

echo "<p>批量更新了 {$affected} 条记录（年轻活跃用户状态）</p>";

echo "<h3>2. 删除数据</h3>";

// 删除单个记录
$deleted = DB::table('users')
    ->where('status', 'banned')
    ->delete();

echo "<p>删除了 {$deleted} 条记录（被禁用的用户）</p>";

echo "<h3>3. 复杂的数据操作</h3>";

// 先恢复一些数据用于演示
DB::table('users')->insert([
    'name' => '测试用户',
    'email' => 'test@example.com',
    'status' => 'banned',
    'age' => 40
]);

echo "<h3>4. 事务处理示例</h3>";

// 事务示例
try {
    DB::transaction(function($db) {
        // 插入新用户
        $userId = $db->table('users')->insertGetId([
            'name' => '事务用户',
            'email' => 'transaction@example.com',
            'status' => 'active',
            'age' => 25
        ]);

        // 插入用户档案
        $db->table('profiles')->insert([
            'user_id' => $userId,
            'bio' => '这是一个事务测试用户',
            'avatar' => 'transaction.jpg'
        ]);

        // 插入订单
        $db->table('orders')->insert([
            'user_id' => $userId,
            'amount' => 199.99,
            'status' => 'completed'
        ]);

        echo "<p>✅ 事务执行成功！创建了用户ID：{$userId}</p>";
    });
} catch (Exception $e) {
    echo "<p>❌ 事务执行失败：" . $e->getMessage() . "</p>";
}

// 事务回滚示例
try {
    DB::beginTransaction();

    $userId = DB::table('users')->insertGetId([
        'name' => '回滚测试用户',
        'email' => 'rollback@example.com',
        'status' => 'active',
        'age' => 30
    ]);

    // 故意抛出异常来测试回滚
    if ($userId > 0) {
        throw new Exception('故意抛出异常测试回滚');
    }

    DB::commit();
} catch (Exception $e) {
    DB::rollBack();
    echo "<p>✅ 事务回滚成功！用户未被创建（异常：{$e->getMessage()}）</p>";
}

echo "<h3>5. 原始SQL查询</h3>";

// 使用原始SQL查询
$results = DB::select('
    SELECT u.name, u.age, COUNT(o.id) as order_count, SUM(o.amount) as total_amount
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.status = ?
    GROUP BY u.id, u.name, u.age
    HAVING order_count > 0
    ORDER BY total_amount DESC
', ['active']);

echo "<p><strong>活跃用户的订单统计（原始SQL）：</strong></p>";
echo "<ul>";
foreach ($results as $result) {
    echo "<li>{$result['name']} - {$result['order_count']}个订单，总金额：¥{$result['total_amount']}</li>";
}
echo "</ul>";

// 使用Raw表达式
$usersWithStats = DB::table('users')
    ->select('name', 'age')
    ->selectRaw('(SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) as order_count')
    ->where('status', 'active')
    ->orderBy('order_count', 'desc')
    ->get();

echo "<p><strong>使用Raw表达式统计订单：</strong></p>";
echo "<ul>";
foreach ($usersWithStats as $user) {
    echo "<li>{$user['name']} - {$user['order_count']}个订单</li>";
}
echo "</ul>";

// ============================================================================
// 调试和错误处理示例
// ============================================================================

echo "<h2>调试和错误处理示例</h2>";

echo "<h3>1. 调试模式</h3>";

// 启用调试模式
$db = new Database();
$db->debugMode = true;

// 执行查询并查看调试信息
$users = DB::table('users')
    ->where('status', 'active')
    ->where('age', '>', 20)
    ->orderBy('created_at', 'desc')
    ->limit(3)
    ->get();

echo "<p><strong>最后执行的SQL：</strong></p>";
echo "<pre>" . htmlspecialchars(DB::getConnection()->getLastQuery()) . "</pre>";

echo "<p><strong>SQL参数绑定：</strong></p>";
echo "<pre>" . htmlspecialchars(print_r(DB::getConnection()->getLastBindings(), true)) . "</pre>";

echo "<h3>2. 错误处理</h3>";

// 尝试查询不存在的表
try {
    $results = DB::table('nonexistent_table')->get();
} catch (Exception $e) {
    echo "<p><strong>捕获到错误：</strong>" . $e->getMessage() . "</p>";
}

// 尝试插入重复的数据
try {
    $userId = DB::table('users')->insertGetId([
        'name' => '张三',
        'email' => 'zhangsan@example.com', // 重复的email
        'status' => 'active'
    ]);
} catch (Exception $e) {
    echo "<p><strong>插入重复数据错误：</strong>" . $e->getMessage() . "</p>";
}

echo "<h3>3. 性能测试</h3>";

// 性能测试
$startTime = microtime(true);

// 执行100次简单查询
for ($i = 0; $i < 100; $i++) {
    $user = DB::table('users')->find(1);
}

$endTime = microtime(true);
$duration = ($endTime - $startTime) * 1000; // 转换为毫秒

echo "<p><strong>性能测试：</strong>执行100次查询耗时 " . number_format($duration, 2) . " 毫秒</p>";

// ============================================================================
// 高级用法示例
// ============================================================================

echo "<h2>高级用法示例</h2>";

echo "<h3>1. 复杂查询构建</h3>";

// 复杂的多条件查询
$complexQuery = DB::table('users')
    ->where(function($query) {
        $query->where('status', 'active')
              ->where('age', '>=', 18);
    })
    ->orWhere(function($query) {
        $query->where('status', 'young_active')
              ->where('age', '<', 30);
    })
    ->whereNotNull('email')
    ->orderBy('age', 'asc')
    ->orderBy('name', 'desc')
    ->limit(5)
    ->get();

echo "<p><strong>复杂查询结果：</strong></p>";
echo "<ul>";
foreach ($complexQuery as $user) {
    echo "<li>{$user['name']} - {$user['status']} - {$user['age']}岁</li>";
}
echo "</ul>";

echo "<h3>2. 子查询示例</h3>";

// 使用子查询
$usersWithOrders = DB::table('users')
    ->whereIn('id', function($query) {
        $query->select('user_id')
              ->from('orders')
              ->where('status', 'completed')
              ->where('amount', '>', 100);
    })
    ->get();

echo "<p><strong>有大额订单的用户：</strong></p>";
echo "<ul>";
foreach ($usersWithOrders as $user) {
    echo "<li>{$user['name']}</li>";
}
echo "</ul>";

echo "<h3>3. 数据聚合分析</h3>";

// 用户年龄分布
$ageDistribution = DB::table('users')
    ->selectRaw('
        CASE
            WHEN age < 25 THEN "18-24岁"
            WHEN age < 30 THEN "25-29岁"
            WHEN age < 35 THEN "30-34岁"
            ELSE "35岁以上"
        END as age_group,
        COUNT(*) as user_count
    ')
    ->whereNotNull('age')
    ->groupBy('age_group')
    ->orderBy('user_count', 'desc')
    ->get();

echo "<p><strong>用户年龄分布：</strong></p>";
echo "<ul>";
foreach ($ageDistribution as $group) {
    echo "<li>{$group['age_group']}：{$group['user_count']}人</li>";
}
echo "</ul>";

// 订单状态统计
$orderStats = DB::table('orders')
    ->select('status', 'COUNT(*) as count', 'SUM(amount) as total', 'AVG(amount) as avg')
    ->groupBy('status')
    ->get();

echo "<p><strong>订单状态统计：</strong></p>";
echo "<ul>";
foreach ($orderStats as $stat) {
    echo "<li>{$stat['status']}：{$stat['count']}单，总额¥{$stat['total']}，平均¥{$stat['avg']}</li>";
}
echo "</ul>";

echo "<h3>4. 批量操作优化</h3>";

// 批量插入优化
$batchSize = 50;
$batchData = [];

for ($i = 1; $i <= 200; $i++) {
    $batchData[] = [
        'name' => "批量用户{$i}",
        'email' => "batch{$i}@example.com",
        'status' => 'active',
        'age' => rand(18, 60)
    ];

    if (count($batchData) >= $batchSize) {
        $startTime = microtime(true);
        DB::table('users_test')->insert($batchData);
        $endTime = microtime(true);

        echo "<p>批量插入 " . count($batchData) . " 条记录，耗时 " . number_format(($endTime - $startTime) * 1000, 2) . " 毫秒</p>";
        $batchData = [];
    }
}

// 插入剩余数据
if (!empty($batchData)) {
    DB::table('users_test')->insert($batchData);
    echo "<p>插入剩余 " . count($batchData) . " 条记录</p>";
}

echo "<h3>5. 数据备份和迁移</h3>";

// 数据备份示例
$backupData = DB::table('users')
    ->where('status', 'active')
    ->get();

$backupCount = 0;
foreach ($backupData as $user) {
    DB::table('users_backup')->insert([
        'user_id' => $user['id'],
        'name' => $user['name'],
        'email' => $user['email'],
        'age' => $user['age'],
        'backup_time' => date('Y-m-d H:i:s')
    ]);
    $backupCount++;
}

echo "<p>成功备份 {$backupCount} 个活跃用户数据</p>";

// ============================================================================
// 总结
// ============================================================================

echo "<h2>使用总结</h2>";

echo "<div style='background: #f0f8ff; padding: 15px; border-radius: 5px; margin: 10px 0;'>";
echo "<h3>✨ SimpleDatabase 优势：</h3>";
echo "<ul>";
echo "<li><strong>🔗 链式查询：</strong>Laravel风格的流畅API</li>";
echo "<li><strong>🛡️ 安全防护：</strong>内置SQL注入防护机制</li>";
echo "<li><strong>📦 零依赖：</strong>只需要PDO扩展，单文件部署</li>";
echo "<li><strong>⚡ 高性能：</strong>直接使用PDO，性能优异</li>";
echo "<li><strong>🎯 功能完整：</strong>支持复杂查询、事务、分页等</li>";
echo "<li><strong>🐛 调试友好：</strong>内置调试模式，方便开发</li>";
echo "</ul>";
echo "</div>";

echo "<div style='background: #f0fff0; padding: 15px; border-radius: 5px; margin: 10px 0;'>";
echo "<h3>📋 适用场景：</h3>";
echo "<ul>";
echo "<li>轻量级Web应用</li>";
echo "<li>API接口开发</li>";
echo "<li>数据管理后台</li>";
echo "<li>快速原型开发</li>";
echo "<li>学习PHP数据库操作</li>";
echo "<li>不想引入完整框架的项目</li>";
echo "</ul>";
echo "</div>";

echo "<p style='text-align: center; font-weight: bold; color: #007bff;'>";
echo "🎉 SimpleDatabase - 让数据库操作变得简单优雅！";
echo "</p>";

// 清理测试数据
DB::statement('DROP TABLE IF EXISTS users_test');
DB::statement('DROP TABLE IF EXISTS users_backup');
?>