MySQL Cheat Sheet

📋 What is MySQL Cheat Sheet?

A MySQL cheat sheet is a quick reference guide containing the most commonly used MySQL commands and syntax. It helps developers quickly find and use database operations without memorizing every command.


-- Quick example of basic MySQL commands
SELECT * FROM users;
INSERT INTO users (name, email) VALUES ('John', '[email protected]');
UPDATE users SET name = 'Jane' WHERE id = 1;

Essential MySQL Commands

🔍

SELECT

Retrieve data from database tables

SELECT * FROM table_name;
SELECT column1, column2 FROM table_name;

➕

INSERT

Add new records to tables

INSERT INTO table_name (col1, col2) 
VALUES ('value1', 'value2');

✏️

UPDATE

Modify existing records

UPDATE table_name 
SET column1 = 'value' 
WHERE condition;

🗑️

DELETE

Remove records from tables

DELETE FROM table_name 
WHERE condition;

🔹 Database Operations

Create, manage, and delete databases with these fundamental commands. These operations form the foundation of MySQL database management and are essential for organizing your data structures.

-- Create a new database
CREATE DATABASE my_database;

-- Show all databases
SHOW DATABASES;

-- Use a specific database
USE my_database;

-- Delete a database
DROP DATABASE my_database;

🔹 Table Operations

Tables store your data in organized rows and columns. Learn how to create tables with different data types, view table structures, modify existing tables, and remove tables when no longer needed.

-- Create a new table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Show all tables
SHOW TABLES;

-- Describe table structure
DESCRIBE users;

-- Delete a table
DROP TABLE users;

🔹 Filtering and Sorting

Filter data using WHERE clauses to find specific records, sort results in ascending or descending order, and limit the number of returned rows. These commands help you retrieve exactly the data you need efficiently.

-- Filter with WHERE
SELECT * FROM users WHERE age > 18;

-- Sort results
SELECT * FROM users ORDER BY name ASC;

-- Limit results
SELECT * FROM users LIMIT 10;

-- Combine conditions
SELECT * FROM users 
WHERE age > 18 AND country = 'USA' 
ORDER BY name 
LIMIT 5;

🔹 Joins

Combine data from multiple tables using different join types. INNER JOIN returns matching records, LEFT JOIN includes all left table records, RIGHT JOIN includes all right table records, and FULL JOIN combines both sides.

-- INNER JOIN
SELECT users.name, orders.product 
FROM users 
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN
SELECT users.name, orders.product 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id;

-- RIGHT JOIN
SELECT users.name, orders.product 
FROM users 
RIGHT JOIN orders ON users.id = orders.user_id;

🔹 Aggregate Functions

Perform calculations on data sets using built-in functions. COUNT returns the number of rows, SUM adds numeric values, AVG calculates averages, MAX finds the highest value, and MIN finds the lowest value.

-- Count records
SELECT COUNT(*) FROM users;

-- Sum values
SELECT SUM(price) FROM orders;

-- Average
SELECT AVG(age) FROM users;

-- Maximum and Minimum
SELECT MAX(salary), MIN(salary) FROM employees;

-- Group by
SELECT country, COUNT(*) 
FROM users 
GROUP BY country;

🔹 Indexes and Keys

Improve query performance with indexes and define relationships with keys. Primary keys uniquely identify records, foreign keys link tables together, and indexes speed up data retrieval by creating quick lookup structures.

-- Add primary key
ALTER TABLE users ADD PRIMARY KEY (id);

-- Add foreign key
ALTER TABLE orders 
ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- Create index
CREATE INDEX idx_email ON users(email);

-- Remove index
DROP INDEX idx_email ON users;