Khóa học miễn phí MySQL – Add/Delete Columns nhận dự án làm có lương

MySQL – Add/Delete Columns

Table of content


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.  

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc