MySQL Columns

A column is an attribute of a relation i.e. Table.Every column has a data type associated with it. Each record or row in a table will have information for every column of the table. The columns define the structure of a table. We will see how to add, delete or create a column in MySQL.

MySQL Add column Syntax

To add a column  to an existing table, we do:

ALTER TABLE <table-name> 
ADD COLUMN <column-name> <datatype><any-conditions>;

Add a column to an existing table in MySQL

In the below example, we add a new column 'Notes' to the existing table 'Employees' using the below statement.

Example: 

ALTER TABLE EMPLOYEES ADD COLUMN NOTES varchar(20);

By default, the columns are added as the last column, but we can specify if we want the column to be added as the first column, we specify the keyword First.

Modify a column in the Existing Table in MySQL

Example of modifying a column, we do:

ALTER TABLE EMPLOYEES MODIFY LAST_NAME VARCHAR(20) NULL; 

We can modify:

  • Data type
  • The number or constraints that we put on a datatype
  • Null/Not Null/Unique conditions
  • Name of the column

Delete a column in MySQL using the command line client

  • Log in with the user credentials. The user needs to have drop privilege.
  • Select a database using the USE statement.

For example, to drop a column, we do:

ALTER TABLE EMPLOYEES DROP COLUMN NOTES;