ALTER Command

ALTER command can be used to add/modify definition of various aspects of database and its objects.

Dropping, Adding or Repositioning a Column

--drop existing column
alter table test_tbl  drop i;
--add the same column at the end of the table
alter table test_tbl add i int;

--drop existing column
alter table test_tbl drop i;
--add the same column at top of the able
alter table test_tbl add i int first;

--drop existing column
alter table test_tbl drop i;
--add same column at after column c
alter table test_tbl add i int after c;

Changing a Column Definition or a Name

--change datatype
alter table test_tbl modify col1 char(10);
--change column name
alter table test_tbl change col1 col1new bigint;
--change datatype
alter table test_tbl change col1new col1new int(1);

--setting the default value
alter table test_tbl modify i bigint not null default 100;

Changing a Column's Default Value

--setting a default value of a column
alter table test_tbl i set default 1000;
--remove the default value of a column
alter table test_tbl alter i drop default;

Changing a Table Type

--change the db engine type for a given table
alter table test_tbl type = myisam;

Renaming a Table

--rename a table
alter table test_tbl rename to alter_tbl;