第10章：MySQL 数据库基础

数据库是现代Web应用的核心组件，它负责存储、管理和检索应用程序数据。MySQL作为最受欢迎的开源关系型数据库管理系统，是PHP开发者的首选数据库之一。

学习目标

完成本章学习后，你将能够：

• 理解数据库的基本概念和关系模型
• 掌握MySQL数据库的安装和配置
• 熟练使用SQL语言进行数据操作
• 设计合理的数据库表结构
• 理解数据库索引和性能优化基础
• 掌握PHP与MySQL的连接方法
• 能够创建和管理简单的数据库应用

本章内容概览

• 数据库基本概念
• MySQL安装与配置
• SQL语言基础
• 数据库和表的管理
• 数据操作语言(DML)
• 数据查询语言(DQL)
• 数据库设计原则
• PHP连接MySQL

数据库基本概念

什么是数据库？

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。它是一个长期存储在计算机内、有组织的、可共享的大量数据的集合。

关系型数据库的特点

关系型数据库基于关系模型来组织数据，具有以下特点：

1. 数据以表格形式存储：数据存储在二维表中
2. 表之间的关系：通过键值建立表之间的关联
3. ACID特性：保证事务的原子性、一致性、隔离性和持久性
4. SQL标准：使用标准化的查询语言

核心概念

-- 数据库：存储数据的容器
CREATE DATABASE myapp;

-- 表：数据的结构化存储
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 记录：表中的一行数据
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

-- 字段：表中的一列
SELECT username, email FROM users;

数据库设计原则

1. 实体-关系模型

实体(Entity)：现实世界中的对象 属性(Attribute)：实体的特征 关系(Relationship)：实体之间的联系

2. 范式化

• 第一范式(1NF)：字段不可再分
• 第二范式(2NF)：满足1NF，非主键字段完全依赖于主键
• 第三范式(3NF)：满足2NF，非主键字段不传递依赖于主键

MySQL安装与配置

1. Windows安装

# 下载MySQL安装包
# 访问 https://dev.mysql.com/downloads/mysql/

# 使用MySQL Installer安装
# 配置root密码
# 启动MySQL服务
net start mysql80

2. Linux安装(Ubuntu/Debian)

# 更新包管理器
sudo apt update

# 安装MySQL服务器
sudo apt install mysql-server

# 启动MySQL服务
sudo systemctl start mysql
sudo systemctl enable mysql

# 安全配置
sudo mysql_secure_installation

3. macOS安装

# 使用Homebrew安装
brew install mysql

# 启动MySQL服务
brew services start mysql

# 设置root密码
mysqladmin -u root password 'your_password'

4. 基本配置

# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# 设置默认字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 设置端口
port = 3306

# 设置最大连接数
max_connections = 100

# 设置查询缓存
query_cache_type = 1
query_cache_size = 16M

# 设置InnoDB缓冲池
innodb_buffer_pool_size = 128M

5. 连接MySQL

# 命令行连接
mysql -u username -p -h hostname database_name

# 连接示例
mysql -u root -p -h localhost mysql

# 查看数据库列表
SHOW DATABASES;

# 退出MySQL
EXIT;

SQL语言基础

SQL（Structured Query Language）是用于管理关系数据库的标准语言。

SQL的分类

1. DDL（数据定义语言）：CREATE, ALTER, DROP
2. DML（数据操作语言）：INSERT, UPDATE, DELETE
3. DQL（数据查询语言）：SELECT
4. DCL（数据控制语言）：GRANT, REVOKE
5. TCL（事务控制语言）：COMMIT, ROLLBACK, SAVEPOINT

基本语法规则

-- SQL注释：使用双横线
/*
   多行注释
   使用斜杠星号
*/

-- 关键字大写，表名和字段名小写（推荐）
SELECT * FROM users WHERE id = 1;

-- 语句以分号结尾
INSERT INTO users (username) VALUES ('john');

-- 字符串使用单引号
SELECT * FROM users WHERE name = 'John Doe';

-- 数值不需要引号
SELECT * FROM users WHERE age > 18;

数据库和表的管理

1. 数据库操作

-- 创建数据库
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 查看所有数据库
SHOW DATABASES;

-- 选择数据库
USE myapp;

-- 删除数据库
DROP DATABASE myapp;

