【MySQL 系列】MySQL 语句篇_DDL 语句

ゞ浴缸里的玫瑰 2024-04-06 15:39 111阅读 0赞

> DDL（ Data Definition Language，数据定义语言）用在定义或改变表的结构数据类型、表之间的链接和约束等初始化工作上。常用的语句关键字包括 CREATE、 DROP、 ALTER 等。

--------------------

#### 文章目录 ####

*   *   *   *  1、MySQL 中的 DQL 语句
             *  2、MySQL 中库表的 DQL 语句详解
             *   *  2.1、数据库操作
                 *   *  2.1.1、创建数据库
                     *  2.1.2、删除数据库
                     *  2.1.3、选择数据库
                     *  2.1.4、查看当前数据库
                 *  2.2、数据表操作
                 *   *  2.2.1、创建数据表
                     *  2.2.2、删除数据表
                     *  2.2.3、修改数据表
                     *  2.2.4、重命名数据表
                     *  2.2.5、清空数据表
                 *  2.3、数据列操作
                 *   *  2.3.1、创建列
                     *  2.3.2、新增列
                     *  2.3.3、删除列
                     *  2.3.4、自增列
                     *  2.3.5、生成列
             *  3、MySQL 中约束的 DQL 语句详解
             *   *  3.1、主键约束
                 *   *  3.1.1、创建主键
                     *  3.1.2、添加主键
                     *  3.1.3、删除主键
                     *  3.1.4、主键值的产生
                     *  3.1.5、主键 vs 唯一索引
                 *  3.2、外键约束
                 *   *  3.2.1、创建外键
                     *  3.2.2、添加外键
                     *  3.2.3、删除外键
                     *  3.2.4、CASCADE 策略
                 *  3.3、唯一键约束
                 *   *  3.3.1、定义一列为唯一键
                     *  3.3.2、定义多列为唯一键
                     *  3.3.3、添加唯一键语法
                     *  3.3.4、删除唯一键语法
                 *  3.4、默认值约束
                 *  3.5、检查约束
                 *   *  3.5.1、定义检查列
                     *  3.5.2、修改检查列
                 *  3.6、非空约束

--------------------

##### 1、MySQL 中的 DQL 语句 #####

DDL（ Data Definition Language，数据定义语言）用在定义或改变表的结构数据类型、表之间的链接和约束等初始化工作上。常用的语句关键字包括 CREATE、 DROP、 ALTER 等。

--------------------

##### 2、MySQL 中库表的 DQL 语句详解 #####

###### 2.1、数据库操作 ######

数据库是存储数据的容器。一个数据库中可以包含多个表。

###### 2.1.1、创建数据库 ######

要想创建表，必须首先创建数据库。在 MySQL 中，`CREATE DATABASE` 和 `CREATE SCHEMA` 语句用来创建数据库。

以下是 `CREATE DATABASE` 语句的语法：

CREATE {
       DATABASE | SCHEMA} [IF NOT EXISTS] database_name
    [CHARACTER SET charset_name]
    [COLLATE collation_name]
    [ENCRYPTION {
       'Y' | 'N'}]

说明：① `CREATE DATABASE` 和 `CREATE SCHEMA` 的是一样的；② `CREATE DATABASE` 后指定要创建的数据库的名字；③ `IF NOT EXISTS` 表示在指定的数据库不存在的情况下才创建。它是可选的；④ `CHARACTER SET charset_name` 指定数据库的字符集。它是可选的。默认使用数据库服务器的配置；⑤ `COLLATE collation_name` 指定数据库的排序规则。它是可选的。默认使用数据库服务器的配置；⑥ `ENCRYPTION` 指定数据库的是否加密。它是可选的。默认使用数据库服务器的配置。

MySQL 数据库命名的要求：

*  数据库的名字最长为 64 个字符。名字的长度还取决于操作系统。
 *  数据库名可以由字母、数字、下划线、美元符号组成。

虽然语法看起来很复杂，但是最常用的就是下面的语句：

CREATE DATABASE db_name;

