What is ALTER TABLE?
ALTER TABLE changes an existing table. Use it when you need to:
- Add a new column you forgot
- Remove a column you don't need
- Rename a column
Add a Column
ALTER TABLE students
ADD email VARCHAR(100);This adds a new email column to the students table.
Remove a Column
ALTER TABLE students
DROP COLUMN email;This removes the email column (and all its data!).
Rename a Column
ALTER TABLE students
RENAME COLUMN name TO full_name;This changes the column name from name to full_name.
Example: Building a Table Step by Step
Start with a basic table:
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name VARCHAR(100)
);Forgot email? Add it:
ALTER TABLE users
ADD email VARCHAR(100);Need age too? Add it:
ALTER TABLE users
ADD age INTEGER;Now the table has: id, name, email, age
How ALTER TABLE Works (Step by Step)
Step 1: Write ALTER TABLE command
Step 2: Specify which table to change
Step 3: Choose the action (ADD, DROP, or RENAME)
Step 4: Provide column details
Step 5: End with semicolon ;
ALTER TABLE users -- Step 1 & 2
ADD email VARCHAR(100); -- Step 3, 4 & 5Quick Reference
- ADD - Add new column
- DROP COLUMN - Remove a column
- RENAME COLUMN - Change column name
Warning: DROP COLUMN deletes all data in that column. Be careful!