创建表

创建表前言

• 创建表前需要事先创建数据库，而创建表需要关注数据类型；
• 表中的行 row，每一行都是一条记录 record，又称为元组；
• 表中的列 column，每一列都是一个字段 field，又称属性。

数据库关系模型

在数据库设计中，实体之间的关系可以分为一对多（One-to-Many）、多对多（Many-to-Many）和一对一（One-to-One）。

这些关系模型有助于在数据库中有效地组织和管理数据，并确保数据的一致性和完整性。

一对多

使用外键

这种关系指的是一个实体（表）的一个记录可以关联到另一个实体的多个记录。例如，一个部门可以有多个员工，但一个员工只能属于一个部门。

下面是一个实际的部门表和员工表的示例，展示了一对多的关系：

部门表（Departments）：

• 部门表（Departments）的主键是 DepartmentID。

员工表（Employees）：

• 员工表（Employees）的主键是 EmployeeID，而 DepartmentID 是外键，用来与部门表中的 DepartmentID 字段建立关联，表示员工所属的部门。

在上述示例中，部门表（Departments）和员工表（Employees）之间存在一对多的关系。每个部门可以有多个员工，但每个员工只能属于一个部门。例如：

• HR部门（DepartmentID为1）有三名员工：John、Alice和Michael。
• Finance部门（DepartmentID为2）有一个员工：Bob。
• IT部门（DepartmentID为3）有一个员工：Sarah。

这个示例展示了一个典型的一对多关系，其中一个部门可以拥有多个员工，但每个员工只能属于一个部门。

多对多

引入第三张表

这种关系指的是一个实体（表）的多个记录可以关联到另一个实体的多个记录。例如，学生和课程之间的关系，一个学生可以选修多门课程，一门课程也可以被多个学生选修。

以下是一个部门表、员工表和项目表的示例，展示了多对多的关系：

部门表（Departments）：

员工表（Employees）：

项目表（Projects）：

为了建立多对多关系，我们需要引入第三张表来表示部门和项目之间的关系，这个表通常称为关联表（也称为连接表、中间表等）。

部门-项目关联表（Department_Project）：

在这个示例中，部门和项目之间是多对多的关系。一个部门可以参与多个项目，而一个项目也可以涉及多个部门。

例如：

• HR部门（DepartmentID为1）参与了两个项目（ProjectID为201和202）。
• Finance部门（DepartmentID为2）参与了一个项目（ProjectID为202）。
• IT部门（DepartmentID为3）参与了两个项目（ProjectID为201和203）。

在关联表中，DepartmentID 和 ProjectID 是外键，分别与部门表和项目表中的对应字段建立关联。

一对一

这种关系指的是一个实体（表）的一个记录只能关联到另一个实体的一个记录。例如，一个人可以有一个身份证号，而每个身份证号也只能对应一个人。

以下是一个使用部门表和员工表举例一对一关系的示例：

部门表（Departments）：

员工表（Employees）：

在这个示例中，我们假设每个员工都有一个唯一的身份证号（ID Card Number），并将其存储在一个新的表中。

身份证信息表（ID_Cards）：

在这个示例中，员工表和身份证信息表之间建立了一对一的关系。每个员工都有且只有一个对应的身份证信息，而每个身份证信息也只对应一个员工。EmployeeID 在身份证信息表中作为主键，与员工表中的 EmployeeID 关联，确保了一对一关系的建立。

这种一对一关系的情况可能出现在某些特定的场景，例如每个员工只有一个唯一的身份证号，这时候就适合使用一对一关系来建模。

数据类型

• 数据长什么样，数据需要多少空间来存放？

• 定义数据类型的目的主要根据数据类型来设置使用的空间，防止存储空间的浪费

• 参考链接：https://dev.mysql.com/doc/refman/8.0/en/data-types.html

选择正确的数据类型对于获得高性能至关重要，下面是定义数据类型的三大原则：

1. 更小的通常更好，尽量使用可正确存储数据的最小数据类型

2. 简单就好，简单数据类型的操作通常需要更少的CPU周期

3. 尽量避免NULL，包含为NULL的列，对MySQL更难优化

在MySQL中，数据类型用于定义表中每个列可以存储的数据类型。下面是一些常见的MySQL数据类型：

整数类型

• Integer Types

TINYINT

1个字节，范围：-128~127

TINYINT 是MySQL中一种整数类型，用于存储很小范围的整数数据。它通常用于表示小的整数值，如状态、标志等。下面是 TINYINT 的详细解释：

语法:

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

• M: 表示可选的显示宽度。对于 TINYINT，可以省略这个参数。对于其他整数类型，它指定了显示宽度。

• UNSIGNED: 表示无符号整数，即只存储非负整数。如果不指定 UNSIGNED，则 TINYINT 默认是有符号的，可存储负数和正数。

• ZEROFILL: 当显示宽度和 UNSIGNED 都被指定时，如果该整数为正数，并且显示的宽度超过了实际数字的宽度，MySQL 将使用零来填充左侧的空位。

示例:

CREATE TABLE example (
    status TINYINT,          -- 有符号 TINYINT
    code TINYINT UNSIGNED,   -- 无符号 TINYINT
    flags TINYINT(3),        -- 有符号 TINYINT，显示宽度为3
    quantity TINYINT(3) UNSIGNED ZEROFILL  -- 无符号 TINYINT，显示宽度为3，使用零填充
);

在上面的示例中，创建了一个名为 example 的表，其中包含了几个 TINYINT 类型的列。

TINYINT 的取值范围是 -128 到 127（如果是有符号的），或者 0 到 255（如果是无符号的）。如果你知道你的数据只会是非负整数，可以考虑使用 TINYINT UNSIGNED，这样可以将存储范围扩展到 0 到 255。这有助于节省存储空间并提高性能。

SMALLINT

2个字节，范围：-32768~32767

SMALLINT 是MySQL中的整数数据类型，用于存储较小范围的整数值。它比 TINYINT 范围更大，但仍然比较节省存储空间。以下是 SMALLINT 的详细解释：

语法:

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

• M: 表示可选的显示宽度。对于 SMALLINT，可以省略这个参数。对于其他整数类型，它指定了显示宽度。

• UNSIGNED: 表示无符号整数，即只存储非负整数。如果不指定 UNSIGNED，则 SMALLINT 默认是有符号的，可存储负数和正数。

• ZEROFILL: 当显示宽度和 UNSIGNED 都被指定时，如果该整数为正数，并且显示的宽度超过了实际数字的宽度，MySQL 将使用零来填充左侧的空位。

示例:

CREATE TABLE example (
    age SMALLINT,             -- 有符号 SMALLINT
    score SMALLINT UNSIGNED,  -- 无符号 SMALLINT
    ranking SMALLINT(4),      -- 有符号 SMALLINT，显示宽度为4
    level SMALLINT(4) UNSIGNED ZEROFILL  -- 无符号 SMALLINT，显示宽度为4，使用零填充
);

在上面的示例中，创建了一个名为 example 的表，其中包含了几个 SMALLINT 类型的列。

SMALLINT 的取值范围是 -32,768 到 32,767（如果是有符号的），或者 0 到 65,535（如果是无符号的）。与 TINYINT 类似，如果你知道你的数据只会是非负整数，可以考虑使用 SMALLINT UNSIGNED，这样可以将存储范围扩展到 0 到 65,535。这有助于节省存储空间并提高性能。

MEDIUMINT

3个字节，范围：-8388608~8388607

MEDIUMINT 是MySQL中的整数数据类型，用于存储较中等范围的整数值。它比 SMALLINT 范围更大，但仍然比 INT 更节省存储空间。以下是 MEDIUMINT 的详细解释：

语法:

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

• M: 表示可选的显示宽度。对于 MEDIUMINT，可以省略这个参数。对于其他整数类型，它指定了显示宽度。

• UNSIGNED: 表示无符号整数，即只存储非负整数。如果不指定 UNSIGNED，则 MEDIUMINT 默认是有符号的，可存储负数和正数。

• ZEROFILL: 当显示宽度和 UNSIGNED 都被指定时，如果该整数为正数，并且显示的宽度超过了实际数字的宽度，MySQL 将使用零来填充左侧的空位。

示例:

CREATE TABLE example (
    population MEDIUMINT,             -- 有符号 MEDIUMINT
    revenue MEDIUMINT UNSIGNED,       -- 无符号 MEDIUMINT
    rank MEDIUMINT(5),                -- 有符号 MEDIUMINT，显示宽度为5
    quantity MEDIUMINT(5) UNSIGNED ZEROFILL  -- 无符号 MEDIUMINT，显示宽度为5，使用零填充
);

在上面的示例中，创建了一个名为 example 的表，其中包含了几个 MEDIUMINT 类型的列。

MEDIUMINT 的取值范围是 -8,388,608 到 8,388,607（如果是有符号的），或者 0 到 16,777,215（如果是无符号的）。与 TINYINT 和 SMALLINT 类似，如果你知道你的数据只会是非负整数，可以考虑使用 MEDIUMINT UNSIGNED，这样可以将存储范围扩展到 0 到 16,777,215。这有助于节省存储空间并提高性能。

INT

4个字节，范围：-2147483648~2147483647

INT 是MySQL中的整数数据类型，用于存储整数值。它是一种通用的整数类型，适用于大多数整数存储需求。以下是 INT 的详细解释：

语法:

INT[(M)] [UNSIGNED] [ZEROFILL]

• M: 表示可选的显示宽度。对于 INT，可以省略这个参数。对于其他整数类型，它指定了显示宽度。

• UNSIGNED: 表示无符号整数，即只存储非负整数。如果不指定 UNSIGNED，则 INT 默认是有符号的，可存储负数和正数。

• ZEROFILL: 当显示宽度和 UNSIGNED 都被指定时，如果该整数为正数，并且显示的宽度超过了实际数字的宽度，MySQL 将使用零来填充左侧的空位。

示例:

CREATE TABLE example (
    quantity INT,                   -- 有符号 INT
    price INT UNSIGNED,             -- 无符号 INT
    order_number INT(10),           -- 有符号 INT，显示宽度为10
    amount INT(10) UNSIGNED ZEROFILL  -- 无符号 INT，显示宽度为10，使用零填充
);

在上面的示例中，创建了一个名为 example 的表，其中包含了几个 INT 类型的列。

INT 的取值范围是 -2,147,483,648 到 2,147,483,647（如果是有符号的），或者 0 到 4,294,967,295（如果是无符号的）。INT 是一种常用的整数类型，适用于大多数应用场景。如果你知道你的数据只会是非负整数，可以考虑使用 INT UNSIGNED，这样可以将存储范围扩展到 0 到 4,294,967,295。这有助于节省存储空间并提高性能。

BIGINT

8个字节，范围：+-9.22*10的18次方

BIGINT 是MySQL中的整数数据类型，用于存储大范围的整数值。它提供了更大的整数范围，适用于需要存储非常大整数值的情况。以下是 BIGINT 的详细解释：

语法:

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

• M: 表示可选的显示宽度。对于 BIGINT，可以省略这个参数。对于其他整数类型，它指定了显示宽度。

• UNSIGNED: 表示无符号整数，即只存储非负整数。如果不指定 UNSIGNED，则 BIGINT 默认是有符号的，可存储负数和正数。

