在《1.概述和工具准备》，我们提过，SQL可以分为四类。

DQL(Data Query Languages)，数据查询语言。
DDL(Data Definition Languages)，数据定义语言。
DML(Data Manipulation Language)，数据操作语言。
DCL(Data Control Language)，数据控制语言。

上一章，我们讨论了DQL，这一章，我们主要讨论DDL、DML和DCL。

命名

规则

MySQL中的命名规则如下：

数据库名、表名不得超过30个字符，变量名不超过29个。
只能是A–Z、a–z、0–9以及_，这63个字符的组合。
同一个MySQL实例中，库不能重名。
同一个库中，表不能重名。
同一个表中，字段不能重名。
不能与"保留字、常用方法名"冲突，如果坚持使用，请在SQL语句中使用着重号`引起来。

规范

在阿里巴巴的开发规范中，还有补充：

【强制】表名、字段名必须使用小写字母或数字，禁止出现数字开头，禁止两个下划线中间只出现数字。
正例：aliyun_admin，rdc_config，level3_name
反例：AliyunAdmin，rdcConfig，level_3_name
【强制】禁用保留字，如desc、range、match、delayed等。
【强制】表必备三字段：id、gmt_create、gmt_modified。
说明：其中id必为主键，类型为BIGINT UNSIGNED、单表时自增、步长为1。gmt_create、gmt_modified的类型均为DATETIME类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。
【推荐】表的命名最好是遵循"业务名称_表的作用"。
正例：alipay_task、force_project、trade_config。
【推荐】库名与应用名称尽量一致。
【参考】合适的字符存储长度，不但节约数据库表空间、节约索引存储，更重要的是提升检索速度。

题外话，在阿里巴巴的开发规范中，表名、字段名必须使用小写字母或数字，但是MySQL官方的表，却有字段的首字母大写了，在本章最后"DCL：用户与权限管理"，讨论权限表的时候，会看到。

库

创建

创建数据库

1	CREATE DATABASE 数据库名;

创建数据库并指定字符集

1	CREATE DATABASE 数据库名 CHARACTER SET 字符集;

示例代码：

1	CREATE DATABASE gbk_ku CHARACTER SET gbk;

不存在才创建数据库

1	CREATE DATABASE IF NOT EXISTS 数据库名;

查看

查看当前所有的数据库

1	SHOW DATABASES;

注意，是复数，有一个S。

查看当前正在使用的数据库

1	SELECT DATABASE();

DATABASE()是一个MySQL的信息函数，返回MySQL命令行当前所在的数据库。在《2.DQL(SELECT)》讨论过。

使用/切换数据库

USE 数据库名;

查看当前库的表

1	SHOW TABLES;

查看指定库中的表

1	SHOW TABLES FROM 数据库名;

查看数据库的创建信息

1	SHOW CREATE DATABASE 数据库名;

示例代码：

1	SHOW CREATE DATABASE appr;

运行结果：

+--------+------------------------------------------------------------------------------------------------------------------------------+
|Database|Create Database                                                                                                               |
+--------+------------------------------------------------------------------------------------------------------------------------------+
|appr    |CREATE DATABASE `appr` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */|
+--------+------------------------------------------------------------------------------------------------------------------------------+

修改

更改数据库字符集

1	ALTER DATABASE 数据库名 CHARACTER SET 字符集;

示例代码：

1	ALTER DATABASE appr CHARACTER SET gbk;

数据库名称无法修改

数据库名称无法修改！

虽然有一些可视化工具可以改名，但其过程实际是建新库，把所有表复制到新库，再删除旧库。

删除

删除指定的数据库

1	DROP DATABASE 数据库名;

存在才删除数据库

1	DROP DATABASE IF EXISTS 数据库名;

表

创建

创建表

语法格式：

CREATE TABLE [IF NOT EXISTS] 表名(
  字段1, 数据类型 [约束条件] [默认值],
  字段2, 数据类型 [约束条件] [默认值],
  字段3, 数据类型 [约束条件] [默认值],
  ......
  [表约束条件]
);

解释说明：

IF NOT EXISTS：只有当前数据库中不存在要创建的数据表，才创建数据表。
必须指定：表名、字段、数据类型
可选指定：约束条件、默认值、表约束条件

示例代码：

CREATE TABLE emp
(
    emp_id   INT,
    emp_name VARCHAR(20),
    birthday DATE
);

创建表同时插入数据

创建表同时插入数据，基于AS 子查询。
语法格式：

1
2
3

CREATE TABLE 表名
[(字段1,字段2,...)]
AS 子查询

指定的列和子查询中的列要一一对应

接下来，我们给大家演示两种。
第一种，示例代码：

1
2
3

CREATE TABLE emp1 AS
SELECT *
FROM employees;

第二种，示例代码：

CREATE TABLE emp80
AS
SELECT employee_id, last_name, salary * 12 ANNSAL, hire_date
FROM employees
WHERE department_id = 80;

那么，现在就有一个问题了，被创建的表，其结构到底是怎么样的？

类似于"第一种"，新表的字段类型和"基础表"的字段类型是一致的，但没有索引。
类似于"第二种"，新表的字段类型是基于"基础表"的字段类型所衍生出来的。

想弄清楚更具体规则，需要点时间。但是没关系，一般我们不用这种方法。一般都是先定义好表，再插入数据。

完全复制表

上述"创建表同时插入数据"，只复制了表结构，没有复制索引结构。
LIKE 原始表，可以复制表结构以及索引结构，当然，只会完整的复制表的结构(包含索引结构)，不会复制原表中的数据。

1	CREATE TABLE 新表 LIKE 原始表;

示例代码：

1	CREATE TABLE emp3 LIKE employees;

1	DESC emp3;

运行结果：

+--------------+-----------+----+---+-------+-----+
|Field         |Type       |Null|Key|Default|Extra|
+--------------+-----------+----+---+-------+-----+
|employee_id   |int        |NO  |PRI|0      |     |
|first_name    |varchar(20)|YES |   |NULL   |     |
|last_name     |varchar(25)|NO  |   |NULL   |     |
|email         |varchar(25)|NO  |UNI|NULL   |     |
|phone_number  |varchar(20)|YES |   |NULL   |     |
|hire_date     |date       |NO  |   |NULL   |     |
|job_id        |varchar(10)|NO  |MUL|NULL   |     |
|salary        |double(8,2)|YES |   |NULL   |     |
|commission_pct|double(2,2)|YES |   |NULL   |     |
|manager_id    |int        |YES |MUL|NULL   |     |
|department_id |int        |YES |MUL|NULL   |     |
+--------------+-----------+----+---+-------+-----+

查看

查看表结构

查看表结构的方法：

1	DESC/DESCRIBE 表名;

示例代码：

DESC emp;

运行结果：

+--------+-----------+----+---+-------+-----+
|Field   |Type       |Null|Key|Default|Extra|
+--------+-----------+----+---+-------+-----+
|emp_id  |int        |YES |   |NULL   |     |
|emp_name|varchar(20)|YES |   |NULL   |     |
|birthday|date       |YES |   |NULL   |     |
+--------+-----------+----+---+-------+-----+

查看建表语句

语法结构：

1	SHOW CREATE TABLE 表名

通过SHOW CREATE TABLE，还可以看到存储引擎和字符编码等更多信息。

示例代码：

1	SHOW CREATE TABLE emp3;

运行结果：

+-----+---------------------------------------------+
|Table|Create Table                                 |
+-----+---------------------------------------------+
|emp3 |CREATE TABLE `emp3` (                        |
|     |`employee_id` int NOT NULL DEFAULT '0',      |
|     |`first_name` varchar(20) DEFAULT NULL,       |
|     |`last_name` varchar(25) NOT NULL,            |
|     |`email` varchar(25) NOT NULL,                |
|     |`phone_number` varchar(20) DEFAULT NULL,     |
|     |`hire_date` date NOT NULL,                   |
|     |`job_id` varchar(10) NOT NULL,               |
|     |`salary` double(8,2) DEFAULT NULL,           |
|     |`commission_pct` double(2,2) DEFAULT NULL,   |
|     |`manager_id` int DEFAULT NULL,               |
|     |`department_id` int DEFAULT NULL,            |
|     |PRIMARY KEY (`employee_id`),                 |
|     |UNIQUE KEY `emp_email_uk` (`email`),         |
|     |UNIQUE KEY `emp_emp_id_pk` (`employee_id`),  |
|     |KEY `emp_dept_fk` (`department_id`),         |
|     |KEY `emp_job_fk` (`job_id`),                 |
|     |KEY `emp_manager_fk` (`manager_id`)          |
|     |) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3      |
+-----+---------------------------------------------+

修改

通过ALTER TABLE，可以对表进行修改。

添加列

基于关键字ADD。

语法格式：

1	ALTER TABLE 表名 ADD [COLUMN] 字段名1 字段类型 [FIRST\|AFTER 字段名2];

FIRST和AFTER 字段名2，指定添加到某个位置。FIRST：第一列；AFTER 字段名2，指定的字段之后。

修改列

基于关键字MODIFY。

可以修改列的数据类型，长度、默认值和位置。

语法格式：

1	ALTER TABLE 表名 MODIFY [COLUMN] 字段名1 字段类型 [DEFAULT 默认值] [FIRST\|AFTER 字段名 2];

注意：对默认值的修改只影响今后对表的修改，不会对之前录入的数据造成影响。

删除列

基于关键字DROP。

语法格式：

1	ALTER TABLE 表名 DROP [COLUMN] 字段名

重命名列

基于关键字CHANGE。

语法格式：

1	ALTER TABLE 表名 CHANGE [column] 列名新列名新数据类型;

示例代码：

1 2	ALTER TABLE dept CHANGE department_name dept_name varchar(15);

重命名表

基于关键字RENAME。

语法结构：

1	ALTER table 原表名 RENAME [TO] 新表名;

特别的，对表名进行修改，也可以不依赖ALTER关键字。
语法结构：

1	RENAME TABLE 原表名 TO 新表名;

关于ALTER对表的修改，在下文讨论约束的时候，我们还会看到其应用。

删

语法格式:

1	DROP TABLE [IF EXISTS] 数据表1 [, 数据表2, ..., 数据表n];

示例代码：

1	DROP TABLE dept80;

清空表

语法格式:

1	TRUNCATE TABLE 表名：

有些资料会说，TRUNCATE TABLE在功能上与不带WHERE子句的DELETE 语句相同。
其实，并不完全相同，对于某个自增的字段，TRUNCATE TABLE之后，该字段会重起始位置重新开始，而对于不带WHERE子句的DELETE 语句，该字段会继续之前的计数。

DDL的原子化

DDL的原子化是8版本的新特性，所谓的原子化，即或者都成功，或者都失败。

例如，假设现在存在一张表，book1，同时不存在book2。

在5版本中，执行如操作：

1	DROP TABLE book1,book2;

会报错，提示book2不存在，但是book1还是会被删除。
而在8版本中，也会报错，提示book2不存在，同时book1不会被删除。

记录

INSERT：插入

为表的所有字段按默认顺序插入记录

语法格式：

1	INSERT INTO 表名 VALUES (value1,value2,....);

注意：值列表中需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序相同。

为表的指定字段插入记录

语法格式：

1	INSERT INTO 表名(column1 [, column2, …, columnn]) VALUES (value1 [,value2, …, valuen]);

value1,....valuen需要与column,...columnn列一一对应。
VALUES也可以写成VALUE，但是VALUES是标准写法。

有部分资料会说，字符和日期数据应包含在单引号中，实际中，字符需要在单引号中，但是日期数据不一定要在单引号中，在下文讨论数据类型中的"日期与时间类型"时，会看到。
(但，建议包含在单引号中，可读性更强。)

同时插入多条记录

语法格式：

INSERT INTO table_name (column1 [, column2, …, columnn])
VALUES
(value1 [,value2, …, valuen]),
(value1 [,value2, …, valuen]),
...
(value1 [,value2, …, valuen]);

也可以不指定列，这样相当于为表的所有字段按默认顺序插入数据。
在新增数据的效果上与多个单行插入的INSERT语句相同，但是效率更高。

示例代码：

INSERT INTO emp(emp_id,emp_name)
VALUES (1001,'桃花岛'),
       (1002,'无量洞'),
       (1003,'星宿派');

将查询结果插入到表中

在INSERT语句中加入子查询。

语法格式：

INSERT INTO 目标表名 
(tar_column1 [, tar_column2, …, tar_columnn]) 
SELECT 
(src_column1 [, src_column2, …, src_columnn]) 
FROM 
源表名 
[WHERE condition]

不必书写VALUES子句。
子查询中的值列表应与INSERT子句中的列名对应。

UPDATE：更新

语法格式：

1
2
3

UPDATE table_name 
SET column1=value1, column2=value2, … , column=valuen 
[WHERE condition]

可以一次更新多条数据。
使用WHERE子句指定需要更新的数据，如果省略WHERE子句，则表中的所有数据都将被更新。

DELETE：删除

语法格式：

1	DELETE FROM table_name [WHERE <condition>];

与更新数据类似：

可以一次删除多条数据。
使用WHERE子句指定需要删除的数据，如果省略WHERE子句，则表中的所有数据都将被删除。

数据类型

常见数据类型

MySQL中的常见数据类型有：

类型	举例
整数类型	`TINYINT`、`SMALLINT`、`MEDIUMINT`、`INT`(或`INTEGER`)、`BIGINT`
浮点类型	`FLOAT`、`DOUBLE`
定点类型	`DECIMAL`
日期时间类型	`DATE`、`TIME`、`DATETIME`、`TIMESTAMP`
文本字符串类型	`CHAR`、`VARCHAR`、`TINYTEXT`、`TEXT`、`MEDIUMTEXT`、`LONGTEXT`
位类型	`BIT`
二进制字符串类型	`BINARY`、`VARBINARY`、`TINYBLOB`、`BLOB`、`MEDIUMBLOB`、`LONGBLOB`
JSON类型	`JSONObject`(JSON对象)、`JSONArray`(JSON数组)
枚举类型	`ENUM`
集合类型	`SET`

整数类型

类型介绍

整数类型	字节	有符号数取值范围	无符号数取值范围
`TINYINT`	$1$	$[-128,127]$	$[0,255]$
`SMALLINT`	$2$	$[-32768,32767]$	$[0,65535]$
`MEDIUMINT`	$3$	$[-8388608,8388607]$	$[0,16777215]$
`INT`(`INTEGER`)	$4$	$[-2^{31},2^{31}-1]$	$[0,2^{32}-1]$
`BIGINT`	$8$	$[-2^{63},2^{63}-1]$	$[0,2^{64}-1]$

在《基于Java的后端开发入门：1.基础语法》中，我们讨论过Java中的基本数据类型，在取值范围中：

MySQL中有符号的TINYINT的取值范围，与Java中byte相同。
MySQL中有符号的SMALLINT的取值范围，与Java中short相同。
MySQL中有符号的INT的取值范围，与Java中int相同。
MySQL中有符号的BIGINT的取值范围，与Java中long相同。

可选属性

整数类型的可选属性有三个：

M
UNSIGNED
ZEROFILL

M：显示宽度

从8.0.17版本开始，整数类型不推荐使用显示宽度属性。
所以关于该部分的例子，基于5.7版本。

M，表示 显示宽度，M的取值范围是 $(0,255)$ ，例如，int(5)。
注意，是 显示宽度 ，不会对插入的数据有任何影响。

如果在定义表结构时，没有指定所需要的显示宽度，会使用默认值，每一种整型的默认显示宽度不一样。示例代码：

CREATE TABLE test_int1
(
    x TINYINT,
    y SMALLINT,
    z MEDIUMINT,
    m INT,
    n BIGINT
);

1	DESC test_int1;

运行结果：

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra | 
+-------+--------------+------+-----+---------+-------+
| x     | tinyint(4)   | YES  |     |   NULL  |       | 
| y     | smallint(6)  | YES  |     |   NULL  |       | 
| z     | mediumint(9) | YES  |     |   NULL  |       | 
| m     | int(11)      | YES  |     |   NULL  |       | 
| n     | bigint(20)   | YES  |     |   NULL  |       | 
+-------+--------------+------+-----+---------+-------+

指定显示宽度，示例代码：

CREATE TABLE test_int2
(
    f1 INT,
    f2 INT(5),
    f3 INT(5) ZEROFILL
)

1	DESC test_int2;

运行结果：

+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra | 
+-------+--------------------------+------+-----+---------+-------+
| f1    | int(11)                  | YES  |     | NULL    |       | 
| f2    | int(5)                   | YES  |     | NULL    |       | 
| f3    | int(5) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+

对指定显示宽度的表，插入数据，示例代码：

INSERT INTO test_int2(f1, f2, f3)
VALUES (1, 123, 123);

INSERT INTO test_int2(f1, f2)
VALUES (123456, 123456);

INSERT INTO test_int2(f1, f2, f3)
VALUES (123456, 123456, 123456);

SELECT * FROM test_int2

运行结果：

+--------+--------+--------+
| f1     | f2     | f3     |
+--------+--------+--------+
| 1      | 123    | 00123  | 
| 123456 | 123456 | NULL   | 
| 123456 | 123456 | 123456 | 
+--------+--------+--------+

解释说明：f3还有一个属性ZEROFILL，所以在数字用零填充了。

UNSIGNED：无符号类型

示例代码：

CREATE TABLE test_int3
(
    f1 INT UNSIGNED
);

1	DESC test_int3;

运行结果：

+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra | 
+-------+------------------+------+-----+---------+-------+
| f1    | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+

解释说明：无符号的INT的默认显示宽度为10。

ZEROFILL：0填充

具体例子，可以参考上文关于M的讨论。

浮点类型

简介

MySQL中的浮点类型有三种：

FLOAT，单精度浮点数，使用4个字节。
DOUBLE，双精度浮点数，使用8个字节。
REAL，REAL则和MySQL的设置有关，默认是DOUBLE，也可以启用"REAL_AS_FLOAT"。启用方式为SET sql_mode = "REAL_AS_FLOAT";。
这种一般不用。

M和D：精度

从8.0.17开始，在官方已经明确表示，不推荐这么使用了。
而且，这是非标准的语法，即其他的数据库不一定支持。

可以通过FLOAT(M,D)、DOUBLE(M,D)对精度进行定义。D是小数位长度，M整数位长度加上小数位长度， $D \in[0,30]$ ， $M \in [D,255]$ 。
例如，FLOAT(5,2)的范围是 $[-999.99,999.99]$ 。

在存储时，如果超出了精度，会进行四舍五入，如果超出了范围，会报错。

示例代码：

CREATE TABLE test_double1
(
    f1 FLOAT,
    f2 FLOAT(5, 2),
    f3 DOUBLE,
    f4 DOUBLE(5, 2)
);

1 2	INSERT INTO test_double1 VALUES (123.456, 123.456, 123.456, 123.456);

1	SELECT * FROM test_double1

运行结果：

+-------+------+-------+------+
|f1     |f2    |f3     |f4    |
+-------+------+-------+------+
|123.456|123.46|123.456|123.46|
+-------+------+-------+------+

解释说明：如果不定义M和D，会按照实际支持的精度来显示。

UNSIGNED：无符号类型

在8.0.17中，关于FLOAT和DOUBLE的UNSIGNED也不推荐使用了。

误差

现在，我们来看一个现象。

假设，存在一张表，其结构如下：

Field	Type	Null	Key	Default	Extra
f1	double	YES		NULL

其数据如下：

f1
0.47
0.44
0.19

现在我们进行SUM，结果应该是1.1。

示例代码：

1 2	SELECT SUM(f1) FROM test_double2

运行结果：

+------------------+
|SUM(f1)           |
+------------------+
|1.0999999999999999|
+------------------+

这就是误差，具体原因是数据在计算机中以二进制的形式存储。解决方法为，使用定点类型。

定点类型：DECIMAL

MySQL中的定点类型只有DECIMAL一种，格式为DECIMAL(M,D)，D是小数位长度，M整数位长度加上小数位长度， $D \in[0,30]$ ， $M \in [D,65]$ 。默认为DECIMAL(10,0)。

在存储时，如果超出了精度，会进行四舍五入，如果超出了范围，会报错。

示例代码：

CREATE TABLE test_decimal1
(
    f1 DECIMAL,
    f2 DECIMAL(5, 2)
);

1	DESC test_decimal1;

运行结果：

+-----+-------------+----+---+-------+-----+
|Field|Type         |Null|Key|Default|Extra|
+-----+-------------+----+---+-------+-----+
|f1   |decimal(10,0)|YES |   |NULL   |     |
|f2   |decimal(5,2) |YES |   |NULL   |     |
+-----+-------------+----+---+-------+-----+

如果超出了精度，会进行四舍五入，示例代码：

1	INSERT INTO test_decimal1(f1,f2) VALUES(123.123,123.456);

1	SELECT * FROM test_decimal1;

运行结果：

+---+------+
|f1 |f2    |
+---+------+
|123|123.46|
+---+------+

如果超出了范围，会报错，示例代码：

1	INSERT INTO test_decimal1(f2) VALUES(1234.34);

运行结果：

1	Out of range value for column 'f2' at row 1

我们把test_double2表中字段f1的数据类型修改为DECIMAL(5,2)，再进行SUM，试一下。
示例代码：

1	ALTER TABLE test_double2 MODIFY f1 DECIMAL(5,2);

1	SELECT SUM(f1) FROM test_double2;

运行结果：

+-------+
|SUM(f1)|
+-------+
|1.10   |
+-------+

日期与时间类型

MySQL有多种表示日期和时间的数据类型，不同的版本可能有所差异，MySQL8.0版本支持的日期和时间类型主要有：

类型	名称	字节	日期格式	最小值	最大值
DATE	日期(年、月、日)	3	YYYY-MM-DD	1000-01-01	9999-12-03
TIME	时间(时、分、秒)	3	HH:MM:SS	-838:59:59	838:59:59
DATETIME	日期时间(年、月、日、时、分、秒)	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期时间(带时区的年、月、日、时、分、秒)	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

其中TIME类型，不仅仅用来表示一天之内的时间，还可以用来表示一个时间间隔，所以取值范围超过24小时。

DATE

在向DATE类型的字段插入数据时，可以使用几种不同的格式：

以YYYY-MM-DD格式或者YYYYMMDD格式表示的日期，其最小取值为1000-01-01，最大取值为9999-12-03，YYYYMMDD格式会被转化为YYYY-MM-DD格式
以YY-MM-DD格式或者YYMMDD格式表示的字符串日期，当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。
使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期。

示例代码：

CREATE TABLE test_date1
(
    f1 DATE
);

INSERT INTO test_date1
VALUES ('2020-10-01'),
       ('20201001'),
       (20201001),
       ('00-01-01'),
       ('000101'),
       ('69-10-01'),
       ('691001'),
       ('70-01-01'),
       ('700101'),
       ('99-01-01'),
       ('990101'),
       (CURRENT_DATE()),
       (NOW());

1	SELECT * FROM test_date1

运行结果：

+----------+
|f1        |
+----------+
|2020-10-01|
|2020-10-01|
|2020-10-01|
|2000-01-01|
|2000-01-01|
|2069-10-01|
|2069-10-01|
|1970-01-01|
|1970-01-01|
|1999-01-01|
|1999-01-01|
|2022-05-03|
|2022-05-03|
+----------+

TIME

在向TIME类型的字段插入数据时，也可以使用几种不同的格式：

可以使用带有冒号的字符串，比如'D HH:MM:SS'、'HH:MM:SS'、'HH:MM'、'D HH:MM'、'D HH'或'SS'格式，其中D表示天，其最小值为0，最大值为34，在插入TIME类型的字段时，D会被转化为小时。
在不带有D的情况下，MySQL会将最左边的解析成小时，即'12:10'表示12:10:00，而不是00:12:10。
可以使用不带有冒号的字符串或者数字，格式为'HHMMSS'或者HHMMSS。
最右边的两位会被解析成秒，例如，1210表示00:12:10，而不是12:10:00。
使用CURRENT_TIME()或者NOW()，会插入当前系统的时间。

示例代码：

CREATE TABLE test_time1
(
    f1 TIME
);

INSERT INTO test_time1
VALUES ('2 12:30:29'),
       ('12:35:29'),
       ('12:40'),
       ('2 12:40'),
       ('1 05'),
       ('45'),
       ('123520'),
       ('124011'),
       ('1210'),
       (124011),
       (1210),
       (NOW()),
       (CURRENT_TIME());

1	SELECT * FROM test_time1

运行结果：

+--------+
|f1      |
+--------+
|60:30:29|
|12:35:29|
|12:40:00|
|60:40:00|
|29:00:00|
|00:00:45|
|12:35:20|
|12:40:11|
|00:12:10|
|12:40:11|
|00:12:10|
|15:56:46|
|15:56:46|
+--------+

DATETIME

在向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件：

以YYYY-MM-DD HH:MM:SS格式或者YYYYMMDDHHMMSS格式插入DATETIME类型的字段时，最小值为1000-01-01 00:00:00，最大值为9999-12-03 23:59:59。
年份也可以用两位数字表示，遵循相同的规则，当年份取值为00到69时，会被转化为2000到2069；当年份取值为70到99时，会被转化为1970到1999。
(以数字的形式插入的话，不能以0开头。)
使用函数CURRENT_TIMESTAMP()和NOW()，可以向DATETIME类型的字段插入系统的当前日期和时间。

示例代码：

CREATE TABLE test_datetime1
(
    dt DATETIME
);

INSERT INTO test_datetime1
VALUES ('2021-01-01 06:50:30'),
       ('20210101065030'),
       ('99-01-01 00:00:00'),
       ('990101000000'),
       ('20-01-01 00:00:00'),
       ('200101000000'),
       (20200101000000),
       (200101000000),
       (19990101000000),
       (990101000000),
       (CURRENT_TIMESTAMP()),
       (NOW());

1	SELECT * FROM test_datetime1

运行结果：

+-------------------+
|dt                 |
+-------------------+
|2021-01-01 06:50:30|
|2021-01-01 06:50:30|
|1999-01-01 00:00:00|
|1999-01-01 00:00:00|
|2020-01-01 00:00:00|
|2020-01-01 00:00:00|
|2020-01-01 00:00:00|
|2020-01-01 00:00:00|
|1999-01-01 00:00:00|
|1999-01-01 00:00:00|
|2022-05-03 16:06:55|
|2022-05-03 16:06:55|
+-------------------+

TIMESTAMP

TIMESTAMP在存储数据的时候需要对当前时间所在的时区进行转换，查询数据的时候再将时间转换回当前的时区。因此，使用TIMESTAMP存储的同一个时间值，在不同的时区查询时会显示不同的时间。

示例代码：

CREATE TABLE test_timestamp1
(
    ts TIMESTAMP
);

INSERT INTO test_timestamp1
VALUES ('1999-01-01 03:04:50'),
       ('19990101030405'),
       ('99-01-01 03:04:05'),
       ('990101030405'),
       ('2020-01-01 00:00:00'),
       ('20-01-01 00:00:00'),
       (CURRENT_TIMESTAMP()),
       (NOW());

1	SELECT * FROM test_timestamp1

运行结果：

+-------------------+
|ts                 |
+-------------------+
|1999-01-01 03:04:50|
|1999-01-01 03:04:05|
|1999-01-01 03:04:05|
|1999-01-01 03:04:05|
|2020-01-01 00:00:00|
|2020-01-01 00:00:00|
|2022-05-03 16:24:39|
|2022-05-03 16:24:39|
+-------------------+

TIMESTAMP和DATETIME的区别：

TIMESTAMP存储空间比较小，表示的日期时间范围也比较小。
底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
TIMESTAMP和时区有关，会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

文本字符串类型

文本字符串类型有：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET等。

(其中TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT，不允许作为主键。)

CHAR

用法

CHAR(M)，M是字符串的长度，类型一般需要预先定义字符串长度；如果不指定(M)，则表示长度默认是1个字符。
如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度；当MySQL检索CHAR类型的数据时，会去除尾部的空格。

M默认为1，示例代码：

CREATE TABLE test_char1
(
    c1 CHAR,
    c2 CHAR(5)
);

1	DESC test_char1;

运行结果：

+-----+-------+----+---+-------+-----+
|Field|Type   |Null|Key|Default|Extra|
+-----+-------+----+---+-------+-----+
|c1   |char(1)|YES |   |NULL   |     |
|c2   |char(5)|YES |   |NULL   |     |
+-----+-------+----+---+-------+-----+

示例代码：

1 2	INSERT INTO test_char1 VALUES ('a', 'Tom');

1	SELECT * From test_char1;

运行结果：

+--+---+
|c1|c2 |
+--+---+
|a |Tom|
+--+---+

当MySQL检索CHAR类型的数据时，会去除尾部的空格，示例代码：

1 2	INSERT INTO test_char1(c2) VALUES ('a ');

1	SELECT CHAR_LENGTH(c2) FROM test_char1;

运行结果：

+---------------+
|CHAR_LENGTH(c2)|
+---------------+
|3              |
|1              |
+---------------+

争议

有些资料说，CHAR(M)中M，就是其所占的存储空间的字节数。实际上是不对的！
(或许在5版本之前，是这样的。)

示例代码：

1 2	INSERT INTO test_char1(c1) VALUES ('哈');

1	SELECT * FROM test_char1

运行结果：

+----+----+
|c1  |c2  |
+----+----+
|a   |Tom |
|NULL|a   |
|哈   |NULL|
+----+----+

如果M就是其子节数的话，是不可能插入中文哈的，在《基于Java的后端开发入门：5.IO流》中，我们讨论过：在GBK编码中，一个汉字占两个字节；在UTF-8编码中，大部分常用字(含中文)，使用三个字节编码；其他极少使用的Unicode辅助字符，使用四字节编码。
所以，M就是其子节数的，这种说法是错误的。

特别的，我们还可以看看，具体占用的子节。
示例代码：

1 2	SELECT c1, LENGTH(c1) FROM test_char1;

运行结果：

+----+----------+
|c1  |LENGTH(c1)|
+----+----------+
|a   |1         |
|NULL|NULL      |
|哈   |3         |
+----+----------+

VARCHAR

特点有：

VARCHAR(M)，定义时必须指定长度M，否则报错；最大为21845。
检索VARCHAR类型的字段数据时，会保留数据尾部的空格。

必须指定长度M，示例代码：

CREATE TABLE test_varchar1
(
    NAME VARCHAR
);

运行结果：

1	You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 4

M最大为21845，示例代码：

CREATE TABLE test_varchar2
(
    NAME VARCHAR(65535)
);

运行结果：

1	Column length too big for column 'NAME' (max = 21845); use BLOB or TEXT instead

示例代码：

1	INSERT INTO test_varchar3 VALUES('硅谷'),('硅谷鲑鱼');

1	SELECT * FROM test_varchar3

运行结果：

+----+
|NAME|
+----+
|硅谷  |
|硅谷鲑鱼|
+----+

固定长度和可变长度

那么，CHAR(M)和VARCHAR(M)的区别到底是什么呢？
CHAR(M)是固定长度的，VARCHAR(M)是可变长度的。

位类型：BIT

BIT(M)，位类型，其中M表示长度， $M\in[1,64]$ ，默认为BIT(1)。

示例代码：

CREATE TABLE test_bit1
(
    f1 BIT,
    f2 BIT(5),
    f3 BIT(64)
);

1	DESC test_bit1

运行结果：

+-----+-------+----+---+-------+-----+
|Field|Type   |Null|Key|Default|Extra|
+-----+-------+----+---+-------+-----+
|f1   |bit(1) |YES |   |NULL   |     |
|f2   |bit(5) |YES |   |NULL   |     |
|f3   |bit(64)|YES |   |NULL   |     |
+-----+-------+----+---+-------+-----+

如果插入的数据超出表示范围，会报错，示例代码：

1	INSERT INTO test_bit1(f1) VALUES(2);

运行结果：

1	Data too long for column 'f1' at row 1

解释说明，2在二进制中表示为10。

二进制字符串类型

MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据。
(通常，不建议在数据库中存文件。)

MySQL中支持的二进制字符串类型有：BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。

BINARY和VARBINARY

BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。

BINARY(M)为固定长度的二进制字符串，M表示最多能存储的字节数，范围是 $[0,255]$ 。如果未指定M，表示只能存储1个字节。如果字段值不足(M)个字节，将在右边填充’0’以补齐指定长度。

VARBINARY(M)为可变长度的二进制字符串，M表示最多能存储的字节数，范围是 $[0,65535]$ ，另外还需要1或2个字节来存储数据的字节数；必须指定M，否则报错。

示例代码：

CREATE TABLE test_binary1
(
    f1 BINARY,
    f2 BINARY(3),
    f3 VARBINARY(10)
);

1	INSERT INTO test_binary1(f1,f2) VALUES('a','a');

1	SELECT * FROM test_binary1

运行结果：

+--+--------+----+
|f1|f2      |f3  |
+--+--------+----+
|a |0x610000|NULL|
+--+--------+----+

我们来试一下插入中文，示例代码：

1	INSERT INTO test_binary1(f1,f2) VALUES('哈','哈');

运行结果：

1	Data too long for column 'f1' at row 1

解释说明：因为中文占3个字节，超长了。

BLOB和MEDIUMBLOB等

二进制字符串类型	值的长度	长度范围	占用空间(字节)
TINYBLOB	L	$[0,255]$	$L+1$
BLOB	L	$[0,65535]$ (相当于64KB)	$L+2$
MEDIUMBLOB	L	$[0,16777215]$ (相当于16MB)	$L+3$
LONGBLOB	L	$[0,4294967295]$ (相当于4GB)	$L+4$

JSON

MySQL从5.7开始，支持直接用SQL解析JSON了。

数据类型是JSON

我们直接举例子。

示例代码：

CREATE TABLE test_json
(
    js json
);

INSERT INTO test_json (js)
VALUES ('{
  "name": "Kaka",
  "age": 18,
  "address": {
    "province": "JiangXi",
    "city": "NanChang"
  }
}');

1	SELECT * FROM test_json;

运行结果：

+-----------------------------------------------------------------------------------+
|js                                                                                 |
+-----------------------------------------------------------------------------------+
|{"age": 18, "name": "Kaka", "address": {"city": "NanChang", "province": "JiangXi"}}|
+-----------------------------------------------------------------------------------+

解析JSON有两种方法：

JSON_EXTRACT
->

示例代码：

SELECT JSON_EXTRACT(js, '$.name')             AS NAME,
       JSON_EXTRACT(js, '$.age')              AS age,
       JSON_EXTRACT(js, '$.address.province') AS province,
       JSON_EXTRACT(js, '$.address.city')     AS city
FROM test_json;

运行结果：

+------+---+---------+----------+
|NAME  |age|province |city      |
+------+---+---------+----------+
|"Kaka"|18 |"JiangXi"|"NanChang"|
+------+---+---------+----------+

示例代码：

SELECT js -> '$.name'             AS NAME,
       js -> '$.age'              AS age,
       js -> '$.address.province' AS province,
       js -> '$.address.city'     AS city
FROM test_json;

运行结果：

+------+---+---------+----------+
|NAME  |age|province |city      |
+------+---+---------+----------+
|"Kaka"|18 |"JiangXi"|"NanChang"|
+------+---+---------+----------+

这两种方法，我个人感觉JSON_EXTRACT，更清晰。

数据类型不是JSON

特别的，如果数据类型不是JSON，比如是VARCHAR，或者TEXT，但是其存储的字符串是JSON字符串，也是可以解析的。

示例代码：

CREATE TABLE `test_json_2nd`
(
    `t` text
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb3

假设其中有两条记录。

{
    "tpl": {
        "items": [{
                "type": "image",
                "config": {
                    "expandable": true,
                    "linkAble": true
                },
                "url": "https://fs.esf.fangdd.net/test/FiZ0OtkhTZoD7fOtkp55SnuLGiKu.png?imageView2/2/w/750",
                "id": 1542348252537
            },
            {
                "type": "image",
                "config": {
                    "expandable": true,
                    "linkAble": true
                },
                "url": "https://fs.esf.fangdd.net/test/FlR1VDQWEzD406NosLFrJUez4g_X.png?imageView2/2/w/750",
                "id": 1542348263477
            },
            {
                "type": "image",
                "config": {
                    "expandable": true,
                    "linkAble": true
                },
                "url": "https://fs.esf.fangdd.net/test/FhMuYkWvnoMbv8I1dlQbm1KaX5Kn.png?imageView2/2/w/750",
                "id": 1542348269599
            },
            {
                "type": "image",
                "config": {
                    "expandable": true,
                    "linkAble": true
                },
                "url": "https://fs.esf.fangdd.net/test/FlgR4IUNElPbcgjN2re_9A8jX30v.png?imageView2/2/w/750",
                "id": 1542348276124
            },
            {
                "type": "image",
                "config": {
                    "expandable": true,
                    "linkAble": true
                },
                "url": "https://fs.esf.fangdd.net/test/FpXF8ETHxU8aqriiKbsYDjnu2Xd5.png?imageView2/2/w/750",
                "id": 1542348282561
            },
            {
                "type": "image",
                "config": {
                    "expandable": true,
                    "linkAble": true
                },
                "url": "https://fs.esf.fangdd.net/test/FkUz5m7Jd6kE2slSyreDucozc3XH.png?imageView2/2/w/750",
                "id": 1542348288150,
                "link": "http://www.baidu.com"
            }
        ],
        "bottomItems": [

        ],
        "title": "demo2",
        "description": "",
        "wxLogo": "",
        "bodyStyleInline": {

        },
        "bg": "",
        "bgType": "",
        "bottomStyleInline": {

        },
        "bottomBg": "",
        "bottomBgType": "",
        "uuid": "aaef8dfe-256a-4559-aec9-95d1fcdcf830",
        "activeItemsName": "items",
        "activeImgType": "",
        "authInfo": {
            "role_list": [{
                    "name": "test",
                    "access_key_list": [

                    ]
                },
                {
                    "name": "审核人员",
                    "access_key_list": [

                    ]
                }
            ],
            "city_list": [

            ],
            "userId": 3108779,
            "userName": "用户名",
            "email": "邮箱",
            "mobile": "手机号",
            "isEmployee": true
        }
    }
}

{"k":"v"}

示例代码：

1 2	SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(t, "$.tpl"), "$.authInfo"), "$.userName") AS userName FROM test_json_2nd

运行结果：

+----------+
|userName  |
+----------+
|"用户名"   |
|NULL      |
+----------+

如果被查询的，有某一行不符合JSON规范，会报错，所有的行都无法返回。
如果符合JSON规范，但是解析不到相关的字段，结果会是NULL。

解析JSON数组

如果是数组，可以用$[0].属性，获取第一个；可以用$[*].属性，获取所有的。

解析items中第一项的url。
示例代码：

1 2	SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(t, "$.tpl"), "$.items"), "$[0].url") AS url FROM test_json_2nd

运行结果：

+------------------------------------------------------------------------------------+
|url                                                                                 |
+------------------------------------------------------------------------------------+
|"https://fs.esf.fangdd.net/test/FiZ0OtkhTZoD7fOtkp55SnuLGiKu.png?imageView2/2/w/750"|
|NULL                                                                                |
+------------------------------------------------------------------------------------+

解析items中每一项的url。
示例代码：

1 2	SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(t, "$.tpl"), "$.items"), "$[*].url") AS url FROM test_json_2nd

运行结果：

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|url                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|["https://fs.esf.fangdd.net/test/FiZ0OtkhTZoD7fOtkp55SnuLGiKu.png?imageView2/2/w/750", "https://fs.esf.fangdd.net/test/FlR1VDQWEzD406NosLFrJUez4g_X.png?imageView2/2/w/750", "https://fs.esf.fangdd.net/test/FhMuYkWvnoMbv8I1dlQbm1KaX5Kn.png?imageView2/2/w/750", "https://fs.esf.fangdd.net/test/FlgR4IUNElPbcgjN2re_9A8jX30v.png?imageView2/2/w/750", "https://fs.esf.fangdd.net/test/FpXF8ETHxU8aqriiKbsYDjnu2Xd5.png?imageView2/2/w/750", "https://fs.esf.fangdd.net/test/FkUz5m7Jd6kE2slSyreDucozc3XH.png?imageView2/2/w/750"]|
|NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

解析items中每一项的config的expandable。
示例代码：

1
2
3

SELECT JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(t, "$.tpl"), "$.items"), "$[*].config"),
                    "$[*].expandable") AS expandable
FROM test_json_2nd

运行结果：

+------------------------------------+
|expandable                          |
+------------------------------------+
|[true, true, true, true, true, true]|
|NULL                                |
+------------------------------------+

如何选择

在阿里巴巴《Java开发手册》，有关于字段类型的选择。

任何字段如果为非负数，必须是UNSIGNED
【强制】小数类型为DECIMAL，禁止使用FLOAT和DOUBLE。
【强制】如果存储的字符串长度几乎相等，使用CHAR定长字符串类型。
【强制】VARCHAR是可变长字符串，不预先分配存储空间，长度不要超过5000。如果存储长度大于此值，定义字段类型为TEXT，独立出来一张表，用主键来对应。

约束

概述

约束是表级的强制规定。

分类

根据约束数据列的限制，约束可分为：

单列约束：每个约束只约束一列。
多列约束：每个约束可约束多列。

根据约束的作用范围，约束可分为：

列级约束：只能作用在一个列上，跟在列的定义后面。
表级约束：可以作用在多个列上，不与列一起，而是单独定义。

查看某个表已有的约束的方法为：

1	SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

根据约束起的作用，约束可分为：

NOT NULL：非空约束，规定某个字段不能为空
UNIQUE：唯一约束，规定某个字段在整个表中是唯一的
PRIMARY KEY：主键(非空且唯一)约束
AUTO_INCREMENT：自增约束
DEFAULT：默认值约束
FOREIGN KEY：外键约束
CHECK：检查约束

其中FOREIGN KEY外键约束和CHECK检查约束，因为不建议使用，我们不讨论。
不建议使用的原因是，所有的业务逻辑，应该在代码层面实现，而不应该放在数据库层面实现。

NOT NULL：非空约束

简介

作用：限定某个字段/某列的值不允许为空

关键字：NOT NULL。

特点：

默认所有的类型的值都可以是NULL。
非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空。
一个表可以有很多列都分别限定了非空。
空字符串''不等于NULL，0也不等于NULL。

添加非空约束

在建表时

CREATE TABLE 表名称
(
  字段名1 数据类型1,
  字段名2 数据类型2 NOT NULL,
  字段名3 数据类型3 NOT NULL
);

在建表后：

1 2	ALTER TABLE 表名称 MODIFY 字段名数据类型 NOT NULL;

删除非空约束

修改为NULL。

1 2	ALTER TABLE 表名称 MODIFY 字段名数据类型 NULL;

去除NOT NULL，因为默认值就是可以为NULL。

1 2	ALTER TABLE 表名称 MODIFY 字段名数据类型;

UNIQUE：唯一性约束

简介

作用：用来限制某个字段/某列的值不能重复。

关键字：UNIQUE。

特点：

同一个表可以有多个唯一约束。
唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一。
唯一性约束允许列值为空。
在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同。
MySQL会给唯一约束的列上默认创建一个唯一索引。

添加唯一约束

在建表时添加唯一约束

可以在字段名之后定义：

CREATE TABLE 表名称
(
    字段名1 数据类型1,
    字段名2 数据类型2 UNIQUE,
    字段名3 数据类型3 UNIQUE KEY,
    字段名4 数据类型4
);

也可以在字段名之外定义，这种方式可以设置组合唯一：

CREATE TABLE 表名称
(
    字段名 数据类型,
    字段名 数据类型,
    字段名 数据类型, 
    [ CONSTRAINT 约束名] UNIQUE KEY(字段名1,字段名2)
);

在建表后添加唯一约束

可以在字段名之后定义：

1	alter table 表名称 modify 字段名字段类型 unique;

也可以在字段名之外定义，这种方式可以设置组合唯一：

1	alter table 表名称 add unique key(字段列表);

删除唯一约束

正如上文所述，添加唯一性约束的列上也会自动创建唯一索引，删除唯一约束的方位为删除唯一索引。
示例代码：

1	ALTER TABLE USER DROP INDEX 【索引名】;

那么，唯一索引的名字是什么？
唯一索引的名字和唯一约束的名字是一样。
那么，唯一约束的名字是什么？

如果创建唯一约束时未指定名称
- 如果是单列，就默认和列名相同。
- 如果是多列，就默认和第一列的列名相同。
如果创建时指定了名称，那么就是指定的名称。

PRIMARY KEY：主键约束

简介

作用：用来唯一标识表中的一行记录。

关键字：PRIMARY KEY

特点：

主键约束列不允许重复，也不允许出现空值。
一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建(复合主键)。
MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用。
当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引；如果删除主键约束了，主键约束对应的索引就自动删除了。
一般不要修改主键字段的值；因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能会破坏数据的完整性。

添加主键约束

建表时指定主键约束

在列级别：

CREATE TABLE 表名称
(
    字段名1 数据类型1 PRIMARY KEY,
    字段名2 数据类型2,
    字段名3 数据类型3
);

在表级别：

create table 表名称
(
    字段名1 数据类型1,
    字段名2 数据类型2,
    字段名3 数据类型3, 
    [ constraint 约束名] primary key (字段名)
)

示例代码：

CREATE TABLE e
(
    id   INT NOT NULL,
    NAME VARCHAR(20),
    pwd  VARCHAR(15),
    CONSTRAINT e_pk PRIMARY KEY (NAME, pwd)
);

1	SHOW CREATE TABLE e

运行结果：

+-----+-----------------------------------------+
|Table|Create Table                             |
+-----+-----------------------------------------+
|e    |CREATE TABLE `e` (                       |
|     |`id` int NOT NULL,                       |
|     |`NAME` varchar(20) NOT NULL,             |
|     |`pwd` varchar(15) NOT NULL,              |
|     |PRIMARY KEY (`NAME`,`pwd`)               |
|     |) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3  |
+-----+-----------------------------------------+

建表后增加主键约束

在列级别：

1	ALTER TABLE 表名称 ADD PRIMARY KEY(字段列表);

字段列表可以是一个字段，也可以是多个字段，如果是多个字段的话，是复合主键

在表级别：

1	ALTER TABLE student ADD PRIMARY KEY (sid);

删除主键约束

1	ALTER TABLE 表名称 DROP PRIMARY KEY;

示例代码：

1 2	ALTER TABLE e DROP PRIMARY KEY;

1	SHOW CREATE TABLE e

运行结果：

+-----+-----------------------------------------+
|Table|Create Table                             |
+-----+-----------------------------------------+
|e    |CREATE TABLE `e` (                       |
|     |`id` int NOT NULL,                       |
|     |`NAME` varchar(20) NOT NULL,             |
|     |`pwd` varchar(15) NOT NULL               |
|     |) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3  |
+-----+-----------------------------------------+

解释说明：

删除主键约束，不需要指定主键名，因为一个表只有一个主键。
删除主键约束后，非空约束还存在。

AUTO_INCREMENT：自增约束

简介

作用：某个字段的值自增。

关键字：AUTO_INCREMENT。

特点：

一个表最多只能有一个自增长列。
自增长列约束的列必须是主键列。
自增约束的列的数据类型必须是整数类型。
如果在插入时，对自增列指定了0或NULL，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

持久化

8版本的一个新特性是自增变量的持久化。

在8版本之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。

比如，现在有一张表，结果如下：

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NULL	auto_increment
name	varchar(32)	YES		NULL

我们往张表中插入4条数据，且在插入时，都不给id指定内容，那么，表中的数据应该是

id	name
1	a
2	b
3	c
4	d

然后，我们删除id为4的，再重启数据库，再插入新的数据，那么这时候，新数据的id应该是多少？
对于8版本，是5，但是对于8版本之前的，是4。
这是因为8版本将自增主键的计数器持久化到"重做日志"中，如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值，而在8版本之前，是由InnoDB数据字典内部一个计数器来决定的，而该计数器只在内存中维护。

关于自增约束的新增和修改方法等，不讨论，和其他的约束并没有太大区别。

DEFAULT：默认值

作用：给某个字段/某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显式赋值，则赋值为默认值。

关键字：DEFAULT

特点：

只有列级的默认值约束，没有表级的默认值约束。
修改表的默认值约束后，只会对新插入的数据生效，不会影响已经插入的数据。

关于默认值约束的新增和修改方法等，我们也不讨论，和其他的约束并没有太大区别。

视图

视图概述

视图建立在已有表的基础上, 视图赖以建立的这些表称为基表，其关系如图：

即视图是一种虚拟表，本身是不具有数据的，只占用很少的内存空间，可以将视图理解为存储起来的SELECT语句。
所以，视图的创建和删除只影响视图本身，不影响对应的基表。
但如果对视图中的数据进行增加、删除和修改操作时，基表中的数据也可能会相应地发生变化。

视图的基本语法结构为

CREATE [OR REPLACE] 
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] 
VIEW 视图名称 [(字段列表)] 
AS 查询语句 
[WITH [CASCADED|LOCAL] CHECK OPTION]

创建视图

1	CREATE VIEW 视图名称 AS 查询语句

根据查询语句的不同，视图还可以分为：

单表视图
多表联合视图
基于视图的视图

查看视图

可以通过，SHOW TABLES查看数据库的表对象、视图对象，需要注意的是，这个命令会展示所有的表和视图，我们并不能判断，哪个是表，哪个是视图。

可以通过SHOW TABLE STATUS LIKE '视图(表)名称'，查看视图(表)的属性信息(存储引擎、版本、数据行数和数据大小等)，对于视图，其Comment的内容为VIEW。
示例代码：

1	SHOW TABLE STATUS LIKE 'emp_view';

运行结果：

+--------+------+-------+----------+----+--------------+-----------+---------------+------------+---------+--------------+-------------------+-----------+----------+---------+--------+--------------+-------+
|Name    |Engine|Version|Row_format|Rows|Avg_row_length|Data_length|Max_data_length|Index_length|Data_free|Auto_increment|Create_time        |Update_time|Check_time|Collation|Checksum|Create_options|Comment|
+--------+------+-------+----------+----+--------------+-----------+---------------+------------+---------+--------------+-------------------+-----------+----------+---------+--------+--------------+-------+
|emp_view|NULL  |NULL   |NULL      |NULL|NULL          |NULL       |NULL           |NULL        |NULL     |NULL          |2022-05-03 19:40:59|NULL       |NULL      |NULL     |NULL    |NULL          |VIEW   |
+--------+------+-------+----------+----+--------------+-----------+---------------+------------+---------+--------------+-------------------+-----------+----------+---------+--------+--------------+-------+

可以通过SHOW CREATE VIEW 视图名称，查看视图的详细定义信息。
示例代码：

1	SHOW CREATE VIEW emp_view

运行结果：

+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
|View    |Create View                                                                                                                                                                                     |character_set_client|collation_connection|
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+
|emp_view|CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `emp_view` AS select `emp`.`emp_id` AS `emp_id`,`emp`.`emp_name` AS `emp_name`,`emp`.`birthday` AS `birthday` from `emp`|utf8mb4             |utf8mb4_0900_ai_ci  |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+--------------------+

修改视图

修改视图有两种方法：

CREATE OR REPLACE VIEW
示例代码：

CREATE OR REPLACE VIEW empvu80 (id_number, name, sal, department_id) AS
SELECT employee_id, first_name || ' ' || last_name, salary, department_id
FROM employees
WHERE department_id = 80;

注意：CREATE OR REPLACE VIEW子句中各列的别名应和子查询中各列相对应。

ALTER VIEW
1
ALTER VIEW 视图名称 AS查询语句

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据。

删除视图的语法是：

1	DROP VIEW IF EXISTS 视图名称1,视图名称2,视图名称3,...;

更新视图的数据

虽然可以更新视图数据，但视图作为虚拟表，主要用于方便查询，不建议更新视图的数据，我们也不讨论。
如果确实需要对视图数据的更改，建议通过对基表的操作来完成的。

用户与权限管理

最后一个话题，DCL，数据控制语言，主要作用是用户与权限管理。

用户管理

创建用户

1	CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];

解释说明：

"用户名"表示新建用户的账户，由用户(User)和主机名(Host)构成，默认的主机名(Host)是%。
[ ]表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。
可以同时创建多个用户。

修改用户名

通过修改mysql.user表中的数据，可以修改用户名。
示例代码：

1 2	UPDATE mysql.user SET USER='li4' WHERE USER='wang5'; FLUSH PRIVILEGES;

删除用户

有两种方法，删除用户：

通过DROP删除
1
DROP USER user[,user]…;
通过删除mysql.user表中的数据删除
示例代码：
1
2
DELETE FROM mysql.user WHERE Host='hostname' AND User='username';
FLUSH PRIVILEGES;
但！不建议通过这种方法删除，因为该方法会有许多残留信息(比如权限表中可能还有相关记录)，建议通过DROP删除。

设置当前用户密码

有两种方法，设置当前用户密码：

通过ALTER USER来修改当前用户密码

1	ALTER USER USER() IDENTIFIED BY 'new_password';

通过SET来修改当前用户密码：
1
SET PASSWORD='new_password'

还有一种旧的方法，在8版本中已经无效：

1	SET PASSWORD = PASSWORD('123456');

修改其他用户密码

有三种方法，修改其它用户密码：

通过ALTER USER来修改其他用户的密码：

1	ALTER USER user [IDENTIFIED BY '新密码'] [,user[IDENTIFIED BY '新密码']]…;

通过SET来修改其他用户的密码：

1	SET PASSWORD FOR 'username'@'hostname'='new_password';

通过修改mysql.user表中的数据，修改其他用户的密码：

1 2	UPDATE mysql.user SET authentication_string=PASSWORD("新密码") WHERE User = "username" AND Host = "hostname"; FLUSH PRIVILEGES;

同样，这种方法不建议。

权限管理

权限列表

可以可以通过show privileges，查看MySQL中的权限。

示例代码：

1	show privileges;

运行结果：

+---------------------------+-------------------------------------+-----------------------------------------------------+
|Privilege                  |Context                              |Comment                                              |
+---------------------------+-------------------------------------+-----------------------------------------------------+
|Alter                      |Tables                               |To alter the table                                   |
|Alter routine              |Functions,Procedures                 |To alter or drop stored functions/procedures         |
|Create                     |Databases,Tables,Indexes             |To create new databases and tables                   |
|Create routine             |Databases                            |To use CREATE FUNCTION/PROCEDURE                     |
|Create role                |Server Admin                         |To create new roles                                  |

【部分运行结果略】

|FLUSH_OPTIMIZER_COSTS      |Server Admin                         |                                                     |
|TABLE_ENCRYPTION_ADMIN     |Server Admin                         |                                                     |
|FLUSH_TABLES               |Server Admin                         |                                                     |
|CONNECTION_ADMIN           |Server Admin                         |                                                     |
|INNODB_REDO_LOG_ENABLE     |Server Admin                         |                                                     |
+---------------------------+-------------------------------------+-----------------------------------------------------+

权限很多，我们列举部分重要的权限。

CREATE和DROP
可以创建新的数据库和表，或删除已有的数据库和表。
SELECT、INSERT、UPDATE和DELETE
允许在一个数据库现有的表上执行相关的操作。
INDEX
允许创建或删除索引，适用于已有的表。
ALTER
可以使用ALTER TABLE来更改表的结构和重新命名表。
GRANT
允许授权给其他用户，可用于数据库、表和保存的程序。
FILE
使用户可以使用LOAD DATA INFILE和SELECT … INTO OUTFILE语句读或写服务器上的文件。

授予权限

有两种方法，授予权限：

直接给用户授权
通过角色赋予用户，间接给用户授权

我们先讨论第一种，直接给用户授权。

授权命令：

1	GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

注意：如果发现没有该用户，会直接新建一个用户。

查看权限

有三种方法，查看当前用户权限：

SHOW GRANTS
SHOW GRANTS FOR CURRENT_USER
SHOW GRANTS FOR CURRENT_USER()

查看其他用户的权限：SHOW GRANTS FOR 'user'@'主机地址'

收回权限

收回权限就是取消已经赋予用户的某些权限。

收回权限命令：

1	REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;

收回全库全表的所有权限，示例代码：

1	REVOKE ALL PRIVILEGES ON . FROM joe@'%';

收回mysql库下的所有表的插删改查权限，示例代码：

1	REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

注意：收回权限，需要用户重新登录后才能生效！

权限表

最重要的权限表是mysql.user。

示例代码：

1	DESC mysql.user

运行结果：

+------------------------+---------------------------------+----+---+---------------------+-----+
|Field                   |Type                             |Null|Key|Default              |Extra|
+------------------------+---------------------------------+----+---+---------------------+-----+
|Host                    |char(255)                        |NO  |PRI|                     |     |
|User                    |char(32)                         |NO  |PRI|                     |     |
|Select_priv             |enum('N','Y')                    |NO  |   |N                    |     |
|Insert_priv             |enum('N','Y')                    |NO  |   |N                    |     |
|Update_priv             |enum('N','Y')                    |NO  |   |N                    |     |

【部分运行结果略】

|Drop_role_priv          |enum('N','Y')                    |NO  |   |N                    |     |
|Password_reuse_history  |smallint unsigned                |YES |   |NULL                 |     |
|Password_reuse_time     |smallint unsigned                |YES |   |NULL                 |     |
|Password_require_current|enum('N','Y')                    |YES |   |NULL                 |     |
|User_attributes         |json                             |YES |   |NULL                 |     |
+------------------------+---------------------------------+----+---+---------------------+-----+

mysql.user表中的字段很多，主要有：

范围列(用户列)：
1. host：连接类型，%表示可以通过所有的机器进行连接，IP或机器名表示指定的IP或机器名才可以连接。
2. user：用户名，同一用户通过不同方式链接的权限是不一样的。
3. password：密码，是密文字符串。
权限列：
1. Grant_priv：是否拥有GRANT权限。
2. Shutdown_priv：是否拥有停止MySQL服务的权限。
3. Super_priv：是否拥有超级权限。
4. Execute_priv：是否拥有EXECUTE权限(拥有EXECUTE权限，可以执行存储过程和函数)。
5. Select_priv、Insert_priv：是否拥有查询和新增的权限。

除此之外，权限表还有：

mysql.db：某个主机的某个用户对库的权限，字段有很多，其业务含义和mysql.user类似。
mysql.tables_priv：某个主机的某个用户对某个库的某张表的权限，包括Select、Insert、Update、Delete、Create、Drop、Grant、 References、Index和Alter。
mysql.columns_priv：某个主机的某个用户对某个库的某张表的某列权限，包库Select、Insert、Update和References。

确认权限时：

首先检查mysql.user表
如果相关权限没有在mysql.user表中被授予，那么MySQL就会继续检查mysql.db表
如果相关权限没有在mysql.db表中被授予，那么MySQL就会继续检查mysql.tables_priv表
如果相关权限没有在mysql.tables_priv表中被授予，那么MySQL就会继续检查mysql.columns_priv表
如果最后还是没有找到，那么将返回错误信息

需要注意的是，并不是所有的权限都要执行该过程，如果比如DROP某张表，最多会存在于mysql.tables_priv，那么检查到此，就不会再往下检查了。

角色管理

第二种权限管理的方法，通过角色赋予用户，间接给用户授权。

为什么需要角色

为了更方便的管理权限，用一张图就可以说明。

角色操作

创建角色

1	CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

host_name可以省略，默认为%。

给角色授权：
1
GRANT privileges ON table_name TO 'role_name'[@'host_name'];
- privileges代表权限的名称，多个权限以逗号隔开。可通过SHOW PRIVILEGES查询权限名称。
查看角色的权限：
1
SHOW GRANTS FOR 角色名

回收角色的权限：

1	REVOKE privileges ON tablename FROM 'rolename';

删除角色
1
DROP ROLE role [,role2]...
给用户赋予角色
1
GRANT role [,role2,...] TO user [,user2,...];
- 可将多个角色同时赋予多个用户，用逗号隔开即可。
激活角色
给用户赋予角色，还需要激活角色。
有两种方法：
1. 使用set default role激活角色
  例如，为下面4个用户默认激活所有已拥有的角色如下：
  1
  2
  3
  4
  5
  SET DEFAULT ROLE ALL TO
  'dev1'@'localhost',
  'read_user1'@'localhost',
  'read_user2'@'localhost',
  'rw_user1'@'localhost';
2. 将activate_all_roles_on_login设置为ON，所有角色永久激活。
  1
  SET GLOBAL activate_all_roles_on_login=ON;
  可以通过show variables like 'activate_all_roles_on_login'查看当前状态。
查看当前用户的角色
1
SELECT CURRENT_ROLE();
撤销用户的角色
1
REVOKE role FROM user;

参考资料：

https://www.bilibili.com/video/BV1iq4y1u7vj

https://www.cnblogs.com/zqifa/p/mysql-7.html

https://blog.csdn.net/ghostyusheng/article/details/84260831

文章作者: Kaka Wan Yifan

文章链接: https://kakawanyifan.com/11103

留言板