Actual MySQL alter statement operations

The following article describes the actual operations and practical applications of the MySQL alter statement, as well as how to correctly add, modify, and delete certain fields. The following is a detailed description of the article, I hope it will help you in this regard.

Primary Key

 
 

  
  
alter table tablename add new_field_id int(5) unsigned default 0 not null auto_increment ,add primary key (new_field_id); 
 
 

Add a new column

 
 

  
  
alter table t2 add d timestamp;  
  
  
alter table infos add ex tinyint not null default '0'; 
 
 

Delete column

 
 

  
  
alter table t2 drop column c; 
 
 

Rename a column

 
 

  
  
alter table t1 change a b integer; 
 
 

Change column type

 
 

  
  
alter table t1 change b b bigint not null;  
  
  
alter table infos change list list tinyint not null default '0'; 
 
 

Rename a table

 
 

  
  
alter table t1 rename t2; 
 
 

Add Index

 
 

  
  
MySQL> alter table tablename change depno int (5) not null;
  
  
MySQL> alter table tablename add index name (field name 1 [, field name 2…]);
  
  
MySQL> alter table tablename add index emp_name (name );
 
 

Index with primary keywords

 
 

  
  
MySQL> alter table tablename add primary key(id); 
 
 

Add an index with unique conditions

 
 

  
  
MySQL> alter table tablename add unique emp_name2(cardnumber); 
 
 

Delete An index

 
 

  
  
MySQL>alter table tablename drop index emp_name; 
 
 

Modify Table:

Add field:

 
 

  
  
MySQL> ALTER TABLE table_name ADD field_name field_type; 
 
 

Modify the original field name and type:

 
 

  
  
MySQL> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type; 
 
 

Delete field:

 
 

  
  
MySQL> ALTER TABLE table_name DROP field_name; 
 
 

The above content is an introduction to MySQL alter statement usage, addition, modification, and deletion of fields.