• ZEROFILL: 当显示宽度和 UNSIGNED 都被指定时，如果该整数为正数，并且显示的宽度超过了实际数字的宽度，MySQL 将使用零来填充左侧的空位。

示例:

CREATE TABLE example (
    population BIGINT,             -- 有符号 BIGINT
    revenue BIGINT UNSIGNED,       -- 无符号 BIGINT
    customer_id BIGINT(20),        -- 有符号 BIGINT，显示宽度为20
    amount BIGINT(20) UNSIGNED ZEROFILL  -- 无符号 BIGINT，显示宽度为20，使用零填充
);

在上面的示例中，创建了一个名为 example 的表，其中包含了几个 BIGINT 类型的列。

BIGINT 的取值范围是 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,807（如果是有符号的），或者 0 到 18,446,744,073,709,551,615（如果是无符号的）。BIGINT 通常用于存储需要非常大整数值的情况，例如用户ID、计数器或其他大范围的唯一标识符。

—

m

例如 int(m) 里的 m 是表示SELECT查询结果集中的显示宽度，并不影响实际的取值范围，规定了MySQL的一些交互工具（例如MySQL命令行客户端）用来显示字符的个数。

对于存储和计算来说，Int(1)和Int(20)是相同的

修饰符 unsigned

在MySQL中，unsigned 是一个整数类型的修饰符，用于声明一个整数列是否允许存储负数。如果一个整数列被声明为 unsigned，则该列只能存储非负整数。

• 上述数据类型，如果加修饰符unsigned后，则最大值翻倍（因为只能存储正整数了）

语法

datatype UNSIGNED

这里，datatype 可以是 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT 等整数类型。

示例1

CREATE TABLE example (
    id INT,               -- 有符号 INT
    quantity INT UNSIGNED -- 无符号 INT
);

在上面的示例中，quantity 列被声明为 INT UNSIGNED，表示它只能存储非负整数。

使用 unsigned 修饰符的主要优势是它可以扩展整数类型的存储范围，因为不再需要存储负数。例如，一个 TINYINT 可以存储的范围是 0 到 255，如果声明为 TINYINT UNSIGNED，则范围是 0 到 255，而不再包括负数。

使用 unsigned 可以节省存储空间，因为它允许整数类型使用整个范围来存储非负整数，而不必将一半的范围保留给负数。但请注意，使用 unsigned 也要谨慎，确保你的数据模型和业务逻辑允许使用无符号整数。如果负数是有效的、有意义的数据值，那么不应该使用 unsigned。

示例2

tinyint unsigned的取值范围为(0~255)

是的，你说得对。TINYINT UNSIGNED 是一个无符号整数类型，其取值范围是从 0 到 255。正常的 TINYINT 可以存储范围从 -128 到 127（有符号），而 TINYINT UNSIGNED 则只能存储非负整数。

具体取值范围如下：

• 对于 TINYINT UNSIGNED，取值范围是 0 到 255。

• 对于 TINYINT，取值范围是 -128 到 127。

这样设计的好处是，无符号整数类型可以存储更大的正整数范围，因为不再需要为负数保留空间。

示例：

CREATE TABLE example (
    quantity TINYINT,              -- 有符号 TINYINT，范围 -128 到 127
    quantity_unsigned TINYINT UNSIGNED  -- 无符号 TINYINT，范围 0 到 255
);

在上述示例中，quantity 列是有符号的 TINYINT，而 quantity_unsigned 列是无符号的 TINYINT UNSIGNED，它们有不同的取值范围。

浮点数类型

• Floating-Point Types）

FLOAT

单精度浮点数，8位精度(4字节)。

FLOAT 是MySQL中的浮点数类型，用于存储近似值的数值数据。FLOAT 类型适用于需要存储小数点后的数字，但对精确性要求不高的情况。以下是 FLOAT 的详细解释：

语法:

FLOAT[(M, D)] [UNSIGNED] [ZEROFILL]

• M 表示总的数字位数，包括小数点前和小数点后的位数。可选参数，如果不指定，则根据实际需要使用默认值。

  • 精度，总位数

• D 表示小数点后的位数。可选参数，默认为 0。

  • 标度，小数位

• UNSIGNED 表示无符号浮点数，即只存储非负的浮点数。如果不指定 UNSIGNED，则 FLOAT 默认是有符号的。

• ZEROFILL 当 UNSIGNED 被指定时，如果该浮点数为正数，并且显示的宽度超过了实际数字的宽度，MySQL 将使用零来填充左侧的空位。

示例:

CREATE TABLE example (
    temperature FLOAT,            -- 有符号 FLOAT
    humidity FLOAT UNSIGNED,      -- 无符号 FLOAT
    value FLOAT(8, 2),            -- 有符号 FLOAT，总位数为8，小数点后2位
    price FLOAT(8, 2) UNSIGNED ZEROFILL  -- 无符号 FLOAT，总位数为8，小数点后2位，使用零填充
);

在上面的示例中，创建了一个名为 example 的表，其中包含了几个 FLOAT 类型的列。

FLOAT 类型适用于存储需要小数精度的数据，但它是一个近似值，而不是精确值。如果对精确性要求较高的话，可能需要考虑使用 DOUBLE 类型，因为 DOUBLE 提供了更高的精度，但相应地会占用更多的存储空间。

DOUBLE

双精度浮点数，16位精度(8字节)。

设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位

DOUBLE 是MySQL中的浮点数类型，用于存储双精度浮点数，提供了更高的精度和范围，相对于 FLOAT 类型。以下是 DOUBLE 的详细解释：

语法:

DOUBLE[(M, D)] [UNSIGNED] [ZEROFILL]

• M 表示总的数字位数，包括小数点前和小数点后的位数。可选参数，如果不指定，则根据实际需要使用默认值。

  • 精度，总位数

• D 表示小数点后的位数。可选参数，默认为 0。

  • 标度，小数位

• UNSIGNED 表示无符号浮点数，即只存储非负的浮点数。如果不指定 UNSIGNED，则 DOUBLE 默认是有符号的。

• ZEROFILL 当 UNSIGNED 被指定时，如果该浮点数为正数，并且显示的宽度超过了实际数字的宽度，MySQL 将使用零来填充左侧的空位。

示例:

CREATE TABLE example (
    temperature DOUBLE,           -- 有符号 DOUBLE
    altitude DOUBLE UNSIGNED,     -- 无符号 DOUBLE
    value DOUBLE(10, 3),          -- 有符号 DOUBLE，总位数为10，小数点后3位
    price DOUBLE(10, 3) UNSIGNED ZEROFILL  -- 无符号 DOUBLE，总位数为10，小数点后3位，使用零填充
);

在上面的示例中，创建了一个名为 example 的表，其中包含了几个 DOUBLE 类型的列。

DOUBLE 类型适用于需要更高精度和更大范围的浮点数。与 FLOAT 相比，DOUBLE 占用更多的存储空间，但提供了更高的精度，适用于对数值精度要求较高的应用场景，如科学计算或金融计算。

定点数类型

• Fixed-Point Types
• 在数据库中存放的是精确值，存为十进制；
• DECIMAL 或 NUMERIC: 用于存储精确的定点数，通常用于表示货币或其他需要精确计算的值。

DECIMAL

DECIMAL 是MySQL中的精确数值类型，用于存储精确的小数值，而不是近似值。DECIMAL 适用于对精确度要求较高的场景，如货币或其他需要准确计算的值。以下是 DECIMAL 的详细解释：

语法:

DECIMAL[(M, D)] [UNSIGNED] [ZEROFILL]

• M 表示总的数字位数，包括小数点前和小数点后的位数。必须指定，不能省略。

  • 参数m<65 是总个数

• D 表示小数点后的位数。必须指定，不能省略。

  • d<30且 d<m 是小数位

• UNSIGNED 表示无符号小数，即只存储非负的小数。如果不指定 UNSIGNED，则 DECIMAL 默认是有符号的。

• ZEROFILL 当 UNSIGNED 被指定时，如果该小数为正数，并且显示的宽度超过了实际数字的宽度，MySQL 将使用零来填充左侧的空位。

示例1:

CREATE TABLE example (
    price DECIMAL(10, 2),          -- 有符号 DECIMAL，总位数为10，小数点后2位
    amount DECIMAL(10, 3) UNSIGNED ZEROFILL  -- 无符号 DECIMAL，总位数为10，小数点后3位，使用零填充
);

在上面的示例中，创建了一个名为 example 的表，其中包含了几个 DECIMAL 类型的列。

DECIMAL 类型用于存储精确的小数值，适用于需要保持小数精确度的情况。相对于浮点数类型，DECIMAL 不涉及近似值，而是以准确的形式存储小数值。这使得 DECIMAL 在涉及精确计算的场景中更为适用，如金融领域的货币计算。

示例2:

decimal(18,9)小数点两边将各存储9个数字，一共使用9个字节：其中，小数点前的9个数字用4个字节，小数点后的9个数字用4个字节，小数点本身占1个字节

对于 DECIMAL(18, 9)，表示总共可以存储18位数字，其中小数点前有9位数字，小数点后有9位数字。每一位数字（0-9）占用 4 位二进制（4 bits），所以小数点前的 9 位数字需要占用 36 位二进制，小数点后的 9 位数字同样需要占用 36 位二进制。此外，小数点本身需要占用 1 位二进制。

因此，总共占用的二进制位数为 36 (小数点前) + 1 (小数点) + 36 (小数点后) = 73 位二进制。

由于一个字节（byte）等于8位二进制，所以 DECIMAL(18, 9) 总共占用的字节数为 73 / 8 ≈ 9 字节。

总的来说，DECIMAL(18, 9) 使用了 9 个字节来存储 18 位数字，其中小数点前后各有 9 位数字，小数点本身占 1 个字节。

NUMERIC

NUMERIC 是MySQL中的精确数值类型，与 DECIMAL 是等价的，两者可以互换使用。NUMERIC 和 DECIMAL 均用于存储精确的小数值，而不是近似值。以下是 NUMERIC 的详细解释：

语法:

NUMERIC[(M, D)] [UNSIGNED] [ZEROFILL]

• M 表示总的数字位数，包括小数点前和小数点后的位数。必须指定，不能省略。

• D 表示小数点后的位数。必须指定，不能省略。

• UNSIGNED 表示无符号小数，即只存储非负的小数。如果不指定 UNSIGNED，则 NUMERIC 默认是有符号的。

• ZEROFILL 当 UNSIGNED 被指定时，如果该小数为正数，并且显示的宽度超过了实际数字的宽度，MySQL 将使用零来填充左侧的空位。

示例:

CREATE TABLE example (
    price NUMERIC(10, 2),          -- 有符号 NUMERIC，总位数为10，小数点后2位
    amount NUMERIC(10, 3) UNSIGNED ZEROFILL  -- 无符号 NUMERIC，总位数为10，小数点后3位，使用零填充
);

在上面的示例中，创建了一个名为 example 的表，其中包含了几个 NUMERIC 类型的列。

与 DECIMAL 类型一样，NUMERIC 用于存储精确的小数值。这两个类型可以在实践中互换使用，它们通常用于需要高精度的场景，如金融领域的货币计算。选择使用 NUMERIC 或 DECIMAL 取决于个人或组织的偏好，它们在功能上是等价的。

