MySQL 数据表创建详细SQL语法

来源：互联网发布：mac dmg 沙盒编辑：程序博客网时间：2024/06/10 18:14

Table 数据表创建

数据表创建

#语法规则1CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name(create_definition, ...)[table_options][partion_options]#语法规则2CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name[(create_definition, ...)][table_options][partion_options]select_statement#语法规则3CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name{LIKE old_tbl_name | (LIKE old_tbl_name)}#create_definition 语法 create_definition:    col_name column_definition    | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, ...)        [index_option] ....    | {INDEX|KEY} {index_name} [index_type] (index_col_name)        [index_option] ...    | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]        [index_name] [index_type] (index_col_name)        [index_option] ...    | [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (index_col_name, ....)      [index_option] ...    | [CONSTRAINT [symbol]] FOREIGN KEY       [index_name] (index_col_name, ...) reference_definition    | CHECK (EXPR)#column_definition 语法column_definition:    data_type [NOT NULL | NULL] [DEFAULT default_value]      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]      [COMMENT 'string']      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]      [STORAGE {DISK|MEMMORY|DEFAULT}]      [reference_definition]#data_type 语法 data_type:    BIT[(length)]  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]  | INT[(length)] [UNSIGNED] [ZEROFILL]  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]  | DATE  | TIME  | TIMESTAMP  | DATETIME  | YEAR  | CHAR[(length)] [BINARY]      [CHARACTER SET charset_name] [COLLATE collation_name]  | VARCHAR(length) [BINARY]      [CHARACTER SET charset_name] [COLLATE collation_name]  | BINARY[(length)]  | VARBINARY(length)  | TINYBLOB  | BLOB  | MEDIUMBLOB  | LONGBLOB  | TINYTEXT [BINARY]      [CHARACTER SET charset_name] [COLLATE collation_name]  | TEXT [BINARY]      [CHARACTER SET charset_name] [COLLATE collation_name]  | MEDIUMTEXT [BINARY]      [CHARACTER SET charset_name] [COLLATE collation_name]  | LONGTEXT [BINARY]      [CHARACTER SET charset_name] [COLLATE collation_name]  | ENUM(value1,value2,value3,...)      [CHARACTER SET charset_name] [COLLATE collation_name]  | SET(value1,value2,value3,...)      [CHARACTER SET charset_name] [COLLATE collation_name]  | spatial_type#index_col_name 语法 col_name:    col_name [(length)] [ASC|DESC]  #INDEX (name(3) ASC, email) #index_type 语法 index_type:    USING {BTREE | HASH} # INDEX index_username_length_3  BTREE (username(3) ASC)#index_option 语法 index_option:    KEY_BLOCK_SIZE [=] value   | index_type   | WITH PARSER parser_name  | COMMENT 'string'#reference_definition 语法reference_definition:    REFERENCES tbl_name (index_col_name, ...)    [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]    [ON DELETE reference_option]    [ON UPDATE reference_option]# reference_option 语法reference_option:    RESTRICT | CASCADE | SET NULL | NO ACTION#table_options 语法：table_options:    table_option [[,] table_option] ...#table_option 语法：table_option:    ENGINE [=] engine_name  | AUTO_INCREMENT [=] value  | AVG_ROW_LENGTH [=] value #指定行的平均长度  | MAX_ROWS [=] value # 和AVG_ROW_LENGTH 配合计算标的最大容量  | MIN_ROWS [=] value  | [DEFAULT] CHARACTER SET [=] charset_name  | CHECKSUM [=] {0|1}  | [DEFAULT] COLLATE [=] collation_name  | DATA DIRECTORY [=] 'absolute path to directory'  | DELAY_KEY_WRITE [=] {0 | 1}  | INDEX DIRECTORY [=] 'absolute path to directory'  | INSERT_METHOD [=] { NO | FIRST | LAST }  | KEY_BLOCK_SIZE [=] value  | PACK_KEYS [=] {0 | 1 | DEFAULT}  | PASSWORD [=] 'string'  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]  | UNION [=] (tbl_name[,tbl_name]...)#partition_options 语法partition_option:    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)          }          [SUBPARTITION num]        ]        [(partition_definition [, partition_definition] ...)]#partition_definition 语法partition_definition:    PARTITION partition_name        [VALUES             {LESS THAN {(expr | value_list) | MAXVALUE}             |             IN (value_list)}]        [[STORAGE] ENGINE [=] engine_name]        [COMMENT [=] 'comment_text' ]        [DATA DIRECTORY [=] 'data_dir']        [INDEX DIRECTORY [=] 'index_dir']        [MAX_ROWS [=] max_number_of_rows]        [MIN_ROWS [=] min_number_of_rows]        [TABLESPACE [=] tablespace_name]        [NODEGROUP [=] node_group_id]        [(subpartition_definition [, subpartition_definition] ...)]#subpartition_definition 语法：subpartition_definition:    SUBPARTITION logical_name        [[STORAGE] ENGINE [=] engine_name]        [COMMENT [=] 'comment_text' ]        [DATA DIRECTORY [=] 'data_dir']        [INDEX DIRECTORY [=] 'index_dir']        [MAX_ROWS [=] max_number_of_rows]        [MIN_ROWS [=] min_number_of_rows]        [TABLESPACE [=] tablespace_name]        [NODEGROUP [=] node_group_id]select_statement:    [IGNORE | REPLACE] [AS] SELECT ...   (Some valid select statement)

