We can also control the position of the new column within the table using the FIRST or AFTER keyword. We can also remove the newly added column using the DROP statement as follows: ALTER TABLE Actors DROP MiddleName ALTER TABLE tablename ADD column varchar(100) FIRST
We can add a new column MiddleName to the Actors table using the following query: ALTER TABLE Actors ADD MiddleName varchar(100) ALTER TABLE tablename DROP column We can also add a column to an existing table. For instance, we can change the column first name to have a varchar length of 300 as shown below: ALTER TABLE Actors MODIFY First_Name varchar(300) ALTER TABLE tablename ADD column varchar(100) On the contrary, we can easily convert the type of a column that doesn’t result in data loss. ALTER TABLE Actors MODIFY First_Name INT For instance, if we try to change the first name column from type varchar to int, we’ll run into an error (as shown below) because the conversion is nonsensical. We have to be cautious when trying to change the type of an existing column. ALTER TABLE Actors CHANGE First_Name First_Name varchar(20) DEFAULT "Anonymous" ALTER TABLE table MODIFY column INT We can also use the CHANGE statement but that will require us to specify the same column name twice as we aren’t renaming the column. For instance, we can specify the default value for the column First_Name to be the string “Anonymous” as follows: ALTER TABLE Actors MODIFY First_Name varchar(20) DEFAULT "Anonymous" We can use the MODIFY keyword if we wish to alter the type or the clauses for a column. ALTER TABLE tablename MODIFY columnname varchar(20) DEFAULT “Anonymous”
If we only wanted to rename the column, we would still need to re-specify the type of the column as well as any other clauses that were specified the first time. We can do so as follows: ALTER TABLE Actors CHANGE FirstName First_Name varchar(120) ĪLTER TABLE Actors CHANGE FirstName First_Name varchar(120) We not only change the column name, but we also change the column length from 20 to 120. Let’s say we want to rename the column FirstName to First_Name for the Actors table. The article queries are reproduced below for convenient copy/paste into the terminal.ĪLTER TABLE Actors CHANGE FirstName First_Name varchar(120) ĪLTER TABLE Actors MODIFY First_Name varchar(20) DEFAULT "Anonymous" ĪLTER TABLE Actors CHANGE First_Name First_Name varchar(20) DEFAULT "Anonymous" ĪLTER TABLE Actors MODIFY First_Name INT ĪLTER TABLE Actors MODIFY First_Name varchar(300) ĪLTER TABLE Actors ADD MiddleName varchar(100) ĪLTER TABLE Actors ADD MiddleName varchar(100) FIRST ĪLTER TABLE Actors ADD MiddleName varchar(100) AFTER DoB ĪLTER TABLE Actors DROP MiddleName, ADD Middle_Name varchar(100) ALTER TABLE tablename CHANGE column1 column2 varchar(120) We can rename tables, add, remove, or rename columns, change the type of an existing column, etc. MySQL allows us to change our minds about the entities we create and alter them.
#MYSQL RENAME COLUMN HOW TO#
In this article we discuss how to modify various database structures once they are created. DROP TABLE IF EXISTS Table1, Table2, Table3.ALTER TABLE tablename1 RENAME tablename2.
0 kommentar(er)