###### 2.1.2、删除数据库 ######

当我们不需要某个数据库的时候，我们可以将数据库删除。在 MySQL 中，`DROP DATABASE` 语句用来删除数据库。

下面显示了该 `DROP DATABASE` 语句的语法：

DROP {
       DATABASE | SCHEMA} [IF EXISTS] database_name;

说明：① `DROP DATABASE` 和 `DROP SCHEMA` 是一样的；② `DROP DATABASE` 关键字后指定要删除的数据库的名称；③ `IF EXISTS` 选项可以避免删除不存在数据库时发生的错误。它是可选的。

`DROP DATABASE` 语句返回它删除的表数。

注意：`DROP DATABASE` 语句将永久删除数据库和数据库中的所有表，请谨慎操作。

###### 2.1.3、选择数据库 ######

在 MySQL 服务器中，可能有多个数据库。如果要进行查询等操作，首先应该先选择要进行操作的数据库。你可以使用 USE 语句选择或者切换数据库。

USE testdb;

我们可以在登陆 MySQL 服务器时直接指定要操作的数据库。可以使用如下命令：

mysql -u root -p -D testdb

这里，我们使用 `-D testdb` 参数指定了我们要连接的数据库是 `testdb`。

###### 2.1.4、查看当前数据库 ######

如果我们想查看当前正在操作的数据库，使用以下方法：

SELECT DATABASE();
    ---
    +------------+
    | DATABASE() |
    +------------+
    | testdb     |
    +------------+
    1 row in set (0.00 sec)

或者使用 `SHOW TABLES` 查看当前数据库表信息。

在输出中，表头 `Tables_in_testdb` 告诉了我们当前的数据库是 `testdb`。

###### 2.2、数据表操作 ######

表是关系数据库中数据存储的基本单位。

###### 2.2.1、创建数据表 ######

我们使用 `CREATE TABLE` 语句在数据库中创建一个新表。 `CREATE TABLE` 语句的语法如下：

CREATE TABLE [IF NOT EXISTS] table_name (
       column_name data_type [NOT NULL | NULL] [DEFAULT expr],
       column_name data_type [NOT NULL | NULL] [DEFAULT expr],
       ...,
       [table_constraints]
    ) [ENGINE=storage_engine];

语句说明：

1.  `CREATE TABLE` 语句创建一个给定名字 `table_name` 的表。表名在一个数据库中是唯一的；可由字母、数字、下划线和美元符号组成，表名长度在 64 个字符以内。新建的表会在当前默认的数据库中。如果还没有选择数据库，则可以使用 `db_name.table_name` 格式指定要新建的表所在的数据库；
2.  `IF NOT EXISTS` 指示只有给定的表不存在的时候才进行创建。它是可选的。如果你给定一个已经存在的表名，又没有使用 `IF NOT EXISTS` 子句，服务器会返回一个错误；
3.  `column_name data_type [NOT NULL | NULL] [DEFAULT expr] [AUTO_INCREMENT]` 定义了表中的一列。多个列使用逗号分隔；
4.  `[table_constraints]` 位于列定义之后，它定义了表的约束。它是可选的。表的约束有主键、外键、CHECK、UNIQUE 等；
5.  `ENGINE=storage_engine` 子句指定了表使用的存储引擎。它是可选的。如果不指定此选项，则采用服务器默认的存储引擎。自 MySQL 5.5 版以来，服务器默认的引擎是由 MyISAM 变成了 InnoDB；
6.  当表名或者字段名中含有空格或者其他特殊字字符时，可以使用 " \` " 包围起来。

###### 2.2.2、删除数据表 ######

当我们不需要某个表的时候，我们可以将此表删除。在 MySQL 中，`DROP TABLE` 语句用来删除表。

注意：`DROP TABLE` 语句将永久删除表和表中的数据，请谨慎操作。

以下是 `DROP TABLE` 语句的基本语法：

DROP TABLE [IF EXISTS]
      table_name [, table_name] ...

