mysql-quick-guide

MySQL - 快速指南

MySQL - 简介

什么是数据库？

数据库是一种专门的应用程序，旨在高效地存储、管理和检索大量数据。虽然简单的数据可以存储在文件或内存结构中，但数据库提供了强大的 API，用于创建、访问、管理、搜索和复制数据，并具有卓越的性能、可靠性和安全性。

如今，关系型数据库管理系统 (RDBMS) 是管理结构化数据的标准。在 RDBMS 中，数据以表的形式组织。这些表可以使用键相互链接或“关联 (related)”，从而实现复杂的查询和数据完整性。

现代关系型数据库管理系统 (RDBMS) 是一种能够：

• 让您能够使用表、列和索引实现数据库。
• 使用约束 (constraints) 保证不同表行之间的引用完整性 (referential integrity)。
• 自动更新索引以保持查询性能。
• 解释标准查询语言 (SQL)，以查询和操作来自不同表的数据。

RDBMS 术语

在深入了解 MySQL 之前，我们先回顾一些关键的数据库术语：

• 数据库 (Database)：包含相关数据的结构化表集合。
• 表 (Table)：相关数据条目的集合，以行和列的形式组织，非常类似于电子表格。
• 列 (Column)：表中垂直的实体，包含一种特定类型的所有信息（例如，user_email 列）。
• 行 (Row)：表中水平的实体，代表一个单条记录或条目（例如，某个特定用户的所有数据）。也称为元组 (tuple)。
• 冗余 (Redundancy)：在多个地方存储相同的数据。通常通过范式化 (normalization) 过程来避免，但有时为了性能提升会在反范式化 (denormalization) 中有意使用。
• 主键 (Primary Key)：一种约束，用于唯一标识表中的每条记录。主键列必须包含唯一值，且不能包含 NULL 值。
• 外键 (Foreign Key)：用于连接两个表的键。它是某个表中的一个字段（或字段集合），引用另一个表中的主键 (PRIMARY KEY)。
• 复合主键 (Composite Key)：由多个列组成的主键，当单个列不足以唯一标识一条记录时使用。
• 索引 (Index)：一种数据结构，它以增加写入和存储空间为代价，提高数据库表上的数据检索操作速度。它就像书后的索引。
• 引用完整性 (Referential Integrity)：数据的一种属性，表明其所有引用都是有效的。在 RDBMS 的上下文中，它确保外键值始终指向被引用表中存在的行。

为什么选择 MySQL？

MySQL 是一款快速、可靠且易于使用的 RDBMS，这使其成为各种规模的开发者和企业的热门选择。它最初由瑞典公司 MySQL AB 创建，现在由 Oracle Corporation 开发和维护。其受欢迎程度源于以下几个关键优势：

• 开源 (Open Source)：MySQL 社区版在 GPL 许可下免费使用，这让所有人都可以访问。
• 强大且可伸缩 (Powerful and Scalable)：它处理了最昂贵和最强大数据库包的大部分功能。它可以管理拥有数十亿行的大型数据库。
• 标准 SQL (Standard SQL)：它使用标准且知名的 SQL 数据语言，使开发者易于学习和使用。
• 跨平台和语言无关 (Cross-Platform and Language-Agnostic)：MySQL 几乎可以在所有平台上运行，包括 Linux、macOS 和 Windows。它为所有主流编程语言（如 PHP、Python、Java、C++、Go 等）提供了强大的连接器。
• 快速性能 (Fast Performance)：它在架构上以速度为目标，即使处理大型数据集也能表现出色，尤其适用于读密集型 (read-heavy) 工作负载。
• 活跃的生态系统 (Vibrant Ecosystem)：MySQL 得到了云服务提供商（AWS、Google Cloud、Azure）、托管公司以及大量第三方工具的广泛支持。MariaDB 和 Percona Server 等分支 (Forks) 也促成了一个健康且具有竞争力的生态系统。
• 安全可靠 (Secure and Reliable)：它包含了强大的安全和数据完整性功能，包括对默认存储引擎 InnoDB 的事务支持。

开始之前

本教程专为初学者设计，但假定您对使用命令行终端 (command-line terminal) 有基本的了解。虽然 SQL 概念是通用的，但我们的代码示例将侧重于使用 PDO 扩展进行数据库交互的现代化 PHP 环境。

熟悉 PHP 会有所帮助，但并非理解 SQL 概念的严格要求。

MySQL - 安装

现代 MySQL 安装方法非常简单。我们将涵盖最常见的平台。

使用软件包管理器安装 MySQL (Linux)

在 Linux 上安装 MySQL 的推荐方法是使用您发行版的软件包管理器。

在 Debian/Ubuntu 上：

sudo apt update
sudo apt install mysql-server

在 RHEL/CentOS/Fedora 上：

sudo dnf install mysql-server

安装后，强烈建议运行附带的安全脚本：

sudo mysql_secure_installation

此脚本将指导您设置 root 密码、删除匿名用户以及其他重要的安全加固步骤。

在 Windows 上安装 MySQL

对于 Windows，官方的 MySQL 安装程序 (MySQL Installer) 是最佳方法。它是一个基于向导的安装程序，捆绑了 MySQL 服务器、MySQL Workbench（图形用户界面客户端 (GUI client)）等工具以及各种编程语言的连接器。

1. 从官方 MySQL 下载 页面下载 MySQL 安装程序。
2. 运行安装程序，选择“开发者默认 (Developer Default)”设置类型，这是一个很好的起点。
3. 按照屏幕上的说明进行操作。安装程序将引导您完成配置，包括设置 root 密码。
4. 确保安装程序将 MySQL 的 bin 目录添加到您系统的 PATH 环境变量中，以便您可以从任何终端窗口运行 MySQL 命令。

使用 Docker 安装 MySQL（跨平台）

使用 Docker 是在隔离容器中运行 MySQL 的一种出色且现代化的方法，这非常适合开发，而不会影响您的主机系统。

首先，请确保您已安装 Docker。然后，运行此命令拉取最新的 MySQL 镜像并启动一个容器：

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my_secret_password -d -p 3306:3306 mysql:latest

• --name some-mysql：为您的容器指定一个易于记忆的名称。
• -e MYSQL_ROOT_PASSWORD=...：设置数据库的 root 密码。
• -d：以分离模式（后台）运行容器。
• -p 3306:3306：将主机上的 3306 端口映射到容器内的 3306 端口，允许您连接到它。

验证 MySQL 安装

安装完成后，您可以验证服务器是否正在运行并连接到它。

1. 检查服务器状态

使用 mysqladmin 工具检查服务器版本和状态。

mysqladmin --version -u root -p

它会提示您输入安装期间设置的 root 密码，并应产生类似于以下内容的输出：

mysqladmin  Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)

如果您收到错误，您的安装可能存在问题，或者服务器可能没有运行。

2. 使用 MySQL 客户端连接

使用 mysql 命令行客户端连接到您的服务器。

mysql -u root -p

输入密码后，您应该会看到 mysql> 提示符。现在您可以运行 SQL 命令了。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

要退出客户端，请输入 exit 或 \q 并按 Enter。

MySQL - 管理

启动和停止 MySQL 服务器

管理 MySQL 服务的方法取决于您的操作系统。

Linux (使用 systemd)

大多数现代 Linux 发行版都使用 systemd。

# 启动服务器
sudo systemctl start mysqld

# 停止服务器
sudo systemctl stop mysqld

# 重启服务器
sudo systemctl restart mysqld

# 检查服务器状态
sudo systemctl status mysqld

注意：在某些系统（如 Debian/Ubuntu）上，服务名称可能是 mysql 而不是 mysqld。

Windows

在 Windows 上，MySQL 通常作为服务运行。您可以从“服务”应用程序 (services.msc) 中管理它。在列表中找到“MySQL”，右键单击它，然后您可以启动、停止或重新启动它。

现代用户账户管理

最佳实践：切勿直接修改 mysql.user 表。始终使用 CREATE USER、GRANT、REVOKE 和 DROP USER 等 SQL 语句来管理用户及其权限。这样做更安全、更可靠。

