编程马·菜鸟教程

Appearance

第 12 章：PHP 操作 MySQL ​

12.1 连接数据库 mysqli_connect ​

mysqli_connect 函数 ​

mysqli_connect() 函数用于建立与 MySQL 数据库的连接。

语法 ​

php
mysqli_connect(host, username, password, database, port, socket);

参数说明 ​

  • host：数据库主机名（默认 localhost）
  • username：数据库用户名（默认 root）
  • password：数据库密码
  • database：数据库名称
  • port：数据库端口（默认 3306）
  • socket：套接字路径

连接数据库示例 ​

php
<?php
// 连接数据库
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

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

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

// 关闭连接
mysqli_close($conn);
?>

使用面向对象方式连接 ​

php
<?php
// 连接数据库
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "myDB";

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

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

// 关闭连接
$conn->close();
?>

12.2 执行查询 mysqli_query ​

mysqli_query 函数 ​

mysqli_query() 函数用于执行 SQL 查询。

语法 ​

php
mysqli_query(connection, query, resultmode);

参数说明 ​

  • connection：数据库连接
  • query：SQL 查询语句
  • resultmode：结果模式（可选）

执行查询示例 ​

php
<?php
// 连接数据库
$conn = mysqli_connect("localhost", "root", "", "myDB");

// 执行查询
$sql = "SELECT * FROM students";
$result = mysqli_query($conn, $sql);

// 检查查询结果
if (mysqli_num_rows($result) > 0) {
    // 输出数据
    while($row = mysqli_fetch_assoc($result)) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. " - Age: " . $row["age"]. "<br>";
    }
} else {
    echo "0 结果";
}

// 关闭连接
mysqli_close($conn);
?>

12.3 查询数据 select ​

查询所有数据 ​

php
<?php
// 连接数据库
$conn = mysqli_connect("localhost", "root", "", "myDB");

// 查询所有数据
$sql = "SELECT * FROM students";
$result = mysqli_query($conn, $sql);

// 输出数据
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row["id"] . "<br>";
        echo "姓名: " . $row["name"] . "<br>";
        echo "年龄: " . $row["age"] . "<br>";
        echo "性别: " . $row["gender"] . "<br>";
        echo "邮箱: " . $row["email"] . "<br><br>";
    }
} else {
    echo "没有找到数据";
}

// 关闭连接
mysqli_close($conn);
?>

条件查询 ​

php
<?php
// 连接数据库
$conn = mysqli_connect("localhost", "root", "", "myDB");

// 条件查询
$age = 18;
$sql = "SELECT * FROM students WHERE age > $age";
$result = mysqli_query($conn, $sql);

// 输出数据
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "ID: " . $row["id"] . " - 姓名: " . $row["name"] . " - 年龄: " . $row["age"] . "<br>";
    }
} else {
    echo "没有找到数据";
}

// 关闭连接
mysqli_close($conn);
?>

12.4 添加数据 insert ​

插入单条数据 ​

php
<?php
// 连接数据库
$conn = mysqli_connect("localhost", "root", "", "myDB");

// 插入数据
$name = "赵六";
$age = 21;
$gender = "男";
$email = "zhaoliu@example.com";

$sql = "INSERT INTO students (name, age, gender, email) VALUES ('$name', $age, '$gender', '$email')";

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

// 关闭连接
mysqli_close($conn);
?>

插入多条数据 ​

php
<?php
// 连接数据库
$conn = mysqli_connect("localhost", "root", "", "myDB");

// 插入多条数据
$sql = "INSERT INTO students (name, age, gender, email) VALUES
('孙七', 19, '女', 'sunqi@example.com'),
('周八', 20, '男', 'zhouba@example.com'),
('吴九', 18, '女', 'wuj iu@example.com')";

if (mysqli_query($conn, $sql)) {
    echo "多条记录插入成功";
} else {
    echo "错误: " . $sql . "<br>" . mysqli_error($conn);
}

// 关闭连接
mysqli_close($conn);
?>

12.5 修改数据 update ​

php
<?php
// 连接数据库
$conn = mysqli_connect("localhost", "root", "", "myDB");

// 修改数据
$id = 1;
$age = 22;
$email = "zhangsan_new@example.com";

$sql = "UPDATE students SET age = $age, email = '$email' WHERE id = $id";

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

// 关闭连接
mysqli_close($conn);
?>

12.6 删除数据 delete ​

