常用SQL语法

基本语法

SQL(Structured Query Language)是一种用于管理和操作关系数据库的标准语言,包括数据查询、数据插入、数据更新、数据删除、数据库结构创建和修改等功能。

推荐一个SQL模拟器来测试语法

img

【大小写敏感性】

MySQL 在 9.2.2. 识别符大小写敏感性_MySQL 中文文档 对大小写有如下规定:

  • 关键字函数名不区分大小写。
  • 列的别名索引存储子程序触发器名在任何平台上对大小写不敏感,也不敏感。
  • 数据库名、表名、列名、变量名等在不同操作系统有不同的默认行为,具体如下表所示:
Windows Linux
数据库名 不区分 区分
表名 不区分 区分
表别名 不区分 区分
变量名 不区分 区分

可以通过数据库系统变量lower_case_tables_name来确定如何在硬盘上保存表命名和数据库名字,值的取值和含义如下:

存储 比较 默认系统 备注
0 大小写区分存储 区分 Unix 可以创建大小写不同的同名数据库/表,只能用完全相同大小写的名字来指明数据库/表
1 小写存储 不区分 Windows/MacOS 不能创建大小写不同的同名数据库/表,只能用小写名字来指明数据库表
2 大小写区分存储 不区分

因此为了统一各平台间的情况,一般约定:库名、表名一律用小写字母,不要用大写字母!!!

【字符集和排序规则】

字符集是表示字符的一套规则,而排序规则则定义了字符集中字符的顺序。字符集用来存储数据。一个字符集中可以包含多种排序规则。

一个字符集定义了表示一组字符(并不一定是所有字符)规则。有的字符集只能表示单字节的字符,而有的字符集可以表示多字节的字符。有的字符集始终以多字节表示字符,而有的字符集使用单字节表示单字节字符,使用多字节表示多字节字符。

排序规则则是字符集中的字符的排序逻辑。比如在同一个字符集中的 Aa,在某个排序规则中是 A > a,在另一个排序规则中可能是 A = a 或者 A < a。同一个字符集支持多种排序规则,但是您必须使用其中的一个排序规则。

可以使用SHOW CHARACTER SET;命令查看当前MySQL支持的字符集。以下时常用字符集和默认排序规则。

字符集 默认排序规则 Maxlen 特点描述
ascii ascii_general_ci 1 仅支持基本拉丁字符,适用于纯英文环境,存储效率高。
utf8 utf8_general_ci 3 UTF-8 Unicode 字符集,支持大多数常见字符,但不支持需要 4 字节的字符。
utf8mb4 utf8mb4_0900_ai_ci 4 完全支持 UTF-8 编码,支持所有 Unicode 字符,包括表情符号等 4 字节字符。
latin1 latin1_swedish_ci 1 cp1252 西欧字符集,支持西欧语言字符。
gb2312 gb2312_chinese_ci 2 GB2312 编码,支持简体中文字符。
gbk gbk_chinese_ci 2 GBK 编码,支持简体中文字符。
big5 big5_chinese_ci 2 Big5 编码,支持繁体中文字符。

数据库管理

【创建数据库】

CREATE DATABASE 语句用来创建数据库。以下是 CREATE DATABASE 语句的语法:

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

说明:

  • CREATE DATABASECREATE SCHEMA 的是一样的。

  • CREATE DATABASE 后指定要创建的数据库的名字。

  • IF NOT EXISTS 选项可以避免创建同名数据库导致发生错误。它是可选的。

    如果你给定一个已经存在的数据库名,又没有使用 IF NOT EXISTS 子句,服务器会返回一个错误。

  • CHARACTER SET [=] charset_name 指定数据库的字符集。它是可选的。默认使用数据库服务器的配置。

  • COLLATE [=] collation_name 指定数据库的排序规则。它是可选的。默认使用数据库服务器的配置。

  • ENCRYPTION [=] {'Y' | 'N'} 指定数据库的是否加密。它是可选的。默认使用数据库服务器的配置。

