MySQl基础

MySQL 简介

MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS（Relational Database Management System：关系数据库管理系统）应用软件之一。

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不同的 API 用于创建、访问、管理、搜索和复制所保存的数据。尽管我们也可以将数据存储在文件中，但在文件中读写数据的速度相对较慢。

因此，我们使用关系型数据库管理系统（RDBMS）来存储和管理大数据量。关系型数据库是建立在关系模型基础上的数据库，利用集合代数等数学概念和方法来处理数据库中的数据。

RDBMS 的特点

  1. 数据以表格的形式出现
  2. 每行为各种记录名称
  3. 每列为记录名称所对应的数据域
  4. 许多的行和列组成一张表单
  5. 若干的表单组成数据库

RDBMS 术语

在学习 MySQL 数据库前，先了解 RDBMS 的一些术语：

  • 数据库：数据库是一些关联表的集合。
  • 数据表：表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
  • 列：一列（数据元素）包含了相同类型的数据，例如邮政编码的数据。
  • 行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
  • 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
  • 主键：主键是唯一的，一个数据表中只能包含一个主键。可以使用主键来查询数据。
  • 外键：外键用于关联两个表。
  • 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
  • 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，类似于书籍的目录。
  • 参照完整性：参照完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

MySQL 管理

启动和停止 MySQL

  • 启动 MySQL： 

    1
    sudo systemctl start mysql

  • 停止 MySQL：

    1
    sudo systemctl stop mysql

登录 MySQL

1
mysql -u root -p

显示数据库

1
SHOW DATABASES;

MySQL PHP 语法

连接 MySQL

在 PHP 中连接 MySQL 数据库可以使用 mysqli 扩展。下面是详细的示例代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?php
$servername = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码
$dbname = "myDB"; // 替换为你的数据库名称

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

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

关闭连接

在完成数据库操作后，需要关闭连接以释放资源：

1
2
3
<?php
$conn->close();
?>

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$servername = "localhost";
$username = "username";
$password = "password";

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

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

// 创建数据库
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    echo "数据库创建成功";
} else {
    echo "创建数据库错误: " . $conn->error;
}

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

删除数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

// 删除数据库
$sql = "DROP DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    echo "数据库删除成功";
} else {
    echo "删除数据库错误: " . $conn->error;
}

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

创建数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

// 创建数据表
$sql = "CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
)";
if ($conn->query($sql) === TRUE) {
    echo "数据表创建成功";
} else {
    echo "创建数据表错误: " . $conn->error;
}

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

插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

// 插入数据
$sql = "INSERT INTO Users (username, email, reg_date)
VALUES ('JohnDoe', 'john@example.com', NOW())";

if ($conn->query($sql) === TRUE) {
    echo "新记录插入成功";
} else {
    echo "插入记录错误: " . $conn->error;
}

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

查询数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

// 查询数据
$sql = "SELECT id, username, email FROM Users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 输出数据
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 结果";
}

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

更新数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

// 更新数据
$sql = "UPDATE Users SET email='jane@example.com' WHERE username='JohnDoe'";

if ($conn->query($sql) === TRUE) {
    echo "记录更新成功";
} else {
    echo "更新记录错误: " . $conn->error;
}

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

删除数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

// 删除数据
$sql = "DELETE FROM Users WHERE username='JohnDoe'";

if ($conn->query($sql) === TRUE) {
    echo "记录删除成功";
} else {
    echo "删除记录错误: " . $conn->error;
}

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

这些示例涵盖了使用 PHP 连接 MySQL 数据库的基本操作，包括创建、插入、查询、更新和删除数据。

MySQL 连接

使用命令行连接

使用 MySQL 客户端连接

要使用命令行连接 MySQL 数据库，可以使用 mysql 客户端工具：

1
mysql -u username -p
  • -u username：指定登录 MySQL 的用户名。
  • -p：提示输入密码。

示例：

1
mysql -u root -p

系统会提示输入密码，成功登录后，显示类似于以下内容：

1
2
3
4
5
6
7
8
9
10
11
12
13
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

使用 PHP 脚本连接 MySQL

PHP 提供了 mysqli_connect() 函数来连接数据库。该函数有 6 个参数，在成功链接到 MySQL 后返回连接标识，失败返回 FALSE。

语法

1
mysqli_connect(host, username, password, dbname, port, socket);

参数说明

参数 描述
host 可选。规定主机名或 IP 地址。
username 可选。规定 MySQL 用户名。
password 可选。规定 MySQL 密码。
dbname 可选。规定默认使用的数据库。
port 可选。规定尝试连接到 MySQL 服务器的端口号。
socket 可选。规定 socket 或要使用的已命名 pipe。

示例

基本示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?php
$host = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码
$dbname = "myDB"; // 替换为你的数据库名称

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

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

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

使用指定端口和 socket

如果需要指定端口或 socket，可以这样做：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?php
$host = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
$port = 3306; // MySQL 默认端口
$socket = null; // 如果有特定的 socket，替换为你的 socket 路径

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

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

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

使用 PDO 扩展连接

PDO（PHP Data Objects）是一种更通用的连接方式，支持多种数据库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?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();
}
?>

使用 MySQL 二进制文件连接

MySQL 提供了多个二进制文件来管理和操作数据库，以下是一些常用的二进制文件及其用法：

mysql 客户端工具

1
mysql -u username -p

如上所述，用于登录 MySQL 命令行客户端。

mysqladmin 工具

用于管理 MySQL 服务器，如查看服务器状态、关闭服务器等：

1
mysqladmin -u username -p status

mysqldump 工具

用于备份数据库：

1
mysqldump -u username -p myDB > myDB_backup.sql

mysqlimport 工具

用于从文本文件导入数据到 MySQL 表：

1
mysqlimport -u username -p --local myDB /path/to/datafile.txt

完整的 PHP 脚本示例

以下是一个更完整的 PHP 脚本示例，包括连接 MySQL 数据库、创建表、插入数据和查询数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

// 创建数据表
$sql = "CREATE TABLE IF NOT EXISTS Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "数据表创建成功<br>";
} else {
    echo "创建数据表错误: " . $conn->error . "<br>";
}

// 插入数据
$sql = "INSERT INTO Users (username, email) VALUES ('JohnDoe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    echo "新记录插入成功<br>";
} else {
    echo "插入记录错误: " . $conn->error . "<br>";
}

// 查询数据
$sql = "SELECT id, username, email FROM Users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // 输出数据
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 结果<br>";
}

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

总结

  • 使用命令行工具如 mysql、mysqladmin 和 mysqldump 可以管理 MySQL 数据库。
  • 使用 PHP 可以通过 mysqli 或 PDO 扩展连接和操作 MySQL 数据库。
  • 需要确保在数据库操作完成后关闭连接以释放资源。

MySQL 创建数据库

使用命令行

要使用命令行在 MySQL 中创建数据库，可以使用以下 SQL 命令：

1
CREATE DATABASE myDB;

使用 mysqladmin 工具创建数据库

mysqladmin 是一个用于执行管理操作的 MySQL 客户端程序，包括创建数据库。使用 mysqladmin 创建数据库的命令如下：

1
mysqladmin -u username -p create myDB
  • -u username：指定登录 MySQL 的用户名。
  • -p：提示输入密码。
  • create myDB：指定要创建的数据库名称。

示例：

1
mysqladmin -u root -p create myDB

在 PHP 中创建数据库

使用 PHP 脚本创建数据库的详细示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$servername = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码

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

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

// 创建数据库
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    echo "数据库创建成功";
} else {
    echo "创建数据库错误: " . $conn->error;
}

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

完整示例：命令行、mysqladmin 和 PHP

使用命令行创建数据库

  1. 打开终端或命令行工具。

  2. 登录 MySQL：

    1
    mysql -u root -p

  3. 输入密码后，执行以下命令创建数据库：

    1
    CREATE DATABASE myDB;

使用 mysqladmin 创建数据库

在终端或命令行工具中，使用以下命令：

1
mysqladmin -u root -p create myDB

系统会提示输入密码，输入后数据库将被创建。

使用 PHP 脚本创建数据库

以下是一个完整的 PHP 脚本示例，用于连接 MySQL 并创建数据库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$servername = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码

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

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

// 创建数据库
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    echo "数据库创建成功";
} else {
    echo "创建数据库错误: " . $conn->error;
}

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

PHP 脚本创建数据库，并包含更多错误处理

在实际应用中，添加更多的错误处理和日志记录是个好习惯。以下示例展示了如何在创建数据库时包含详细的错误处理：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<?php
$servername = "localhost";
$username = "username";
$password = "password";

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

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

// 创建数据库
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    echo "数据库创建成功<br>";
} else {
    echo "创建数据库错误: " . $conn->error . "<br>";
}

