MySQL数据库深度剖析：从基础到实战 （上）

MySQL数据库深度剖析：从基础到实战 （上）

由此进入MySQL数据库深度剖析：从基础到实战 （下）

一、MySQL 基础入门

1.1 MySQL 简介

MySQL 是一款由瑞典 MySQL AB 公司开发，目前属于 Oracle 旗下的开源关系型数据库管理系统（RDBMS） 。它使用结构化查询语言（SQL）进行数据库管理，在数据库领域占据着重要地位，是最流行的数据库管理系统之一。

MySQL 的发展历程丰富且充满变革。它起源于 1979 年，最初是由蒙蒂・维德纽斯（Monty Widenius）设计的一款底层面向报表的存储引擎工具 ——Unireg。1996 年，MySQL 1.0 版本发布，开始在小范围内使用。此后，MySQL 不断发展壮大，1999 - 2000 年，MySQL AB 公司在瑞典成立，与 Sleepycat 合作开发了 Berkeley DB 引擎，开始支持事务处理。2005 年 10 月，里程碑版本 5.0 发布，使 MySQL 具备了企业级数据库管理系统的特性，如加入了游标、储存过程、触发器、视图和事务支持等。2008 年 1 月 16 号，MySQL 被 Sun 公司收购，进入 Sun MySQL 时代；2009 年 4 月 20 日，甲骨文（Oracle）收购 Sun 公司，MySQL 又进入 Oracle MySQL 时代。在这之后，MySQL 持续迭代，2010 年 12 月发布的 MySQL 5.5 版本加强了各个方面企业级的特性，2015 年发布的 MySQL 5.7 GA 带来了如 160 万只读 QPS 等新特性。2023 年 7 月，MySQL 版本更迭为 9.0.0，主要增加了 JavaScript 存储程序、VECTOR 类型支持、内联和隐式外键约束等功能，同年 10 月发布了 8.2.0 版本，2025 年 1 月 21 日，MySQL 9.2.0 版本发布 。

MySQL 之所以如此受欢迎，得益于其众多优势：

• 开源免费：用户可以自由获取、使用和修改其源代码，大大降低了企业的成本，同时也拥有广大的用户和开发者社区，社区成员可以共享经验、提供问题解决方案，促进 MySQL 的持续改进和优化。

• 高性能：采用了多种速度优化技术，包括独立的存储引擎架构、快速插入和替换操作、延迟关联等，在处理大量数据和高并发请求时表现出色，能够满足各类应用的需求。

• 易用性：安装、配置和使用都非常简单，支持标准的 SQL 语言，熟悉 SQL 的开发人员可以轻松上手，并且提供了丰富的工具和库，方便用户管理和操作数据库，错误信息也清晰明了，有助于快速定位问题。

• 灵活性：支持多种存储引擎，如 InnoDB、MyISAM 等，用户可以根据实际需求（例如数据一致性要求、并发性能要求、存储成本等）选择合适的存储引擎。

• 可扩展性：支持集群、主从复制等高级功能，能够满足企业不断增长的数据存储和访问需求，方便进行数据库的扩展和负载均衡，提高系统的可用性和性能。

• 安全性：提供了丰富的安全特性，如用户权限管理、数据加密、密码加密、网络层安全、主机层安全等，确保数据在存储和传输过程中的安全性和完整性。

由于这些优势，MySQL 的应用场景极为广泛：

• Web 开发领域：它是 LAMP（Linux、Apache、MySQL、PHP/Perl/Python）堆栈的重要组成部分，几乎成为每一个 Web 开发人员的必备工具，被广泛应用于电子商务网站、社交媒体平台、博客、论坛等各种类型的 Web 应用中，其高效查询能力和可靠的事务处理能够确保 Web 应用的性能和可扩展性，应对高并发的 Web 访问需求，确保数据的完整性和一致性 。

• 数据仓库：MySQL 的分区表、索引优化和查询优化等功能，使其能够处理海量数据，并对这些数据进行复杂的分析和查询操作，许多企业选择 MySQL 来构建其数据仓库系统，以便进行商业智能分析和决策支持，其多线程和并行查询能力，可高效地处理复杂的查询和分析任务 。

• 内容管理系统（CMS）：著名的 CMS 平台如 WordPress、Joomla 和 Drupal 都使用 MySQL 作为其默认数据库，它能够高效地存储和管理大量的内容，如文章、图片、用户评论等，通过事务处理和外键约束等功能，确保数据的安全性和一致性，还支持全文搜索，提升用户体验 。

• 物联网（IoT）：物联网设备生成大量数据，MySQL 凭借其高效的数据存储和查询能力，成为许多 IoT 解决方案的首选，其高可用性和容灾能力，确保了物联网系统的稳定运行，开发者可以轻松地将 IoT 设备生成的数据存储到数据库中，并进行实时分析和处理 。

• 金融服务：金融服务行业对数据的安全性、可靠性和性能要求极高，MySQL 通过数据加密、访问控制和事务处理等功能，确保了金融数据的安全性和一致性，银行、证券公司和保险公司等金融机构常使用 MySQL 来管理其交易记录、客户信息和财务数据 。

• 游戏开发：作为一个轻量级、开源的关系型数据库管理系统，MySQL 能够满足游戏开发中对游戏数据、玩家信息和游戏进度的管理需求 。

1.2 安装与配置

1.2.1 Windows 系统下安装

1. 下载安装包：

• 访问 MySQL 官方下载页面：Mysql下载地址（Windows） 。

• 根据系统是 32 位还是 64 位选择对应的安装包下载。

1. 运行安装程序：

• 双击下载好的安装包，启动安装向导，出现欢迎界面，点击 “Next” 继续。

1. 选择安装类型：

• Developer Default（默认开发者环境）：这个选项会安装 MySQL 服务器、客户端以及一些常用的开发工具，适合开发人员使用，安装过程相对简单，采用默认配置即可满足大多数开发场景需求。

• Custom（自定义安装）：如果对安装有特殊要求，比如指定安装路径、选择特定的组件等，可以选择此选项。在自定义安装中，可以详细选择要安装的 MySQL Server 版本、存储引擎插件、客户端工具等组件，还能更改安装路径，适合对 MySQL 有深入了解且有特定部署需求的用户。

1. 安装 MySQL 服务器：

• 若选择 “Developer Default”，安装程序会自动下载并安装所选组件。在安装过程中，可能需要等待一段时间，期间安装程序会自动处理依赖关系并完成文件复制和配置。

• 若是 “Custom” 安装，在组件选择界面，确保选中 “MySQL Server” 组件，还可根据需求选择其他组件，如 MySQL Workbench（一款可视化数据库管理工具）等，然后点击 “Next”，安装程序会自动下载并安装所选组件。