说明：① `DROP TABLE` 关键字后面是要删除的表名。如果要删除多个表，请使用逗号分隔表名；② `IF EXISTS` 选项避免了删除不存在的表时发生的错误。它是可选的；③ `DROP TABLE` 将删除表的定义和表中的数据，以及表上触发器；④ 需要具有要删除的每一个表的 `DROP` 权限才可以使用。

###### 2.2.3、修改数据表 ######

在使用表的过程中，如果您需要对表进行修改，您可以使用 `ALTER TABLE` 语句。通过 `ALTER TABLE` 语句，您可以重命名表、重命名列、添加列、删除列、修改列的属性等。

`ALTER TABLE` 语法：

ALTER TABLE table_name
      [alter_action options], ...

其中 `alter_action` 是一个修改动作，包括：`ADD`、`DROP`、`MODIFY`、`CHANGE`、`RENAME`。

`ADD` 关键字可用来添加列、索引、约束等，包括：

ADD [COLUMN] 																	# 添加列
    ADD INDEX 																		# 添加索引
    ADD PRIMARY KEY																# 添加主键
    ADD FOREIGN KEY																# 添加外键
    ADD UNIQUE INDEX															# 添加唯一索引
    ADD CHECK																			# 添加检查约束

`DROP` 关键字可用来删除列、索引、约束等，包括：

DROP [COLUMN] col_name												# 删除列
    ADD INDEX index_name													# 删除索引
    DROP PRIMARY KEY															# 删除主键
    DROP FOREIGN KEY fk_symbol										# 删除外键
    DROP CHECK symbol															# 删除检查约束

`MODIFY` 关键字用来修改列的定义。与 `CHANGE` 关键字不同，它不能重命名列。例如: `MODIFY [COLUMN] col_name column_definition`。

`CHANGE` 关键字用来修改列的定义。与 `MODIFY` 关键字不同，它可以重命名列。例如: `CHANGE [COLUMN] old_col_name new_col_name column_definition`。

`RENAME` 关键字可以重命名列、索引和表。包括：

RENAME COLUMN old_col_name TO new_col_name		# 重命名列。
    RENAME INDEX old_index_name TO new_index_name	# 重命名索引。
    RENAME new_tbl_name														# 重命名表。

###### 2.2.4、重命名数据表 ######

重命名表是一个简单的动作，但是它可能会带来一系列的问题。如果您没有同步修改那些用到此表的代码，则他们可能不能正常运行。因此，当我们打算修改一个表名的时候，需要首先从整体上进行评估。然后，再决定是否进行重命名表。一旦决定了要重命名一个表，需要把需要同步修改的地方整理清楚。

我们需要同步修改的代码可能包括那些使用了此表的：存储过程、视图、函数、触发器、外键约束 （在较旧的 MySQL 版本中）以及应用程序。

可以按照如下的语法使用 `RENAME TABLE` 语句重命名一个表：

RENAME TABLE
        old_table_name TO new_table_name
        [, old_table_name2 TO new_table_name2];

也可以按照如下语法使用 `ALTER TABLE` 语句重命名表：

ALTER TABLE old_table_name
    RENAME TO new_table_name;

语句说明：① `old_table_name` 是需要重命名的表；② `new_table_name` 是新的表名；③ `RENAME TABLE` 语句和 `ALTER TABLE` 语句也可以用来重命名视图。

`RENAME TABLE` 语句和 `ALTER TABLE` 语句也存在一些不同：

*  `RENAME TABLE` 语句更加简洁；
 *  可以在一个 `RENAME TABLE` 语句中同时重命名多个表；
 *  `RENAME TABLE` 语句不可以用来重命名临时表，而 `ALTER TABLE` 语句可以用来重命名临时表。

###### 2.2.5、清空数据表 ######

当我们需要清空一个表中的所有行时，除了使用 `DELETE * FROM table` 还可以使用 `TRUNCATE TABLE` 语句。

如果想要清空一个表， `TRUNCATE TABLE` 语句比 [`DELETE`][DELETE] 语句更加有效。