总结

浮点类型在存储同样范围的值时，通常比decimal使用更少的空间。float使用4个字节存储。double占 用8个字节因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用bigint代替decimal

字符串类型

• String Types

CHAR

固定长度的字符串，最多255个字符。

CHAR 是MySQL中的字符串数据类型，用于存储固定长度的字符序列。与 VARCHAR 不同，CHAR 在存储时会用空格字符填充至指定长度，因此它适用于存储长度固定的字符串。以下是 CHAR 的详细解释：

语法:

CHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

• M 表示字符串的固定长度，范围是 0 到 255。

• CHARACTER SET charset_name 可选项，表示字符集的名称，指定了该 CHAR 列中存储的字符的编码方式。

• COLLATE collation_name 可选项，指定了字符集的排序规则。

示例:

CREATE TABLE example (
    code CHAR(10),                     -- 固定长度为10的CHAR
    description CHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci  -- 固定长度为50的CHAR，使用UTF-8字符集和排序规则
);

在上面的示例中，创建了一个名为 example 的表，其中包含了两个 CHAR 类型的列。

CHAR 类型适用于存储长度固定的字符串，例如存储具有恒定长度的国家/地区代码、产品编号等信息。需要注意的是，由于 CHAR 存储时会填充空格，因此它可能浪费存储空间，特别是当存储的字符串长度不足指定长度时。

与 CHAR 不同，VARCHAR 是一种可变长度字符串类型，它只存储实际的字符，并没有填充。选择使用 CHAR 还是 VARCHAR 取决于具体的需求和存储效率的考虑。

VARCHAR

变长字符串，最多65535个字符。

VARCHAR 是MySQL中的可变长度字符串数据类型，用于存储可变长度的字符序列。相对于 CHAR 类型，VARCHAR 不会在存储时填充空格，因此它更适用于存储长度不固定的字符串。以下是 VARCHAR 的详细解释：

语法:

VARCHAR(M) [CHARACTER SET charset_name] [COLLATE collation_name]

• M 表示字符串的最大长度，范围是 0 到 65535。

• CHARACTER SET charset_name 可选项，表示字符集的名称，指定了该 VARCHAR 列中存储的字符的编码方式。

• COLLATE collation_name 可选项，指定了字符集的排序规则。

示例:

CREATE TABLE example (
    name VARCHAR(50),                  -- 最大长度为50的VARCHAR
    address VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci  -- 最大长度为255的VARCHAR，使用UTF-8字符集和排序规则
);

在上面的示例中，创建了一个名为 example 的表，其中包含了两个 VARCHAR 类型的列。

VARCHAR 类型适用于存储长度不固定的字符串，例如存储人名、地址等信息。相对于 CHAR 类型，VARCHAR 在存储时不会填充空格，因此更节省存储空间，特别是当存储的字符串长度变化较大时。

需要注意的是，由于 VARCHAR 是可变长度的，它的实际存储长度是存储的字符串长度加上一个额外的字节用于记录字符串的长度信息。

CHAR 和 VARCHAR 对比

参考链接：https://dev.mysql.com/doc/refman/8.0/en/char.html

1. char(n) 若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉，所以char类型存储的字符串末尾不能有空格，varchar不限于此
2. char(n) 固定长度，char(4)不管是存入几个字符，都将占用4个字节，varchar是存入的实际字符数+1个字节（n< n>255)，所以varchar(4),存入3个字符将占用4个字节
3. char类型的字符串检索速度要比varchar类型的快，因为磁盘空间占用相对规整

文本类型

TEXT

• 可变长度，最多65535个字符，用于存储较长的文本数据。

TEXT 是MySQL中的文本数据类型，用于存储可变长度的字符串，通常用于存储大段文本数据，例如文章内容、评论等。以下是 TEXT 的详细解释：

语法:

TEXT [CHARACTER SET charset_name] [COLLATE collation_name]

• CHARACTER SET charset_name 可选项，表示字符集的名称，指定了该 TEXT 列中存储的字符的编码方式。

• COLLATE collation_name 可选项，指定了字符集的排序规则。

示例:

CREATE TABLE example (
    description TEXT,                           -- 存储可变长度文本的TEXT列
    content TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci  -- 使用UTF-8字符集和排序规则的TEXT列
);

在上面的示例中，创建了一个名为 example 的表，其中包含了两个 TEXT 类型的列。

TEXT 类型适用于存储大段的文本数据，长度不限制。与 VARCHAR 不同，TEXT 类型通常用于存储超过 VARCHAR 最大长度限制的文本。

需要注意的是，由于 TEXT 是可变长度的，它的实际存储长度是存储的字符串长度加上一个额外的字节用于记录字符串的长度信息。在使用 TEXT 类型时，需要根据实际需求来选择是否使用字符集和排序规则。

TINYTEXT

可变长度，最多255个字符

TINYTEXT 是MySQL中的文本数据类型，用于存储可变长度的字符串，通常用于存储较短的文本数据。以下是 TINYTEXT 的详细解释：

语法:

TINYTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

• CHARACTER SET charset_name 可选项，表示字符集的名称，指定了该 TINYTEXT 列中存储的字符的编码方式。

• COLLATE collation_name 可选项，指定了字符集的排序规则。

示例:

CREATE TABLE example (
    description TINYTEXT,                            -- 存储可变长度文本的TINYTEXT列
    content TINYTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci  -- 使用UTF-8字符集和排序规则的TINYTEXT列
);

在上面的示例中，创建了一个名为 example 的表，其中包含了两个 TINYTEXT 类型的列。

TINYTEXT 类型适用于存储较短的文本数据，其最大存储长度为 255 个字符。与 TEXT 不同，TINYTEXT 存储的文本长度有限制，适用于存储相对较小的文本数据，例如短描述、标签等。

需要注意的是，由于 TINYTEXT 是可变长度的，它的实际存储长度是存储的字符串长度加上一个额外的字节用于记录字符串的长度信息。在使用 TINYTEXT 类型时，同样可以选择是否使用字符集和排序规则。

MEDIUMTEXT

可变长度，最多2的24次方-1个字符。

MEDIUMTEXT 是MySQL中的文本数据类型，用于存储可变长度的字符串，通常用于存储中等长度的文本数据。以下是 MEDIUMTEXT 的详细解释：

语法:

MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

• CHARACTER SET charset_name 可选项，表示字符集的名称，指定了该 MEDIUMTEXT 列中存储的字符的编码方式。

• COLLATE collation_name 可选项，指定了字符集的排序规则。

示例:

CREATE TABLE example (
    description MEDIUMTEXT,                             -- 存储可变长度文本的MEDIUMTEXT列
    content MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci  -- 使用UTF-8字符集和排序规则的MEDIUMTEXT列
);

在上面的示例中，创建了一个名为 example 的表，其中包含了两个 MEDIUMTEXT 类型的列。

MEDIUMTEXT 类型适用于存储中等长度的文本数据，其最大存储长度为 16,777,215 个字符。与 TINYTEXT 和 TEXT 不同，MEDIUMTEXT 可以用于存储更大的文本数据，但相对于 LONGTEXT 来说，它的存储空间更为有限。

需要注意的是，由于 MEDIUMTEXT 是可变长度的，它的实际存储长度是存储的字符串长度加上一个额外的字节用于记录字符串的长度信息。在使用 MEDIUMTEXT 类型时，同样可以选择是否使用字符集和排序规则。

LONGTEXT

可变长度，最多2的32次方-1个字符。

LONGTEXT 是MySQL中的文本数据类型，用于存储可变长度的字符串，通常用于存储非常大的文本数据。以下是 LONGTEXT 的详细解释：

语法:

LONGTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

• CHARACTER SET charset_name 可选项，表示字符集的名称，指定了该 LONGTEXT 列中存储的字符的编码方式。

• COLLATE collation_name 可选项，指定了字符集的排序规则。

示例:

CREATE TABLE example (
    description LONGTEXT,                             -- 存储可变长度文本的LONGTEXT列
    content LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci  -- 使用UTF-8字符集和排序规则的LONGTEXT列
);

在上面的示例中，创建了一个名为 example 的表，其中包含了两个 LONGTEXT 类型的列。

LONGTEXT 类型适用于存储非常大的文本数据，其最大存储长度为 4,294,967,295 个字符。它通常用于存储文章内容、大段文本、或其他需要大量文本存储的场景。

需要注意的是，由于 LONGTEXT 是可变长度的，它的实际存储长度是存储的字符串长度加上一个额外的字节用于记录字符串的长度信息。在使用 LONGTEXT 类型时，同样可以选择是否使用字符集和排序规则。

VARCHAR 和 TEXT 对比

1. varchar可指定n，text不能指定，内部存储varchar是存入的实际字符数+1个字节（n< n>255)，text是实际字符数+2个字节。
2. text类型不能有默认值
3. varchar可直接创建索引，text创建索引要指定前多少个字符。varchar查询速度快于text数据类型

二进制类型

BINARY

固定长度，可存二进制或字符，长度为0-M字节。

BINARY 是MySQL中的二进制数据类型，用于存储固定长度的二进制数据。这种类型主要用于存储包含字节数据的列，例如图像、音频或其他二进制格式的数据。以下是 BINARY 的详细解释：

语法:

BINARY(N)

• N 表示二进制数据的固定长度，即存储的字节数。

示例:

CREATE TABLE example (
    image_data BINARY(1024)  -- 存储固定长度为1024字节的二进制数据
);

在上面的示例中，创建了一个名为 example 的表，其中包含了一个 BINARY 类型的列。

BINARY 类型适用于存储具有固定长度的二进制数据，例如文件的二进制内容。与 VARBINARY 不同，BINARY 在存储时不会用零字节填充到指定长度，而是截断或补齐输入的数据，确保存储的数据是固定长度的。

需要注意的是，BINARY 类型的存储空间是固定的，因此如果存储的数据长度小于指定的长度，MySQL 会在存储时右侧补零。如果长度大于指定的长度，MySQL 会截断数据。

如果需要存储可变长度的二进制数据，可以考虑使用 VARBINARY 类型。

VARBINARY

可变长度，可存二进制或字符，允许长度为0-M字节。

VARBINARY 是MySQL中的二进制数据类型，用于存储可变长度的二进制数据。与 BINARY 类型不同，VARBINARY 不要求存储的数据具有固定长度，而是根据实际输入的数据长度来存储。以下是 VARBINARY 的详细解释：

语法:

VARBINARY(N)

• N 表示二进制数据的最大长度，即存储的字节数。如果未指定 N，则可以存储最大长度为 65,535 字节的数据。

示例:

CREATE TABLE example (
    file_data VARBINARY(4096)  -- 存储可变长度最大为4096字节的二进制数据
);

在上面的示例中，创建了一个名为 example 的表，其中包含了一个 VARBINARY 类型的列。

VARBINARY 类型适用于存储可变长度的二进制数据，例如图像、音频或其他二进制格式的数据。与 BINARY 类型不同，VARBINARY 在存储时不会用零字节填充到指定长度，而是根据实际输入的数据长度来存储。

需要注意的是，VARBINARY 类型的存储空间是可变的，因此存储的数据长度不会受到固定长度的限制。如果存储的数据长度超过了指定的最大长度，MySQL 将会截断数据。