【删除数据库】

DROP DATABASE 语句用来删除数据库,下面显示了该 DROP DATABASE 语句的语法:

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

说明:

  • DROP DATABASEDROP SCHEMA 是一样的。

  • DROP DATABASE 关键字后指定要删除的数据库的名称。

  • IF EXISTS 选项可以避免删除不存在数据库时发生的错误。它是可选的。

    当要删除的数据库中有不存在的数据库时:

    • IF EXISTS 选项,不会对不存在的数据库报错。该语句会删除存在的数据库,并给出不存在数据库的提示。
    • 没有 IF EXISTS 选项,该语句运行失败带有一个指示不能移除不存在的表的错误。该语句不会删除任何表。

【修改数据库】

修改数据库语法如下,只能对存在的数据库进行更改,未指定数据库名时更改当前(默认)数据库。

1
2
3
4
ALTER DATABASE 
[database_name];
[CHARACTER SET charset_name]
[COLLATE collation_name];

【选择数据库】

切换当前(默认)数据库为指定数据库。

1
USE database_name;

【查看数据库】

查看当前数据库服务器上所有数据库。

1
SHOW DATABASES;

查单当前数正在操作(默认)的数据库。

1
SELECT DATABASE();

查看数据库创建信息。

1
SHOW create database database_name;

基本数据类型

数值类型

可以存储有符号和无符号的整数值,整数可以存储有符号和无符号,范围取决于大小,有符号使用补码表示;浮点数遵守IEEE754标准表示。可以在定义时增加 UNSIGNED 关键字以检查存入是否合法(无符号)。

DECIMAL(M , D)类型中,M表示总长度(1~65,默认10),D表示小数位数(0~30,默认0,且不能大于M),以二进制方式存储,然后转换为数值。所有基本计算( +, -, *, / )与 DECIMAL 列相关的内容均以 65 位精度完成。

附加属性:

  • UNSIGNED:检查存入是否未非负值,对于整数将改为源码存取,对于浮点不改变存储范围,仅检查插入。
  • ZEROFILL:对于规定显示位数的值,多余部分用0填充,同时附加上UNSIGNED
  • AUTO_INCREMENT:插入NULL时候自动改为存入当前列最大值+1(最小值为1),存入0与NULL一样,除非启用 NO_AUTO_VALUE_ON_ZERO SQL 模式
类型 大小 用途 别名
BIT(n) n bit(默认1)
TINYINT 1 Bytes 小整数值 BOOL / BOOLEAN
SMALLINT 2 Bytes 大整数值
MEDIUMINT 3 Bytes 大整数值
INT 4 Bytes 大整数值 INTEGER
BIGINT 8 Bytes 极大整数值
FLOAT 4 Bytes 单精度 浮点数值
DOUBLE 8 Bytes 双精度 浮点数值
DECIMAL(M, D) $\lceil M/2 \rceil$ Byte 精度要求非常高的数值

时间类型

DATETIME和TIMESTAMP可以多存储6位小数秒。小数秒的范围在0.000000 ~ 0.499999 之间。

TIMSTAMP字面值在存储的时候会先转化为UTC时间如存入北京时间早上8点先转化为UTC凌晨0点,再存;读取的时候,会将数据转化为当前时区再显示。这个转化通过time_zone变量设置,如在东8区存入 2025-02-09 08:00:00,则会先转化为 UTC 2025-02-09 00:00:00 的 TIMESTAMP 秒存入,在东9区读出这个 TIMESTAMP,得到的是 2025-02-09 09:00:00

类型 大小 ( bytes) 范围 格式 用途
DATE 3 1000-01-01 ~ 9999-12-31 YYYY-MM-DD 日期值
TIME 3 -838:59:59 ~ +838:59:59 HH:MM:SS 时间值或持续时间
YEAR 1 1901~2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 YYYY-MM-DD hh:mm:ss 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC
没有1970-01-01 00:00:00
而是0000-00-00 00:00:00
YYYY-MM-DD hh:mm:ss 混合日期和时间值,时间戳