`TRUNCATE TABLE` 的语法很简单，如下：

TRUNCATE [TABLE] table_name;

其中 `TABLE` 关键字是可选的。也就是说， `TRUNCATE t;` 和 `TRUNCATE TABLE t;` 是等效的。

`TRUNCATE TABLE` 语句相当于先将此表删除掉，再创建一个新表。`TRUNCATE TABLE` 语句需要对操作的表具有 `DROP` 权限。

虽然 `TRUNCATE` 与 `DELETE` 类似，但是他们在以下几个方面存在不同：

*  `TRUNCATE` 被归类为 DDL 语句，而 `DELETE` 被归类为 DML 语句；
 *  `TRUNCATE` 操作无法被回滚，而 `DELETE` 可以被回滚；
 *  `TRUNCATE` 操作删除和重建表，它的速度比 `DELETE` 快得多；
 *  `TRUNCATE` 操作会重置表的自增值，而 `DELETE` 不会；
 *  `TRUNCATE` 操作不会激活删除触发器，而 `DELETE` 会；
 *  `TRUNCATE` 操作不返回代表删除行的数量的值，它通常返回 `0 rows affected`。`DELETE` 返回删除的行数；
 *  如果一个表被其他表的外键引用，对此表的 `TRUNCATE` 操作会失败。

###### 2.3、数据列操作 ######

MySQL 允许您使用 `ALTER TABLE` 语句来修改一个现有的表。

###### 2.3.1、创建列 ######

`CREATE TABLE` 时的列操作：

column_name data_type [NOT NULL | NULL] [DEFAULT expr] [AUTO_INCREMENT]\

改语句定义了表中的一列。多个列使用逗号分隔：

*  `column_name` 是列的名字。列名可由字母、数字、下划线和美元符号组成，列名长度在 64 个字符以内。列名在一个表中是唯一的；
 *  `data_type` 是数据类型，可以是 `CHAR`、`VARCHAR`、`INT`、 `DATE`、`DATETIME`、`BIT`、`TEXT`、`ENUM`、`JSON`、`BOLB` 等；
 *  `[NOT NULL | NULL]` 指示该列是否可以为 `NULL`。它是可选的。如果不指定该选项，则此列可以为 `NULL`。如果设置为 `NOT NULL`，则插入新行时该列必须有值；
 *  `[DEFAULT expr]` 指示该列的默认值。它是可选的。如果不指定该选项，则此列的默认是 `NULL`；
 *  `[AUTO_INCREMENT]` 指示该列是否是一个自增列。如果使用了此选项，则该列的值可有服务器自动产生和填充。该列的值从 `1` 开始，每增加一个行就会加 `1`。一个表中只能有一个自增列。

###### 2.3.2、新增列 ######

`ALTER TABLE ADD COLUMN` 时的列操作：

要向一个表中添加一列或多列，可以使用 `ALTER TABLE ... ADD COLUMN` 语句。

以下是 MySQL `ALTER TABLE ... ADD COLUMN` 语句的语法。

ALTER TABLE table_name
    ADD [COLUMN] column_definition [FIRST|AFTER existing_column]
    [, ADD [COLUMN] column_definition [FIRST|AFTER existing_column]];

在这个语法中：① 位于 `ALTER TABLE` 关键字之后的 `table_name` 是向其中添加列的表名；② `ADD [COLUMN]` 关键字之后是列的定义。您可以省略 `COLUMN` 关键字；③ 您需要在列定义 `column_definition` 中指定列名，列的类型等信息；④ 默认情况下，新列将被添加为表的最后一列。您可以通过使用 `FIRST` 关键字将列添加为表的第一列，或者使用 `AFTER existing_column` 将新列添加到现有列 `existing_column` 的后面；⑤ 如果需要在一个语句中添加多个列，则使用多个使用逗号分隔的 `ADD COLUMN` 子句。

###### 2.3.3、删除列 ######

`ALTER TABLE ADD COLUMN` 时的列操作：