BLOB

• BLOB和text存储方式不同，TEXT以文本方式存储，英文存储区分大小写，而Blob以二进制方式存储，不分大小写

• BLOB存储的数据只能整体读出

• TEXT可以指定字符集，BLOB不用指定字符集

SET 是MySQL中的一种集合数据类型，用于存储零个或多个从属于一个预定义的集合的值。SET 类型适用于需要存储多选值的场景。以下是 SET 的详细解释：

语法:

SET('value1', 'value2', ..., 'valueN') [CHARACTER SET charset_name] [COLLATE collation_name]

• value1, value2, …, valueN 是预定义集合中允许的值。

• CHARACTER SET charset_name 可选项，表示字符集的名称，指定了该 SET 列中存储的字符的编码方式。

• COLLATE collation_name 可选项，指定了字符集的排序规则。

示例:

CREATE TABLE example (
    roles SET('Admin', 'User', 'Editor')  -- 定义一个SET列，允许包含 'Admin', 'User', 'Editor' 中的零个或多个值
);

在上面的示例中，创建了一个名为 example 的表，其中包含了一个 SET 类型的列。

SET 类型适用于需要存储多个可能取值的场景。在实际使用时，应选择与列中可能的值相关的离散选项。与 ENUM 类型不同，SET 类型的列可以包含多个值，这些值是用逗号分隔的。

需要注意的是，SET 类型虽然提供了一种存储多选值的方式，但也有一些限制，包括不支持添加新值或删除已有值。在某些情况下，使用关联表和外键来实现多对多关系可能更为灵活。

枚举类型

ENUM

• 指一一列举，例如表示星期的SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY、SATURDAY就是一个枚举。
• 枚举是一个被命名的整型常数的集合

ENUM 是MySQL中的一种枚举数据类型，用于定义一组可能的取值，列举了一个列可能包含的不同选项。ENUM 类型是一种用于存储单选值的有限集合的数据类型。以下是 ENUM 的详细解释：

语法:

ENUM('value1', 'value2', ..., 'valueN')

• value1, value2, …, valueN 是允许的枚举值。

示例:

CREATE TABLE example (
    status ENUM('Active', 'Inactive', 'Pending')  -- 定义一个ENUM列，只允许包含 'Active', 'Inactive', 'Pending' 中的一个值
);

在上面的示例中，创建了一个名为 example 的表，其中包含了一个 ENUM 类型的列。

ENUM 类型适用于列举一组有限的可能取值的情况。在实际使用时，应选择与列中可能的值相关的离散选项。这种类型的列只能包含列举的选项之一，不接受其他值。

需要注意的是，ENUM 类型虽然可以提供一种有限的选项，但在某些情况下可能不适用于存储动态变化的选项，因为在更改 ENUM 的选项时可能需要修改表结构。对于需要动态选项的情况，可能更适合使用关联表和外键来实现。

集合类型

SET

SET 是MySQL中的一种集合数据类型，用于存储零个或多个从属于一个预定义的集合的值。SET 类型适用于需要存储多选值的场景。以下是 SET 的详细解释：

语法:

SET('value1', 'value2', ..., 'valueN') [CHARACTER SET charset_name] [COLLATE collation_name]

• value1, value2, …, valueN 是预定义集合中允许的值。

• CHARACTER SET charset_name 可选项，表示字符集的名称，指定了该 SET 列中存储的字符的编码方式。

• COLLATE collation_name 可选项，指定了字符集的排序规则。

示例:

CREATE TABLE example (
    roles SET('Admin', 'User', 'Editor')  -- 定义一个SET列，允许包含 'Admin', 'User', 'Editor' 中的零个或多个值
);

在上面的示例中，创建了一个名为 example 的表，其中包含了一个 SET 类型的列。

SET 类型适用于需要存储多个可能取值的场景。在实际使用时，应选择与列中可能的值相关的离散选项。与 ENUM 类型不同，SET 类型的列可以包含多个值，这些值是用逗号分隔的。

需要注意的是，SET 类型虽然提供了一种存储多选值的方式，但也有一些限制，包括不支持添加新值或删除已有值。在某些情况下，使用关联表和外键来实现多对多关系可能更为灵活。

日期与时间类型

Date and Time Types

DATE

日期类型，2008-12-2。

DATE 是MySQL中的日期数据类型，用于存储日期值。DATE 类型存储的值包括年、月、日，不包含具体的时间。以下是 DATE 的详细解释：

语法:

DATE

示例:

CREATE TABLE example (
    birth_date DATE  -- 用于存储日期的DATE列
);

在上面的示例中，创建了一个名为 example 的表，其中包含了一个 DATE 类型的列。

DATE 类型适用于存储日期信息，例如出生日期、事件日期等。存储的日期值采用 ‘YYYY-MM-DD’ 的格式，其中 YYYY 表示年，MM 表示月，DD 表示日。

插入数据:

INSERT INTO example (birth_date) VALUES ('1990-05-15');  -- 插入日期值

查询数据:

SELECT * FROM example WHERE birth_date = '1990-05-15';  -- 查询出生日期为1990-05-15的行

需要注意的是，DATE 类型只存储日期信息，不包含具体的时间。如果需要同时存储日期和时间信息，可以考虑使用 DATETIME 或 TIMESTAMP 类型。

TIME

时间类型，12:25:36。

TIME 是MySQL中的时间数据类型，用于存储时间值。TIME 类型存储的值包括时、分、秒，不包含日期信息。以下是 TIME 的详细解释：

语法:

TIME

示例:

CREATE TABLE example (
    meeting_time TIME  -- 用于存储时间的TIME列
);

在上面的示例中，创建了一个名为 example 的表，其中包含了一个 TIME 类型的列。

TIME 类型适用于存储时间信息，例如会议时间、持续时间等。存储的时间值采用 ‘HH:MM:SS’ 的格式，其中 HH 表示时，MM 表示分，SS 表示秒。

插入数据:

INSERT INTO example (meeting_time) VALUES ('14:30:00');  -- 插入时间值

查询数据:

SELECT * FROM example WHERE meeting_time = '14:30:00';  -- 查询会议时间为14:30:00的行

需要注意的是，TIME 类型只存储时间信息，不包含日期。如果需要同时存储日期和时间信息，可以考虑使用 DATETIME 或 TIMESTAMP 类型。

DATETIME

日期和时间类型，2008-12-2 22:06:44。

DATETIME 是MySQL中的日期时间数据类型，用于存储包含日期和时间的值。DATETIME 类型存储的值包括年、月、日、时、分、秒。以下是 DATETIME 的详细解释：

语法:

DATETIME

示例:

CREATE TABLE example (
    event_datetime DATETIME  -- 用于存储日期和时间的DATETIME列
);

在上面的示例中，创建了一个名为 example 的表，其中包含了一个 DATETIME 类型的列。

DATETIME 类型适用于需要同时存储日期和时间信息的场景，例如事件发生时间、数据库记录的创建时间等。存储的日期时间值采用 ‘YYYY-MM-DD HH:MM:SS’ 的格式。

插入数据:

INSERT INTO example (event_datetime) VALUES ('2022-02-04 15:30:00');  -- 插入日期时间值

查询数据:

SELECT * FROM example WHERE event_datetime = '2022-02-04 15:30:00';  -- 查询事件发生时间为2022-02-04 15:30:00的行

需要注意的是，DATETIME 类型包含日期和时间信息，但不包含时区信息。如果需要考虑时区，可以考虑使用 TIMESTAMP 类型。

TIMESTAMP

时间戳，通常用于记录最后修改的时间。

自动存储记录修改时间，timestamp字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间

TIMESTAMP 是MySQL中的日期时间数据类型，用于存储包含日期和时间的值，与 DATETIME 类型类似。TIMESTAMP 类型存储的值包括年、月、日、时、分、秒。以下是 TIMESTAMP 的详细解释：

语法:

TIMESTAMP

示例:

CREATE TABLE example (
    record_timestamp TIMESTAMP  -- 用于存储日期和时间的TIMESTAMP列
);

在上面的示例中，创建了一个名为 example 的表，其中包含了一个 TIMESTAMP 类型的列。

与 DATETIME 不同，TIMESTAMP 类型的值存储时会受到时区的影响。MySQL会将存储的时间值从当前连接的时区转换为协调世界时 (UTC) 存储，并在检索时将其转换回连接时区。

插入数据:

INSERT INTO example (record_timestamp) VALUES ('2022-02-04 15:30:00');  -- 插入日期时间值

查询数据:

SELECT * FROM example WHERE record_timestamp = '2022-02-04 15:30:00';  -- 查询记录时间为2022-02-04 15:30:00的行

需要注意的是，TIMESTAMP 类型包含日期和时间信息，并且受到时区的影响。如果不需要考虑时区，可以考虑使用 DATETIME 类型。

YEAR

YEAR 是MySQL中的年份数据类型，用于存储年份的值。YEAR 类型存储的值为四位数的年份。以下是 YEAR 的详细解释：

语法:

YEAR

示例:

CREATE TABLE example (
    graduation_year YEAR  -- 用于存储年份的YEAR列
);

在上面的示例中，创建了一个名为 example 的表，其中包含了一个 YEAR 类型的列。

YEAR 类型适用于只需存储年份信息的场景，例如毕业年份、活动年份等。

插入数据:

INSERT INTO example (graduation_year) VALUES (2022);  -- 插入年份值

查询数据:

SELECT * FROM example WHERE graduation_year = 2022;  -- 查询毕业年份为2022的行

需要注意的是，YEAR 类型只能存储四位数的年份，不包含月和日信息。如果需要同时存储日期和时间信息，可以考虑使用 DATETIME、TIMESTAMP 或其他适当的类型。

YEAR(2)

18

在MySQL中，YEAR(2) 是对 YEAR 数据类型的显示宽度的一种限制。YEAR 数据类型用于存储年份信息，通常为四位数。但是，通过使用 YEAR(2)，你可以指定显示宽度，即只显示年份的后两位数字。

示例:

CREATE TABLE example (
    graduation_year YEAR(2)  -- 使用YEAR(2)存储年份的后两位数字
);

在上面的示例中，创建了一个名为 example 的表，其中包含了一个 YEAR(2) 类型的列。

插入数据:

INSERT INTO example (graduation_year) VALUES (22);  -- 插入年份值的后两位数字

查询数据:

SELECT * FROM example WHERE graduation_year = 22;  -- 查询毕业年份的后两位数字为22的行

需要注意的是，YEAR(2) 只影响显示宽度，不会改变存储的数据。如果插入的年份为四位数，则存储的仍然是完整的四位数年份。 YEAR(2) 主要用于在查询结果中以指定的显示宽度显示年份。

YEAR(4)

2018

在MySQL中，YEAR(4) 也是对 YEAR 数据类型的显示宽度的一种限制。与 YEAR(2) 类似，YEAR(4) 可以用来指定显示宽度，但它将年份显示为四位数字。实际上，YEAR 数据类型默认显示为四位数字，因此指定 (4) 并不会产生实际的变化。

示例:

CREATE TABLE example (
    graduation_year YEAR(4)  -- 使用YEAR(4)存储年份的四位数字
);