// 检查数据库是否存在
$sql = "SHOW DATABASES LIKE 'myDB'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    echo "数据库 myDB 已经存在<br>";
} else {
    echo "数据库 myDB 不存在<br>";
}

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

总结

  • 可以使用命令行、mysqladmin 工具和 PHP 脚本来创建 MySQL 数据库。
  • 命令行使用 CREATE DATABASE 语句创建数据库。
  • mysqladmin 工具提供了简单的命令来创建数据库。
  • PHP 脚本使用 mysqli 扩展来连接 MySQL 并执行 SQL 语句创建数据库。

MySQL 删除数据库

使用命令行

要使用命令行在 MySQL 中删除数据库，可以使用以下 SQL 命令：

1
DROP DATABASE myDB;

使用 mysqladmin 工具删除数据库

mysqladmin 是一个用于执行管理操作的 MySQL 客户端程序，包括删除数据库。使用 mysqladmin 删除数据库的命令如下：

1
mysqladmin -u username -p drop myDB
  • -u username：指定登录 MySQL 的用户名。
  • -p：提示输入密码。
  • drop myDB：指定要删除的数据库名称。

示例：

1
mysqladmin -u root -p drop myDB

系统会提示输入密码，并确认是否要删除数据库，输入 y 确认删除。

在 PHP 中删除数据库

使用 PHP 脚本删除数据库的详细示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$servername = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码

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

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

// 删除数据库
$sql = "DROP DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    echo "数据库删除成功";
} else {
    echo "删除数据库错误: " . $conn->error;
}

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

完整示例：命令行、mysqladmin 和 PHP

使用命令行删除数据库

  1. 打开终端或命令行工具。

  2. 登录 MySQL：

    1
    mysql -u root -p

  3. 输入密码后，执行以下命令删除数据库：

    1
    DROP DATABASE myDB;

使用 mysqladmin 删除数据库

在终端或命令行工具中，使用以下命令：

1
mysqladmin -u root -p drop myDB

系统会提示输入密码，并确认删除操作，输入 y 确认删除。

使用 PHP 脚本删除数据库

以下是一个完整的 PHP 脚本示例，用于连接 MySQL 并删除数据库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$servername = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码

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

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

// 删除数据库
$sql = "DROP DATABASE myDB";
if ($conn->query($sql) === TRUE) {
    echo "数据库删除成功";
} else {
    echo "删除数据库错误: " . $conn->error;
}

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

PHP 脚本删除数据库，并包含更多错误处理

在实际应用中，添加更多的错误处理和日志记录是个好习惯。以下示例展示了如何在删除数据库时包含详细的错误处理：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<?php
$servername = "localhost";
$username = "username";
$password = "password";

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

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

// 检查数据库是否存在
$sql = "SHOW DATABASES LIKE 'myDB'";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    // 删除数据库
    $sql = "DROP DATABASE myDB";
    if ($conn->query($sql) === TRUE) {
        echo "数据库删除成功<br>";
    } else {
        echo "删除数据库错误: " . $conn->error . "<br>";
    }
} else {
    echo "数据库 myDB 不存在<br>";
}

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

总结

  • 可以使用命令行、mysqladmin 工具和 PHP 脚本来删除 MySQL 数据库。
  • 命令行使用 DROP DATABASE 语句删除数据库。
  • mysqladmin 工具提供了简单的命令来删除数据库。
  • PHP 脚本使用 mysqli 扩展来连接 MySQL 并执行 SQL 语句删除数据库。

MySQL 选择数据库

使用命令行选择数据库

在 MySQL 命令行中选择数据库，可以使用 USE 语句。这个命令会将指定的数据库设置为当前会话的默认数据库。

1
USE myDB;

使用 PHP 脚本选择数据库

在 PHP 中，可以使用 mysqli 类的 select_db 方法来选择数据库。下面是详细的示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
$servername = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码
$dbname = "myDB"; // 替换为你要选择的数据库名称

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

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

// 选择数据库
if ($conn->select_db($dbname) === TRUE) {
    echo "数据库选择成功";
} else {
    echo "选择数据库错误: " . $conn->error;
}

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

完整示例：命令行和 PHP

使用命令行选择数据库

  1. 打开终端或命令行工具。

  2. 登录 MySQL：

    1
    mysql -u root -p

  3. 输入密码后，执行以下命令选择数据库：

    1
    USE myDB;

    当命令成功执行后，所有后续的 SQL 语句都会在 myDB 数据库上执行。

使用 PHP 脚本选择数据库

以下是一个完整的 PHP 脚本示例，用于连接 MySQL 并选择数据库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
<?php
$servername = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码
$dbname = "myDB"; // 替换为你要选择的数据库名称

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

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

// 选择数据库
if ($conn->select_db($dbname)) {
    echo "数据库选择成功<br>";

    // 示例查询
    $sql = "SELECT * FROM some_table"; // 替换为你实际的表名
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // 输出数据
        while($row = $result->fetch_assoc()) {
            echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
        }
    } else {
        echo "0 结果<br>";
    }
} else {
    echo "选择数据库错误: " . $conn->error . "<br>";
}

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

PHP 脚本选择数据库并包含错误处理

在实际应用中，添加更多的错误处理和日志记录是个好习惯。以下示例展示了如何在选择数据库时包含详细的错误处理：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

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

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

// 选择数据库
if ($conn->select_db($dbname)) {
    echo "数据库选择成功<br>";
} else {
    echo "选择数据库错误: " . $conn->error . "<br>";
}

// 示例查询
$sql = "SELECT * FROM some_table"; // 替换为你实际的表名
if ($result = $conn->query($sql)) {
    if ($result->num_rows > 0) {
        // 输出数据
        while($row = $result->fetch_assoc()) {
            echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
        }
    } else {
        echo "0 结果<br>";
    }
    // 释放结果集
    $result->free();
} else {
    echo "查询错误: " . $conn->error . "<br>";
}

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

总结

  • 可以使用命令行和 PHP 脚本来选择 MySQL 数据库。
  • 在命令行中使用 USE 语句选择数据库。
  • 在 PHP 脚本中使用 mysqli 类的 select_db 方法选择数据库。
  • 示例展示了如何在不同环境中选择数据库，并包括了错误处理和示例查询。

MySQL 数据类型

在 MySQL 中，数据类型用于指定可以在表中存储的特定类型的数据。选择适当的数据类型对数据库的性能和数据完整性至关重要。以下是常见的 MySQL 数据类型及其详细说明和示例。

数值数据类型

数据类型 描述 范围 (有符号) 范围 (无符号)
TINYINT 非常小的整数 -128 到 127 0 到 255
SMALLINT 小整数 -32,768 到 32,767 0 到 65,535
MEDIUMINT 中等大小的整数 -8,388,608 到 8,388,607 0 到 16,777,215
INT 标准整数 -2,147,483,648 到 2,147,483,647 0 到 4,294,967,295
BIGINT 大整数 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807 0 到 18,446,744,073,709,551,615
FLOAT 单精度浮点数 -3.402823466E+38 到 3.402823466E+38 同左
DOUBLE 双精度浮点数 -1.7976931348623157E+308 到 1.7976931348623157E+308 同左
DECIMAL 定点数 取决于定义 同左

示例

1
2
3
4
5
6
7
8
9
CREATE TABLE numbers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tiny TINYINT,
    small SMALLINT,
    medium MEDIUMINT,
    normal INT,
    big BIGINT,
    price DECIMAL(10, 2)
);

字符串数据类型

数据类型 描述 长度
CHAR(size) 固定长度字符串 0 到 255 字节
VARCHAR(size) 可变长度字符串 0 到 65,535 字节
TINYTEXT 非常小的文本字符串 0 到 255 字节
TEXT 小文本字符串 0 到 65,535 字节
MEDIUMTEXT 中等长度文本字符串 0 到 16,777,215 字节
LONGTEXT 长文本字符串 0 到 4,294,967,295 字节

示例

1
2
3
4
5
CREATE TABLE texts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    description TEXT
);

日期和时间数据类型

数据类型 描述 格式 范围
DATE 日期 YYYY-MM-DD 1000-01-01 到 9999-12-31
DATETIME 日期和时间 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 到 9999-12-31 23:59:59
TIMESTAMP 时间戳 YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC
TIME 时间 HH:MM:SS -838:59:59 到 838:59:59
YEAR 年份 YYYY 1901 到 2155

示例

1
2
3
4
5
6
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_date DATE,
    event_time TIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

枚举与集合类型（Enumeration and Set Types）

数据类型 描述
ENUM 枚举类型，用于存储单一值，可以选择一个预定义的集合
SET 集合类型，用于存储多个值，可以选择多个预定义的集合

示例

1
2
3
4
5
CREATE TABLE preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status ENUM('active', 'inactive', 'pending'),
    colors SET('red', 'green', 'blue')
);