1. 配置 MySQL 服务器：

• 设置服务器类型：一般选择 “Standalone（独立服务器）”，这种模式下 MySQL 作为独立的服务运行，适用于大多数个人开发和小型企业应用场景。

• 设置端口：默认端口为 3306，如果这个端口没有被其他程序占用，建议保持默认设置，这样方便与其他基于默认端口配置的应用程序进行交互。若 3306 端口已被占用，可以选择其他未被占用的端口，如 3307 等。

• 选择认证方式：

  • Use Legacy Authentication Method（使用旧版认证方式）：如果需要兼容旧版本的客户端（如一些早期开发的应用程序，其数据库连接部分没有更新到支持新版认证方式），可以选择此方式。这种认证方式在安全性上相对较弱，但兼容性更好。

  • Use Strong Password Encryption for Authentication（使用强密码加密认证）：这是 MySQL 8.0 及以后版本推荐的认证方式，安全性更高，使用更复杂的密码加密算法来保护用户密码。一般情况下，新开发的应用程序推荐使用这种认证方式。

• 设置管理员密码（root 用户）：为 root 用户设置一个强密码，密码应包含大小写字母、数字和特殊字符，长度足够，以增强数据库的安全性。同时，要妥善保管好这个密码，因为 root 用户拥有最高权限，能对数据库进行任何操作。

• 可选操作：可以创建额外用户，在用户管理部分，点击 “Add User”，输入用户名、密码，并为用户分配相应的权限，如对特定数据库的读写权限等，这样可以实现更细粒度的权限控制，提高数据库的安全性。

1. 完成安装：

• 按提示完成安装，并启动 MySQL 服务。安装完成后，可以在系统的 “服务” 中找到 “MySQL” 服务，确保其状态为 “正在运行”。

• 验证安装：可以使用 MySQL Workbench 或命令行工具验证安装是否成功。使用命令行验证时，打开命令提示符，输入 “mysql -u root -p”，然后输入设置的 root 用户密码，如果能成功进入 MySQL 命令行界面（出现 “mysql>” 提示符），则表示安装成功。

1.2.2 macOS 系统下安装

1. 确保已安装 Homebrew：如果没有安装 Homebrew，可以在终端中运行以下命令安装：

/bin/bash -c "\$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

1. 使用 Homebrew 安装 MySQL：在终端中执行以下命令：

brew install mysql

安装过程中，Homebrew 会自动下载 MySQL 的相关文件，并处理依赖关系进行安装。

3. 启动 MySQL 服务：

• 安装完成后，使用以下命令启动 MySQL：

brew services start mysql

也可以使用 “brew services restart mysql” 命令重启 MySQL 服务，或者使用 “brew services stop mysql” 命令停止 MySQL 服务。

4. 设置 root 密码：

• 进入 MySQL 后运行以下命令设置 root 密码：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql\_native\_password BY 'your\_password';

将 “your_password” 替换为自己设置的强密码。

1.2.3 Linux 系统下安装（以 Ubuntu 为例）

1. 更新软件包：在终端中运行以下命令更新系统软件包列表：

sudo apt update

sudo apt upgrade

这一步骤确保系统安装的是最新版本的软件包，避免因软件包版本过旧导致安装问题。

2. 安装 MySQL：在终端中运行以下命令安装 MySQL 服务器：

sudo apt install mysql-server

安装过程中，系统会提示确认安装，输入 “Y” 并回车继续安装，安装程序会自动下载并安装 MySQL 及其依赖包。

3. 启动 MySQL 服务：安装完成后，使用以下命令启动 MySQL 服务：

sudo systemctl start mysql

还可以使用 “sudo systemctl enable mysql” 命令设置 MySQL 服务开机自启，这样每次系统启动时 MySQL 服务都会自动运行。

4. 安全配置：运行以下命令以强化 MySQL 的安全性：

sudo mysql\_secure\_installation

根据提示进行操作，如设置 root 用户密码、删除匿名用户、禁止远程 root 登录、删除测试数据库等。设置 root 用户密码时，同样要设置一个强密码；删除匿名用户可以避免未经授权的访问；禁止远程 root 登录可以减少安全风险；删除测试数据库可以避免不必要的安全隐患。

5. 登录 MySQL：使用以下命令登录 MySQL：

mysql -u root -p

输入设置的 root 用户密码，成功登录后会出现 “mysql>” 提示符，表示可以开始执行 SQL 命令。

1.2.4 基础配置参数含义与设置方法

MySQL 的配置文件在不同操作系统下位置可能不同，在 Windows 下一般位于安装目录的 “my.ini” 文件，在 Linux 下通常是 “/etc/mysql/my.cnf” 文件 。以下介绍一些常见的基础配置参数：

1. 端口号（port）：

• 含义：MySQL 服务器监听的端口号，默认是 3306，用于客户端与服务器之间的通信。

• 设置方法：在配置文件中找到 “port” 参数，修改其值即可。例如，如果要将端口改为 3307，可在配置文件中添加或修改为 “port = 3307” ，修改后需要重启 MySQL 服务使设置生效。

1. 字符集（character - set - server）：

• 含义：设置 MySQL 服务器的默认字符集，用于存储和处理字符数据。常见的字符集有 utf8、utf8mb4 等，utf8mb4 是 utf8 的超集，支持更多的字符，包括一些 emoji 表情等，建议使用 utf8mb4 以支持更广泛的字符处理。

• 设置方法：在配置文件中添加或修改 “character - set - server = utf8mb4”，修改后重启 MySQL 服务。例如，在 Linux 的 “/etc/mysql/my.cnf” 文件中添加该配置，然后执行 “sudo systemctl restart mysql” 命令重启服务。

1. 缓存大小（innodb_buffer_pool_size）：

• 含义：InnoDB 存储引擎的缓冲池大小，用于缓存数据和索引。增大这个值可以提高 InnoDB 存储引擎的性能，因为更多的数据和索引可以缓存在内存中，减少磁盘 I/O 操作，但设置过大可能会导致系统内存不足。一般根据服务器的内存大小来合理设置，对于内存为 8GB 的服务器，可以设置为 2GB - 4GB 左右。

• 设置方法：在配置文件中添加或修改 “innodb_buffer_pool_size = 2G”（假设设置为 2GB），然后重启 MySQL 服务。

1. 日志文件路径（log - error）：

• 含义：MySQL 错误日志文件的路径，记录 MySQL 服务器运行过程中的错误信息，有助于排查故障。

