MySQL- Add multiple columns after a specific column

This article will be looking into how to add multiple columns after the last or a specific column in MySQL.

Table of Contents

• MySQL- how to add multiple columns after the last column
• MySQL -how to add multiple columns after a specific column
• MySQL -how to add multiple columns after different specific columns
• MySQL -how to add multiple columns with default values

We will be moving into details of each section but let us first make the data to be used across. We will be creating a table students_data followed by inserting a few rows into it.

CREATE TABLE students_data (
student_id INT AUTO_INCREMENT,
student_first_name VARCHAR(255),
student_address VARCHAR(255),
student_grade VARCHAR(255),
student_subject VARCHAR(255),
 PRIMARY KEY (student_id)
);
INSERT INTO students_data (student_first_name, student_address, student_grade, student_subject) 
VALUES("Gustav","56A Denmark","A","Physics"),
("Henric","255 USA","B","Geography"),
("Richa","78 India","C","Physics"),
("Margit","58 Canada","A","Physics"),	
("Rasmus","18 Libya","B","Physics"),
("Erick","43 Sweden","C","Geography"),
("Tanya","78 Singapore","D","Geography")

SELECT * FROM students_data;

MySQL- how to add multiple columns after the last column

In this section, let us see how to add multiple columns to a table. By default, new columns are added after the last column in the table. Observe the below query.

ALTER TABLE students_data 
ADD COLUMN student_gender VARCHAR(50) ,
ADD COLUMN  student_email VARCHAR(255),    
ADD COLUMN  student_age INT;

Action Output Message Response:-

0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0

Let us check if the columns got added in the students_data table by executing:

SELECT * FROM students_data LIMIT 1;

Output:-

Figure 2 shows that three new columns, student_gender, student_email, and student_age, were added with null values after the table’s last column.

MySQL -how to add multiple columns after a specific column

This section lets us see how to add multiple columns to a table after a specific column. Observe the below query.

ALTER TABLE students_data 
ADD COLUMN student_gender VARCHAR(50) ,
ADD COLUMN  student_email VARCHAR(255),    
ADD COLUMN  student_age INT AFTER student_grade;

Action Output Message Response:-

0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0

Let us check if the columns got added by executing:

SELECT * FROM students_data LIMIT 1;

Output:-

Figure 3 shows that three new columns, student_gender, student_email, and student_age, got added with null values to the students_data table.

student_age got added after student_grade and student_gender, student_email got added after the last column of the table students_data.

MySQL -how to add multiple columns after different columns

In this section, let us see how to add multiple columns to a table after different columns. Observe the below query.

ALTER TABLE students_data 
ADD COLUMN student_last_name VARCHAR(255) AFTER student_first_name,
ADD COLUMN student_email VARCHAR(255)AFTER student_last_name, 
ADD COLUMN student_gender VARCHAR(50) AFTER student_email,
ADD COLUMN student_age INT AFTER student_subject;

Action Output Message Response:-

0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0

Let us check if the columns got added to the students_data table by executing:

SELECT * FROM students_data LIMIT 1;

Figure 4 shows that four new columns student_last_name, student_email, student_gender and student_age got added with null values to the students_data table.

student_last_name is added after student_first_name. student_email is added after student_last_name. student_gender is added after student_email and student_age after student_subject.

MySQL -how to add multiple columns with default value

This section lets us see how to add multiple columns to a table with default values.

We will be adding student_email with a default value of “[email protected]” and student_age with a default value of 18.

ALTER TABLE students_data 
ADD COLUMN  student_email VARCHAR(255) DEFAULT "[email protected]",    
ADD COLUMN  student_age INT DEFAULT 18 ;

Action Output Message Response:-

0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0

Let us check if the above query worked by executing:

SELECT * FROM students_data LIMIT 1;

Figure 5 shows that student_email and student_age got added to the students_data table with default values [email protected] and 18, respectively.

We hope this article provided a good understanding of adding multiple columns to a table. Good Luck !!!.