ITInsightHub

SQL cheat sheet: modifying data & tables

The write path has two halves: the DML statements that change rows (INSERT, UPDATE, DELETE) and the DDL statements that change structure (CREATE, ALTER, DROP). The first rule for all of them: write the WHERE before the statement.

The second rule: wrap changes in a transaction. A transaction lets you run several statements and only commit them when they all succeeded — otherwise roll everything back, leaving the database exactly as it was.

Published

INSERT, UPDATE, DELETE

The three DML statements, with the WHERE-first habit
-- INSERT one row (or several, with commas)
INSERT INTO employees (name, department_id, salary)
VALUES ('Ada', 1, 120000);

-- UPDATE — always write WHERE first
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 1;

-- DELETE — WHERE first, always
DELETE FROM employees
WHERE id = 42;

-- DELETE every row (no WHERE) — truncate is usually faster for full clears
TRUNCATE TABLE employees;
An UPDATE or DELETE without a WHERE touches every row. The habit that prevents disaster: type UPDATE employees SET salary = salary * 1.05 WHERE department_id = 1; as the WHERE clause first, then back up and fill in the SET. Most databases let you wrap the statement in a transaction and ROLLBACK if the affected-row count surprises you.

Creating tables and constraints

A CREATE TABLE with the constraints that matter
CREATE TABLE employees (
  id            INTEGER PRIMARY KEY,
  name          TEXT NOT NULL,
  email         TEXT UNIQUE,
  department_id INTEGER,
  salary        NUMERIC DEFAULT 0,
  CHECK (salary >= 0),
  FOREIGN KEY (department_id) REFERENCES departments (id)
);

The constraint vocabulary

PRIMARY KEY
uniquely identifies each row — one per table, implicitly NOT NULL
FOREIGN KEY ... REFERENCES
enforces that a value exists in another table (referential integrity)
UNIQUE
no two rows may share this value; unlike PRIMARY KEY, allows NULLs
NOT NULL
the column cannot be NULL
DEFAULT value
the value used when an INSERT omits the column
CHECK (condition)
validates each row against a condition, e.g. CHECK (salary >= 0)
Type names are the one genuinely vendor-specific part: INTEGER/INT works everywhere, but text is TEXT in PostgreSQL and SQLite, VARCHAR(n) in MySQL and SQL Server, and auto-incrementing keys are SERIAL (PostgreSQL), AUTO_INCREMENT (MySQL) or IDENTITY (SQL Server).

ALTER, DROP and indexes

Change structure and speed up queries
-- Add, rename and drop columns
ALTER TABLE employees ADD COLUMN hired_on DATE;
ALTER TABLE employees RENAME COLUMN salary TO annual_salary;
ALTER TABLE employees DROP COLUMN hired_on;

-- Drop a table (and its data)
DROP TABLE employees;

-- Index the column you filter on most — the standard speed-up
CREATE INDEX idx_employees_department ON employees (department_id);
Indexes make lookups on a column fast at the cost of slower writes and extra storage. The rule of thumb: index foreign keys and columns you filter or join on, and leave rarely-queried columns alone. Most databases auto-index PRIMARY KEY and UNIQUE columns, so don't re-index those.

Transactions

All-or-nothing changes — commit only when every step succeeded
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If both updates are correct:
COMMIT;

-- If anything went wrong instead:
ROLLBACK;
MySQL's START TRANSACTION is the equivalent of BEGIN. The point of a transaction is atomicity: either both updates land or neither does, so a crash or error mid-way cannot leave the money in only one account. Use ROLLBACK freely during manual work — until you COMMIT, nothing is permanent.

References

FAQ

How do I undo a DELETE or UPDATE I just ran?

If it is inside a transaction that has not committed yet, run ROLLBACK. Once COMMIT has run (or autocommit applied the statement), the change is permanent and must be repaired from backups or a manual compensating statement. That is why the two habits matter: write WHERE first, and wrap non-trivial changes in a transaction.

What is the difference between DELETE and TRUNCATE?

DELETE removes rows one at a time and can carry a WHERE clause, firing triggers and logging each row. TRUNCATE removes all rows in one fast operation that resets table storage and (in most databases) cannot be rolled back row-by-row and cannot have a WHERE clause. Use DELETE for selective removal, TRUNCATE for a full clear.

What is the difference between PRIMARY KEY and UNIQUE?

Both enforce uniqueness, but a table has exactly one PRIMARY KEY, which is the row's identity, is implicitly NOT NULL, and is the default target of foreign keys. UNIQUE can be applied to any number of columns and allows NULL values (in most databases). A table can have several UNIQUE constraints but only one primary key.

Why would I add an index?

An index lets the database find rows by a column value in logarithmic time instead of scanning every row. It speeds up WHERE, JOIN and ORDER BY on that column. The trade-off is slower INSERT/UPDATE/DELETE and more storage, so index the columns you actually query, not everything.