CREATE TABLE declares a table's name and its columns, each with a type and optional constraints. Once created, ALTER TABLE can add, remove, or modify columns without recreating the whole table — though some changes (like changing a column's type on a table with existing data) can be slow or restricted depending on the engine. DROP TABLE deletes the table structure and every row of data in it, permanently, with no confirmation and no undo.
CREATE TABLE: defining the schema
Each column gets a name, type, and optional constraints, all defined together up front.
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- avoid errors if it might already exist:
CREATE TABLE IF NOT EXISTS users (...);ALTER TABLE: changing an existing schema
Adding a column is generally fast; changing a column's type on a large existing table can require rewriting every row and may briefly lock the table, depending on the engine — worth checking your specific engine's behavior before running this on a production table with real traffic.
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN email TO email_address;
ALTER TABLE users ALTER COLUMN email_address TYPE TEXT; -- syntax varies by engineDROP TABLE: permanent, no undo
There's no confirmation prompt and no recycle bin — the only real safety net is a database backup taken beforehand. IF EXISTS avoids an error when the table might not exist, but does nothing to protect data that does.
DROP TABLE IF EXISTS old_logs;
-- gone. every row, every index, every constraint on old_logs — permanently.