"SQL Chapter--create TABLE"

" SQL Chapter" "SQL Statement grooming:--based on MySQL5.6" "has been combed: CREATE TABLE" "will persist in perfection"SQL:1. Data Definition Statements:1.3 CREATE TABLE format: 3 Types of Build table statements1. Simple SQL indicates the structure:

CREATE TABLE T7 (ID INT, NAME VARCHAR (), PRIMARY KEY (' id ')) engine=innodb;

format:

CREATE [temporary] TABLE [IF not EXISTS] Tbl_name (create_definition,...) [Table_options] [Partition_options]

　　

2. Simple SQL table name structure:

CREATE TABLE T7 (ID int,time TIMESTAMP) as SELECT id,time from T1;

"Remarks": After MySQL5.6.9, if the Gtid mode is turned on, the parameter enforce_gtid_consistency=on will limit the execution of this type of SQL error. format:

CREATE [temporary] TABLE [IF not EXISTS] tbl_name [(create_definition,...)] [Table_options] [Partition_options] [IGNORE | REPLACE] [as] query_expression

　　

3. Simple SQL indicates the structure:

CREATE TABLE T7 like T1;

format:

CREATE [temporary] TABLE [IF not EXISTS] tbl_name {like Old_tbl_name | (Like Old_tbl_name)}

Tb_name:1. Format:tb1_name or ' tb1_name 'db1.tb1_name or ' db1 '. ' Tb1_name '2.IF not EXISTSis not present when it is created. Avoid duplicate table name errors. data_type1.auto_increment is used only for: integer and floating point. Not used for: Blod and text types. 2. Character data type: (CHAR, VARCHAR, TEXT), including: CHARACTER set and collate settingseg:create TABLE T1 (c CHAR () CHARACTER SET UTF8 COLLATE utf8_bin);3. For char, VARCHAR, BINARY, and VARBINARY indexes can only be used for the part of the field: col_name (length)4. Only InnoDB and MyISAM support setting indexes on BLOBs and texteg:

CREATE TABLE T2 (Blob_col blob, INDEX (Blob_col (10)));

　　

