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)

 

 

    修改字段类型及名称

 

        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)