• 设置方法：在配置文件中添加或修改 “log - error = /var/log/mysql/error.log”（假设日志文件路径为 “/var/log/mysql/error.log”），确保指定的路径存在且 MySQL 服务对该路径有写入权限，修改后重启 MySQL 服务。

1.3 SQL 基础语法

SQL（Structured Query Language）即结构化查询语言，是用于管理关系型数据库的标准语言，MySQL 也使用 SQL 来进行数据操作和管理。以下介绍 SQL 语句的基本结构、语法规则和常用注释方式。

1. 基本结构：

• SQL 语句主要由关键字、表名、字段名、值等组成。例如，最基本的查询语句：

SELECT column1, column2 FROM table_name;

• SELECT：是关键字，用于指定要查询的字段，这里的 “column1, column2” 是要查询的字段名，可以是一个或多个字段，用逗号分隔，如果要查询所有字段，可以使用 “*” 代替。

• FROM：也是关键字，用于指定查询数据所在的表，“table_name” 是表名。

1. 语法规则：

• 关键字大小写不敏感：SQL 关键字（如 SELECT、FROM、INSERT 等）不区分大小写，例如 “SELECT” 和 “select” 的作用是一样的，但为了提高代码的可读性，通常将关键字大写。

• 语句结束符：在 MySQL 中，每条 SQL 语句通常以分号（;）结尾，表示语句的结束。例如：

INSERT INTO users (name, age) VALUES ('John', 25);

• 字符串和日期值：字符串值需要用单引号（’ '）括起来，例如 ‘John’；日期值也可以用单引号括起来，并且要符合指定的日期格式，如 ‘2024 - 01 - 01’ 表示 2024 年 1 月 1 日。如果使用双引号（" "）来括字符串，需要在 MySQL 配置中开启 “sql_mode” 的 “ANSI_QUOTES” 模式 。

• 表名和字段名：表名和字段名在 SQL 语句中要准确无误，并且如果表名或字段名包含特殊字符（如空格、连字符等），或者是 SQL 关键字，需要用反引号（ ）括起来。例如，如果有一个表名为 “user - info”，查询时应写成：

SELECT * FROM `user - info`;

1. 常用注释方式：

• 单行注释：使用 “–”（两个连字符）进行单行注释，从 “-- ” 开始到行末的内容都被视为注释，不会被 MySQL 执行。例如：

-- 这是一条查询所有用户的SQL语句
SELECT * FROM users;

• 多行注释：使用 “/* /” 进行多行注释，在 “/” 和 “*/” 之间的所有内容都是注释内容。例如：

/* 
这是一个多行注释示例，
可以用于解释复杂的SQL语句逻辑，
这里的内容都不会被MySQL执行。
*/
SELECT column1, column2 FROM table_name WHERE condition;

通过以上对 MySQL 简介、安装配置以及 SQL 基础语法的介绍，为深入学习和使用 MySQL 数据库奠定了基础，后续将进一步学习 MySQL 的各种操作和高级特性。

二、MySQL 数据类型

2.1 数值类型

MySQL 中的数值类型主要分为整数类型和浮点类型，不同的数值类型有着各自的取值范围、存储需求和适用场景，合理选择数值类型对于优化数据库性能和节省存储空间至关重要。

1. 整数类型：

• TINYINT：占用 1 个字节存储空间。有符号的 TINYINT 取值范围是 - 128 到 127，无符号的取值范围是 0 到 255。例如，在存储表示性别的字段时，0 表示男，1 表示女，使用 TINYINT 就非常合适，既节省空间又能满足需求。

• SMALLINT：占用 2 个字节。有符号的取值范围是 - 32768 到 32767，无符号的取值范围是 0 到 65535。在一些需要存储较小整数且取值范围比 TINYINT 稍大的场景，如存储一个小型公司的员工数量，使用 SMALLINT 是不错的选择。

• MEDIUMINT：占用 3 个字节。有符号的取值范围是 - 8388608 到 8388607，无符号的取值范围是 0 到 16777215。比如在存储一个城市的人口数量（假设城市规模不是特别大）时，可以考虑使用 MEDIUMINT 。

• INT（或 INTEGER）：占用 4 个字节。有符号的取值范围是 - 2147483648 到 2147483647，无符号的取值范围是 0 到 4294967295。这是最常用的整数类型，适用于大多数整数数据的存储，比如存储用户 ID，一般情况下使用 INT 类型即可满足需求。

• BIGINT：占用 8 个字节。有符号的取值范围是 - 9223372036854775808 到 9223372036854775807，无符号的取值范围是 0 到 18446744073709551615。当需要存储非常大的整数，如存储一个大型电商平台的交易流水号，或者天文数据中的一些极大数值时，就需要使用 BIGINT 类型。

1. 浮点类型：

• FLOAT：单精度浮点数，占用 4 个字节。可以表示大约 7 位有效数字，取值范围为 - 3.402823466E+38 到 -1.175494351E-38、0 和 1.175494351E-38 到 3.402823466E+38。在一些对精度要求不是特别高，且数值范围较大的场景下适用，比如存储商品的价格（允许一定的精度误差）。定义 FLOAT 类型时，可以使用 FLOAT (M, D) ，其中 M 为总位数，D 为小数位数，例如 FLOAT (5, 2) 表示总共有 5 位数字，其中小数部分占 2 位，可表示的范围是 - 999.99 到 999.99 。

• DOUBLE（或 DOUBLE PRECISION、REAL）：双精度浮点数，占用 8 个字节。可以表示大约 15 位有效数字，取值范围为 - 1.7976931348623157E+308 到 -2.2250738585072014E-308、0 和 2.2250738585072014E-308 到 1.7976931348623157E+308。当需要更高精度的浮点数时，使用 DOUBLE 类型，如在科学计算、金融计算中，如果对精度要求较高，就应该使用 DOUBLE 。同样，DOUBLE 也可以使用 DOUBLE (M, D) 的形式定义精度，例如 DOUBLE (8, 4) 表示总共有 8 位数字，小数部分占 4 位，可表示的范围是 - 99999.9999 到 99999.9999 。

• DECIMAL（或 NUMERIC）：定点数类型，用于高精度的小数计算。DECIMAL (M, D) 表示最多可以存储 M 位数，其中 D 位是小数部分，占用的存储空间依赖于 M 和 D 的值。例如 DECIMAL (10, 2) 表示总共有 10 位数字，小数部分占 2 位，可表示的范围是 - 99999999.99 到 99999999.99 。DECIMAL 类型在存储时不会出现精度损失，所以在处理金融数据，如银行账户余额、交易金额等对精度要求极高的场景下，必须使用 DECIMAL 类型。