php
<?php
// 连接数据库
$conn = mysqli_connect("localhost", "root", "", "myDB");

// 删除数据
$id = 5;

$sql = "DELETE FROM students WHERE id = $id";

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

// 关闭连接
mysqli_close($conn);
?>

12.7 防止 SQL 注入 ​

SQL 注入的危害 ​

SQL 注入是一种常见的 Web 安全漏洞，攻击者可以通过输入恶意 SQL 代码来操纵数据库。

防止 SQL 注入的方法 ​

1. 使用 mysqli_real_escape_string() ​

php
<?php
// 连接数据库
$conn = mysqli_connect("localhost", "root", "", "myDB");

// 接收用户输入
$username = $_POST['username'] ?? '';
$password = $_POST['password'] ?? '';

// 转义特殊字符
$username = mysqli_real_escape_string($conn, $username);
$password = mysqli_real_escape_string($conn, $password);

// 执行查询
$sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
$result = mysqli_query($conn, $sql);

// 处理结果
// ...

// 关闭连接
mysqli_close($conn);
?>

2. 使用预处理语句（推荐） ​

php
<?php
// 连接数据库
$conn = mysqli_connect("localhost", "root", "", "myDB");

// 接收用户输入
$username = $_POST['username'] ?? '';
$password = $_POST['password'] ?? '';

// 预处理语句
$stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
$stmt->bind_param("ss", $username, $password);

// 执行查询
$stmt->execute();
$result = $stmt->get_result();

// 处理结果
if ($result->num_rows > 0) {
    echo "登录成功";
} else {
    echo "用户名或密码错误";
}

// 关闭语句和连接
$stmt->close();
$conn->close();
?>

12.8 实操：学生信息管理（增删改查） ​

创建 student-management.php 文件 ​

php
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>学生信息管理</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
        }
        .section {
            margin: 20px 0;
            padding: 15px;
            border: 1px solid #ccc;
            border-radius: 5px;
        }
        h2 {
            color: #333;
        }
        table {
            border-collapse: collapse;
            width: 100%;
            margin: 10px 0;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2;
        }
        input, button {
            padding: 8px;
            margin: 5px 0;
        }
        .success {
            color: green;
        }
        .error {
            color: red;
        }
    </style>
