We can change the structure of a table using the ALTER TABLE
command. We can
- Add a column
- Rename a column
- Modify a column
- Delete a column
- Rename a table
Add Column in a Table
We can add columns in a table using the ALTER TABLE
command with the ADD
clause. For example,
ALTER TABLE Customers
ADD phone varchar(10);
Here, the SQL command adds a column named phone in the Customers table.
Add Multiple Columns in a Table
We can also add multiple columns at once in a table. For example,
ALTER TABLE Customers
ADD phone varchar(10), age int;
Here, the SQL command adds the phone and age column in the Customers table.
Rename Column in a Table
We can rename columns in a table using the ALTER TABLE
command with the RENAME COLUMN
clause. For example,
ALTER TABLE Customers
RENAME COLUMN customer_id TO c_id;
Here, the SQL command changes the column name of customer_id to c_id in the Customers table.
Modify Column in a Table
We can also change the column's data type using the ALTER TABLE
command with MODIFY
or ALTER COLUMN
clause. For example,
SQL Server
ALTER TABLE Customers
ALTER COLUMN age VARCHAR(2);
MySQL
ALTER TABLE Customers
MODIFY COLUMN age VARCHAR(2);
Oracle
ALTER TABLE Customers
MODIFY age VARCHAR(2);
PostgreSQL
ALTER TABLE Customers
ALTER COLUMN age TYPE VARCHAR(2);
Here, the SQL command changes the data type of the age column to VARCHAR
in the Customers table.
Drop Column in a Table
We can also drop (remove) columns in a table using the ALTER TABLE
command with the DROP
clause. For example,
ALTER TABLE Customers
DROP COLUMN age;
Here, the SQL command removes the phone column from the Customers table.
Rename a Table
We can change the name of a table using the ALTER TABLE
command with the RENAME
clause. For example,
ALTER TABLE Customers
RENAME TO newCustomers;
Here, the SQL command renames the Customers table to newCustomers.