数据库操作

学习PHP与MySQL数据库的连接和操作

全站首页 频道首页

数据库连接

PHP提供了多种方式连接MySQL数据库，主要有MySQLi和PDO两种扩展。

MySQLi扩展

MySQLi（MySQL improved）是MySQL的改进扩展，支持面向对象和面向过程两种编程方式。

mysqli_connect.php 
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检查连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}
echo "连接成功";
?>

PDO扩展

PDO（PHP Data Objects）是一个数据库访问抽象层，支持多种数据库系统。

pdo_connect.php 
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // 设置 PDO 错误模式为异常
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "连接成功";
} catch(PDOException $e) {
    echo "连接失败: " . $e->getMessage();
}
?>

MySQLi vs PDO

特性 MySQLi PDO
数据库支持 仅MySQL 多种数据库
编程方式 面向对象和面向过程 仅面向对象
预处理语句 支持 支持
命名参数 不支持 支持

创建数据库和表

在开始操作数据之前，需要先创建数据库和表结构。

create_database.php 
<?php
$servername = "localhost";
$username = "username";
$password = "password";

// 创建连接
$conn = mysqli_connect($servername, $username, $password);

// 检查连接
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}

// 创建数据库
$sql = "CREATE DATABASE myDB";
if (mysqli_query($conn, $sql)) {
    echo "数据库创建成功";
} else {
    echo "Error creating database: " . mysqli_error($conn);
}

mysqli_close($conn);
?>
create_table.php 
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = mysqli_connect($servername, $username, $password, $dbname);

$sql = "CREATE TABLE Users (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)";

if (mysqli_query($conn, $sql)) {
    echo "表 Users 创建成功";
} else {
    echo "创建数据表错误: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

数据库设计原则

  • 为每个表设置主键
  • 使用适当的数据类型
  • 设置合理的字段长度
  • 添加必要的索引
  • 遵循数据库范式

插入数据

向数据库表中添加新记录。

insert_data.php 
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = mysqli_connect($servername, $username, $password, $dbname);

// 使用预处理语句防止SQL注入
$stmt = $conn->prepare("INSERT INTO Users (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// 设置参数并执行
$firstname = "张";
$lastname = "三";
$email = "zhangsan@example.com";
$stmt->execute();

$firstname = "李";
$lastname = "四";
$email = "lisi@example.com";
$stmt->execute();

echo "新记录插入成功";

$stmt->close();
$conn->close();
?>

插入数据的多种方式

  • 简单插入： 使用INSERT INTO语句
  • 批量插入： 一次插入多条记录
  • 预处理语句： 防止SQL注入，提高安全性
  • 忽略重复： 使用INSERT IGNORE忽略重复记录

查询数据

从数据库表中检索数据。

select_data.php 
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = mysqli_connect($servername, $username, $password, $dbname);

$sql = "SELECT id, firstname, lastname, email FROM Users";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // 输出数据
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - 姓名: " . $row["firstname"]. " " . $row["lastname"]. " - 邮箱: " . $row["email"]. "<br>";
    }
} else {
    echo "0 结果";
}

mysqli_close($conn);
?>

查询数据的常用方法

  • mysqli_fetch_assoc： 返回关联数组
  • mysqli_fetch_array： 返回关联数组和数字数组
  • mysqli_fetch_row： 返回数字数组
  • mysqli_fetch_object： 返回对象

高级查询技巧

  • 使用WHERE子句过滤数据
  • 使用ORDER BY排序结果
  • 使用LIMIT限制返回记录数
  • 使用JOIN连接多个表
  • 使用GROUP BY分组数据

更新和删除数据

修改和删除数据库中的记录。

更新数据

update_data.php 
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = mysqli_connect($servername, $username, $password, $dbname);

$sql = "UPDATE Users SET email='newemail@example.com' WHERE id=1";

if (mysqli_query($conn, $sql)) {
    echo "记录更新成功";
} else {
    echo "更新错误: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

删除数据

delete_data.php 
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = mysqli_connect($servername, $username, $password, $dbname);

$sql = "DELETE FROM Users WHERE id=2";

if (mysqli_query($conn, $sql)) {
    echo "记录删除成功";
} else {
    echo "删除错误: " . mysqli_error($conn);
}

mysqli_close($conn);
?>

更新和删除的注意事项

  • 始终使用WHERE子句，避免意外更新或删除所有记录
  • 在执行删除操作前备份数据
  • 考虑使用软删除（标记删除而非物理删除）
  • 使用事务确保数据一致性

数据库事务

事务是一组要么全部成功要么全部失败的数据库操作。

事务处理示例 
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = mysqli_connect($servername, $username, $password, $dbname);

// 开始事务
mysqli_begin_transaction($conn);

try {
    // 执行多个SQL语句
    $sql1 = "UPDATE accounts SET balance = balance - 100 WHERE id = 1";
    $sql2 = "UPDATE accounts SET balance = balance + 100 WHERE id = 2";
    
    mysqli_query($conn, $sql1);
    mysqli_query($conn, $sql2);
    
    // 提交事务
    mysqli_commit($conn);
    echo "事务成功完成";
} catch (Exception $e) {
    // 回滚事务
    mysqli_rollback($conn);
    echo "事务失败: " . $e->getMessage();
}

mysqli_close($conn);
?>

数据库安全最佳实践

安全提示：
  • 始终使用预处理语句来防止SQL注入
  • 对用户输入进行验证和过滤
  • 使用最小权限原则，为数据库用户分配必要的最小权限
  • 定期备份数据库
  • 使用强密码并定期更换
  • 加密敏感数据
  • 使用HTTPS保护数据传输
  • 定期更新数据库软件

防止SQL注入

SQL注入是最常见的Web安全漏洞之一。使用预处理语句可以有效防止SQL注入攻击。

预处理语句示例 
<?php
// 使用预处理语句防止SQL注入
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);

$username = $_POST['username'];
$password = md5($_POST['password']);
$stmt->execute();

$result = $stmt->get_result();
?>

数据库性能优化

查询优化

  • 使用EXPLAIN分析查询性能
  • 为常用查询字段创建索引
  • 避免在WHERE子句中使用函数
  • 只选择需要的列
  • 使用LIMIT限制返回记录数

连接优化

  • 使用连接池
  • 及时关闭数据库连接
  • 使用持久连接

数据库设计优化

  • 规范化数据库设计
  • 适当使用反规范化提高查询性能
  • 分区大型表
  • 使用适当的数据类型

实践练习

创建一个完整的用户管理系统，包含以下功能：

  1. 用户注册
  2. 用户登录
  3. 用户信息查看
  4. 用户信息修改
  5. 用户删除
  6. 用户列表分页显示
提示： 可以使用MySQLi或PDO扩展实现，确保使用预处理语句防止SQL注入。

扩展练习

尝试实现以下更复杂的功能：

  • 用户权限管理
  • 数据导出功能
  • 数据库备份和恢复
  • 数据库迁移工具
← 上一课：会话与Cookies 下一课：面向对象编程 →