MySQL — Alter Table

Adding Columns

-- Add single column
ALTER TABLE users ADD phone VARCHAR(20);

-- Add multiple columns
ALTER TABLE users
ADD phone VARCHAR(20),
ADD address TEXT;

Modifying Columns

-- Modify column type
ALTER TABLE users MODIFY name VARCHAR(150);

-- Modify with constraints
ALTER TABLE users MODIFY email VARCHAR(100) NOT NULL;

-- Rename column
ALTER TABLE users RENAME COLUMN phone TO telephone;

Dropping Columns

-- Drop single column
ALTER TABLE users DROP phone;

-- Drop multiple columns
ALTER TABLE users DROP phone, DROP address;

Renaming Tables

-- Rename table
ALTER TABLE users RENAME TO customers;

-- Or use RENAME
RENAME TABLE users TO customers;

Alter Table Examples

-- Add columns
ALTER TABLE products
ADD category VARCHAR(50),
ADD stock INT DEFAULT 0;

-- Modify columns
ALTER TABLE products MODIFY price DECIMAL(10,2) NOT NULL;

-- Drop columns
ALTER TABLE products DROP description;

-- Add constraint
ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

Mini Practice

Write MySQL code that:

1. Adds a column
2. Modifies a column
3. Drops a column
4. Renames a table

Up Next

Next: Learn about Dropping Tables.