1. 选择建议：

• 考虑取值范围：根据实际数据的取值范围选择合适的整数类型，避免数据溢出。如果知道某个字段的值不会超过 TINYINT 的范围，就不要使用更大的整数类型，以节省存储空间。

• 精度要求：对于需要精确计算的场景，如金融领域，应优先选择 DECIMAL 类型；对于对精度要求不高的一般计算，可以选择 FLOAT 或 DOUBLE ，根据精度需求选择单精度或双精度。

• 性能影响：整数类型的运算速度通常比浮点类型快，在满足需求的情况下，尽量使用整数类型可以提高性能。例如，在统计用户点赞数、评论数等场景下，使用整数类型进行存储和计算效率更高。

2.2 字符串类型

MySQL 中的字符串类型用于存储文本数据，不同的字符串类型在存储方式和性能上存在差异，开发者需要根据实际需求选择合适的类型。

1. CHAR：

• 定义与特点：CHAR 是固定长度的字符串类型，在定义表结构时需要指定长度，例如 CHAR (10) ，表示该字段固定占用 10 个字符的存储空间。无论实际存储的字符串长度是多少，都会占用指定的固定长度空间。如果实际存储的字符串长度小于定义长度，MySQL 会在字符串的右侧填充空格以达到指定长度；在读取数据时，MySQL 会自动去除这些填充的空格。

• 存储需求：占用的存储空间为定义的长度，例如 CHAR (10) 占用 10 个字符的空间，具体占用的字节数取决于字符集。在 UTF - 8 字符集中，每个字符最多占用 3 个字节，所以 CHAR (10) 在 UTF - 8 字符集下最多占用 30 个字节；在 GBK 字符集中，每个字符最多占用 2 个字节，CHAR (10) 最多占用 20 个字节。

• 适用场景：适用于存储长度固定的字符串，如国家代码（如 “CN”“US” 等，固定为 2 个字符）、邮政编码（一般固定为 6 位数字）、身份证号码（18 位固定长度）等。由于其长度固定，在存储和读取时效率较高，因为不需要计算字符串的长度，在使用索引时也能更快地定位到记录的位置。

1. VARCHAR：

• 定义与特点：VARCHAR 是可变长度的字符串类型，同样在定义表结构时需要指定最大长度，例如 VARCHAR (100) ，表示该字段最多可以存储 100 个字符。它根据实际存储的字符串长度使用字节数，并额外使用 1 或 2 个字节来存储字符串的长度信息。对于长度小于 255 的字符串，使用 1 个字节存储长度信息；长度大于 255 的字符串使用 2 个字节。

• 存储需求：实际占用的存储空间为字符串本身的长度加上存储长度信息的字节数。例如，存储字符串 “hello”（5 个字符）到 VARCHAR (100) 字段中，在 UTF - 8 字符集下，占用的存储空间为 5 * 3 + 1 = 16 字节（5 个字符每个字符 3 字节，加上 1 个字节的长度信息）；如果存储长度超过 255 个字符的字符串，在 UTF - 8 字符集下，占用的存储空间为字符串长度 * 3 + 2 字节（加上 2 个字节的长度信息）。

• 适用场景：适用于存储长度不固定的字符串，如用户姓名（长度因人而异）、电子邮件地址（长度不固定）、文章内容（长度变化较大）等。由于它只存储实际需要的字节数，在存储空间上更为节省，尤其适用于存储长度差异较大的字符串数据，但在某些情况下，由于需要额外处理长度信息，其性能可能稍逊于 CHAR ，不过在大部分情况下性能差异不大。

1. TEXT：

• 定义与特点：TEXT 类型用于存储长文本数据，分为 TINYTEXT（最大长度 255 字节）、TEXT（最大长度 65535 字节）、MEDIUMTEXT（最大长度 16777215 字节）和 LONGTEXT（最大长度 4294967295 字节）。它与 VARCHAR 类似，也是可变长度的，但它主要用于存储较长的文本内容，并且在存储和处理上有一些不同。TEXT 类型不支持默认值，且对 TEXT 类型的字段建立索引时需要特别注意，因为索引的大小有限制，如果文本过长可能无法创建索引或导致索引效率低下。

• 存储需求：根据不同的子类型，占用不同大小的存储空间，如 TINYTEXT 占用最多 255 字节，TEXT 占用最多 65535 字节等 。

• 适用场景：适用于存储较长的文本，如新闻文章的正文、博客文章的内容、产品的详细描述等。由于其存储的文本长度较大，在使用时要注意性能问题，避免频繁对大文本字段进行读写操作，以免影响数据库性能。

1. 选择建议：

• 长度固定性：如果数据的长度是固定的，应优先选择 CHAR 类型，以提高存储和查询效率；如果数据长度不固定，选择 VARCHAR 类型可以节省存储空间。

• 存储大小：对于存储较短的字符串，VARCHAR 和 CHAR 都适用，但如果字符串长度可能超过 255 字节，或者需要存储长文本，应选择 TEXT 类型。

• 性能考虑：在频繁查询和排序操作中，CHAR 类型由于长度固定，性能可能更好；而 VARCHAR 类型在存储空间利用上更有优势，在大部分情况下，两者性能差异不明显，开发者可以根据实际情况权衡选择。

2.3 日期和时间类型

MySQL 提供了多种日期和时间类型，用于存储和处理日期与时间相关的数据，不同类型在存储格式、取值范围和使用场景上有所不同。

1. DATE：

• 存储格式：以 ‘YYYY - MM - DD’ 的格式存储日期，例如 ‘2024 - 01 - 01’ 表示 2024 年 1 月 1 日。

• 取值范围：‘1000 - 01 - 01’ 到 ‘9999 - 12 - 31’ 。

• 适用场景：适用于只需要存储日期，而不需要存储时间的场景，如记录用户的生日、订单的下单日期、商品的生产日期等。在查询时，可以方便地按照日期进行筛选和统计，例如查询 2024 年 1 月所有的订单：

SELECT * FROM orders WHERE order_date BETWEEN '2024 - 01 - 01' AND '2024 - 01 - 31';

1. DATETIME：

• 存储格式：以 ‘YYYY - MM - DD HH:ii:ss’ 的格式存储日期和时间，例如 ‘2024 - 01 - 01 12:30:00’ 表示 2024 年 1 月 1 日 12 点 30 分 0 秒。

• 取值范围：‘1000 - 01 - 01 00:00:00’ 到 ‘9999 - 12 - 31 23:59:59’ 。

