MySQL-ALTER TABLE命令学习[20180503]
学习ALTER TABLE删除、添加和修改字段和类型CREATE TABLE alter_tab01( id int, col01 char(20)) engin=InnoDB default charset=utf8; 删除字段 ALTER TABLE <tab_name> DROP <col_name>;
mysql> alter table alter_tab01 drop col01;
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
添加字段 ALTER TABLE <tab_name> ADD <col_name> TYPE; ALTER TABLE <tab_name> ADD <col_name> TYPE [ FIRST| AFTER <col_name>]; ALTER TABLE <tab_name> ADD <col_name> 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)
SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。
修改字段类型及名称 ALTER TABLE <tab_name> MODIFY <col_name> TYPE; ALTER TABLE <tab_name> CHANGE <old_name> <new_name> TYPE;
mysql> alter table alter_tab01 modify col02 varchar(10);
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table alter_tab01 change col02 new_col02 char(2); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from alter_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 <tab_name> MODIFY <col_name> TYPE NOT NULL DEFAULT <默认值>;
mysql> alter table alter_tab01 modify id bigint not null default 1;
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from alter_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 <tab_name> ALTER <col_name> SET DEFAULT <默认值>; ALTER TABLE <tab_name> ALTER <col_name> DROP DEFAULT;
mysql> alter table alter_tab01 alter new_col02 set default '01';
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from alter_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.00 sec) mysql> alter table alter_tab01 alter new_col02 drop default; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from alter_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 <tab_name> 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: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 4194304 Auto_increment: NULL Create_time: 2018-05-03 16:11:39 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec) mysql> alter table alter_tab01 engine=MyISAM; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show table status like 'alter_tab01'\G *************************** 1. row *************************** Name: alter_tab01 Engine: MyISAM Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 54887620458577919 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2018-05-03 16:12:35 Update_time: 2018-05-03 16:12:35 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
修改表的名称 ALTER TABLE <tab_name> RENAME TO <new_name>;
mysql> alter table alter_tab01 rename to alter_tab02;
Query OK, 0 rows affected (0.00 sec) mysql> show table status like 'alter_tab02'\G *************************** 1. row *************************** Name: alter_tab02 Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 4194304 Auto_increment: NULL Create_time: 2018-05-03 16:14:02 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
更多精彩