注意：创建表之前首先得具有CREATE权限，如果没有权限，表已经存在，没有声明引用的数据库将引发错误

复制（克隆）表

 CREATE TABLE new_tbl LIKE old_tbl;

从另一张表创建

#创建新表并复制另一张表的所有数据CREATE TABLE new_tbl [AS] SELECT * FROM old_tbl;#创建新表并复制另一张表的部分数据CREATE TABLE new_tbl (    id INT(11) UNSIGNED NOT NULL PRIMARY KEY,    name VARCHAR(32) NOT NULL,    UNIQUE KEY (name) )ENGINE = InnoDBASSELECT id,name FROM user;

临时表

临时表只存在于当前会话，当当前会话结束的时候，临时表将被自动清除，创建临时表使用TEMPORARY关键字

同名表

使用关键字 IF NOT EXISTS 可以避免当同名表出现时的错误；

物理存储

MySQL 将表的定义存储在数据库目录下一个名为tbl_name.frm文件中；

对于InnoDB存储引擎的数据表，如果配置项innodb_file_per_table被打开，则每个表被创建的时候都会有一个 .ibd 格式的文件生成，用于存储表数据和表索引，当这个配置项关闭的时候，InnoDB存储引擎的数据表文件都存储在系统的 ibdata* 文件中；

文件作用 tbl_name.frm 表结构定义 tbl_name.MYD 存储表数据 tbl_name.MYI 存储表索引

数据类型和表属性

并不是所有的属性都可以运用所有的数据类型，AUTO_INCREMENT 只可以运用于整数类型和浮点类型，DEFAULT 类型不能用于 BLOB 和 TEXT 类型

数据列没有指定NULL或者NOT NULL 属性时，则被视为可空；
如果一个具有AUTO_INCREMENT属性的（INTEGER或者FLOAT）列被插入为一个NULL或者0，那么它将自动更新为下一个自增至，通常是value + 1,value 为该数据表中当前列的最大值；但是如果NO_AUTO_VALUE_ON_ZERO模式被开启的时候，允许0值被存储在该列中；需要注意的是每张表只能有一列为AUTO_INCREMENT属性，而且这里必须被索引，且不能有DEFAUL值，而且只存储正数，当插入一个负数的时候相当于插入一个很大的正值；
VARCHAR,CHAR,TEXT 列可以声明DEFAULT CHARACTER SET 和 COLLATE 属性；
CREATE TABLE IF NOT EXISTS tbl_char (TEXT(20) NOT NULL CHARACTER SET utf8 COLLATE utf8_general_ci);
DEFAULT 用于声明一列的默认值，但他必须是常量；这也就是意味着你不能设置一个日期列的默认值值为NOW()或者CURRENT_DATE,但是例外的是你可以设置一个TIMESTAMP列的默认值为CURRENT_TIMESTAMP;还有需要注意的是，对于一个日期属性的默认值，它必须是和 NO_ZERO_IN_DATE 设置相对应，否则将会引发错误；
birthday DATE DEFAULT ‘2010-00-00’ #将会导致一个错误，Invalid default value for ‘c1’
COMMENT 属性用于解释一个列，它会在SHOW CREATE TABLE 和 SHOW FULL COLUMNS 中显示；
KEY 通常情况下和 INDEX 是一个同义词；
一个PRIMARY KEY 列是具有UNIQUE 属性且NOT NULL,如果没有显示声明MySQL将会隐式声明。PRIMARY KEY 通常被写作PRIMARY,每张表也只能有一个PRIMARY 索引；
如果表中没有PRIMARY KEY ，但是一个应用请求PRIMARY KEY时，MySQL将会返回一个UNIQUE且NOT NULL 的列；