在上述示例中，status 列可以存储 'active', 'inactive', 'pending' 之一，而 colors 列可以存储 'red', 'green', 'blue' 的任意组合。

空间数据类型（Spatial Data Types）

数据类型 描述
GEOMETRY 几何对象
POINT 点对象
LINESTRING 线对象
POLYGON 多边形对象
MULTIPOINT 多点对象
MULTILINESTRING 多线对象
MULTIPOLYGON 多多边形对象
GEOMETRYCOLLECTION 几何集合对象

示例

1
2
3
4
5
CREATE TABLE spatial_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location POINT,
    boundary POLYGON
) ENGINE=MyISAM;

在上述示例中，location 列存储点对象，而 boundary 列存储多边形对象。注意，使用空间数据类型的表通常需要使用 MyISAM 存储引擎。

布尔和枚举类型

数据类型 描述
BOOLEAN 布尔值 (1 或 0)
ENUM 枚举类型，用于存储一个预定义的字符串集合

示例

1
2
3
4
5
CREATE TABLE preferences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    is_active BOOLEAN,
    color ENUM('red', 'green', 'blue')
);

总结

  • INT: 整数类型，适用于存储整数值。
  • VARCHAR(size): 可变长度字符串，适用于存储长度可变的字符串。
  • CHAR(size): 固定长度字符串，适用于存储固定长度的字符串。
  • TEXT: 大文本，适用于存储大量文本数据。
  • DATE: 日期，适用于存储日期值。
  • TIMESTAMP: 时间戳，适用于存储日期和时间值。
  • ENUM: 枚举类型，适用于存储单一值，从预定义集合中选择。
  • SET: 集合类型，适用于存储多个值，从预定义集合中选择多个。
  • GEOMETRY: 几何对象，用于存储空间数据。
  • POINT, LINESTRING, POLYGON: 分别用于存储点、线和多边形对象。

MySQL 创建数据表

在 MySQL 中，可以使用 SQL 命令来创建数据表。以下是使用命令行和 PHP 脚本创建数据表的示例。

使用命令行创建数据表

1
2
3
4
5
6
CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
);

在上述示例中：

  • 创建了名为 Users 的数据表。
  • 数据表包含四个列：id, username, email, 和 reg_date。
  • id 列是主键，使用 AUTO_INCREMENT 自增。
  • username 列是 VARCHAR(50) 类型，不允许为空 (NOT NULL)。
  • email 列是 VARCHAR(50) 类型，允许为空。
  • reg_date 列是 TIMESTAMP 类型，用于存储注册日期。

在 PHP 中创建数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
$sql = "CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
    echo "数据表创建成功";
} else {
    echo "创建数据表错误: " . $conn->error;
}
?>

在上述 PHP 脚本中：

  • 使用 SQL 查询创建了名为 Users 的数据表。
  • 通过 query() 方法执行 SQL 查询。
  • 如果创建成功，输出 "数据表创建成功"；如果失败，输出创建错误信息。

完整示例

下面是一个完整的 PHP 脚本示例，用于连接 MySQL 并创建数据表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<?php
$servername = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码
$dbname = "myDB"; // 替换为你的数据库名称

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

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

// 创建数据表的 SQL 语句
$sql = "CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(50),
    reg_date TIMESTAMP
)";

// 执行创建数据表的 SQL 查询
if ($conn->query($sql) === TRUE) {
    echo "数据表创建成功";
} else {
    echo "创建数据表错误: " . $conn->error;
}

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

MySQL 删除数据表

删除数据表是一个常见的操作，可以通过命令行或在 PHP 中执行 SQL 查询来完成。以下是使用命令行和 PHP 脚本删除数据表的示例。

使用命令行删除数据表

1
DROP TABLE Users;

在上述示例中，使用 DROP TABLE 命令来删除名为 Users 的数据表。

在 PHP 中删除数据表

1
2
3
4
5
6
$sql = "DROP TABLE Users";
if ($conn->query($sql) === TRUE) {
    echo "数据表删除成功";
} else {
    echo "删除数据表错误: " . $conn->error;
}

在上述 PHP 脚本中：

  • 使用 SQL 查询 DROP TABLE 来删除名为 Users 的数据表。
  • 通过 query() 方法执行 SQL 查询。
  • 如果删除成功，输出 "数据表删除成功"；如果失败，输出删除错误信息。

完整示例

下面是一个完整的 PHP 脚本示例，用于连接 MySQL 并删除数据表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<?php
$servername = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码
$dbname = "myDB"; // 替换为你的数据库名称

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

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

// 删除数据表的 SQL 语句
$sql = "DROP TABLE Users";

// 执行删除数据表的 SQL 查询
if ($conn->query($sql) === TRUE) {
    echo "数据表删除成功";
} else {
    echo "删除数据表错误: " . $conn->error;
}

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

删除数据表的原理

删除数据表会永久删除表的结构以及表中的所有数据。当你执行 DROP TABLE 命令时，MySQL 将删除指定的表，包括表的所有列、索引、约束等。因此，在执行删除操作之前，请确保你不再需要该表及其数据。

注意事项

  • 删除数据表是一个不可逆的操作，谨慎使用。
  • 在删除数据表之前，请确保你已经备份了重要的数据。
  • 使用 DROP TABLE 命令删除数据表时，所有相关的索引、外键约束等也将被删除。
  • 在执行删除操作时，确保数据库连接正常，以避免意外中断造成的数据丢失。

MySQL 插入数据

插入数据是向数据库表中添加新记录的过程。可以通过命令行或在 PHP 中执行 SQL 查询来实现。以下是使用命令行和 PHP 脚本插入数据的示例。

使用命令行插入数据

1
2
INSERT INTO Users (username, email, reg_date)
VALUES ('JohnDoe', 'john@example.com', NOW());

在上述示例中：

  • 使用 INSERT INTO 语句向名为 Users 的数据表插入新记录。
  • 指定了要插入的列名 username, email, reg_date。
  • 使用 VALUES 关键字指定要插入的值，其中 NOW() 函数用于将当前日期和时间插入 reg_date 列。

语法解释

mysqli_query 是 PHP 中用于执行 MySQL 查询的函数。下面是它的语法和参数说明：

语法

1
mysqli_query(connection, query, resultmode);

参数

  • connection：必需。指定要使用的 MySQL 连接。通常是通过 mysqli_connect 或 mysqli_init 函数创建的连接对象。

  • query：必需。要执行的查询字符串，即要发送到 MySQL 数据库执行的 SQL 查询语句。

  • resultmode：可选。一个常量，用于指定结果集的处理模式。可选的值有：

    • MYSQLI_USE_RESULT：如果查询结果集很大，应该使用这个模式来逐行获取结果，以节省内存。适用于一次性获取数据量较大的查询结果。

    • MYSQLI_STORE_RESULT：默认值。将整个结果集存储在客户端内存中，并返回一个结果对象，适用于一次性获取数据量较小的查询结果。

剖析

mysqli_query 函数用于执行 SQL 查询，并返回执行结果。它通过连接参数指定要使用的 MySQL 连接，并通过查询参数指定要执行的 SQL 查询语句。执行成功后，可以根据需要选择不同的结果集处理模式，使用 MYSQLI_USE_RESULT 模式逐行获取大量数据，或使用 MYSQLI_STORE_RESULT 模式一次性获取较小的数据量。

这个函数非常灵活，可以执行各种类型的查询，包括插入、更新、删除和选择操作。在执行查询之前，需要确保已经建立了有效的数据库连接，并且查询语句符合 MySQL 的语法规则。

在 PHP 中插入数据

1
2
3
4
5
6
7
$sql = "INSERT INTO Users (username, email, reg_date)
VALUES ('JohnDoe', 'john@example.com', NOW())";
if ($conn->query($sql) === TRUE) {
    echo "新记录插入成功";
} else {
    echo "插入记录错误: " . $conn->error;
}

在上述 PHP 脚本中：

  • 使用 SQL 查询 INSERT INTO 来向名为 Users 的数据表插入新记录。
  • 通过 query() 方法执行 SQL 查询。
  • 如果插入成功，输出 "新记录插入成功"；如果失败，输出插入错误信息。

完整示例

下面是一个完整的 PHP 脚本示例，用于连接 MySQL 并插入数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
<?php
$servername = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码
$dbname = "myDB"; // 替换为你的数据库名称

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

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

// 插入数据的 SQL 语句
$sql = "INSERT INTO Users (username, email, reg_date)
VALUES ('JohnDoe', 'john@example.com', NOW())";

// 执行插入数据的 SQL 查询
if ($conn->query($sql) === TRUE) {
    echo "新记录插入成功";
} else {
    echo "插入记录错误: " . $conn->error;
}

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

插入数据的原理

