MySQL数据表操作-创建数据表(CREATE TABLE)

亦凉 2023-01-12 04:26 381阅读 0赞

## 语法 ##

`CREATE TABLE` 命令语法比较多，其主要是由表创建定义（`create-definition`）、表选项（`table-options`）和分区选项（`partition-options`）所组成的

CREATE [临时表] TABLE <表名> ([表定义选项])[表选项][分区选项];
    
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
        (create_definition,...)
        [table_options]
        [partition_options]

*  `CREATE TABLE`：用于创建给定名称的表，必须拥有表CREATE的权限
 *  `[临时表]`，创建的是临时表，仅在当前会话中可见，并且在关闭会话时会自动删除
 *  `<表名>`，指定数据库是mydb.mytable，半角下的“点”，当前数据库是mytable，mydb和mytable只要符合标识符命名规则就行；如果表存在会出现错误，可以在表名前使用`IF NOT EXISTS`
 *  `[表定义选项]`，由列名（col\_name）、列的定义（column\_definition）以及可能的空值说明、完整性约束或表索引组成，`<列名1> <类型1> [,…] <列名n> <类型n>`，多个列，要用逗号隔开
 *  `[表选项]`，NOT NULL（列的值不能为null）、CHECK（检查是否满足条件可以插入）、DEFAULT（默认值）、PRIMARY KEY（唯一索引主键）、FOREIGN KEY（外键）等等
 *  `[分区选项]`，PARTITION BY、HASH、RANGE等

### 实例 ###

mysql> CREATE TABLE test_db.t_create_table (
        -> r_name varchar(100) NULL,
        -> r_sex varchar(5) NULL,
        -> r_type varchar(5) NULL,
        -> r_no varchar(100) NULL
        -> )
        -> ENGINE=InnoDB
        -> DEFAULT CHARSET=utf8mb4
        -> COLLATE=utf8mb4_general_ci
        -> COMMENT='';
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> show create table test_db.t_create_table \G
    *************************** 1. row ***************************
           Table: t_create_table
    Create Table: CREATE TABLE `t_create_table` (
      `r_name` varchar(100) DEFAULT NULL,
      `r_sex` varchar(5) DEFAULT NULL,
      `r_type` varchar(5) DEFAULT NULL,
      `r_no` varchar(100) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    1 row in set (0.00 sec)
    
    mysql> desc test_db.t_create_table;
    +--------+--------------+------+-----+---------+-------+
    | Field  | Type         | Null | Key | Default | Extra |
    +--------+--------------+------+-----+---------+-------+
    | r_name | varchar(100) | YES  |     | NULL    |       |
    | r_sex  | varchar(5)   | YES  |     | NULL    |       |
    | r_type | varchar(5)   | YES  |     | NULL    |       |
    | r_no   | varchar(100) | YES  |     | NULL    |       |
    +--------+--------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

Reference

[官方 CREATE TABLE Statement][CREATE TABLE Statement]

## HELP详情 ##

mysql> help create table;
    Name: 'CREATE TABLE'
    Description:
    Syntax:
    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 (expr)
    }
    
    column_definition: {
        data_type [NOT NULL | NULL] [DEFAULT default_value]
          [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
          [COMMENT 'string']
          [COLLATE collation_name]
          [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
          [STORAGE {DISK | MEMORY}]
          [reference_definition]
      | data_type
          [COLLATE collation_name]
          [GENERATED ALWAYS] AS (expr)
          [VIRTUAL | STORED] [NOT NULL | NULL]
          [UNIQUE [KEY]] [[PRIMARY] KEY]
          [COMMENT 'string']
          [reference_definition]
    }
    
    data_type:
        (see https://dev.mysql.com/doc/refman/5.7/en/data-types.html)
    
    key_part:
        col_name [(length)] [ASC | DESC]
    
    index_type:
        USING {BTREE | HASH}
    
    index_option: {
        KEY_BLOCK_SIZE [=] value
      | index_type
      | WITH PARSER parser_name
      | COMMENT 'string'
    }	
    
    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: {
        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
      | 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}]
      | 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)
    
    CREATE TABLE creates a table with the given name. You must have the
    CREATE privilege for the table.
    
    By default, tables are created in the default database, using the
    InnoDB storage engine. An error occurs if the table exists, if there is
    no default database, or if the database does not exist.
    
    MySQL has no limit on the number of tables. The underlying file system
    may have a limit on the number of files that represent tables.
    Individual storage engines may impose engine-specific constraints.
    InnoDB permits up to 4 billion tables.
    
    For information about the physical representation of a table, see
    https://dev.mysql.com/doc/refman/5.7/en/create-table-files.html.
    
    URL: https://dev.mysql.com/doc/refman/5.7/en/create-table.html

[CREATE TABLE Statement]: https://dev.mysql.com/doc/refman/5.7/en/create-table.html