-- 修改数据库字符集
ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. 表的基本操作

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    age INT,
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'inactive',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 查看表结构
DESCRIBE users;
-- 或者
SHOW COLUMNS FROM users;

-- 查看创建表的SQL语句
SHOW CREATE TABLE users;

-- 修改表结构
-- 添加字段
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- 修改字段类型
ALTER TABLE users MODIFY COLUMN phone VARCHAR(25);

-- 修改字段名和类型
ALTER TABLE users CHANGE COLUMN phone mobile_phone VARCHAR(25);

-- 删除字段
ALTER TABLE users DROP COLUMN mobile_phone;

-- 添加索引
ALTER TABLE users ADD INDEX idx_age (age);

-- 删除索引
ALTER TABLE users DROP INDEX idx_age;

-- 重命名表
ALTER TABLE users RENAME TO app_users;

-- 删除表
DROP TABLE users;

-- 清空表数据（保留表结构）
TRUNCATE TABLE users;

3. 数据类型详解

数值类型

-- 整数类型
CREATE TABLE number_types (
    tinyint_col TINYINT,           -- 1字节，-128到127
    smallint_col SMALLINT,         -- 2字节，-32768到32767
    mediumint_col MEDIUMINT,       -- 3字节
    int_col INT,                   -- 4字节，常用
    bigint_col BIGINT,             -- 8字节
    boolean_col BOOLEAN            -- TINYINT(1)的别名
);

-- 浮点类型
CREATE TABLE float_types (
    float_col FLOAT,               -- 单精度浮点数
    double_col DOUBLE,             -- 双精度浮点数
    decimal_col DECIMAL(10,2)     -- 定点数，精确到小数点后2位
);

字符串类型

CREATE TABLE string_types (
    char_col CHAR(10),             -- 固定长度字符串
    varchar_col VARCHAR(255),      -- 可变长度字符串，常用
    text_col TEXT,                 -- 长文本，最多65535字符
    mediumtext_col MEDIUMTEXT,     -- 中等文本，最多16777215字符
    longtext_col LONGTEXT,         -- 长文本，最多4294967295字符
    enum_col ENUM('A', 'B', 'C'), -- 枚举类型
    set_col SET('A', 'B', 'C')     -- 集合类型
);

日期时间类型

CREATE TABLE datetime_types (
    date_col DATE,                 -- 日期 YYYY-MM-DD
    time_col TIME,                 -- 时间 HH:MM:SS
    datetime_col DATETIME,         -- 日期时间 YYYY-MM-DD HH:MM:SS
    timestamp_col TIMESTAMP,       -- 时间戳，范围较小
    year_col YEAR                  -- 年份 YYYY
);

-- 插入日期时间数据
INSERT INTO datetime_types VALUES
('2024-01-01', '12:30:45', '2024-01-01 12:30:45', NOW(), 2024);

数据操作语言(DML)

1. 插入数据

-- 单行插入
INSERT INTO users (username, email, password_hash, age, status)
VALUES ('john_doe', 'john@example.com', 'hashed_password', 25, 'active');

-- 多行插入
INSERT INTO users (username, email, age) VALUES
('jane_smith', 'jane@example.com', 23),
('bob_jones', 'bob@example.com', 30),
('alice_brown', 'alice@example.com', 28);

-- 从其他表插入数据
INSERT INTO user_profiles (user_id, bio)
SELECT id, CONCAT('Hello, I am ', username) FROM users WHERE age > 25;

-- 插入时忽略重复
INSERT IGNORE INTO users (username, email) VALUES ('john_doe', 'john2@example.com');

-- 插入时更新重复数据
INSERT INTO users (username, email)
VALUES ('john_doe', 'john3@example.com')
ON DUPLICATE KEY UPDATE email = VALUES(email);

2. 更新数据

-- 更新单个字段
UPDATE users SET status = 'active' WHERE id = 1;

-- 更新多个字段
UPDATE users
SET status = 'active', updated_at = NOW()
WHERE id = 1;

-- 基于条件的更新
UPDATE users SET age = age + 1 WHERE status = 'active';

-- 使用JOIN更新
UPDATE users u
JOIN user_profiles p ON u.id = p.user_id
SET u.status = 'premium'
WHERE p.subscription_level = 'gold';

-- 限制更新的行数
UPDATE users SET status = 'inactive' LIMIT 10;

3. 删除数据

