Appearance
MySQL DDL
Note：DDL (Data Definition Language)。
1. 数据库操作
1.1. 查询所有数据库
SQL
SHOW {DATABASES | SCHEMAS}
[LIKE 'pattern' | WHERE expr]1
2
2
1.2. 查询当前数据库
SQL
SELECT DATABASE();https://dev.mysql.com/doc/refman/8.4/en/information-functions.html#function_database
1.3. 创建数据库
SQL
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
| ENCRYPTION [=] {'Y' | 'N'}
}1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
https://dev.mysql.com/doc/refman/8.4/en/create-database.html
1.4. 删除数据库
SQL
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name1.5. 使用数据库
SQL
USE db_name;2. 表操作
2.1. 查询所有表
SQL
SHOW [EXTENDED] [FULL] TABLES
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]1
2
3
2
3
2.2. 查询表结构
SQL
DESC tbl_name;DESC 实际上是 DESCRIBE 单词的缩写，它们与 EXPLAIN 同义。完整的语法如下：
SQL
{EXPLAIN | DESCRIBE | DESC}
tbl_name [col_name | wild]
{EXPLAIN | DESCRIBE | DESC}
[explain_type] [INTO variable]
{[schema_spec] explainable_stmt | FOR CONNECTION connection_id}
{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] [schema_spec] select_statement
explain_type: {
FORMAT = format_name
}
format_name: {
TRADITIONAL
| JSON
| TREE
}
explainable_stmt: {
SELECT statement
| TABLE statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
schema_spec:
FOR {SCHEMA | DATABASE} schema_name1
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
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
Bash
mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| Country | char(3) | NO | UNI | | |
| District | char(20) | YES | MUL | | |
| Population | int(11) | NO | | 0 | |
+------------+----------+------+-----+---------+----------------+2.3. 创建表结构
SQL
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
[IGNORE | REPLACE]
[AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition: {
col_name column_definition
| {INDEX | KEY} [index_name] [index_type] (key_part,...)
[index_option] ...
| {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| check_constraint_definition
}
column_definition: {
data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
[VISIBLE | INVISIBLE]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
[ENGINE_ATTRIBUTE [=] 'string']
[SECONDARY_ENGINE_ATTRIBUTE [=] 'string']
[STORAGE {DISK | MEMORY}]
[reference_definition]
[check_constraint_definition]
| data_type
[COLLATE collation_name]
[GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[VISIBLE | INVISIBLE]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[reference_definition]
[check_constraint_definition]
}
data_type:
(see Chapter 13, Data Types)
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
|ENGINE_ATTRIBUTE [=] 'string'
|SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
}
check_constraint_definition:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
reference_definition:
REFERENCES tbl_name (key_part,...)
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
table_option [[,] table_option] ...
table_option: {
AUTOEXTEND_SIZE [=] value
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| ENGINE_ATTRIBUTE [=] 'string'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| START TRANSACTION
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value
| tablespace_option
| UNION [=] (tbl_name[,tbl_name]...)
}
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
tablespace_option:
TABLESPACE tablespace_name [STORAGE DISK]
| [TABLESPACE tablespace_name] STORAGE MEMORY
query_expression:
SELECT ... (Some valid select or union statement)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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
SQL
CREATE TABLE `table_name` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`val` int(11) unsigned DEFAULT NULL COMMENT 'value comment',
`txt` varchar(2000) NOT NULL COMMENT 'text comment',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='table comment'SQL
CREATE TABLE t1 (
year_col INT,
some_data INT
)
PARTITION BY RANGE (year_col) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1995),
PARTITION p2 VALUES LESS THAN (1999),
PARTITION p3 VALUES LESS THAN (2002),
PARTITION p4 VALUES LESS THAN (2006),
PARTITION p5 VALUES LESS THAN MAXVALUE
);2.4. 修改表结构
SQL
ALTER TABLE tbl_name
[alter_option [, alter_option] ...]
[partition_options]
alter_option: {
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX | KEY} [index_name]
[index_type] (key_part,...) [index_option] ...
| ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
(key_part,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (key_part,...)
[index_option] ...
| ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
[index_name] [index_type] (key_part,...)
[index_option] ...
| ADD [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (col_name,...)
reference_definition
| ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
| DROP {CHECK | CONSTRAINT} symbol
| ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
| ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
| ALTER [COLUMN] col_name {
SET DEFAULT {literal | (expr)}
| SET {VISIBLE | INVISIBLE}
| DROP DEFAULT
}
| ALTER INDEX index_name {VISIBLE | INVISIBLE}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST | AFTER col_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| {DISABLE | ENABLE} KEYS
| {DISCARD | IMPORT} TABLESPACE
| DROP [COLUMN] col_name
| DROP {INDEX | KEY} index_name
| DROP PRIMARY KEY
| DROP FOREIGN KEY fk_symbol
| FORCE
| LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| ORDER BY col_name [, col_name] ...
| RENAME COLUMN old_col_name TO new_col_name
| RENAME {INDEX | KEY} old_index_name TO new_index_name
| RENAME [TO | AS] new_tbl_name
| {WITHOUT | WITH} VALIDATION
}
partition_options:
partition_option [partition_option] ...
partition_option: {
ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| DISCARD PARTITION {partition_names | ALL} TABLESPACE
| IMPORT PARTITION {partition_names | ALL} TABLESPACE
| TRUNCATE PARTITION {partition_names | ALL}
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION]
| ANALYZE PARTITION {partition_names | ALL}
| CHECK PARTITION {partition_names | ALL}
| OPTIMIZE PARTITION {partition_names | ALL}
| REBUILD PARTITION {partition_names | ALL}
| REPAIR PARTITION {partition_names | ALL}
| REMOVE PARTITIONING
}
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option: {
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
}
table_options:
table_option [[,] table_option] ...
table_option: {
AUTOEXTEND_SIZE [=] value
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'}
| CONNECTION [=] 'connect_string'
| {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| ENCRYPTION [=] {'Y' | 'N'}
| ENGINE [=] engine_name
| ENGINE_ATTRIBUTE [=] 'string'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
| SECONDARY_ENGINE_ATTRIBUTE [=] 'string'
| STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
| STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
| STATS_SAMPLE_PAGES [=] value
| TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
| UNION [=] (tbl_name[,tbl_name]...)
}
partition_options:
(see CREATE TABLE options)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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
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
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
SQL
ALTER TABLE t1 CHANGE b b INT NOT NULL;2.5. 删除表结构
SQL
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]1
2
3
2
3
3. 所有 SHOW 语句
关于 SHOW 语句的完整语法说明请参考官方说明文档。
3.1. 查询所有对象
| 语句 | 说明 |
|---|---|
SHOW DATABASES | 显示服务器上所有数据库的列表 |
SHOW ENGINES | 显示服务器支持的所有存储引擎及其当前状态 |
SHOW PLUGINS | 显示服务器中已安装的插件及其状态 |
SHOW VARIABLES | 显示服务器系统变量及其当前值，这些变量用于配置服务器行为 |
SHOW EVENTS | 显示当前数据库中的所有计划事件 |
SHOW TABLES | 显示当前数据库中所有表的列表 |
SHOW COLUMNS | 显示某个表的列信息，包括列名、数据类型、是否允许为空等 |
SHOW TRIGGERS | 显示当前数据库中的所有触发器及其定义 |
3.2. 查询所有字符集及排序规则
| 语句 | 说明 |
|---|---|
SHOW CHARACTER SET | 显示服务器支持的字符集及其默认排序规则 |
SHOW COLLATION | 显示服务器支持的排序规则（Collation），包括关联的字符集 |
3.3. 查看对象脚本
| 语句 | 说明 |
|---|---|
SHOW CREATE DATABASE | 显示用于创建指定数据库的 CREATE DATABASE 语句 |
SHOW CREATE USER | 显示用于创建指定用户的 CREATE USER 语句，包括权限信息 |
SHOW CREATE TABLE | 显示用于创建指定表的 CREATE TABLE 语句，包括表的定义和索引 |
SHOW INDEX | 显示指定表的索引信息，包括索引名、列名、唯一性等 |
SHOW CREATE TRIGGER | 显示用于创建指定触发器的 CREATE TRIGGER 语句 |
SHOW CREATE VIEW | 显示用于创建指定视图的 CREATE VIEW 语句 |
SHOW CREATE PROCEDURE | 显示用于创建指定存储过程的 CREATE PROCEDURE 语句 |
SHOW PROCEDURE CODE | 显示指定存储过程的代码（仅存储过程内部代码） |
SHOW CREATE FUNCTION | 显示用于创建指定存储函数的 CREATE FUNCTION 语句 |
SHOW FUNCTION CODE | 显示指定存储函数的代码（仅存储函数内部代码） |
SHOW CREATE EVENT | 显示用于创建指定事件的 CREATE EVENT 语句 |
3.4. 查询状态
| 语句 | 说明 |
|---|---|
SHOW STATUS | 显示服务器的状态变量及其当前值，提供关于服务器性能的信息 |
SHOW ENGINE | 显示存储引擎的状态信息，通常用于分析特定存储引擎的运行情况 |
SHOW OPEN TABLES | 显示当前打开的表列表及其锁状态 |
SHOW TABLE STATUS | 显示当前数据库中所有表的状态信息，包括表类型、行数、数据文件大小等 |
SHOW PROCEDURE STATUS | 显示服务器上所有存储过程的状态信息，包括数据库、过程名、创建日期等 |
SHOW FUNCTION STATUS | 显示服务器上所有存储函数的状态信息，包括数据库、函数名、创建日期等 |
3.5. 性能分析
| 语句 | 说明 |
|---|---|
SHOW PROCESSLIST | 显示当前服务器上所有活动线程的列表及其状态 |
SHOW PROFILE | 显示某个 SQL 查询的详细执行性能信息，分析 SQL 性能 |
SHOW PROFILES | 显示最近执行的查询的性能概要信息 |
3.6. 日志相关
| 语句 | 说明 |
|---|---|
SHOW ERRORS | 显示最近执行语句中的错误信息，与 SHOW WARNINGS 类似，但仅显示错误 |
SHOW WARNINGS | 显示最近执行语句中的警告和错误信息 |
SHOW BINARY LOGS | 显示服务器上所有二进制日志文件的列表及其大小。这些日志用于复制和恢复 |
SHOW BINLOG EVENTS | 显示指定二进制日志文件中的事件，通常用于分析日志内容 |
3.7. 主从复制相关
| 语句 | 说明 |
|---|---|
SHOW MASTER STATUS | 显示主服务器的二进制日志信息，通常用于复制设置 |
SHOW SLAVE, REPLICA STATUS | 显示从服务器的详细复制状态，SHOW SLAVE STATUS 是旧语法 |
SHOW SLAVE HOSTS, SHOW REPLICAS | 显示所有从服务器的连接信息，SHOW SLAVE HOSTS 是旧语法 |
SHOW REPLICAS | 显示复制环境中所有从服务器的信息（MySQL 8.0.22 开始使用） |
SHOW REPLICA STATUS | 显示从服务器的复制状态（MySQL 8.0.22 及更高版本） |
SHOW RELAYLOG EVENTS | 显示中继日志文件中的事件，通常用于复制环境 |
3.8. 用户权限
| 语句 | 说明 |
|---|---|
SHOW PRIVILEGES | 显示服务器支持的所有权限类型 |
SHOW GRANTS | 显示指定用户的权限授予信息 |