mysql  alter table statement

the MySQL alter table statement is used to change the structure of existing tables.

you can use the MySQL alter table command to add or drop column, change data type of column, add primary key, rename table and many more.

mysql alter table syntax:

to add a column in a table, use the following syntax:

       alter table   table_name

       add    column_name     datatype

to change the data type of a column in a table, use the following syntax:

       alter table    table_name

       modify     column_name  datatype

to remove a column in a table, use the following syntax:

      alter table    table_name

       drop   column    column_name

to rename a table , use the following syntax:

      alter table    table_name

      rename   [to|as]    table_name

demo "users" table previous alter operation look like:

useridfirstnamelastname
1sandeepkumar
2ankitnehra
3mysqlalter

MySQL Alter table Command example :

  
      mysql>alter    table    users
               
               rename  to  userinfo,
               
               add        salary   float,
               
               add        primary key (userid);

after run the above mysql commands then the avaliable table completely modified so we can say about the mysql alter command that can change the table name, table column name, add new cell in table , delete the table column,change data type of column,add primary foreign key.

output : after the alter operation on "users" table look like:

useridfirstnamelastnamesalary
1sandeepkumar10000
2ankitnehra20000
3mysqlalter300000

after the alter operation the "users' table name is userinfo.

add new 'salary' column in the userinfo table.

add constraints(primary key) to the table.


MySQL Alter table - drop column example

we want to delete the column named "salary" in the "userinfo" table.

       alter  table   userinfo

       drop    column     salary,lastname;

the "userinfo" table will now look like this:

useridfirstname
1sandeep
2ankit
3ram