在上面的示例中，创建了一个名为 example 的表，其中包含了一个 YEAR(4) 类型的列。

插入数据:

INSERT INTO example (graduation_year) VALUES (2022);  -- 插入四位数年份值

查询数据:

SELECT * FROM example WHERE graduation_year = 2022;  -- 查询毕业年份为2022的行

需要注意的是，YEAR(4) 主要用于在查询结果中以指定的显示宽度显示年份。在存储层面，YEAR 类型始终存储四位数的年份。

布尔型

Boolean Type

布尔型是TINYINT(1)的同义词。zero值被视为假，非zero值视为真。

BOOLEAN

在MySQL中，并没有直接的 BOOLEAN 数据类型。通常，布尔值可以通过使用 TINYINT(1) 类型来表示，其中 1 表示该字段只能存储一个字节的数据，而且被限制为取值范围为 0 或 1。在MySQL中，通常使用 0 表示 FALSE，使用 1 表示 TRUE。

以下是使用 TINYINT(1) 来表示布尔值的示例：

创建表:

CREATE TABLE example (
    is_active TINYINT(1)  -- 用于表示布尔值的TINYINT列
);

插入数据:

INSERT INTO example (is_active) VALUES (1);  -- 插入TRUE
INSERT INTO example (is_active) VALUES (0);  -- 插入FALSE

查询数据:

SELECT * FROM example WHERE is_active = 1;  -- 查询所有is_active为TRUE的行
SELECT * FROM example WHERE is_active = 0;  -- 查询所有is_active为FALSE的行

虽然 BOOLEAN 不是MySQL的原生数据类型，但在实践中，TINYINT(1) 常常被用来表示布尔值，而且在许多情况下，数据库的客户端库和应用程序会将 TINYINT(1) 自动映射为布尔值。

BOOL

在MySQL中，BOOL 也不是官方定义的原生数据类型。通常，布尔值可以通过使用 TINYINT(1) 类型来表示，其中 1 表示该字段只能存储一个字节的数据，被限制为取值范围为 0 或 1。在MySQL中，通常使用 0 表示 FALSE，使用 1 表示 TRUE。

以下是使用 TINYINT(1) 来表示布尔值的示例：

创建表:

CREATE TABLE example (
    is_active TINYINT(1)  -- 用于表示布尔值的TINYINT列
);

插入数据:

INSERT INTO example (is_active) VALUES (1);  -- 插入TRUE
INSERT INTO example (is_active) VALUES (0);  -- 插入FALSE

查询数据:

SELECT * FROM example WHERE is_active = 1;  -- 查询所有is_active为TRUE的行
SELECT * FROM example WHERE is_active = 0;  -- 查询所有is_active为FALSE的行

尽管 BOOL 不是MySQL的原生数据类型，但在实践中，TINYINT(1) 常常被用来表示布尔值，而且在许多情况下，数据库的客户端库和应用程序会将 TINYINT(1) 自动映射为布尔值。

修饰符

在MySQL中，表的列可以使用各种修饰符进行定义。

这些修饰符可以根据实际需要进行组合使用。请注意，某些修饰符可能在特定的数据库管理系统中具有特定的行为，例如 AUTO_INCREMENT 在MySQL中表示自增列，而在其他数据库中可能有不同的表示。

• AUTO_INCREMENT、UNSIGNED 仅适用于数值型，其他适用于所有类型。

在MySQL中，表的列可以使用各种修饰符进行定义。以下是一些常见的列修饰符：

NULL

数据列可包含NULL值，默认值

指定列是否允许包含NULL值。如果指定了 NULL，则该列可以包含NULL值；如果没有指定 NULL，则该列不允许包含NULL值。

column_name INT NULL;  -- 允许包含NULL值的INT列

NULL 是一个特殊的值，表示一个数据项未知或不适用。在数据库中，NULL 常常用于表示缺失的数据或未知的值。以下是关于 NULL 的一些详细解释：

1. NULL 用法：

   • 在数据库中，NULL 用于表示缺失的、未知的、或不适用的值。
   • 在创建表时，可以使用 NULL 关键字来声明某列允许包含 NULL 值。

   CREATE TABLE example (
       column1 INT,           -- 允许包含NULL值的INT列
       column2 VARCHAR(255)    -- 默认不允许包含NULL值的VARCHAR列
   );

2. 插入和更新数据：

   • 可以通过在 INSERT 或 UPDATE 语句中使用 NULL 来插入或更新列的值为 NULL。

   INSERT INTO example (column1, column2) VALUES (NULL, 'Some Value');
   UPDATE example SET column1 = NULL WHERE ...;

3. 对比和检查 NULL：

   • 使用 IS NULL 和 IS NOT NULL 来检查列是否为 NULL。
   • 不要使用等号 = 来检查 NULL，因为在数据库中 NULL 与任何其他值的比较都会返回未知。

   SELECT * FROM example WHERE column1 IS NULL;        -- 查找 column1 为 NULL 的行
   SELECT * FROM example WHERE column1 IS NOT NULL;    -- 查找 column1 不为 NULL 的行

4. 与其他值的运算：

   • 与 NULL 进行任何数学或逻辑运算的结果都是 NULL。

   SELECT NULL + 5;       -- 结果为 NULL
   SELECT NULL = 5;       -- 结果为 NULL

5. 替代值（COALESCE 和 IFNULL）：

   • 使用 COALESCE 或 IFNULL 函数可以将 NULL 替换为其他默认值。

   SELECT COALESCE(column1, 0) FROM example;        -- 将 column1 中的 NULL 替换为 0
   SELECT IFNULL(column2, 'Unknown') FROM example;  -- 将 column2 中的 NULL 替换为 'Unknown'

使用 NULL 的关键在于理解它是一个特殊的标记，表示缺失或未知的值。在数据库设计中，合理使用 NULL 可以提高数据的灵活性和可读性。

NOT NULL

指定列不允许包含NULL值。

column_name VARCHAR(255) NOT NULL;  -- 不允许包含NULL值的VARCHAR列

NOT NULL 是用于指定数据库表中列的约束，表示该列不允许包含 NULL 值。通过使用 NOT NULL 约束，可以确保列中的数据始终存在且不缺失。以下是关于 NOT NULL 的详细解释：

1. 在创建表时使用 NOT NULL：

   • 在创建表时，可以使用 NOT NULL 修饰符来确保列不允许包含 NULL 值。

   CREATE TABLE example (
       column1 INT NOT NULL,           -- 不允许包含NULL值的INT列
       column2 VARCHAR(255) NOT NULL   -- 不允许包含NULL值的VARCHAR列
   );

2. 在修改表结构时使用 NOT NULL：

   • 可以使用 ALTER TABLE 语句来在已存在的表中添加 NOT NULL 约束。

   ALTER TABLE example
   MODIFY column1 INT NOT NULL;

3. 插入和更新数据时的要求：

   • 当使用 NOT NULL 约束时，在插入或更新数据时必须为这些列提供非 NULL 的值。

   INSERT INTO example (column1, column2) VALUES (42, 'Some Value');  -- 正确，提供了非NULL值
   INSERT INTO example (column1, column2) VALUES (NULL, 'Another Value');  -- 错误，违反了NOT NULL约束

4. 检查 NOT NULL 约束：

   • 使用 DESCRIBE 或 SHOW CREATE TABLE 命令可以查看表的结构，了解哪些列具有 NOT NULL 约束。

   DESCRIBE example;  -- 查看表的结构，了解列是否有NOT NULL约束

5. 与 NULL 的对比：

   • 使用 IS NULL 和 IS NOT NULL 来检查列是否为 NULL。

   SELECT * FROM example WHERE column1 IS NULL;        -- 查找 column1 为 NULL 的行
   SELECT * FROM example WHERE column1 IS NOT NULL;    -- 查找 column1 不为 NULL 的行

NOT NULL 约束用于确保某列中的数据始终存在，有助于提高数据的完整性和可靠性。在设计数据库表时，根据业务需求，合理使用 NOT NULL 约束以确保数据的一致性。

DEFAULT

为列指定默认值，当插入新记录时，如果没有提供该列的值，将使用默认值。

column_name INT DEFAULT 0;  -- 设置默认值为0的INT列

DEFAULT 是用于指定列的默认值的关键字。当在插入新记录时，如果未提供该列的值，将使用默认值。以下是关于 DEFAULT 的详细解释：

1. 在创建表时使用 DEFAULT：

   • 在创建表时，可以使用 DEFAULT 关键字来为列指定默认值。

   CREATE TABLE example (
       column1 INT DEFAULT 0,                -- 设置默认值为0的INT列
       column2 VARCHAR(255) DEFAULT 'N/A'   -- 设置默认值为'N/A'的VARCHAR列
   );

2. 在修改表结构时使用 DEFAULT：

   • 可以使用 ALTER TABLE 语句来在已存在的表中添加或修改默认值。

   ALTER TABLE example
   ALTER COLUMN column1 SET DEFAULT 42;  -- 修改column1的默认值为42

3. 插入数据时的默认值：

   • 当插入新记录时，如果未提供带有默认值的列的值，将使用默认值。

   INSERT INTO example (column1, column2) VALUES (NULL, 'Some Value');  -- 使用column1的默认值，提供了column2的值

4. 与 NULL 的区别：

   • 未提供值且未设置默认值的列将被插入为 NULL，而提供了默认值的列在未提供值时将使用默认值。

   INSERT INTO example (column1) VALUES (NULL);  -- column1插入为NULL
   INSERT INTO example (column2) VALUES (NULL);  -- column2使用默认值

5. 检查默认值：

   • 使用 DESCRIBE 或 SHOW CREATE TABLE 命令可以查看表的结构，了解哪些列具有默认值。

   DESCRIBE example;  -- 查看表的结构，了解列的默认值

使用 DEFAULT 关键字可以确保在插入新记录时，如果未提供某列的值，将使用预先定义的默认值。这对于确保数据的完整性和提供有意义的缺省值是很有用的。

DEFAULT CURRENT_TIMESTAMP

用于在插入新记录时，自动设置列为当前时间戳。

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;  -- 创建时间戳列，默认为当前时间

DEFAULT CURRENT_TIMESTAMP 是用于在创建表时为列指定默认值的关键字，用于表示该列的默认值为当前的时间戳。以下是关于 DEFAULT CURRENT_TIMESTAMP 的详细解释：

1. 在创建表时使用 DEFAULT CURRENT_TIMESTAMP：

   • 在创建表时，可以使用 DEFAULT CURRENT_TIMESTAMP 来为列指定默认值，该默认值为当前的时间戳。

   CREATE TABLE example (
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 创建时间戳列，使用当前时间作为默认值
       updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- 更新时间戳列，使用当前时间作为默认值和更新值
   );

2. 插入数据时的默认值：

   • 当插入新记录时，如果未提供带有 DEFAULT CURRENT_TIMESTAMP 的列的值，将使用当前的时间戳作为默认值。

   INSERT INTO example (created_at) VALUES (DEFAULT);  -- 使用当前时间戳作为created_at的默认值

3. 与 NULL 的区别：

   • DEFAULT CURRENT_TIMESTAMP 提供了一个默认值，而不是 NULL。即使未提供该列的值，也会使用当前时间戳。

   INSERT INTO example () VALUES ();  -- created_at将使用当前时间戳作为默认值