插入数据操作将新的记录添加到数据库表中。通过使用 INSERT INTO 语句，可以向指定的表中插入新的行。在 VALUES 子句中，指定了要插入的值，可以使用常量、表达式或函数返回的值。在执行插入操作时，数据库会为新记录分配一个唯一的标识符（如自增长的主键）。

注意事项

  • 在插入数据之前，确保数据库连接正常，以避免插入错误。
  • 在插入数据时，确保列名与值的数量和顺序一致。
  • 可以使用 NOW() 函数将当前日期和时间插入到时间戳列中。
  • 插入数据时，确保数据的完整性和一致性，避免不必要的错误。

MySQL 查询数据

查询数据是数据库操作中的核心之一，用于从数据库表中检索所需的数据。可以通过命令行或在 PHP 中执行 SQL 查询来实现。以下是使用命令行和 PHP 脚本查询数据的示例。

使用命令行查询数据

1
SELECT * FROM Users;

在上述示例中，使用 SELECT 语句从名为 Users 的数据表中检索所有列的数据。

在 PHP 中查询数据

1
2
3
4
5
6
7
8
9
10
$sql = "SELECT id, username, email FROM Users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 结果";
}

在上述 PHP 脚本中：

  • 使用 SQL 查询 SELECT 从名为 Users 的数据表中选择 id, username, email 列的数据。
  • 通过 query() 方法执行 SQL 查询，将查询结果保存在 $result 变量中。
  • 使用 num_rows 方法检查是否有数据返回。
  • 使用 fetch_assoc() 方法逐行获取查询结果，并将每行数据以关联数组的形式存储在 $row 变量中。
  • 输出检索到的数据。

完整示例

下面是一个完整的 PHP 脚本示例，用于连接 MySQL 并查询数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?php
$servername = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码
$dbname = "myDB"; // 替换为你的数据库名称

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

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

// 查询数据的 SQL 语句
$sql = "SELECT id, username, email FROM Users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["username"]. " - Email: " . $row["email"]. "<br>";
    }
} else {
    echo "0 结果";
}

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

查询数据的原理

查询数据操作通过执行 SQL 查询语句从数据库表中检索所需的数据。在 PHP 中，可以使用 mysqli_query() 函数执行查询，并通过检索结果集来处理返回的数据。在执行查询之前，需要确保已经建立了有效的数据库连接，并且查询语句符合 MySQL 的语法规则。

注意事项

  • 在执行查询之前，确保数据库连接正常，以避免查询错误。
  • 使用 SELECT 查询时，确保选择正确的列和表。
  • 在处理查询结果时，确保检查是否有数据返回，以避免空结果集导致的错误。
  • 查询数据时，可以使用不同的查询条件、排序和限制来获取符合条件的数据。

示例 1：使用 WHERE 子句进行条件查询

1
SELECT * FROM Users WHERE username='JohnDoe';

在这个示例中，我们只检索用户名为 "JohnDoe" 的用户信息。

示例 2：使用 ORDER BY 子句进行排序

1
SELECT * FROM Users ORDER BY reg_date DESC;

在这个示例中，我们按注册日期（reg_date）降序排序检索用户信息，即最新注册的用户排在前面。

示例 3：使用 LIMIT 子句限制结果集数量

1
SELECT * FROM Users LIMIT 5;

这个示例中，我们只检索前 5 条用户信息，用于分页或限制查询结果数量。

示例 4：使用 WHERE 子句和逻辑运算符进行复杂条件查询

1
SELECT * FROM Users WHERE username='JohnDoe' AND email LIKE '%example.com';

这个示例中，我们检索用户名为 "JohnDoe" 并且邮箱以 "@example.com" 结尾的用户信息。

示例 5：使用 JOIN 连接多个表进行联合查询

1
2
3
SELECT Users.username, Orders.product_name
FROM Users
INNER JOIN Orders ON Users.id = Orders.user_id;

在这个示例中，我们连接名为 Users 和 Orders 的两个表，并检索用户与其对应订单的信息。

MySQL WHERE 子句

WHERE 子句用于过滤 SQL 查询结果，使得只有满足指定条件的行才会被返回。它可以应用于 SELECT, UPDATE, 或 DELETE 语句中，用于限制操作的范围。

WHERE 子句语法：

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE condition;
  • column1, column2, ...: 指定要返回的列名，可以是一个或多个列。
  • table_name: 指定要从中检索数据的表名。
  • condition: 指定过滤条件，仅返回满足条件的行。条件可以是列名与值之间的比较、逻辑运算符和 SQL 函数等组合。

WHERE 子句示例：

1
SELECT * FROM Users WHERE age > 18;

在这个示例中，WHERE 子句限制了只返回表 Users 中年龄大于 18 岁的用户记录。

WHERE 子句原理：

  • 查询执行时，会逐行检查表中的记录。
  • 对于每一行记录，都会对 WHERE 子句中的条件进行评估。
  • 如果条件为真（即满足条件），则该行将包含在查询结果中；否则，将被排除在外。

WHERE 子句示例扩展：

1
SELECT * FROM Orders WHERE amount > 1000 AND status = 'Pending';

这个示例展示了如何同时使用多个条件来过滤结果。只有订单金额大于 1000 并且状态为 "Pending" 的订单记录会被返回。

1
UPDATE Users SET status = 'Active' WHERE last_login < '2023-01-01';

这个示例展示了如何在 UPDATE 语句中使用 WHERE 子句，只有上次登录时间在 '2023-01-01' 之前的用户状态会被更新为 "Active"。

1
DELETE FROM Customers WHERE country = 'USA' AND status = 'Inactive';

这个示例展示了如何在 DELETE 语句中使用 WHERE 子句，只有国家为 "USA" 且状态为 "Inactive" 的客户记录会被删除。

通过使用 WHERE 子句，可以根据特定条件过滤出所需的数据，使得 SQL 查询更加灵活和有用。

以下是一个示例，演示了如何使用 mysqli_query() 函数执行 SQL 查询，并使用 mysqli_fetch_assoc() 函数逐行获取查询结果：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
<?php
// 连接到数据库
$servername = "localhost";
$username = "username"; // 替换为你的数据库用户名
$password = "password"; // 替换为你的数据库密码
$dbname = "myDB"; // 替换为你的数据库名称

$conn = new mysqli($servername, $username, $password, $dbname);

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

// 构造 SQL 查询语句
$sql = "SELECT * FROM runoob_tbl WHERE runoob_author = 'RUNOOB.COM'";

// 执行 SQL 查询
$result = $conn->query($sql);

// 检查查询结果是否为空
if ($result->num_rows > 0) {
    // 输出数据
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["runoob_id"]. " - Name: " . $row["runoob_title"]. " - Author: " . $row["runoob_author"]. "<br>";
    }
} else {
    echo "0 结果";
}

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

在这个示例中：

  • 首先，我们连接到数据库。
  • 然后，构造了一个 SQL 查询语句，查询名为 runoob_tbl 表中 runoob_author 字段值为 'RUNOOB.COM' 的所有记录。
  • 接着，执行 SQL 查询，并检查查询结果是否为空。
  • 最后，通过循环遍历查询结果，逐行输出数据。

你可以根据实际情况修改数据库连接信息和查询条件，以符合你的需求。

MySQL UPDATE 更新

UPDATE 语句用于修改表中的现有记录。它允许你更新表中的一列或多列数据，并可以根据指定的条件来选择要更新的记录。

UPDATE 语句语法：

1
2
3
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
  • table_name: 指定要更新数据的表名。
  • column1 = value1, column2 = value2, ...: 指定要更新的列和它们的新值。
  • condition: 指定更新的条件，仅有满足条件的记录会被更新。

UPDATE 语句示例：

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

这个示例将更新表 Users 中 id 为 1 的用户的邮箱地址为 'newemail@example.com'。

UPDATE 语句原理：

  • UPDATE 语句按条件选择表中的行，并将指定的列设置为新值。
  • 如果没有指定条件，则所有行都将被更新。
  • 如果指定了条件，但没有匹配到任何行，则不会进行更新操作。

示例扩展：

以下是一个更复杂的示例，演示了如何同时更新多列和使用多个条件：

1
2
3
UPDATE Users
SET email = 'newemail@example.com', status = 'Active'
WHERE age > 18 AND country = 'USA';

这个示例将表 Users 中年龄大于 18 岁且国家为 'USA' 的用户的邮箱地址更新为 'newemail@example.com'，并将状态设置为 'Active'。

更新结果：

id username email status
1 John newemail@example.com Active
2 Jane jane@example.com Inactive
3 Bob bob@example.com Active

通过使用 UPDATE 语句，你可以轻松地更新表中的记录，从而保持数据的准确性和完整性。

MySQL DELETE 语句

DELETE 语句用于从表中删除记录。它允许你根据指定的条件来删除表中的行。