删除列是一个很危险的动作。像使用 DROP TABLE 删除表一样，删除列后，您很难再恢复其中的数据。 删除列之前一定要确定此动作的必要性。

以下是 MySQL `ALTER TABLE ... DROP COLUMN` 语句的语法：

ALTER TABLE table_name
    DROP [COLUMN] column_name
    [, DROP [COLUMN] column_name];

在这个语法中：① 位于 `ALTER TABLE` 关键字之后的 `table_name` 是从中删除列的表名；② 位于 `DROP COLUMN` 关键字之后的 `column_name` 是要删除的列名；③ 您可以省略 `DROP COLUMN` 关键字中的 `COLUMN` 关键字。它是可选的；④ 如果您需要在一个语句中删除多个列，请使用多个使用逗号分隔的 `DROP COLUMN` 子句。

###### 2.3.4、自增列 ######

自增列是 MySQL 中的一个特殊的列，该列的值可由 MySQL 服务器自动生成，并且是一个按升序增长的正整数序列。自增列能够被用来为表的新行产生唯一的标识。

要定义一个自增列，请使用 `AUTO_INCREMENT` 关键字。定义自增列的语法如下：

column_name data_type AUTO_INCREMENT
      ...

这里：① `AUTO_INCREMENT` 关键字标识该列是一个自增列；② 自增列不能使用全部的数据类型，它只适用于整数或者浮点数类型，包括： `TINYINT`、`SMALLINT`、`INT`、`MEDIUMINT`、`BIGINT`、`DECIMAL`、`FLOAT`、`DOUBLE`；③ 自增列必须是主键或者唯一键；④ 自增列默认是 [`NOT NULL`][NOT NULL] 的；⑤ 每个表中只能有一个自增列；⑥ 自增列的初始值是 1。每插入一行到表中，该列的值自动增加 ；⑦ 不像生成列，在插入新行时可以为自增列指定一个值

###### 2.3.5、生成列 ######

在 MySQL 中，生成列（GENERATED COLUMN）是一个特殊的列，它的值会根据列定义中的表达式自动计算得出。并且，你不能直接写入或更新生成列的值。

生成列有 2 种类型：

*  虚拟生成列：列值不会被存储下来。当读取该列时，MySQL 自动计算该列的值；
 *  存储生成列：当插入或修改数据时，MySQL 自动计算该列的值并存储在磁盘上。

要创建生成列，需使用以下语法：

col_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]

语句说明：① `GENERATED ALWAYS` 关键字指示此列是一个生成列。它是可选的；② `AS (expr)` 设置此生成列的表达式；③ `VIRTUAL` 或 `STORED` 关键字指示了是否存储列值。这是可选的；④ `VIRTUAL`: 列值不会被存储。当读取该列时，MySQL 自动计算该列的值。它是默认值；⑤ `STORED`: 当插入或修改数据时，MySQL 自动计算该列的值并存储下来；⑥ 可以在通过 [`CREATE TABLE`][CREATE TABLE] 语句创建表时定义生成列，或者通过 [`ALTER TABLE`][ALTER TABLE] 语句添加一个生成列。

##### 3、MySQL 中约束的 DQL 语句详解 #####

在 MySQL 中，约束是指对表中数据的一种约束，能够帮助数据库管理员更好地管理数据库，并且能够确保数据库中数据的正确性和有效性。

###### 3.1、主键约束 ######

主键约束是使用最频繁的约束。在设计数据表时，一般情况下，都会要求表中设置一个主键。主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。

在 MySQL 中，主键需要遵循以下规则：

*  主键是定义在表上的。一个表不强制定义主键，但最多只能定义一个主键。
 *  主键可以包含一个列或者多个列。
 *  主键列的值必须是唯一的。如果主键包含多个列，则这些列的值组合起来必须是唯一的。
 *  主键列中不能包含 `NULL` 值。

###### 3.1.1、创建主键 ######

我们可以在创建表时定义主键。如下：

CREATE TABLE user (
      id INT PRIMARY KEY,
      name VARCHAR(45)
    );

