MySQL DML Cheat Sheet

Data Manipulation Language (DML) is used to manage data within tables: querying, inserting, updating, and deleting records.

---

SELECT

-- all columns
SELECT * FROM table;
SELECT * FROM table1, table2;

-- specific columns
SELECT field1, field2 FROM table1, table2;

-- basic select
SELECT ... FROM ... WHERE condition
SELECT ... FROM ... WHERE condition GROUP BY field;
SELECT ... FROM ... WHERE condition GROUP BY field HAVING condition2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2;
SELECT ... FROM ... WHERE condition ORDER BY field1, field2 DESC;
SELECT ... FROM ... WHERE condition LIMIT 10;

-- distinct values
SELECT DISTINCT field1 FROM table;
SELECT DISTINCT field1 FROM ...
SELECT DISTINCT field1, field2 FROM ...

Basic Select

SELECT column1, column2
FROM table_name;

All Columns

SELECT * FROM table_name;

Aliases

SELECT column_name AS alias_name
FROM table_name;

WHERE Clause

SELECT * FROM table_name
WHERE condition;

Logical Operators

WHERE age > 25 AND city = 'London';
WHERE status = 'active' OR status = 'pending';
WHERE NOT gender = 'M';

ORDER BY

SELECT * FROM table_name
ORDER BY column1 [ASC|DESC];

LIMIT

SELECT * FROM table_name
LIMIT 10 OFFSET 5;

IN, BETWEEN, LIKE

WHERE id IN (1, 2, 3);
WHERE price BETWEEN 10 AND 50;
WHERE name LIKE 'A%';  -- starts with A

INSERT

Single Row

INSERT INTO table_name (column1, column2)
VALUES ('value1', 'value2');

Multiple Rows

INSERT INTO table_name (col1, col2)
VALUES 
  ('v1', 'v2'),
  ('v3', 'v4');

Insert From Another Table

INSERT INTO table2 (col1, col2)
SELECT colA, colB FROM table1
WHERE condition;

UPDATE

Basic Update

UPDATE table_name
SET column1 = 'new_value'
WHERE condition;

Multiple Columns

UPDATE users
SET name = 'John Doe',
    email = 'john@example.com'
WHERE id = 5;

⚠️ Always use a WHERE clause unless you're updating every row.

DELETE

Delete Rows

DELETE FROM table_name
WHERE condition;

Delete All Rows

DELETE FROM table_name;

⚠️ This keeps the table structure but removes all records.

TIPS

NULL Handling

WHERE column IS NULL;
WHERE column IS NOT NULL;

Conditional Expressions

SELECT id,
  IF(active = 1, 'Yes', 'No') AS is_active
FROM users;

Aggregate Functions

SELECT COUNT(*), AVG(price), MAX(score)
FROM table_name;

GROUP BY and HAVING

SELECT city, COUNT(*) AS total
FROM users
GROUP BY city
HAVING total > 10;

JOINs

-- INNER JOIN
SELECT a.col, b.col
FROM table_a a
JOIN table_b b ON a.id = b.a_id;

-- LEFT JOIN
SELECT *
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Safe Practices

• always BACK UP before mass updates/deletes
• use WHERE + LIMIT when testing destructive operations
• use transactions for multiple related DML operations

---