-- 删除特定记录
DELETE FROM users WHERE id = 1;

-- 基于条件的删除
DELETE FROM users WHERE status = 'inactive' AND created_at < '2020-01-01';

-- 删除所有数据（保留表结构）
DELETE FROM users;
-- 或者
TRUNCATE TABLE users;

-- 使用JOIN删除
DELETE users FROM users
JOIN user_profiles ON users.id = user_profiles.user_id
WHERE user_profiles.bio IS NULL;

-- 限制删除的行数
DELETE FROM users WHERE status = 'suspended' LIMIT 5;

数据查询语言(DQL)

1. 基本查询

-- 查询所有字段
SELECT * FROM users;

-- 查询指定字段
SELECT id, username, email FROM users;

-- 使用别名
SELECT
    id AS user_id,
    username AS name,
    email AS email_address
FROM users;

-- 去重查询
SELECT DISTINCT status FROM users;

-- 条件查询
SELECT * FROM users WHERE age > 18;

-- 多条件查询
SELECT * FROM users
WHERE age >= 18 AND status = 'active';

-- 范围查询
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- 列表查询
SELECT * FROM users WHERE status IN ('active', 'pending');

-- 模糊查询
SELECT * FROM users WHERE username LIKE 'john%';
SELECT * FROM users WHERE email LIKE '%@gmail.com';

2. 排序和限制

-- 升序排序
SELECT * FROM users ORDER BY age ASC;

-- 降序排序
SELECT * FROM users ORDER BY created_at DESC;

-- 多字段排序
SELECT * FROM users ORDER BY status DESC, age ASC;

-- 限制结果数量
SELECT * FROM users LIMIT 10;

-- 分页查询
SELECT * FROM users LIMIT 10 OFFSET 20;
-- 或者
SELECT * FROM users LIMIT 20, 10;

3. 聚合函数

-- 统计记录数
SELECT COUNT(*) FROM users;
SELECT COUNT(id) FROM users WHERE status = 'active';

-- 计算平均值
SELECT AVG(age) FROM users;

-- 计算总和
SELECT SUM(age) FROM users;

-- 查找最大值和最小值
SELECT MAX(age), MIN(age) FROM users;

-- 分组统计
SELECT status, COUNT(*) as count
FROM users
GROUP BY status;

-- 分组后过滤
SELECT status, COUNT(*) as count
FROM users
GROUP BY status
HAVING COUNT(*) > 5;

4. 连接查询

-- 内连接
SELECT u.username, p.bio
FROM users u
INNER JOIN user_profiles p ON u.id = p.user_id;

-- 左连接
SELECT u.username, p.bio
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id;

-- 右连接
SELECT u.username, p.bio
FROM users u
RIGHT JOIN user_profiles p ON u.id = p.user_id;

-- 多表连接
SELECT u.username, p.bio, c.name as city_name
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
LEFT JOIN cities c ON p.city_id = c.id;

5. 子查询

-- WHERE子句中的子查询
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- FROM子句中的子查询
SELECT u.* FROM (
    SELECT * FROM users WHERE age > 18
) AS u WHERE u.status = 'active';

-- EXISTS子查询
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.total > 100
);

数据库设计原则

1. 设计用户管理系统

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    avatar_url VARCHAR(255),
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'inactive',
    email_verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 用户资料表
CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    bio TEXT,
    phone VARCHAR(20),
    address TEXT,
    city_id INT,
    birth_date DATE,
    gender ENUM('male', 'female', 'other'),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 城市表