这里定义了 `id` 列为主键。

上面这种方式适合只有一列作为主键的情况，如果主键包含多列，请使用下面的方式:

CREATE TABLE user (
      com_id INT,
      user_number INT,
      name VARCHAR(45),
      PRIMARY KEY(com_id, user_number)
    );

这里定义一个主键，它包含 `com_id` 和 `user_number` 2 列。

###### 3.1.2、添加主键 ######

如果我们创建表的时候没有设置主键，而现在我们想添加一个主键，请使用下面的方式：

ALTER TABLE user
    ADD PRIMARY KEY(id);

这里为 `user` 表添加了主键，该主键包括 `id` 列。

注意，当你向一个有数据的表中添加主键时，由于主键要求列的值是唯一的并且不能为 `NULL`，如果该列中有重复的值或者 `NULL` 值，则会返回错误。

###### 3.1.3、删除主键 ######

如果我们想删除一个表上主键，请使用下面的方式：

ALTER TABLE user
    DROP PRIMARY KEY;

这里，我们删除了 `user` 表上的主键。

###### 3.1.4、主键值的产生 ######

通常在业务系统中，我们不使用业务字段作为主键，虽然它们也是唯一的。我们一般使用单独的字段作为主键，这主要是出于以下两方面的原因：① 保密业务数据；② 方便这些业务字段的修改。

为了生成唯一的主键值，我们通常采用以下方法：

1.  将主键字段设置为 [`AUTO_INCREMENT`][AUTO_INCREMENT]。声明为 `AUTO_INCREMENT` 的字段会自动生成连续的整数值；
2.  使用 `UUID()` 函数。`UUID()` 函数产生一个长度为 36 个字符的字符串，并且永不重复。`UUID()` 适合用在集群环境下。这样即使一个表被分区在多个服务器上，也不会产生相同的主键的记录；
3.  使用 `UUID_SHORT()` 函数。`UUID_SHORT()` 函数返回一个 64 位无符号整数并全局唯一。

###### 3.1.5、主键 vs 唯一索引 ######

主键和唯一索引都要求值是唯一的，但它们之间存在一些不同：

*  一个表中只能定义一个主键，但是能定义多个唯一索引；
 *  主键中的值不能为 `NULL`，而索引中的值可以为 `NUL`

###### 3.2、外键约束 ######

外键约束经常和主键约束一起使用，用来确保数据的一致性。

外键相对于主键而言，用来引用其他表。外键通过子表的一个或多个列对应到父表的主键或唯一键值，将子表的行和父表行建立起关联关系。

例如，Sakila 示例数据库中的 [`country`][country] 表和 [`city`][city] 表。

下面是它们的关系图：

![image-20240312213702366][]

`country` 表和 `city` 表是一对多的关系。一个国家中可以有多个城市，一个城市只能位于一个国家。

如果一个国家已经有了城市，那么这个你就不能轻易的从 `country` 表删除国家，否则就会造成这个城市数据的不完整。你也不能为一个城市设定一个不存在的 `country_id`，否则这个城市数据就是错误的。

###### 3.2.1、创建外键 ######

通常，外键所属的表被称作子表，被外键引用的表被称作父表。

让我们看一下 `city` 表定义的外键约：

SHOW CREATE TABLE city

CREATE TABLE `city` (
      `city_id` smallint unsigned NOT NULL AUTO_INCREMENT,
      `city` varchar(50) NOT NULL,
      `country_id` smallint unsigned NOT NULL,
      `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`city_id`),
      KEY `idx_fk_country_id` (`country_id`),
      CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

注意其中的部分：

CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`)
        REFERENCES `country` (`country_id`)
        ON DELETE RESTRICT
        ON UPDATE CASCADE

这里定义了一个外键：