4. 更新时的默认值：

   • 对于带有 ON UPDATE CURRENT_TIMESTAMP 的列，在更新记录时，将自动更新为当前的时间戳。

   UPDATE example SET updated_at = DEFAULT;  -- 更新updated_at列，将使用当前时间戳

使用 DEFAULT CURRENT_TIMESTAMP 可以方便地记录数据的创建时间或最后更新时间。这对于维护时间戳列非常有用，而无需在每次插入或更新时手动提供时间戳值。

PRIMARY KEY

主键，所有记录中此字段的值不能重复，且不能为NULL

用于定义主键，确保表中的每行都有唯一标识。

id INT PRIMARY KEY;  -- 定义一个主键列

PRIMARY KEY 是用于定义表中主键的关键字。主键是表中的一列或一组列，其值用于唯一标识每一行。以下是关于 PRIMARY KEY 的详细解释：

1. 在创建表时使用 PRIMARY KEY：

   • 在创建表时，可以使用 PRIMARY KEY 关键字来定义主键。

   CREATE TABLE example (
       id INT PRIMARY KEY,            -- 定义整数列 id 为主键
       username VARCHAR(255) PRIMARY KEY  -- 定义字符串列 username 为主键
   );

   • 主键可以包含多个列，这种情况下被称为复合主键。

   CREATE TABLE example (
       id INT,
       username VARCHAR(255),
       PRIMARY KEY (id, username)  -- 定义由 id 和 username 组成的复合主键
   );

2. 在修改表结构时使用 PRIMARY KEY：

   • 可以使用 ALTER TABLE 语句在已存在的表中添加或修改主键。

   ALTER TABLE example
   ADD PRIMARY KEY (id);  -- 向表中添加 id 列为主键

3. 主键的特性：

   • 主键列的值必须是唯一的，不能重复。
   • 主键列的值不能为 NULL，即主键列不允许包含 NULL 值。
   • 表中只能有一个主键，但主键可以包含多个列（复合主键）。

4. 自动增长的主键列：

   • 可以将主键列定义为自动增长（AUTO_INCREMENT），在插入新记录时，系统会自动生成唯一的主键值。

   CREATE TABLE example (
       id INT PRIMARY KEY AUTO_INCREMENT,  -- 定义自动增长的整数列 id 为主键
       name VARCHAR(255)
   );

   • 在某些数据库系统中，关键字 SERIAL 也可用于定义自动增长的整数主键列。

   CREATE TABLE example (
       id SERIAL PRIMARY KEY,  -- 定义自动增长的整数列 id 为主键
       name VARCHAR(255)
   );

5. 删除主键：

   • 可以使用 DROP PRIMARY KEY 来删除表中的主键。

   ALTER TABLE example
   DROP PRIMARY KEY;  -- 删除表中的主键

主键是数据库表中的一项重要约束，用于确保每一行都有一个唯一标识。它对于数据库的查询性能和数据完整性非常重要。在设计数据库表时，合理选择主键非常关键。

主键详解

主键（Primary Key）在关系型数据库中是一种用于唯一标识表中每条记录的机制。

主键的主要特点包括唯一性和非空性，每个表只能有一个主键，并且主键列中的值不能有重复或为NULL。

以下是关于主键的详细解释：

1. 唯一性：

   • 主键的值必须是唯一的，每个记录都有一个独特的标识符。
   • 保证了在表中不会有两条记录具有相同的主键值。

2. 非空性：

   • 主键列的值不能为NULL。每个记录都必须具有一个非空的主键值。
   • 这确保了主键的完整性，不会存在没有标识符的记录。

3. 主键的定义：

   • 在创建表时，可以通过在列定义后添加 PRIMARY KEY 关键字来指定主键。
   • 主键可以包含一个或多个列。当主键包含多个列时，称为复合主键。

   CREATE TABLE example_table (
       id INT PRIMARY KEY,
       name VARCHAR(255)
   );

4. 自动递增：

   • 通常，主键列会使用自动递增的方式生成唯一标识符。这可以通过在列定义时添加 AUTO_INCREMENT 关键字来实现。

   CREATE TABLE example_table (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(255)
   );

   • 上述示例中，id 列被定义为自动递增的主键，每次插入记录时，数据库系统会自动分配一个唯一的递增值。

5. 主键的作用：

   • 提供了一种高效的查找机制，通过主键可以迅速定位表中的某条记录。
   • 用于建立表与表之间的关系，作为外键的引用。

6. 主键与外键的关系：

   • 主键和外键之间建立关系，可以确保表与表之间的数据一致性。
   • 外键通常引用另一张表的主键，形成表与表之间的关联。

   CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       customer_id INT,
       FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
   );

   • 上述示例中，orders 表的 customer_id 列作为外键引用了 customers 表的 customer_id 主键。

7. 主键的选择：

   • 主键的选择应该是独一无二且稳定的，一般不建议使用经常变化的列作为主键。
   • 可以使用自然主键（例如身份证号、学号）或人工主键（通过添加自动递增列）。

总体而言，主键是关系型数据库中非常重要的概念，它确保了数据表中的每条记录都有一个唯一的标识符，有助于保持数据的一致性和完整性。

FOREIGN KEY

B表中的列，引用了A表中的主键，B表中的列就是外键

• A表称为主表，B表称为从表

PS：并非一定是引用A表中的主键，也可以引用候选键，但通常是引用的主键。

用于定义外键，创建与其他表的关联。

user_id INT,
FOREIGN KEY (user_id) REFERENCES users(id);  -- 创建指向users表的外键

FOREIGN KEY 是用于定义表之间关联的关键字。它建立了一个外键约束，用于确保一个表中的值与另一个表中的值相匹配。以下是关于 FOREIGN KEY 的详细解释：

1. 在创建表时使用 FOREIGN KEY：

   • 在创建表时，可以使用 FOREIGN KEY 关键字来定义外键。

   CREATE TABLE orders (
      order_id INT PRIMARY KEY,
      product_id INT,
      FOREIGN KEY (product_id) REFERENCES products(product_id)  -- 创建指向 products 表的外键
   );

2. 在修改表结构时使用 FOREIGN KEY：

   • 可以使用 ALTER TABLE 语句在已存在的表中添加或修改外键。

   ALTER TABLE orders
   ADD FOREIGN KEY (product_id) REFERENCES products(product_id);  -- 向 orders 表中添加外键

3. 外键的特性：

   • 外键用于建立表之间的关联，确保一个表中的列的值存在于另一个表的列中。
   • 外键通常用于关联两个表中的主键和外键列。
   • 外键列的值必须与目标表中的列的值匹配，或者可以是 NULL，前提是外键列允许 NULL。

4. 删除外键：

   • 可以使用 ALTER TABLE 语句来删除表中的外键。

   ALTER TABLE orders
   DROP FOREIGN KEY orders_product_fk;  -- 删除 orders 表中名为 orders_product_fk 的外键

   • 具体语法可能因数据库系统而异，上述示例语法适用于MySQL。

5. 级联操作：

   • 外键还可以定义级联操作，如 ON DELETE 和 ON UPDATE，用于指定在关联表中的行被删除或更新时应该执行的操作。

   CREATE TABLE orders (
      order_id INT PRIMARY KEY,
      product_id INT,
      FOREIGN KEY (product_id) REFERENCES products(product_id)
          ON DELETE CASCADE   -- 当关联表中的行被删除时，删除 orders 表中相应的行
          ON UPDATE SET NULL  -- 当关联表中的行被更新时，将 orders 表中的外键列设置为 NULL
   );

FOREIGN KEY 用于在关系型数据库中建立表之间的关联，确保数据的一致性和完整性。使用外键可以确保在关联的表中进行操作时，不会存在不一致的数据。

外键详解

外键（Foreign Key）是关系型数据库中用于建立表与表之间关联关系的一种约束。外键用于指定一个表中的列（或列组合），这些列的值必须对应到另一表中的主键或唯一键上。外键的存在可以确保数据的一致性和完整性，实现表与表之间的关联。

以下是关于外键的详细解释：

1. 定义外键：

   • 在创建表时，可以通过在列定义中使用 FOREIGN KEY 关键字来定义外键。外键通常指向另一张表的主键或唯一键。

   CREATE TABLE orders (
      order_id INT PRIMARY KEY,
      customer_id INT,
      FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
   );

   • 上述示例中，orders 表的 customer_id 列被定义为外键，它引用了 customers 表的 customer_id 主键。

2. 外键的作用：

   • 建立表与表之间的关联，确保数据的一致性。
   • 防止无效的数据插入，只允许引用表中已存在的值。

3. 引用主键或唯一键：

   • 通常，外键引用目标表的主键或唯一键，确保关联的唯一性。
   • 可以引用其他表的唯一键，但最常见的是引用主键。

4. 级联操作：

   • 外键还可以定义级联操作，即在主表发生更新或删除时，外键表的对应数据也会发生变化。
   • 常见的级联操作包括：
     • CASCADE: 在主表发生更新或删除时，外键表的对应数据也会被更新或删除。
     • SET NULL: 在主表发生更新或删除时，外键表的对应数据会被设置为 NULL。
     • SET DEFAULT: 在主表发生更新或删除时，外键表的对应数据会被设置为默认值。
     • NO ACTION (默认): 不执行任何级联操作，只要保证外键关系的一致性。

   CREATE TABLE orders (
      order_id INT PRIMARY KEY,
      customer_id INT,
      FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON UPDATE CASCADE ON DELETE SET NULL
   );

   • 上述示例中，定义了在主表（customers 表）发生更新时执行 CASCADE 操作，在主表发生删除时执行 SET NULL 操作。

5. 多列外键：

   • 外键可以包含多列，形成复合外键。这时，外键的每个列都对应到目标表中的相应列。

   CREATE TABLE order_items (
      order_id INT,
      product_id INT,
      quantity INT,
      PRIMARY KEY (order_id, product_id),
      FOREIGN KEY (order_id) REFERENCES orders(order_id),
      FOREIGN KEY (product_id) REFERENCES products(product_id)
   );

   • 上述示例中，order_items 表定义了一个复合主键 (order_id, product_id)，并分别定义了两个外键，分别引用了 orders 表和 products 表中的主键。

外键是数据库中用于维护表与表之间关系的关键机制之一。通过使用外键，可以确保数据的一致性，帮助维护表之间的关联，同时提供了方便的查询和检索机制。

外键约束

外键约束（Foreign Key Constraint）是一种数据库约束，用于确保在一个表中的某些列的值与另一个表的列的值保持一致。外键约束定义了两个表之间的关系，其中一个表的列（子表）引用了另一个表的列（父表）作为外键。外键约束的作用是保证数据的完整性和一致性，确保在进行数据操作时，不会出现无效的引用。

以下是外键约束的一些重要特点和用法：

1. 定义外键约束： 在创建表时，可以通过在列定义中使用 FOREIGN KEY 关键字来定义外键约束。

   CREATE TABLE orders (
       order_id INT PRIMARY KEY,
       customer_id INT,
       FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
   );

   在上述示例中，orders 表中的 customer_id 列被定义为外键，它引用了 customers 表中的 customer_id 列作为其父表。

