MySQL Cheatsheet

Quick reference guide for MySQL database operations

Introduction to SQL

SQL (Structured Query Language) is the standard language for managing relational databases.

SQL Command Types

DDL: Data Definition Language (CREATE, ALTER, DROP)
DML: Data Manipulation Language (INSERT, UPDATE, DELETE)
DQL: Data Query Language (SELECT)
DCL: Data Control Language (GRANT, REVOKE)
TCL: Transaction Control Language (COMMIT, ROLLBACK)

Basic Example

SELECT * FROM customers 
WHERE country = 'USA' 
ORDER BY last_name 
LIMIT 10;

Setting Up SQL Environment

Installation

MySQL Community Server
MySQL Workbench (GUI)
XAMPP/WAMP bundles

Online Tools

SQLFiddle
DB Fiddle
SQLite Online

IDEs

MySQL Workbench
DBeaver
phpMyAdmin
HeidiSQL

Database and Table Operations

Database Creation

CREATE DATABASE my_database;
USE my_database;

Table Creation

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE,
  hire_date DATE DEFAULT CURRENT_DATE,
  salary DECIMAL(10,2) CHECK (salary > 0)
);

Common Data Types

INT: Integer
VARCHAR(n): Variable string

DATE: Date only
DATETIME: Date + time

BOOLEAN: True/False
DECIMAL(m,n): Precise numbers

Basic SQL Queries

Filtering Data

SELECT product_name, price
FROM products
WHERE price > 100 
  AND category = 'Electronics'
ORDER BY price DESC
LIMIT 5;

Sorting & Limiting

SELECT * FROM customers
ORDER BY last_name ASC, 
         first_name ASC
LIMIT 10 OFFSET 20;

Modifying Data (DML)

INSERT

INSERT INTO employees 
(name, email, salary)
VALUES 
('John Doe', 'john@example.com', 50000);

UPDATE

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Sales';

DELETE

DELETE FROM products
WHERE discontinued = 1;

Advanced Filtering

IN, BETWEEN, LIKE

SELECT * FROM products
WHERE category IN ('Electronics', 'Books')
  AND price BETWEEN 50 AND 200
  AND product_name LIKE '%Phone%';

NULL Handling

SELECT * FROM customers
WHERE phone IS NOT NULL;

UPDATE employees
SET manager_id = NULL
WHERE manager_id = 10;

Working with Multiple Tables

INNER JOIN

SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c 
  ON o.customer_id = c.customer_id;

LEFT JOIN

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
  ON e.dept_id = d.dept_id;

Self Join Example

SELECT e1.name AS employee, 
       e2.name AS manager
FROM employees e1
LEFT JOIN employees e2
  ON e1.manager_id = e2.employee_id;

Aggregation and Grouping

Aggregate Functions

SELECT 
  COUNT(*) AS total_products,
  AVG(price) AS avg_price,
  MIN(price) AS min_price,
  MAX(price) AS max_price,
  SUM(stock) AS total_stock
FROM products;

GROUP BY & HAVING

SELECT department_id, 
  COUNT(*) AS emp_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

Key Differences

WHERE filters before grouping
HAVING filters after grouping
Non-aggregated columns in SELECT must be in GROUP BY
Aggregate functions can't be used in WHERE

Subqueries

Single-Row Subquery

SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
);

Multi-Row Subquery

SELECT product_name, price
FROM products
WHERE category_id IN (
  SELECT category_id
  FROM categories
  WHERE department = 'Electronics'
);

Correlated Subquery

SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

Views and Indexes

Creating Views

CREATE VIEW high_paid_employees AS
SELECT id, name, salary, department
FROM employees
WHERE salary > 100000
WITH CHECK OPTION;

SELECT * FROM high_paid_employees;

Creating Indexes

CREATE INDEX idx_employee_name 
ON employees(name);

CREATE UNIQUE INDEX idx_customer_email
ON customers(email);

CREATE INDEX idx_order_dates
ON orders(order_date, shipped_date);

Index Best Practices

Index columns frequently used in WHERE, JOIN, ORDER BY
Avoid over-indexing (slows down INSERT/UPDATE)
Consider composite indexes for multi-column queries
Monitor index usage and remove unused indexes

Basic SQL Functions

String Functions

SELECT 
  CONCAT(first_name, ' ', last_name) AS full_name,
  UPPER(email) AS email_upper,
  SUBSTRING(phone, 1, 3) AS area_code,
  LENGTH(address) AS addr_length
FROM customers;

Date Functions

SELECT 
  NOW() AS current_datetime,
  DATE(order_date) AS order_date_only,
  DATEDIFF(due_date, CURDATE()) AS days_remaining,
  DATE_ADD(hire_date, INTERVAL 1 YEAR) AS anniversary
FROM orders;

Math Functions

SELECT 
  ROUND(price * 1.1, 2) AS price_with_tax,
  ABS(discount) AS absolute_discount,
  MOD(quantity, 10) AS remainder,
  CEILING(weight) AS rounded_weight
FROM products;

Transactions and ACID

Transaction Control

START TRANSACTION;

UPDATE accounts 
SET balance = balance - 100
WHERE id = 123;

UPDATE accounts 
SET balance = balance + 100
WHERE id = 456;

COMMIT;
-- or ROLLBACK if error occurs

ACID Properties

Atomicity: All or nothing execution
Consistency: Valid state transitions
Isolation: Concurrent transactions don't interfere
Durability: Committed changes persist

Stored Procedures & Functions

Stored Procedure

DELIMITER //
CREATE PROCEDURE update_salary(
  IN emp_id INT,
  IN increase_percent DECIMAL(5,2)
)
BEGIN
  UPDATE employees
  SET salary = salary * (1 + increase_percent/100)
  WHERE id = emp_id;
END //
DELIMITER ;

CALL update_salary(101, 10);

User-Defined Function

DELIMITER //
CREATE FUNCTION get_employee_age(
  emp_id INT
) RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE age INT;
  SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE())
  INTO age
  FROM employees
  WHERE id = emp_id;
  RETURN age;
END //
DELIMITER ;

SELECT name, get_employee_age(id) AS age
FROM employees;

Quick reference guide

Comprehensive MySQL Query Cheatsheet Reference

This MySQL Query cheatsheet on Nikhil Learn Hub collects syntax, commands, and practical snippets for quick revision. Practice MySQL queries, joins, clauses, functions, and database operations using clear syntax and real-world examples.

Use the reference cards and examples above during coding sessions; return here instead of scattered searches when you need dependable reminders. Follow the MySQL learning roadmap when you want structured lessons beyond one-page lookups.

Quick lookup coverage

Syntax, commands, and API signatures
Copy-ready examples and common patterns
Terminology for coursework and interviews
Cross-links to the matching learning roadmap

How to study with this sheet

Production debugging and tuning reminders
Security, performance, or scale cautions
Integration with adjacent stacks on this site
Deeper study through tutorials and roadmaps

Who Should Use This Cheatsheet

Students, self-taught developers, and professionals who need fast MySQL Query lookups during labs, debugging, or interview revision should keep this page bookmarked.

Related Resources on Nikhil Learn Hub

MySQL learning roadmapstructured learning path for the same technology
Cheatsheets hubbrowse all quick-reference sheets
Technology hubtutorials, roadmaps, and practice hubs