创建新用户

使用 CREATE USER 语句。用户通过用户名和可连接的主机来标识。

CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'a_very_secure_password';

• 'webapp' 是用户名。
• 'localhost' 意味着此用户只能从运行 MySQL 服务器的同一台机器连接。使用 '%' 允许从任何主机连接（请谨慎使用）。
• IDENTIFIED BY ... 设置用户的密码。MySQL 会自动处理哈希。

授予权限

新用户默认没有权限。使用 GRANT 语句分配权限。最佳实践是仅授予必要的权限（最小权限原则 (Principle of Least Privilege)）。

-- 授予对 'TUTORIALS' 数据库中所有表的基本 CRUD（创建、读取、更新、删除）权限
GRANT SELECT, INSERT, UPDATE, DELETE ON TUTORIALS.* TO 'webapp'@'localhost';

-- 应用权限更改
FLUSH PRIVILEGES;

FLUSH PRIVILEGES; 命令重新加载授权表，使更改立即生效。对于 GRANT 语句通常不是必需的，但了解它是一个好习惯。

常用管理命令

以下是您将经常使用的一些重要 MySQL 命令：

• USE database_name;：选择要操作的数据库。所有后续查询都将针对此数据库运行。
• SHOW DATABASES;：列出当前用户可访问的所有数据库。
• SHOW TABLES;：显示当前选定数据库中的所有表。
• SHOW COLUMNS FROM table_name;（或 DESCRIBE table_name;）：显示表的列、数据类型和其他信息。
• SHOW INDEX FROM table_name;：显示表上的所有索引，包括主键 (PRIMARY KEY)。
• SHOW CREATE TABLE table_name;：显示重新创建表所需的 CREATE TABLE 语句，包括其结构和索引。

MySQL - 使用 PHP 连接（使用 PDO）

虽然 MySQL 可以与许多编程语言配合使用，但 PHP 仍然是 Web 开发的热门选择。我们将在所有示例中使用 PHP 数据对象 (PDO)。PDO 为在 PHP 中访问数据库提供了统一、现代化且安全的接口。

为什么选择 PDO？ 旧的 mysql_* 函数已从 PHP 中移除，因为它们缺乏对现代数据库功能的支持，并且容易受到 SQL 注入等安全漏洞的影响。PDO 是现代标准，因为：

• 安全性：它支持预处理语句 (prepared statements)，这是防止 SQL 注入的最佳防御措施。
• 一致性：它为与任何支持的数据库（MySQL、PostgreSQL、SQLite 等）交互提供了一致的函数集。
• 错误处理：它提供了健壮的基于异常的错误处理。
• 现代特性：它支持事务 (transactions)、将数据提取到对象中以及其他高级功能。

MySQL - 连接

从命令行连接

如安装部分所示，您可以使用 mysql 客户端从命令提示符连接到 MySQL：

# 以 root 用户连接（将提示输入密码）
mysql -u root -p

# 以特定用户连接到特定主机
mysql -u webapp -p -h database.server.com

连接后，您将看到 mysql> 提示符。您可以随时键入 exit 断开连接。

mysql> exit
Bye

使用现代 PHP 脚本连接（PDO）

要使用 PDO 连接到 MySQL，您需要创建一个新的 PDO 对象。其构造函数需要数据源名称 (DSN)、用户名和密码。

最佳实践：始终将连接逻辑包装在 try...catch 块中，以优雅地处理潜在的连接错误。

示例：完整连接脚本

创建一个名为 db_connect.php 的文件。

<?php

$host = '127.0.0.1'; // 或 'localhost'
$db   = 'TUTORIALS';
$user = 'webapp';
$pass = 'a_very_secure_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";

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

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected to the $db database successfully!";
} catch (\PDOException $e) {
    // 在实际应用中，您会记录此错误并显示通用消息。
    // 切勿向公众暴露详细的错误消息。
    error_log($e->getMessage());
    die("Database connection failed. Please try again later.");
}

// 连接现在存储在 $pdo 变量中。
// 当脚本结束时，连接会自动关闭。

?>

脚本的关键部分：

• $dsn：数据源名称 (DSN) 字符串。它告诉 PDO 使用哪个驱动程序 (mysql)、主机、数据库名称和字符集。
• $options：PDO 连接的配置选项数组。PDO::ERRMODE_EXCEPTION 至关重要，因为它使 PDO 在出错时抛出异常，我们可以 catch 这些异常。

MySQL - 创建数据库

使用 mysqladmin

您需要管理权限才能创建数据库。从命令行来看，mysqladmin 是一种快速完成此操作的方法。

# 这将提示输入 root 密码
mysqladmin -u root -p create TUTORIALS

使用 SQL 语句

在 mysql> 客户端内部，您可以使用 CREATE DATABASE SQL 命令。

CREATE DATABASE TUTORIALS CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

最佳实践：创建数据库时务必指定字符集和排序规则 (collation)，以确保正确处理国际字符。utf8mb4 是推荐标准。

使用 PHP 脚本（PDO）

要执行不返回数据的简单命令，例如 CREATE DATABASE，您可以使用 PDO::exec() 方法。请注意，您必须在 DSN 中 不 指定数据库名称来连接 MySQL，因为该数据库尚不存在。

<?php

$host = '127.0.0.1';
$user = 'root'; // 需要创建权限
$pass = 'root_password';
$charset = 'utf8mb4';

// 不带数据库名称的 DSN
$dsn = "mysql:host=$host;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];

$dbNameToCreate = 'TUTORIALS';

try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    // 使用反引号转义数据库名称，以防它是保留关键字
    $sql = "CREATE DATABASE `$dbNameToCreate` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";

    $pdo->exec($sql);

    echo "Database `$dbNameToCreate` created successfully!";

} catch (\PDOException $e) {
    error_log($e->getMessage());
    die("Could not create database: " . $e->getMessage());
}

?>

MySQL - 删除数据库

警告：删除数据库是不可逆的操作。其中所有表和数据将被永久删除。在运行此命令之前务必确定。

使用 mysqladmin

从命令行，您可以使用 mysqladmin drop。

mysqladmin -u root -p drop TUTORIALS

# 它会要求确认：
# 删除数据库可能是一个非常糟糕的操作。
# 数据库中存储的任何数据都将被销毁。
#
# 您真的要删除 'TUTORIALS' 数据库吗 [y/N] y
# 数据库 "TUTORIALS" 已删除

使用 PHP 脚本（PDO）

与创建数据库类似，您可以使用 PDO::exec() 运行 DROP DATABASE 命令。

<?php

// ...（使用与创建示例相同的连接设置）...

$dbNameToDrop = 'TUTORIALS';

try {
    $pdo = new PDO($dsn, $user, $pass, $options);

    $sql = "DROP DATABASE `$dbNameToDrop`";
    $pdo->exec($sql);

    echo "Database `$dbNameToDrop` deleted successfully!";

} catch (\PDOException $e) {
    error_log($e->getMessage());
    die("Could not delete database: " . $e->getMessage());
}

?>

MySQL - 选择数据库

从命令提示符选择数据库

连接到 MySQL 服务器后，使用 USE 命令选择要操作的数据库。

mysql> USE TUTORIALS;
Database changed

所有后续查询现在都将在 TUTORIALS 数据库上执行。在 Linux 系统上，MySQL 中的数据库、表和列名通常区分大小写，因此请使用它们的精确名称。

使用 PHP 选择数据库（PDO）

使用 PDO，最常见和推荐的选择数据库方式是在初始连接时在 DSN 中指定它。这确保您的整个脚本会话都在正确的数据库上运行。

// DSN 包含 'dbname=TUTORIALS'
$dsn = "mysql:host=127.0.0.1;dbname=TUTORIALS;charset=utf8mb4";
$pdo = new PDO($dsn, $user, $pass, $options);

如果您需要在连接后切换到不同的数据库，仍然可以执行 USE 命令。

// 假设 $pdo 是一个现有连接
$pdo->exec('USE another_database');
echo "Switched to another_database.";

MySQL - 数据类型

