Code snippets
Code snippets
Copy-ready answers to everyday coding questions, each with a short explanation. Laravel, PHP, JavaScript, SQL, Bash and more.
6 snippets · Show all
SQL
October 20, 2026
MySQL backup and restore a database with mysqldump
Dumps a database to a file and restores it later. --single-transaction gives a consistent backup of InnoDB tables without locking them.
# Backup
mysqldump -u root -p --single-transaction mydb > mydb.sql
# Backup compressed
SQL
October 20, 2026
MySQL group by month and count rows per period
Counts rows per month for reports and charts. DATE_FORMAT builds the month label to group on.
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
COUNT(*) AS orders,
SUM(total) AS revenue
FROM orders
SQL
October 20, 2026
MySQL add an index and check it with EXPLAIN
Adds an index to speed up a slow WHERE clause, then uses EXPLAIN to confirm the query uses it. Check that key is set and rows is small.
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);
EXPLAIN
SELECT * FROM orders
SQL
October 20, 2026
MySQL INSERT ON DUPLICATE KEY UPDATE upsert
Inserts a row, or updates it if a unique key already exists. The table needs a UNIQUE or PRIMARY KEY on the column you match.
INSERT INTO page_views (page, views)
VALUES ('/home', 1)
ON DUPLICATE KEY UPDATE views = views + 1;
SQL
October 20, 2026
MySQL get the Nth highest salary using LIMIT OFFSET
A classic interview question. DISTINCT skips ties, and OFFSET N-1 jumps to the Nth value. This example returns the 2nd highest.
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1; -- change the offset for the 3rd, 4th, etc.
SQL
October 20, 2026
MySQL find and delete duplicate rows keeping the lowest id
Finds rows that repeat a column value and deletes all but the one with the smallest id. Run the SELECT first to preview what will be removed, and back up the ta...
-- Preview duplicates
SELECT email, COUNT(*) AS total
FROM users
GROUP BY email