MySQL – Add/Delete Columns
A column in a table is a series of vertical cells that are used to store different types of data such as text, numbers, images, etc. Every column can contain one or more rows, where each row can store a single value.
Adding Columns to a MySQL table
In MySQL, we can add one or multiple columns in a table using the ALTER TABLE ADD statement. Adding columns to a table can be useful when we need to add new data.
Syntax
Following is the syntax to add a column in a MySQL table −
ALTER TABLE table_name ADD [COLUMN] column_1_definition [FIRST|AFTER existing_column], ADD [COLUMN] column_2_definition [FIRST|AFTER existing_column], ...;
Where,
- The FIRST keyword is used to add a specific column at the beginning of the table.
- The AFTER keyword is used to add a column after a particular existing column in the table.
Example
First of all, let us create a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL );
Execute the following query to retrieve the columns list in above created table −
DESCRIBE CUSTOMERS;
Following are the columns that are present in the CUSTOMERS table at the moment −
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int | NO | NULL | ||
NAME | varchar(20) | NO | NULL |
Now, we are adding a column named AGE to the CUSTOMERS table using the below query −
ALTER TABLE CUSTOMERS ADD COLUMN AGE INT NOT NULL;
Output
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
Verification
After adding the AGE column to the CUSTOMERS table, we can check to confirm if the AGE column has been added or not, using the following query −
DESCRIBE CUSTOMERS;
As we can see in the colums list of CUSTOMERS table, the column AGE is added successfully.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int | NO | NULL | ||
NAME | varchar(20) | NO | NULL | ||
AGE | int | NO | NULL |
Example
In the following query, we are using the FIRST keyword to add the S_NO column at the beginning of the previosly created CUSTOMERS table −
ALTER TABLE CUSTOMERS ADD COLUMN S_NO INT NOT NULL FIRST;
Output
On executing the given query, the output is displayed as follows −
Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
Verification
Now, let us verify whether the S_NO column is added first or not by executing the below query −
DESCRIBE CUSTOMERS;
As we can see in the output table, the S_NO column is added successfully at the beginning of the table.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
S_NO | int | NO | NULL | ||
ID | int | NO | NULL | ||
NAME | varchar(20) | NO | NULL | ||
AGE | int | NO | NULL |
Example
At the moment, the CUSTOMERS table has 4 columns in it. Now, we are using the AFTER keyword to add a new column GENDER after the column named ID −
ALTER TABLE CUSTOMERS ADD COLUMN GENDER VARCHAR(10) AFTER ID;
Output
Executing the query above will produce the following output −
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
Verification
Using the following DESCRIBE statement, we can verify whether the column GENDER is added after the ID column or not −
DESCRIBE CUSTOMERS;
The GENDER column is successfully added after the ID column.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
S_NO | int | NO | NULL | ||
ID | int | NO | NULL | ||
GENDER | varchar(10) | YES | NULL | ||
NAME | varchar(20) | NO | NULL | ||
AGE | int | NO | NULL |
Adding Multiple Columns
We can add multiple columns into a specified table using the ALTER TABLE…ADD command. To do this, we just need to specify the new columns that we want to add, separating them with commas.
Example
In the below query, we are adding multiple columns (ADDRESS and CONTACT) to the CUSTOMERS table with a single ALTER statement −
ALTER TABLE CUSTOMERS ADD COLUMN ADDRESS CHAR (25), ADD COLUMN CONTACT INT;
Output
The output for the program above is produced as given below −
Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
We can verify whether the columns MARKS and GRADES are added or not using the following query −
DESCRIBE CUSTOMERS;
The following output show that the MARKS and GRADES columns are added into CUSTOMERS table −
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
S_NO | int | NO | NULL | ||
ID | int | NO | NULL | ||
GENDER | varchar(10) | YES | NULL | ||
NAME | varchar(20) | NO | NULL | ||
AGE | int | NO | NULL | ||
ADDRESS | char(25) | YES | NULL | ||
CONTACT | int | YES | NULL |
Deleting Columns from a MySQL table
In MySQL, we can delete single or multiple columns from a table using the ALTER TABLE DROP COLUMN statement. We generally delete the columns when there is specific data that is no longer needed.
Syntax
Following is the syntax of ATLER TABLE DROP COLUMN in MySQL −
ALTER TABLE table_name DROP COLUMN column_name;
Example
At the moment, we have 7 columns in the CUSTOMERS table. Now, we are deleting the existing column S_NO from the CUSTOMERS table −
ALTER TABLE CUSTOMERS DROP COLUMN S_NO;
Output
When we execute the program above, the output is obtained as follows −
Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
Verification
We can verify whether the column named S_NO is deleted or not using the following query −
DESCRIBE CUSTOMERS;
As we can see the newly updated columns list of CUSTOMERS table, the S_NO column has deleted.
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int | NO | NULL | ||
GENDER | varchar(10) | YES | NULL | ||
NAME | varchar(20) | NO | NULL | ||
AGE | int | NO | NULL | ||
ADDRESS | char(25) | YES | NULL | ||
CONTACT | int | YES | NULL |
Example
Here, we are trying to delete multiple columns (GENDER, ADDRESS, and CONTACT) using a single ALTER statement −
ALTER TABLE CUSTOMERS DROP COLUMN AGE, DROP COLUMN GENDER;
Output
On executing the given program, the output is displayed as follows −
Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
Verification
Using the following query, we can verify whether the GENDER, ADDRESS and CONTACT columns are deleted or not −
DESCRIBE CUSTOMERS;
Following is the list of columns in CUSTOMERS after deleting the above mentioned columns −
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
ID | int | NO | NULL | ||
NAME | varchar(20) | NO | NULL | ||
ADDRESS | char(25) | YES | NULL | ||
CONTACT | int | YES | NULL |
Adding/Deleting column in a table Using a Client Program
Besides adding/deleting a column in a table in MySQL database with a MySQL query, we can also use a client program to perform the ALTER TABLE ADD/DROP operation.
Syntax
Following are the syntaxes to Add/Delete a column in MySQL Database in various programming languages −
To Add/Delete a column in/of a table into MySQL database through a PHP program, we need to execute ALTER statement using the mysqli function query() as −
//following is the syntax for add column in existing table. $sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name"; //following is the syntax for delete column in existing table. $sql = "ALTER TABLE table_name DROP COLUMN column_name"; $mysqli->query($sql);
To Add/Delete a column in/of a Table into MySQL database through a Node.js program, we need to execute ALTER statement using the query() function of the mysql2 library as −
//following is the syntax for add column in existing table. sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name"; //following is the syntax for delete column in existing table. sql = "ALTER TABLE table_name DROP COLUMN column_name"; con.query(sql);
To Add/Delete a column in/of a Table into MySQL database through a Java program, we need to execute ALTER statement using the JDBC function executeUpdate() as −
//following is the syntax for add column in existing table. String sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name"; //following is the syntax for delete column in existing table. String sql = "ALTER TABLE table_name DROP COLUMN column_name"; statement.executeUpdate(sql);
To Add/Delete a column in/of a Table into MySQL database through a Python program, we need to execute ALTER statement using the execute() function of the MySQL Connector/Python as −
//following is the syntax for add column in existing table. sql = "ALTER TABLE table_name ADD COLUMN column_name datatype NOT NULL AFTER existing_column_name" //following is the syntax for delete column in existing table. sql = "ALTER TABLE table_name DROP COLUMN column_name" cursorObj.execute(sql);
Example
Following are the programs −
$dbhost = ''localhost $dbuser = ''root $dbpass = ''password $dbname = ''TUTORIALS $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } // printf(''Connected successfully.
''); // Query to add column name in table... $sql = "ALTER TABLE tutorials_tbl ADD COLUMN tutorial_name VARCHAR(30) NOT NULL AFTER tutorial_id"; if ($mysqli->query($sql)) { printf(" Coulumn added seccessfully in existing table.
"); } //Query to Delete column of a table... $sql = "ALTER TABLE tutorials_tbl DROP COLUMN tutorial_name"; if ($mysqli->query($sql)) { printf(" Coulumn Deleted seccessfully in existing table.
"); } if ($mysqli->errno) { printf("we''r getting an error.
", $mysqli->error); } $mysqli->close();
Output
The output obtained is as follows −
Coulumn added seccessfully in existing table. Coulumn Deleted seccessfully in existing table.
var mysql = require(''mysql2''); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); sql = "USE TUTORIALS" con.query(sql); sql = "CREATE TABLE STUDENTS (ID INT NOT NULL, NAME VARCHAR(40) NOT NULL);" con.query(sql); //Adding column named "AGE" sql = "ALTER TABLE STUDENTS ADD COLUMN AGE INT NOT NULL;" con.query(sql); sql = "DESCRIBE STUDENTS;" con.query(sql, function(err, result){ if (err) throw err console.log(result); console.log("--------------------------"); }); //Deleting column named "AGE" sql = "ALTER TABLE STUDENTS DROP COLUMN AGE;" con.query(sql); sql = "DESCRIBE STUDENTS;" con.query(sql, function(err, result){ if (err) throw err console.log(result); }); });
Output
The output produced is as follows −
Connected! -------------------------- [ { Field: ''ID'', Type: ''int'', Null: ''NO'', Key: '''', Default: null, Extra: '''' }, { Field: ''NAME'', Type: ''varchar(40)'', Null: ''NO'', Key: '''', Default: null, Extra: '''' }, { Field: ''AGE'', Type: ''int'', Null: ''NO'', Key: '''', Default: null, Extra: '''' } ] -------------------------- [ { Field: ''ID'', Type: ''int'', Null: ''NO'', Key: '''', Default: null, Extra: '''' }, { Field: ''NAME'', Type: ''varchar(40)'', Null: ''NO'', Key: '''', Default: null, Extra: '''' } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class AddDelColumn{ public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //Adding One Column extra into the tutorials_tbl String sql = "ALTER TABLE tutorials_tbl ADD COLUMN tutorial_name VARCHAR(30) NOT NULL AFTER tutorial_id"; statement.executeUpdate(sql); System.out.println("Column added into the tutorials table successfully...!"); //Deleting One Column from the tutorials_tbl String sql1 = "ALTER TABLE tutorials_tbl DROP COLUMN tutorial_name"; statement.executeUpdate(sql1); System.out.println("Column deleted successfully from the tutorials table ...!"); ResultSet resultSet = statement.executeQuery("DESCRIBE tutorials_tbl"); while (resultSet.next()) { System.out.print(resultSet.getNString(1)); System.out.println(); } connection.close(); }catch(Exception e) { System.out.println(e); } } }
Output
The output obtained is as shown below −
Connected successfully...! Column added into the tutorials table successfully...! Column deleted successfully from the tutorials table ...! tutorial_id tutorial_title tutorial_author submission_date
import mysql.connector #establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) table_name = ''tutorials_tbl_cloned'' column_to_add = ''tutorial_price'' column_to_delete = ''tutorial_id'' # Adding a new column cursorObj = connection.cursor() add_column_query = f"ALTER TABLE {table_name} ADD COLUMN {column_to_add} INT" cursorObj.execute(add_column_query) print(f"Column ''{column_to_add}'' is added to table ''{table_name}'' successfully.") # Deleting a column delete_column_query = f"ALTER TABLE {table_name} DROP COLUMN {column_to_delete}" cursorObj.execute(delete_column_query) print(f"Column ''{column_to_delete}'' is deleted from table ''{table_name}'' successfully.") cursorObj.close() connection.close()
Output
Following is the output of the above code −
Column ''tutorial_price'' is added to table ''tutorials_tbl_cloned'' successfully. Column ''tutorial_id'' is deleted from table ''tutorials_tbl_cloned'' successfully.