为列选择正确的数据类型对于数据库优化、数据完整性和性能至关重要。您应该始终使用能够可靠地存储该字段所有可能值的最小数据类型。

MySQL 数据类型大致分为数字型、日期和时间型以及字符串型。

数字数据类型

MySQL 支持所有标准的 SQL 数字数据类型。

• TINYINT：一个非常小的整型。有符号范围：-128 到 127。无符号范围：0 到 255。非常适合标志或小型计数器。
• SMALLINT：一个小型整型。有符号范围：-32,768 到 32,767。无符号范围：0 到 65,535。
• INT（或 INTEGER）：一个标准整型。有符号范围：-2,147,483,648 到 2,147,483,647。无符号范围：0 到 4,294,967,295。是整型 ID 最常见的选择。
• BIGINT：一个大型整型。当 INT 不够大时使用。有符号范围约为 -9.2 京到 9.2 京。
• DECIMAL(M,D)（或 NUMERIC）：一个定点数，非常适合对精度要求严格的财务计算。M 是总位数，D 是小数点后的位数。例如，DECIMAL(10, 2) 可以存储 12345678.99 这样的数字。
• FLOAT：单精度浮点数。可能存在舍入误差，不适合精确的货币值。
• DOUBLE：双精度浮点数。比 FLOAT 精度更高，但仍可能存在舍入误差。
• BOOL（或 BOOLEAN）：TINYINT(1) 的同义词。零值被认为是假，非零值被认为是真。

日期和时间类型

• DATE：以 ‘YYYY-MM-DD’ 格式存储日期值。
• TIME：以 ‘HH:MM:SS’ 格式存储时间值。
• DATETIME：以 ‘YYYY-MM-DD HH:MM:SS’ 格式存储日期和时间组合。范围从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。
• TIMESTAMP：类似于 DATETIME，但范围较小（UTC ‘1970-01-01 00:00:01’ 到 UTC ‘2038-01-19 03:14:07’）。它具有特殊属性：如果 TIMESTAMP 列具有 DEFAULT CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP 属性，其值会在插入或更新时自动设置为当前时间戳。它还以 UTC 存储值，并在与当前会话时区之间进行转换。
• YEAR：以 4 位数字格式存储年份。

字符串类型

• CHAR(M)：固定长度字符串。如果您定义 CHAR(10) 并存储 ‘hi’，它将用 8 个空格填充。用于邮政编码或国家代码等固定长度数据。
• VARCHAR(M)：变长字符串。M 代表最大长度（最多 65,535）。用于大多数文本数据，如姓名或标题。
• TEXT：用于长文本。最多可容纳 65,535 个字符。无需指定长度。
• MEDIUMTEXT：最多可容纳 16,777,215 个字符。
• LONGTEXT：最多可容纳 4,294,967,295 个字符。
• BLOB：“二进制大对象 (Binary Large Objects)”。用于直接在数据库中存储图像或文件等二进制数据（尽管这通常不是推荐的方法）。它们有对应的 TINYBLOB、MEDIUMBLOB 和 LONGBLOB 类型。
• ENUM：枚举。一个字符串对象，只能从预定义的值列表中选择一个值。例如，ENUM('pending', 'active', 'suspended')。
• JSON：原生的 JSON 数据类型（自 MySQL 5.7 起）。存储 JSON 文档并允许使用内置函数高效访问其中的数据。非常适合存储半结构化数据。

MySQL - 创建表

CREATE TABLE 语句用于定义一个新表。您必须指定表名、列名列表、它们的数据类型以及任何约束 (constraints)。

语法

CREATE TABLE table_name (
    column1_name column1_type [CONSTRAINTS],
    column2_name column2_type [CONSTRAINTS],
    ...
    PRIMARY KEY (column_name)
) ENGINE=InnoDB;

最佳实践：始终指定存储引擎。InnoDB 是现代默认引擎，在几乎所有情况下都应使用，因为它支持事务、外键和行级锁定。

示例：tutorials_tbl

让我们在 TUTORIALS 数据库中创建一个表。

CREATE TABLE tutorials_tbl (
    tutorial_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tutorial_title VARCHAR(100) NOT NULL,
    tutorial_author VARCHAR(40) NOT NULL,
    submission_date DATE,
    PRIMARY KEY (tutorial_id)
) ENGINE=InnoDB;

此处使用的关键属性：

• NOT NULL：一个约束，确保列不能有 NULL 值。
• AUTO_INCREMENT：插入新记录时自动生成唯一数字。通常用于主键。
• PRIMARY KEY：定义唯一标识每行的列。
• UNSIGNED：一个数值类型修饰符，禁止负值，从而有效地将正数范围加倍。适用于 ID。

使用 PHP 脚本创建表（PDO）

使用 PDO::exec() 方法执行 CREATE TABLE 语句。

<?php

// 假设您有一个来自 db_connect.php 的 $pdo 连接对象
require 'db_connect.php';

try {
    $sql = "CREATE TABLE tutorials_tbl (\n        tutorial_id INT UNSIGNED NOT NULL AUTO_INCREMENT,\n        tutorial_title VARCHAR(100) NOT NULL,\n        tutorial_author VARCHAR(40) NOT NULL,\n        submission_date DATE,\n        PRIMARY KEY (tutorial_id)\n    ) ENGINE=InnoDB;";

    $pdo->exec($sql);

    echo "Table 'tutorials_tbl' created successfully!";

} catch (PDOException $e) {
    // 这里的常见错误是“表已存在”
    die("Could not create table: " . $e->getMessage());
}

?>

MySQL - 删除表

警告：删除表是不可逆的操作，会永久删除表的结构及其所有数据。

语法

DROP TABLE [IF EXISTS] table_name;

最佳实践：使用 IF EXISTS 可以防止在表不存在时抛出错误，这在脚本中很有用。

从命令提示符删除表

mysql> USE TUTORIALS;
Database changed

mysql> DROP TABLE IF EXISTS tutorials_tbl;
Query OK, 0 rows affected (0.01 sec)

使用 PHP 脚本删除表（PDO）

<?php

require 'db_connect.php';

try {
    $sql = "DROP TABLE IF EXISTS tutorials_tbl";
    $pdo->exec($sql);
    echo "Table 'tutorials_tbl' deleted successfully!";
} catch (PDOException $e) {
    die("Could not delete table: " . $e->getMessage());
}

?>

MySQL - 插入查询

INSERT INTO 语句用于向表中添加新行数据。

语法

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

您也可以一次插入多行：

INSERT INTO table_name (column1, column2, column3)
VALUES
    (value1a, value2a, value3a),
    (value1b, value2b, value3b);

从命令提示符插入数据

mysql> USE TUTORIALS;

mysql> INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date)
    -> VALUES
    -> ('Learn Modern PHP', 'John Doe', '2023-10-27'),
    -> ('Mastering MySQL 8', 'Jane Smith', NOW());
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

请注意，我们没有为 tutorial_id 提供值，因为它是一个 AUTO_INCREMENT 列。MySQL 会自动分配值。NOW() 是一个 MySQL 函数，它返回当前日期和时间。

使用 PHP 脚本安全地插入数据（预处理语句）

安全警告：切勿通过将用户输入直接拼接成 SQL 字符串来插入数据。这会造成一个严重的安全漏洞，称为 SQL 注入 (SQL Injection)。始终使用带占位符 (placeholders) 的预处理语句 (prepared statements)。

预处理语句是 SQL 查询的模板。您首先将模板发送到数据库，然后单独发送数据。数据库引擎将数据视为字面值，而不是 SQL 命令的一部分，从而使注入成为不可能。

示例：从表单插入数据

首先，一个 HTML 表单 (add_tutorial.html)：

<html>
<head><title>Add New Tutorial</title></head>
<body>
    <h2>Add New Record in MySQL Database</h2>
    <form action="insert.php" method="post">
        <p>
            <label for="title">Tutorial Title:</label>
            <input type="text" name="tutorial_title" id="title" required>
        </p>
        <p>
            <label for="author">Tutorial Author:</label>
            <input type="text" name="tutorial_author" id="author" required>
        </p>
        <p>
            <label for="date">Submission Date:</label>
            <input type="date" name="submission_date" id="date" required>
        </p>
        <input type="submit" name="add" value="Add Tutorial">
    </form>