• 适用场景：适用于需要同时存储日期和时间的场景，如记录用户的登录时间、系统的操作日志时间、会议的开始时间和结束时间等。在进行时间相关的计算和比较时非常方便，例如计算两个时间之间的时间差：

SELECT TIMESTAMPDIFF(SECOND, '2024 - 01 - 01 12:00:00', '2024 - 01 - 01 12:30:00');

1. TIMESTAMP：

• 存储格式：以时间戳的形式存储，即从 1970 年 1 月 1 日 00:00:00 UTC 到指定时间的秒数。在 MySQL 中，TIMESTAMP 以 4 个字节存储，显示格式与 DATETIME 类似，例如 ‘2024 - 01 - 01 12:30:00’ 。

• 取值范围：‘1970 - 01 - 01 00:00:01’ 到 ‘2038 - 01 - 19 03:14:07’ 。

• 适用场景：常用于记录数据的创建时间或最后更新时间，因为它占用的存储空间较小，并且会自动根据服务器的时区进行转换。例如在创建用户表时，可以添加一个 create_time 字段，使用 TIMESTAMP 类型来记录用户的注册时间：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

当插入新用户时，如果不指定 create_time 的值，它会自动设置为当前时间。此外，TIMESTAMP 类型在不同时区的服务器上使用时，能够正确地处理时间转换，保证时间的一致性。

4. TIME：

• 存储格式：以 ‘HH:ii:ss’ 的格式存储时间，例如 ‘12:30:00’ 表示 12 点 30 分 0 秒，也可以表示为 ‘-838:59:59’ 到 ‘838:59:59’ 之间的时间，这里的正负号表示时间的方向（例如，负数可以表示过去的时间，正数表示未来的时间，常用于表示时间间隔）。

• 取值范围：‘-838:59:59’ 到 ‘838:59:59’ 。

• 适用场景：适用于只需要存储时间，而不需要存储日期的场景，如表示一场电影的播放时长、课程的上课时间、公交车的发车时间间隔等。在进行时间计算时，可以方便地进行时间的加减操作，例如计算一场电影播放结束的时间：

SELECT ADDTIME('19:00:00', '2:30:00'); -- 结果为'21:30:00'

1. YEAR：

• 存储格式：以 4 位数字表示年份，例如 ‘2024’，也可以用 2 位数字表示，取值范围为 ‘70’ 到 ‘69’（表示 1970 年到 2069 年），但在 MySQL 5.5.27 及更高版本中，不推荐使用 2 位数字表示年份。

• 取值范围：‘1901’ 到 ‘2155’，当使用 2 位数字表示时，取值范围为 ‘70’ 到 ‘69’（对应 1970 年到 2069 年）。

• 适用场景：适用于只需要存储年份的场景，如记录书籍的出版年份、产品的上市年份等。在查询时，可以方便地按照年份进行筛选，例如查询 2024 年出版的书籍：

SELECT * FROM books WHERE publish_year = '2024';

1. 日期时间运算：

• 加法运算：使用 ADDTIME 函数可以对时间进行加法运算，例如计算一个会议在开始时间的基础上延长 30 分钟后的结束时间：

SELECT ADDTIME('2024 - 01 - 01 10:00:00', '0:30:00');

• 减法运算：使用 SUBTIME 函数可以对时间进行减法运算，例如计算两个时间点之间的时间差：

SELECT SUBTIME('2024 - 01 - 01 12:00:00', '2024 - 01 - 01 10:00:00');

• 日期时间比较：可以直接使用比较运算符（如 =、<>、<、>、<=、>= ）对日期和时间进行比较，例如查询 2024 年 1 月 1 日之后创建的订单：

SELECT * FROM orders WHERE create_time > '2024 - 01 - 01 00:00:00';

2.4 其他数据类型

除了上述常见的数据类型外，MySQL 还提供了一些其他数据类型，它们各自具有特殊的用途和使用方法。

1. 二进制类型：

• BINARY 和 VARBINARY：

  • 定义与特点：BINARY 是固定长度的二进制字符串类型，VARBINARY 是可变长度的二进制字符串类型。与 CHAR 和 VARCHAR 类似，BINARY 在定义时需要指定长度，例如 BINARY (10) ，表示固定占用 10 个字节的存储空间；VARBINARY 需要指定最大长度，例如 VARBINARY (100) ，表示最多占用 100 个字节，实际占用空间根据

三、MySQL 关键字详解（一）：数据查询

3.1 SELECT 关键字

SELECT 关键字是 MySQL 中用于数据查询的核心关键字，其基本语法结构如下：

SELECT [DISTINCT] {* | column1 [AS alias1], column2 [AS alias2], ...}
FROM table_name
[WHERE condition]
[GROUP BY column1, column2, ...]
[HAVING condition]
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...]
[LIMIT [offset,] row_count];

1. 选择特定列：在 SELECT 关键字后列出要查询的列名，列名之间用逗号分隔。例如，假设有一个名为 students 的表，包含 id、name、age 和 grade 列，要查询学生的姓名和年龄，可以使用以下语句：

SELECT name, age
FROM students;

1. 使用通配符：使用通配符 * 可以选择表中的所有列。例如：

SELECT *
FROM students;

虽然这种方式在编写查询语句时较为便捷，但在实际应用中，如果不需要所有列的数据，建议明确列出所需列，因为查询不必要的列可能会降低查询性能，增加网络传输的数据量。

3. 给列取别名：可以使用 AS 关键字给查询结果中的列取别名，也可以省略 AS，直接在列名后加上别名。别名可以使查询结果的列名更具描述性，便于理解和使用。例如：

SELECT name AS student_name, age AS student_age
FROM students;

或者

SELECT name student_name, age student_age
FROM students;

如果别名中包含空格或特殊字符，需要用双引号或反引号括起来，例如：

SELECT name AS "Student's Name", age AS "Student Age"
FROM students;

1. 计算列：在 SELECT 语句中可以对列进行计算，生成新的计算列。例如，假设有一个 products 表，包含 price 和 quantity 列，要查询每个产品的总价（price * quantity），可以使用以下语句：

SELECT product_name, price, quantity, price * quantity AS total_price
FROM products;

1. 选择常量值：在 SELECT 语句中可以选择常量值，常量值会在每一行的查询结果中显示。例如：

SELECT 'This is a constant', name, age
FROM students;

上述语句会在每一行的查询结果中，第一列显示常量值 This is a constant，后面两列分别显示学生的姓名和年龄。

3.2 WHERE 关键字

WHERE 关键字用于在 SELECT 语句中对数据进行条件过滤，只返回满足指定条件的记录。其基本语法结构为：

SELECT column1, column2, ...
FROM table_name
WHERE condition;