TIMESTAMPDATETIME 的区别

explicit_defaults_for_timestamp变量(默认为OFF)如果为 TRUE 会将 TIMESTAMP的隐式默认值指明NULL指明NOT NULL特性变得和DATETIME一样。如果为OFF,表中的第一个TIMESTAMP字段自动添加DEFAULT CURRENT_TIMESTAMPON UPDATE CURRENT_TIMESTAMP属性。

属性 TIMESTAMP DATETIME
自动更新 ✅ 支持ON UPDATE CURRENT_TIMESTAMP ⚠️ MySQL 5.6.5+ 支持
显示默认值 ✅ 支持DEFAULT CURRENT_TIMESTAMP ⚠️ MySQL 5.6.5+ 支持
隐式默认值 0000-00-00 00:00:00 NULL
指明 NULL 允许为NULL
存入NULL就是NULL
隐式默认值变为NULL
默认属性就是NULL
NULL就是NULL
指明 NOT NULL 默认属性就是NOT NULL
存入NULL自动转为当前时间
不允许NULL值存入
隐式默认值变为0000-00-00 00:00:00
时区影响 ✅ 受 MySQL 时区影响 ❌ 不受时区影响(存什么读什么)
存储大小 4 字节 8 字节
时间范围 1970-01-01 ~ 2038-01-19 1000-01-01 ~ 9999-12-31

字符串类型

CHAR(n)和VARCHAR(n)在声明时,括号中的指的是允许存入的最大字符数量,其中根据字符集和是否允许NULL,VARCHAR的最大字符数量n不能实际到达65536。CAHR、VARCHAR、TEXT等在使用=搜索的时候都是自动忽略所有尾部空格的,可以使用LIKE查询或者= BINARY搜索来精确查询相同空格。

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

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

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

类型 大小 说明 特点
CHAR(M) M 字符(固定) 定长字符串
M最大为255
取出时不显示末尾空格
存入时候末尾添加空格补足
=搜索无视末尾空格
VARCHAR(M) 1byte ~ M字符+2byte 变长字符串
有一个1~2B的字段存储长度
受到行最大空间65536字节限制
NOT NULL 时最多存储65533个1B字符
NULL 则最多存储65532个1B字符
存取不额外增减末尾空格
=查无视末尾空格
BINARY(M) M bytes 定长字节串 存取搜都保留空格和'\0'
存入时候末尾填充'\0'补足
VARBINARY(M) M+1 bytes 边长字节串
有 1 byte 为终止符
读写搜都保留空格和'\0'
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
TINYTEXT 0-255 bytes 短文本字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
TEXT 0-65 535 bytes 长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据

数据表管理

【创建数据表】

CREATE TABLE 语句用来创建表。 CREATE TABLE 语句的语法如下:

1
2
3
4
5
6
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];