</body>
</html>

现在，处理表单的 PHP 脚本 (insert.php)：

<?php

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    require 'db_connect.php'; // 您的 PDO 连接

    try {
        $sql = "INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES (?, ?, ?)";

        $stmt = $pdo->prepare($sql);

        // 绑定 POST 请求中的参数并执行
        $stmt->execute([
            $_POST['tutorial_title'],
            $_POST['tutorial_author'],
            $_POST['submission_date']
        ]);

        // 获取插入记录的 ID
        $lastId = $pdo->lastInsertId();

        echo "Data entered successfully. New record has ID: $lastId";

    } catch (PDOException $e) {
        die("Could not enter data: " . $e->getMessage());
    }
}
?>

这里，? 是匿名占位符。execute() 方法接受一个值数组，这些值按顺序对应于这些占位符。这是处理数据库插入的安全、现代方式。

MySQL - 查询

SELECT 语句用于从一个或多个表中获取数据。

语法

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC|DESC]]
[LIMIT count]
[OFFSET start];

• 您可以指定要返回的列，或使用 * 选择所有列。
• WHERE 子句根据条件过滤记录。
• ORDER BY 子句对结果集进行排序。
• LIMIT 子句限制返回的行数。
• OFFSET 子句指定从何处开始返回行（对于**分页 (pagination)**很有用）。

从命令提示符获取数据

mysql> SELECT tutorial_id, tutorial_title, tutorial_author FROM tutorials_tbl;
+-------------+--------------------+-----------------+
| tutorial_id | tutorial_title     | tutorial_author |
+-------------+--------------------+-----------------+
|           1 | Learn Modern PHP   | John Doe        |
|           2 | Mastering MySQL 8  | Jane Smith      |
+-------------+--------------------+-----------------+
2 rows in set (0.00 sec)

使用 PHP 脚本获取数据（PDO）

要使用 PDO 获取数据，您需要 prepare 您的 SELECT 语句，execute 它，然后使用 fetchAll() 等获取方法将结果作为数组获取。

示例：显示所有教程

<?php

require 'db_connect.php';

try {
    $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl ORDER BY tutorial_id DESC';

    $stmt = $pdo->query($sql); // 对于没有用户输入的简单查询，query() 即可。

    // 使用 PDO::FETCH_ASSOC 返回一个关联数组（列名 => 值）
    $tutorials = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if ($tutorials) {
        echo '<ul>';
        foreach ($tutorials as $row) {
            echo '<li>';
            echo htmlspecialchars($row['tutorial_title'], ENT_QUOTES, 'UTF-8');
            echo ' by ';
            echo htmlspecialchars($row['tutorial_author'], ENT_QUOTES, 'UTF-8');
            echo ' (ID: ' . $row['tutorial_id'] . ')';
            echo '</li>';
        }
        echo '</ul>';
    } else {
        echo '<p>No tutorials found.</p>';
    }

} catch (PDOException $e) {
    die('Could not get data: ' . $e->getMessage());
}

?>

安全提示：在网页上显示用户生成的数据时，始终使用 htmlspecialchars() 对输出进行转义，以防止跨站脚本攻击 (XSS)。

获取单行

如果您只期望一个结果，请使用 fetch() 方法而不是 fetchAll()。

$stmt = $pdo->prepare('SELECT * FROM tutorials_tbl WHERE tutorial_id = ?');
$stmt->execute([1]);
$tutorial = $stmt->fetch(); // 返回单行，如果未找到则返回 false

MySQL - WHERE 子句

WHERE 子句用于过滤记录。它用于只提取满足指定条件的记录。

运算符	描述	示例
=	等于	... WHERE id = 10
<>或!=	不等于	... WHERE status != 'pending'
>	大于	... WHERE price > 99.99
<	小于	... WHERE stock < 10
>=	大于或等于	... WHERE score >= 80
<=	小于或等于	... WHERE attempts <= 3
BETWEEN	在（包含）范围内	... WHERE date BETWEEN '2023-01-01' AND '2023-01-31'
IN	匹配列表中的任何值	... WHERE country IN ('USA', 'Canada', 'Mexico')
LIKE	搜索模式（参见 LIKE 子句章节）	... WHERE name LIKE 'J%'

使用 PHP 脚本和 WHERE 获取数据

当 WHERE 子句条件依赖于用户输入时，您必须使用预处理语句以确保安全。

示例：按特定作者查找教程

<?php

require 'db_connect.php';

$authorToFind = 'Jane Smith'; // 在实际应用中，这会来自用户输入，例如 $_GET['author']

try {
    $sql = 'SELECT tutorial_id, tutorial_title FROM tutorials_tbl WHERE tutorial_author = ?';

    $stmt = $pdo->prepare($sql);
    $stmt->execute([$authorToFind]);

    $results = $stmt->fetchAll();

    if ($results) {
        echo "<h3>Tutorials by $authorToFind:</h3><ul>";
        foreach ($results as $row) {
            echo "<li>" . htmlspecialchars($row['tutorial_title']) . "</li>";
        }
        echo "</ul>";
    } else {
        echo "<p>No tutorials found for author: $authorToFind</p>";
    }

} catch (PDOException $e) {
    die('Query failed: ' . $e->getMessage());
}

?>

通过为作者姓名使用占位符 ?，我们保护了查询免受 SQL 注入，即使提供了恶意字符串。

MySQL - 更新查询

UPDATE 语句用于修改表中现有记录。

语法

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

警告：如果您省略 WHERE 子句，表中的所有记录都将被更新！ 请务必小心并仔细检查您的 WHERE 子句。

从命令提示符更新数据

mysql> UPDATE tutorials_tbl
    -> SET tutorial_title = 'Advanced PHP and OOP'
    -> WHERE tutorial_id = 1;

使用 PHP 脚本更新数据

使用带有占位符的预处理语句，既用于要设置的值，也用于 WHERE 子句中的条件。

<?php

require 'db_connect.php';

$newTitle = 'Advanced PHP and Object-Oriented Programming';
$tutorialIdToUpdate = 1;

try {
    $sql = "UPDATE tutorials_tbl SET tutorial_title = ? WHERE tutorial_id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$newTitle, $tutorialIdToUpdate]);

    // rowCount() 可以告诉您受影响的行数
    $affectedRows = $stmt->rowCount();

    echo "Updated data successfully. $affectedRows row(s) were affected.";

} catch (PDOException $e) {
    die('Could not update data: ' . $e->getMessage());
}

?>

MySQL - 删除查询

DELETE 语句用于从表中删除现有记录。

语法

DELETE FROM table_name WHERE condition;

警告：如果您省略 WHERE 子句，表中的所有记录都将被删除！ 这是一个破坏性且通常是灾难性的错误。

从命令提示符删除数据

mysql> DELETE FROM tutorials_tbl WHERE tutorial_id = 3;

使用 PHP 脚本删除数据

对于涉及用户输入的 DELETE 查询，请务必使用预处理语句。

<?php

require 'db_connect.php';

$tutorialIdToDelete = 3;

try {
    $sql = "DELETE FROM tutorials_tbl WHERE tutorial_id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$tutorialIdToDelete]);

    $affectedRows = $stmt->rowCount();

    echo "Deleted data successfully. $affectedRows row(s) were affected.";

} catch (PDOException $e) {
    die('Could not delete data: ' . $e->getMessage());
}

?>

MySQL - LIKE 子句

LIKE 运算符在 WHERE 子句中使用，用于在列中搜索指定的模式。它对于实现搜索功能至关重要。

LIKE 运算符通常与两个通配符结合使用：

• %（百分号）：代表零个、一个或多个字符。
• _（下划线）：代表单个字符。

示例

• WHERE name LIKE 'a%'：查找以“a”开头的任何值。
• WHERE name LIKE '%a'：查找以“a”结尾的任何值。
• WHERE name LIKE '%or%'：查找在任何位置包含“or”的任何值。
• WHERE name LIKE '_r%'：查找在第二个位置包含“r”的任何值。
• WHERE name LIKE 'a_%'：查找以“a”开头且长度至少为 2 个字符的任何值。