1. 比较运算符：

• 等于（=）：用于检索列中与指定值相等的行。例如，从 employees 表中查询部门编号为 1 的员工：

SELECT *
FROM employees
WHERE department_id = 1;

• 不等于（<>** 或 !=）**：用于检索列中与指定值不相等的行。例如，查询类别不是 Electronics 的产品：

SELECT *
FROM products
WHERE category <> 'Electronics';

• 大于（>）：用于检索列中大于指定值的行。例如，查询订单总金额大于 1000 的订单：

SELECT *
FROM orders
WHERE total_amount > 1000;

• 小于（<）：用于检索列中小于指定值的行。例如，查询年龄小于 18 岁的学生：

SELECT *
FROM students
WHERE age < 18;

• 大于等于（>=）：用于检索列中大于或等于指定值的行。例如，查询工资大于等于 50000 的员工：

SELECT *
FROM employees
WHERE salary >= 50000;

• 小于等于（<=）：用于检索列中小于或等于指定值的行。例如，查询价格小于等于 50 的产品：

SELECT *
FROM products
WHERE price <= 50;

1. 逻辑运算符：

• AND：用于结合两个条件，要求同时满足两个条件的行被检索。例如，查询部门编号为 1 且工资大于 50000 的员工：

SELECT *
FROM products
WHERE price <= 50;

• OR：用于结合两个条件，只要满足其中一个条件的行就会被检索。例如，查询类别是 Electronics 或者价格大于 1000 的产品：

SELECT *
FROM products
WHERE category = 'Electronics' OR price > 1000;

• NOT：用于否定一个条件，检索不满足指定条件的行。例如，查询年龄不小于 18 岁的学生（即年龄大于等于 18 岁）：

SELECT *
FROM students
WHERE NOT age < 18;

1. 范围条件过滤：

• BETWEEN：用于检索在指定范围内的行。例如，查询价格在 50 到 100 之间的产品：

SELECT *
FROM products
WHERE price BETWEEN 50 AND 100;

需要注意的是，BETWEEN 包含边界值，即上述查询会返回价格等于 50 和 100 的产品记录。

• IN：用于检索属于指定值列表中的行。例如，查询客户编号为 1、2 或 3 的订单：

SELECT *
FROM orders
WHERE customer_id IN (1, 2, 3);

1. 模糊匹配：

• LIKE：用于模糊匹配，检索满足指定模式的行。它通常与通配符一起使用，常用的通配符有 %（表示任意字符序列，包括空字符序列）和 _（表示任意单个字符）。例如：

  • 查询名字以 J 开头的员工：

SELECT *
FROM employees
WHERE first_name LIKE 'J%';

• 查询名字包含 an 的员工：

SELECT *
FROM employees
WHERE first_name LIKE '%an%';

• 查询名字为四个字符，且第三个字符为 n 的员工：

SELECT *
FROM employees
WHERE first_name LIKE '__n_';

1. 空值检查：

• IS NULL：用于检索列中值为 NULL 的行。例如，查询没有填写电子邮件的员工：

SELECT *
FROM employees
WHERE email IS NULL;

• IS NOT NULL：用于检索列中值不为 NULL 的行。例如，查询填写了电子邮件的员工：

SELECT *
FROM employees
WHERE email IS NOT NULL;

3.3 ORDER BY 关键字

ORDER BY 关键字用于对 SELECT 语句的查询结果进行排序，可以按照一个或多个列的值进行升序（ASC）或降序（DESC）排列。其基本语法结构为：

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

1. 按单个列排序：

• 升序排序：默认情况下，ORDER BY 按照升序排序，即从小到大排列。例如，从 employees 表中查询员工信息，并按工资升序排列：

SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary;

• 降序排序：使用 DESC 关键字指定降序排序，即从大到小排列。例如，按工资降序排列员工信息：

SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

1. 按多个列排序：当需要按照多个列进行排序时，会先按照第一个列进行排序，如果第一个列的值相同，再按照第二个列进行排序，以此类推。例如，从 employees 表中查询员工信息，先按部门编号升序排序，对于部门编号相同的员工，再按工资降序排序：

SELECT employee_id, first_name, last_name, department_id, salary
FROM employees
ORDER BY department_id ASC, salary DESC;

1. 按列的位置排序：除了使用列名进行排序，还可以使用列在 SELECT 列表中的位置进行排序。列的位置从 1 开始计数。例如，从 employees 表中查询员工信息，并按照 SELECT 列表中第 4 列（即 salary 列）进行降序排序：

SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY 4 DESC;

1. 按表达式排序：可以按照表达式进行排序，例如对列进行计算后的结果进行排序。假设有一个 employees 表，包含 salary 列，要查询员工信息，并按照工资增加 10% 后的结果降序排序，可以使用以下语句：

SELECT employee_id, first_name, last_name, salary, salary * 1.1 AS new_salary
FROM employees
ORDER BY new_salary DESC;

1. 注意事项：

• 性能影响：排序操作会对性能产生一定影响，尤其是在处理大量数据时。因此，尽量避免对大型表进行不必要的排序。如果需要排序，应确保排序的列上有合适的索引，以提高排序效率。

• NULL 值处理：不同的数据库系统对 NULL 值在排序中的处理方式可能不同。在 MySQL 中，NULL 值在升序排序时会被视为最小值，排在最前面；在降序排序时会被视为最大值，排在最后面。例如，对包含 NULL 值的 salary 列进行升序排序：

SELECT employee_id, salary
FROM employees
ORDER BY salary ASC;

上述查询结果中，salary 为 NULL 的记录会排在最前面。

3.4 GROUP BY 与 HAVING 关键字

1. GROUP BY 关键字：

• 原理：GROUP BY 关键字用于将结果集按照一个或多个列进行分组，通常与聚合函数（如 SUM、AVG、COUNT、MAX、MIN 等）一起使用，对每个分组应用聚合函数，从而生成总结性的数据。其基本语法结构为：

SELECT column1, aggregate_function(column2)
FROM table_name
[WHERE condition]
GROUP BY column1;

例如，假设有一个 employees 表，包含 department（部门）和 salary（薪资）字段，要计算各部门的员工数量和总薪资，可以使用以下语句：

SELECT department, COUNT(*) AS employee_count, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

在这个查询中，GROUP BY department 表示按照 department 列对 employees 表中的记录进行分组，COUNT(*) 用于计算每个部门的员工数量，SUM(salary) 用于计算每个部门的总薪资。AS employee_count 和 AS total_salary 分别为聚合函数的结果取了别名，以便在查询结果中更清晰地显示。

