MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。本文记录 MySQL 的基本知识。

概述 {#概述}

数据库 {#数据库}

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。

每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

也可以将数据存储在文件中，但是在文件中读写数据速度相对较慢。

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

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

1. 数据以表格的形式出现

2. 每行为各种记录名称

3. 每列为记录名称所对应的数据域

4. 许多的行和列组成一张表单

5. 若干的表单组成database

RDBMS 术语 {#RDBMS-术语}

先了解下RDBMS的一些术语：

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

MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:

• 表头(header): 每一列的名称;
• 列(col): 具有相同数据类型的数据的集合;
• 行(row): 每一行用来描述某条记录的具体信息;
• 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
• 键(key): 键的值在当前列中具有唯一性。

MySQL数据库 {#MySQL数据库}

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• MySQL 是开源的，目前隶属于 Oracle 旗下产品。
• MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL 使用标准的 SQL 数据语言形式。
• MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
• MySQL 对 PHP 有很好的支持，PHP 是很适合用于 Web 程序开发。
• MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。
• MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

MySQL 管理 {#MySQL-管理}

Windows 系统下 {#Windows-系统下}

启动 MySQL 服务器:

1、通过 "服务" 管理工具： 打开"运行"对话框（Win + R），输入 services.msc，找到"MySQL"服务，右击选择"启动"。

2、通过命令提示符： 打开命令提示符（以管理员身份），输入以下命令：

关闭MySQL服务器:

1、**通过 "服务" 管理工具：**同样打开"运行"对话框，输入 services.msc，找到 "MySQL" 服务，右击选择"停止"。

2、通过命令提示符： 打开命令提示符（以管理员身份），输入以下命令：

Linux 系统下 {#Linux-系统下}

1、启动 MySQL 服务：

使用 systemd命令（适用于大多数现代 Linux 发行版，如 Ubuntu、CentOS 等）：

使用 service 命令（在一些较旧的发行版中）：

2、关闭 MySQL 服务：

使用 systemd：

使用 service 命令：

3、重启 MySQL 服务：

使用 systemd：

使用 service 命令：

4、检查 MySQL 服务状态：

使用 systemd命令：

使用 service 命令：

Mac OS 系统 {#Mac-OS-系统}

启动 MySQL 服务：

使用命令行：

关闭 MySQL 服务：

使用命令行：

重启 MySQL 服务：

使用命令行：

检查 MySQL 服务状态：

使用命令行：

以上命令中，mysql 可能会因为安装路径或版本的不同而有所变化。

在 Mac OS 中，MySQL 的安装路径通常是 /usr/local/mysql/，因此启动和关闭 MySQL 服务需要使用这个路径下的 mysql.server 脚本。

MySQL 用户设置 {#MySQL-用户设置}

在 MySQL 中，用户设置包括创建用户、设置权限、管理用户等操作。以下是一些常用的 MySQL 用户设置操作，包括创建用户、设置权限、查看和删除用户等。

创建用户 {#创建用户}

要创建一个新用户，你可以使用以下 SQL 命令：

• username：用户名。
• host：指定用户可以从哪些主机连接。例如，localhost 仅允许本地连接，% 允许从任何主机连接。
• password：用户的密码。

实例

授权权限 {#授权权限}

创建用户后，你需要授予他们访问权限，使用 GRANT 命令来授予权限：

• privileges：所需的权限，如 ALL PRIVILEGES、SELECT、INSERT、UPDATE、DELETE 等。
• database_name.*：表示对某个数据库或表授予权限。database_name.* 表示对整个数据库的所有表授予权限，database_name.table_name 表示对指定的表授予权限。
• TO 'username'@'host'：指定授予权限的用户和主机。

实例

刷新权限 {#刷新权限}

授予或撤销权限后，需要刷新权限使更改生效：

• 举例：在数据库 test 上新建一位只读用户 reader

查看用户权限 {#查看用户权限}

要查看特定用户的权限，可以使用以下命令：

实例

撤销权限 {#撤销权限}

要撤销用户的权限，使用 REVOKE 命令：

实例

删除用户 {#删除用户}

如果需要删除用户，可以使用以下命令：

实例

修改用户密码 {#修改用户密码}

要修改用户的密码，可以使用 ALTER USER 命令：

实例

修改用户主机 {#修改用户主机}

要更改用户的主机（即允许从哪些主机连接），可以先删除用户，再重新创建一个新的用户。

实例

• 删除旧用户

• 重新创建用户并指定新的主机

创建用户时指定权限 {#创建用户时指定权限}

在创建用户时，也可以同时授予权限（在 MySQL 8.0.16 及更高版本）：

实例

/etc/my.cnf 文件配置 {#etc-my-cnf-文件配置}

/etc/my.cnf 文件是 MySQL 配置文件，用于配置 MySQL 服务器的各种参数和选项。

一般情况下，不需要修改该配置文件，该文件默认配置如下：

在配置文件中，你可以指定不同的错误日志文件存放的目录，一般你不需要改动这些配置。

/etc/my.cnf 文件在不同的系统和 MySQL 版本中可能有所不同，但是一般包含以下几个部分：

基本设置 {#基本设置}

• basedir: MySQL 服务器的基本安装目录。
• datadir: 存储 MySQL 数据文件的位置。
• socket: MySQL 服务器的 Unix 套接字文件路径。
• pid-file: 存储当前运行的 MySQL 服务器进程 ID 的文件路径。
• port: MySQL 服务器监听的端口号，默认是 3306。

服务器选项 {#服务器选项}

• bind-address: 指定 MySQL 服务器监听的 IP 地址，可以是 IP 地址或主机名。
• server-id: 在复制配置中，为每个 MySQL 服务器设置一个唯一的标识符。
• default-storage-engine: 默认的存储引擎，例如 InnoDB 或 MyISAM。
• max_connections: 服务器可以同时维持的最大连接数。
• thread_cache_size: 线程缓存的大小，用于提高新连接的启动速度。
• query_cache_size: 查询缓存的大小，用于提高相同查询的效率。
• default-character-set: 默认的字符集。
• collation-server: 服务器的默认排序规则。

性能调优 {#性能调优}

• innodb_buffer_pool_size: InnoDB 存储引擎的缓冲池大小，这是 InnoDB 性能调优中最重要的参数之一。
• key_buffer_size: MyISAM 存储引擎的键缓冲区大小。
• table_open_cache: 可以同时打开的表的缓存数量。
• thread_concurrency: 允许同时运行的线程数。

安全设置 {#安全设置}

• skip-networking: 禁止 MySQL 服务器监听网络连接，仅允许本地连接。
• skip-grant-tables: 以无需密码的方式启动 MySQL 服务器，通常用于恢复忘记的 root 密码，但这是一个安全风险。
• auth_native_password=1: 启用 MySQL 5.7 及以上版本的原生密码认证。

日志设置 {#日志设置}

• log_error: 错误日志文件的路径。
• general_log: 记录所有客户端连接和查询的日志。
• slow_query_log: 记录执行时间超过特定阈值的慢查询。
• log_queries_not_using_indexes: 记录未使用索引的查询。

复制设置 {#复制设置}

• master_host 和 master_user: 主服务器的地址和复制用户。
• master_password: 复制用户的密码。
• master_log_file 和 master_log_pos: 用于复制的日志文件和位置。

管理MySQL的命令 {#管理MySQL的命令}

以下列出了使用Mysql数据库过程中常用的命令：

• USE 数据库名 :
  选择要操作的Mysql数据库，使用该命令后所有Mysql命令都只针对该数据库。

• SHOW DATABASES:
  列出 MySQL 数据库管理系统的数据库列表。

• SHOW TABLES:
  显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。

• SHOW COLUMNS FROM *数据表*:
  显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。

• SHOW INDEX FROM *数据表*:
  显示数据表的详细索引信息，包括PRIMARY KEY（主键）。

• SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] \G:
  该命令将输出Mysql数据库管理系统的性能及统计信息。

MySQL 连接 {#MySQL-连接}

您可以使用 MySQL 二进制方式进入到 mysql 命令提示符下来连接 MySQL 数据库，格式如下：

参数说明：

• -u 参数用于指定用户名。
• -p 参数表示需要输入密码。

以下是从命令行中连接 mysql 服务器的简单实例：

按照提示输入密码，并按下 Enter 键。

在登录成功后会出现 mysql> 命令提示窗口，你可以在上面执行任何 SQL 语句。

数据库操作 {#数据库操作}

创建数据库 {#创建数据库}

我们可以在登陆 MySQL 服务后，使用 create 命令创建数据库，语法如下:

以下命令简单的演示了创建数据库的过程，数据名为 RUNOOB:

建数据库的基本语法如下：

如果你希望在创建数据库时指定一些选项，可以使用 CREATE DATABASE 语句的其他参数，例如，你可以指定字符集和排序规则：

如果数据库已经存在，执行 CREATE DATABASE 将导致错误。

为了避免这种情况，你可以在 CREATE DATABASE 语句中添加 IF NOT EXISTS 子句：

删除数据库 {#删除数据库}

使用普通用户登陆 MySQL 服务器，你可能需要特定的权限来创建或者删除 MySQL 数据库，所以我们这边使用 root 用户登录，root 用户拥有最高权限。

在删除数据库过程中，务必要十分谨慎，因为在执行删除命令后，所有数据将会消失。

drop 命令删除数据库

drop 命令格式：

参数说明：

• IF EXISTS 是一个可选的子句，表示如果数据库存在才执行删除操作，避免因为数据库不存在而引发错误。
• database_name 是你要删除的数据库的名称。

例如删除名为 RUNOOB 的数据库：

注意： 在执行删除数据库操作前，请确保你确实想要删除数据库及其所有数据，因为该操作是不可逆的。为了避免误操作，通常建议在执行删除之前备份数据库。

选择数据库 {#选择数据库}

在你连接到 MySQL 数据库后，可能有多个可以操作的数据库，所以你需要选择你要操作的数据库。

从命令提示窗口中选择 MySQL 数据库

在 mysql> 提示窗口中可以很简单的选择特定的数据库。

在 MySQL 中，要选择要使用的数据库，可以使用 USE 语句，以下是基本的语法：

参数说明：

• database_name 是你要选择的数据库的名称。

选择来数据库后，你的后续 SQL 查询和操作在指定的数据库 database_name 上执行。

MySQL 数据类型 {#MySQL-数据类型}

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL 支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

数值类型 {#数值类型}

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

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 | |-------------|----------------------------------|---------------------------------------------------------------------------------------------------------------------------------|-----------------------------------------------------------------|----------| | TINYINT | 1 Bytes | (-128，127) | (0，255) | 小整数值 | | SMALLINT | 2 Bytes | (-32 768，32 767) | (0，65 535) | 大整数值 | | MEDIUMINT | 3 Bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 | | INT或INTEGER | 4 Bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 | | BIGINT | 8 Bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 | | FLOAT | 4 Bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 | | DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 | | DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |

日期和时间类型 {#日期和时间类型}

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

| 类型 | 大小 ( bytes) | 范围 | 格式 | 用途 | |-----------|-------------|----------------------------------------------------------------------------------------------------------------------------------------|---------------------|--------------| | DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 | | TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 | | YEAR | 1 | 1901/2155 | YYYY | 年份值 | | DATETIME | 8 | '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59' | YYYY-MM-DD hh:mm:ss | 混合日期和时间值 | | TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07' UTC结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYY-MM-DD hh:mm:ss | 混合日期和时间值，时间戳 |

字符串类型 {#字符串类型}

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

| 类型 | 大小 | 用途 | |------------|-----------------------|--------------------| | CHAR | 0-255 bytes | 定长字符串 | | VARCHAR | 0-65535 bytes | 变长字符串 | | TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 | | TINYTEXT | 0-255 bytes | 短文本字符串 | | BLOB | 0-65 535 bytes | 二进制形式的长文本数据 | | TEXT | 0-65 535 bytes | 长文本数据 | | MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 | | MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 | | LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 | | LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

枚举与集合类型（Enumeration and Set Types） {#枚举与集合类型（Enumeration-and-Set-Types）}

• ENUM: 枚举类型，用于存储单一值，可以选择一个预定义的集合。
• SET: 集合类型，用于存储多个值，可以选择多个预定义的集合。

空间数据类型（Spatial Data Types） {#空间数据类型（Spatial-Data-Types）}

GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION: 用于存储空间数据（地理信息、几何图形等）。

数据表操作 {#数据表操作}

创建数据表 {#创建数据表}

创建 MySQL 数据表需要以下信息：

• 表名
• 表字段名
• 定义每个表字段的数据类型

语法

以下为创建 MySQL 数据表的 SQL 通用语法：

参数说明：

• table_name 是你要创建的表的名称。
• column1, column2, ... 是表中的列名。
• datatype 是每个列的数据类型。

以下是一个具体的实例，创建一个用户表 users：

实例解析：

• id: 用户 id，整数类型，自增长，作为主键。
• username: 用户名，变长字符串，不允许为空。
• email: 用户邮箱，变长字符串，不允许为空。
• birthdate: 用户的生日，日期类型。
• is_active: 用户是否已经激活，布尔类型，默认值为 true。

以上只是一个简单的实例，用到了一些常见的数据类型包括 INT, VARCHAR, DATE, BOOLEAN，你可以根据实际需要选择不同的数据类型。AUTO_INCREMENT 关键字用于创建一个自增长的列，PRIMARY KEY 用于定义主键。

如果你希望在创建表时指定数据引擎，字符集和排序规则等，可以使用 CHARACTER SET 和 COLLATE 子句：

以上代码创建一个使用 utf8mb4 字符集和 utf8mb4_general_ci 排序规则的表。

以下例子中我们将在 RUNOOB 数据库中创建数据表 runoob_tbl：

实例解析：

• 如果你不想字段为空 可以设置字段的属性为 NOT NULL，如上实例中的 runoob_title 与 runoob_author 字段， 在操作数据库时如果输入该字段的数据为空，就会报错。
• AUTO_INCREMENT 定义列为自增的属性，一般用于主键，数值会自动加 1。
• PRIMARY KEY 关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号 , 分隔。
• ENGINE 设置存储引擎，CHARSET 设置编码。

删除数据表 {#删除数据表}

MySQL中删除数据表是非常容易操作的，但是你在进行删除表操作时要非常小心，因为执行删除命令后所有数据都会消失。

语法

以下为删除 MySQL 数据表的通用语法：

或

参数说明：

• table_name 是要删除的表的名称。
• IF EXISTS 是一个可选的子句，表示如果表存在才执行删除操作，避免因为表不存在而引发错误。

请替换 mytable 为你要删除的表的名称。

如果你只是想删除表中的所有数据，但保留表的结构，可以使用 TRUNCATE TABLE 语句：

这会清空表中的所有数据，但不会删除表本身。

注意事项：

• 备份数据：在删除表之前，确保已经备份了数据，如果你需要的话。
• 外键约束：如果该表与其他表有外键约束，可能需要先删除外键约束，或者确保依赖关系被处理好。

插入数据 {#插入数据}

MySQL 表中使用 INSERT INTO 语句来插入数据。

你可以通过 mysql> 命令提示窗口中向数据表中插入数据，或者通过PHP脚本来插入数据。

语法

以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

参数说明：

• table_name 是你要插入数据的表的名称。
• column1, column2, column3, ... 是表中的列名。
• value1, value2, value3, ... 是要插入的具体数值。

如果数据是字符型，必须使用单引号 ' 或者双引号 "，如： 'value1', "value1"。

一个简单的实例，插入了一行数据到名为 users 的表中：

• username: 用户名，字符串类型。
• email: 邮箱地址，字符串类型。
• birthdate: 用户生日， 日期类型。
• is_active: 是否已激活，布尔类型。

如果你要插入所有列的数据，可以省略列名：

这里，NULL 是用于自增长列的占位符，表示系统将为 id 列生成一个唯一的值。

如果你要插入多行数据，可以在 VALUES 子句中指定多组数值：

以上代码将在 users 表中插入三行数据。

查询数据 {#查询数据}

MySQL 数据库使用 SELECT 语句来查询数据。

你可以通过 mysql> 命令提示窗口中在数据库中查询数据，或者通过 PHP 脚本来查询数据。

语法

以下为在 MySQL 数据库中查询数据通用的 SELECT 语法：

参数说明：

• column1, column2, ... 是你想要选择的列的名称，如果使用 * 表示选择所有列。
• table_name 是你要从中查询数据的表的名称。
• WHERE condition 是一个可选的子句，用于指定过滤条件，只返回符合条件的行。
• ORDER BY column_name [ASC | DESC] 是一个可选的子句，用于指定结果集的排序顺序，默认是升序（ASC）。
• LIMIT number 是一个可选的子句，用于限制返回的行数。

SELECT 语句可以是灵活的，我们可以根据实际需求组合和使用这些子句，比如同时使用 WHERE 和 ORDER BY 子句，或者使用 LIMIT 控制返回的行数。

在 WHERE 子句中，你可以使用各种条件运算符（如 =, <, >, <=, >=, !=），逻辑运算符（如 AND, OR, NOT），以及通配符（如 %）等。

以下是一些进阶的 SELECT 语句实例：

WHERE 子句 {#WHERE-子句}

我们知道从 MySQL 表中使用 SELECT 语句来读取数据。

如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

WHERE 子句用于在 MySQL 中过滤查询结果，只返回满足特定条件的行。

语法

以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：

参数说明：

• column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
• table_name 是你要从中查询数据的表的名称。
• WHERE condition 是用于指定过滤条件的子句。

更多说明：

• 查询语句中你可以使用一个或者多个表，表之间使用逗号**,** 分割，并使用WHERE语句来设定查询条件。
• 你可以在 WHERE 子句中指定任何条件。
• 你可以使用 AND 或者 OR 指定一个或多个条件。
• WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
• WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

以下为操作符列表，可用于 WHERE 子句中。

下表中实例假定 A 为 10, B 为 20

| 操作符 | 描述 | 实例 | |----------|------------------------------------------------|--------------------| | = | 等号，检测两个值是否相等，如果相等返回true | (A = B) 返回false。 | | <>, != | 不等于，检测两个值是否相等，如果不相等返回true | (A != B) 返回 true。 | | > | 大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true | (A > B) 返回false。 | | < | 小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true | (A < B) 返回 true。 | | >= | 大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true | (A >= B) 返回false。 | | <= | 小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true | (A <= B) 返回 true。 |

简单实例

1. 等于条件：

2. 不等于条件：

3. 大于条件:

4. 小于条件:

5. 大于等于条件:

6. 小于等于条件:

7. 组合条件（AND、OR）:

8. 模糊匹配条件（LIKE）:

9. IN 条件:

10. NOT 条件:

11. BETWEEN 条件:

12. IS NULL 条件

13. IS NOT NULL 条件:

如果我们想在 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。

使用主键来作为 WHERE 子句的条件查询是非常快速的。

如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。

UPDATE 更新 {#UPDATE-更新}

如果我们需要修改或更新 MySQL 中的数据，我们可以使用 UPDATE 命令来操作。

语法

以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

参数说明：

• table_name 是你要更新数据的表的名称。
• column1, column2, ... 是你要更新的列的名称。
• value1, value2, ... 是新的值，用于替换旧的值。
• WHERE condition 是一个可选的子句，用于指定更新的行。如果省略 WHERE 子句，将更新表中的所有行。

更多说明：

• 你可以同时更新一个或多个字段。
• 你可以在 WHERE 子句中指定任何条件。
• 你可以在一个单独表中同时更新数据。

当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

实例

以下实例演示了如何使用 UPDATE 语句。

1. 更新单个列的值：

2. 更新多个列的值：

3. 使用表达式更新值：

以上 SQL 语句将每个属于 'Electronics' 类别的产品的价格都增加了 10%。

4. 更新符合条件的所有行：

以上 SQL 语句将所有学生的状态更新为 'Graduated'。

5. 更新使用子查询的值：

以上 SQL 语句通过子查询计算每个 'Premium' 类型客户的总购买金额，并将该值更新到 total_purchases 列中。

注意： 在使用 UPDATE 语句时，请确保你提供了足够的条件来确保只有你想要更新的行被修改。如果不提供 WHERE 子句，将更新表中的所有行，可能导致不可预测的结果。

DELETE 语句 {#DELETE-语句}

你可以使用 DELETE FROM 命令来删除 MySQL 数据表中的记录。

你可以在 mysql> 命令提示符或 PHP 脚本中执行该命令。

语法

以下是 DELETE 语句从 MySQL 数据表中删除数据的通用语法：

参数说明：

• table_name 是你要删除数据的表的名称。
• WHERE condition 是一个可选的子句，用于指定删除的行。如果省略 WHERE 子句，将删除表中的所有行。

更多说明：

• 如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
• 你可以在 WHERE 子句中指定任何条件
• 您可以在单个表中一次性删除记录。

当你想删除数据表中指定的记录时 WHERE 子句是非常有用的。

实例

以下实例演示了如何使用 DELETE 语句。

1. 删除符合条件的行：

以上 SQL 语句删除了 students 表中所有 graduation_year 为 2021 的学生的记录。

2. 删除所有行：

以上 SQL 语句删除了 orders 表中的所有记录，但表结构保持不变。

3. 使用子查询删除符合条件的行：

以上 SQL 语句通过子查询删除了 orders 表中在 '2023-01-01' 之前下的订单对应的客户。

注意： 在使用 DELETE 语句时，请确保你提供了足够的条件来确保只有你想要删除的行被删除。如果不提供 WHERE 子句，将删除表中的所有行，可能导致不可预测的结果。

LIKE 子句 {#LIKE-子句}

我们知道在 MySQL 中使用 SELECT 命令来读取数据， 同时我们可以在 SELECT 语句中使用 WHERE 子句来获取指定的记录。

WHERE 子句中可以使用等号 = 来设定获取数据的条件，如 "runoob_author = 'RUNOOB.COM'"。

但是有时候我们需要获取 runoob_author 字段含有 "COM" 字符的所有记录，这时我们就需要在 WHERE 子句中使用 LIKE 子句。

LIKE 子句是在 MySQL 中用于在 WHERE 子句中进行模糊匹配的关键字。它通常与通配符一起使用，用于搜索符合某种模式的字符串。

LIKE 子句中使用百分号 **%**字符来表示任意字符，类似于UNIX或正则表达式中的星号 *****。

如果没有使用百分号 % , LIKE 子句与等号 = 的效果是一样的。

语法

以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：

参数说明：

• column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
• table_name 是你要从中查询数据的表的名称。
• column_name 是你要应用 LIKE 子句的列的名称。
• pattern 是用于匹配的模式，可以包含通配符。

更多说明：

• 你可以在 WHERE 子句中指定任何条件。
• 你可以在 WHERE 子句中使用LIKE子句。
• 你可以使用LIKE子句代替等号 =。
• LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
• 你可以使用 AND 或者 OR 指定一个或多个条件。
• 你可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件。

实例

以下是一些 LIKE 子句的使用实例。

1. 百分号通配符 %：

% 通配符表示零个或多个字符。例如，'a%' 匹配以字母 'a' 开头的任何字符串。

以上 SQL 语句将选择所有姓氏以 'S' 开头的客户。

2. 下划线通配符 _：

_ 通配符表示一个字符。例如，'_r%' 匹配第二个字母为 'r' 的任何字符串。

以上 SQL 语句将选择产品名称的第二个字符为 'a' 的所有产品。

3. 组合使用 % 和 _：

以上 SQL 语句将匹配以字母 'a' 开头，然后是零个或多个字符，接着是 'o'，最后是一个任意字符的字符串，如 'aaron'、'apol'。

4. 不区分大小写的匹配：

以上 SQL 语句将选择姓氏以 'smi' 开头的所有员工，不区分大小写。

LIKE 子句提供了强大的模糊搜索能力，可以根据不同的模式和需求进行定制。在使用时，请确保理解通配符的含义，并根据实际情况进行匹配。

UNION 操作符 {#UNION-操作符}

本教程为大家介绍 MySQL UNION 操作符的语法和实例。

描述

MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合，并去除重复的行。

UNION 操作符必须由两个或多个 SELECT 语句组成，每个 SELECT 语句的列数和对应位置的数据类型必须相同。

语法

MySQL UNION 操作符语法格式：

参数说明：

• column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
• table1, table2, ... 是你要从中查询数据的表的名称。
• condition1, condition2, ... 是每个 SELECT 语句的过滤条件，是可选的。
• ORDER BY 子句是一个可选的子句，用于指定合并后的结果集的排序顺序。

实例

1. 基本的 UNION 操作：

以上 SQL 语句将选择客户表和供应商表中所有城市的唯一值，并按城市名称升序排序。

2. 使用过滤条件的 UNION：

以上 SQL 语句将选择电子产品和服装类别的产品名称，并按产品名称升序排序。

3. UNION 操作中的列数和数据类型必须相同：

以上 SQL 语句中，departments 表中的 department_name 列被映射到了 employees 表中的 last_name 列，但是列数和数据类型必须相同。

4. 使用 UNION ALL 不去除重复行：

以上 SQL 语句使用 UNION ALL 将客户表和供应商表中的所有城市合并在一起，不去除重复行。

UNION 操作符在合并结果集时会去除重复行，而 UNION ALL 不会去除重复行，因此 UNION ALL 的性能可能更好，但如果你确实希望去除重复行，可以使用 UNION。

示例 {#示例}

演示数据库

在本教程中，我们将使用 RUNOOB 样本数据库。

下面是选自 "Websites" 表的数据：

下面是 "apps" APP 的数据：

UNION 实例 {#UNION-实例}

• 下面的 SQL 语句从 "Websites" 和 "apps" 表中选取所有不同的country（只有不同的值）：

执行以上 SQL 输出结果如下：

**注释：**UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家，每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值！

UNION ALL 实例 {#UNION-ALL-实例}

下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的country（也有重复的值）：

执行以上 SQL 输出结果如下：

带有 WHERE 的 SQL UNION ALL {#带有-WHERE-的-SQL-UNION-ALL}

下面的 SQL 语句使用 UNION ALL 从 "Websites" 和 "apps" 表中选取所有的中国(CN)的数据（也有重复的值）：

执行以上 SQL 输出结果如下：

ORDER BY(排序) 语句 {#ORDER-BY-排序-语句}

我们知道从 MySQL 表中使用 SELECT 语句来读取数据。

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。

MySQL ORDER BY(排序) 语句可以按照一个或多个列的值进行升序（ASC ）或降序（DESC）排序。

语法

以下是 SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：

参数说明：

• column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
• table_name 是你要从中查询数据的表的名称。
• ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ... 是用于指定排序顺序的子句。ASC 表示升序（默认），DESC 表示降序。

更多说明：

• 你可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 你可以设定多个字段来排序。
• 你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
• 你可以添加 WHERE...LIKE 子句来设置条件。

实例

以下是一些 ORDER BY 子句的使用实例。

1. 单列排序：

以上 SQL 语句将选择产品表 products 中的所有产品，并按产品名称升序 ASC 排序。

2. 多列排序：

以上 SQL 语句将选择员工表 employees 中的所有员工，并先按部门 ID 升序 ASC 排序，然后在相同部门中按雇佣日期降序 DESC 排序。

3. 使用数字表示列的位置：

以上 SQL 语句将选择员工表 employees 中的名字和工资列，并按第三列（salary）降序 DESC 排序，然后按第一列（first_name）升序 ASC 排序。

4. 使用表达式排序：

以上 SQL 语句将选择产品表 products 中的产品名称和根据折扣率计算的折扣后价格，并按折扣后价格降序 DESC 排序。

5.从 MySQL 8.0.16 版本开始，可以使用 NULLS FIRST 或 NULLS LAST 处理 NULL 值：

以上 SQL 语句将选择产品表 products 中的产品名称和价格，并按价格降序 DESC 排序，将 NULL 值排在最后。

相反，如果你想让 NULL 值排在前面，可以这样写：

ORDER BY 子句是一个强大的工具，可以根据不同的业务需求对查询结果进行排序。在实际应用中，注意选择适当的列和排序顺序，以获得符合期望的排序效果。

MySQL GROUP BY 语句 {#MySQL-GROUP-BY-语句}

GROUP BY 语句根据一个或多个列对结果集进行分组。

在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。

GROUP BY 语句是 SQL 查询中用于汇总和分析数据的重要工具，尤其在处理大量数据时，它能够提供有用的汇总信息。

GROUP BY 语法

• column1：指定分组的列。
• aggregate_function(column2)：对分组后的每个组执行的聚合函数。
• table_name：要查询的表名。
• condition：可选，用于筛选结果的条件。

假设有一个名为 orders 的表，包含以下列：order_id、customer_id、order_date 和 order_amount。

我们想要按照 customer_id 进行分组，并计算每个客户的订单总金额，SQL 语句如下：

实例

以上实例中，我们使用 GROUP BY customer_id 将结果按 customer_id 列分组，然后使用 SUM(order_amount) 计算每个组中 order_amount 列的总和。

AS total_amount 是为了给计算结果取一个别名，使查询结果更易读。

注意事项：

• GROUP BY 子句通常与聚合函数一起使用，因为分组后需要对每个组进行聚合操作。
• SELECT 子句中的列通常要么是分组列，要么是聚合函数的参数。
• 可以使用多个列进行分组，只需在 GROUP BY 子句中用逗号分隔列名即可。

实例

连接的使用 {#连接的使用}

在前几章节中，我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。

本章节我们将向大家介绍如何使用 MySQL 的 JOIN 在两个或多个表中查询数据。

你可以在 SELECT, UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

• INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
• **LEFT JOIN（左连接）：**获取左表所有记录，即使右表没有对应匹配的记录。
• RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

本章节使用的数据库结构及数据下载：runoob-mysql-join-test.sql。

INNER JOIN {#INNER-JOIN}

INNER JOIN 返回两个表中满足连接条件的匹配行，以下是 INNER JOIN 语句的基本语法：

参数说明：

• column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
• table1, table2 是要连接的两个表的名称。
• table1.column_name = table2.column_name 是连接条件，指定了两个表中用于匹配的列。

简单的 INNER JOIN：

以上 SQL 语句将选择 orders 表和 customers 表中满足连接条件的订单 ID 和客户名称。

2. 使用表别名：

以上 SQL 语句使用表别名 o 和 c 作为 orders 和 customers 表的别名。

3. 多表 INNER JOIN：

以上 SQL 语句涉及了 orders、customers、order_items 和 products 四个表的连接。它选择了订单 ID、客户名称和产品名称，连接了这些表的关联列。

4. 使用 WHERE 子句进行过滤：

以上 SQL 语句在 INNER JOIN 后使用 WHERE 子句，过滤了订单日期在 '2023-01-01' 及以后的订单。

LEFT JOIN {#LEFT-JOIN}

LEFT JOIN 返回左表的所有行，并包括右表中匹配的行，如果右表中没有匹配的行，将返回 NULL 值，以下是 LEFT JOIN 语句的基本语法：

1. 简单的 LEFT JOIN：

以上 SQL 语句将选择客户表中的客户 ID 和客户名称，并包括左表 customers 中的所有行，以及匹配的订单 ID（如果有的话）。

2. 使用表别名：

以上 SQL 语句使用表别名 c 和 o 分别代替 customers 和 orders 表的名称。

3. 多表 LEFT JOIN：

以上 SQL 语句连接了 customers、orders、order_items 和 products 四个表，并选择了客户 ID、客户名称、订单 ID 和产品名称。左连接保证了即使在 order_items 或 products 中没有匹配的行，仍然会返回客户和订单的信息。

4. 使用 WHERE 子句进行过滤：

以上 SQL 语句在 LEFT JOIN 后使用 WHERE 子句，过滤了订单日期在 '2023-01-01' 及以后的订单，以及没有匹配订单的客户。

LEFT JOIN 是一种常用的连接类型，尤其在需要返回左表中所有行的情况下。当右表中没有匹配的行时，相关列将显示为 NULL。在使用 LEFT JOIN 时，请确保理解连接条件并根据需求过滤结果。

RIGHT JOIN {#RIGHT-JOIN}

RIGHT JOIN 返回右表的所有行，并包括左表中匹配的行，如果左表中没有匹配的行，将返回 NULL 值，以下是 RIGHT JOIN 语句的基本语法：：

以下是一个简单的 RIGHT JOIN 实例：

以上 SQL 语句将选择右表 orders 中的所有订单 ID，并包括左表 customers 中匹配的客户 ID。如果在 customers 表中没有匹配的客户 ID，相关列将显示为 NULL。

在开发过程中中，RIGHT JOIN 并不经常使用，因为它可以用 LEFT JOIN 和表的顺序交换来实现相同的效果。例如，上面的查询可以通过使用 LEFT JOIN 改写为：

以上 SQL 语句返回相同的结果，因为 LEFT JOIN 与 RIGHT JOIN 是对称的。在实际使用中，你可以根据个人偏好或组织规范选择使用哪种形式。

NULL 值处理 {#NULL-值处理}

我们已经知道 MySQL 使用 SELECT 命令及 WHERE 子句来读取数据表中的数据，但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。

在 MySQL 中，NULL 用于表示缺失的或未知的数据，处理 NULL 值需要特别小心，因为在数据库中它可能会导致不同于预期的结果。

为了处理这种情况，MySQL提供了三大运算符:

• IS NULL: 当列的值是 NULL,此运算符返回 true。
• IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
• <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。

在 MySQL 中，NULL 值与任何其它值的比较（即使是 NULL）永远返回 NULL，即 NULL = NULL 返回 NULL 。

MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。

注意：

columnName1，columnName2 为 int 型，当 columnName2 中，有值为 null 时，columnName1+columnName2=null， ifnull(columnName2,0) 把 columnName2 中 null 值转为 0。

MySQL 中处理 NULL 值的常见注意事项和技巧

1. 检查是否为 NULL：

要检查某列是否为 NULL，可以使用 IS NULL 或 IS NOT NULL 条件。

2. 使用 COALESCE 函数处理 NULL：

COALESCE 函数可以用于替换为 NULL 的值，它接受多个参数，返回参数列表中的第一个非 NULL 值：

以上 SQL 语句中，如果 stock_quantity 列为 NULL，则 COALESCE 将返回 0。

3. 使用 IFNULL 函数处理 NULL：

IFNULL 函数是 COALESCE 的 MySQL 特定版本，它接受两个参数，如果第一个参数为 NULL，则返回第二个参数。

4. NULL 排序：

在使用 ORDER BY 子句进行排序时，NULL 值默认会被放在排序的最后。如果希望将 NULL 值放在最前面，可以使用 ORDER BY column_name ASC NULLS FIRST，反之使用 ORDER BY column_name DESC NULLS LAST。

5. 使用 <=> 操作符进行 NULL 比较：

<=> 操作符是 MySQL 中用于比较两个表达式是否相等的特殊操作符，对于 NULL 值的比较也会返回 TRUE。它可以用于处理 NULL 值的等值比较。

6. 注意聚合函数对 NULL 的处理：

在使用聚合函数（如 COUNT, SUM, AVG）时，它们会忽略 NULL 值，因此可能会得到不同于预期的结果。如果希望将 NULL 视为 0，可以使用 COALESCE 或 IFNULL。

这样即使 salary 为 NULL，聚合函数也会将其视为 0。

处理 NULL 值时，要特别小心确保查询和操作的语义符合预期。在设计表结构时，也需要考虑 NULL 值的使用场景和合理性。

正则表达式 {#正则表达式}

在前面的章节我们已经了解到 MySQL 可以通过 LIKE ...% 来进行模糊匹配。

MySQL 同样也支持其他正则表达式的匹配， MySQL 中使用 REGEXP 和 RLIKE操作符来进行正则表达式匹配。

如果您了解 PHP 或 Perl，那么操作起来就非常简单，因为 MySQL 的正则表达式匹配与这些脚本的类似。

下表中的正则模式可应用于 REGEXP 操作符中。

| 模式 | 描述 | |------------|--------------------------------------------------------------------------------| | ^ | 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。 | | $ | 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 '\n' 或 '\r' 之前的位置。 | | . | 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符，请使用像 '[.\n]' 的模式。 | | [...] | 字符集合。匹配所包含的任意一个字符。例如， '[abc]' 可以匹配 "plain" 中的 'a'。 | | [^...] | 负值字符集合。匹配未包含的任意字符。例如， '[^abc]' 可以匹配 "plain" 中的'p'。 | | p1|p2|p3 | 匹配 p1 或 p2 或 p3。例如，'z|food' 能匹配 "z" 或 "food"。'(z|f)ood' 则匹配 "zood" 或 "food"。 | | * | 匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。 | | + | 匹配前面的子表达式一次或多次。例如，'zo+' 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。 | | {n} | n 是一个非负整数。匹配确定的 n 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。 | | {n,m} | m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。 |

正则表达式匹配的字符类 {#正则表达式匹配的字符类}

• .：匹配任意单个字符。
• ^：匹配字符串的开始。
• $：匹配字符串的结束。
• *：匹配零个或多个前面的元素。
• +：匹配一个或多个前面的元素。
• ?：匹配零个或一个前面的元素。
• [abc]：匹配字符集中的任意一个字符。
• [^abc]：匹配除了字符集中的任意一个字符以外的字符。
• [a-z]：匹配范围内的任意一个小写字母。
• [0-9]：匹配一个数字字符。
• \w：匹配一个字母数字字符（包括下划线）。
• \s：匹配一个空白字符。

使用 REGEXP 进行模式匹配 {#使用-REGEXP-进行模式匹配}

REGEXP 是用于进行正则表达式匹配的运算符。

REGEXP 用于检查一个字符串是否匹配指定的正则表达式模式，以下是 REGEXP 运算符的基本语法：

参数说明：

• column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
• table_name 是你要从中查询数据的表的名称。
• column_name 是你要进行正则表达式匹配的列的名称。
• 'pattern' 是一个正则表达式模式。

查找 name 字段中以 'st' 为开头的所有数据：

查找 name 字段中以 'ok' 为结尾的所有数据：

查找 name 字段中包含 'mar' 字符串的所有数据：

查找 name 字段中以元音字符开头或以 'ok' 字符串结尾的所有数据：

选择订单表中描述中包含 "item" 后跟一个或多个数字的记录。

使用 BINARY 关键字，使得匹配区分大小写：

使用 OR 进行多个匹配条件，以下将选择姓氏为 "Smith" 或 "Johnson" 的员工记录：

使用 RLIKE 进行模式匹配 {#使用-RLIKE-进行模式匹配}

RLIKE 是 MySQL 中用于进行正则表达式匹配的运算符，与 REGEXP 是一样的，RLIKE 和 REGEXP 可以互换使用，没有区别。

以下是使用 RLIKE 进行正则表达式匹配的基本语法：

参数说明：

• column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
• table_name 是你要从中查询数据的表的名称。
• column_name 是你要进行正则表达式匹配的列的名称。
• 'pattern' 是一个正则表达式模式。

以上 SQL 语句选择产品名称以数字开头的所有产品。

事务 {#事务}

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中，事务是一组SQL语句的执行，它们被视为一个单独的工作单元。

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert、update、delete 语句

一般来说，事务是必须满足4个条件（ACID）：：原子性（A tomicity，或称不可分割性）、一致性（C onsistency）、隔离性（I solation，又称独立性）、持久性（Durability）。

• **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事务控制语句 {#事务控制语句}

• BEGIN 或 START TRANSACTION 显式地开启一个事务；
• COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
• RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier 把事务回滚到标记点；
• SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT 来实现

• BEGIN 或 START TRANSACTION：开用于开始一个事务。
• ROLLBACK 事务回滚，取消之前的更改。
• COMMIT：事务确认，提交事务，使更改永久生效。

2、直接用 SET 来改变 MySQL 的自动提交模式:

• SET AUTOCOMMIT=0 禁止自动提交
• SET AUTOCOMMIT=1 开启自动提交

BEGIN 或 START TRANSACTION -- 用于开始一个事务：

COMMIT -- 用于提交事务，将所有的修改永久保存到数据库：

ROLLBACK -- 用于回滚事务，撤销自上次提交以来所做的所有更改：

SAVEPOINT -- 用于在事务中设置保存点，以便稍后能够回滚到该点：

ROLLBACK TO SAVEPOINT -- 用于回滚到之前设置的保存点：

下面是一个简单的 MySQL 事务的例子：

事务测试

ALTER 命令 {#ALTER-命令}

当我们需要修改数据表名或者修改数据表字段时，就需要使用到 MySQL ALTER 命令。

MySQL 的 ALTER 命令用于修改数据库、表和索引等对象的结构。

ALTER 命令允许你添加、修改或删除数据库对象，并且可以用于更改表的列定义、添加约束、创建和删除索引等操作。

ALTER 命令非常强大，可以在数据库结构发生变化时进行灵活的修改和调整。

以下是 ALTER 命令的常见用法和实例：

1. 添加列

以下 SQL 语句在 employees 表中添加了一个名为 birth_date 的日期列：

2. 修改列的数据类型

以下 SQL 语句将 employees 表中的 salary 列的数据类型修改为 DECIMAL(10,2)：

3. 修改列名

以下 SQL 语句将 employees 表中的某个列的名字由 old_column_name 修改为 new_column_name，并且可以同时修改数据类型：

4. 删除列

以下 SQL 语句将 employees 表中的 birth_date 列删除：

5. 添加 PRIMARY KEY

以下 SQL 语句在 employees 表中添加了一个主键：

6. 添加 FOREIGN KEY

以下 SQL 语句在 orders 表中添加了一个外键，关联到 customers 表的 customer_id 列：

7. 修改表名

以下 SQL 语句将表名由 employees 修改为 staff：

**注意：**在使用 ALTER 命令时要格外小心，因为一些操作可能需要重建表或索引，这可能会影响数据库的性能和运行时间。

在进行重要的结构修改时，建议先备份数据，并在生产环境中谨慎操作。

索引 {#索引}

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。

MySQL 索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。

打个比方，如果合理的设计且使用索引的 MySQL 是一辆兰博基尼的话，那么没有设计和使用索引的 MySQL 就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引：

• 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引。
• 组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

索引虽然能够提高查询性能，但也需要注意以下几点：

• 索引需要占用额外的存储空间。
• 对表进行插入、更新和删除操作时，索引需要维护，可能会影响性能。
• 过多或不合理的索引可能会导致性能下降，因此需要谨慎选择和规划索引。

普通索引 {#普通索引}

索引能够显著提高查询的速度，尤其是在大型表中进行搜索时。通过使用索引，MySQL 可以直接定位到满足查询条件的数据行，而无需逐行扫描整个表。

创建索引 {#创建索引}

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

普通索引是最常见的索引类型，用于加速对表中数据的查询。

CREATE INDEX 的语法：

• CREATE INDEX: 用于创建普通索引的关键字。
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
• table_name: 指定要在哪个表上创建索引。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

以下实例假设我们有一个名为 students 的表，包含 id、name 和 age 列，我们将在 name 列上创建一个普通索引。

上述语句将在 students 表的 name 列上创建一个名为 idx_name 的普通索引，这将有助于提高通过姓名进行搜索的查询性能。

需要注意的是，如果表中的数据量较大，索引的创建可能会花费一些时间，但一旦创建完成，查询性能将会显著提高。

修改表结构(添加索引) {#修改表结构-添加索引}

我们可以使用 ALTER TABLE 命令可以在已有的表中创建索引。

ALTER TABLE 允许你修改表的结构，包括添加、修改或删除索引。

ALTER TABLE 创建索引的语法：

• ALTER TABLE: 用于修改表结构的关键字。
• table_name: 指定要修改的表的名称。
• ADD INDEX: 添加索引的子句。ADD INDEX用于创建普通索引。
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

下面是一个实例，我们将在已存在的名为 employees 的表上创建一个普通索引:

上述语句将在 employees 表的 age 列上创建一个名为 idx_age 的普通索引。

创建表的时候直接指定 {#创建表的时候直接指定}

我们可以在创建表的时候，你可以在 CREATE TABLE 语句中直接指定索引，以创建表和索引的组合。

• CREATE TABLE: 用于创建新表的关键字。
• table_name: 指定要创建的表的名称。
• (column1 data_type, column2 data_type, ...): 定义表的列名和数据类型。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• INDEX: 用于创建普通索引的关键字。
• index_name: 指定要创建的索引的名称。索引名称在表中必须是唯一的。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

下面是一个实例，我们要创建一个名为 students 的表，并在 age 列上创建一个普通索引。

在上述实例中，我们在 students 表的 age 列上创建了一个名为 idx_age 的普通索引。

删除索引的语法 {#删除索引的语法}

我们可以使用 DROP INDEX 语句来删除索引。

DROP INDEX 的语法：

• DROP INDEX: 用于删除索引的关键字。
• index_name: 指定要删除的索引的名称。
• ON table_name: 指定要在哪个表上删除索引。

使用 ALTER TABLE 语句删除索引的语法如下：

• ALTER TABLE: 用于修改表结构的关键字。
• table_name: 指定要修改的表的名称。
• DROP INDEX: 用于删除索引的子句。
• index_name: 指定要删除的索引的名称。

以下实例假设我们有一个名为 employees 的表，并在 age 列上有一个名为 idx_age 的索引，现在我们要删除这个索引：

或使用 ALTER TABLE 语句：

这两个命令都会从 employees 表中删除名为 idx_age 的索引。

如果该索引不存在，执行命令时会产生错误。因此，在删除索引之前最好确认该索引是否存在，或者使用错误处理机制来处理可能的错误情况。

唯一索引 {#唯一索引}

在 MySQL 中，你可以使用 CREATE UNIQUE INDEX 语句来创建唯一索引。

唯一索引确保索引中的值是唯一的，不允许有重复值。

创建索引 {#创建索引-2}

• CREATE UNIQUE INDEX: 用于创建唯一索引的关键字组合。
• index_name: 指定要创建的唯一索引的名称。索引名称在表中必须是唯一的。
• table_name: 指定要在哪个表上创建唯一索引。
• (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

以下是一个创建唯一索引的实例： 假设我们有一个名为 employees的 表，包含 id 和 email 列，现在我们想在email列上创建一个唯一索引，以确保每个员工的电子邮件地址都是唯一的。

以上实例将在 employees 表的 email 列上创建一个名为 idx_email 的唯一索引。

修改表结构添加索引 {#修改表结构添加索引}

我们可以使用 ALTER TABLE 命令来创建唯一索引。

ALTER TABLE命令允许你修改已经存在的表结构，包括添加新的索引。

• ALTER TABLE: 用于修改表结构的关键字。
• table_name: 指定要修改的表的名称。
• ADD CONSTRAINT: 这是用于添加约束（包括唯一索引）的关键字。
• unique_constraint_name: 指定要创建的唯一索引的名称，约束名称在表中必须是唯一的。
• UNIQUE (column1, column2, ...): 指定要索引的表列名。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。

以下是一个使用 ALTER TABLE 命令创建唯一索引的实例：假设我们有一个名为 employees 的表，包含 id 和 email 列，现在我们想在 email 列上创建一个唯一索引，以确保每个员工的电子邮件地址都是唯一的。

以上实例将在 employees 表的 email 列上创建一个名为 idx_email 的唯一索引。

请注意，如果表中已经有重复的 email 值，那么添加唯一索引将会失败。在创建唯一索引之前，你可能需要确保表中的 email 列没有重复的值。

创建表的时候直接指定 {#创建表的时候直接指定-2}

我们也可以在创建表的同时，你可以在 CREATE TABLE 语句中使用 UNIQUE 关键字来创建唯一索引。

这将在表创建时同时定义唯一索引约束。

CREATE TABLE 语句中创建唯一索引的语法：

• CREATE TABLE: 用于创建新表的关键字。
• table_name: 指定要创建的表的名称。
• (column1 data_type, column2 data_type, ...): 定义表的列名和数据类型。你可以指定一个或多个列作为索引的组合。这些列的数据类型通常是数值、文本或日期。
• CONSTRAINT: 用于添加约束的关键字。
• index_name: 指定要创建的唯一索引的名称。约束名称在表中必须是唯一的。
• UNIQUE (column1, column2, ...): 指定要索引的表列名。
• ASC和DESC（可选）: 用于指定索引的排序顺序。默认情况下，索引以升序（ASC）排序。

以下是一个在创建表时创建唯一索引的实例：假设我们要创建一个名为 employees 的表，其中包含 id、name 和 email 列，我们希望 email 列的值是唯一的，因此我们要在创建表时定义唯一索引。

在这个例子中，email 列被定义为唯一索引，因为在它的后面加上了 UNIQUE 关键字。

请注意，使用 UNIQUE 关键字后，索引名称将自动生成，你也可以根据需要指定索引名称。

使用ALTER 命令添加和删除索引 {#使用ALTER-命令添加和删除索引}

有四种方式来添加数据表的索引：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):

  该语句添加一个主键，主键列中的值必须唯一，主键的列的列表，可以是一个或多个列，不能包含 NULL 值。 。

• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。

• **ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)😗*该语句指定了索引为 FULLTEXT ，用于全文索引。

以下实例为在表中添加索引。

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

使用 ALTER 命令添加和删除主键 {#使用-ALTER-命令添加和删除主键}

主键作用于列上（可以一个列或多个列联合主键），添加主键时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

你也可以使用 ALTER 命令删除主键：

删除主键时只需指定 PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息 {#显示索引信息}

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。

可以通过添加 \G 来格式化输出信息。

SHOW INDEX 语句：:

• SHOW INDEX: 用于显示索引信息的关键字。
• FROM table_name: 指定要查看索引信息的表的名称。
• \G: 格式化输出信息。

执行上述命令后，将会显示指定表中所有索引的详细信息，包括索引名称（Key_name）、索引列（Column_name）、是否是唯一索引（Non_unique）、排序方式（Collation）、索引的基数（Cardinality）等。

临时表 {#临时表}

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。

临时表只在当前连接可见，当关闭连接时，MySQL 会自动删除表并释放所有空间。

在 MySQL 中，临时表是一种在当前会话中存在的表，它在会话结束时会自动被销毁。

MySQL 临时表只在当前连接可见，如果你使用PHP脚本来创建 MySQL 临时表，那每当 PHP 脚本执行完成后，该临时表也会自动销毁。

如果你使用了其他 MySQL 客户端程序连接 MySQL 数据库服务器来创建临时表，那么只有在关闭客户端程序时才会销毁临时表，当然你也可以手动销毁。

创建临时表 {#创建临时表}

或者简写为：

插入数据到临时表 {#插入数据到临时表}

查询临时表 {#查询临时表}

修改临时表 {#修改临时表}

临时表的修改操作与普通表类似，可以使用 ALTER TABLE 命令。

删除临时表 {#删除临时表}

临时表在会话结束时会自动被销毁，但你也可以使用 DROP TABLE 明确删除它。

实例 {#实例}

复制表 {#复制表}

如果我们需要完全的复制 MySQL 的数据表，包括表的结构，索引，默认值等。

如果仅仅使用 CREATE TABLE ... SELECT 命令，是无法实现的。

本章节将为大家介绍如何完整的复制 MySQL 数据表，步骤如下：

• 使用 SHOW CREATE TABLE 命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。
• 复制以下命令显示的 SQL 语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。
• 如果你想复制表的内容，你就可以使用 INSERT INTO ... SELECT 语句来实现。

实例

尝试以下实例来复制表 runoob_tbl 。

步骤一：

获取数据表的完整结构。

步骤二：

修改 SQL 语句的数据表名，并执行 SQL 语句。

步骤三：

执行完第二步骤后，你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用 INSERT INTO... SELECT 语句来实现。

执行以上步骤后，会完整的复制表的内容，包括表结构及表数据。

元数据 {#元数据}

MySQL 元数据是关于数据库和其对象（如表、列、索引等）的信息。

元数据存储在系统表中，这些表位于 MySQL 数据库的 information_schema 数据库中，通过查询这些系统表，你可以获取关于数据库结构、对象和其他相关信息的详细信息。

你可能想知道MySQL以下三种信息：

• 查询结果信息： SELECT, UPDATE 或 DELETE语句影响的记录数。
• 数据库和数据表的信息： 包含了数据库及数据表的结构信息。
• MySQL 服务器信息： 包含了数据库服务器的当前状态，版本号等。

在 MySQL 的命令提示符中，我们可以很容易的获取以上服务器信息，但如果使用 Perl 或 PHP 等脚本语言，你就需要调用特定的接口函数来获取，接下来我们会详细介绍。

以下是一些常用的 MySQL 元数据查询：

查看所有数据库：

选择数据库：

查看数据库中的所有表：

查看表的结构：

查看表的索引：

查看表的创建语句：

查看表的行数：

查看列的信息：

以上SQL 语句中的 'your_database_name' 和 'your_table_name' 分别是你的数据库名和表名。

查看外键信息：

请替换上述 SQL 语句中的 'your_database_name' 和 'your_table_name' 为实际的数据库名和表名。

information_schema 数据库 {#information-schema-数据库}

information_schema 是 MySQL 数据库中的一个系统数据库，它包含有关数据库服务器的元数据信息，这些信息以表的形式存储在 information_schema 数据库中。

• SCHEMATA 表

存储有关数据库的信息，如数据库名、字符集、排序规则等。

• TABLES 表

包含有关数据库中所有表的信息，如表名、数据库名、引擎、行数等。

• COLUMNS 表

包含有关表中列的信息，如列名、数据类型、是否允许 NULL 等。

• STATISTICS 表

提供有关表索引的统计信息，如索引名、列名、唯一性等。

• KEY_COLUMN_USAGE 表

包含有关表中外键的信息，如外键名、列名、关联表等。

• REFERENTIAL_CONSTRAINTS 表

存储有关外键约束的信息，如约束名、关联表等。

这些表提供了丰富的元数据信息，可以用于查询数据库结构、表信息、列信息、索引信息等。

请注意，你需要将查询中的 'your_database_name' 和 'your_table_name' 替换为实际的数据库名和表名。

获取查询语句影响的记录数 {#获取查询语句影响的记录数}

PERL 实例

在 DBI 脚本中， 语句影响的记录数通过函数 do( ) 或 execute( )返回：

序列使用（AUTO_INCREMENT） {#序列使用（AUTO-INCREMENT）}

在 MySQL 中，序列是一种自增生成数字序列的对象，是一组整数 1、2、3、...，由于一张数据表只能有一个字段自增主键。

尽管 MySQL 本身并没有内建的序列类型，但可以使用 AUTO_INCREMENT 属性来模拟序列的行为，通常 AUTO_INCREMENT 属性用于指定表中某一列的自增性。

一个使用 AUTO_INCREMENT 创建表的例子：

以上例子中，id 列被定义为 INT AUTO_INCREMENT，这表示每次插入一行数据时，id 列的值会自动增加。主键约束保证了 id 列的唯一性。

当你插入一行数据时，可以不指定 id 列的值，数据库会自动为其分配一个唯一的、自增的值：

你也可以使用 LAST_INSERT_ID() 函数来获取刚刚插入的行的自增值：

如果你需要获取表的当前自增值，可以使用以下语句：

在结果集中，Auto_increment 列的值即为当前表的自增值。

请注意，使用 AUTO_INCREMENT 属性的列只能是整数类型（通常是 INT 或 BIGINT）。此外，如果你删除表中的某一行，其自增值不会被重新使用，而是会继续递增。如果你希望手动设置自增值，可以使用 SET 语句，但这不是一种常规的做法，因为可能引起唯一性冲突。

• 使用 AUTO_INCREMENT

MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义序列。

实例

以下实例中创建了数据表 insect， insect 表中 id 无需指定值可实现自动增长。

• 获取 AUTO_INCREMENT 值

在 MySQL 的客户端中你可以使用 SQL 中的 LAST_INSERT_ID() 函数来获取最后的插入表中的自增列的值。

在 PHP 或 PERL 脚本中也提供了相应的函数来获取最后的插入表中的自增列的值。

处理重复数据 {#处理重复数据}

有些 MySQL 数据表中可能存在重复的记录，有些情况我们允许重复数据的存在，但有时候我们也需要删除这些重复的数据。

本章节我们将为大家介绍如何防止数据表出现重复数据及如何删除数据表中的重复数据。

• 防止表中出现重复数据

你可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性。

让我们尝试一个实例：下表中无索引及主键，所以该表允许出现多条重复记录。

如果你想设置表中字段 first_name，last_name 数据不能重复，你可以设置双主键模式来设置数据的唯一性， 如果你设置了双主键，那么那个键的默认值不能为 NULL，可设置为 NOT NULL。如下所示：

如果我们设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。

INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE INTO 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

以下实例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：

INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO 如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。

另一种设置数据的唯一性方法是添加一个 UNIQUE 索引，如下所示：

• 统计重复数据

以下我们将统计表中 first_name 和 last_name的重复记录数：

以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下，查询重复的值，请执行以下操作：

• 确定哪一列包含的值可能会重复。
• 在列选择列表使用COUNT(*)列出的那些列。
• 在GROUP BY子句中列出的列。
• HAVING子句设置重复数大于1。

• 过滤重复数据

如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

你也可以使用 GROUP BY 来读取数据表中不重复的数据：

• 删除重复数据

如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：

当然你也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：

导出数据 {#导出数据}

mysqldump 命令可以用于备份和还原 MySQL 数据库。

如果你只想复制一张表，你可以使用 mysqldump 导出该表的结构和数据，然后再导入到新的数据库或新的表中。

以下是一个简单的实例。

备份表数据：

这将导出名为 old_table 的表的结构和数据到一个名为 old_table_dump.sql 的 SQL 文件。

你需要提供 MySQL 用户名和密码，并替换 username、dbname 和 old_table 为实际的值。

还原到新的数据库：

这将在新的数据库（new_dbname）中创建一个名为 old_table 的表，并将之前导出的结构和数据导入到新表中。

mysqldump 是 MySQL 提供的用于备份和导出数据库的命令行工具。

mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。

mysqldump 基本的用法：

参数说明：

• -u: 指定 MySQL 用户名。
• -p: 提示输入密码。
• -h: 指定 MySQL 主机名。
• database_name: 要导出的数据库名称。
• output_file.sql: 导出数据保存到的文件。

以下实例将数据表 runoob_tbl 导出到 /tmp 目录中：

• mysqldump 实例

1. 导出整个数据库

导出 mydatabase 数据库到 mydatabase_backup.sql 文件：

2. 导出特定表

如果你只想导出数据库中的某个表，可以使用以下命令：

或：

3. 导出数据库结构

如果只想导出数据库结构而不包括数据，可以使用 --no-data 选项：

4. 导出压缩文件

你可以将导出的数据进行压缩，以减小文件大小。例如，使用 gzip：

• 导出 SQL 格式的数据

导出 SQL 格式的数据到指定文件，如下所示：

以上命令创建的文件内容如下：

如果你需要导出整个数据库的数据，可以使用以下命令：

如果需要备份所有数据库，可以使用以下命令：

--all-databases 选项在 MySQL 3.23.12 及以后版本加入。

该方法可用于实现数据库的备份策略。

• 将数据表及数据库拷贝至其他主机

如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。

在源主机上执行以下命令，将数据备份至 dump.txt 文件中:

如果完整备份数据库，则无需使用特定的表名称。

如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：

以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

导入数据 {#导入数据}

本章节我们为大家介绍几种简单的 MySQL 导入数据命令。

mysql -u your_username -p -h your_host -P your_port -D your_database

1. mysql 命令导入

使用 mysql 命令导入语法格式为：

your_username、your_host、your_port、your_database 分别为你的 MySQL 用户名、主机、端口和数据库。

实例：

以上命令将将备份的整个数据库 runoob.sql 导入。

执行上述命令后，系统将要求输入 MySQL 用户的密码。输入密码并按Enter键。

这样，MySQL 将执行 SQL 文件中的语句，将数据导入到指定的数据库中。

请注意，如果 SQL 文件包含创建数据库的语句，确保在执行导入之前数据库已经存在。如果文件包含创建表的语句，确保表不存在或者是空的，以免导入数据时发生冲突。

2. source 命令导入

source 命令导入数据库需要先登录到数库终端：

使用 source 命令的好处是，你可以在 MySQL 命令行中直接执行，而无需退出 MySQL 并使用其他命令。

3. 使用 LOAD DATA 导入数据

MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。

如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

你能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。

两个命令的 FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。

如果用户指定一个 FIELDS 子句，它的子句 （TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。

如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：

4. 使用 mysqlimport 导入数据

mysqlimport 客户端提供了 LOAD DATA INFILEQL 语句的一个命令行接口。mysqlimport 的大多数选项直接对应 LOAD DATA INFILE 子句。

从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：

mysqlimport 命令可以指定选项来设置指定格式,命令语句格式如下：

mysqlimport 语句中使用 --columns 选项来设置列的顺序：

• mysqlimport的常用选项介绍

| 选项 | 功能 | |------------------------------|--------------------------------------------------------------------------------------------------| | -d or --delete | 新数据导入数据表中之前删除数据数据表中的所有信息 | | -f or --force | 不管是否遇到错误，mysqlimport将强制继续插入数据 | | -i or --ignore | mysqlimport跳过或者忽略那些有相同唯一 关键字的行， 导入文件中的数据将被忽略。 | | -l or -lock-tables | 数据被插入之前锁住表，这样就防止了， 你在更新数据库时，用户的查询和更新受到影响。 | | -r or -replace | 这个选项与－i选项的作用相反；此选项将替代 表中有相同唯一关键字的记录。 | | --fields-enclosed- by= char | 指定文本文件中数据的记录时以什么括起的， 很多情况下 数据以双引号括起。 默认的情况下数据是没有被字符括起的。 | | --fields-terminated- by=char | 指定各个数据的值之间的分隔符，在句号分隔的文件中， 分隔符是句号。您可以用此选项指定数据之间的分隔符。 默认的分隔符是跳格符（Tab） | | --lines-terminated- by=str | 此选项指定文本文件中行与行之间数据的分隔字符串 或者字符。 默认的情况下mysqlimport以newline为行分隔符。 您可以选择用一个字符串来替代一个单个的字符： 一个新行或者一个回车。 |

mysqlimport 命令常用的选项还有 -v 显示版本（version）， -p 提示输入密码（password）等。

函数 {#函数}

MySQL 有很多内置的函数，以下列出了这些函数的说明。

MySQL 字符串函数 {#MySQL-字符串函数}

| 函数 | 描述 | 实例 | |---------------------------------------|--------------------------------------------------------------------------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------| | ASCII(s) | 返回字符串 s 的第一个字符的 ASCII 码。 | 返回 CustomerName 字段第一个字母的 ASCII 码：SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers; | | CHAR_LENGTH(s) | 返回字符串 s 的字符数 | 返回字符串 RUNOOB 的字符数SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString; | | CHARACTER_LENGTH(s) | 返回字符串 s 的字符数，等同于 CHAR_LENGTH(s) | 返回字符串 RUNOOB 的字符数SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString; | | CONCAT(s1,s2...sn) | 字符串 s1,s2 等多个字符串合并为一个字符串 | 合并多个字符串SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString; | | CONCAT_WS(x, s1,s2...sn) | 同 CONCAT(s1,s2,...) 函数，但是每个字符串之间要加上 x，x 可以是分隔符 | 合并多个字符串，并添加分隔符：SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString; | | FIELD(s,s1,s2...) | 返回第一个字符串 s 在字符串列表(s1,s2...)中的位置 | 返回字符串 c 在列表值中的位置：SELECT FIELD("c", "a", "b", "c", "d", "e"); | | FIND_IN_SET(s1,s2) | 返回在字符串s2中与s1匹配的字符串的位置 | 返回字符串 c 在指定字符串中的位置：SELECT FIND_IN_SET("c", "a,b,c,d,e"); | | FORMAT(x,n) | 函数可以将数字 x 进行格式化 "#,###.##", 将 x 保留到小数点后 n 位，最后一位四舍五入。 | 格式化数字 "#,###.##" 形式：SELECT FORMAT(250500.5634, 2); -- 输出 250,500.56 | | INSERT(s1,x,len,s2) | 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串 | 从字符串第一个位置开始的 6 个字符替换为 runoob：SELECT INSERT("google.com", 1, 6, "runoob"); -- 输出：runoob.com | | LOCATE(s1,s) | 从字符串 s 中获取 s1 的开始位置 | 获取 b 在字符串 abc 中的位置：SELECT LOCATE('st','myteststring'); -- 5返回字符串 abc 中 b 的位置：SELECT LOCATE('b', 'abc') -- 2 | | LCASE(s) | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写：SELECT LCASE('RUNOOB') -- runoob | | LEFT(s,n) | 返回字符串 s 的前 n 个字符 | 返回字符串 runoob 中的前两个字符：SELECT LEFT('runoob',2) -- ru | | LOWER(s) | 将字符串 s 的所有字母变成小写字母 | 字符串 RUNOOB 转换为小写：SELECT LOWER('RUNOOB') -- runoob | | LPAD(s1,len,s2) | 在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len | 将字符串 xx 填充到 abc 字符串的开始处：SELECT LPAD('abc',5,'xx') -- xxabc | | LTRIM(s) | 去掉字符串 s 开始处的空格 | 去掉字符串 RUNOOB开始处的空格：SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;-- RUNOOB | | MID(s,n,len) | 从字符串 s 的 n 位置截取长度为 len 的子字符串，同 SUBSTRING(s,n,len) | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO | | POSITION(s1 IN s) | 从字符串 s 中获取 s1 的开始位置 | 返回字符串 abc 中 b 的位置：SELECT POSITION('b' in 'abc') -- 2 | | REPEAT(s,n) | 将字符串 s 重复 n 次 | 将字符串 runoob 重复三次：SELECT REPEAT('runoob',3) -- runoobrunoobrunoob | | REPLACE(s,s1,s2) | 将字符串 s2 替代字符串 s 中的字符串 s1 | 将字符串 abc 中的字符 a 替换为字符 x：SELECT REPLACE('abc','a','x') --xbc | | REVERSE(s) | 将字符串s的顺序反过来 | 将字符串 abc 的顺序反过来：SELECT REVERSE('abc') -- cba | | RIGHT(s,n) | 返回字符串 s 的后 n 个字符 | 返回字符串 runoob 的后两个字符：SELECT RIGHT('runoob',2) -- ob | | RPAD(s1,len,s2) | 在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len | 将字符串 xx 填充到 abc 字符串的结尾处：SELECT RPAD('abc',5,'xx') -- abcxx | | RTRIM(s) | 去掉字符串 s 结尾处的空格 | 去掉字符串 RUNOOB 的末尾空格：SELECT RTRIM("RUNOOB ") AS RightTrimmedString; -- RUNOOB | | SPACE(n) | 返回 n 个空格 | 返回 10 个空格：SELECT SPACE(10); | | STRCMP(s1,s2) | 比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1 | 比较字符串：SELECT STRCMP("runoob", "runoob"); -- 0 | | SUBSTR(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串 | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO | | SUBSTRING(s, start, length) | 从字符串 s 的 start 位置截取长度为 length 的子字符串，等同于 SUBSTR(s, start, length) | 从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO | | SUBSTRING_INDEX(s, delimiter, number) | 返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。 | SELECT SUBSTRING_INDEX('a*b','*',1) -- a SELECT SUBSTRING_INDEX('a*b','*',-1) -- b SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) -- c | | TRIM(s) | 去掉字符串 s 开始和结尾处的空格 | 去掉字符串 RUNOOB 的首尾空格：SELECT TRIM(' RUNOOB ') AS TrimmedString; | | UCASE(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写：SELECT UCASE("runoob"); -- RUNOOB | | UPPER(s) | 将字符串转换为大写 | 将字符串 runoob 转换为大写：SELECT UPPER("runoob"); -- RUNOOB |

MySQL 数字函数 {#MySQL-数字函数}

| 函数名 | 描述 | 实例 | |------------------------------------|-----------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------| | ABS(x) | 返回 x 的绝对值 | 返回 -1 的绝对值：SELECT ABS(-1) -- 返回1 | | ACOS(x) | 求 x 的反余弦值（单位为弧度），x 为一个数值 | SELECT ACOS(0.25); | | ASIN(x) | 求反正弦值（单位为弧度），x 为一个数值 | SELECT ASIN(0.25); | | ATAN(x) | 求反正切值（单位为弧度），x 为一个数值 | SELECT ATAN(2.5); | | ATAN2(n, m) | 求反正切值（单位为弧度） | SELECT ATAN2(-0.8, 2); | | AVG(expression) | 返回一个表达式的平均值，expression 是一个字段 | 返回 Products 表中Price 字段的平均值：SELECT AVG(Price) AS AveragePrice FROM Products; | | CEIL(x) | 返回大于或等于 x 的最小整数 | SELECT CEIL(1.5) -- 返回2 | | CEILING(x) | 返回大于或等于 x 的最小整数 | SELECT CEILING(1.5); -- 返回2 | | COS(x) | 求余弦值(参数是弧度) | SELECT COS(2); | | COT(x) | 求余切值(参数是弧度) | SELECT COT(6); | | COUNT(expression) | 返回查询的记录总数，expression 参数是一个字段或者 * 号 | 返回 Products 表中 products 字段总共有多少条记录：SELECT COUNT(ProductID) AS NumberOfProducts FROM Products; | | DEGREES(x) | 将弧度转换为角度 | SELECT DEGREES(3.1415926535898) -- 180 | | n DIV m | 整除，n 为被除数，m 为除数 | 计算 10 除于 5：SELECT 10 DIV 5; -- 2 | | EXP(x) | 返回 e 的 x 次方 | 计算 e 的三次方：SELECT EXP(3) -- 20.085536923188 | | FLOOR(x) | 返回小于或等于 x 的最大整数 | 小于或等于 1.5 的整数：SELECT FLOOR(1.5) -- 返回1 | | GREATEST(expr1, expr2, expr3, ...) | 返回列表中的最大值 | 返回以下数字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); -- 34返回以下字符串列表中的最大值：SELECT GREATEST("Google", "Runoob", "Apple"); -- Runoob | | LEAST(expr1, expr2, expr3, ...) | 返回列表中的最小值 | 返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); -- 3返回以下字符串列表中的最小值：SELECT LEAST("Google", "Runoob", "Apple"); -- Apple | | LN | 返回数字的自然对数，以 e 为底。 | 返回 2 的自然对数：SELECT LN(2); -- 0.6931471805599453 | | LOG(x) 或 LOG(base, x) | 返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数。 | SELECT LOG(20.085536923188) -- 3 SELECT LOG(2, 4); -- 2 | | LOG10(x) | 返回以 10 为底的对数 | SELECT LOG10(100) -- 2 | | LOG2(x) | 返回以 2 为底的对数 | 返回以 2 为底 6 的对数：SELECT LOG2(6); -- 2.584962500721156 | | MAX(expression) | 返回字段 expression 中的最大值 | 返回数据表 Products 中字段 Price 的最大值：SELECT MAX(Price) AS LargestPrice FROM Products; | | MIN(expression) | 返回字段 expression 中的最小值 | 返回数据表 Products 中字段 Price 的最小值：SELECT MIN(Price) AS MinPrice FROM Products; | | MOD(x,y) | 返回 x 除以 y 以后的余数 | 5 除于 2 的余数：SELECT MOD(5,2) -- 1 | | PI() | 返回圆周率(3.141593） | SELECT PI() --3.141593 | | POW(x,y) | 返回 x 的 y 次方 | 2 的 3 次方：SELECT POW(2,3) -- 8 | | POWER(x,y) | 返回 x 的 y 次方 | 2 的 3 次方：SELECT POWER(2,3) -- 8 | | RADIANS(x) | 将角度转换为弧度 | 180 度转换为弧度：SELECT RADIANS(180) -- 3.1415926535898 | | RAND() | 返回 0 到 1 的随机数 | SELECT RAND() --0.93099315644334 | | ROUND(x [,y]) | 返回离 x 最近的整数，可选参数 y 表示要四舍五入的小数位数，如果省略，则返回整数。 | SELECT ROUND(1.23456) --1 SELECT ROUND(345.156, 2) -- 345.16 | | SIGN(x) | 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1 | SELECT SIGN(-10) -- (-1) | | SIN(x) | 求正弦值(参数是弧度) | SELECT SIN(RADIANS(30)) -- 0.5 | | SQRT(x) | 返回x的平方根 | 25 的平方根：SELECT SQRT(25) -- 5 | | SUM(expression) | 返回指定字段的总和 | 计算 OrderDetails 表中字段 Quantity 的总和：SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails; | | TAN(x) | 求正切值(参数是弧度) | SELECT TAN(1.75); -- -5.52037992250933 | | TRUNCATE(x,y) | 返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入） | SELECT TRUNCATE(1.23456,3) -- 1.234 |

MySQL 日期函数 {#MySQL-日期函数}

| 函数名 | 描述 | 实例 | |---------------------------------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | ADDDATE(d,n) | 计算起始日期 d 加上 n 天的日期 | SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY); ->2017-06-25 | | ADDTIME(t,n) | n 是一个时间表达式，时间 t 加上时间表达式 n | 加 5 秒：SELECT ADDTIME('2011-11-11 11:11:11', 5); ->2011-11-11 11:11:16 (秒)添加 2 小时, 10 分钟, 5 秒:SELECT ADDTIME("2020-06-15 09:34:21", "2:10:5"); -> 2020-06-15 11:44:26 | | CURDATE() | 返回当前日期 | SELECT CURDATE(); -> 2018-09-19 | | CURRENT_DATE() | 返回当前日期 | SELECT CURRENT_DATE(); -> 2018-09-19 | | CURRENT_TIME | 返回当前时间 | SELECT CURRENT_TIME(); -> 19:59:02 | | CURRENT_TIMESTAMP() | 返回当前日期和时间 | SELECT CURRENT_TIMESTAMP() -> 2018-09-19 20:57:43 | | CURTIME() | 返回当前时间 | SELECT CURTIME(); -> 19:59:02 | | DATE() | 从日期或日期时间表达式中提取日期值 | SELECT DATE("2017-06-15"); -> 2017-06-15 | | DATEDIFF(d1,d2) | 计算日期 d1->d2 之间相隔的天数 | SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32 | | DATE_ADD(d，INTERVAL expr type) | 计算起始日期 d 加上一个时间段后的日期，type 值可以是：MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH | SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); -> 2017-06-25 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE); -> 2017-06-15 09:49:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR); ->2017-06-15 06:34:21 SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 MONTH); ->2017-03-15 09:34:21 | | DATE_FORMAT(d,f) | 按表达式 f的要求显示日期 d | SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r') -> 2011-11-11 11:11:11 AM | | DATE_SUB(date,INTERVAL expr type) | 函数从日期减去指定的时间间隔。 | Orders 表中 OrderDate 字段减去 2 天：SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate FROM Orders | | DAY(d) | 返回日期值 d 的日期部分 | SELECT DAY("2017-06-15"); -> 15 | | DAYNAME(d) | 返回日期 d 是星期几，如 Monday,Tuesday | SELECT DAYNAME('2011-11-11 11:11:11') ->Friday | | DAYOFMONTH(d) | 计算日期 d 是本月的第几天 | SELECT DAYOFMONTH('2011-11-11 11:11:11') ->11 | | DAYOFWEEK(d) | 日期 d 今天是星期几，1 星期日，2 星期一，以此类推 | SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6 | | DAYOFYEAR(d) | 计算日期 d 是本年的第几天 | SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315 | | EXTRACT(type FROM d) | 从日期 d 中获取指定的值，type 指定返回的值。 type可取值为： MICROSECONDSECONDMINUTEHOURDAYWEEKMONTHQUARTERYEARSECOND_MICROSECONDMINUTE_MICROSECONDMINUTE_SECONDHOUR_MICROSECONDHOUR_SECONDHOUR_MINUTEDAY_MICROSECONDDAY_SECONDDAY_MINUTEDAY_HOURYEAR_MONTH | SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') -> 11 | | FROM_DAYS(n) | 计算从 0000 年 1 月 1 日开始 n 天后的日期 | SELECT FROM_DAYS(1111) -> 0003-01-16 | | HOUR(t) | 返回 t 中的小时值 | SELECT HOUR('1:2:3') -> 1 | | LAST_DAY(d) | 返回给给定日期的那一月份的最后一天 | SELECT LAST_DAY("2017-06-20"); -> 2017-06-30 | | LOCALTIME() | 返回当前日期和时间 | SELECT LOCALTIME() -> 2018-09-19 20:57:43 | | LOCALTIMESTAMP() | 返回当前日期和时间 | SELECT LOCALTIMESTAMP() -> 2018-09-19 20:57:43 | | MAKEDATE(year, day-of-year) | 基于给定参数年份 year 和所在年中的天数序号 day-of-year 返回一个日期 | SELECT MAKEDATE(2017, 3); -> 2017-01-03 | | MAKETIME(hour, minute, second) | 组合时间，参数分别为小时、分钟、秒 | SELECT MAKETIME(11, 35, 4); -> 11:35:04 | | MICROSECOND(date) | 返回日期参数所对应的微秒数 | SELECT MICROSECOND("2017-06-20 09:34:00.000023"); -> 23 | | MINUTE(t) | 返回 t 中的分钟值 | SELECT MINUTE('1:2:3') -> 2 | | MONTHNAME(d) | 返回日期当中的月份名称，如 November | SELECT MONTHNAME('2011-11-11 11:11:11') -> November | | MONTH(d) | 返回日期d中的月份值，1 到 12 | SELECT MONTH('2011-11-11 11:11:11') ->11 | | NOW() | 返回当前日期和时间 | SELECT NOW() -> 2018-09-19 20:57:43 | | PERIOD_ADD(period, number) | 为 年-月 组合日期添加一个时段 | SELECT PERIOD_ADD(201703, 5); -> 201708 | | PERIOD_DIFF(period1, period2) | 返回两个时段之间的月份差值 | SELECT PERIOD_DIFF(201710, 201703); -> 7 | | QUARTER(d) | 返回日期d是第几季节，返回 1 到 4 | SELECT QUARTER('2011-11-11 11:11:11') -> 4 | | SECOND(t) | 返回 t 中的秒钟值 | SELECT SECOND('1:2:3') -> 3 | | SEC_TO_TIME(s) | 将以秒为单位的时间 s 转换为时分秒的格式 | SELECT SEC_TO_TIME(4320) -> 01:12:00 | | STR_TO_DATE(string, format_mask) | 将字符串转变为日期 | SELECT STR_TO_DATE("August 10 2017", "%M %d %Y"); -> 2017-08-10 | | SUBDATE(d,n) | 日期 d 减去 n 天后的日期 | SELECT SUBDATE('2011-11-11 11:11:11', 1) ->2011-11-10 11:11:11 (默认是天) | | SUBTIME(t,n) | 时间 t 减去 n 秒的时间 | SELECT SUBTIME('2011-11-11 11:11:11', 5) ->2011-11-11 11:11:06 (秒) | | SYSDATE() | 返回当前日期和时间 | SELECT SYSDATE() -> 2018-09-19 20:57:43 | | TIME(expression) | 提取传入表达式的时间部分 | SELECT TIME("19:30:10"); -> 19:30:10 | | TIME_FORMAT(t,f) | 按表达式 f 的要求显示时间 t | SELECT TIME_FORMAT('11:11:11','%r') 11:11:11 AM | | TIME_TO_SEC(t) | 将时间 t 转换为秒 | SELECT TIME_TO_SEC('1:12:00') -> 4320 | | TIMEDIFF(time1, time2) | 计算时间差值 | mysql> SELECT TIMEDIFF("13:10:11", "13:10:10"); -> 00:00:01 mysql> SELECT TIMEDIFF('2000:01:01 00:00:00', -> '2000:01:01 00:00:00.000001'); -> '-00:00:00.000001' mysql> SELECT TIMEDIFF('2008-12-31 23:59:59.000001', -> '2008-12-30 01:01:01.000002'); -> '46:58:57.999999' | | TIMESTAMP(expression, interval) | 单个参数时，函数返回日期或日期时间表达式；有2个参数时，将参数加和 | mysql> SELECT TIMESTAMP("2017-07-23", "13:10:11"); -> 2017-07-23 13:10:11 mysql> SELECT TIMESTAMP('2003-12-31'); -> '2003-12-31 00:00:00' mysql> SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00'); -> '2004-01-01 00:00:00' | | TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) | 计算时间差，返回 datetime_expr2 − datetime_expr1 的时间差 | mysql> SELECT TIMESTAMPDIFF(DAY,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少天 -> 89 mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01'); // 计算两个时间相隔多少月 -> 3 mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01'); // 计算两个时间相隔多少年 -> -1 mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55'); // 计算两个时间相隔多少分钟 -> 128885 | | TO_DAYS(d) | 计算日期 d 距离 0000 年 1 月 1 日的天数 | SELECT TO_DAYS('0001-01-01 01:01:01') -> 366 | | WEEK(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 | SELECT WEEK('2011-11-11 11:11:11') -> 45 | | WEEKDAY(d) | 日期 d 是星期几，0 表示星期一，1 表示星期二 | SELECT WEEKDAY("2017-06-15"); -> 3 | | WEEKOFYEAR(d) | 计算日期 d 是本年的第几个星期，范围是 0 到 53 | SELECT WEEKOFYEAR('2011-11-11 11:11:11') -> 45 | | YEAR(d) | 返回年份 | SELECT YEAR("2017-06-15"); -> 2017 | | YEARWEEK(date, mode) | 返回年份及第几周（0到53），mode 中 0 表示周天，1表示周一，以此类推 | SELECT YEARWEEK("2017-06-15"); -> 201724 |

MySQL 高级函数 {#MySQL-高级函数}

| 函数名 | 描述 | 实例 | |------------------------------------------------------------------------------------------------------------------------------|------------------------------------------------------------------------------------------------------------------------|----------------------------------------------------------------------------------------------| | BIN(x) | 返回 x 的二进制编码，x 为十进制数 | 15 的 2 进制编码:SELECT BIN(15); -- 1111 | | BINARY(s) | 将字符串 s 转换为二进制字符串 | SELECT BINARY "RUNOOB"; -> RUNOOB | | CASE expression WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... WHEN conditionN THEN resultN ELSE result END | CASE 表示函数开始，END 表示函数结束。如果 condition1 成立，则返回 result1, 如果 condition2 成立，则返回 result2，当全部不成立则返回 result，而当有一个成立之后，后面的就不执行了。 | SELECT CASE 　WHEN 1 > 0 　THEN '1 > 0' 　WHEN 2 > 0 　THEN '2 > 0' 　ELSE '3 > 0' 　END ->1 > 0 | | CAST(x AS type) | 转换数据类型 | 字符串日期转换为日期：SELECT CAST("2017-08-29" AS DATE); -> 2017-08-29 | | COALESCE(expr1, expr2, ..., expr_n) | 返回参数中的第一个非空表达式（从左向右） | SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com'); -> runoob.com | | CONNECTION_ID() | 返回唯一的连接 ID | SELECT CONNECTION_ID(); -> 4292835 | | CONV(x,f1,f2) | 返回 f1 进制数变成 f2 进制数 | SELECT CONV(15, 10, 2); -> 1111 | | CONVERT(s USING cs) | 函数将字符串 s 的字符集变成 cs | SELECT CHARSET('ABC') ->utf-8 SELECT CHARSET(CONVERT('ABC' USING gbk)) ->gbk | | CURRENT_USER() | 返回当前用户 | SELECT CURRENT_USER(); -> guest@% | | DATABASE() | 返回当前数据库名 | SELECT DATABASE(); -> runoob | | IF(expr,v1,v2) | 如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。 | SELECT IF(1 > 0,'正确','错误') ->正确 | | IFNULL(v1,v2) | 如果 v1 的值不为 NULL，则返回 v1，否则返回 v2。 | SELECT IFNULL(null,'Hello Word') ->Hello Word | | ISNULL(expression) | 判断表达式是否为 NULL | SELECT ISNULL(NULL); ->1 | | LAST_INSERT_ID() | 返回最近生成的 AUTO_INCREMENT 值 | SELECT LAST_INSERT_ID(); ->6 | | NULLIF(expr1, expr2) | 比较两个字符串，如果字符串 expr1 与 expr2 相等 返回 NULL，否则返回 expr1 | SELECT NULLIF(25, 25); -> | | SESSION_USER() | 返回当前用户 | SELECT SESSION_USER(); -> guest@% | | SYSTEM_USER() | 返回当前用户 | SELECT SYSTEM_USER(); -> guest@% | | USER() | 返回当前用户 | SELECT USER(); -> guest@% | | VERSION() | 返回数据库的版本号 | SELECT VERSION() -> 5.6.34 |

以下是 MySQL 8.0 版本新增的一些常用函数：

| 函数 | 描述 | 实例 | |-----------------|-----------------------|------------------------------------------------------------------------------| | JSON_OBJECT() | 将键值对转换为 JSON 对象 | SELECT JSON_OBJECT('key1', 'value1', 'key2', 'value2') | | JSON_ARRAY() | 将值转换为 JSON 数组 | SELECT JSON_ARRAY(1, 2, 'three') | | JSON_EXTRACT() | 从 JSON 字符串中提取指定的值 | SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') | | JSON_CONTAINS() | 检查一个 JSON 字符串是否包含指定的值 | SELECT JSON_CONTAINS('{"name": "John", "age": 30}', 'John', '$.name') | | ROW_NUMBER() | 为查询结果中的每一行分配一个唯一的数字 | SELECT ROW_NUMBER() OVER(ORDER BY id) AS row_number, name FROM users | | RANK() | 为查询结果中的每一行分配一个排名 | SELECT RANK() OVER(ORDER BY score DESC) AS rank, name, score FROM students |

运算符 {#运算符}

本章节我们主要介绍 MySQL 的运算符及运算符的优先级。 MySQL 主要有以下几种运算符：

• 算术运算符
• 比较运算符
• 逻辑运算符
• 位运算符

算术运算符 {#算术运算符}

MySQL 支持的算术运算符包括:

| 运算符 | 作用 | |---------|----| | + | 加法 | | - | 减法 | | * | 乘法 | | / 或 DIV | 除法 | | % 或 MOD | 取余 |

在除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。

1、加

2、减

3、乘

4、除

5、商

6、取余

比较运算符 {#比较运算符}

SELECT 语句中的条件语句经常要使用比较运算符。通过这些比较运算符，可以判断表中的哪些记录是符合条件的。比较结果为真，则返回 1，为假则返回 0，比较结果不确定则返回 NULL。

| 符号 | 描述 | 备注 | |----------------|-----------------|------------------------------------------| | = | 等于 | | | <>, != | 不等于 | | | > | 大于 | | | < | 小于 | | | <= | 小于等于 | | | >= | 大于等于 | | | BETWEEN | 在两值之间 | >=min&&<=max | | NOT BETWEEN | 不在两值之间 | | | IN | 在集合中 | | | NOT IN | 不在集合中 | | | <=> | 严格比较两个NULL值是否相等 | 两个操作码均为NULL时，其所得值为1；而当一个操作码为NULL时，其所得值为0 | | LIKE | 模糊匹配 | | | REGEXP 或 RLIKE | 正则式匹配 | | | IS NULL | 为空 | | | IS NOT NULL | 不为空 | |

1、等于

2、不等于

3、安全等于

与 = 的区别在于当两个操作码均为 NULL 时，其所得值为 1 而不为 NULL，而当一个操作码为 NULL 时，其所得值为 0而不为 NULL。

4、小于

5、小于等于

6、大于

7、大于等于

8、BETWEEN

9、IN

10、NOT IN

11、IS NULL

12、IS NOT NULL

13、LIKE

14、REGEXP

逻辑运算符 {#逻辑运算符}

逻辑运算符用来判断表达式的真假。如果表达式是真，结果返回 1。如果表达式是假，结果返回 0。

| 运算符号 | 作用 | |---------|------| | NOT 或 ! | 逻辑非 | | AND | 逻辑与 | | OR | 逻辑或 | | XOR | 逻辑异或 |

1、与

2、或

3、非

4、异或

位运算符 {#位运算符}

位运算符是在二进制数上进行计算的运算符。位运算会先将操作数变成二进制数，进行位运算。然后再将计算结果从二进制数变回十进制数。

| 运算符号 | 作用 | |------|------| | & | 按位与 | | | | 按位或 | | ^ | 按位异或 | | ! | 取反 | | << | 左移 | | >> | 右移 |

1、按位与

2、按位或

3、按位异或

4、按位取反

5、按位右移

6、按位左移

运算符优先级 {#运算符优先级}

最低优先级为： :=。

命令大全 {#命令大全}

基础命令 {#基础命令}

| 操作 | 命令 | |---------------|--------------------------------------------------------| | 连接到 MySQL 数据库 | mysql -u 用户名 -p | | 查看所有数据库 | SHOW DATABASES; | | 选择一个数据库 | USE 数据库名; | | 查看所有表 | SHOW TABLES; | | 查看表结构 | DESCRIBE 表名; 或 SHOW COLUMNS FROM 表名; | | 创建一个新数据库 | CREATE DATABASE 数据库名; | | 删除一个数据库 | DROP DATABASE 数据库名; | | 创建一个新表 | CREATE TABLE 表名 (列名1 数据类型 [约束], 列名2 数据类型 [约束], ...); | | 删除一个表 | DROP TABLE 表名; | | 插入数据 | INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...); | | 查询数据 | SELECT 列1, 列2, ... FROM 表名 WHERE 条件; | | 更新数据 | UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... WHERE 条件; | | 删除数据 | DELETE FROM 表名 WHERE 条件; | | 创建用户 | CREATE USER '用户名'@'主机' IDENTIFIED BY '密码'; | | 授权用户 | GRANT 权限 ON 数据库名.* TO '用户名'@'主机'; | | 刷新权限 | FLUSH PRIVILEGES; | | 查看当前用户 | SELECT USER(); | | 退出 MySQL | EXIT; |

数据库相关命令 {#数据库相关命令}

下面是与 MySQL 数据库操作相关的命令，包括创建、删除和修改数据库等操作：

| 操作 | 命令 | |--------------------|----------------------------------------------------------------------------------------------------| | 创建数据库 | CREATE DATABASE 数据库名; | | 删除数据库 | DROP DATABASE 数据库名; | | 修改数据库编码格式和排序规则 | ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 编码格式 DEFAULT COLLATE 排序规则; | | 查看所有数据库 | SHOW DATABASES; | | 查看数据库详细信息 | SHOW CREATE DATABASE 数据库名; | | 选择数据库 | USE 数据库名; | | 查看数据库的状态信息 | SHOW STATUS; | | 查看数据库的错误信息 | SHOW ERRORS; | | 查看数据库的警告信息 | SHOW WARNINGS; | | 查看数据库的表 | SHOW TABLES; | | 查看表的结构 | DESC 表名; DESCRIBE 表名; SHOW COLUMNS FROM 表名; EXPLAIN 表名; | | 创建表 | CREATE TABLE 表名 (列名1 数据类型 [约束], 列名2 数据类型 [约束], ...); | | 删除表 | DROP TABLE 表名; | | 修改表结构 | ALTER TABLE 表名 ADD 列名 数据类型 [约束]; ALTER TABLE 表名 DROP 列名; ALTER TABLE 表名 MODIFY 列名 数据类型 [约束]; | | 查看表的创建 SQL | SHOW CREATE TABLE 表名; |

数据表相关命令 {#数据表相关命令}

以下是与 MySQL 数据表相关的常用命令，包括创建、修改、删除表以及查看表的结构和数据等操作：

| 操作 | 命令 | |----------------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 创建表 | CREATE TABLE 表名 (列名1 数据类型 [约束], 列名2 数据类型 [约束], ...); | | 删除表 | DROP TABLE 表名; | | 修改表结构 | 添加列: ALTER TABLE 表名 ADD 列名 数据类型 [约束]; 删除列: ALTER TABLE 表名 DROP 列名; 修改列: ALTER TABLE 表名 MODIFY 列名 数据类型 [约束]; 重命名列: ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [约束]; | | 查看表结构 | DESC 表名; DESCRIBE 表名; SHOW COLUMNS FROM 表名; EXPLAIN 表名; | | 查看表的创建 SQL | SHOW CREATE TABLE 表名; | | 查看表中的所有数据 | SELECT * FROM 表名; | | 插入数据 | INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...); | | 更新数据 | UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... WHERE 条件; | | 删除数据 | DELETE FROM 表名 WHERE 条件; | | 查看表的索引 | SHOW INDEX FROM 表名; | | 创建索引 | CREATE INDEX 索引名 ON 表名 (列名); | | 删除索引 | DROP INDEX 索引名 ON 表名; | | 查看表的约束 | SHOW CREATE TABLE 表名; (约束信息会包含在创建表的 SQL 中) | | 查看表的统计信息 | SHOW TABLE STATUS LIKE '表名'; |

MySQL 事务相关命令 {#MySQL-事务相关命令}

以下是与 MySQL 事务相关的常用命令：

| 操作 | 命令 | |----------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| | 开始事务 | START TRANSACTION; 或 BEGIN; | | 提交事务 | COMMIT; | | 回滚事务 | ROLLBACK; | | 查看当前事务的状态 | SHOW ENGINE INNODB STATUS; (可查看 InnoDB 存储引擎的事务状态) | | 锁定表以进行事务操作 | LOCK TABLES 表名 WRITE; 或 LOCK TABLES 表名 READ; | | 释放锁定的表 | UNLOCK TABLES; | | 设置事务的隔离级别 | SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |

参考资料 {#参考资料}

• https://www.runoob.com/mysql/mysql-tutorial.html

文章链接：
https://www.zywvvd.com/notes/coding/dataset/mysql/mysql-tutorial/