CREATE TABLE cities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_country (country)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 用户角色表
CREATE TABLE user_roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    permissions JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 用户角色关联表
CREATE TABLE user_role_assignments (
    user_id INT,
    role_id INT,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_by INT,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES user_roles(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 插入初始数据
INSERT INTO user_roles (name, description, permissions) VALUES
('admin', '管理员', JSON_OBJECT('users', ['create', 'read', 'update', 'delete'], 'system', ['config', 'logs'])),
('moderator', '版主', JSON_OBJECT('content', ['create', 'read', 'update', 'delete'])),
('user', '普通用户', JSON_OBJECT('profile', ['read', 'update']));

INSERT INTO cities (name, country, state) VALUES
('北京', '中国', '北京市'),
('上海', '中国', '上海市'),
('广州', '中国', '广东省'),
('深圳', '中国', '广东省');

2. 索引优化

-- 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_status_created ON users(status, created_at);

-- 创建全文索引（用于文本搜索）
CREATE FULLTEXT INDEX idx_bio ON user_profiles(bio);

-- 查看表的索引
SHOW INDEX FROM users;

-- 分析查询性能
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

3. 视图创建

-- 创建用户详细信息视图
CREATE VIEW user_details AS
SELECT
    u.id,
    u.username,
    u.email,
    u.first_name,
    u.last_name,
    u.status,
    p.bio,
    p.phone,
    c.name as city_name,
    u.created_at,
    u.last_login
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
LEFT JOIN cities c ON p.city_id = c.id;

-- 使用视图
SELECT * FROM user_details WHERE status = 'active';

-- 更新视图（某些情况下）
UPDATE user_details SET bio = 'New bio' WHERE id = 1;

PHP连接MySQL

1. 使用MySQLi扩展

<?php
// 数据库配置
$host = 'localhost';
$username = 'root';
$password = 'your_password';
$database = 'myapp';

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

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

// 设置字符集
$conn->set_charset("utf8mb4");

// 执行查询
$sql = "SELECT id, username, email FROM users WHERE status = 'active'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 输出数据
    while($row = $result->fetch_assoc()) {
        echo "ID: " . $row["id"]. " - 用户名: " . $row["username"]. " - 邮箱: " . $row["email"]. "<br>";
    }
} else {
    echo "没有找到记录";
}

// 预处理语句示例
$stmt = $conn->prepare("INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $username, $email, $password_hash);

$username = "new_user";
$email = "newuser@example.com";
$password_hash = password_hash("password123", PASSWORD_DEFAULT);

$stmt->execute();
echo "新记录创建成功";

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

2. 使用PDO扩展（推荐）

<?php
// 数据库配置
$host = 'localhost';
$dbname = 'myapp';
$username = 'root';
$password = 'your_password';
$charset = 'utf8mb4';

// DSN (Data Source Name)
$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";

$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    // 创建PDO实例
    $pdo = new PDO($dsn, $username, $password, $options);

    // 简单查询
    $stmt = $pdo->query("SELECT id, username, email FROM users WHERE status = 'active'");

    while ($row = $stmt->fetch()) {
        echo "ID: " . $row['id'] . " - 用户名: " . $row['username'] . "<br>";
    }

    // 预处理语句
    $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ? AND status = ?");
    $stmt->execute(['john_doe', 'active']);
    $user = $stmt->fetch();

    // 插入数据
    $stmt = $pdo->prepare("INSERT INTO users (username, email, password_hash) VALUES (?, ?, ?)");
    $stmt->execute(['new_user', 'newuser@example.com', 'hashed_password']);

    // 获取最后插入的ID
    $lastId = $pdo->lastInsertId();
    echo "新用户ID: " . $lastId;

} catch (PDOException $e) {
    die("数据库错误: " . $e->getMessage());
}
?>

3. 数据库连接类

<?php
class Database {
    private $pdo;
    private $host;
    private $dbname;
    private $username;
    private $password;
    private $charset;

    public function __construct($host, $dbname, $username, $password, $charset = 'utf8mb4') {
        $this->host = $host;
        $this->dbname = $dbname;
        $this->username = $username;
        $this->password = $password;
        $this->charset = $charset;
    }

    public function connect() {
        if ($this->pdo === null) {
            $dsn = "mysql:host={$this->host};dbname={$this->dbname};charset={$this->charset}";

            $options = [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
                PDO::ATTR_PERSISTENT         => true, // 持久连接
            ];

            try {
                $this->pdo = new PDO($dsn, $this->username, $this->password, $options);
            } catch (PDOException $e) {
                throw new Exception("数据库连接失败: " . $e->getMessage());
            }
        }

        return $this->pdo;
    }

    public function query($sql, $params = []) {
        $stmt = $this->connect()->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

    public function fetch($sql, $params = []) {
        return $this->query($sql, $params)->fetch();
    }

    public function fetchAll($sql, $params = []) {
        return $this->query($sql, $params)->fetchAll();
    }

    public function insert($table, $data) {
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));

        $sql = "INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})";

        $this->query($sql, array_values($data));
        return $this->pdo->lastInsertId();
    }

    public function update($table, $data, $where, $whereParams = []) {
        $setClauses = [];
        $params = [];

        foreach ($data as $column => $value) {
            $setClauses[] = "{$column} = ?";
            $params[] = $value;
        }

        $sql = "UPDATE {$table} SET " . implode(', ', $setClauses) . " WHERE {$where}";

        $params = array_merge($params, $whereParams);

        $stmt = $this->query($sql, $params);
        return $stmt->rowCount();
    }

    public function delete($table, $where, $params = []) {
        $sql = "DELETE FROM {$table} WHERE {$where}";
        $stmt = $this->query($sql, $params);
        return $stmt->rowCount();
    }

    public function beginTransaction() {
        return $this->connect()->beginTransaction();
    }

    public function commit() {
        return $this->pdo->commit();
    }

    public function rollback() {
        return $this->pdo->rollBack();
    }
}

