MySQL之DDL语句

GrowthDBA 2021-08-13

174

DDL 是 DBMS 的核心组件，也是 SQL 的重要组成部分，DDL 的正确性和稳定性是整个 SQL 运行的重要基础。回顾一下以前的知识，DDL 的英文全称是 Data Definition Language，中文是数据定义语言。它定义了数据库的结构和数据表的结构。Let's GO!~

官档地址：https://dev.mysql.com/doc/refman/8.0/en/sql-data-definition-statements.html

数据库定义语句

创建数据库语法：

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
  | ENCRYPTION [=] {'Y' | 'N'}
}
复制

CREATE DATABASE
创建具有给定名称的数据库。 CREATE SCHEMA
是 CREATE DATABASE的同义词。如果数据库存在而没有指定 IF NOT EXISTS，则会发生错误。

e.g.

/* 创建一个名为employees、字符编码为utf8mb4的数据库或实例 */
[root@localhost][(none)]> CREATE DATABASE `employees` DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.02 sec)
/* 数据库存在而没有指定IF NOT EXISTS则会发生错误 */
[root@localhost][(none)]> CREATE DATABASE `employees` DEFAULT CHARACTER SET utf8mb4;
ERROR 1007 (HY000): Can't create database 'employees'; database exists
/* 数据库存在指定IF NOT EXISTS则会产生警告 */
[root@localhost][(none)]> CREATE DATABASE IF NOT EXISTS `employees` DEFAULT CHARACTER SET utf8mb4;
Query OK, 1 row affected, 1 warning (0.00 sec)
/* 警告信息如下 */
[root@localhost][(none)]> SHOW WARNINGS;
+-------+------+----------------------------------------------------+
| Level | Code | Message                                            |
+-------+------+----------------------------------------------------+
| Note | 1007 | Can't create database 'employees'; database exists  |
+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)
复制

删除数据库语法：

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
复制

DROP DATABASE
删除数据库中的所有表并删除数据库。 DROP SCHEMA
是DROP DATABASE的同义词。IF EXISTS 用于防止在数据库不存在时发生错误。

e.g.

/* 删除名为employees的数据库及其所有表数据 */
[root@localhost][(none)]> DROP DATABASE `employees`;
Query OK, 0 rows affected (0.02 sec)
/* 数据库不存在，DROP操作未指定IF EXISTS报错 */
[root@localhost][(none)]> DROP DATABASE `employees`;
ERROR 1008 (HY000): Can't drop database 'employees'; database doesn't exist
/* 数据库不存在，DROP操作指定IF EXISTS无报错 */
[root@localhost][(none)]> DROP SCHEMA IF EXISTS `employees`;
Query OK, 0 rows affected, 1 warning (0.01 sec)
复制

哈哈

，这个操作就是传说中的删库跑路，作为一个具备职业操守的计算机行业人员，这种事情应尽量避免。

小提示

很遗憾，MySQL原生不支持修改库名的语法。

[root@localhost][(none)]> RENAME DATABASE test_clone TO test_clone_bak;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DATABASE test_clone TO test_clone_bak' at line 1
复制

并不是没有办法，也有一些技巧可以修改库名，但是当数据量很大的时候，方法1虽然保证了数据安全，但是出于耗时、磁盘空间等硬件成本方面考虑，尽量还是谨慎操作。故，在数据库设计的时候就要想好库名，防止后期不必要的麻烦。下面介绍两种“修改库名”的小技巧：

方法 1

1.创建所需名字的新数据库；
2.通过mysqldump导出旧数据库数据；
3.通过mysql -d[新数据库] < [步骤2导出旧数据库数据文件.sql]，指定新库导入旧库数据；
4.删除旧库。
注意：这种方法虽然安全，但是如果数据量大，会比较耗时，同时还需要考虑到磁盘空间等硬件成本。
复制

方法 2

#!/bin/bash
# 假设将db_old数据库名改为db_new
# MyISAM直接更改数据库目录下的文件即可

mysql -uroot -p123456 -e 'CREATE DATABASE IF NOT EXISTS db_new'
list_table=$(mysql -uroot -p123456 -Nse "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='db_old'")

for table in $list_table
do
    mysql -uroot -p123456 -e "RENAME TABLE db_old.$table to db_new.$table"
