时间：2021-07-01 10:21:17 帮助过：12人阅读
CREATE TABLE t7(id INT ,NAME VARCHAR(20), PRIMARY KEY(`id`)) ENGINE=INNODB;格式：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options]
2.简单SQL表名结构：
CREATE TABLE t7(id INT,TIME TIMESTAMP) AS SELECT id,TIME FROM t1 ;【备注】：MySQL5.6.9之后，若开启GTID模式，参数enforce_gtid_consistency=on会限制此种SQL的执行而报错。 格式：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] [IGNORE | REPLACE] [AS] query_expression
3.简单SQL表明结构：
CREATE TABLE t7 like t1;格式：
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
tb_name: 1.格式： tb1_name 或者 `tb1_name` db1.tb1_name 或者 `db1`.`tb1_name` 2.IF NOT EXISTS 不存在时，创建。避免表名重复出现错误。 data_type 1.AUTO_INCREMENT只用于：integer和floating point.不用于：BLOD和TEXT类型。 2. 字符数据类型：(CHAR, VARCHAR, TEXT)，包括：CHARACTER SET and COLLATE的设置 eg:CREATE TABLE t1 (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin); 3.对于CHAR, VARCHAR, BINARY, and VARBINARY 索引只能用于字段的部分：col_name(length) 4.只有InnoDB和MyISAM 支持在BLOB和TEXT上设置索引 eg:
CREATE TABLE t2 (blob_col BLOB, INDEX(blob_col(10)));
NOT NULL | NULL 1.如果不指定NOT NULL和NULL，默认是NULL 2.在MySQL5.6中只有innodb，myisam,memory存储引擎，支持在列值有NULL的列上建索引。其它情况必须声 明所建索引列not null。 DEFAULT 1.设置列的默认值，必须使常量。不能设置为函数：NOW(),CURRENT_DATE() 2.当列为：DATETIME或TIMESTAMP时，可以设置default为：CURRENT_TIMESTAMP 3.如果没有default值，会根据列的类型，默认指定default值。/*其它文章会梳理着部分内容*/ 4.BLOB和TEXT类型，不会指定默认值。 5.注意strict mode开启时的限制，例如：NO_ZERO_IN_DATE 例句：
CREATE TABLE `t2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `num` int(11) NOT NULL DEFAULT ‘0‘, `logintime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1；
AUTO_INCREMENT 1.自增列，一表只能有一个，必须使用索引，不能有默认值。自增步长和起始可以通过参数设置，也可通过建表语句指定。 事例：
CREATE TABLE t2( id INT NOT NULL AUTO_INCREMENT, num INT NOT NULL DEFAULT 0, logintime TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) )ENGINE=INNODB AUTO_INCREMENT=100;
插入数据：
mysql> insert into t2(num) values(1); Query OK, 1 row affected (0.12 sec) mysql> insert into t2(num) values(2); Query OK, 1 row affected (0.01 sec)
mysql> select * from t2; +-----+-----+---------------------+ | id | num | logintime | +-----+-----+---------------------+ | 100 | 1 | 2016-11-16 14:45:20 | | 101 | 2 | 2016-11-16 14:45:25 | +-----+-----+---------------------+ 2 rows in set (0.00 sec)
通过两个参数分别设置：递增量和起始数值
mysql> show variables like ‘%auto_inc%‘; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | | auto_increment_offset | 1 | +--------------------------+-------+
COMMENT 字段描述：可以通过show create table;show full columns;查看显示： 事例：
CREATE TABLE t4( id INT NOT NULL AUTO_INCREMENT COMMENT ‘序列号‘, num INT NOT NULL DEFAULT 0 COMMENT ‘电话号码‘, logintime TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT ‘登录时间‘, PRIMARY KEY (`id`) )ENGINE=INNODB AUTO_INCREMENT=100 ;
查看：
mysql> show full columns from t4; +-----------+-----------+-----------+------+-----+-------------------+----------------+---------------------------------+--------------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-----------+-----------+-----------+------+-----+-------------------+----------------+---------------------------------+--------------+ | id | int(11) | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | 序列号 | | num | int(11) | NULL | NO | | 0 | | select,insert,update,references | 电话号码 | | logintime | timestamp | NULL | NO | | CURRENT_TIMESTAMP | | select,insert,update,references | 登录时间 | +-----------+-----------+-----------+------+-----+-------------------+----------------+---------------------------------+--------------+ 3 rows in set (0.00 sec)
COLUMN_FORMAT
CREATE TABLE t5 ( c1 INT STORAGE DISK, c2 INT STORAGE MEMORY ) ENGINE NDB; CREATE TABLE t1 ( c1 INT STORAGE DISK, c2 INT STORAGE MEMORY ) TABLESPACE ts_1 ENGINE NDB;
CREATE TABLE ... LIKE 格式：
CREATE TABLE new_tbl LIKE orig_tbl;
基于另一个表创建空表，包括表的列和索引的定义。 事例：
create table t4 like t2;
t2表的定义：
mysql> desc t2; +-----------+-----------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | num | int(11) | NO | | 0 | | | logintime | timestamp | NO | | CURRENT_TIMESTAMP | | +-----------+-----------+------+-----+-------------------+----------------+
t4表的定义：
mysql> desc t4; +-----------+-----------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------+------+-----+-------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | num | int(11) | NO | | 0 | | | logintime | timestamp | NO | | CURRENT_TIMESTAMP | | +-----------+-----------+------+-----+-------------------+----------------+
注意： 1.从MySQL5.6.1开始，当另一个表处于：LOCK TABLE状态时，不能create table. . . like 2.当创建新表时的sql_mode和创建原表的sql_mode不同时，表的定义对于新的sql_mode是无效的，导致创建语句失败。 CREATE TABLE ... SELECT 格式：
CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;
1.只会复制表结构，不复制索引；可以复制指定列。 2.创建表的列和 select后面的列，要保持一致。 事例： 原表s1:
mysql> desc s1;
+---------+-------------+------+-----+-------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+-------------------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| LogTime | timestamp | YES | | CURRENT_TIMESTAMP | |
+---------+-------------+------+-----+-------------------+-------+
mysql> select * from s1; +----+------+---------------------+ | id | name | LogTime | +----+------+---------------------+ | 1 | kata | 2016-11-16 16:01:48 | +----+------+---------------------+
创建新表s2、s3
create table s2 as select * from s1;
mysql> desc s2; +---------+-------------+------+-----+-------------------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+-------------------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | LogTime | timestamp | YES | | CURRENT_TIMESTAMP | | +---------+-------------+------+-----+-------------------+-------+
mysql> select * FROM s2; +----+------+---------------------+ | id | name | LogTime | +----+------+---------------------+ | 1 | kata | 2016-11-16 16:01:48 | +----+------+---------------------+
创建表s3
create table s3 as select id,name from s2;
mysql> desc s3; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
mysql> select * from s3; +----+------+ | id | name | +----+------+ | 1 | kata | +----+------+
创建表时指定新的列名：前后列一致
create table s4(Sid int,Sname varchar(10)) as select id as ‘Sid‘,name as ‘Sname‘ from s1;
mysql> desc s4; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Sid | int(11) | YES | | NULL | | | Sname | varchar(10) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
创建表时指定的列名:前后不一致时：结果异常
mysql> create table s7(Sid int,Sname varchar(10)) as select id,name from s1;
异常结果：
mysql> desc s7; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | Sid | int(11) | YES | | NULL | | | Sname | varchar(10) | YES | | NULL | | | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
表的物理文件 1.表文件的存储由配置参数：innodb_file_per_table决定 MySQL版本：<= 5.6.5 默认OFF MySQL版本：>= 5.6.6 默认ON 参数配置开启ON：innodb会将表的data和indexs存储在单独的文件里：.ibd 参数配置关闭OFF: innodb会将表的data和indexs存储在:系统表空间文件：一个或多个 ibdata* files. 2.MYISAM表的数据文件结构
| File | Purpose |
| tbl_name.frm | Table format (definition) file |
| tbl_name.MYD | Data file |
| tbl_name.MYI | Index file |
【SQL篇章--CREATE TABLE】
标签：res date() engine 简单 文章 存储 默认 表空间 margin