DELETE 语句语法：

1
DELETE FROM table_name WHERE condition;
  • table_name: 指定要删除数据的表名。
  • condition: 指定删除的条件，仅有满足条件的记录会被删除。

DELETE 语句示例：

1
DELETE FROM Users WHERE id = 1;

这个示例将从表 Users 中删除 id 为 1 的用户记录。

DELETE 语句原理：

  • DELETE 语句按条件选择表中的行，并将其删除。
  • 如果没有指定条件，则所有行都将被删除。
  • 如果指定了条件，但没有匹配到任何行，则不会进行删除操作。

示例扩展：

以下是一个更复杂的示例，演示了如何使用多个条件来删除记录：

1
DELETE FROM Users WHERE age > 30 AND status = 'Inactive';

这个示例将从表 Users 中删除年龄大于 30 岁且状态为 'Inactive' 的用户记录。

删除结果：

执行以上示例后，表 Users 中的记录将会被删除，删除后的表可能如下所示：

id username email age status
1 John john@example.com 25 Active
2 Jane jane@example.com 35 Active
3 Bob bob@example.com 40 Active

通过使用 DELETE 语句，你可以轻松地从表中删除满足条件的记录，从而实现数据的管理和维护。

MySQL LIKE 子句

LIKE 子句用于在 WHERE 子句中搜索列中的指定模式。它允许你使用通配符来匹配文本数据。

LIKE 子句语法：

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;
  • column1, column2, ...: 指定要返回的列名，可以是一个或多个列。
  • table_name: 指定要从中检索数据的表名。
  • column: 指定要搜索的列名。
  • pattern: 指定要匹配的模式。可以使用 % 表示零个或多个字符，或者使用 _ 表示一个单个字符。

LIKE 子句示例：

1
SELECT * FROM Users WHERE username LIKE 'J%';

这个示例将返回表 Users 中用户名以字母 'J' 开头的所有用户记录。

LIKE 子句原理：

  • LIKE 子句使用通配符来进行模式匹配，其中 % 表示零个或多个字符，_ 表示一个单个字符。
  • 它对字符串进行模糊搜索，找到与指定模式匹配的结果。
  • LIKE 子句通常与 % 或 _ 通配符一起使用，以匹配特定的字符串模式。

示例扩展：

以下是一些更复杂的示例，演示了如何使用不同的通配符来匹配不同的字符串模式：

1
2
3
4
SELECT * FROM Users WHERE username LIKE 'J%'; -- 匹配以 'J' 开头的用户名
SELECT * FROM Users WHERE email LIKE '%@example.com'; -- 匹配以 '@example.com' 结尾的邮箱地址
SELECT * FROM Users WHERE username LIKE '_ohn'; -- 匹配以任意字符开头，然后是 'ohn' 的用户名
SELECT * FROM Users WHERE username LIKE 'J__n'; -- 匹配以 'J' 开头，然后是两个任意字符，最后是 'n' 的用户名

通过使用 LIKE 子句，你可以进行灵活的模式匹配，从而实现更精确的数据查询。

MySQL UNION

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。它将两个或多个查询的结果集合并为一个结果集，并且会自动去除重复的行。

UNION 操作语法：

1
2
3
4
5
SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
  • column1, column2, ...: 指定要返回的列名，可以是一个或多个列。
  • table1, table2, ...: 指定要从中检索数据的表名。
  • 每个 SELECT 语句中的列数和类型必须相同。

UNION 操作示例：

1
2
3
SELECT username FROM Users
UNION
SELECT product_name FROM Products;

这个示例将返回表 Users 和表 Products 中所有用户名和产品名称的唯一值，并自动去除重复的行。

UNION 操作原理：

  • UNION 操作符将两个或多个查询的结果集合并为一个结果集。
  • 它会自动去除重复的行，只保留唯一的结果。
  • 结果集中的列数和数据类型必须相同，否则会产生错误。

示例扩展：

以下是一个更复杂的示例，演示了如何使用 UNION 操作符合并多个查询的结果集：

1
2
3
SELECT username, email FROM Users WHERE age > 30
UNION
SELECT product_name, description FROM Products WHERE price > 100;

这个示例将返回年龄大于 30 岁的用户信息和价格大于 100 的产品信息，并自动去除重复的行。

示例：

假设我们有两个表，一个是 Users 表，包含用户的用户名和邮箱地址，另一个是 Products 表，包含产品的名称和描述。我们想要获取这两个表中所有用户名和产品名称的唯一值，并将它们合并为一个结果集。

我们可以使用 UNION 操作符来实现这一目标：

1
2
3
SELECT username FROM Users
UNION
SELECT product_name FROM Products;

这个查询将返回表 Users 和表 Products 中所有用户名和产品名称的唯一值，并自动去除重复的行。

结果：

用户名或产品名称
John
Jane
Bob
Laptop
Phone
Tablet

这个表格展示了合并后的结果集，其中包含了所有用户名和产品名称的唯一值。

通过使用 UNION 操作符，你可以将多个查询的结果合并为一个结果集，从而实现更灵活和高效的数据查询。

MySQL ORDER BY 语句

ORDER BY 语句用于对查询结果进行排序。它可以根据一个或多个列的值对结果进行排序，并可以指定升序（ASC）或降序（DESC）排序。

ORDER BY 语句语法：

1
2
3
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 ASC|DESC, column2 ASC|DESC, ...;
  • column1, column2, ...: 指定要返回的列名，可以是一个或多个列。
  • table_name: 指定要从中检索数据的表名。
  • ASC|DESC: 指定排序顺序，ASC 表示升序（默认），DESC 表示降序。

ORDER BY 语句示例：

1
SELECT * FROM Users ORDER BY age DESC;

这个示例将按照年龄降序排列返回表 Users 中的所有用户记录。

ORDER BY 语句原理：

  • ORDER BY 子句按照指定的列对结果集进行排序。
  • 可以对一个或多个列进行排序，当有多个列时，排序将按照列的顺序依次进行。
  • 默认情况下，排序是升序的（ASC），可以使用 DESC 关键字指定降序排序。

示例扩展：

以下是一个更复杂的示例，演示了如何对多个列进行排序：

1
SELECT * FROM Users ORDER BY country ASC, age DESC;

这个示例将首先按照国家名称升序排列，然后在每个国家内按照年龄降序排列返回表 Users 中的所有用户记录。

排序结果：

以下是一个排序后的示例结果：

id username age country
3 Bob 40 USA
1 John 35 UK
2 Jane 30 UK

这个表格展示了按照年龄降序和国家名称升序排列后的用户记录。

MySQL 分组

GROUP BY 子句用于将结果集按照一个或多个列进行分组，并且对每个分组应用聚合函数。

GROUP BY 语句语法：

1
2
3
SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...;
  • column1, column2, ...: 指定要进行分组的列名，可以是一个或多个列。
  • table_name: 指定要从中检索数据的表名。

GROUP BY 语句示例：

1
SELECT country, COUNT(*) FROM Customers GROUP BY country;

这个示例将根据客户所在的国家对客户进行分组，并计算每个国家的客户数量。

GROUP BY 语句原理：

  • GROUP BY 子句按照指定的列对结果集进行分组。
  • 分组后，可以使用聚合函数（例如 COUNT、SUM、AVG 等）对每个分组进行计算。
  • 如果在 SELECT 查询中使用了 GROUP BY 子句，则除了在 GROUP BY 子句中指定的列外，其他列都必须是聚合函数的参数。

示例扩展：

以下是一个更复杂的示例，演示了如何对多个列进行分组并应用聚合函数：

1
2
3
SELECT country, city, COUNT(*) AS num_customers, AVG(age) AS avg_age
FROM Customers
GROUP BY country, city;

这个示例将根据客户所在的国家和城市对客户进行分组，并计算每个国家和城市的客户数量以及平均年龄。

分组结果：

以下是一个分组后的示例结果：

country city num_customers avg_age
USA New York 2 35.5
USA Los Angeles 1 40
UK London 2 32.5

这个表格展示了按照国家和城市分组后的客户数量和平均年龄。 ***

MySQL 连接的使用

连接（JOIN）用于在多个表之间建立关系，以便在查询中检索相关数据。MySQL 支持不同类型的连接操作，如内连接、外连接和交叉连接等。

连接的基本语法：

1
2
3
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column;
  • table1, table2: 指定要连接的表名。
  • column1, column2, ...: 指定要返回的列名，可以是一个或多个列。
  • ON: 指定连接条件，即连接两个表的列。

内连接（INNER JOIN）示例：

1
2
3
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

这个示例将返回 Orders 表和 Customers 表中匹配的订单和客户信息。

连接类型及其含义：