• 使用规则：在使用 GROUP BY 的 SQL 语句中，SELECT 中返回的字段，必须满足以下两个条件之一：

  • 这些字段被包含在 GROUP BY 语句的后面，作为分组的依据。

  • 这些字段包含在聚合函数中。

    例如，下面的查询是错误的，因为 first_name 既没有包含在 GROUP BY 中，也没有包含在聚合函数中：

SELECT department, first_name, COUNT(*)
FROM employees
GROUP BY department;

1. HAVING 关键字：

• 作用：HAVING 关键字用于对 GROUP BY 语句产生的分组结果进行条件过滤，它可以基于聚合函数的结果进行筛选。WHERE 子句用于在分组前对数据进行过滤，而 HAVING 子句用于在分组后对聚合结果进行过滤。其基本语法结构为：

SELECT column1, aggregate_function(column2)
FROM table_name
[WHERE condition]
GROUP BY column1
HAVING condition;

例如，继续以 employees 表为例，如果只想查看员工数大于 10 的部门，可以使用以下语句：

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

在这个查询中，HAVING COUNT(*) > 10 表示只返回员工数量大于 10 的部门分组，这里的 COUNT(*) 是聚合函数，用于计算每个部门的员工数量。

• 与 WHERE 的区别：

  • 使用时机：WHERE 子句在数据分组之前进行过滤，而 HAVING 子句在数据分组之后进行过滤。如果没有使用 GROUP BY 子句，则 HAVING 的行为与 WHERE 类似，但 HAVING 支持聚合函数的使用，而 WHERE 不支持。

  • 支持的函数：WHERE 子句不能使用聚合函数作为过滤条件，HAVING 子句可以使用聚合函数作为过滤条件，因为它是在分组后对聚合结果进行筛选。

  • 字段别名：在 WHERE 子句中，通常不能直接使用字段别名（在某些数据库系统中可能支持，但这不是标准行为）。在 HAVING 子句中，可以使用字段别名（尤其是当别名是基于聚合函数的结果时）。例如：

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 50000;

这里的 avg_salary 是 AVG(salary) 的别名，在 HAVING 子句中可以直接使用。

3. 应用场景：

• 报表生成：在生成业务报表时，GROUP BY 和 HAVING 可以用来统计销售额、客户数量等重要指标。例如，从 sales_data 表中统计每个地区的总销售额，并只返回总销售额超过 100000 的地区：

SELECT region, SUM(sales) AS total_sales
FROM sales_data
GROUP BY region
HAVING SUM(sales) > 100000;

• 数据清理：在数据分析中，可以通过结合 GROUP BY 和 HAVING，快速找到频繁出现的错误数据。例如，从 access_log 表中查找出现次数超过 5 次的用户 IP：

SELECT ip_address, COUNT(*) AS access_count
FROM access_log
GROUP BY ip_address
HAVING COUNT(*) > 5;

• 人力资源分析：在 HR 数据分析中，通常需要对员工数据进行分类和汇总。例如，计算每个部门的平均薪水，并且只保留平均薪水超过 50000 的部门：

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

3.5 DISTINCT 关键字

DISTINCT 关键字用于去除查询结果中的重复记录，它作用于所有列，即只有当所有列的值都相同时，才会被视为重复记录并去除。其基本语法结构为：

SELECT DISTINCT column1, column2, ...
FROM table_name;

例如，假设有一个 employees 表，包含 department_id 和 job_title 列，要查询不同的部门编号，可以使用以下语句：

SELECT DISTINCT department_id
FROM employees;

上述语句会返回 employees 表中所有不同的 department_id 值，去除了重复的部门编号。如果查询多个列，如：

SELECT DISTINCT department_id, job_title
FROM employees;

此时只有当 department_id 和 job_title 的组合完全相同时，才会被视为重复记录并去除。例如，若有两条记录，一条是 department_id 为 1，job_title 为 Manager；另一条 department_id 为 1，job_title 为 Engineer，这两条记录不会被视为重复记录，都会出现在查询结果中。因为它们的 job_title 不同。

需要注意的是，DISTINCT 关键字必须放在所有列名的前面，如果写成 SELECT salary, DISTINCT department_id FROM employees 则会报错。并且 DISTINCT 是对

四、MySQL 关键字详解（二）：数据更新与管理

4.1 INSERT INTO 关键字

INSERT INTO 关键字用于向 MySQL 表中插入新的数据行，其基本语法有两种形式。

插入单条数据：

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

其中，table_name 是要插入数据的表名，column1, column2, ... 是表中的列名，value1, value2, ... 是要插入的对应列的值。例如，假设有一个 students 表，包含 student_id、name、age 列，要插入一条学生记录：

INSERT INTO students (student_id, name, age) VALUES (1, 'John Doe', 20);

如果要插入的数据包含表中的所有列，并且列的顺序与表定义中的顺序一致，那么可以省略列名：

INSERT INTO students VALUES (2, 'Jane Smith', 22);

插入多条数据：

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

例如，要一次性插入多条学生记录：

INSERT INTO students (student_id, name, age) VALUES (3, 'Bob Johnson', 21), (4, 'Alice Brown', 19);

这样可以减少与数据库的交互次数，提高插入数据的效率，特别是在插入大量数据时。

注意事项：

• 列数和顺序：插入的值必须与指定的列数和顺序相匹配。如果省略列名，那么值的顺序必须与表定义中的列顺序完全一致。例如，在 students 表中，如果定义顺序为 student_id、name、age，那么 INSERT INTO students VALUES (22, 'Invalid Insertion', 5); 这种插入是错误的，因为值的顺序与列顺序不匹配。

• 数据类型匹配：插入的值的数据类型必须与表中对应列的数据类型兼容。例如，不能将字符串 'twenty' 插入到 age 列（假设 age 列是整数类型），否则会导致错误。如果数据类型不匹配，MySQL 可能会尝试进行类型转换，但这可能会导致数据丢失或不准确。

• NULL 值处理：如果某个列允许 NULL 值，可以在 VALUES 中省略该列的值，或者明确插入 NULL。例如，如果 students 表中的 email 列允许 NULL，可以这样插入：

INSERT INTO students (student_id, name, age, email) VALUES (5, 'Tom Wilson', 23, NULL);

或者省略 email 列：

INSERT INTO students (student_id, name, age) VALUES (6, 'Sarah Davis', 24);

4.2 UPDATE 关键字

UPDATE 关键字用于修改 MySQL 表中已存在的数据行，其基本语法如下：

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