说明:

  • CREATE TABLE 语句创建一个给定名字 table_name 的表。

    • 表名可由字母、数字、下划线和美元符号组成,表名长度在 64 个字符以内。
    • 表名在一个数据库中是唯一的。
    • 新建的表会在当前默认的数据库中。如果还没有选择数据库,请使用 db_name.table_name 格式指定要新建的表所在的数据库。
  • IF NOT EXISTS 指示只有给定的表不存在的时候才进行创建。它是可选的。

    如果你给定一个已经存在的表名,又没有使用 IF NOT EXISTS 子句,服务器会返回一个错误。

  • column_name data_type [NOT NULL | NULL] [DEFAULT expr] [AUTO_INCREMENT] 定义了表中的一列。多个列使用逗号分隔。

    • column_name 是列的名字。列名可由字母、数字、下划线和美元符号组成,列名长度在 64 个字符以内。列名在一个表中是唯一的。
    • data_type 是数据类型,详键基本数据类型。
    • [NOT NULL | NULL] 指示该列是否可以为 NULL。它是可选的。如果不指定该选项,则此列可以为 NULL。如果设置为 NOT NULL,则插入新行时该列必须有值。
    • [DEFAULT expr] 指示该列的默认值。它是可选的。如果不指定该选项,则此列的默认是 NULL
    • [AUTO_INCREMENT] 指示该列是否是一个自增列。如果使用了此选项,则该列的值可有服务器自动产生和填充。该列的值从 1 开始,每增加一个行就会加 1。一个表中只能有一个自增列。
  • [table_constraints] 位于列定义之后,它定义了表的约束。它是可选的。详见表约束。

  • ENGINE=storage_engine 子句指定了表使用的存储引擎。它是可选的。如果不指定此选项,则采用服务器默认的存储引擎。自 MySQL 5.5 版以来,服务器默认的引擎是由 MyISAM 变成了 InnoDB。

  • 当表名或者字段名中含有空格或者其他特殊字字符时,请使用 ` 包围起来。比如: ` test 1`

【删除数据表】

DROP TABLE 语句用来删除表。以下是 DROP TABLE 语句的基本语法:

1
2
DROP TABLE [IF EXISTS]
table_name [, table_name] ...;

说明:

  • DROP TABLE 关键字后面是要删除的表名。如果要删除多个表,请使用逗号分隔表名。

  • IF EXISTS 选项避免了删除不存在的表时发生的错误。它是可选的。

    当要删除的表中有不存在的表时:

    • IF EXISTS 选项,不会对不存在的表报错。该语句会删除存在的表,并给出不存在的表的提示。
    • 没有 IF EXISTS 选项,该语句运行失败带有一个指示不能移除不存在的表的错误。该语句不会删除任何表。
  • DROP TABLE 删除表的定义和表中的数据,以及表上触发器。

  • 你需要具有要删除的每一个表的 DROP 权限。

【修改数据表】

ALTER TABLE 语句,您可以重命名表、重命名列、添加列、删除列、修改列的属性等。以下是ALTER TABLE 基本语法:

1
2
ALTER TABLE table_name
[alter_action options], ...;

其中 alter_action 是一个修改动作,包括:

  • 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 [TO] new_tbl_name: 重命名表。

【查看数据表】

查看指定数据库内所有数据表,默认为当前书库:

1
SHOW TABLES [FROM database_name];

查看某个数据表的列信息:

1
SHOW [FULL] COLUMNS FROM table_name;

查看某个数据表的定义语句:

1
SHOW CREATE TABLE table_name;

【清空数据表】

DELETE * FROM table 还可以使用 TRUNCATE TABLE 语句。其语法如下:

1
TRUNCATE [TABLE] table_name;

虽然 TRUNCATEDELETE 类似,但是他们在以下几个方面存在不同:

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

表约束

主键

在关系数据库中,主键是能够唯一标识表中的每一行的一个字段或者多个字段的组合。在 MySQL 中,主键需要遵循以下规则:

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

定义主键

可以在创建表的时候添加主键。在列定义中添加PRIMARY KEY或者在表约束中添加,也可以在创建完表后设置主键,语法如下:

注意,当你向一个有数据的表中添加主键时,要求列(的组合)的值是唯一的并且不能为 NULL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#定义主键
CREATE TABLE table_name (
column_name data_type PRIMARY KEY,

[CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
);

# 添加主键
ALTER TABLE table_name
ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...

# 删除主键
ALTER TABLE table_name DROP CONSTRAINT constaint_name;
ALTER TABLE table_name DROP PRIMARY KEY;

解释:

  • CONSTRAINT [symbol] PRIMARY KEY

    • 添加主键约束。

    • symbol是约束名称,可选。

  • index_typeUSING {BTREE | HASH}

    • BTREE:表示使用 B 树索引,这是 MySQL 默认的索引类型,适用于大多数存储引擎。
    • HASH:表示使用哈希索引,适用于 MEMORY 存储引擎。
  • key_partcol_name [(length)] [ASC | DESC]
    • col_name:指定列的名称。
    • length:可选项,指定索引前缀的长度。对于字符串类型的列,可以只索引前 length 个字符。对于 BLOB 和 TEXT 类型的列,必须指定前缀长度。
    • ASCDESC:可选项,指定索引的排序顺序。ASC 表示升序,DESC 表示降序。
  • index_optionKEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string'
    • KEY_BLOCK_SIZE:可选项,指定索引的块大小。
    • index_type:可选项,指定索引的类型,如 BTREEHASH
    • WITH PARSER parser_name:可选项,指定用于全文索引的解析器。
    • COMMENT 'string':可选项,为索引添加注释。

产生主键

通常在业务系统中,我们不使用业务字段作为主键,虽然它们也是唯一的。我们一般使用单独的字段作为主键,这主要是出于以下两方面的原因:

  1. 保密业务数据
  2. 方便这些业务字段的修改

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

  1. 将主键字段设置为 AUTO_INCREMENT

    声明为 AUTO_INCREMENT 的字段会自动生成连续的整数值。

  2. 使用 UUID() 函数。

    UUID() 函数产生一个长度为 36 个字符的字符串,并且永不重复。

  3. 使用 UUID_SHORT() 函数。

    UUID_SHORT() 函数返回一个 64 位无符号整数并全局唯一。

主键vs唯一索引

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

  • 一个表中只能定义一个主键,但是能定义多个唯一索引。
  • 主键中的值不能为 NULL,而索引中的值可以为 NULL

唯一键

唯一键/唯一索引保证一列或几列的值是唯一的。主键列不能包含 NULL 值,而唯一键列可以包含 NULL 值。NULL可以重复。

定义唯一键

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE table_name(
# 在列定义中添加唯一键属性
column_name data_type UNIQUE [KEY],

# 在表约束中添加唯一键约束
[CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
);

# 添加唯一键
ALTER TABLE table_name
ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...;

# 删除唯一键
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
ALTER TABLE table_name DROP {INDEX | KEY} index_name;
DROP INDEX index_name ON table_name;

解释:

  • CONSTRAINT [symbol] PRIMARY KEY

    • 添加唯一约束。

    • symbol是约束名称,可选。

外键

在关系数据库中,外键用来定义两个实体之间的约束关系。外键对保证数据的完整性很有用。外键相对于主键而言,用来引用其他表。外键通过子表的一个或多个列对应到父表的主键或唯一键值,将子表的行和父表行建立起关联关系。

定义外键

简单来说,子表中外键的值是父表对应键的子集。外键可以在表创建时定义,也可以在建表后添加,语法如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
CREATE TABLE table_name (
# 列定义中添加
column_name data_type
REFERENCES referenced_column (referenced_column, ...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}],

# 在表约束中添加
CONSTRAINT [symbol] FOREIGN KEY
[index_name] (col_name,...)
REFERENCES referenced_table (referenced_column, ...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
);

# 添加外键
ALTER TABLE table_name
ADD FOREIGN KEY
[index_name] (col_name,...)
REFERENCES referenced_table (referenced_column, ...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}];

# 删除外键
ALTER TABLE table_name
DROP FOREIGN KEY fk_symbol;

# 删除约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
ALTER TABLE table_name DROP {INDEX | KEY} index_name;
DROP INDEX index_name ON table_name;

解释:

  • CONSTRAINT [symbol] FOREIGN KEY
    • 添加外键约束。
    • symbol是约束名称,可选。
  • FOREIGN KEY [index_name] (column_name, ...)
    • 指定当前表中的一个或多个列(column_name, ...)作为外键。
    • index_name是外键名称,可选
  • REFERENCES referenced_table (referenced_column):
    • 指定父表(referenced_table)及被引用列(referenced_column)。
    • 这表示当前表的外键列引用了被引用表的主键或唯一键列。
  • MATCH {FULL | PARTIAL | SIMPLE}
    • 定义外键匹配类型。
    • MATCH FULL 表示所有外键列必须与被引用表的所有列匹配;
    • MATCH PARTIAL 表示外键列可以与被引用表的部分列匹配;
    • MATCH SIMPLE 是默认值,表示外键列与被引用表的列一一对应。
  • ON DELETE
    • 定义当被引用表的记录被删除时,当前表的外键列的行为。
  • ON UPDATE
    • 定义当被引用表的记录被更新时,当前表的外键列的行为。

外键行为

可供选择的外键行为包括:RESTRICT、CASCADE、SET NULL、NO ACTION、SET DEFAULT,其功能如下:

  • RESTRICT
    • 禁止删除父表中与子表匹配的行。
    • 禁止删除父表中与子表匹配的行的键的值。
  • CASCADE
    • 删除父表的记录时,自动删除当前表中所有引用该记录的行。
    • 更新被引用表的记录时,自动更新当前表中所有引用该记录的值。
  • SET NULL
    • 当父表的行被删除的时候,子表中匹配的行的外键的值被设置为 NULL
    • 当父表的行的键值被更新的时候,子表中匹配的行的列的值被设置为 NULL
  • NO ACTION
    • RESTRICT 类似,但在某些数据库系统中,NO ACTIONRESTRICT 的行为可能有所不同。
  • SET DEFAULT
    • 删除父表的记录时,子表的匹配行的外键的值设为默认值。
    • 更新父表的记录时,子表的匹配行的外键的值设为默认值。

CHECK约束

对于任何应用,都对数据的正确性有要求。比如,用户的年龄必须是大于零的,用户的登录名中不能包含空格,用户的密码必须满足一定的复杂度,等等。CHECK 约束来保证存储到表中的数据是符合你的要求的。不符合 CHECK 约束的数据会被拒绝。直到 MySQL 8.0.16,MySQL 才真正的支持 CHECK 约束。

定义 CHECK 约束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE table_name (
# 列定义中添加
column_name data_type
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED],

# 在表约束中添加
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
);

# 添加
ALTER TABLE table_name
ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

# 删除约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
ALTER TABLE table_name DROP {INDEX | KEY} index_name;
DROP INDEX index_name ON table_name;

解释:

  • CHECK (expression):定义约束条件的表达式。
  • NOT ENFORCED:可选项,指示约束不被强制执行。

锁机制

加锁保持数据一致性,这个没什么好说的,和所有课程的锁的概念差不多。但是只能一次性申请/释放所需的所有的锁,以此来破坏以破坏死锁发生的必要条件之一(请求和保持)。表锁相关的语法如下:

1
2
3
4
5
LOCK {TABLE | TABLES}
tbl_name [[AS] alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, tbl_name [[AS] alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...

UNLOCK {TABLE | TABLES}

解释:

  • LOCK {TABLE | TABLES}:对以下表申请锁
  • table_name [[AS] alias]:申请锁的表的名称/别名
  • 四种不同锁类型:
    • READ读锁,其他会话不能写表。
    • READ LOCAL其他会话可以写表,但是本地无法读取到。
    • WRITE 写锁,
    • LOW_PRIORITY WRITE读优先的写锁,等待们没有读操作再获得锁

READ锁

  • 持有表锁的会话只能读取表,但不能写入表。
  • 多个会话可以同时获取一个表的 READ 锁。
  • 其他会话无需显式获取 READ 锁即可读取该表,但是不能写入表。
  • 其他会话的写操作会一直等待知道读锁被释放。

WRITE锁

  • 持有锁的会话可以读写表。
  • 只有持有锁的会话才能访问该表。在释放锁之前,没有其他会话可以访问它。
  • 持有 WRITE 锁时,其他会话对表的锁请求会阻塞。

参考文献

  1. MySQL 中文文档 | MySQL 中文网
  2. SQL 语法 | 菜鸟教程
  3. MySQL 教程