2. 外键约束的作用：

   • 确保引用完整性：外键约束确保了在子表中引用的父表中的值是有效的，从而保持数据的引用完整性。
   • 约束数据操作：外键约束可以防止对父表中被引用的值进行删除或修改，除非相应的子表中的引用也被删除或更新。

3. 级联操作：

   • 外键约束还可以定义级联操作，即在父表中进行更新或删除时，子表中的相应数据也会进行相应的更新或删除操作。
   • 常见的级联操作包括 CASCADE、SET NULL、SET DEFAULT 和 NO ACTION。

4. 检查外键约束状态：

   • 可以使用 SHOW CREATE TABLE 或 SHOW TABLE STATUS 等语句来查看表的创建语句，以确定是否存在外键约束。

5. 管理外键约束：

   • 可以使用 ALTER TABLE 语句来添加、修改或删除外键约束。
   • 使用 DROP FOREIGN KEY 语句来删除现有的外键约束。

外键约束是数据库设计中重要的概念，它有助于维护数据的一致性和完整性。通过定义适当的外键约束，可以确保数据之间的关联正确，并防止无效引用和操作。

范例：外键定义与引用

好的，让我们通过一个简单的例子演示如何定义和引用外键。我们将考虑两张表：employees 和 departments。每个雇员（employee）属于一个部门（department）。以下是这两张表的定义：

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

在这里我们有两个表：

1. departments 表：

   • 包含 department_id 作为主键，用于唯一标识每个部门。
   • department_name 用于存储部门的名称。

2. employees 表：

   • 包含 employee_id 作为主键，用于唯一标识每个雇员。
   • employee_name 用于存储雇员的名称。
   • department_id 用于存储每个雇员所属的部门，同时定义了一个外键引用 departments 表的 department_id 列。

在 employees 表的定义中，使用了 FOREIGN KEY 来定义外键，该外键引用了 departments 表中的 department_id 列。这表示 employees 表中的 department_id 列的值必须是 departments 表中 department_id 列的一个有效值。

这样的设计确保了数据的一致性，因为每个雇员的部门信息必须对应到 departments 表中存在的部门。如果试图在 employees 表中插入一个不存在的部门，将会触发外键约束，防止插入无效的数据。

UNIQUE KEY

唯一键，所有记录中此字段的值不能重复，但可以为NULL，用于确保列中的所有值都是唯一的。

email VARCHAR(255) UNIQUE;  -- 唯一的邮箱地址列

UNIQUE KEY 是用于定义表中唯一键约束的关键字。与 PRIMARY KEY 不同，UNIQUE KEY 允许列中包含唯一的值，但允许 NULL 值。以下是关于 UNIQUE KEY 的详细解释：

1. 在创建表时使用 UNIQUE KEY：

   • 在创建表时，可以使用 UNIQUE KEY 关键字来定义唯一键。

   CREATE TABLE example (
       email VARCHAR(255) UNIQUE KEY,  -- 定义唯一键约束的字符串列 email
       username VARCHAR(255) UNIQUE KEY  -- 定义唯一键约束的字符串列 username
   );

   • UNIQUE KEY 也可以用于复合唯一键。

   CREATE TABLE example (
       first_name VARCHAR(255),
       last_name VARCHAR(255),
       UNIQUE KEY (first_name, last_name)  -- 定义由 first_name 和 last_name 组成的复合唯一键
   );

2. 在修改表结构时使用 UNIQUE KEY：

   • 可以使用 ALTER TABLE 语句在已存在的表中添加或修改唯一键。

   ALTER TABLE example
   ADD UNIQUE KEY (email);  -- 向表中添加唯一键约束到 email 列

3. 唯一键的特性：

   • UNIQUE KEY 约束确保列中的值是唯一的，不允许重复。
   • 不同于 PRIMARY KEY，UNIQUE KEY 允许 NULL 值，但仅允许一个 NULL 值。

4. 删除唯一键：

   • 可以使用 DROP INDEX 或 DROP KEY 语句来删除表中的唯一键。

   ALTER TABLE example
   DROP INDEX email;  -- 删除表中的 email 列的唯一键

   • 有些数据库系统也支持使用 ALTER TABLE 语句删除唯一键。

   ALTER TABLE example
   DROP UNIQUE KEY email;  -- 删除表中的 email 列的唯一键

UNIQUE KEY 适用于需要确保某列或某组列中的值唯一的情况。与 PRIMARY KEY 不同，UNIQUE KEY 允许包含 NULL 值，因此适用于希望允许列中有唯一值或 NULL 的场景。

唯一键注意事项

• 可以通过 NOT NULL 修饰符，使唯一键不能为 NULL

CHARACTER SET

• 指定一个字符集

CHARACTER SET 是用于定义数据库、表、列等数据存储的字符集的属性。字符集定义了可以在数据库中存储的字符的集合，以及如何将这些字符映射到二进制数据。以下是关于 CHARACTER SET 的详细解释：

1. 在创建数据库时使用 CHARACTER SET：

   • 在创建数据库时，可以使用 CHARACTER SET 关键字来指定数据库的默认字符集。

   CREATE DATABASE example_db CHARACTER SET utf8mb4;  -- 创建一个默认字符集为 utf8mb4 的数据库

2. 在创建表时使用 CHARACTER SET：

   • 在创建表时，可以使用 CHARACTER SET 关键字来指定表的默认字符集。

   CREATE TABLE example_table (
       column1 VARCHAR(255) CHARACTER SET utf8mb4,  -- 创建一个字符集为 utf8mb4 的列
       column2 TEXT CHARACTER SET utf8mb4           -- 创建一个字符集为 utf8mb4 的文本列
   );

3. 在修改表结构时使用 CHARACTER SET：

   • 可以使用 ALTER TABLE 语句在已存在的表中修改列的字符集。

   ALTER TABLE example_table
   MODIFY column1 VARCHAR(255) CHARACTER SET utf8mb4;  -- 将 column1 列的字符集修改为 utf8mb4

4. 字符集的选择：

   • 选择合适的字符集取决于你的应用需求，通常推荐使用支持更广泛字符范围的字符集，如 utf8mb4。
   • utf8mb4 支持包括表情符号在内的所有Unicode字符，而 utf8 只支持基本的Unicode字符。

5. 列级别和表级别的字符集：

   • 在表创建时，可以在表级别上指定字符集，也可以在列级别上指定字符集。
   • 如果在列级别和表级别都指定了字符集，列级别的设置将覆盖表级别的设置。

   CREATE TABLE example_table (
       column1 VARCHAR(255) CHARACTER SET utf8mb4,  -- 列级别的字符集设置
       column2 TEXT  -- 表级别的字符集设置将应用于所有没有指定字符集的列
   ) CHARACTER SET utf8;  -- 表级别的字符集设置

6. 查看字符集信息：

   • 可以使用 SHOW VARIABLES LIKE 'character_set_%'; 和 SHOW CREATE TABLE table_name; 来查看字符集的信息。

   SHOW VARIABLES LIKE 'character_set_%';  -- 查看数据库服务器的字符集信息
   SHOW CREATE TABLE example_table;        -- 查看表的创建语句，包含字符集信息

字符集的选择对于支持不同语言和符号的应用非常重要。合理选择字符集可以确保在数据库中存储和检索数据时不会出现乱码或数据损坏的情况。

CHECK

用于定义列的检查约束条件，确保插入的值符合指定的条件。

age INT CHECK (age >= 18);  -- 年龄列必须大于等于18

CHECK 是用于在创建表时定义列级别的检查约束的关键字。它用于确保列中的值满足指定的条件。以下是关于 CHECK 的详细解释：

1. 在创建表时使用 CHECK：

   • 在创建表时，可以使用 CHECK 关键字来定义列级别的检查约束。

   CREATE TABLE example_table (
       age INT CHECK (age >= 18),        -- 创建一个检查约束，确保 age 列的值大于等于 18
       price DECIMAL(10,2) CHECK (price > 0)  -- 创建一个检查约束，确保 price 列的值大于 0
   );

2. 在修改表结构时使用 CHECK：

   • 一些数据库系统可能支持在已存在的表中添加或修改列级别的检查约束，具体语法可能会有所不同。

   ALTER TABLE example_table
   ADD CHECK (quantity > 0);  -- 向表中添加检查约束，确保 quantity 列的值大于 0

3. 多个条件的 CHECK 约束：

   • 可以使用逻辑运算符如 AND、OR 和 NOT 来组合多个条件。

   CREATE TABLE example_table (
       temperature INT CHECK (temperature >= -20 AND temperature <= 40),  -- 创建一个范围检查约束
       status VARCHAR(10) CHECK (status IN ('Active', 'Inactive'))  -- 创建一个取值检查约束
   );

4. CHECK 约束的命名：

   • 一些数据库系统允许为 CHECK 约束命名，以方便后续的管理和维护。

   CREATE TABLE example_table (
       age INT,
       CHECK (age >= 18) CONSTRAINT check_age  -- 命名 CHECK 约束为 check_age
   );

5. 检查约束的触发时机：

   • CHECK 约束通常在插入或更新数据时触发，确保被插入或更新的值满足指定的条件。

   INSERT INTO example_table (age) VALUES (17);  -- 触发 CHECK 约束，插入失败
   UPDATE example_table SET age = 20 WHERE id = 1;  -- 触发 CHECK 约束，更新失败

6. CHECK 约束的删除：

   • 删除 CHECK 约束的语法可能因数据库系统而异。

   ALTER TABLE example_table
   DROP CONSTRAINT check_age;  -- 删除名为 check_age 的 CHECK 约束

CHECK 约束用于确保列中的值满足指定的条件，这有助于保证数据的一致性和完整性。在设计数据库时，使用 CHECK 约束可以强制执行业务规则和限制条件。

AUTO_INCREMENT

自动递增，适用于整数类型

用于指定列为自增长列，通常用于主键列。

id INT AUTO_INCREMENT PRIMARY KEY;  -- 自增长的主键列

AUTO_INCREMENT 是用于创建自动递增列的关键字，通常用于为表中的主键列生成唯一的整数值。以下是关于 AUTO_INCREMENT 的详细解释：

1. 在创建表时使用 AUTO_INCREMENT：

   • 在创建表时，可以使用 AUTO_INCREMENT 关键字为某一列定义自动递增的属性。

   CREATE TABLE example_table (
       id INT AUTO_INCREMENT PRIMARY KEY,  -- 创建一个自动递增的主键列
       name VARCHAR(255)
   );

   • AUTO_INCREMENT 通常用于整数列，确保每次插入新记录时，该列都会自动递增。

2. 在修改表结构时使用 AUTO_INCREMENT：

   • 可以使用 ALTER TABLE 语句在已存在的表中添加 AUTO_INCREMENT 属性。

   ALTER TABLE example_table
   MODIFY id INT AUTO_INCREMENT;  -- 将 id 列的属性修改为自动递增

3. 插入数据时使用 AUTO_INCREMENT：

   • 在插入新记录时，可以省略自动递增列的值，数据库系统会自动分配一个唯一的递增值。

   INSERT INTO example_table (name) VALUES ('John Doe');  -- id 列将自动递增

