CRUD操作

什么是CRUD

CRUD是数据库操作的四个基本功能：

• Create（创建）：插入新数据
• Read（读取）：查询数据
• Update（更新）：修改现有数据
• Delete（删除）：删除数据

准备工作

创建示例数据表

<?php
// 创建用户表的SQL语句
$createTableSQL = "
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    age INT NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

// 执行创建表的SQL
$pdo->exec($createTableSQL);
echo "用户表创建成功！";
?>

CREATE（创建数据）

基本插入操作

<?php
// 使用预处理语句（推荐）
function insertUser($pdo, $username, $email, $age, $status = 'active') {
    $sql = "INSERT INTO users (username, email, age, status) VALUES (?, ?, ?, ?)";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$username, $email, $age, $status]);

        // 返回插入的ID
        return $pdo->lastInsertId();

    } catch (PDOException $e) {
        echo "插入失败: " . $e->getMessage();
        return false;
    }
}

// 使用示例
$userId = insertUser($pdo, 'zhangsan', 'zhangsan@example.com', 25);
if ($userId) {
    echo "用户插入成功，ID: $userId";
}
?>

使用命名参数

<?php
function insertUserWithNamedParams($pdo, $userData) {
    $sql = "INSERT INTO users (username, email, age, status)
            VALUES (:username, :email, :age, :status)";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($userData);

        return $pdo->lastInsertId();

    } catch (PDOException $e) {
        echo "插入失败: " . $e->getMessage();
        return false;
    }
}

// 使用示例
$userData = [
    'username' => 'lisi',
    'email' => 'lisi@example.com',
    'age' => 30,
    'status' => 'active'
];

$userId = insertUserWithNamedParams($pdo, $userData);
echo "用户插入成功，ID: $userId";
?>

READ（读取数据）

查询单条记录

<?php
// 根据ID查询用户
function getUserById($pdo, $id) {
    $sql = "SELECT * FROM users WHERE id = ?";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$id]);
        return $stmt->fetch();

    } catch (PDOException $e) {
        echo "查询失败: " . $e->getMessage();
        return false;
    }
}

// 使用示例
$user = getUserById($pdo, 1);
if ($user) {
    echo "用户名: " . $user['username'] . "<br>";
    echo "邮箱: " . $user['email'] . "<br>";
}
?>

查询多条记录

<?php
// 查询所有用户
function getAllUsers($pdo) {
    $sql = "SELECT * FROM users ORDER BY created_at DESC";

    try {
        $stmt = $pdo->query($sql);
        return $stmt->fetchAll();

    } catch (PDOException $e) {
        echo "查询失败: " . $e->getMessage();
        return [];
    }
}

// 分页查询
function getUsersWithPagination($pdo, $page = 1, $limit = 10) {
    $offset = ($page - 1) * $limit;

    $sql = "SELECT * FROM users ORDER BY created_at DESC LIMIT ? OFFSET ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$limit, $offset]);
    $users = $stmt->fetchAll();

    $countSql = "SELECT COUNT(*) as total FROM users";
    $total = $pdo->query($countSql)->fetch()['total'];

    return [
        'users' => $users,
        'total' => $total,
        'page' => $page,
        'total_pages' => ceil($total / $limit)
    ];
}

// 使用示例
$users = getAllUsers($pdo);
foreach ($users as $user) {
    echo "ID: {$user['id']}, 用户名: {$user['username']}<br>";
}
?>

UPDATE（更新数据）

<?php
// 更新用户信息
function updateUser($pdo, $id, $data) {
    $setParts = [];
    $params = [];

    foreach (['username', 'email', 'age', 'status'] as $field) {
        if (isset($data[$field])) {
            $setParts[] = "$field = ?";
            $params[] = $data[$field];
        }
    }

    if (empty($setParts)) {
        return false;
    }

    $sql = "UPDATE users SET " . implode(', ', $setParts) . " WHERE id = ?";
    $params[] = $id;

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();

    } catch (PDOException $e) {
        echo "更新失败: " . $e->getMessage();
        return false;
    }
}

// 使用示例
$updateData = [
    'email' => 'newemail@example.com',
    'age' => 26
];

$affectedRows = updateUser($pdo, 1, $updateData);
echo "更新了 $affectedRows 行记录";
?>

DELETE（删除数据）

<?php
// 根据ID删除用户
function deleteUser($pdo, $id) {
    $sql = "DELETE FROM users WHERE id = ?";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$id]);
        return $stmt->rowCount();

    } catch (PDOException $e) {
        echo "删除失败: " . $e->getMessage();
        return false;
    }
}

// 软删除（推荐）
function softDeleteUser($pdo, $id) {
    $sql = "UPDATE users SET status = 'deleted' WHERE id = ?";

    try {
        $stmt = $pdo->prepare($sql);
        $stmt->execute([$id]);
        return $stmt->rowCount();

    } catch (PDOException $e) {
        echo "软删除失败: " . $e->getMessage();
        return false;
    }
}

// 使用示例
$deletedRows = deleteUser($pdo, 1);
echo "删除了 $deletedRows 行记录";
?>

综合示例

用户管理类

<?php
class UserManager {
    private $pdo;

    public function __construct($pdo) {
        $this->pdo = $pdo;
    }

    // 创建用户
    public function create($userData) {
        $sql = "INSERT INTO users (username, email, age, status)
                VALUES (:username, :email, :age, :status)";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($userData);
            return $this->pdo->lastInsertId();

        } catch (PDOException $e) {
            throw new Exception("创建用户失败: " . $e->getMessage());
        }
    }

    // 读取用户
    public function read($id) {
        $sql = "SELECT * FROM users WHERE id = :id";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['id' => $id]);
            return $stmt->fetch();

        } catch (PDOException $e) {
            throw new Exception("读取用户失败: " . $e->getMessage());
        }
    }

    // 更新用户
    public function update($id, $userData) {
        $setParts = [];
        foreach ($userData as $field => $value) {
            $setParts[] = "$field = :$field";
        }

        $sql = "UPDATE users SET " . implode(', ', $setParts) . " WHERE id = :id";
        $userData['id'] = $id;

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($userData);
            return $stmt->rowCount();

        } catch (PDOException $e) {
            throw new Exception("更新用户失败: " . $e->getMessage());
        }
    }

    // 删除用户
    public function delete($id) {
        $sql = "DELETE FROM users WHERE id = :id";

        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(['id' => $id]);
            return $stmt->rowCount();

        } catch (PDOException $e) {
            throw new Exception("删除用户失败: " . $e->getMessage());
        }
    }
}

// 使用示例
$userManager = new UserManager($pdo);

// 创建用户
$userId = $userManager->create([
    'username' => 'testuser',
    'email' => 'test@example.com',
    'age' => 25,
    'status' => 'active'
]);

// 读取用户
$user = $userManager->read($userId);

// 更新用户
$userManager->update($userId, ['age' => 26]);
?>

通过本节的学习，你应该掌握了使用PDO进行CRUD操作的基本技巧。下一节我们将深入学习预处理语句的高级用法。