PHP MySQL 数据库

$host   = 'localhost';
$user   = 'root';
$pass   = 'your_password';
$dbname = 'myapp';

// 面向对象方式
$conn = new mysqli($host, $user, $pass, $dbname);
$conn->set_charset('utf8mb4');
if ($conn->connect_error) {
    die("连接失败：" . $conn->connect_error);
}
echo "连接成功";
?>
``

创建数据表
`sql
CREATE TABLE users (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    username   VARCHAR(50)  NOT NULL UNIQUE,
    email      VARCHAR(100) NOT NULL,
    password   VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
`

增删改查（CRUD）
插入数据
`php
<?php
// 使用预处理语句防止 SQL 注入
$stmt = $conn->prepare("INSERT INTO users (username, email, password) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $username, $email, $hashed);

$username = "张三";
$email    = "zhangsan@example.com";
$hashed   = password_hash("123456", PASSWORD_DEFAULT);
$stmt->execute();
echo "插入成功，ID：" . $conn->insert_id;
$stmt->close();
?>
`

查询数据
`php
<?php
// 查询所有
$result = $conn->query("SELECT id, username, email FROM users");
while ($row = $result->fetch_assoc()) {
    echo "{$row['id']} - {$row['username']} - {$row['email']}\n";
}

// 带条件查询（预处理）
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 1;
$stmt->execute();
$user = $stmt->get_result()->fetch_assoc();
echo $user['username'];
$stmt->close();
?>
`

更新数据
`php
<?php
$stmt = $conn->prepare("UPDATE users SET email = ? WHERE id = ?");
$stmt->bind_param("si", $email, $id);

$email = "new@example.com";
$id    = 1;
$stmt->execute();
echo "影响行数：" . $stmt->affected_rows;
$stmt->close();
?>
`

删除数据
`php
<?php
$stmt = $conn->prepare("DELETE FROM users WHERE id = ?");
$stmt->bind_param("i", $id);
$id = 1;
$stmt->execute();
echo "删除成功";
$stmt->close();
?>
`

bind_param 类型说明
字符
类型
s
string 字符串
i
integer 整数
d
double 浮点数
b
blob 二进制
事务处理
`php
<?php
$conn->begin_transaction();

try {
    // 转账：A 扣款
    $stmt = $conn->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
    $stmt->bind_param("di", $amount, $fromId);
    $amount = 500; $fromId = 1;
    $stmt->execute();
// 转账：B 收款
    $stmt = $conn->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
    $stmt->bind_param("di", $amount, $toId);
    $toId = 2;
    $stmt->execute();
$conn->commit();
    echo "转账成功";
} catch (Exception $e) {
    $conn->rollback();
    echo "转账失败：" . $e->getMessage();
}
?>
`

分页查询
`php
<?php
$page     = max(1, (int)($_GET['page'] ?? 1));
$pageSize = 10;
$offset   = ($page - 1) * $pageSize;

// 查询总数
$total = $conn->query("SELECT COUNT(*) FROM users")->fetch_row()[0];
$pages = ceil($total / $pageSize);
// 分页数据
$stmt = $conn->prepare("SELECT * FROM users ORDER BY id DESC LIMIT ? OFFSET ?");
$stmt->bind_param("ii", $pageSize, $offset);
$stmt->execute();
$users = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);
foreach ($users as $user) {
    echo $user['username'] . "\n";
}
echo "第 $page / $pages 页，共 $total 条";
?>
`

关闭连接
``php
<?php
$conn->close();