其中，table_name 是要更新数据的表名，SET 子句用于指定要更新的列及其新值，WHERE 子句是可选的，用于指定更新的条件。如果省略 WHERE 子句，将会更新表中的所有行，这在大多数情况下是非常危险的操作，可能会导致大量数据被错误修改。

条件更新：

例如，假设有一个 employees 表，要将员工 John Doe 的薪水增加 10%，可以使用以下语句：

UPDATE employees SET salary = salary * 1.1 WHERE name = 'John Doe';

这里，WHERE name = 'John Doe' 是条件，只有满足这个条件的行才会被更新。如果有多个条件，可以使用逻辑运算符（如 AND、OR）进行组合。例如，要将部门为 Sales 且薪水小于 5000 的员工薪水增加 500：

UPDATE employees SET salary = salary + 500 WHERE department = 'Sales' AND salary < 5000;

多列更新：

可以同时更新多个列的值。例如，要将员工 Jane Smith 的职位更新为 Manager，薪水增加 20%，并且入职日期更新为当前日期（假设使用 MySQL 的 CURRENT_DATE 函数获取当前日期）：

UPDATE employees 
SET job_title = 'Manager', 
    salary = salary * 1.2, 
    hire_date = CURRENT_DATE 
WHERE name = 'Jane Smith';

在 SET 子句中，使用逗号分隔要更新的列及其新值。

需要特别注意的是，在执行 UPDATE 操作时，务必仔细检查 WHERE 条件，确保只更新预期的数据行。如果没有 WHERE 条件，可能会意外地更新整个表的数据，造成数据错误或丢失。例如：

UPDATE employees SET salary = 0; -- 错误示范，会将所有员工薪水设为0

这种操作可能会对业务数据造成严重影响，所以在生产环境中执行 UPDATE 语句时要格外小心。

4.3 DELETE FROM 关键字

DELETE FROM 关键字用于从 MySQL 表中删除数据行，其基本语法为：

DELETE FROM table_name [WHERE condition];

其中，table_name 是要删除数据的表名，WHERE 子句是可选的，用于指定删除的条件。如果省略 WHERE 子句，将会删除表中的所有数据。

例如，假设有一个 orders 表，要删除订单号为 1001 的订单记录，可以使用以下语句：

DELETE FROM orders WHERE order_id = 1001;

如果要删除所有订单金额小于 100 的订单记录：

DELETE FROM orders WHERE order_amount < 100;

在使用 DELETE FROM 时，一定要谨慎操作，特别是在省略 WHERE 子句时。例如：

DELETE FROM orders; -- 危险操作，会删除orders表中的所有数据

这种操作会导致表中的所有数据丢失，而且通常无法直接恢复（除非有备份）。因此，在执行删除操作之前，最好先进行数据备份，或者使用 SELECT 语句先查询符合条件的数据，确认无误后再执行删除操作。

此外，当表中存在外键约束时，删除数据可能会受到限制。如果要删除的数据与其他表存在关联关系（通过外键），并且外键约束设置为 RESTRICT（默认）或 NO ACTION，那么删除操作会失败，以确保数据的完整性。例如，如果 orders 表中的 customer_id 是 customers 表的外键，当尝试删除 customers 表中某个客户记录时，如果该客户在 orders 表中有相关订单记录，删除操作会失败。如果希望在删除主表记录时同时删除关联的从表记录，可以将外键约束设置为 CASCADE，但这需要谨慎使用，因为可能会导致大量相关数据被删除。

4.4 CREATE、ALTER 与 DROP 关键字

1. CREATE 关键字：

• 创建数据库：

  • 使用 CREATE DATABASE 语句创建数据库，基本语法为：

CREATE DATABASE [IF NOT EXISTS] database_name;

其中，database_name 是要创建的数据库名称，IF NOT EXISTS 是可选的，用于防止在数据库已存在时创建失败并报错。例如：

CREATE DATABASE IF NOT EXISTS my_database;

这样，如果 my_database 数据库不存在，就会创建它；如果已存在，则不会进行任何操作，避免了错误。

• 创建表：

  • 创建表时，需要定义表的结构，包括列名、数据类型和约束等。基本语法为：

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 data_type [constraint],
    column2 data_type [constraint],
   ...
);

例如，创建一个 products 表，包含 product_id（主键，自增长）、product_name、price 和 quantity 列：

CREATE TABLE IF NOT EXISTS products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL
);

在这个例子中，product_id 列使用 INT 类型，设置为自增长（AUTO_INCREMENT），并作为主键（PRIMARY KEY）；product_name 列使用 VARCHAR(100) 类型，不允许为空（NOT NULL）；price 列使用 DECIMAL(10, 2) 类型表示精确的小数，也不允许为空；quantity 列使用 INT 类型且不允许为空。

2. ALTER 关键字：

• 修改表结构：

  • 添加列：使用 ALTER TABLE 语句添加列，语法为：

ALTER TABLE table_name ADD [COLUMN] column_name data_type [constraint];

例如，在 products 表中添加一个 description 列，数据类型为 TEXT：

ALTER TABLE products ADD COLUMN description TEXT;

COLUMN 关键字可以省略。

• 修改列：可以修改列的名称、数据类型或约束。

  • 修改列名：

ALTER TABLE table_name CHANGE old_column_name new_column_name data_type [constraint];

例如，将 products 表中的 product_name 列名改为 product_title：

ALTER TABLE products CHANGE product_name product_title VARCHAR(100) NOT NULL;

• 修改数据类型：

ALTER TABLE table_name MODIFY column_name new_data_type [constraint];

例如，将 products 表中 quantity 列的数据类型从 INT 修改为 BIGINT：

ALTER TABLE products MODIFY quantity BIGINT NOT NULL;

• 删除列：

ALTER TABLE table_name DROP [COLUMN] column_name;

例如，删除 products 表中的 description 列：

ALTER TABLE products DROP COLUMN description;

1. DROP 关键字：

• 删除数据库：

  • 使用 DROP DATABASE 语句删除数据库，语法为：

DROP DATABASE [IF EXISTS] database_name;

IF EXISTS 用于防止在数据库不存在时删除失败并报错。例如：

DROP DATABASE IF EXISTS my_database;

这样，如果 my_database 数据库存在，就会被删除；如果不存在，不会报错。

• 删除表：

  • 使用 DROP TABLE 语句删除表，语法为：

DROP TABLE [IF EXISTS] table_name;

例如，删除 products 表：

DROP TABLE IF EXISTS products;

DROP 操作是不可逆的，一旦执行，被删除的数据库或表及其所有数据将永久丢失，所以在执行 DROP 操作前一定要确认无误，尤其是在生产环境中。

下一篇MySQL数据库深度剖析：从基础到实战 （下）