Data Definition

ALTER TABLE tbl_name alter_option

alter_option: {
    table_option
  | ADD COLUMN (col_name column_definition,...)
  | DROP COLUMN col_name
  | MODIFY COLUMN col_name column_definition

}

table_option: {
  ENGINE [=] engine_name
| CONVERT TO CHARACTER SET charset_name
}

CREATE DATABASE [IF NOT EXISTS] db_name
    [create_option] ...

create_option: {
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
}

mysql> CREATE DATABASE sbtest1;
Query OK, 4 rows affected (0.01 sec)

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part:
    col_name [(length)]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER NGRAM
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

mysql> CREATE INDEX idx_id_age ON t1(id, age);
Query OK, 0 rows affected (0.17 sec)

CREATE TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

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] ...
}

column_definition: {
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
      [STORAGE {DISK | MEMORY}]
}

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

table_options:
    table_option [[,] table_option] ...

table_option: {
    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 [=] {InnoDB | TokuDB}
  | 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}
  | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
  | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}]
}

partition_options:
      PARTITION BY HASH(shard-key)
    | PARTITION BY LIST(shard-key)(PARTITION backend VALUES IN (value_list),...)
    | SINGLE
    | GLOBAL
    | DISTRIBUTED BY (backend-name)

mysql> CREATE TABLE t1(id int, age int) PARTITION BY HASH(id);
Query OK, 0 rows affected (1.80 sec)

mysql> CREATE TABLE t2(id int, age int) GLOBAL;
Query OK, 0 rows affected (1.80 sec)

mysql> CREATE TABLE t3(id int, age int) SINGLE;
Query OK, 0 rows affected (1.80 sec)

mysql> CREATE TABLE t4(id int, age int,primary key(id));
Query OK, 0 rows affected (1.110 sec)

mysql> CREATE TABLE t5(id int, age int) DISTRIBUTED BY (Bgd2aclf2peds630203362);
Query OK, 0 rows affected (1.110 sec)

mysql> CREATE TABLE h2 (c1 INT, c2 INT) PARTITION BY LIST(c1) (
       PARTITION p0 VALUES IN (1, 4, 7),
       PARTITION p1 VALUES IN (2, 5, 8)
       );
Query OK, 0 rows affected (1.110 sec)

mysql> CREATE TABLE t6(id int, age int) PARTITION BY HASH(id) PARTITIONS 16;
Query OK, 0 rows affected (1.80 sec)

 DROP DATABASE [IF EXISTS] db_name

mysql> DROP DATABASE sbtest1;
Query OK, 0 rows affected (0.02 sec)

  DROP INDEX index_name ON table_name

mysql> DROP INDEX idx_id_age ON t1;
Query OK, 0 rows affected (0.09 sec)

DROP TABLE [IF EXISTS] table_name

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.05 sec)

TRUNCATE TABLE table_name

mysql> insert into t1(a) values(1);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> truncate table t1;
Query OK, 0 rows affected (0.17 sec)

mysql> select * from t1;
Empty set (0.01 sec)