从命令提示符使用 LIKE

mysql> SELECT * FROM tutorials_tbl WHERE tutorial_title LIKE '%MySQL%';
+-------------+-------------------+-----------------+-----------------+
| tutorial_id | tutorial_title    | tutorial_author | submission_date |
+-------------+-------------------+-----------------+-----------------+
|           2 | Mastering MySQL 8 | Jane Smith      | 2023-10-27      |
+-------------+-------------------+-----------------+-----------------+

在 PHP 脚本中使用 LIKE

当在预处理语句中使用 LIKE 时，您将通配符字符（%）包含在绑定到占位符的值中。

<?php

require 'db_connect.php';

$searchTerm = 'PHP'; // 来自用户输入

try {
    $sql = "SELECT tutorial_id, tutorial_title FROM tutorials_tbl WHERE tutorial_title LIKE ?";

    $stmt = $pdo->prepare($sql);

    // 在执行前将通配符添加到搜索词中
    $stmt->execute(["%$searchTerm%"]);

    $results = $stmt->fetchAll();

    if ($results) {
        echo "<h3>Search results for '$searchTerm':</h3><ul>";
        foreach ($results as $row) {
            echo "<li>" . htmlspecialchars($row['tutorial_title']) . "</li>";
        }
        echo "</ul>";
    } else {
        echo "<p>No results found.</p>";
    }

} catch (PDOException $e) {
    die('Query failed: ' . $e->getMessage());
}

?>

MySQL - 排序结果

ORDER BY 子句用于按升序或降序对结果集进行排序。默认情况下，MySQL 服务器可以自由地以任何顺序返回行，因此如果顺序很重要，您必须指定它。

语法

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

• ASC（升序）：从最低到最高排序（A-Z，0-9）。这是默认值。
• DESC（降序）：从最高到最低排序（Z-A，9-0）。
• 您可以按多个列排序。结果首先按第一列排序，然后对于第一列中具有相同值的行，按第二列排序，依此类推。

在命令提示符使用 ORDER BY

mysql> SELECT tutorial_author, tutorial_title
    -> FROM tutorials_tbl
    -> ORDER BY tutorial_author ASC, submission_date DESC;
+-----------------+---------------------------------------------+
| tutorial_author | tutorial_title                              |
+-----------------+---------------------------------------------+
| Jane Smith      | Mastering MySQL 8                           |
| John Doe        | Advanced PHP and Object-Oriented Programming|
+-----------------+---------------------------------------------+

在 PHP 脚本中使用 ORDER BY

ORDER BY 子句本身是 SQL 查询字符串的一部分。如果您需要让用户选择排序顺序，请务必小心。不要直接将用户输入插入到 ORDER BY 子句中。

最佳实践：使用白名单 (whitelist) 来验证用户提供的排序参数。

<?php

require 'db_connect.php';

// 允许排序的列白名单
$allowedSortColumns = ['tutorial_title', 'tutorial_author', 'submission_date'];
// 允许排序方向的白名单
$allowedSortDirections = ['ASC', 'DESC'];

// 从用户获取排序参数，并带有安全默认值
$sortColumn = $_GET['sort_by'] ?? 'submission_date';
$sortDirection = $_GET['direction'] ?? 'DESC';

// 根据白名单验证用户输入
if (!in_array($sortColumn, $allowedSortColumns)) {
    $sortColumn = 'submission_date'; // 默认设置为安全值
}
if (!in_array(strtoupper($sortDirection), $allowedSortDirections)) {
    $sortDirection = 'DESC'; // 默认设置为安全值
}

try {
    // 安全地构建查询
    $sql = "SELECT * FROM tutorials_tbl ORDER BY `$sortColumn` $sortDirection";
    $stmt = $pdo->query($sql); // 安全的，因为我们已经验证了输入
    $results = $stmt->fetchAll();

    // ... 显示结果 ...
    echo "<p>Sorting by $sortColumn $sortDirection</p>";
    // ... 遍历 $results 并显示它们 ...

} catch (PDOException $e) {
    die('Query failed: ' . $e->getMessage());
}

?>

使用 MySQL Joins

在关系型数据库中，数据通常分散在多个表中。JOIN 子句用于根据表之间的相关列来组合来自两个或多个表的行。

最佳实践：使用显式 JOIN 语法（INNER JOIN、LEFT JOIN），而不是 FROM 子句中旧的逗号分隔语法。它更具可读性，并且不易发生意外的交叉连接 (cross joins)。

INNER JOIN

INNER JOIN 关键字选择在两个表中都具有匹配值的记录。它是最常见的连接类型。

示例

让我们假设我们有 tutorials_tbl 和一个新表 authors_tbl，用于存储作者简介。

-- authors_tbl 结构
CREATE TABLE authors_tbl (
    author_name VARCHAR(40) NOT NULL PRIMARY KEY,
    author_bio TEXT
) ENGINE=InnoDB;

-- tutorials_tbl 结构（带外键）
CREATE TABLE tutorials_tbl (
    tutorial_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    tutorial_title VARCHAR(100) NOT NULL,
    tutorial_author VARCHAR(40) NOT NULL,
    submission_date DATE,
    FOREIGN KEY (tutorial_author) REFERENCES authors_tbl(author_name)
) ENGINE=InnoDB;

要获取教程列表及其作者简介，我们可以连接这些表：

SELECT
    t.tutorial_title,
    t.submission_date,
    a.author_name,
    a.author_bio
FROM
    tutorials_tbl AS t
INNER JOIN
    authors_tbl AS a ON t.tutorial_author = a.author_name;

这里，AS t 和 AS a 是表别名，它们使查询更短且更具可读性。

LEFT JOIN（或 LEFT OUTER JOIN）

LEFT JOIN 关键字返回左表 (tutorials_tbl) 中的所有记录，以及右表 (authors_tbl) 中匹配的记录。如果没有匹配项，结果在右侧为 NULL。

当您想列出所有教程，即使那些作者在 authors_tbl 中可能还没有简介的教程时，这会很有用。

SELECT
    t.tutorial_title,
    a.author_name,
    a.author_bio -- 如果作者不在 authors_tbl 中，则此项将为 NULL
FROM
    tutorials_tbl AS t
LEFT JOIN
    authors_tbl AS a ON t.tutorial_author = a.author_name;

在 PHP 脚本中使用 Join

在 PHP 中执行 JOIN 查询就像执行任何其他 SELECT 查询一样。

<?php

require 'db_connect.php';

try {
    $sql = "SELECT t.tutorial_title, a.author_name, a.author_bio \n            FROM tutorials_tbl AS t \n            INNER JOIN authors_tbl AS a ON t.tutorial_author = a.author_name";

    $stmt = $pdo->query($sql);
    $results = $stmt->fetchAll();

    // ... 遍历并显示组合结果 ...

} catch (PDOException $e) {
    die('Query failed: ' . $e->getMessage());
}

?>

处理 MySQL NULL 值

数据库表中的 NULL 值是一个特殊标记，用于指示数据值不存在。它与空字符串 '' 或数字 0 不同。

过滤 NULL 值时，不能使用 = 或 != 等标准比较运算符。相反，您必须使用 IS NULL 或 IS NOT NULL 运算符。

• IS NULL：如果列值为 NULL，则此运算符返回 true。
• IS NOT NULL：如果列值不为 NULL，则此运算符返回 true。
• <=> (NULL 安全相等运算符)：此运算符执行类似于 = 运算符的相等比较，但如果两个操作数都为 NULL，则返回 1 (true) 而不是 NULL；如果其中一个操作数为 NULL，则返回 0 (false) 而不是 NULL。

在命令提示符使用 NULL 运算符

假设 tutorials_tbl 有一个 submission_date 字段，它可以是 NULL。

-- 这不会按预期工作
mysql> SELECT * FROM tutorials_tbl WHERE submission_date = NULL;
Empty set (0.00 sec)

