mysql table alter_MySQL-ALTER TABLE命令学习[20180503]

学习ALTER TABLE删除、添加和修改字段和类型

CREATE TABLE alter_tab01(

id int,

col01 char(20))

engin=InnoDB default charset=utf8;

删除字段

ALTER TABLE DROP ;

mysql> alter table alter_tab01 dropcol01;

Query OK, 0 rows affected (0.01sec)

Records: 0 Duplicates: 0 Warnings: 0

添加字段

ALTER TABLE ADD TYPE;

ALTER TABLE ADD TYPE [ FIRST| AFTER ];

ALTER TABLE ADD TYPE NOT NULL;

mysql> alter table alter_tab01 add col01 char(20);

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table alter_tab01 add col02 char(20) first;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table alter_tab01 add col03 char(20) after id;

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table alter_tab01 add col04 char(20) not null;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> show columns from alter_tab01;

+-------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+----------+------+-----+---------+-------+

| col02 | char(20) | YES | | NULL | |

| id | int(11) | YES | | NULL | |

| col03 | char(20) | YES | | NULL | |

| col01 | char(20) | YES | | NULL | |

| col04 | char(20) | NO | | NULL | |

+-------+----------+------+-----+---------+-------+

5 rows in set (0.00 sec)

修改字段类型及名称

ALTER TABLE MODIFY TYPE;

ALTER TABLE CHANGE TYPE;

mysql> alter table alter_tab01 modify col02 varchar(10);

Query OK, 0 rows affected (0.01sec)

Records: 0 Duplicates: 0 Warnings: 0mysql> alter table alter_tab01 change col02 new_col02 char(2);

Query OK, 0 rows affected (0.01sec)

Records: 0 Duplicates: 0 Warnings: 0mysql> show columns fromalter_tab01;

+-----------+----------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+----------+------+-----+---------+-------+

| new_col02 | char(2) | YES | | NULL | |

| id | int(11) | YES | | NULL | |

| col03 | char(20) | YES | | NULL | |

| col01 | char(20) | YES | | NULL | |

| col04 | char(20) | NO | | NULL | |

+-----------+----------+------+-----+---------+-------+

5 rows in set (0.00 sec)

修改字段NOT NULL约束与默认值

ALTER TABLE MODIFY TYPE NOT NULL DEFAULT ;

mysql> alter table alter_tab01 modify id bigint not null default 1;

Query OK, 0 rows affected (0.01sec)

Records: 0 Duplicates: 0 Warnings: 0mysql> show columns fromalter_tab01;

+-----------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+------------+------+-----+---------+-------+

| new_col02 | char(2) | YES | | NULL | |

| id | bigint(20) | NO | | 1 | |

| col03 | char(20) | YES | | NULL | |

| col01 | char(20) | YES | | NULL | |

| col04 | char(20) | NO | | NULL | |

+-----------+------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

修改字段的默认值

ALTER TABLE ALTER SET DEFAULT ;

ALTER TABLE ALTER DROP DEFAULT;

mysql> alter table alter_tab01 alter new_col02 set default '01';

Query OK, 0 rows affected (0.01sec)

Records: 0 Duplicates: 0 Warnings: 0mysql> show columns fromalter_tab01;

+-----------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+------------+------+-----+---------+-------+

| new_col02 | char(2) | YES | | 01 | |

| id | bigint(20) | NO | | 1 | |

| col03 | char(20) | YES | | NULL | |

| col01 | char(20) | YES | | NULL | |

| col04 | char(20) | NO | | NULL | |

+-----------+------------+------+-----+---------+-------+

5 rows in set (0.00sec)

mysql> alter table alter_tab01 alter new_col02 drop default;

Query OK, 0 rows affected (0.00sec)

Records: 0 Duplicates: 0 Warnings: 0mysql> show columns fromalter_tab01;

+-----------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+------------+------+-----+---------+-------+

| new_col02 | char(2) | YES | | NULL | |

| id | bigint(20) | NO | | 1 | |

| col03 | char(20) | YES | | NULL | |

| col01 | char(20) | YES | | NULL | |

| col04 | char(20) | NO | | NULL | |

+-----------+------------+------+-----+---------+-------+

5 rows in set (0.00 sec)

修改表的存储引擎

ALTER TABLE ENGINE=【MyISAM | InnoDB | BDB | Memory | Merge | Archive | Federated | Cluster/NDB | Other】

mysql> show table status like 'alter_tab01'\G

*************************** 1. row ***************************Name: alter_tab01

Engine: InnoDB

Version: 10Row_format: Compact

Rows: 0Avg_row_length: 0Data_length: 16384Max_data_length: 0Index_length: 0Data_free: 4194304Auto_increment: NULLCreate_time: 2018-05-03 16:11:39Update_time: NULLCheck_time: NULLCollation: utf8_general_ci

Checksum: NULLCreate_options:

Comment:

1 row in set (0.00sec)

mysql> alter table alter_tab01 engine=MyISAM;

Query OK, 0 rows affected (0.01sec)

Records: 0 Duplicates: 0 Warnings: 0mysql> show table status like 'alter_tab01'\G

*************************** 1. row ***************************Name: alter_tab01

Engine: MyISAM

Version: 10Row_format: Fixed

Rows: 0Avg_row_length: 0Data_length: 0Max_data_length: 54887620458577919Index_length: 1024Data_free: 0Auto_increment: NULLCreate_time: 2018-05-03 16:12:35Update_time: 2018-05-03 16:12:35Check_time: NULLCollation: utf8_general_ci

Checksum: NULLCreate_options:

Comment:

1 row in set (0.00 sec)

修改表的名称

ALTER TABLE RENAME TO ;

mysql> alter table alter_tab01 rename toalter_tab02;

Query OK, 0 rows affected (0.00sec)

mysql> show table status like 'alter_tab02'\G

*************************** 1. row ***************************Name: alter_tab02

Engine: InnoDB

Version: 10Row_format: Compact

Rows: 0Avg_row_length: 0Data_length: 16384Max_data_length: 0Index_length: 0Data_free: 4194304Auto_increment: NULLCreate_time: 2018-05-03 16:14:02Update_time: NULLCheck_time: NULLCollation: utf8_general_ci

Checksum: NULLCreate_options:

Comment:

1 row in set (0.00 sec)