连接类型 含义
INNER JOIN 返回两个表中匹配的行，如果没有匹配的行，则不返回任何结果。
LEFT JOIN 返回左表中的所有行，以及右表中匹配的行。如果没有匹配的行，则为右表返回 NULL 值。
RIGHT JOIN 返回右表中的所有行，以及左表中匹配的行。如果没有匹配的行，则为左表返回 NULL 值。
FULL JOIN 返回左表和右表中的所有行，如果没有匹配的行，则分别为左表和右表返回 NULL 值。

连接原理：

  • 连接操作通过将两个或多个表中的行关联起来，根据指定的连接条件检索相关数据。
  • 内连接（INNER JOIN）只返回匹配的行，而外连接（LEFT JOIN、RIGHT JOIN 和 FULL JOIN）返回匹配的行以及未匹配的行。

下面是对四种连接类型的详细剖析：

INNER JOIN（内连接）

内连接（INNER JOIN）返回两个表中匹配的行，如果没有匹配的行，则不返回任何结果。

语法：

1
2
3
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

示例：

1
2
3
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

这个示例将返回 Orders 表和 Customers 表中匹配的订单和客户信息。

LEFT JOIN（左连接）

左连接（LEFT JOIN）返回左表中的所有行，以及右表中匹配的行。如果没有匹配的行，则为右表返回 NULL 值。

语法：

1
2
3
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

示例：

1
2
3
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

这个示例将返回所有客户及其对应的订单信息，如果客户没有订单，则订单信息为 NULL。

RIGHT JOIN（右连接）

右连接（RIGHT JOIN）返回右表中的所有行，以及左表中匹配的行。如果没有匹配的行，则为左表返回 NULL 值。

语法：

1
2
3
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

示例：

1
2
3
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

这个示例将返回所有订单及其对应的客户信息，如果订单没有对应的客户，则客户信息为 NULL。

FULL JOIN（全连接）

全连接（FULL JOIN）返回左表和右表中的所有行，如果没有匹配的行，则分别为左表和右表返回 NULL 值。

语法：

1
2
3
SELECT column1, column2, ...
FROM table1
FULL JOIN table2 ON table1.column = table2.column;

示例：

1
2
3
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

这个示例将返回所有客户和订单信息，如果客户没有订单或订单没有客户，则相应信息为 NULL。

通过连接操作，你可以在多个表之间建立关系，并根据需要检索相关数据，从而实现更灵活和高效的数据查询。

MySQL NULL 值处理

在 MySQL 中，NULL 值用于表示缺少值或未知值。当某个字段的值未知或不适用时，可以使用 NULL 值进行表示。NULL 值不同于空字符串或数字 0，它表示的是一个缺失的值。

NULL 值的基本特点：

  • NULL 值表示缺失的值或未知的值。
  • NULL 值可以用于任何数据类型，包括字符串、数字、日期等。
  • 当查询结果中存在 NULL 值时，可以使用 IS NULL 或 IS NOT NULL 条件来判断该字段是否为 NULL。

处理 NULL 值的方法：

  1. 使用 IS NULL 条件来判断字段是否为 NULL。
  2. 使用 IS NOT NULL 条件来判断字段是否不为 NULL。
  3. 使用 COALESCE 函数将 NULL 值替换为指定的默认值。
  4. 使用 IFNULL 函数将 NULL 值替换为指定的默认值。

示例：

假设我们有一个名为 students 的表，其中包含学生的姓名和分数。某些学生可能没有分数记录。

1
2
3
4
5
6
7
8
CREATE TABLE students (
    name VARCHAR(50),
    score INT
);

INSERT INTO students (name, score) VALUES ('Alice', 85);
INSERT INTO students (name, score) VALUES ('Bob', NULL);
INSERT INTO students (name, score) VALUES ('Charlie', 90);

现在我们来查询该表中的数据，并处理其中的 NULL 值：

1
2
3
SELECT name, 
       IFNULL(score, 'N/A') AS score
FROM students;

这个查询将返回学生的姓名和分数，并将 NULL 值替换为 'N/A'。

结果：

name score
Alice 85
Bob N/A
Charlie 90

这个表格展示了处理 NULL 值后的查询结果。对于没有分数记录的学生，分数列显示为 'N/A'。

MySQL 正则表达式

MySQL 支持正则表达式，可以用于模式匹配和文本搜索。使用正则表达式可以实现更灵活和精确的匹配规则，从而满足各种复杂的搜索需求。

正则表达式的基本语法：

MySQL 中使用 REGEXP 或 RLIKE 关键字来执行正则表达式匹配操作。

1
2
3
SELECT column1, column2, ...
FROM table_name
WHERE column_name REGEXP pattern;
  • column1, column2, ...: 指定要返回的列名，可以是一个或多个列。
  • table_name: 指定要从中检索数据的表名。
  • column_name: 指定要进行正则表达式匹配的列名。
  • pattern: 指定正则表达式模式，用于匹配数据。

正则表达式的模式：

正则表达式模式由一系列字符和元字符组成，用于描述匹配规则。以下是一些常用的元字符：

  • .：匹配任意单个字符。
  • ^：匹配字符串的开始位置。
  • $：匹配字符串的结束位置。
  • []：匹配字符集中的任意一个字符。
  • [^]：匹配除了字符集中的任意一个字符以外的字符。
  • *：匹配前面的字符零次或多次。
  • +：匹配前面的字符一次或多次。
  • ?：匹配前面的字符零次或一次。
  • {n}：匹配前面的字符恰好 n 次。
  • {n,}：匹配前面的字符至少 n 次。
  • {n,m}：匹配前面的字符至少 n 次，但不超过 m 次。

示例：

假设我们有一个名为 employees 的表，其中包含员工的姓名和职位信息。现在我们想要查找职位中包含 "manager" 的所有员工。

1
2
3
SELECT name, position
FROM employees
WHERE position REGEXP 'manager';

这个查询将返回所有职位中包含 "manager" 的员工信息。

结果：

name position
Alice Sales Manager
Bob Product Manager
Charlie Project Manager

这个表格展示了所有职位中包含 "manager" 的员工信息。

通过使用正则表达式，我们可以实现更灵活和精确的模式匹配，从而实现各种复杂的文本搜索和匹配需求。

MySQL 事务

事务（Transaction）是由一组 SQL 语句组成的一个操作序列，它们被视为一个单一的工作单元。事务要么全部执行成功，要么全部失败并回滚到事务开始之前的状态。事务的目的是确保数据库的一致性和完整性。

事务的基本特性：

  1. 原子性（Atomicity）：事务是一个原子操作单元，要么全部执行成功，要么全部失败回滚。如果事务中的任何一个操作失败，则整个事务将被回滚，数据库状态将恢复到事务开始之前的状态。

  2. 一致性（Consistency）：事务执行结束后，数据库状态应该是一致的。即使事务失败回滚，数据库也不会处于一个不一致的状态。

  3. 隔离性（Isolation）：事务的执行应该是相互隔离的，即一个事务的执行不应该影响其他事务。事务的隔离性可以通过并发控制来实现，以防止事务间的相互干扰。

  4. 持久性（Durability）：一旦事务提交成功，其所做的修改将永久保存在数据库中，即使系统发生故障或崩溃，数据也不会丢失。

事务的控制语句：

MySQL 提供了以下几个关键字来控制事务的开始、提交和回滚：

  • BEGIN 或 START TRANSACTION：开始一个事务。
  • COMMIT：提交事务，将事务中的操作永久保存到数据库。
  • ROLLBACK：回滚事务，撤销事务中的操作，恢复到事务开始之前的状态。

示例：

假设我们有一个名为 accounts 的表，其中存储着用户的账户余额信息。现在我们要进行一系列转账操作，保证转账过程的原子性。

1
2
3
4
5
6
BEGIN; -- 开始事务

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; -- 用户 1 转出 100 元
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; -- 用户 2 收到 100 元

COMMIT; -- 提交事务

这个示例中的操作将被视为一个整体，要么全部执行成功，要么全部失败并回滚。这样可以确保转账操作的原子性，即用户 1 转出的金额将完整地到达用户 2 的账户。

通过使用事务，我们可以确保数据库操作的一致性和完整性，保护数据的安全性。

MySQL ALTER 命令

ALTER 命令用于修改数据库表的结构，包括添加、删除或修改列等操作。通过 ALTER 命令，可以对现有的表进行结构调整，以满足新的需求或改进数据库设计。

常见的 ALTER 命令操作：

  1. 添加列（Add Column）：向表中添加新的列。
  2. 删除列（Drop Column）：从表中删除指定的列。
  3. 修改列（Modify Column）：修改表中已存在列的数据类型或属性。
  4. 重命名表（Rename Table）：修改表的名称。

ALTER 命令的语法：

