Changing the database structure

Your database isn’t written in stone. By using the ALTER query, you can change the name of the table; add, drop, or rename a column; or change the data type or other attributes of the column.
The basic format for this query is ALTER TABLE tablename, followed by the specified changes.

• ADD columnname definition Adds a column; definition includes the data type and optional definitions.
• ALTER columnname SET DEFAULT value Changes the default value for a column
• ALTER columnname DROP DEFAULT Removes the default value for a column.
• CHANGE columnname newcolumnname definition Changes the definition of a column and renames the colum, definition includes the data type and optional definitions
  
• DROP columnname Deletes a column, including all the data in the column. The data cannot be recovered.
• MODIFY columnname definition Changes the definition of a column; definition includes the data type and optional definitions.
• RENAME newtablename Renames a table.

Changing a database is not a rare occurrence. You might want to change your database for many reasons. For example, suppose that you defined the column lastName with VARCHAR(20) in the Member table of the MemberDirectory database. At the time, 20 characters seemed sufficient for a last name. But now you just received a memo announcing the new CEO, John Schwartzheimer-Losertman. Oops. MySQL will truncate his name to the first 20 letters, a less-than-desirable new name for the boss. So you need to make the column wider — pronto. Send this query to change the column in a second:
ALTER TABLE Member MODIFY lastName VARCHAR(50)