4. 获取最后插入的 AUTO_INCREMENT 值：

   • 在某些数据库系统中，可以使用特定的函数获取最后插入的自动递增值。

   SELECT LAST_INSERT_ID();  -- 获取最后插入的 AUTO_INCREMENT 值

   • 请注意，不同的数据库系统可能使用不同的函数或语法来获取最后插入的自动递增值。

5. AUTO_INCREMENT 的起始值和步长：

   • 在创建表或修改表结构时，可以指定 AUTO_INCREMENT 列的起始值和步长（增量）。

   CREATE TABLE example_table (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(255)
   ) AUTO_INCREMENT = 100;  -- 指定 AUTO_INCREMENT 的起始值
   
   ALTER TABLE example_table
   AUTO_INCREMENT = 100;  -- 修改表的 AUTO_INCREMENT 的起始值

   • 步长表示每次递增的量，可以设置为不同的正整数值，默认情况下为 1。

AUTO_INCREMENT 是一种方便的方法，用于自动生成唯一的、递增的整数值，通常用于作为表的主键。这有助于确保每个记录都有唯一的标识符，并简化了向表中插入新记录的过程。

范例

mysql> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

# auto_increment_offset 定义初始值
# auto_increment_increment 定义步进

UNSIGNED

在数据库中，无符号（unsigned）是一个数据类型的属性，它表示该数据类型只能存储非负整数（包括零和正整数），而不允许存储负整数。这是通过在声明数据类型时添加 UNSIGNED 关键字来实现的。

在 MySQL 中，常见的整数数据类型有 TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT，它们都可以使用 UNSIGNED 关键字进行无符号声明。

例如，如果你声明一个 INT UNSIGNED 类型的列，那么这个列将只能存储非负的整数值，而不能存储负数。同样的规则适用于其他整数类型。以下是一个示例：

CREATE TABLE example_table (
    id INT UNSIGNED,
    quantity TINYINT UNSIGNED
);

在上述示例中，id 列和 quantity 列都被声明为无符号整数类型，因此它们只能存储非负的整数值。

使用无符号整数的一个优势是它提供了比相同有符号整数更大的正数范围。例如，一个 TINYINT 可以存储的范围是 0 到 255，而有符号的 TINYINT 只能存储 -128 到 127。因此，如果你知道一个列不需要存储负数，并且你想要更大的正整数范围，你可以选择使用无符号整数。

UNSIGNED 是一种修饰符，用于指定整数数据类型为无符号的，即只能存储非负整数。以下是关于 UNSIGNED 的详细解释：

1. 在创建表时使用 UNSIGNED：

   • 在创建表时，可以使用 UNSIGNED 修饰符来指定整数列为无符号。

   CREATE TABLE example_table (
       id INT UNSIGNED PRIMARY KEY,  -- 创建一个无符号整数的主键列
       quantity INT UNSIGNED  -- 创建一个无符号整数的列
   );

2. 在修改表结构时使用 UNSIGNED：

   • 可以使用 ALTER TABLE 语句在已存在的表中添加或修改列的 UNSIGNED 修饰符。

   ALTER TABLE example_table
   MODIFY quantity INT UNSIGNED;  -- 将 quantity 列的数据类型修改为无符号整数

3. UNSIGNED 整数的取值范围：

   • 无符号整数只能存储非负数，其取值范围是从 0 到最大无符号整数值。
   • 例如，TINYINT UNSIGNED 的取值范围是 0 到 255，INT UNSIGNED 的取值范围是 0 到 4294967295。

4. 注意事项：

   • 使用 UNSIGNED 时，确保你的应用逻辑和数据模型不允许存储负数，否则可能导致意外的错误。
   • 在合适的情况下，使用 UNSIGNED 可以有效地增加整数列的可用范围，但要确保这符合业务逻辑。

   CREATE TABLE example_table (
       amount INT,              -- 正负数都可存储
       positive_amount INT UNSIGNED  -- 仅存储非负数
   );

5. 与 SIGNED 整数的对比：

   • 有符号整数（SIGNED）可以存储正负数，而无符号整数（UNSIGNED）仅能存储非负数。

   CREATE TABLE signed_example (
       id INT SIGNED,  -- 有符号整数列，可以存储正负数
       quantity INT UNSIGNED  -- 无符号整数列，仅能存储非负数
   );

UNSIGNED 修饰符用于指定整数列为无符号的，这对于确保某些列仅包含非负整数值非常有用。在设计数据库时，可以根据业务需求和数据的性质来选择使用 SIGNED 还是 UNSIGNED 整数。

COMMENT

在 MySQL 数据库中，COMMENT 语句用于添加注释或描述到数据库对象（例如表、列、索引等）。这些注释对于记录和理解数据库结构、表结构等非常有用。以下是对 COMMENT 的详细解释：

1. 为表添加注释：

   在创建表时，可以使用 COMMENT 语句为整个表添加注释。注释可以包含对表的描述、用途、注意事项等信息。

   CREATE TABLE example_table (
       id INT PRIMARY KEY,
       name VARCHAR(50)
   ) COMMENT 'This table stores information about something.';

2. 为列添加注释：

   可以在列定义中使用 COMMENT 语句为特定列添加注释。这可以包含关于列的额外信息，如用途、数据类型等。

   CREATE TABLE example_table (
       id INT PRIMARY KEY COMMENT 'Unique identifier for each record.',
       name VARCHAR(50) COMMENT 'Name of the item.'
   );

3. 为索引添加注释：

   可以在创建索引时使用 COMMENT 语句为索引添加注释。这有助于理解索引的作用和用途。

   CREATE INDEX idx_example ON example_table (name) COMMENT 'Index on the name column for faster searches.';

4. 查看注释信息：

   使用 SHOW CREATE TABLE 语句可以查看表的创建语句，包括注释信息。

   SHOW CREATE TABLE example_table;

   这将显示包含表结构和注释的创建语句。

5. 修改注释：

   使用 ALTER TABLE 语句可以修改表或列的注释。

   ALTER TABLE example_table COMMENT 'Updated table description.';

   或者修改列的注释：

   ALTER TABLE example_table MODIFY COLUMN id INT COMMENT 'Updated identifier description.';

   或者修改索引的注释：

   ALTER TABLE example_table DROP INDEX idx_example, ADD INDEX idx_example (name) COMMENT 'Updated index description.';

注释在数据库设计和维护过程中非常有用，可以提供关于数据库对象的额外信息，帮助开发人员更好地理解和使用数据库。

修饰符选择指导

选择适当的修饰符取决于你的数据模型和使用场景。以下是一些通用的指导原则，帮助你选择适当的修饰符：

1. NULL 和 NOT NULL：

   • 对于允许空值的列，使用 NULL。
   • 对于不允许空值的列，使用 NOT NULL。

2. DEFAULT：

   • 对于可以有默认值的列，使用 DEFAULT 来提供默认值。
   • 对于没有默认值的列，可以考虑使用 NULL 或者在应用层面提供默认值。

3. AUTO_INCREMENT：

   • 对于主键或需要唯一标识的列，使用 AUTO_INCREMENT，通常用于整数类型。
   • 注意：并非所有的数据库都支持自增列，需要根据数据库系统的支持情况来选择。

4. PRIMARY KEY 和 UNIQUE：

   • 对于主键列，使用 PRIMARY KEY。
   • 对于需要唯一性的列，但不是主键，使用 UNIQUE。

5. CHECK：

   • 使用 CHECK 约束来确保插入的值符合特定条件。这对于限制范围或特定值的列非常有用。

6. FOREIGN KEY：

   • 对于需要与其他表建立关联的列，使用 FOREIGN KEY。这通常用于建立表与表之间的关系。

7. DEFAULT CURRENT_TIMESTAMP：

   • 对于记录创建时间戳的列，使用 DEFAULT CURRENT_TIMESTAMP。
   • 注意：并非所有的数据库都支持这一特性，需要根据数据库系统的支持情况来选择。

8. 长度修饰符：

   • 对于字符串类型，使用长度修饰符来限制字符串的最大长度。
   • 对于数字类型，使用长度修饰符来指定整数和小数的位数。

9. 其他特定修饰符：

   • 根据需要，使用其他特定的修饰符，如 BINARY、ZEROFILL 等，来满足具体的需求。

总体而言，选择修饰符需要根据实际的业务需求和数据模型来决定。考虑到性能、数据完整性和查询需求，选择合适的修饰符对于建立健壮的数据库结构是至关重要的。

创建表范例

register

设计一张表，记录账户的注册信息，应包含用户的姓名、登录名、密码。

在 InnoDB 存储引擎中，推荐创建主键。

CREATE TABLE `register` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(48) DEFAULT NULL,
  `login_name` varchar(48) NOT NULL COMMENT '登陆账号',
  `login_password` char(128) NOT NULL COMMENT '登陆密码',
  PRIMARY KEY (`id`),
  -- 这是创建一个唯一键（Unique Key）的 SQL 语句，该唯一键名为 login_name，作用于表中的 login_name 列。
  UNIQUE KEY `login_name` (`login_name`)
  /** 
   UNIQUE KEY： 这部分指定了创建唯一键的操作。
   login_name： 这是唯一键的名称，也就是这个唯一键的标识符。
   ( login_name )： 这表示唯一键应用于名为 login_name 的列。
  **/
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

• 为避免用户名重复，将login_name 字段通过 UNIQUE KEY 设置为了唯一键。

—

employees

在MySQL中，创建表是通过使用CREATE TABLE语句完成的。下面是一个详细的MySQL创建表的示例和解释：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    hire_date DATE,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

让我们一步一步解释这个示例：

1. CREATE TABLE employees: 这部分声明了你要创建的表的名称，这里是"employees"。你可以选择其他名称，但要确保名称是唯一的。

2. ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), ... ): 这是表的列定义部分。每个列都有一个名称和一个数据类型。在这个示例中，我们创建了多个列，包括 employee_id（整数类型），first_name 和 last_name（字符串类型），birth_date 和 hire_date（日期类型），以及 department_id（整数类型）。

   • employee_id INT PRIMARY KEY: 这定义了一个整数类型的列 employee_id，并将其指定为主键（PRIMARY KEY）。主键是用来唯一标识每一行的列。它确保表中的每一行都有一个唯一的标识符。

   • first_name VARCHAR(50), last_name VARCHAR(50): 这定义了两个字符串类型的列 first_name 和 last_name，每个可以存储最多50个字符的文本。

   • birth_date DATE, hire_date DATE: 这定义了两个日期类型的列 birth_date 和 hire_date。

   • department_id INT: 这定义了一个整数类型的列 department_id，用于存储部门的标识符。

3. FOREIGN KEY (department_id) REFERENCES departments(department_id): 这是外键约束的定义。它确保 department_id 列的值必须是另一张表中的 departments 表的 department_id 列的值之一。这样可以创建表之间的关联关系。

这只是一个简单的示例，实际上，你可能会有更多的列和复杂的约束条件，具体取决于你的数据模型和需求。创建表的语法和规则在不同的数据库管理系统中可能有所不同，但上述示例基本上适用于大多数情况。

—

classes

• 反引号标注的名称，会被认为是非关键字，以防冲突

CREATE TABLE `classes` (
  `ClassID` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `Class` varchar(100) DEFAULT NULL,
  `NumOfStu` smallint unsigned DEFAULT NULL,
  PRIMARY KEY (`ClassID`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3;