1
2
3
4
5
ALTER TABLE table_name
  ADD column_name datatype [after existing_column_name],
  DROP column_name,
  MODIFY column_name new_datatype [constraints],
  RENAME TO new_table_name;
  • table_name：指定要修改的表名。
  • ADD column_name datatype：添加新列，指定列名和数据类型。
  • DROP column_name：删除指定列。
  • MODIFY column_name new_datatype：修改指定列的数据类型。
  • RENAME TO new_table_name：重命名表。

示例：

假设我们有一个名为 employees 的表，其中包含员工的姓名和年龄信息。现在我们要向该表中添加一个新的列 department，表示员工所属部门。

1
2
ALTER TABLE employees
  ADD department VARCHAR(50) AFTER age;

这个示例将向 employees 表中添加一个新的 department 列，并将其放置在 age 列之后。

结果：

name age department
Alice 30 HR
Bob 35 IT
Charlie 40 Marketing

这个表格展示了添加了新列 department 后的 employees 表的结构。

通过使用 ALTER 命令，可以对数据库表的结构进行灵活调整，以适应不断变化的需求。

MySQL 索引

索引在数据库中起着重要的作用，可以加快数据的检索速度。MySQL 中可以在一列或多列上创建索引，以提高查询效率。

索引的原理：

索引是一种数据结构，类似于书籍的目录，它存储了表中数据列的值与其对应行的物理地址之间的映射关系。通过索引，数据库可以直接定位到满足查询条件的数据，而不必逐行扫描整个表。

常见的索引类型：

  1. 单列索引（Single-Column Index）：基于单个列的索引。
  2. 唯一索引（Unique Index）：索引列的值必须是唯一的，用于保证数据的唯一性。
  3. 复合索引（Composite Index）：基于多个列组合而成的索引，用于支持多列的查询条件。
  4. 全文索引（Full-Text Index）：用于对文本内容进行全文搜索。

创建索引的语法：

1
2
CREATE INDEX index_name
ON table_name (column1, column2, ...);
  • index_name：指定索引的名称。
  • table_name：指定要在哪个表上创建索引。
  • (column1, column2, ...)：指定要创建索引的列名，可以是单列或多列。

示例：

假设我们有一个名为 employees 的表，其中包含员工的姓名和年龄信息。现在我们要在 name 列上创建一个单列索引，以加快对员工姓名的检索速度。

1
CREATE INDEX idx_name ON employees (name);

这个示例将在 employees 表的 name 列上创建一个名为 idx_name 的单列索引。

通过合理地创建索引，可以显著提高数据库的查询性能，特别是在数据量较大的情况下。但是需要注意的是，过多或不必要的索引可能会导致写操作的性能下降，因此需要根据实际情况进行权衡和选择。

MySQL 临时表

临时表是在会话级别创建的表，它们的生命周期仅限于当前会话。当会话结束时，临时表会自动被删除，不会影响其他会话中的表。

创建临时表的语法：

1
2
3
4
5
CREATE TEMPORARY TABLE temp_table_name (
    column1 datatype,
    column2 datatype,
    ...
);
  • temp_table_name：指定临时表的名称。
  • column1, column2, ...：指定临时表的列名和数据类型。

特点和用途：

  1. 作用范围：临时表仅在创建它们的会话中存在，当会话结束时自动被删除。因此，它们对其他会话是不可见的，不会造成命名冲突。

  2. 表的类型：临时表可以是局部临时表（只能在当前会话中使用）或全局临时表（对所有会话可见，但只有创建它的会话可以访问）。

  3. 适用场景：临时表通常用于在会话中存储中间结果，或者在复杂的数据处理过程中暂时存储数据。它们还可以用于模拟数据库中的临时数据集，以便执行特定的查询操作。

  4. 表结构：临时表可以具有与常规表相同的结构，包括列、索引、约束等。

示例：

假设我们需要在会话中存储一些临时数据，并对其进行查询操作。我们可以通过创建临时表来实现：

1
2
3
4
5
6
7
8
CREATE TEMPORARY TABLE temp_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO temp_data (name) VALUES ('Alice'), ('Bob'), ('Charlie');

SELECT * FROM temp_data;

这个示例创建了一个名为 temp_data 的临时表，向表中插入了一些数据，并对其进行查询操作。当会话结束时，临时表会自动被删除，不会影响其他会话中的表。

通过使用临时表，我们可以在数据库中临时存储和处理数据，而无需创建永久性的表结构。这在一些临时性的数据处理任务中非常有用，可以提高数据库的灵活性和效率。

MySQL 复制表

在 MySQL 中，可以使用 CREATE TABLE ... SELECT 语句来复制一个表的结构和数据到一个新表中。

复制表的语法：

1
2
3
4
CREATE TABLE new_table_name
SELECT *
FROM original_table_name
WHERE condition;
  • new_table_name：指定新表的名称。
  • original_table_name：指定要复制的原始表的名称。
  • SELECT * FROM original_table_name：使用 SELECT 语句从原始表中选择所有的行和列。
  • WHERE condition：可选项，用于指定选择哪些行进行复制。

特点和用途：

  1. 结构和数据：CREATE TABLE ... SELECT 语句不仅复制了原始表的结构，还复制了其中的数据。

  2. 灵活性：可以通过添加 WHERE 子句来选择性地复制原始表中的部分数据。

  3. 快速：复制表的过程在 MySQL 中是非常高效的，特别是对于大型表而言。

示例：

假设我们有一个名为 employees 的表，包含了员工的姓名、年龄和部门信息。现在我们要复制这个表的结构和数据到一个新表 employees_copy 中：

1
2
3
CREATE TABLE employees_copy
SELECT *
FROM employees;

这个示例将复制 employees 表的结构和数据到一个名为 employees_copy 的新表中。

通过使用 CREATE TABLE ... SELECT 语句，我们可以快速、方便地复制一个表的结构和数据，从而满足数据库操作中的各种需求。

MySQL 元数据

在 MySQL 中，元数据是描述数据库对象的数据，包括表、列、索引、视图、存储过程等信息。通过元数据，可以了解数据库的结构、属性和关系，从而进行数据库管理和操作。

常见的元数据查询：

  1. 查看所有表：
1
SHOW TABLES;
  1. 查看表结构：
1
DESCRIBE table_name;
  1. 查看索引信息：
1
SHOW INDEX FROM table_name;
  1. 查看列信息：
1
SHOW COLUMNS FROM table_name;
  1. 查看视图信息：
1
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

示例：

假设我们有一个名为 employees 的表，我们可以通过以下示例查询该表的元数据信息：

1
2
3
4
5
6
7
8
9
10
11
-- 查看所有表
SHOW TABLES;

-- 查看表结构
DESCRIBE employees;

-- 查看索引信息
SHOW INDEX FROM employees;

-- 查看列信息
SHOW COLUMNS FROM employees;

通过这些元数据查询语句，我们可以了解数据库中的对象结构、属性和关系，帮助我们更好地进行数据库管理和操作。

通过查询元数据，可以更好地了解和管理数据库，从而提高数据库的可靠性、性能和安全性。 ***

MySQL 序列

在 MySQL 中，虽然没有内置的序列对象，但可以通过 AUTO_INCREMENT 关键字来实现类似序列的功能。

使用 AUTO_INCREMENT 实现序列：

1
2
3
4
5
6
CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column1 datatype,
    column2 datatype,
    ...
);
  • id 列使用 AUTO_INCREMENT 关键字进行定义，表示自动递增的列。
  • 每次向表中插入一条记录时，id 列的值会自动递增，保证了其在表中的唯一性。

特点和用途：

  1. 自动生成唯一值：通过使用 AUTO_INCREMENT，可以自动生成唯一的递增值，用作表的主键或其他唯一标识符。

  2. 简化数据插入：无需手动为每条记录分配唯一的标识符，数据库会自动为新记录生成递增的值，简化了数据插入的过程。

  3. 保证数据完整性：AUTO_INCREMENT 保证了生成的值的唯一性，并且在数据库级别上实现了锁定机制，确保多个并发插入操作不会导致重复值的产生。

示例：

假设我们有一个名为 employees 的表，其中的 id 列使用 AUTO_INCREMENT 关键字进行定义：

1
2
3
4
5
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

在插入数据时，无需为 id 列指定值，数据库会自动生成唯一的递增值：

1
INSERT INTO employees (name, age) VALUES ('Alice', 30), ('Bob', 35), ('Charlie', 25);

这样，每个新插入的记录都会自动分配一个唯一的 id 值，保证了表中的数据完整性和唯一性。

通过使用 AUTO_INCREMENT，可以方便地实现类似序列的功能，为表中的记录分配唯一的标识符，简化了数据管理和操作。

MySQL 处理重复数据

在 MySQL 中，可以使用 DISTINCT 关键字来去除查询结果中的重复行，从而处理重复数据。

使用 DISTINCT 去除重复数据：

