The ALTER TABLE query is used to add, delete, rename or modify any table field in an existing table. The ALTER TABLE query is also used to add and drop various constraints on an existing table.
The ALTER statement is always used with ADD, DROP, RENAME and MODIFY commands according to the needs.
ADD a column in the table using ALTER TABLE Query
MYSQL Syntax
ALTER TABLE table_name
ADD column_name datatype
[ FIRST | AFTER column_name ];
MYSQL Example :
ALTER TABLE tblstudent
ADD area varchar(255)
after stud_address;
As per as above example, area column is added in table tblstudent. You can add multiple columns in table.
MODIFY column in the table using ALTER TABLE Query
MYSQL Syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
MYSQL Example :
ALTER TABLE tblstudent MODIFY stud_name VARCHAR(90) ;
As per as above example, size of stud_name has been changed form 70 to 90 using MODIFY clause in alter table query.
DROP column in table using ALTER TABLE Query
MYSQL Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
MYSQL Example :
ALTER TABLE tblstudent DROP COLUMN area
As per as above example, area field name has been deleted from table tblstudent .
RENAME column in table using ALTER TABLE Query
MYSQL Syntax:
ALTER TABLE table_name CHANGE old_column_name new_column_name DataType
MYSQL Example :
ALTER TABLE tblstudent CHANGE stud_mobile stud_phone VARCHAR(11)
As per as above example, stud_mobile column name has been changed to stud_phone in table tblstudent.
RENAME table using ALTER TABLE Query
MYSQL Syntax:
ALTER TABLE table_name RENAME TO new_table_name
MYSQL Example :
ALTER TABLE tblstud RENAME TO tblstudent
As per as above example, table tblstud name has been changed to tblstudent.
Comments