done
复制

方法2的技巧点在于，通过RENAME实现跨库移动操作，需要按照[OLD_SCHEMA_NAME].[TABLE_NAME] → [NEW_SCHEMA_NAME].[TABLE_NAME]格式来指定，如有变更库名的需求，推荐使用这种方式操作，执行速度非常快。

-Nse为MySQL客户端可执行文件的选项参数，解释如下：

-N, --skip-column-names 不显示列信息
-s, --silent 一行一行输出，中间有[Tab]分隔符
-e, --execute=name 执行mysql的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 11, 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}
  | 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:
    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]

query_expression:
    SELECT ... (Some valid select or union statement)
复制

e.g.

/* 创建一张学生表 */
CREATE TABLE `student` (
    `student_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '学生编号',
    `student_name` VARCHAR(20) NOT NULL COMMENT '学生姓名',
    `address` VARCHAR(100) NULL COMMENT '家庭住址',
    PRIMARY KEY(`student_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT='学生表';
复制

修改表结构语法：

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)
复制

e.g.

/* 在学生表中添加年龄age字段 */
ALTER TABLE `student` ADD COLUMN `age` INT(11) NULL COMMENT '学生年龄';
/* 将age字段名改成student_age */
ALTER TABLE `student` RENAME COLUMN `age` TO `student_age`;
/* 调整student_age字段的数据类型为FLOAT(3,1) */
ALTER TABLE `student` MODIFY `student_age` FLOAT(3,1) NULL COMMENT '学生年龄'; 
/* 删除student_age字段 */
ALTER TABLE `student` DROP COLUMN `student_age`;
复制

删除表语法：

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]
复制

TRUNCATE [TABLE] tbl_name
复制

这里需要注意，删除表的DDL语句有两个，区别是：DROP操作会将整个表（数据+表结构定义）全部清除，TRUNCATE操作只会清空表数据而保留表结构定义（相当于DROP+CREATE），两个删除操作都会释放磁盘空间。

修改表名语法：

ALTER TABLE [旧表名] RENAME TO [新表名];
RENAME TABLE [旧表名1] TO [新表名1], [旧表名2] TO [新表名2], ... [旧表名n] TO [新表名n];
复制

小提示

再回顾和补充一些表、表字段操作的语法，并非所有的语句都是DDL语句，但是涵盖了日常中经常使用到的操作：

查看表结构：

DESCRIBE [表名];
DESC [表名];
EXPLAIN [表名];
SHOW COLUMNS FROM [表名];
SHOW FIELDS FROM [表名];
复制

上述这些语句，触发的显示效果都是一样的。

添加字段到第一列的位置：

ALTER TABLE [表名] ADD COLUMN [字段名] [字段的类型] [字段的属性] FIRST;
复制

添加字段至指定字段的后面：

ALTER TABLE [表名] ADD COLUMN [字段名] [字段的类型] [字段的属性] AFTER [指定字段名];
复制

修改字段：

ALTER TABLE [表名] CHANGE [旧字段名] [新字段名] [新数据类型] [新属性];
复制

修改字段排列的位置为第一列：

ALTER TABLE [表名] MODIFY [字段名] [字段的类型] [字段的属性] FIRST;
复制

修改字段排列的位置为指定字段的后面：

ALTER TABLE [表名] MODIFY [字段名] [字段的类型] [字段的属性] AFTER [指定字段名];
复制

小结

今天介绍了对库、表、字段定义的DDL语句，官档中提供的庞大语法，真实工作中很多都使用不到。对于一些日常工作使用率较高的语句进行了举例，但DDL语句不仅是针对库、表、字段操作，还有对存储过程、函数、事件、触发器等功能的管理操作，后面会针对本次未涉及的内容进行单独详解，且听下回分解。DDL在DBA的工作中占比较大，对于某些DDL操作，还会发生锁表的情况，对于业务来说，这是绝对不允许的，所以数据库版本在迭代的过程中衍生出很多新功能，来避免一些数据库使用中对业务造成的影响，其中Online DDL就是其一，后期，也会开辟单独文章进行讲解。拭目以待吧！~

end

mysql

文章转载自GrowthDBA，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL之DDL语句

评论

相关阅读