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)

 

 

文章来源: Mysql-ALTER TABLE命令学习[20180503]

人吐槽 人点赞

猜你喜欢

发表评论

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Mysql-ALTER TABLE命令学习[20180503]