Table¶

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

CREATE OR REPLACE TABLE tbl_name (create_definition, ...)  [table_option] ...

create_definition:
    col_name data_type [column_option] ...
  | PRIMARY KEY index_definition
  | UNIQUE [KEY | INDEX] [idx_name] index_definition
  | {KEY | INDEX} [idx_name] index_definition

data_type:
    BOOL
  | TINYINT [UNSIGNED]
  | SMALLINT [UNSIGNED]
  | {INT | INTEGER} [UNSIGNED]
  | BIGINT [UNSIGNED]
  | TIMESTAMP
  | CHAR[(n)] [COLLATE <collation_name>]
  | VARCHAR[(n)] [COLLATE <collation_name>]
  | BINARY[(n)]
  | VBINARY[(n)]

collation_name:
    BINARY
  | NOCASE

column_option:
    {NOT NULL | NULL} 
  | DEFAULT value
  | AUTO_INCREMENT 
  | [PRIMARY] KEY
  | UNIQUE [KEY]

index_definition: [USING {HASH | BTREE}] (col_name, ...)

table_option:
    ENGINE = MEMORY
  | MAX_ROWS = value
  | MIN_ROWS = value
  | LOCKMODE = {THREAD | PROCESS | NOLOCK}
  | ROWLOCK = {0 | 1}
  | MVCC = {0 | 1}

DROP TABLE [IF EXISTS] tbl_name

SHOW TABLES [LIKE pattern] [WHERE expr]

XDB> show TABLES ;
+---------+--------+-------------+-----------+
| table   | engine | primary_key | data_path |
+---------+--------+-------------+-----------+
| student |        |             |           |
| teacher |        |             |           |
| book    |        |             |           |
+---------+--------+-------------+-----------+
3 rows in set (0.022 ms)

SHOW CREATE TABLE tbl_name

XDB> show CREATE TABLE student ;
+-------------------------------+
| schema                        |
+-------------------------------+
| CREATE TABLE student (        |
|   id               INT,       |
|   name             CHAR(16),  |
|   age              INT,       |
|   class            CHAR(16),  |
|   score            FLOAT,     |
|   info             CHAR(256), |
|   PRIMARY KEY (id),           |
|   KEY         name_2 (name)   |
| );                            |
+-------------------------------+
1 row in set (0.026 ms)

{DESC | DESCRIBE} tbl_name

SHOW COLUMNS [FROM tbl_name] [WHERE expr]

XDB> DESCRIBE student ;
+--------+-------+-----+
| column | type  | len |
+--------+-------+-----+
| id     | INT   | 4   |
| name   | CHAR  | 16  |
| age    | INT   | 4   |
| class  | CHAR  | 16  |
| score  | FLOAT | 4   |
| info   | CHAR  | 256 |
+--------+-------+-----+
6 rows in set (0.023 ms)