The MySQL Cheat Sheet: Queries You'll Actually Use

When you start working with databases, MySQL is usually the first system you'll encounter. It's battle-tested and powers everything from small side projects to massive enterprise applications.

Instead of reading a massive textbook, this guide covers the 20% of commands you'll use 80% of the time!

1. Databases & Tables

CREATE DATABASE zoo_db;
USE zoo_db;

CREATE TABLE habitat (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64));
CREATE TABLE animal (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(64),
  habitat_id INT,
  FOREIGN KEY (habitat_id) REFERENCES habitat(id)
);

2. Data Manipulation (CRUD)

INSERT INTO habitat (name) VALUES ('River'), ('Forest');

UPDATE animal SET name = 'Quack' WHERE id = 2;

DELETE FROM animal WHERE id = 1;

3. Querying and Aggregation

The Power of GROUP BY

Want to find the average age of each animal species?

SELECT species, AVG(age) as average_age
FROM animal
WHERE id != 3
GROUP BY species
HAVING AVG(age) > 3
ORDER BY AVG(age) DESC;

This powerful query filters, groups, aggregates, filters the groups, and sorts the final result!

Joining Tables

To combine data from our two tables and view the animal's name alongside its habitat's name:

SELECT animal.name AS animal_name, habitat.name AS habitat_name
FROM animal
INNER JOIN habitat ON animal.habitat_id = habitat.id;

You Might Also Like

• Database Seeding Strategies with Prisma and TypeScript
• Turso & libSQL: Distributed SQLite with Embedded Replicas for Low-Latency Backends
• Distributed Locking in Distributed Systems: Redlock, PostgreSQL Advisory Locks & etcd Leases
• Redis Memory Optimization: Internals, Data Structure Encoding, and Memory Profiling