欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

Mysql-ALTER TABLE命令学习[20180503]

程序员文章站 2022-04-04 23:08:13
学习ALTER TABLE删除、添加和修改字段和类型 CREATE TABLE alter_tab01( id int, col01 char(20)) engin=InnoDB default charset=utf8; 删除字段 ALTER TABLE DROP 
学习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)

 

 

上一篇： 苹果的 iPhone 6s 电池保护壳确实很“智能”

下一篇： Sass学习笔记之Sass快速入门教程

推荐阅读