1.  位于 `CONSTRAINT` 关键字之后的 `fk_city_country` 是外键的名字。它是可选的；
2.  位于 `FOREIGN KEY` 关键字之后的是作为外键的列名；
3.  位于 `REFERENCES` 关键字之后的是被引用的表和列；
4.  `ON DELETE` 和 `ON UPDATE` 指定了删除或更新被引用的表中的数据时要采取的约束策略。我们可以使用以下 3 个策略中的一个：
    
     *  `CASCADE`：如果被引用的表中的一行被删除或更新，该表中匹配行的值会自动删除或更新。
     *  `SET NULL`：如果被引用的表中的一行被删除或更新，该表中匹配行的值设置为 `NULL`。
     *  `RESTRICT`: 如果被引用的表中的一行在该表中有匹配的行，试图删除或更新被引用的表中行时会引发 MySQL 错误。这是默认的策略。

###### 3.2.2、添加外键 ######

如果建表的时候没有定义外键，你也可以后来通过以下语法添加外键：

ALTER TABLE child_table_name
    ADD [CONSTRAINT foreign_key_name]
      FOREIGN KEY (column))
      REFERENCES parent_table_name (column);
      [ON DELETE | UPDATE RESTRICT]

这里：① 使用 [`ALTER TABLE`][ALTER TABLE] 语句修改表的定义；② 使用 `ADD [CONSTRAINT foreign_key_name]` 添加一个名为 `foreign_key_name` 的约束。`[CONSTRAINT foreign_key_name]` 是可选的；③ 使用 `FOREIGN KEY (column)) REFERENCES parent_table_name (column)` 定义了外键。

###### 3.2.3、删除外键 ######

要删除表上外键，可以采用下面的两种语法之一：

ALTER TABLE table_name
    DROP FOREIGN KEY foreign_key_name;

ALTER TABLE table_name
    DROP CONSTRAINT constraint_name;

这里：① 使用 `ALTER TABLE` 语句修改表的定义；② `DROP FOREIGN KEY` 后面指定外键名，也就是约束名；③ `DROP CONSTRAINT` 后面指定约束名。它可以通过名字删除任何约束，并不仅仅是外键；

###### 3.2.4、CASCADE 策略 ######

如果外键的 `ON DELETE` 和 `ON UPDATE` 使用了 `CASCADE` 策略：

*  当父表的行被删除的时候，子表中匹配的行也会被删除。
 *  当父表的行的键值更新的时候，子表中匹配的行的字段也会被更

###### 3.3、唯一键约束 ######

唯一约束与主键约束有一个相似的地方，就是它们都能够确保列的唯一性。与主键约束不同的是，唯一约束在一个表中可以有多个，并且设置唯一约束的列是允许有空值的，虽然只能有一个空值。例如，在用户信息表中，要避免表中的用户名重名，就可以把用户名列设置为唯一约束。

###### 3.3.1、定义一列为唯一键 ######

要定义唯一键，请使用 UNIQUE 关键字。您可以在创建表的时候定义唯一键或者创建表后通过修改表增加一个唯一键。

这里是创建表时定义一列为唯一列的语法：

CREATE TABLE table_name(
        ...,
        column_name data_type UNIQUE,
        ...
    );

###### 3.3.2、定义多列为唯一键 ######

如果唯一键包含多个列，请使用如下语法：

CREATE TABLE table_name(
       column_name1 column_definition,
       column_name2 column_definition,
       ...,
       [CONSTRAINT constraint_name]
          UNIQUE(column_name1,column_name2)
    );

这里：

*  `UNIQUE` 关键子后的括号中包含了逗号分隔的多个列。
 *  `CONSTRAINT constraint_name` 用于定义一个约束的名称。 它是可选的。如果不定义约束名称， MySQL 会自动为他生成一个。

###### 3.3.3、添加唯一键语法 ######

我们也可以向已有的表中添加一个唯一键。这是添加唯一键的语法：

ALTER TABLE table_name
    ADD [CONSTRAINT constraint_name] UNIQUE (column_list);

注意，如果现有表中的要设置为唯一键的列中已有重复数据，MySQL 服务器会返回错误。

###### 3.3.4、删除唯一键语法 ######

要从表上删除唯一键，你可以使用修改表语句或者删除索引语句：