// 使用示例
try {
    $db = new Database('localhost', 'myapp', 'root', 'password');

    // 查询用户
    $users = $db->fetchAll("SELECT * FROM users WHERE status = ?", ['active']);

    // 插入用户
    $userId = $db->insert('users', [
        'username' => 'test_user',
        'email' => 'test@example.com',
        'password_hash' => password_hash('password', PASSWORD_DEFAULT)
    ]);

    // 更新用户
    $db->update('users',
        ['status' => 'active'],
        'id = ?',
        [$userId]
    );

    // 删除用户
    $db->delete('users', 'id = ?', [$userId]);

} catch (Exception $e) {
    echo "错误: " . $e->getMessage();
}
?>

实践项目：简单的博客系统

数据库设计

-- 创建博客数据库
CREATE DATABASE blog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE blog;

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(100),
    avatar_url VARCHAR(255),
    bio TEXT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 分类表
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    parent_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

-- 文章表
CREATE TABLE posts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    excerpt TEXT,
    author_id INT NOT NULL,
    category_id INT,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    comment_count INT DEFAULT 0,
    view_count INT DEFAULT 0,
    featured BOOLEAN DEFAULT FALSE,
    published_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_status (status),
    INDEX idx_published (published_at),
    INDEX idx_author (author_id),
    INDEX idx_category (category_id)
);

-- 评论表
CREATE TABLE comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    post_id INT NOT NULL,
    author_name VARCHAR(100) NOT NULL,
    author_email VARCHAR(100) NOT NULL,
    author_url VARCHAR(255),
    content TEXT NOT NULL,
    parent_id INT,
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE
);

