PHP：MySQL数据库基础

风沢鸣人

329人浏览 · 2026-05-27 09:05:50

风沢鸣人 · 2026-05-27 09:05:50 发布

PHP：MySQL数据库基础

1 什么是数据库？

数据库就像一个电子化的文件柜，用来存储数据。MySQL是最流行的开源数据库。

2 开启MySQL服务

默认配置：

主机：localhost
端口：3306
用户名：root
密码：root（或空）

3 使用 PDO 连接数据库（推荐）

PDO是PHP的数据库抽象层，支持多种数据库，安全且现代。

代码示例

<?php
$host = "localhost";
$dbname = "test";
$username = "root";
$password = "root";

try {
    // 连接数据库
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    echo "连接成功！";
} catch (PDOException $e) {
    die("连接失败：" . $e->getMessage());
}
?>

4 创建数据库和表

先创建一个测试数据库和表：

使用 phpMyAdmin

访问 http://localhost/phpmyadmin
新建数据库 test
在 test 库里执行以下SQL创建表：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

5 插入数据（INSERT）

代码示例

<?php
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8", "root", "root");

// 方式1：直接插入
$sql = "INSERT INTO users (name, age, email) VALUES ('小明', 18, 'xiaoming@example.com')";
$pdo->exec($sql);

// 方式2：预处理语句（推荐，更安全）
$stmt = $pdo->prepare("INSERT INTO users (name, age, email) VALUES (?, ?, ?)");
$stmt->execute(["小红", 20, "xiaohong@example.com"]);
$stmt->execute(["小刚", 22, "xiaogang@example.com"]);

echo "插入成功！";
?>

6 查询数据（SELECT）

代码示例

<?php
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8", "root", "root");

// 查询所有数据
$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "<table border='1'>";
echo "<tr><th>ID</th><th>姓名</th><th>年龄</th><th>邮箱</th></tr>";
foreach ($users as $user) {
    echo "<tr>";
    echo "<td>" . $user['id'] . "</td>";
    echo "<td>" . $user['name'] . "</td>";
    echo "<td>" . $user['age'] . "</td>";
    echo "<td>" . $user['email'] . "</td>";
    echo "</tr>";
}
echo "</table>";

// 条件查询
$stmt = $pdo->prepare("SELECT * FROM users WHERE age > ?");
$stmt->execute([18]);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($result);
?>

7 更新数据（UPDATE）

代码示例

<?php
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8", "root", "root");

// 更新小明的年龄
$stmt = $pdo->prepare("UPDATE users SET age = ? WHERE name = ?");
$stmt->execute([19, "小明"]);

echo "更新成功！影响行数：" . $stmt->rowCount();
?>

8 删除数据（DELETE）

代码示例

<?php
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8", "root", "root");

// 删除小刚
$stmt = $pdo->prepare("DELETE FROM users WHERE name = ?");
$stmt->execute(["小刚"]);

echo "删除成功！";
?>

9 完整示例：用户管理系统

users.php

<?php
$pdo = new PDO("mysql:host=localhost;dbname=test;charset=utf8", "root", "root");

// 处理添加
if (isset($_POST['add'])) {
    $stmt = $pdo->prepare("INSERT INTO users (name, age, email) VALUES (?, ?, ?)");
    $stmt->execute([$_POST['name'], $_POST['age'], $_POST['email']]);
}

// 处理删除
if (isset($_GET['delete'])) {
    $stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
    $stmt->execute([$_GET['delete']]);
}

// 查询所有用户
$users = $pdo->query("SELECT * FROM users")->fetchAll(PDO::FETCH_ASSOC);
?>

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>用户管理</title>
</head>
<body>
    <h2>添加用户</h2>
    <form method="post">
        <input type="text" name="name" placeholder="姓名" required>
        <input type="number" name="age" placeholder="年龄">
        <input type="email" name="email" placeholder="邮箱">
        <button type="submit" name="add">添加</button>
    </form>

    <h2>用户列表</h2>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>姓名</th>
            <th>年龄</th>
            <th>邮箱</th>
            <th>操作</th>
        </tr>
        <?php foreach ($users as $user): ?>
        <tr>
            <td><?= $user['id'] ?></td>
            <td><?= $user['name'] ?></td>
            <td><?= $user['age'] ?></td>
            <td><?= $user['email'] ?></td>
            <td><a href="?delete=<?= $user['id'] ?>">删除</a></td>
        </tr>
        <?php endforeach; ?>
    </table>
</body>
</html>

本章代码汇总

文件位置：examples/10-database.php

<?php
// 第10章完整示例（演示用，需要先建库建表）

echo "<h3>PDO数据库操作演示</h3>";
echo "<p>请先创建数据库 test 和表 users</p>";

$host = "localhost";
$dbname = "test";
$username = "root";
$password = "root";

try {
    $pdo = new PDO("mysql:host=$host;dbname=$dbname;charset=utf8", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "<p style='color:green'>数据库连接成功！</p>";
    
    // 建表SQL（如果不存在）
    $createTable = "CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        age INT,
        email VARCHAR(100),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )";
    $pdo->exec($createTable);
    echo "<p>users表已准备就绪</p>";
    
    // 演示插入
    $stmt = $pdo->prepare("INSERT INTO users (name, age, email) VALUES (?, ?, ?)");
    $stmt->execute(["张三", 25, "zhangsan@example.com"]);
    $stmt->execute(["李四", 30, "lisi@example.com"]);
    
    // 查询并显示
    $users = $pdo->query("SELECT * FROM users")->fetchAll(PDO::FETCH_ASSOC);
    echo "<h4>用户列表：</h4>";
    echo "<table border='1'>";
    echo "<tr><th>ID</th><th>姓名</th><th>年龄</th><th>邮箱</th></tr>";
    foreach ($users as $user) {
        echo "<tr>";
        echo "<td>{$user['id']}</td>";
        echo "<td>{$user['name']}</td>";
        echo "<td>{$user['age']}</td>";
        echo "<td>{$user['email']}</td>";
        echo "</tr>";
    }
    echo "</table>";
    
} catch (PDOException $e) {
    echo "<p style='color:red'>错误：" . $e->getMessage() . "</p>";
}
?>