ADD COLUMN

The ALTER TABLE ... ADD COLUMN command in SQL is used to add one or more columns to an existing table. Here’s the syntax for adding multiple columns:

ALTER TABLE table_name 
ADD COLUMN column_name1 data_type1 [options],
ADD COLUMN column_name2 data_type2 [options],
...;

• table_name: the name of the table where you want to add new columns.
• column_name: the name of each new column you want to add.
• data_type: the data type for each new column.
• [options]: optional settings for each column, such as NOT NULL, DEFAULT value, etc.

Example

Suppose you have a table named customers and want to add two columns: email of type VARCHAR(255) and birth_date of type DATE. The command would look like this:

ALTER TABLE customers 
ADD COLUMN email VARCHAR(255),
ADD COLUMN birth_date DATE;

This command will add the email and birth_date columns to the customers table.

DROP COLUMN

The ALTER TABLE ... DROP COLUMN command in SQL is used to delete a column from an existing table. Here’s the syntax:

ALTER TABLE table_name DROP COLUMN column_name;

• table_name: the name of the table from which you want to remove the column.
• column_name: the name of the column you want to delete.

Example

If you have a table named customers and you want to remove a column called email, the command would look like this:

ALTER TABLE customers DROP COLUMN email;

Warning: Dropping a column is a permanent action and will remove all data stored in that column.

This command will delete the email column from the customers table.

ADD COLUMN AFTER

The ALTER TABLE ... ADD COLUMN ... AFTER command in SQL is used to add one or more columns to an existing table, specifying the position of the new columns relative to an existing column. Here’s the syntax for adding multiple columns after a specific column:

ALTER TABLE table_name 
ADD COLUMN column_name1 data_type1 [options] AFTER existing_column_name,
ADD COLUMN column_name2 data_type2 [options] AFTER existing_column_name,
...;

• table_name: the name of the table where you want to add new columns.
• column_name: the name of each new column you want to add.
• data_type: the data type for each new column.
• existing_column_name: the existing column after which the new columns will be added.
• [options]: any optional settings for each column, such as NOT NULL, DEFAULT value, etc.

Example

Suppose you have a table named customers and want to add two columns, email of type VARCHAR(255) and birth_date of type DATE, placing them after an existing column called name. The command would look like this:

ALTER TABLE customers 
ADD COLUMN email VARCHAR(255) AFTER name,
ADD COLUMN birth_date DATE AFTER name;

This command will add the email and birth_date columns to the customers table, positioning them after the name column.