-- 这是查找日期为 NULL 的行的正确方法
mysql> SELECT * FROM tutorials_tbl WHERE submission_date IS NULL;

-- 这是查找有日期的行的正确方法
mysql> SELECT * FROM tutorials_tbl WHERE submission_date IS NOT NULL;

在 PHP 脚本中处理 NULL 值

您可以在 SQL 查询中检查 NULL。如果需要使用 PDO 插入 NULL 值，只需传递 PHP 的 null 值。

示例：插入 NULL 值

<?php

require 'db_connect.php';

try {
    // 假设提交日期是可选的
    $sql = "INSERT INTO tutorials_tbl (tutorial_title, tutorial_author, submission_date) VALUES (?, ?, ?)";
    $stmt = $pdo->prepare($sql);

    // 传递 PHP null 类型以插入 SQL NULL
    $stmt->execute(['New Draft Tutorial', 'Unassigned', null]);

    echo "Inserted a record with a NULL date.";

} catch (PDOException $e) {
    die('Insert failed: ' . $e->getMessage());
}

?>

MySQL - 正则表达式

除了使用 LIKE 进行简单模式匹配外，MySQL 还支持使用 REGEXP（或 RLIKE）运算符进行强大的正则表达式模式匹配。

下表列出了 MySQL 正则表达式中一些最常用的元字符 (metacharacters)：

模式	模式匹配的内容
^	字符串的开头
$	字符串的末尾
.	任何单个字符
[...]	方括号中列出的任何字符
[^...]	方括号中未列出的任何字符
p1|p2|p3	交替 (Alternation)；匹配模式 p1、p2 或 p3 中的任何一个
*	前面元素的零个或多个实例
+	前面元素的一个或多个实例
{n}	前面元素的 n 个实例
{m,n}	前面元素的 m 到 n 个实例

示例

根据上表，您可以构建各种复杂的查询。

查找所有以“J”开头的作者姓名：

mysql> SELECT tutorial_author FROM tutorials_tbl WHERE tutorial_author REGEXP '^J';

查找所有以“th”结尾的作者姓名：

mysql> SELECT tutorial_author FROM tutorials_tbl WHERE tutorial_author REGEXP 'th$';

查找所有包含“oh”或“an”的作者姓名：

mysql> SELECT tutorial_author FROM tutorials_tbl WHERE tutorial_author REGEXP 'oh|an';

查找所有恰好为 4 个字符长的作者姓名：

mysql> SELECT tutorial_author FROM tutorials_tbl WHERE tutorial_author REGEXP '^.{4}$';

MySQL - 事务

事务 (Transaction) 是作为单个逻辑工作单元执行的一系列操作。事务中的所有操作都必须成功完成。如果任何操作失败，整个事务将被回滚 (rolled back)，数据库将恢复到事务开始之前的状态。这确保了数据完整性。

事务的 ACID 特性

事务由四个标准属性定义，称为 ACID：

• 原子性 (Atomicity)：确保工作单元内的所有操作都成功完成。如果不是，事务将在失败点中止，所有先前的操作都将回滚到其原始状态。
• 一致性 (Consistency)：确保数据库在事务之前和之后保持一致状态。写入数据库的任何数据都必须符合所有定义的规则，包括约束、级联 (cascades) 和触发器 (triggers)。
• 隔离性 (Isolation)：确保并发事务产生与按顺序执行时相同的结果。一个事务的中间状态对其他事务是隐藏的。
• 持久性 (Durability)：确保一旦事务被提交 (committed)，它将保持不变，即使在断电、崩溃或错误的情况下也是如此。

SQL 中的事务控制

您可以通过三个主要命令控制事务：

• START TRANSACTION（或 BEGIN）：启动一个新事务。
• COMMIT：将事务中进行的所有更改保存到数据库，使它们永久化。
• ROLLBACK：丢弃事务中进行的所有更改，将数据库恢复到事务开始之前的状态。

为了使事务正常工作，您的表必须使用支持事务的存储引擎。InnoDB 是 MySQL 中为此目的而生的默认且使用最广泛的引擎。

在 PHP 脚本中使用事务（PDO）

PDO 提供了出色、直观的事务控制方法。

示例：资金转账

想象一下将 100 美元从账户 A 转账到账户 B。这需要两个 UPDATE 操作，它们必须同时成功或同时失败。

<?php

require 'db_connect.php';

// 账户 ID 和转账金额
$fromAccountId = 1;
$toAccountId = 2;
$amount = 100.00;

try {
    // 1. 启动事务
    $pdo->beginTransaction();

    // 2. 从第一个账户扣款
    $stmt1 = $pdo->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
    $stmt1->execute([$amount, $fromAccountId]);

    // 3. 向第二个账户入账
    $stmt2 = $pdo->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
    $stmt2->execute([$amount, $toAccountId]);

    // 4. 如果代码执行到这里，说明两个查询都成功了。提交事务。
    $pdo->commit();
    echo "Transfer successful!";

} catch (PDOException $e) {
    // 5. 如果任何查询失败，将抛出异常。回滚事务。
    $pdo->rollBack();
    die("Transfer failed: " . $e->getMessage());
}

?>

MySQL - ALTER 命令

ALTER TABLE 语句用于在现有表中添加、删除或修改列。它还可以用于在现有表上添加和删除各种约束。

添加列

要添加新列，请使用 ADD COLUMN。您可以使用 FIRST 或 AFTER another_column 指定其位置。

-- 在表末尾添加一个新列 'status'
ALTER TABLE tutorials_tbl ADD COLUMN status ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'draft';

-- 在 'tutorial_title' 后添加一个列 'views'
ALTER TABLE tutorials_tbl ADD COLUMN views INT UNSIGNED NOT NULL DEFAULT 0 AFTER tutorial_title;

删除列

要删除现有列，请使用 DROP COLUMN。

ALTER TABLE tutorials_tbl DROP COLUMN views;

修改或更改列

要更改列的数据类型或约束，请使用 MODIFY COLUMN。

-- 更改 tutorial_author 列以使其更宽
ALTER TABLE tutorials_tbl MODIFY COLUMN tutorial_author VARCHAR(50) NOT NULL;

要同时重命名和更改列的定义，请使用 CHANGE COLUMN。

-- 将 'submission_date' 重命名为 'publish_date' 并更改其类型
ALTER TABLE tutorials_tbl CHANGE COLUMN submission_date publish_date DATETIME;

重命名表

要重命名整个表，请使用 RENAME TO。

ALTER TABLE tutorials_tbl RENAME TO articles_tbl;

MySQL - 索引

数据库索引是一种数据结构，可以提高表上数据检索操作的速度。数据库搜索引擎使用索引来更快地查找记录，类似于使用书中的索引。如果没有索引，数据库必须扫描表中的每一行（“全表扫描”）才能找到请求的数据。

尽管索引可以加快 SELECT 查询和 WHERE 子句的速度，但它们会减慢 INSERT、UPDATE 和 DELETE 等数据修改操作的速度，因为索引也必须更新。因此，您应该只在搜索条件中频繁使用的列上创建索引。

创建索引

您可以使用 CREATE INDEX 语句创建索引。

-- 在 tutorial_author 列上创建简单索引
CREATE INDEX idx_author ON tutorials_tbl (tutorial_author);

-- 创建唯一索引，防止列中出现重复值
CREATE UNIQUE INDEX idx_unique_title ON tutorials_tbl (tutorial_title);

使用 ALTER TABLE 添加索引

您还可以使用 ALTER TABLE 为现有表添加索引。

-- 添加多列索引
ALTER TABLE tutorials_tbl ADD INDEX idx_author_date (tutorial_author, submission_date);

主键

A PRIMARY KEY 是一种特殊的唯一索引。每个表都应该有一个主键，因为它唯一标识每一行，并且通常用于 JOIN 操作。主键列不能包含 NULL 值。

-- 为现有表添加主键（列必须为 NOT NULL）
ALTER TABLE tutorials_tbl ADD PRIMARY KEY (tutorial_id);

删除索引

要删除索引，请使用 DROP INDEX 语句。