Not NULL | NULL1. If not null and NULL are not specified, the default is null2. In MySQL5.6, only the innodb,myisam,memory storage engine supports indexing on columns with NULL column values. In other cases, you must declare that the index column you are building is not null. DEFAULT1. Set the default value of the column, you must make the constant. cannot be set as function: Now (), current_date ()2. When listed as: DateTime or TIMESTAMP, you can set default to: Current_timestamp3. If there is no default value, the default value is specified according to the type of the column. /* Other articles will comb some of the content * /the 4.BLOB and text types do not specify a default value. 5. Note The limitations of strict mode when it is turned on, for example: No_zero_in_dateExample:

CREATE TABLE ' T2 ' (   ' id ' int (one) not null auto_increment,   ' num ' int (one) ' NOT null DEFAULT ' 0 ',   ' logintime ' time Stamp not NULL default Current_timestamp,   PRIMARY KEY (' id ')) engine=innodb default charset=latin1;

　　

auto_increment1. Self-increment column, one table can only have one, must use the index, cannot have the default value. The self-increment step and start can be set by parameter or by a build table statement. Case:

CREATE TABLE T2 (id int not NULL auto_increment,num int NOT NULL default 0,logintime TIMESTAMP default CURRENT_TIMESTAMP,PR Imary KEY (' id ')) engine=innodb auto_increment=100;

　　

Insert Data:

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)

　　

101

　　

set by two parameters: Increment amount and starting value

Mysql> Show variables like '%auto_inc% '; +--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 1     | | auto_increment_offset    | 1     |+--------------------------+-------+

　　

COMMENTField Description: Available through show create table;show full columns; View display:Case:

CREATE TABLE t4 (id INT not NULL auto_increment COMMENT ' serial number ', num INT not null DEFAULT 0 COMMENT ' phone number ', Logintime TIMESTAMP DEFAULT current_timestamp COMMENT ' login time ', PRIMARY KEY (' id ')) engine=innodb auto_increment=100;

　　

View:

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 | Serial number | | num | Int (11) | NULL | NO | | 0 | | select,insert,update,references | Phone number | | Logintime | Timestamp | NULL | NO | | Current_timestamp | | select,insert,update,references | Login time |+-----------+-----------+-----------+------+-----+-------------------+----------------+------------------- --------------+--------------+3 rows in Set (0.00 sec)

Column_format

1. Typically used for individual columns of the NDB table.
2. Fixed: Specifies the column width
3. DYNAMIC: Specifying variable column widths
4. DEFAULT: Both are available and are automatically selected based on the column type.
5. for the NDB table, Column_format is: default. For non-NDB engine table is not effective, after MySQL5.6, the default is not turned on. STORAGE

Case:applies only to: NDB engine

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 ... likeformat:

CREATE TABLE new_tbl like orig_tbl;

　　

creates an empty table based on another table, including the definition of the table's columns and indexes. Case:

CREATE table T4 like T2;

　　

Definition of t2 table:

ID PRI Num Logintime| Timestamp | NO | | Current_timestamp | |+-----------+-----------+------+-----+-------------------+----------------+

　　

Definition of T4 table:

ID PRI Num Logintime | Timestamp | NO | | Current_timestamp | |+-----------+-----------+------+-----+-------------------+----------------+

　　

Note:1. Starting from MySQL5.6.1, when another table is in the LOCK table state, you cannot create table ... like2. When a new table is created with a different sql_mode than the Sql_mode that created the original table, the table definition is not valid for the new Sql_mode, resulting in the creation of the statement failure.  CREATE TABLE ... SELECTformat:

CREATE TABLE new_tbl [as] SELECT * from ORIG_TBL;

　　

1. Only the table structure is replicated, the index is not copied, and the specified columns can be copied. 2. Create the columns for the table and the columns following the select to be consistent. Case:Original table S1:

Mysql> desc s1;+---------+-------------+------+-----+-------------------+-------+| Field   | Type        | Null | Key | Default           | Extra |+---------+-------------+------+-----+-------------------+-------+| ID      | int     | 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 |+----+------+---------------------+

　　

Create a new table S2, S3

CREATE TABLE s2 as select * from S1;

Mysql> desc s2;+---------+-------------+------+-----+-------------------+-------+| Field   | Type        | Null | Key | Default           | Extra |+---------+-------------+------+-----+-------------------+-------+| ID      | int     | 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 |+----+------+---------------------+

　　

Create a table S3

CREATE TABLE s3 as select Id,name from S2;

　　

Mysql> desc s3;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| ID    | int     | NO   |     | NULL    |       | | name  | varchar (20) | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+

　　

Mysql> SELECT * FROM s3;+----+------+| ID | Name |+----+------+|  1 | Kata |+----+------+

　　

to specify a new column name when creating a table: Consistent front and back columns

CREATE TABLE S4 (sid Int,Snamas 'Sid 'as' Sname ' from S1;

　　

Mysql> desc s4;+-------+-------------+------+-----+---------+-------+| Field | Type        Sid  | int     | YES  |     | NULL    |       Sname | varchar (10) | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+

　　

Column name specified when creating table: Inconsistent: result Exception

Mysql> CREATE TABLE s7 (Sid int,sname varchar) as select id,name from S1;

Exception Result:

Mysql> desc s7;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| Sid   | int |     YES  |     | NULL    |       | | Sname | varchar (10) | YES  |     | NULL    | |       | ID    | int     | NO   |     | NULL    |       | | name  | varchar (20) | YES  |     | NULL    |       | +-------+-------------+------+-----+---------+-------+

　　

physical file of the table1. Table file storage is determined by configuration parameters: Innodb_file_per_tableMySQL version: <= 5.6.5 default offMySQL version: >= 5.6.6 default onparameter configuration on On:innodb the data and indexs of the table are stored in a separate file:. IBDparameter configuration shutdown OFF:INNODB stores the table's data and Indexs in: System tablespace file: One or more ibdata* files.data file structure for 2.MYISAM tables

File	Purpose
Tbl_name.frm	Table format (definition) file
Tbl_name. MYD	Data file
Tbl_name. MYI	Index file

    Remarks: About the temporary table field types and tables index section separate grooming!!!

"SQL Chapter--create TABLE"