*  `ALTER TABLE table_name DROP CONSTRAINT constraint_name`
 *  `ALTER TABLE table_name DROP INDEX index_name`
 *  `DROP INDEX index_name ON table_name`

###### 3.4、默认值约束 ######

默认值约束用来约束当数据表中某个字段不输入值时，自动为其添加一个已经设置好的值。例如，在注册学生信息时，如果不输入学生的性别，那么会默认设置一个性别或者输入一个"未知"。

在建表的时候设置默认值：

column_name data_type DEFAULT <默认值>;

在修改表时添加设置默认值：

ALTER TABLE table_name
    CHANGE COLUMN old_column_name new_column_name data_type DEFAULT <默认值>;

删除默认值约束

ALTER TABLE table_name
    CHANGE COLUMN old_column_name new_column_name data_type DEFAULT NULL;

###### 3.5、检查约束 ######

对于任何应用，都对数据的正确性有要求。比如，用户的年龄必须是大于零的，用户的登录名中不能包含空格，用户的密码必须满足一定的复杂度，等等。

对于这些要求，虽然我们可以在应用界面来对用户输入的数据进行验证，但是这并不能替代数据库层面的数据验证。这能增加应用的安全性。

MySQL 提供了 `CHECK` 约束来保证存储到表中的数据是符合你的要求的。不符合 `CHECK` 约束的数据会被拒绝。

下面是 MySQL `CHECK` 的语法：

CHECK(expr)

这里， `expr` 是一个布尔表达式，此表达式针对一行的数据进行计算。如果返回为真，则 MySQL 允许此行插入到表中，否则 MySQL 拒绝此行插入到表中并给出错误。

您可以在 [`CREATE TABLE` 语句][CREATE TABLE]中的列定义或者约束定义中使用 `CHECK` 约束或者在 [`ALTER TABLE` 语句][ALTER TABLE] 中添加 `CHECK` 约束。

如果你在列定义中使用 `CHECK` 约束，则 `CHECK` 表达式只能引用此列。

如果你在使用独立的 `CHECK` 约束，则 `CHECK` 表达式可以应用表上的所有列。

下面以 age 列需要大于 0 为例，使用不同的方法添加此约束：

###### 3.5.1、定义检查列 ######

在创建表的时候在列定义中使用 `CHECK` 约束

CREATE TABLE user (
        id INT AUTO_INCREMENT PRIMARY KEY,
        age INT NOT NULL CHECK(age > 0)
    );

在创建表的时候在表上定义 `CHECK` 约束

CREATE TABLE user (
        id INT AUTO_INCREMENT PRIMARY KEY,
        age INT NOT NULL,
        CONSTRAINT CHECK(age > 0)
    );

###### 3.5.2、修改检查列 ######

使用修改表语句的为表添加 `CHECK` 约束

ALTER TABLE user
    ADD CONSTRAINT CHECK(age > 0);

###### 3.6、非空约束 ######

非空约束用来约束表中的字段不能为空。例如，在学生信息表中，如果不添加学生姓名，那么这条记录是没有用的。

在建表的时候设置非空列：

column_name data_type NOT NULL ...

在修改表时添加设置非空列：

ALTER TABLE table_name
    MODIFY column_name data_type NOT NULL ...;

在修改表时删除设置非空列：

ALTER TABLE table_name
    MODIFY column_name data_type ...;

这里，只需要去掉列定义上的 `NOT NULL` 关键字。

[DELETE]: https://www.sjkjc.com/mysql/delete/
[NOT NULL]: https://www.sjkjc.com/mysql/not-null/
[CREATE TABLE]: https://www.sjkjc.com/mysql/table-create/
[ALTER TABLE]: https://www.sjkjc.com/mysql/table-alter/
[AUTO_INCREMENT]: https://www.sjkjc.com/mysql/auto-increment/
[country]: https://www.sjkjc.com/sakila/table-country/
[city]: https://www.sjkjc.com/sakila/table-city/
[image-20240312213702366]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/458012a3c88d44f291f3a1451363573d.png