DROP INDEX idx_author ON tutorials_tbl;

显示索引信息

使用 SHOW INDEX 查看表上的所有索引。

mysql> SHOW INDEX FROM tutorials_tbl;
-- 或为了更好的格式化：
mysql> SHOW INDEX FROM tutorials_tbl\G

MySQL - 临时表

临时表是 MySQL 中的一项特殊功能，允许您存储临时结果集，您可以在单个会话中对其进行进一步处理。

临时表的一个关键特性是它只对当前数据库会话可见，并在会话结束时自动删除。这使得它们对于复杂的报告或多步数据转换非常有用，而不会使主数据库架构混乱。

示例

让我们创建一个临时表来保存销售数据摘要。

mysql> CREATE TEMPORARY TABLE SalesSummary (
    ->     product_name VARCHAR(50) NOT NULL,
    ->     total_sales DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
    ->     total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary VALUES ('SuperWidget', 5000.75, 100);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+--------------------+
| product_name | total_sales | total_units_sold   |
+--------------+-------------+--------------------+
| SuperWidget  |     5000.75 |                100 |
+--------------+-------------+--------------------+

如果您发出 SHOW TABLES 命令，临时表将不会被列出。如果您启动新的连接会话，SalesSummary 表将不再存在。

删除临时表

尽管临时表在会话结束时会自动删除，但您可以使用标准 DROP TABLE 命令显式删除它们。这是一种好习惯，可以在不再需要表时立即释放资源。

mysql> DROP TABLE SalesSummary;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM SalesSummary;
ERROR 1146 (42S02): Table 'TUTORIALS.SalesSummary' doesn't exist

MySQL - 克隆表

有时您需要创建一个表的精确副本，包括其结构、索引和默认值。这对于创建备份或创建测试表进行实验非常有用。

方法 1：两步克隆

此方法创建一个具有相同结构的空表，然后复制数据。

步骤 1：创建结构的空克隆

CREATE TABLE tutorials_tbl_clone LIKE tutorials_tbl;

步骤 2：从原始表复制数据

INSERT INTO tutorials_tbl_clone SELECT * FROM tutorials_tbl;

这种方法简洁、易读，并且通常推荐。

方法 2：一步克隆（仅结构）

您可以创建一个新表并根据 SELECT 查询定义其结构。但是，此方法不复制索引或 AUTO_INCREMENT 属性。

CREATE TABLE tutorials_tbl_backup AS SELECT * FROM tutorials_tbl;

这对于创建简单的数据备份很快，但不是真正的结构克隆。

方法 3：使用 SHOW CREATE TABLE 手动克隆

为了完全控制，您可以获取原始表的 CREATE TABLE 语句，修改它，然后运行它。

步骤 1：获取表的创建语句

mysql> SHOW CREATE TABLE tutorials_tbl\G
*************************** 1. row ***************************
       Table: tutorials_tbl
Create Table: CREATE TABLE `tutorials_tbl` (
  `tutorial_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tutorial_title` varchar(100) NOT NULL,
  `tutorial_author` varchar(40) NOT NULL,
  `submission_date` date DEFAULT NULL,
  PRIMARY KEY (`tutorial_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
...

步骤 2：修改并执行语句

复制输出，将表名从 tutorials_tbl 更改为您想要的克隆名称（例如 clone_tbl），然后执行修改后的 CREATE TABLE 查询。然后，如果需要，使用方法 1 中的 INSERT INTO ... SELECT 语句复制数据。

MySQL - 数据库信息

获取和使用 MySQL 元数据

元数据 (Metadata) 是关于数据的数据。在 MySQL 中，这可以是：

• 查询结果信息：一个查询影响了多少行？
• 架构信息：存在哪些数据库、表和列？
• 服务器信息：服务器是什么版本？它的配置设置是什么？

获取查询结果信息 (PHP/PDO)

UPDATE、DELETE、INSERT 影响的行数

执行 UPDATE、DELETE 或 INSERT 的预处理语句后，您可以使用 PDOStatement::rowCount() 方法。

$stmt = $pdo->prepare("DELETE FROM tutorials_tbl WHERE tutorial_id = ?");
$stmt->execute([10]);
$affectedRows = $stmt->rowCount();
echo "$affectedRows rows were deleted.";

获取最后插入行的 ID

当您将一行插入到具有 AUTO_INCREMENT 主键的表中时，您可以使用 PDO::lastInsertId() 方法获取新 ID。

$stmt = $pdo->prepare("INSERT INTO tutorials_tbl (tutorial_title, ...) VALUES (?, ...)");
$stmt->execute(['New Tutorial', ...]);
$newId = $pdo->lastInsertId();
echo "The new tutorial was inserted with ID: $newId";

获取架构信息

您可以通过执行 SHOW 命令获取数据库和表的列表。

<?php
require 'db_connect.php';

// 获取所有数据库列表
$stmt = $pdo->query('SHOW DATABASES');
$databases = $stmt->fetchAll(PDO::FETCH_COLUMN);
print_r($databases);

// 获取当前数据库中的表列表
$stmt = $pdo->query('SHOW TABLES');
$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
print_r($tables);
?>

获取服务器元数据

您可以执行简单的查询来获取有关服务器本身的信息。

序号	命令与描述
1	SELECT VERSION()：返回 MySQL 服务器版本字符串。
2	SELECT DATABASE()：返回当前默认数据库的名称。
3	SELECT USER()：返回当前用户的用户名和主机。
4	SHOW STATUS：提供大量服务器状态指标（例如，运行时间、总查询数）。
5	SHOW VARIABLES：显示服务器配置变量的值。

<?php
require 'db_connect.php';

$version = $pdo->query('SELECT version()')->fetchColumn();
echo "Server Version: $version";
?>

MySQL - 使用 MySQL 序列

序列 (Sequence) 是一组按需按顺序生成的整数（1, 2, 3, …）。序列在数据库中至关重要，因为许多应用程序要求表中的每一行都具有唯一值，而序列为主键提供了生成这些值的简单方法。

使用 AUTO_INCREMENT 列

在 MySQL 中创建序列最简单、最常见的方法是定义一个带有 AUTO_INCREMENT 属性的列。MySQL 将自动为插入的每一行生成该列的新唯一值。

示例

mysql> CREATE TABLE insects (
    ->     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(30) NOT NULL,
    ->     origin VARCHAR(30) NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO insects (name, origin) VALUES
    ->     ('housefly', 'kitchen'),
    ->     ('millipede', 'driveway');
Query OK, 2 rows affected (0.01 sec)

mysql> SELECT * FROM insects;
+----+-----------+----------+
| id | name      | origin   |
+----+-----------+----------+
|  1 | housefly  | kitchen  |
|  2 | millipede | driveway |
+----+-----------+----------+

请注意，我们没有在 INSERT 语句中指定 id；MySQL 为我们生成了它。

在 PHP 中获取 AUTO_INCREMENT 值

INSERT 查询后，您通常需要知道刚创建的新行的 ID。使用 PDO::lastInsertId() 方法检索它。

<?php
require 'db_connect.php';

$stmt = $pdo->prepare("INSERT INTO insects (name, origin) VALUES (?, ?)");
$stmt->execute(['grasshopper', 'front yard']);

$newInsectId = $pdo->lastInsertId();

echo "Successfully inserted new insect with ID: $newInsectId"; // 可能会输出 "3"

?>

重新排序现有表

警告：这是一个危险的操作，如果其他表具有引用您即将更改的 AUTO_INCREMENT 列的外键，则应避免此操作。它可能会破坏您数据的引用完整性。

如果您删除了许多行并想填补序列中的空白，唯一的方法是删除该列并再次添加它。

mysql> ALTER TABLE insects DROP id;
mysql> ALTER TABLE insects ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);

从特定值开始序列

默认情况下，AUTO_INCREMENT 序列从 1 开始。您可以使用 ALTER TABLE 设置不同的起始值。

-- 将下一个自动递增值设置为 100
mysql> ALTER TABLE insects AUTO_INCREMENT = 100;

MySQL - 处理重复项

表中的重复记录可能导致数据不一致和不正确的分析。重要的是既要防止创建重复项，又要清理已存在的任何重复项。

使用约束防止重复项

防止重复项的最佳方法是使用约束在数据库级别强制执行唯一性。

• PRIMARY KEY：一个或多个列上的主键确保这些列中的值组合在整个表中是唯一的。
• UNIQUE 索引：唯一索引提供与主键相同的唯一性保证，但一个表可以有多个唯一索引。与主键不同，唯一索引可以允许多个 NULL 值。

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL UNIQUE, -- 用户名必须唯一
    email VARCHAR(100) NOT NULL UNIQUE -- 电子邮件也必须唯一
);

如果您尝试插入违反 PRIMARY KEY 或 UNIQUE 约束的记录，MySQL 将返回错误。

处理 INSERT 上的重复错误

当插入可能违反唯一约束时，除了让它失败之外，您还有其他选择。

• INSERT IGNORE：如果记录不重复现有记录，MySQL 会插入它。如果它是重复的，MySQL 会静默丢弃新记录，而不会生成错误。
• REPLACE INTO：如果记录是新的，则插入。如果它是重复的（基于 PRIMARY 或 UNIQUE 键），新记录将替换旧记录。
• INSERT ... ON DUPLICATE KEY UPDATE：这是最灵活的选项。如果记录是新的，则插入。如果它是重复的，则改为执行 UPDATE 语句。

-- ON DUPLICATE KEY UPDATE 示例：插入新用户，如果用户存在则更新其 last_login 时间。
INSERT INTO users (username, email, last_login)
VALUES ('johndoe', 'john.doe@example.com', NOW())
ON DUPLICATE KEY UPDATE last_login = NOW();

查找现有重复项

要根据某些列查找重复行，您可以使用 GROUP BY 和 HAVING。

-- 查找被多个用户使用的所有电子邮件
SELECT email, COUNT(*) AS repetitions
FROM users
GROUP BY email
HAVING repetitions > 1;

删除现有重复项

从实时表中删除重复项可能很棘手。一种安全的方法是创建一个包含唯一行的新表，然后替换旧表。

-- 步骤 1：创建一个具有相同结构的临时表
CREATE TABLE users_unique LIKE users;

-- 步骤 2：在新表中添加一个定义重复项的列上的唯一索引
ALTER TABLE users_unique ADD UNIQUE INDEX (email);

-- 步骤 3：复制数据，忽略重复项
INSERT IGNORE INTO users_unique SELECT * FROM users;

-- 步骤 4：重命名表（如果可能，请在事务中执行此操作）
RENAME TABLE users TO users_old, users_unique TO users;

MySQL 和 SQL 注入

SQL 注入 (SQL Injection) 是最常见、最危险的 Web 应用程序漏洞之一。当攻击者能够通过将恶意 SQL 代码插入用户输入来操纵应用程序的 SQL 查询时，就会发生这种情况。

漏洞：未经清理的输入

考虑以下过去常见的易受攻击的 PHP 代码：

// 不要使用此代码！它存在漏洞。
$userId = $_GET['id'];
$sql = "SELECT * FROM users WHERE id = $userId";
$result = $pdo->query($sql);

合法用户可能会提供一个像 123 这样的 ID。查询变为 SELECT * FROM users WHERE id = 123。

然而，攻击者可以提供 123; DROP TABLE users; 这样的输入。查询变为 SELECT * FROM users WHERE id = 123; DROP TABLE users;。尽管现代 PDO 驱动程序通常默认阻止多查询（查询堆叠 (query stacking)），但其他形式的注入仍然可能发生，例如 123 OR 1=1，它将返回所有用户。

解决方案：预处理语句

防止 SQL 注入的第一条规则是：永远不要相信用户输入，并始终使用带占位符的预处理语句。

预处理语句将 SQL 命令与数据分离。数据库首先编译命令模板，然后将数据作为参数接收。这使得数据不可能被解释为 SQL 命令。

编写安全查询的方法

// 这是安全、正确的方法

// 获取用户输入
$userId = $_GET['id'];

// 1. 使用占位符 (?) 准备 SQL 语句
$sql = "SELECT * FROM users WHERE id = ?";
$stmt = $pdo->prepare($sql);

// 2. 执行语句，将用户输入绑定到占位符
$stmt->execute([$userId]);

// 3. 获取结果
$user = $stmt->fetch();

在此安全版本中，即使攻击者提供 123; DROP TABLE users;，数据库也会将整个字符串视为它试图为 id 列查找的单个值。它只会找不到匹配项，恶意命令永远不会被执行。

已弃用实践：过去，开发者使用 mysql_real_escape_string() 等函数手动“转义”特殊字符。这种方法容易出错，并且已被视为过时。始终优先使用预处理语句。

MySQL - 数据库导出

导出数据库是创建备份或将数据迁移到另一台服务器的关键任务。主要工具是 mysqldump 命令行实用程序。

使用 mysqldump 导出数据

mysqldump 生成一个文件，其中包含一组 SQL 语句，这些语句可以执行以重新创建原始数据库对象和数据。

导出单个数据库

# 语法：mysqldump -u [用户名] -p [数据库名] > [文件名.sql]
$ mysqldump -u root -p TUTORIALS > tutorials_backup.sql

导出特定表

# 语法：mysqldump -u [用户名] -p [数据库名] [表名] > [文件名.sql]
$ mysqldump -u root -p TUTORIALS tutorials_tbl > single_table_backup.sql

导出所有数据库

$ mysqldump -u root -p --all-databases > all_databases_backup.sql

InnoDB 的最佳实践：备份 InnoDB 表时，使用 --single-transaction 标志。这会在转储开始时创建数据库的一致快照，而不会锁定表，从而允许您的应用程序不中断地继续运行。

$ mysqldump -u root -p --single-transaction TUTORIALS > consistent_backup.sql

使用 SELECT ... INTO OUTFILE 导出数据

此 SQL 语句将查询结果直接导出到服务器本地文件系统上的文件。它速度非常快，对于生成 CSV 或其他基于文本的数据文件非常有用。

注意：此命令具有安全隐患。执行此命令的用户必须具有 FILE 权限，并且输出文件不能已存在。该文件由 MySQL 服务器进程写入，因此将归 mysql 系统用户所有。

mysql> SELECT * FROM tutorials_tbl
    -> INTO OUTFILE '/var/lib/mysql-files/tutorials.csv'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n';

出于安全原因，INTO OUTFILE 的默认位置通常受 secure_file_priv 系统变量的限制。您可能需要检查此变量的值（SHOW VARIABLES LIKE 'secure_file_priv';）以查看允许哪个目录。

MySQL - 数据库导入

导入数据是将数据加载到数据库的过程，通常来自 mysqldump 创建的备份文件或像 CSV 这样的数据文件。

从 mysqldump 文件导入

恢复数据库最常见的方法是使用 mysql 命令行客户端执行 mysqldump 创建的 SQL 脚本。

步骤 1：创建数据库（如果不存在）

转储文件通常不包含 CREATE DATABASE 语句，因此您需要先创建它。

mysql> CREATE DATABASE TUTORIALS;

步骤 2：导入数据

将备份文件重定向到 mysql 客户端。

# 语法：mysql -u [用户名] -p [数据库名] < [文件名.sql]
$ mysql -u root -p TUTORIALS < tutorials_backup.sql

这将执行文件中所有的 SQL 语句，重新创建表并插入数据。

使用 LOAD DATA INFILE 导入数据

LOAD DATA INFILE 是用于将文本文件（如 CSV）中的数据批量加载到表中的 SQL 命令。它速度极快。

mysql> LOAD DATA INFILE '/var/lib/mysql-files/tutorials.csv'
    -> INTO TABLE tutorials_tbl
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 ROWS; -- 使用此选项跳过 CSV 文件中的标题行

LOCAL 关键字（LOAD DATA LOCAL INFILE）可用于从客户端机器的文件系统而非服务器的文件系统加载文件。然而，出于安全原因，此功能通常在服务器和客户端上默认禁用，因为它可能允许恶意服务器从客户端机器请求任何文件。