</head>
<body>
    <h1>学生信息管理系统</h1>
    
    <!-- 连接数据库 -->
    <?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "myDB";
    
    // 创建连接
    $conn = mysqli_connect($servername, $username, $password, $dbname);
    
    // 检查连接
    if (!$conn) {
        die("连接失败: " . mysqli_connect_error());
    }
    
    // 创建数据库（如果不存在）
    $sql = "CREATE DATABASE IF NOT EXISTS $dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
    mysqli_query($conn, $sql);
    
    // 选择数据库
    mysqli_select_db($conn, $dbname);
    
    // 创建学生表（如果不存在）
    $sql = "CREATE TABLE IF NOT EXISTS students (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(50) NOT NULL,
        age INT,
        gender VARCHAR(10),
        email VARCHAR(100)
    )";
    mysqli_query($conn, $sql);
    
    // 处理添加学生
    if (isset($_POST['add'])) {
        $name = mysqli_real_escape_string($conn, $_POST['name']);
        $age = (int)$_POST['age'];
        $gender = mysqli_real_escape_string($conn, $_POST['gender']);
        $email = mysqli_real_escape_string($conn, $_POST['email']);
        
        $sql = "INSERT INTO students (name, age, gender, email) VALUES ('$name', $age, '$gender', '$email')";
        if (mysqli_query($conn, $sql)) {
            $message = "学生添加成功";
            $message_type = "success";
        } else {
            $message = "错误: " . mysqli_error($conn);
            $message_type = "error";
        }
    }
    
    // 处理删除学生
    if (isset($_GET['delete'])) {
        $id = (int)$_GET['delete'];
        $sql = "DELETE FROM students WHERE id = $id";
        if (mysqli_query($conn, $sql)) {
            $message = "学生删除成功";
            $message_type = "success";
        } else {
            $message = "错误: " . mysqli_error($conn);
            $message_type = "error";
        }
    }
    
    // 处理编辑学生
    if (isset($_POST['edit'])) {
        $id = (int)$_POST['id'];
        $name = mysqli_real_escape_string($conn, $_POST['name']);
        $age = (int)$_POST['age'];
        $gender = mysqli_real_escape_string($conn, $_POST['gender']);
        $email = mysqli_real_escape_string($conn, $_POST['email']);
        
        $sql = "UPDATE students SET name = '$name', age = $age, gender = '$gender', email = '$email' WHERE id = $id";
        if (mysqli_query($conn, $sql)) {
            $message = "学生信息更新成功";
            $message_type = "success";
        } else {
            $message = "错误: " . mysqli_error($conn);
            $message_type = "error";
        }
    }
    
    // 获取编辑学生信息
    $edit_student = null;
    if (isset($_GET['edit'])) {
        $id = (int)$_GET['edit'];
        $sql = "SELECT * FROM students WHERE id = $id";
        $result = mysqli_query($conn, $sql);
        if (mysqli_num_rows($result) > 0) {
            $edit_student = mysqli_fetch_assoc($result);
        }
    }
    
    // 查询所有学生
    $sql = "SELECT * FROM students";
    $result = mysqli_query($conn, $sql);
    ?>
    
    <!-- 显示消息 -->
    <?php if (isset($message)): ?>
        <p class="<?php echo $message_type; ?>"><?php echo $message; ?></p>
    <?php endif; ?>
    
    <!-- 添加学生 -->
    <div class="section">
        <h2><?php echo $edit_student ? '编辑学生' : '添加学生'; ?></h2>
        <form method="post">
            <?php if ($edit_student): ?>
                <input type="hidden" name="id" value="<?php echo $edit_student['id']; ?>">
            <?php endif; ?>
            <input type="text" name="name" placeholder="姓名" value="<?php echo $edit_student ? $edit_student['name'] : ''; ?>" required><br>
            <input type="number" name="age" placeholder="年龄" value="<?php echo $edit_student ? $edit_student['age'] : ''; ?>" required><br>
            <select name="gender" required>
                <option value="" disabled <?php echo !$edit_student ? 'selected' : ''; ?>>选择性别</option>
                <option value="男" <?php echo $edit_student && $edit_student['gender'] == '男' ? 'selected' : ''; ?>>男</option>
                <option value="女" <?php echo $edit_student && $edit_student['gender'] == '女' ? 'selected' : ''; ?>>女</option>
            </select><br>
            <input type="email" name="email" placeholder="邮箱" value="<?php echo $edit_student ? $edit_student['email'] : ''; ?>" required><br>
            <button type="submit" name="<?php echo $edit_student ? 'edit' : 'add'; ?>"><?php echo $edit_student ? '更新' : '添加'; ?></button>
        </form>
    </div>
    
    <!-- 学生列表 -->
    <div class="section">
        <h2>学生列表</h2>
        <table>
            <tr>
                <th>ID</th>
                <th>姓名</th>
                <th>年龄</th>
                <th>性别</th>
                <th>邮箱</th>
                <th>操作</th>
            </tr>
            <?php if (mysqli_num_rows($result) > 0): ?>
                <?php while($row = mysqli_fetch_assoc($result)): ?>
                    <tr>
                        <td><?php echo $row['id']; ?></td>
                        <td><?php echo $row['name']; ?></td>
                        <td><?php echo $row['age']; ?></td>
                        <td><?php echo $row['gender']; ?></td>
                        <td><?php echo $row['email']; ?></td>
                        <td>
                            <a href="?edit=<?php echo $row['id']; ?>">编辑</a> | 
                            <a href="?delete=<?php echo $row['id']; ?>" onclick="return confirm('确定要删除吗？');">删除</a>
                        </td>
                    </tr>
                <?php endwhile; ?>
            <?php else: ?>
                <tr>
                    <td colspan="6">没有学生数据</td>
                </tr>
            <?php endif; ?>
        </table>
    </div>
    
    <!-- 关闭连接 -->
    <?php mysqli_close($conn); ?>
</body>
</html>

访问页面 ​

  1. 将文件保存到网站根目录
  2. 打开浏览器，访问 http://localhost/student-management.php
  3. 测试学生信息的增删改查功能

小结 ​

通过本章的学习，你掌握了 PHP 操作 MySQL 数据库的基本方法，包括连接数据库、执行查询、增删改查操作以及防止 SQL 注入。这些知识是 PHP 开发 Web 应用的核心，在后续的实战项目中，你将使用这些技术来实现数据的存储和管理。

© 2026 编程马·菜鸟教程 版权所有

基于 VitePress 构建