-- 插入示例数据
INSERT INTO users (username, email, password_hash, display_name) VALUES
('admin', 'admin@blog.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', '管理员'),
('john', 'john@blog.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'John Doe');

INSERT INTO categories (name, slug, description) VALUES
('技术', 'tech', '技术相关文章'),
('生活', 'life', '生活随笔'),
('教程', 'tutorial', '教程类文章');

INSERT INTO posts (title, slug, content, author_id, category_id, status, published_at) VALUES
('欢迎来到我的博客', 'welcome-to-my-blog', '这是第一篇文章的内容...', 1, 2, 'published', NOW()),
('PHP基础教程', 'php-basic-tutorial', '今天我们来学习PHP基础...', 1, 3, 'published', NOW());

PHP操作示例

<?php
class Blog {
    private $db;

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

    // 获取发布的文章
    public function getPublishedPosts($limit = 10, $offset = 0) {
        $sql = "SELECT p.*, u.display_name as author_name, c.name as category_name
                FROM posts p
                LEFT JOIN users u ON p.author_id = u.id
                LEFT JOIN categories c ON p.category_id = c.id
                WHERE p.status = 'published'
                ORDER BY p.published_at DESC
                LIMIT ? OFFSET ?";

        return $this->db->fetchAll($sql, [$limit, $offset]);
    }

    // 获取文章详情
    public function getPost($slug) {
        $sql = "SELECT p.*, u.display_name as author_name, c.name as category_name
                FROM posts p
                LEFT JOIN users u ON p.author_id = u.id
                LEFT JOIN categories c ON p.category_id = c.id
                WHERE p.slug = ? AND p.status = 'published'";

        $post = $this->db->fetch($sql, [$slug]);

        if ($post) {
            // 增加浏览次数
            $this->db->query("UPDATE posts SET view_count = view_count + 1 WHERE id = ?", [$post['id']]);
        }

        return $post;
    }

    // 获取文章评论
    public function getPostComments($postId, $approvedOnly = true) {
        $sql = "SELECT * FROM comments
                WHERE post_id = ?" . ($approvedOnly ? " AND status = 'approved'" : "") . "
                ORDER BY created_at ASC";

        return $this->db->fetchAll($sql, [$postId]);
    }

    // 添加评论
    public function addComment($postId, $authorName, $authorEmail, $content, $parent_id = null) {
        return $this->db->insert('comments', [
            'post_id' => $postId,
            'author_name' => $authorName,
            'author_email' => $authorEmail,
            'content' => $content,
            'parent_id' => $parent_id,
            'ip_address' => $_SERVER['REMOTE_ADDR'] ?? '',
            'user_agent' => $_SERVER['HTTP_USER_AGENT'] ?? '',
            'status' => 'pending' // 需要审核
        ]);
    }

    // 获取分类列表
    public function getCategories() {
        return $this->db->fetchAll("SELECT * FROM categories ORDER BY name ASC");
    }

    // 获取分类下的文章
    public function getPostsByCategory($categorySlug, $limit = 10) {
        $sql = "SELECT p.*, u.display_name as author_name
                FROM posts p
                LEFT JOIN users u ON p.author_id = u.id
                LEFT JOIN categories c ON p.category_id = c.id
                WHERE c.slug = ? AND p.status = 'published'
                ORDER BY p.published_at DESC
                LIMIT ?";

        return $this->db->fetchAll($sql, [$categorySlug, $limit]);
    }

    // 用户注册
    public function registerUser($username, $email, $password, $displayName) {
        // 检查用户名和邮箱是否已存在
        $existing = $this->db->fetch(
            "SELECT id FROM users WHERE username = ? OR email = ?",
            [$username, $email]
        );

        if ($existing) {
            throw new Exception("用户名或邮箱已存在");
        }

        return $this->db->insert('users', [
            'username' => $username,
            'email' => $email,
            'password_hash' => password_hash($password, PASSWORD_DEFAULT),
            'display_name' => $displayName
        ]);
    }

    // 用户登录验证
    public function login($username, $password) {
        $user = $this->db->fetch(
            "SELECT id, username, email, password_hash, display_name, status
             FROM users WHERE username = ? OR email = ? AND status = 'active'",
            [$username, $username]
        );

        if ($user && password_verify($password, $user['password_hash'])) {
            return $user;
        }

        return false;
    }
}

// 使用示例
try {
    $database = new Database('localhost', 'blog', 'root', 'password');
    $blog = new Blog($database);

    // 获取最新文章
    $posts = $blog->getPublishedPosts(5);

    echo "<h2>最新文章</h2>";
    foreach ($posts as $post) {
        echo "<h3><a href='post.php?slug={$post['slug']}'>" . htmlspecialchars($post['title']) . "</a></h3>";
        echo "<p>作者: " . htmlspecialchars($post['author_name']) .
             " | 分类: " . htmlspecialchars($post['category_name']) .
             " | 浏览: " . $post['view_count'] . "</p>";
        echo "<p>" . htmlspecialchars(substr($post['content'], 0, 200)) . "...</p>";
        echo "<hr>";
    }

} catch (Exception $e) {
    echo "错误: " . $e->getMessage();
}
?>

总结

MySQL数据库基础是Web开发的重要技能。通过本章的学习，你应该掌握：

关键知识点：

1. 数据库基本概念：理解关系模型、表、记录、字段等概念
2. SQL语言：熟练使用DDL、DML、DQL进行数据操作
3. 数据库设计：遵循范式化原则，设计合理的表结构
4. 索引优化：了解索引的作用和使用场景
5. PHP数据库操作：使用MySQLi和PDO扩展连接和操作数据库

最佳实践：

• 使用预处理语句防止SQL注入
• 合理设计索引提高查询性能
• 使用外键约束保证数据完整性
• 定期备份数据库
• 监控和优化慢查询

后续学习方向：

• 深入学习数据库优化和索引调优
• 了解存储过程和触发器
• 学习数据库集群和分库分表
• 掌握数据库备份和恢复
• 学习NoSQL数据库作为补充

通过实践本章的博客系统项目，你可以将理论知识应用到实际开发中，为构建复杂的Web应用奠定坚实的基础。