1
2
SELECT DISTINCT column1, column2, ...
FROM table_name;
  • DISTINCT 关键字用于指示查询结果中不包含重复的行。
  • 可以在 SELECT 语句中的列列表中使用 DISTINCT 关键字，表示去除指定列中的重复值。

原理和用途：

  1. 去除重复行：DISTINCT 关键字用于去除查询结果集中的重复行，保证了返回的结果集中每一行都是唯一的。

  2. 适用范围：DISTINCT 关键字可以应用于任何查询，无论是简单的 SELECT 查询还是包含聚合函数的复杂查询。

示例：

假设我们有一个名为 customers 的表，其中包含了客户的姓名和所在城市。现在我们想要查询所有不重复的城市列表：

1
2
SELECT DISTINCT city
FROM customers;

这个查询将返回表 customers 中所有不重复的城市名称。

通过使用 DISTINCT 关键字，可以方便地处理重复数据，保证查询结果的唯一性和准确性。

MySQL 及 SQL 注入

SQL 注入是一种常见的安全漏洞，攻击者可以通过恶意构造的 SQL 查询来利用程序对数据库的访问权限，从而获取、修改或删除数据库中的数据。在 MySQL 中，防止 SQL 注入是非常重要的安全措施之一。

原理：

SQL 注入利用了程序未能正确过滤用户输入数据的漏洞，使攻击者可以将恶意 SQL 代码注入到程序中，从而执行未授权的数据库操作。常见的攻击方式包括：

  1. 无过滤的用户输入：当程序未对用户输入进行充分验证和过滤时，攻击者可以通过在输入框中插入恶意的 SQL 代码来执行不受控制的数据库操作。

  2. 拼接 SQL 语句：当程序使用字符串拼接的方式构建 SQL 查询时，如果未对输入数据进行适当的转义处理，攻击者可以通过在字符串中插入特殊字符来改变 SQL 查询的语义，从而执行恶意操作。

预防措施：

为了防止 SQL 注入攻击，可以采取以下预防措施：

  1. 使用参数化查询：使用预编译的 SQL 语句和参数绑定的方式来执行数据库查询，确保输入数据不会被解释为 SQL 代码，从而防止注入攻击。

  2. 输入验证和过滤：对用户输入数据进行严格的验证和过滤，确保只允许合法的数据输入，并对特殊字符进行转义或过滤。

  3. 最小权限原则：为数据库用户分配最小权限，避免使用具有过高权限的数据库账户，从而降低攻击者利用 SQL 注入漏洞的风险。

  4. 安全编码实践：开发人员应遵循安全编码规范，避免使用动态拼接 SQL 查询语句，使用安全的数据库访问接口和框架，及时更新和修补程序中的安全漏洞。

示例：

假设一个简单的登录页面，用于用户验证：

1
2
3
4
$username = $_POST['username'];
$password = $_POST['password'];

$sql = "SELECT * FROM users WHERE username='$username' AND password='$password'";

这段代码存在 SQL 注入漏洞，攻击者可以通过在用户名或密码中插入恶意 SQL 代码来绕过身份验证，例如：

1
2
username: admin' OR '1'='1
password: anything

攻击者可以通过这种方式绕过密码验证，成功登录到系统中。

通过采取预防措施，可以有效地防止 SQL 注入攻击，保护数据库和应用程序的安全。

MySQL 导出数据

在 MySQL 中，可以使用 mysqldump 命令将数据库中的数据导出到 SQL 文件中，以便备份数据或在不同的数据库之间迁移数据。

使用 mysqldump 导出数据：

1
mysqldump -u username -p database_name > backup.sql
  • mysqldump 是 MySQL 提供的用于备份数据库的命令行工具。
  • -u username 指定 MySQL 用户名。
  • -p 表示在命令行中提示输入密码。
  • database_name 是要导出的数据库名称。
  • backup.sql 是要保存导出数据的 SQL 文件名。

导出的内容：

  1. 数据库结构：默认情况下，mysqldump 会导出指定数据库中的所有表结构和数据。

  2. CREATE 语句：导出的 SQL 文件包含 CREATE TABLE 语句，用于创建数据库中的所有表。

  3. INSERT 语句：导出的 SQL 文件还包含 INSERT INTO 语句，用于插入表中的数据。

示例：

假设我们要将名为 employees 的数据库导出到名为 backup.sql 的 SQL 文件中：

1
mysqldump -u root -p employees > backup.sql

这个命令将导出 employees 数据库中的所有表结构和数据，并保存到名为 backup.sql 的文件中。

通过使用 mysqldump 命令，可以方便地将 MySQL 数据导出到 SQL 文件中，以实现数据备份、迁移和恢复等操作。

MySQL 导入数据

在 MySQL 中，可以使用多种方式将数据从 SQL 文件导入到数据库中。常用的方法包括使用 mysql 命令、source 命令、LOAD DATA 语句以及 mysqlimport 命令。

使用 mysql 命令导入数据：

1
mysql -u username -p database_name < backup.sql
  • mysql 是 MySQL 提供的用于执行 SQL 文件的命令行工具。
  • -u username 指定 MySQL 用户名。
  • -p 表示在命令行中提示输入密码。
  • database_name 是要导入数据的数据库名称。
  • < backup.sql 表示从指定的 SQL 文件中读取数据并导入到数据库中。

使用 source 命令导入数据：

1
2
mysql> use database_name;
mysql> source /path/to/backup.sql;
  • use database_name; 用于选择要导入数据的数据库。
  • source /path/to/backup.sql; 用于执行指定路径下的 SQL 文件，将数据导入到当前选择的数据库中。

使用 LOAD DATA 导入数据：

1
2
3
4
LOAD DATA INFILE '/path/to/data.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
  • LOAD DATA INFILE 语句用于从指定的文件中加载数据。
  • '/path/to/data.csv' 是要导入数据的文件路径。
  • INTO TABLE table_name 指定要将数据导入到的表。
  • FIELDS TERMINATED BY ',' 指定字段之间的分隔符。
  • LINES TERMINATED BY '\n' 指定行之间的分隔符。

使用 mysqlimport 导入数据：

1
mysqlimport -u username -p database_name /path/to/data.csv
  • mysqlimport 是 MySQL 提供的用于从文件中导入数据的命令行工具。
  • -u username 指定 MySQL 用户名。
  • -p 表示在命令行中提示输入密码。
  • database_name 是要导入数据的数据库名称。
  • /path/to/data.csv 是包含要导入数据的 CSV 文件路径。

通过以上方法，可以灵活地将数据从 SQL 文件或其他数据文件导入到 MySQL 数据库中，实现数据的快速迁移和导入。

MySQL 函数

MySQL 的函数分为多个类别，包括字符串函数、数学函数、日期和时间函数、条件函数等。

字符串函数

  • CONCAT(str1, str2, ...)：将多个字符串连接成一个字符串。
  • LENGTH(str)：返回字符串的长度。
  • UPPER(str)：将字符串转换为大写。
  • LOWER(str)：将字符串转换为小写。
  • SUBSTRING(str, start, length)：提取字符串的子串。

数学函数

  • ROUND(num, decimals)：四舍五入到指定的小数位数。
  • CEIL(num)：向上取整。
  • FLOOR(num)：向下取整。
  • ABS(num)：返回数的绝对值。

日期和时间函数

  • NOW()：返回当前日期和时间。
  • DATE_FORMAT(date, format)：将日期格式化为指定的格式。
  • YEAR(date)：返回日期的年份部分。
  • MONTH(date)：返回日期的月份部分。
  • DAY(date)：返回日期的日份部分。

条件函数

  • IF(condition, value_if_true, value_if_false)：根据条件返回指定值。
  • CASE：类似于编程语言中的 switch 语句，根据不同的条件返回不同的值。

示例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT CONCAT('Hello', ' ', 'World') AS greeting;
-- 输出：Hello World

SELECT UPPER('hello') AS upper_case;
-- 输出：HELLO

SELECT ROUND(3.14159265359, 2) AS rounded_number;
-- 输出：3.14

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS formatted_date;
-- 输出：2024-05-25

SELECT IF(5 > 3, 'Yes', 'No') AS result;
-- 输出：Yes

MySQL 运算符

下面是 MySQL 中常用的运算符及其说明：

算术运算符

运算符 描述 示例
+ 加法 a + b
- 减法 a - b
* 乘法 a * b
/ 除法 a / b
% 取模 a % b
DIV 整除运算 a DIV b

比较运算符

运算符 描述 示例
= 等于 a = b
!= 不等于 a != b
<> 不等于 a <> b
< 小于 a < b
<= 小于等于 a <= b
> 大于 a > b
>= 大于等于 a >= b
<=> 安全等于 a <=> b
IS NULL 判断是否为NULL a IS NULL
IS NOT NULL 判断是否不为NULL a IS NOT NULL