DDL语句

Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段)。

1. 创建表

1.1 建表语法

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) }

表属性指定：

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.1 创建临时表

mysql除了自身会在查询的使用在内存中创建临时表，也开放给用户可以手动创建临时表, 它是基于会话级别：

create temporary table sql_trains.t_person(
    id int,
    name varchar(30)
);

也就意味着不同的用户可以创建相同的表，用户创建的临时表和mysql系统创建的表区别在于引擎不一样，mysql创建的临时表引擎是Memory: 创建的临时表表结构默认存储在/tmp目录下的xxx.frm文件中，临时表数据默认存储在mysql数据目录下的ibtmp1文件下，它也就是临时表空间：

1.2 外键约束

考虑到性能开销，一般用的比较少。 在外键约束中可以设置不同的删除或更新行为：

• RESTRICT：在试图删除或更新主表中的记录时，如果该记录在子表中被引用，则操作会被拒绝(失败)
• CASCADE：在删除或更新主表中的记录时，相关的子表记录也会被自动删除或更新
• SETNULL： 当主表中的记录被删除或更新时，相应的子表中引用该记录的外键字段将被设置为NULL
• NO ACTION：删除或更新主表记录时，如果有子表记录引用该主表记录，则操作失败，和RESTRICT类似

create table product(
    category int not null ,
    id int not null,
    price DECIMAL,
    PRIMARY KEY(category, id)
) engine=INNODB;

create table customer(
    id int 	not null PRIMARY key
) engine=INNODB;

create table product_order(
    no int not null auto_increment,
    product_category int not null,
    product_id int not null,
    customer_id int not null,
    PRIMARY key(no),
    index(product_category, product_id),
    index(customer_id),
    FOREIGN KEY (product_category,product_id)
    REFERENCES product(category, id)
    on UPDATE cascade on delete RESTRICT,
    FOREIGN key (customer_id) 
    REFERENCES customer(id) 
) ENGINE =INNODB;

2. 修改表

修改表的语法如下：
需要注意的是一下操作会造成表的操作阻塞，既不可写也不可读：
参考Online DDL Operations

如果遇到修改表内存不足，需要修改参数：

-- 如果更改表结构的最大内存大小
show variables like '%innodb_online_alter_log_max_size%';

3. 删除表

DROP TABLE [ IF EXISTS ] 表名;

可选项 IF EXISTS 代表，只有表名存在时才会删除该表，表名不存在，则不执行删除操作(如果不加该参数项，删除一张不存在的表，执行将会报错)。

3.1 删除指定表, 并重新创建表

TRUNCATE TABLE 表名;