[TOC]
前言
本人一直认为『数据』是应用程序的源泉，我们的日常生活都会产生大量的数据，当我们对这些数据进行收集、分析、整合时，就可以充分挖掘出蕴藏在这些数据背后的大量价值，同时这些数据又会反哺给程序，创造出更多的工作岗位，使更多的人使用我们的程序。
比如，数据的收集、过滤通常交由后台服务器进行，因此后端工程师应运而生；
比如，数据需要展示给到用户，方便用户查看，因此前端/移动工程师应运而生；
比如，随着数据的持续收集，数据量会不断增加，这时就需要进行大数据处理，大数据处理工程师应运而生；
比如，当数据量达到一定程度时，就可以从这些海量数据中找出一定的规律，使之能预测匹配某类事务，人工智能也应运而生了;
以上还只是站在程序员的角度上看待数据，而在资本市场上，数据更是被誉为『21 世纪的石油』。未来的世界是数据的世界，谁能够掌握更多数据，并挖掘出数据背后的价值，谁就是市场的赢家。
既然数据这么重要，那么无论我们对数据做出了怎样的处理，最终都需要将数据存储起来，如何更快更好的存储数据，这就是大多数工程师需要关注的问题了。
数据库简介
应用程序如果需要保存用户的数据，一种方法是直接将数据以一定的格式存储到文件中，比如存储到 CSV 文件：
id,name,age
1,张三,20
2,李四,30
3,王五,40
4,赵六,50
CSV 格式文件可以使用 Excel 打开，自动将数据转换为表格格式，方便用户查看。
但是，对数据的操作不仅仅在于查看而已，还有增加、删除、改动...随着数据量的不断增大，会愈加发现存储到文件中的数据管理愈发复杂，这个时候，就需要使用专业的数据管理软件来帮助我们维护这些数据。
数据库软件会提供接口供应用程序操作数据，应用程序无需关心数据是如何存储到文件中的，无需关心如何增、删、改、查数据的具体细节，只需使用相应接口就行。
根据数据特点的不同，可以将数据分为『结构化数据』和『非结构化数据』：
-
结构化数据：也称作行数据，是指由『二维表结构』来逻辑表达和实现的数据，严格地遵循数据格式与长度规范。
-
非结构化数据：指的是数据结构不规则或不完整，没有任何预定义的数据模型，不方便用二维逻辑表来表现的数据。
结构化数据的特点是以行为单位，一行数据表示一个实体信息，每一行的数据属性都是相同的，因此结构化数据主要通过关系型数据库进行存储和管理。
非结构化数据的特点是数据项没有特定统一格式，数据格式常以『键值对』形式进行存储，典型的非结构化数据有办公文档、文本、图片、视频、音频等。通常将存储非结构化数据的数据库称为『非关系型数据库』。
注：其实还有一种介于结构化与非结构化之间的数据，将其称之为『半结构化数据』，它不符合二维表结构化数据模型，但是它本身内容具备自描述结构，也可以像结构化数据一样将每行数据表示为一个实体信息。常见的半结构化数据有 XML 和 JSON，半结构化数据通常也采用非关系型数据库进行存储，其将数据以文档（即 XML 或 JSON 格式）的形式进行存储，因此也被称为『文档型 NoSQL』。
简而言之，依据存储数据结构的不同，可以将数据库分为『关系型数据库』和『非关系型数据库』，其中：
-
关系型数据库：是指采用了关系模型（即二维表格模型）来组织数据的数据库，其以行和列的形式存储数据。一系列的行和列组成了『表』，一组表组成了数据库。
注：关系型数据的本质是数据之间存在联系，而数据可能分散在不同的表中，因此形式上也表现为表之间存在关系。 -
非关系型数据库：也称为 NoSQL（Not Only SQL），意即“不仅仅是 SQL”。NoSQL 常以键值对、文档、图等格式来存储数据，数据结构不要求相同，存取数据更加灵活。
关系型数据库的优点是数据格式严谨、支持海量数据存储、确保数据一致性（ACID 特性）...但对于高并发场景的读写效率低（高并发下 IO 压力大），由于表间存在关系，因此在分布式场景下很难实现高扩展和高可用...
而非关系数据库却恰恰相反，它在海量数据中的并发读写效率异常出色，而且其数据存储基于键值对，数据之间没有耦合，在分布式场景下非常容易实现水平扩展...但是非关系型数据库不具备事务和数据强一致性。
需要注意的一点是，非关系型数据是对关系型数据库的一种补充，两者之间并不是对立冲突的。对数据库的选型应当考虑业务场景，比如，关系型数据库的最大优点就是事务的一致性，因此对于数据需要强一致性的场景（比如银行系统转账业务），应当选择关系型数据库。其他大多数场景，非关系型数据库可供优先选择。其实有时候甚至是需要同时结合使用关系型数据库和非关系型数据库，典型的场景比如缓存，缓存采用非关系型数据库存储，优先从缓存中获取数据，没有才到关系型数据库中进行查询。
常见的关系型数据库有：MySQL、Oracle、DB2、SQL Server、Postgre SQL 等，
非关系型数据库有 MongoDB、Redis、Memcached、HBase 等等。
本文主要介绍关系型数据库 MySQL 的一些基础用法，尽量涉及 MySQL 常用功能，可将本文作为 MySQL 使用的一个速查表（Cheat Sheet）。
注：本文使用的版本为 MySQL 8.0。
相关术语
在具体讲解 MySQL 相关内容前，需要对一些概念有所了解：
-
数据库（DataBase，DB）：按照某种特定的数据结构来组织、存储和管理数据的仓库。
数据库按数据组织方式（数据结构）的不同，大致可以分为几种模型：层次数据库，网状数据库、关系型数据库 和 非关系型数据库...
目前主流的数据库模型为关系型数据库和非关系型数据库。 -
关系型数据库：参考上文。
-
表：在关系型数据库中，数据以表格的形式展示，可以认为表是数据的矩阵。表由纵向的『列』和横向的『行』组成。
-
行：在关系型数据库中，行被称为『记录（record）』或『元组（tuple）』，是组织数据的单位。
一行的内容代表一个实体信息。 -
列：在关系型数据库中，列被称为『字段（field）』，每一列表示记录的一个属性，属性自身携带相应的描述信息，比如数据类型、数据宽度等（即表中每一列都限定为特定单一数据类型，一个列包含了相同数据类型的数据）。
-
主键（Primary Key）：又称为『主码』，用于唯一的标识表中的每一条记录。
可以将表中的一列或多列组合定义为主键。一个表中不能存在内容相同的两个主键，也不能存在空值主键（即主键值存在且唯一）。
注：虽然表并不总是需要主键，但是强烈建议建表时都定义一个主键，方便以后的数据操作和管理。 -
外键（Foreign Key）：外键是另一个表中的主键，通过外键就可以将当前表关联到另一个表。
-
数据库系统（Database System）：提供存储空间用于存储数据库的系统。一个数据库系统通常包含许多个数据库。
-
数据库管理系统（DataBase Management System，DBMS）：用于创建、管理和操纵数据库的软件。
DBMS 存在两种架构：基于共享文件系统的 DBMS 和 基于客户机 - 服务器的 DBMS。基于客户机 - 服务器的架构相对更加主流，其服务器端是负责所有数据访问和处理的一个软件，真正执行对数据库文件的增、删、改、查操作。而客户机只负责将用户对数据的操作请求发送给服务器，让服务器软件执行这个请求，再将操作结果返回给到客户机。
注：用于操纵关系型数据库的数据库管理系统（数据库软件）的对应术语称为：RDBMS（Relational DataBase Management System）。
注：MySQL 是一种基于客户机 - 服务器的 RDBMS，即 MySQL 是一种数据库软件，可用于管理和操作数据库。 -
数据库应用程序（DataBase Application）：数据库应用程序负责与 DBMS 进行通信，访问和管理 DBMS 中存储的数据。
注：虽然数据库应用程序也是通过 DBMS 实现对数据的操作，但相对于 DBMS，数据库应用程序可以提供更加简洁直观的数据管理操作体验。比如，MySQL 提供的客户端程序为mysql，它是一个基于命令行的 DBMS，操作相对不友好，此时可以使用带有界面的数据库应用程序（比如 Workbench、Navicat...）连接 MySQL 服务端，直接在界面上操作数据库，无需使用命令，体验更加友好。 -
SQL（Structured Query Language）：即结构化查询语言，SQL 是一种专门用于与数据库通信的领域特定语言（DSL)。
SQL 命令主要包含 5 大类内容：DDL、DML、DCL、TCL 和 DQL，如下表所示：SQL 语言 内容 数据定义语言（Data Definition Language，DDL） 主要包含 CREATE、DROP、ALTER等语句数据操作语言（Data Manipulation Language，DML） 主要包含 INSERT、UPDATE、DELETE等语句数据控制语言（Data Control Language，DCL） 主要包含 GRANT、REVOKE等语句事务控制语言（Transaction Control Language，TCL） 主要包含 COMMIT、ROLLBACK等语句数据查询语言（Data Query Language，DQL) 主要为 SELECT等语句注：虽然 SQL 存在一个标准委员会，其试图定义可供所有 DBMS 使用的统一 SQL 语法，但事实上，不同的 DBMS 可能实现不同的 SQL 语法。
注：完整的 SQL 语句可查看：MySQL - SQL Statements。
安装
MySQL 支持多平台安装，传统安装方式只需下载对应平台的 MySQL Community Server 即可。如下图所示：
MySQL Community Server
具体安装过程这里就不再赘述。
注：Linux 上安装 MySQL，可以直接使用发行版的包管理器，比如，Ubuntu 系统可以直接使用命令apt-get install mysql-server来安装最新版本的 MySQL。
但是本文不准备采用传统安装方式，而是采用在 Docker 中使用 MySQL。现在服务端很多采用的都是基于 Docker 的部署方式，这样可以避免环境配置等问题，简洁高效。
Docker 安装 MySQL 具体步骤如下：
注：请先确保用户系统已安装 Docker，再进行后续操作。
-
下载 MySQL 镜像：
$ docker pull mysql注：在 Docker Hub 上搜索 mysql，可以看到有很多个版本可供选择，这里我们选择的是官方版本，即第一个
mysql。但是 MySQL 官网教程使用的版本是 mysql/mysql-server，这个版本也是由 Oracle MySQL 团队维护的，该版本对 MySQL 的 Docker 镜像文件进行了一些优化。这两个版本任选其一即可，我这里由于 mysql-sever 镜像下载很慢（已设置国内源），就直接使用官方第一个版本，mysql-server 的配置方式都是差不多的，具体配置步骤可查看：附录 - Docker 安装 mysql/mysql-server。 -
启动一个容器运行 MySQL 镜像：
$ docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql其中：
-
--name mysql：表示容器名称为mysql。 -
-p hostPort:containPost：表示端口映射，即将容器端口映射到本机系统端口，后续外部程序访问hostPort就会重定向到容器containPost中。 -
-e MYSQL_ROOT_PASSWORD=123456：表示 ROOT 用户密码为123456。 -
-d：表示后台运行容器。
-
-
如果要将容器内的 MySQL 相关目录映射到本地系统，则可使用如下命令：
$ docker run --name mysql \ -v /usr/local/docker/mysql:/etc/mysql \ -v /usr/local/docker/mysql/conf:/etc/mysql/conf.d \ -v /usr/local/docker/mysql/logs:/var/log/mysql \ -v /usr/local/docker/mysql/data:/var/lib/mysql \ -v /usr/local/docker/mysql/lib/mysql-files:/var/lib/mysql-files \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORD=123456 \ -d mysql-
-v <host_dir>:<container_dir>：表示将本机目录host_dir挂载到容器目录container_dir。
-
-
可通过以下命令查看容器是否处于运行状态：
$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 5992f89357f0 mysql "docker-entrypoint.s�" 5 seconds ago Up 3 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql -
容器运行成功后，就可以进入容器：
$ docker exec -it mysql bash注：如果想为 docker bash 增加中文输入功能，可添加如下环境变量：
$ docker exec -it mysql env LANG=C.UTF-8 bash -
进入容器后，就可以使用容器内的 MySQL 服务了。比如，这里我们登录 MySQL：
$ mysql -u root -p Enter password:
至此，我们连接到 Docker 内部的 MySQL 服务端，现在我们就可以对 MySQL 进行操作了。
注：如果是在 Windows 子系统（即 wsl2）中使用 MySQL，可能会出现以下错误：
Error: Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)
这是因为 MySQL 后台服务未启动，此时通过以下命令即可启动服务，然后就可以登录 MySQL：
$ sudo /etc/init.d/mysql start
如果上述操作后仍然出现上述错误，则可以等待一会，直到创建了mysqld.sock（wsl2 中执行到这一步速度有时很慢），可通过日志查询启动过程：
$ docker logs -f mysql
...
2020-10-15T15:48:30.166060Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
编码规范
在对数据库进行操作前，有必要了解下数据库操作的一些编码规范，大致有如下：
-
SQL 关键字和函数均使用大写。
注：SQL 语句本身不区分大小写，但为了更好地区分 SQL 关键字与自定义字段，将 SQL 关键字等使用大写进行表示。 -
数据库名词，表名词、字段名称等均使用小写字母。
-
SQL 语句必须以分号
;结尾。
更多 MySQL 数据库编码规范，可参考：MySQL学习笔记-数据库设计规范
数据库基本操作
在安装完 MySQL 后，就可以对数据库进行操作了。首先介绍下 MySQL 中对数据库的基本操作。
注：后文中介绍到的任何 SQL 命令，都可以直接在 MySQL 命令行中通过help命令查看其具体信息。比如，我们想查看CREATE DATABASE命令，只需输入如下内容：
mysql> help CREATE DATABASE;
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
}
CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
# 甚至附带了官方文档链接
URL: https://dev.mysql.com/doc/refman/5.7/en/create-database.html
-
增：安装完数据库后，第一步就是创建一个数据库。创建数据库的 SQL 语句语法如下：
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ... create_option: [DEFAULT] { CHARACTER SET [=] charset_name | COLLATE [=] collation_name | ENCRYPTION [=] {'Y' | 'N'} }注：命令行参数中，各符号表示的意思如下表所示：
符号 描述 {}必须任选一个 {}内给出的选择<>必选参数 []可选参数 -
使用：在对数据库进行操作前，需要手动选择要使用的数据库。其语法如下：
USE db_name举个例子：下面手动创建一个数据库
whyn，并选中该数据库，后文所有的操作都基于该数据库下：mysql> CREATE DATABASE IF NOT EXISTS whyn; Query OK, 1 row affected (0.13 sec) # 选择使用数据库 whyn mysql> use whyn; Database changed -
查：查询数据库借助的是
SHOW命令，主要的数据库查询操作有如下几个：-
查询系统存在的数据库：具体使用的命令为
SHOW DATABASES，其语法如下所示：SHOW {DATABASES | SCHEMAS} [LIKE 'pattern' | WHERE expr]举个例子：查询数据库
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | whyn | +--------------------+ 5 rows in set (0.00 sec)可以看到，除了我们创建的数据库
whyn外，MySQL 本身内置了很多其他数据库文件，其中：- todo::
-
查询数据库的定义：具体命令为
SHOW CREATE DATABASE，其语法如下所示：SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name举个例子：查看我们上述创建的数据库
whyn具体详情：mysql> SHOW CREATE DATABASE whyn; +----------+--------------------------------------------------------------------------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------------------------------------------------------------------------+ | whyn | CREATE DATABASE `whyn` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +----------+--------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
-
-
改：修改数据库使用的命令为：
ALTER DATABASE，其具体语法如下：ALTER {DATABASE | SCHEMA} [db_name] alter_option ... alter_option: { [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name | [DEFAULT] ENCRYPTION [=] {'Y' | 'N'} | READ ONLY [=] {DEFAULT | 0 | 1} } -
删：删除数据库使用的命令为
DROP DATABASE，其具体语法如下：DROP {DATABASE | SCHEMA} [IF EXISTS] db_name注：使用
DROP DATABASE删除数据库时，MySQL 不会给出任何确认提醒信息，且一经删除，数据库中所有的表和数据都无法进行恢复，因此要谨慎使用该命令。
字段数据类型
在介绍 MySQL 表具体操作前，有必要先了解一下 MySQL 支持的字段数据类型。因为表创建的一个重要内容就是确定字段类型。
MySQL 字段支持多种数据类型，主要可分为三大类：『数值类型』，『日期和时间类型』和『字符串类型』，具体内容如下：
-
数值类型：主要包含『整数类型』，『浮点数类型』和『定点小数类型』，具体内容如下所示：
-
整数类型：MySQL 中的整数型数据类型如下表所示：
类型 占用空间(字节) 有符号最小值 有符号最大值 无符号最小值 无符号最大值 描述 TINYINT1 -128 127 0 255 微小整数 SMALLINT2 -32768 32767 0 65535 小整数 MEDIUMINT3 -8388608 8388607 0 16777215 中等大小整数 INT4 -2147483648 2147483647 0 4294967295 普通大小整数 BIGINT8 0 大整数 注：整数类型均可分为有符号类型（比如：
INT）和无符号类型（比如：INT UNSIGNED），两者的取值范围不同。整数类型在定义时可指定一个显示宽度，表示的是数值在展示时显示的位数，比如对于字段
year INT(4)，表示字段year显示时默认显示 4 位数字，位数超过 4 位时，则显示完整数字，位数不足时则由空格进行填充。
注：如果想使用0进行填充，则字段定义可使用ZEROFILL关键字修饰，即year INT ZEROFILL，同时，ZEROFILL隐含字段为无符号类型数据。需要注意的是，在 MySQL 8.0.17 版本时，ZEROFILL被标记为deprecated，建议通过LPAD()函数或使用Char类型进行表示。当未指定显示宽度时，系统会为每一种类型指定默认的宽度值，比如
TINYINT(4)、SMALLINT(6)、MEDIUMINT(9)、INT(11)、BIGINT(20)。默认的显示宽度能够确保完全显示数据类型所有范围内的取值，比如，TINYINT的有符号数值范围为，无符号数值范围为
，最大的显示个数为
-128共占 4 个显示宽度，因此，默认为TINYINT(4)。一个需要注意的地方是：显示宽度只用于显示，并不会影响取值范围和占用空间。
注：在 MySQL 8.0.17 版本中，整数类型的数据宽度规范被标记为
deprecated，因此现在使用DESC命令也不会输出显示整数类型的数据宽度。综上，在 MySQL 8.0 中，各个数值类型的语法如下：
TINYINT[(M)] [UNSIGNED] [ZEROFILL] SMALLINT[(M)] [UNSIGNED] [ZEROFILL] MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL] INT[(M)] [UNSIGNED] [ZEROFILL] INTEGER[(M)] [UNSIGNED] [ZEROFILL] # MySQL 中，INTEGER 是 INT 的同义词 BIGINT[(M)] [UNSIGNED] [ZEROFILL]其中：
-
M：表示数据显示宽度。 -
UNSIGNED：表示字段为无符号类型。 -
ZEROFILL：表示用0填充，且隐含字段为UNSIGNED。
-
注：更多数值类型详细内容，可在 MySQL 命令行中输入以下命令进行查询：
mysql> help TINYINT; # 查看 TINYINT 类型 mysql> help SMALLINT; # 查看 SMALLINT 类型 ...-
小数类型：MySQL 中用于表示小数的类型有两种：『浮点数』和『定点数』：
-
浮点数（floating-point）：浮点数有两种类型：单精度浮点数（
FLOAT）和双精度浮点数（DOUBLE）。如下表所示：类型 占用空间(字节) 有符号最小值 有符号最大值 无符号最小值 无符号最大值 描述 FLOAT4 和
单精度浮点数 DOUBLE8 双精度浮点数 FLOAT和DOUBLE的语法格式如下所示：FLOAT[(M,D)] [UNSIGNED] [ZEROFILL] # 非标准写法 FLOAT(p) [UNSIGNED] [ZEROFILL] DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL] DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL] # 非标准写法 REAL[(M,D)] [UNSIGNED] [ZEROFILL] # 非标准写法注：
DOUBLE PRECISION和REAL是 MySQL 的非标准扩展实现，它们默认都等同于DOUBLE，但是如果设置了 REAL_AS_FLOAT 模式，此时会将REAL类型视为FLOAT。其中：
-
M：称为精度，表示总共的有效位数（整数部分 + 小数部分）。 -
D：称为标度，表示小数的位数。 -
p：表示位精度。但是在 MySQL 中，p用来决定采用FLOAT类型还是DOUBLE类型存储。如果p的值为0到24之间，就采用FLOAT类型（不设置M和D)，如果p的值为25到53之间，就使用DOUBLE类型（不设置M和D）。
FLOAT和DOUBLE在不指定精度情况下，实际的精度则由计算机硬件和操作系统决定。一般情况下，FLOAT类型数据精确到小数点后 7 位左右（即精度最多为 7 位有效数字），DOUBLE类型数据精确到小数点后 15 位左右。 -
-
定点数（fixed-point）：定点数类型为
DECIMAL，其底层以二进制形式存储，因此存储的是准确的数字。如下表所示：类型 占用空间(字节) 描述 DECIMAL(M,D)精确的定点数 DECIMAL的语法如下所示：DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL] DEC[(M[,D])] [UNSIGNED] [ZEROFILL] # DEC 是 DECIMAL 的同义词 NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL] # NUMERIC 是 DECIMAL 的同义词 FIXED[(M[,D])] [UNSIGNED] [ZEROFILL] # FIXED 是 DECIMAL 的同义词DECIMAL中的M和D与浮点数类型数据含义一致，都表示精度和标度。
在 SQL 标准下，DECIMAL(5,2)总共能够存储 5 位有效数字，其中最多包含两个小数，因此其存储的范围为：。
注：DECIMAL存储精度不会将负号-包含在内。DECIMAL的存储空间并不是固定的，而是由其精度M决定的，总共占用个字节大小的空间。
DECIMAL中精度M最大取值为65，标度D的最大取值为30。但实际能表示的数值范围受精度和标度的限制，比如M最大可为65，即理论上可以精确到小数点后 65 位小数，但是标度最大只能30，此时小数部分超出精度（即第 31 位及其后）会进行四舍五入。DECIMAL可以不指定M和D的值，此时相当于采用DECIMAL(10,0)，即M的默认值为10，表示最多存储 10 位有效数字，而D的默认值为0，即不存储小数部分。
浮点数和定点数都可以用来表示小数，但它们之间的一些异同点有：
- 无论是浮点数还是定点数，如果插入的数据超出指定精度范围，都会执行四舍五入。
其中，浮点数静默执行四舍五入，而定点数在超出精度而执行四舍五入后，会给出一个警告。 - 浮点数存储的是近似的值（不精确），定点数存储的是精确值。
- 浮点数相对于定点数的优点是：在长度一定的情况下，浮点数能表示更大范围的数值。
缺点是：浮点数会引起精度问题。
浮点数和定点数的选择权衡：在对精度要求比较高的场景（比如货币，科学数据等），应当使用
DECIMAL类型，同时如果涉及到数据间的比较运算时，要牢记浮点数运算会存在误差，此时使用定点数会更适合。举个例子：创建表
tmp，定义三个字段a、b和c，其类型依次为FLOAT(5,1)，DOUBLE(5,1)和DECIMAL(5,1)，即都是五位有效数字，其中最多一个小数位置，然后为这三个字段都插入值5.12，分别查看下存储结果：mysql> CREATE TABLE tmp ( -> a FLOAT(5,1), -> b DOUBLE(5,1), -> c DECIMAL(5,1) -> ); Query OK, 0 rows affected, 2 warnings (0.54 sec) mysql> INSERT INTO tmp VALUES (5.12,5.12,5.12); Query OK, 1 row affected, 1 warning (0.17 sec) # 这里出现了一个警告 mysql> SHOW WARNINGS; # 该命令可显示当前会话出现的警告信息 +-------+------+----------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------+ | Note | 1265 | Data truncated for column 'c' at row 1 | # 这里的警告信息是字段 c 被截断了 +-------+------+----------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tmp; +------+------+------+ | a | b | c | +------+------+------+ | 5.1 | 5.1 | 5.1 | +------+------+------+ 1 row in set (0.00 sec)上述例子操作结果可以看到，MySQL 对于浮点数的精度超出，会静默自动执行四舍五入。而对于定点数的精度超出，会明确给出一个警告信息。
-
-
-
日期和时间类型：MySQL 中用于表示日期和时间的数据类型有：
DATE、TIME、DATETIME、TIMESTAMP和YEAR。如下表所示：类型 占用空间（字节） 日期格式 日期范围 描述 YEAR1 YYYY 1901 ~ 2155 年份 DATE3 YYYY-MM-DD 1000-01-01 ~ 9999-12-3 日期 TIME3 HH:MM:SS -838:59:59 ~ 838:59:59 时间 DATETIME8 YYYY-MM-DD hh:mm:ss[.fraction] 1000-01-01 00:00:00.000000 ~ 9999-12-31 23:59:59.999999 日期和时间 TIMESTAMP4 YYYY-MM-DD hh:mm:ss[.fraction] 1970-01-01 00:00:01.000000 UTC ~ 2038-01-19 03:14:07.999999 UTC 时间戳 各个时间类型的语法如下所示：
DATE DATETIME[(fsp)] TIMESTAMP[(fsp)] TIME[(fsp)] YEAR[(4)]其中：
fsp表示 fractional seconds precision，即微秒精度（格式上表现为时间类型的小数部分）。fsp的取值范围为，
0表示不携带小数部分，6表示最多显示 6 位小数。如果fsp忽略，则默认使用0（标准 SQL 默认值为6，MySQL 采用0是为了兼容旧版本）。在使用日期和时间类型数据时，需要牢记下面所列举的一些通用建议：
-
MySQL 以标准输出的格式接收输入数据，但它同时支持其他很多种不同的输入格式，比如
YEAR支持字符串格式'YYYY'和数值格式YYYY的输入（详情可参考：Date and Time Literals），不同的类型接收的输入格式可能不同，这部分格式稍显混乱，建议统一使用标准输出的格式进行输入。 -
当将日期或时间类型数据用于计算时，MySQL 会自动将其转换为数值类型，反之亦然。
-
默认情况下，当输入的数据超出对应日期或时间类型，或者输入无效数据时，MySQL 会自动将该值转换为“零”值。一个例外的情况为，当输入的数值超过
TIME类型范围时，该值会被转换为最接近TIME类型数值范围端点值。各种日期和时间的“零”值具体内容如下表所示：类型 "零"值 DATE'0000-00-00'TIME'00:00:00'DATETIME'0000-00-00 00:00:00'TIMESTAMP'0000-00-00 00:00:00'YEAR0000 -
“零”值是一些特殊的数值，我们可以存储和使用这些“零”值。要插入“零”值到表中时，可以简单地使用
0或'0'。对于包含日期部分的类型（比如DATE、DATETIME和TIMESTAMP），使用“零”值可能会引发警告或错误，这种行为可以通过启用 MySQL 的严格模式（strict mode）和NO_ZERO_DATE模式进行控制。
更多日期和时间类型的通用考虑，可参考：Date and Time Data Types
举个例子：创建数据表
tmp，为其添加多个日期字段，并分别插入一些数据：mysql> CREATE TABLE tmp ( -> year YEAR, -> date DATE, -> time TIME, -> dt DATETIME, -> ts TIMESTAMP -> ); Query OK, 0 rows affected (0.93 sec) mysql> INSERT INTO tmp VALUES ( -> '2020', # year -> '2020-10-25', # date -> '21:05:30', # time -> '2020-10-25 21:05:30', # datetime -> '2020-10-25 21:05:30' # timestamp -> ); Query OK, 1 row affected (0.17 sec) mysql> INSERT INTO tmp VALUES ( -> '2020', -> CURRENT_DATE, # 获取当前日期 -> '21:07:59', -> NOW(), # 获取系统当前日期和时间 -> NOW() -> ); Query OK, 1 row affected (0.08 sec) mysql> SELECT year, date, time, dt as datetime, ts as timestamp FROM tmp; +------+------------+----------+---------------------+---------------------+ | year | date | time | datetime | timestamp | +------+------------+----------+---------------------+---------------------+ | 2020 | 2020-10-25 | 21:05:30 | 2020-10-25 21:05:30 | 2020-10-25 21:05:30 | | 2020 | 2020-10-25 | 21:07:59 | 2020-10-25 13:07:55 | 2020-10-25 13:07:55 | +------+------------+----------+---------------------+---------------------+ 2 rows in set (0.00 sec)输入日期和时间时，可以使用 MySQL 内置的一些函数，比如
NOW()，CURRENT_DATE...
其中，NOW()返回日期和时间，CURRENT_DATE返回日期，如下所示：mysql> SELECT NOW(); +---------------------+ | now() | +---------------------+ | 2020-10-25 13:16:25 | # NOW() 输出格式 +---------------------+ 1 row in set (0.00 sec) mysql> SELECT CURRENT_DATE; +--------------+ | current_date | +--------------+ | 2020-10-25 | # CURRENT_DATE 输出格式 +--------------+ 1 row in set (0.00 sec)注：使用内置日期函数时，输出是以系统当前设置的时区为准。查看 MySQL 设置的时区方法如下：
mysql> SHOW VARIABLES LIKE "%time_zone%"; # 时区 +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | UTC | | time_zone | SYSTEM | +------------------+--------+ 2 rows in set (0.01 sec)MySQL 会依据变量
time_zone设置的时区为准。如果想将时区设置为本机系统一致（对于中国而言，除新疆等地外，均为东八区），需要将time_zone设置为+08:00，具体步骤为：打开全局配置文件/etc/mysql/my.cnf，输入如下内容：[mysqld] default-time_zone = '+08:00'注：更多 MySQL 内置日期时间函数，请参考：Date and Time Functions
注：更多 MySQL 配置文件信息，请参考：Using Option Files最后，一个需要注意的是，
TIMESTAMP和DATETIME的显示格式是一样的，显示宽度也都是固定 19 个字符，两者除了存储占用空间和支持的范围不同之外，最大的区别在于：DATETIME是按实际输入格式进行存储的，与时区无关。而TIMESTAMP的存储是以『世界标准时间（Coordinated Universal Time，UTC）』格式保存的，它首先会将输入数据（即YYYY-MM-DD hh:mm:ss[.fraction]）对当前时区进行转换，然后再存储。检索时再将值转换回当前时区，因此，查询时，同一时间戳在不同的时区显示的时间是不同的。
注：TIMESTAMP相对于DATETIME的另一个优点是：如果没有指定TIMESTAMP字段具体值，则 MySQL 会自动插入系统当前时间戳。 -
-
字符串类型：MySQL 内置的字符串类型除了可以存储文本字符串外，还支持存储图片和视频等二进制数据，原因是 MySQL 提供了两类字符串型数据：文本字符串和二进制字符串。
-
文本字符串（text string）：MySQL 内置的文本字符串类型如下表所示：
类型 占用空间(字节) 描述 CHAR(M)，且
定长文本字符串 VARCHAR(M)，且
其中，表示实际存储的字符长度
变长文本字符串 TINYTEXT变长很小文本字符串 TEXT，且
变长小文本字符串 MEDIUMTEXT，且
变长中等大小文本字符串 LONGTEXT，且
变长大文本字符串 ENUM1 或 2 字节，取决于枚举具体数量（最大值为 65536） 枚举字符串 SET1、2、3、4 或 8 字节，取决于集合成员具体数量（最多可为 64 个成员） 集合字符串 其中：
-
CHAR(M)为固定长度字符串，即无论输入数据大小，存储都为
当输入数据小于PAD_CHAR_TO_FULL_LENGTH。
注：如果未指定M，则默认M等于1。 -
VARCHAR(M)为长度可变的字符串，实际存储空间为输入数据大小。
。
与CHAR不同的是，VARCHAR存储的是输入数据的实际大小，即如果插入 10 个字符串，则实际存储为 11 字节（10 个字符加 1 个结束字符），且检索时，原义输出字符串。 -
TEXT类型常用于保存文章、评论等比较大的文本字符串，它也是按原义保存，原义检索。
TEXT类型可再细分为以下四种类型：-
TINYTEXT：表示很小的文本串，支持不超过个字符大小的字符串。
-
TEXT：表示小文本串，最大长度为，即字符串大小 64K 左右。
-
MEDIUMTEXT：表示中等大小的字符串，最大长度为，即 16M 左右。
-
LONGTEXT：表示大文本字符串，最大长度为，即 4G 左右。
-
-
ENUM类型是一个字符串对象，其语法为：字段名 ENUM('值1','值2',...,'值n')ENUM内部使用整数来代替枚举成员，每个枚举值都对应唯一索引号（MySQL 实际存储的是索引值）。索引数值由 1 开始编号，最大索引值为 65535（即最大能存储 65535 个枚举成员）。
如果枚举成员存在空格，则其尾部空格会自动被删除。
ENUM类型字段只能有一个值，且只能设置为枚举列表中的某一个值，或者设置为NULL（允许空值情况下），或者设置为错误值''。
比如，对于字段order ENUM('first','second','third')，其取值范围如下表所示：值 索引 NULLNULL''0first1second2third3注：
ENUM字段在未指定时，会自动设置一个默认值：如果ENUM字段可空，则默认值为NULL。如果字段不能为空NOT NULL，则其将第一个枚举成员作为默认值。 -
SET类型也是一个字符串对象，它可以同时存储 零个或多个值。SET字段最多容纳 64 个成员。其语法如下所示：字段名 SET('值1',’值2',...,'值n')SET与ENUM的内部存储都是使用整数，每个成员都对应一个唯一索引值。当成员携带空格时，其尾部空格也会被自动删除。
SET与ENUM的最大不同之处在于：ENUM字段只能存储一个枚举成员，而SET字段可以存储多个成员。举个例子：创建表
tmp，定义两个字段gender和hobby，代表性别和爱好。其中，gender是一个ENUM类型，其成员有male和female。hobby是一个SET类型，其成员有football、basketball和swimming。如下所示：mysql> CREATE TABLE tmp ( # 创建表 -> gender ENUM('male','female'), -> hobby SET('football','basketball','swimming') -> ); Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO tmp VALUES ( # 插入数据 -> 'female', -> 'swimming' -> ); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO tmp VALUES ( # 插入数据 -> 'male', -> 'basketball,football' # 一次性插入多个数据 -> ); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM tmp; +--------+---------------------+ | gender | hobby | +--------+---------------------+ | female | swimming | | male | football,basketball | +--------+---------------------+ 2 rows in set (0.00 sec)
各个文本字符串的语法如下所示：
[NATIONAL] CHAR[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] [NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name] TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name] TEXT[(M)] [CHARACTER SET charset_name] [COLLATE collation_name] MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name] LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name] ENUM('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name] SET('value1','value2',...) [CHARACTER SET charset_name] [COLLATE collation_name] -
-
二进制字符串（binary string）：MySQL 提供的二进制字符串其实就是字节流数据（byte strings），因此此类数据可用于图片、音视频等字节流数据存储。MySQL 内置的二进制字符串类型如下表所示：
类型 占用空间(字节) 描述 BIT(M)左右，且
位（bit） BINARY(M)定长字节流 VARBINARY(M)变长字节流 TINYBLOB(M)非常小的字节流对象 BLOB(M)小字节流对象 MEDIUMBLOB(M)中等大小字节流对象 LONGBLOB(M)大字节流对象 JSON同 BLOBJSON 格式数据 其中：
-
BIT(M)是位字段类型，M表示每个值的最大位数，范围为。如果省略
M，则默认为1。
BIT类型的数据输入可以直接输入十进制数据，也可以采用前缀b来输入二进制数据，比如b'101'表示以二进制格式存储数字5，此时至少存储 3 位二进制数字，即BIT(3)。 -
BINARY和VARBINARY类型类似于CHAR和VARCHAR，不同的是它们存储的是二进制字节数据。 -
BLOB类型是一个二进制大对象，用来存储可变数量的字节流数据。
BLOB类型可再细分为如下几种类型：-
TINYBLOB：表示很小的字节流对象，其最大长度为。
-
BLOB：表示小字节流对象，其最大长度为，即 64KB 左右。
-
MEDIUMBLOB：表示中等大小的字节流对象，其最大长度为，即 16MB 左右。
-
LONGBLOB：表示非常大的字节流对象，其最大长度为，即 4GB 左右。
-
-
JSON类型是 MySQL 8.0 新增的数据类型，MySQL 并未在底层存储直接支持 JSON 数据，而是通过在 Server 层提供一些便于操作 JSON 的函数，简单地将 JSON 编码成BLOB，然后交由存储引擎层进行处理。一个简单的例子如下所示：# 建表：data字段为 JSON 类型 mysql> CREATE TABLE tmp (data JSON); Query OK, 0 rows affected (0.25 sec) # 插入 JSON 数据（以字符串格式输入） mysql> INSERT INTO tmp VALUES ('{"key1": "value1"}'); Query OK, 1 row affected (0.01 sec) # 插入 JSON 数据（借助 JSON_OBJECT 函数） mysql> INSERT INTO tmp VALUES ( JSON_OBJECT('key2', 2, 'key3', 'value3') ); Query OK, 1 row affected (0.01 sec) # 插入 JSON 数据（借助 JSON_ARRAY 函数） mysql> INSERT INTO tmp VALUES ( JSON_ARRAY('value1', 2, NOW()) ); Query OK, 1 row affected (0.01 sec) # 查表 mysql> SELECT * FROM tmp; +---------------------------------------------+ | data | +---------------------------------------------+ | {"key1": "value1"} | | {"key2": 2, "key3": "value3"} | | ["value1", 2, "2020-11-19 22:51:46.000000"] | +---------------------------------------------+ 3 rows in set (0.00 sec)更多 JSON 数据类型详细内容，请查看：The JSON Data Type
各个二进制字符串的语法如下所示：
BIT[(M)] BINARY[(M)] VARBINARY(M) TINYBLOB BLOB[(M)] MEDIUMBLOB LONGBLOB JSON -
-
数据表基本操作
创建完数据库后，就可以在该数据库内进行数据表创建，让表作为某些实体数据的载体。
对数据表的基本操作，包含如下内容：
-
增：创建数据表的过程是规定数据列属性的过程，同时也是实施数据完整性约束的过程。表创建语法如下：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] [IGNORE | REPLACE] [AS] query_expression CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }其中：
-
TEMPORARY：该关键字用于创建一个临时表，该临时表只会在当前会话中可见，当关闭会话时，该临时表会自动被销毁。 -
IF NOT EXISTS：只有在表不存在时，才进行创建。 -
create_definition：表创建时的一些定义和约束。 -
table_options：一些用于表的选项。 -
partition_options：设置分析函数PARTITION BY的选项。 -
query_expression：查询表达式，创建表时可以通过查询表达式将其他表中的数据直接复制到新表中。
注：
create_definition、table_options、partition_options和query_expression的具体内容请查看：CREATE TABLE Statement可以看到，
CREATE TABLE命令带有很多选项，这里我们不过多纠结于选项细节中，只需关注常用的表创建方式即可，其格式如下所示：CREATE TABLE <表名> ( 字段名1 数据类型 [列级约束条件] [默认值], 字段名2 数据类型 [列级约束条件] [默认值], ... [表级约束条件] )其中：建表时，字段名和其对应的数据类型是必须提供的，然后我们还可以为字段和表添加一些约束，常用的约束有如下几种：
-
字段自增：可以通过为字段增加
AUTO_INCREMENT关键字来实现每次插入记录时，字段自增功能（从1开始自增）。
注：一个表中只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。
AUTO_INCREMENT约束的字段类型可以是任意的整数类型（TINYINT、SMALLINT、INT、BIGINT等等）。 -
主键约束（Primary Key Constraint）：主键能够唯一的标识表中的一条记录，可以结合外键来定义不同数据表之间的关系。主键列的数据唯一，且不能为空。主键按字段数量可分为两种类型：
- 单字段主键：即主键由一个字段表示。其语法如下所示：
# 格式一：直接指定 字段名 数据类型 PRIMARY KEY [默认值] # 格式二：主键约束指定 [CONSTRAINT <约束名>] PRIMARY KEY [字段名]- 多字段联合主键：即主键有多个字段联合组成。其语法如下所示：
# 主键约束指定 PRIMARY KEY [字段1, 字段2, ..., 字段n] -
外键约束（Foreign Key Constraint）：外键通常是另一张表的主键。一个表可以有一个或多个外键，用以对一张或多张表建立连接。一个表的外键可以为空值，若不为空值，则每个外键都必须指向另一个表中的主键。
注：定义外键后，不允许直接删除另一张表中关联的行。外键的语法规则如下所示：
[CONSTRAINT <外键名>] FOREIGN KEY 字段名1 [, 字段名2, ...] REFERENCES <主表名>(主键列1 [, 主键列2, ...])其中：
- 主表：也称为『父表』，指对于两个具有关联关系的表而言，关联字段中 主键 所在的表即为主表。
- 从表：也称为『子表』，指对于两个具有关联关系的表而言，关联字段中 外键 所在的表即为从表。
简单来说，定义了 外键 的表就是从表，而另一个关联表就是主表。
举个例子：创建两张表：文章
article和评论comment，每篇文章都对应一条或多条评论：mysql> CREATE TABLE comment ( -> id INT AUTO_INCREMENT PRIMARY KEY, # 主键 -> content TINYTEXT # 评论内容 -> ); Query OK, 0 rows affected (1.41 sec) mysql> CREATE TABLE article ( -> id INT AUTO_INCREMENT PRIMARY KEY, # 主键 -> title VARCHAR(30), # 标题 -> content TEXT, # 正文 -> comment_id INT, # 外键 -> CONSTRAINT fk_article_comment FOREIGN KEY(comment_id) REFERENCES comment(id) # 外键约束 -> ); Query OK, 0 rows affected (2.01 sec)注：这里的外键约束应当添加到表
comment中，以表示一条评论对应一篇文章，但此处设置不影响后文内容，就不进行修改了。以上我们就成功为表
article添加了名称为fk_article_comment的外键约束，外键具体字段为article.comment_id，其指向表comment的主键id。在后续如果我们对表
article和comment插入了相应内容后，此时我们无法直接删除comment表中相关内容，因为这些内容被表article关联了，必须先删除article中相关联的记录后，才能删除表comment中的相关内容。 -
默认约束（Default Constraint）：指为字段设置默认值，用户未显示设置该字段时，则使用默认值。其语法如下所示：
字段名 数据类型 DEFAULT 默认值 -
非空约束（Not Null Constraint）：对于使用了非空约束的字段，表示用户在添加数据时，必须为该字段设置值（除非该字段同时设置了默认约束）。其语法如下所示：
字段名 数据类型 NOT NULL -
唯一性约束（Unique Constraint）：唯一性约束要求该字段所有值不能出现重复值，且能且只能出现一个空值。其语法如下所示：
# 格式一：字段直接指定 字段名 数据类型 UNIQUE # 格式二：约束指定 [CONSTRAINT <约束名>] UNIQUE(<字段名>)
举个例子：创建一张学生表，涉及的信息包含学生名称
name，学号id，性别gender，年龄age，年级grade：mysql> CREATE TABLE IF NOT EXISTS student ( -> name VARCHAR(10) NOT NULL, # 非空约束 -> id MEDIUMINT UNSIGNED AUTO_INCREMENT, # 自增约束 -> gender ENUM('male','female'), -> age TINYINT, -> grade CHAR(15) NOT NULL DEFAULT 'grade one', # 非空约束 + 默认约束 -> PRIMARY KEY(id) # 主键约束 -> )ENGINE=InnoDB DEFAULT CHARSET=utf8; # 表选项上表中，我们设置了
id作为表student的主键，通常主键具备自增功能，因此其设置了AUTO_INCREMENT。在表级选项中，通过
ENGINE和CHARSET为表student设置了使用存储引擎InnoDB和字符集utf8。
注：实际上，从 MySQL 8.0 开始，数据库默认使用的存储引擎已经更改为InnoDB，默认的字符集也已经从latin1改为utf8mb4（utf8mb4是utf8的超集，其支持 4 字节字符，是真正意义上的utf8，而 MySQL 中的uft8只支持 3 字节字符插入（即utf8mb3），强烈建议后续字符编码都使用utf8mb4）。如下所示：mysql> SELECT VERSION(); # 查看 MySQL 版本 +-----------+ | VERSION() | +-----------+ | 8.0.21 | +-----------+ 1 row in set (0.00 sec) mysql> CREATE TEMPORARY TABLE tmp SELECT * FROM student; # 复制 student 表中所有数据到临时表 tmp 中 Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE tmp\G # \G 会将查询结果以垂直方式进行展示，方便阅读 *************************** 1. row *************************** Table: tmp Create Table: CREATE TEMPORARY TABLE `tmp` ( `name` varchar(10) CHARACTER SET utf8 NOT NULL, `id` mediumint unsigned NOT NULL DEFAULT '0', `gender` enum('male','female') CHARACTER SET utf8 DEFAULT NULL, `age` tinyint DEFAULT NULL, `grade` char(15) CHARACTER SET utf8 NOT NULL DEFAULT 'grade one' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci # 默认引擎，默认字符集... 1 row in set (0.00 sec)可以看到，在我们本机系统中，MySQL 版本为 8.0.21，其创建的表的默认引擎和字符集确实为
InnoDB和utf8mb4。 -
-
查：创建完成数据表后，就可以对表结构进行查询。表结构查询可分为如下两种类型：
-
基本结构查询：查看表的基本结构使用的命令为
DESCRIBE/DESC。其语法如下所示：# 格式一 {EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] # 格式二 {EXPLAIN | DESCRIBE | DESC} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} # 格式三 {EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] select_statement explain_type: { FORMAT = format_name } format_name: { TRADITIONAL | JSON | TREE } explainable_stmt: { SELECT statement | TABLE statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement }DESCRIBE和EXPLAIN是同义词，但是在实际操作中，通常都是使用DESCRIBE来查询表结构，而使用EXPLAIN来查看查询语句执行流程。举个例子：查看我们上述创建完成的表
article的基本结构：mysql> DESC article; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | title | varchar(30) | YES | | NULL | | | content | text | YES | | NULL | | | comment_id | int | YES | MUL | NULL | | +------------+-------------+------+-----+---------+----------------+ 4 rows in set (0.09 sec)DESCRIBE命令是SHOW COLUMNS的简写方式，其可用于查询表的字段信息，包含如下内容：-
Field：表示表字段名 -
Type：表示字段数据类型 -
Null：表示字段是否可以存储NULL值。YES表示可空约束，NO表示非空约束。 -
Key：表示字段是否编制索引。其中：-
PRI表示字段是表主键的一部分 -
UNI表示字段是UNIQUE索引的一部分 -
MUL表示在字段某个给定值可以出现多次
-
-
Default：表示字段的默认值 -
Extra：表示该字段的一些附加信息，比如auto_increment等
-
-
详细结构查询：查看表创建时的具体语句，可以使用
SHOW CREATE TABLE命令。其语法如下所示：SHOW CREATE TABLE <表名>\G注：
\G用来替换;，可使输出结果以垂直的方式进行展示，某些情况下显示效果更好。举个例子：查看表
article的详细结构：mysql> SHOW CREATE TABLE article\G *************************** 1. row *************************** Table: article Create Table: CREATE TABLE `article` ( `id` int NOT NULL AUTO_INCREMENT, `title` varchar(30) DEFAULT NULL, `content` text, `comment_id` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_article_comment` (`comment_id`), CONSTRAINT `fk_article_comment` FOREIGN KEY (`comment_id`) REFERENCES `comment` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)从上述查询结构可以看到，
SHOW CREATE TABLE命令不仅可以查看创建表时的详细语句，还可以查看表使用的存储引擎和字符编码等信息。 -
表状态查询：如果想查看表的状态信息，可以使用
SHOW TABLE STATUS命令。其语法如下所示：SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]举个例子：查询表
article的状态信息：mysql> SHOW TABLE STATUS LIKE 'article'\G *************************** 1. row *************************** Name: article Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 16384 Data_free: 0 Auto_increment: 2 Create_time: 2020-10-29 14:56:03 Update_time: 2020-10-29 15:06:03 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.01 sec) -
表索引信息查询：查看表中的索引信息，可以使用命令
SHOW INDEX。其具体语法如下：SHOW [EXTENDED] {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]举个例子：查看表
article的索引信息：mysql> show index from article\G *************************** 1. row *************************** Table: article # 表名 Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id # 索引字段名 Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: article # 表名 Non_unique: 1 Key_name: fk_article_comment Seq_in_index: 1 Column_name: comment_id # 索引字段名 Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 2 rows in set (0.01 sec)
-
-
改：修改表的命令为
ALTER TABLE，其语法如下所示：
注：对表进行修改前，最好首先对数据表进行备份操作，因为对表的修改操作有些是无法回退的，此时会造成数据的永久消失，预先备份可以让我们避免一些灾难性的后果。ALTER TABLE tbl_name [alter_option [, alter_option] ...] [partition_options]注：
alter_option和partition_options详细结构请查看：SQL - ALTER TABLE对数据表的修改，实际上修改的是表的结构，以下介绍常用的表修改操作：
-
修改表名：其语法如下所示：
ALTER TABLE <旧表名> RENAME [TO] <新表名>; -
修改字段类型：修改字段的数据类型使用的命令如下所示：
ALTER TABLE <表名> MODIFY <字段名> <数据类型>; -
修改字段名称：其语法如下所示：
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>;注：由于不同类型的数据其存储的方式和空间大小都不相同，因此修改字段数据类型可能会影响表中已有的数据记录，可能对数据造成破坏。
-
添加字段：为表添加新的字段，使用如下命令：
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST | AFTER 已存在的字段名];其中，默认将新添加字段设置为数据表的最后一列，也可以手动进行指定，如下所示：
-
FIRST：该可选参数的作用是将新添加的字段设置为表的第一个字段。 -
AFTER：该可选参数的作用是将新添加的字段添加到指定的表中已存在字段的后面。
-
-
删除字段：删除表中已存在的字段，可使用如下命令：
ALTER TABLE <表名> DROP <字段名>; -
修改字段的排列位置：字段位置在表创建时就进行指定了，后续若想更改字段排列位置，可使用如下命令：
ALTER TABLE <表名> MODIFY <字段1> <数据类型> FIRST | AFTER <字段2>;其中：
-
字段1：为要更改排列位置的字段。 -
数据类型：指字段1的数据类型。 -
字段2：参照字段，指将修改字段放置到字段2后面。
-
-
更改表存储引擎：MySQL 支持多种存储引擎（命令
SHOW ENGINES可查看内置支持的引擎），常用的引擎有MyISAM、InnoDB等。
表创建的时候会可以指定存储引擎，未指定则使用系统默认的存储引擎。后续更改存储引擎可使用如下命令：ALTER TABLE <表名> ENGINE=<新引擎>; -
添加外键约束：为字段添加外键约束，其语法如下所示：
ALTER TABLE <表名> ADD [CONSTRAINT [外键约束名]] FOREIGN KEY(列名) REFERENCES <引用外键表(列名)> -
删除外键约束：对于数据表中定义的外键，如果不再需要，可将其删除。其语法如下所示：
ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>举个例子：删除表
article中的指向comment表中的外键：mysql> SHOW CREATE TABLE article\G # 首次查看表结构 *************************** 1. row *************************** Table: article Create Table: CREATE TABLE `article` ( `id` int NOT NULL AUTO_INCREMENT, `title` varchar(30) DEFAULT NULL, `content` text, `comment_id` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_article_comment` (`comment_id`), CONSTRAINT `fk_article_comment` FOREIGN KEY (`comment_id`) REFERENCES `comment` (`id`) # 存在外键 fk_article_comment ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.10 sec) mysql> ALTER TABLE article DROP FOREIGN KEY fk_article_comment; # 删除外键 fk_article_comment Query OK, 0 rows affected (0.78 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE article\G # 再次查看表结构 *************************** 1. row *************************** Table: article Create Table: CREATE TABLE `article` ( `id` int NOT NULL AUTO_INCREMENT, `title` varchar(30) DEFAULT NULL, `content` text, `comment_id` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_article_comment` (`comment_id`) # 不存在外键约束 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)
-
-
删：删除一张表的时候，表的结构定义和表中所有的数据均会被删除，因此，删除表之前最好先进行备份，以免造成无法挽回的后果。
表删除命令的语法如下所示：DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]注：如果要删除的数据表与其他表之间存在外键关联情况，则此时无法直接删除父表，因为这会破坏表的参照完整性。
解决的办法有如下两种：- 先删除关联子表，再删除父表。
- 撤销关联子表的外键约束，再删除父表。
注：直接删除两个关联表方法简单粗暴，但是如果只需要删除父表，则应当采用删除外键约束这个方法。
举个例子：将表
article（子表）字段comment_id重新设置为指向表comment.id的外键，然后删除表comment（父表），观察结果。
然后再删除表article的外键约束，此时再删除表comment，观察结果：mysql> ALTER TABLE article ADD CONSTRAINT fk_article_comment FOREIGN KEY(comment_id) REFERENCES comment(id); # 增加外键约束 Query OK, 0 rows affected (7.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE article\G # 显示表结构 *************************** 1. row *************************** Table: article Create Table: CREATE TABLE `article` ( `id` int NOT NULL AUTO_INCREMENT, `title` varchar(30) DEFAULT NULL, `content` text, `comment_id` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_article_comment` (`comment_id`), CONSTRAINT `fk_article_comment` FOREIGN KEY (`comment_id`) REFERENCES `comment` (`id`) # 存在外键约束 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> DROP TABLE comment; # 删除父表（失败，存在外键约束） ERROR 3730 (HY000): Cannot drop table 'comment' referenced by a foreign key constraint 'fk_article_comment' on table 'article'. mysql> ALTER TABLE article DROP FOREIGN KEY fk_article_comment; # 删除子表外键约束 Query OK, 0 rows affected (0.48 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE article\G # 查看表结构 *************************** 1. row *************************** Table: article Create Table: CREATE TABLE `article` ( `id` int NOT NULL AUTO_INCREMENT, `title` varchar(30) DEFAULT NULL, `content` text, `comment_id` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `fk_article_comment` (`comment_id`) # 不是外键约束 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> DROP TABLE comment; # 删除父表（成功） Query OK, 0 rows affected (1.24 sec)
网友评论