Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Add/Delete Columns

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

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 
PHPNodeJSJavaPython
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);
Example
Following are the programs 
PHPNodeJSJavaPython
$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.
﻿
﻿
﻿
﻿
﻿
﻿
﻿

Field	Type	Null	Default
S_NO	int	NO	NULL
ID	int	NO	NULL
GENDER	varchar(10)	YES	NULL
NAME	varchar(20)	NO	NULL
AGE	int	NO	NULL

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Add/Delete Columns

Adding Columns to a MySQL table

Syntax

Example

Output

Verification

Example

Output

Verification

Example

Output

Verification

Adding Multiple Columns

Example

Output

Deleting Columns from a MySQL table

Syntax

Example

Output

Verification

Example

Output

Verification

Adding/Deleting column in a table Using a Client Program

Syntax

Example

Output