尽量保持PRIMARY KEY 最短以减少存储次要索引的开销，每个次要索引都会保存一个对应行中主键列的副本；
在创建表的时候，首先声明PRIMARY KEY，然后是UNIQUE KEY, 最后是NO UNIQUE INDEX，这将帮助MySQL优化器去排序哪个索引被使用以及更快的检测重复值；
你可以声明一个多列的PRIMARY KEY ，例如 PRIMARY KEY (columna, column2);
If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements.
在MySQL中，PRIMARY KEY 的索引名称是 PRIMARY,对于其他的索引类型，如果没有声明一个索引名称，MySQL将次索引名称设置为第一列的列明，附加可选的后缀（_2,_3,…）使其唯一；
对于VARCHAR,CHAR,BINARY,VARBINARY列，索引可以被设置只去索引该列一部分，如 INDEX (col_name(10)),但是对于BLOB和TEXT列，长度是必须设置的；前缀长度在字符串类型中表示长度，在二进制类型中表示索引的字节数，也就是说索引包括（VARVHAR,CHAR,TEXT）第一个字符或者（BINARY,VARBINARY,BLOB）第一个字节,像这样仅仅索引一列的前几个字符可以使索引文件更小；
CREATE TABLE IF NOT EXISTS posts (content TEXT NOT NULL, INDEX (content(10)))ENGINE = InnoDB;
索引列名末尾可以跟上ASC或者DESC，不过此关键字目前会被忽略；

存储引擎

引擎描述 InnoDB 支持事务，外键，行级锁，默认存储引擎 MyISAM 适合存数二进制数据，在大量读的情况下具有很大优势

其他的不做描述

性能优化

这里只提几个常用的属性

AUTO_INCREMENT [=] value , 下一个自增值，可在CREATE TABLE 声明或者ALTER TABLE 中重置；
[DEFAULT] CHARACTER SET 设置字符集；
[DEFAULT] COLLATE 设置排序规则；
ENGINE [=] 设置标的存储引擎；

创建分区表

HASH(expr)分区，基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
```
    CREATE TABLE t1 (col1 INT, col2 CHAR(5)) PARTITION BT HASH(col1);    CREATE TABLE t2 (col1 INT, col2 CHAR(5), col3 DATETIME) PARTITION BY HASH(YEAR(col3));
```
用户可以指定的分区个数最多为1024，在HASH分区中，可以不用VALUES LESS THAN 或者 VALUES IN 语句；

KEY [ALGORITHM = {1|2}] (column_list)：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列（最多16列）；

    CREATE TABLE t1 (col DATE) PARTITION BY KEY(col) PARTITIONS 5;    CREATE TABLE t1 (col DATE) PARTITION BY LINEAR KEY(col) PARTITIONS 5;

RANGE(expr) 或者 RANGE COLUMNS(column_list) 基于属于一个给定连续区间的列值，把多行分配给分区。

CREATE TABLE t1 (    year_col  INT,    some_data INT)PARTITION BY RANGE (year_col) (    PARTITION p0 VALUES LESS THAN (1991),    PARTITION p1 VALUES LESS THAN (1995),    PARTITION p2 VALUES LESS THAN (1999),    PARTITION p3 VALUES LESS THAN (2002),    PARTITION p4 VALUES LESS THAN (2003),    PARTITION p5 VALUES LESS THAN MAXVALUE);#这种分区常用语多个条件CREATE TABLE t2 (    a INT NOT NULL,    b INT NOT NULL)PARTITION BY RANGE COLUMNS(A,B)(    PARTITION p1 VALUES LESS THAN (5,10),    PARTITION p2 VALUES LESS THAN (20,10),    PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE),);

LIST(expr) 或者 LIST COLUMNS(column_list) 类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择.

CREATE TABLE client_firms (    id   INT,    name VARCHAR(35))PARTITION BY LIST (id) (    PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),    PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),    PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),    PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24));CREATE TABLE lc (    a INT NULL,     b INT NULL)PARTITION BY LIST COLUMNS(a,b) (    PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ),    PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ),    PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ),    PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) ));

0 1