All SQL commands to modify a table
List of common commands to be performed on a SQL table, to change its structure. The general syntax and an example.
The commands to access the content with SELECT or adding rows with INSERT or UPDATE are not addressed here.
How to delete a column in a SQL table?
ALTER TABLE tablename DROP columname
How to add a new column?
ALTER TABLE tablename ADD columname type
ALTER TABLE mytable ADD colx int(4)
How to change the name of a column in a SQL table?
ALTER TABLE tablename CHANGE oldname newname type
ALTER TABLE mytable CHANGE val qtt integer(8)
How do I change the type of a column?
ALTER TABLE tablename MODIFY columname newtype
ALTER TABLE mytable MODIFY colx decimal(3,3)
How do I change the characters set (charset) of an entire table?
ALTER TABLE tablename CONVERT TO CHARACTER SET format COLLATE format
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci
For the entire database:
ALTER DATABASE basename DEFAULT CHARACTER SET format COLLATE format
How do I change the characters set in a column?
ALTER TABLE tablename MODIFY columname type CHARACTER SET format
ALTER TABLE mytable MODIFY colx TINYTEXT CHARACTER SET utf8
See also