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;

Related Database Resources

Want to Practice SQL?

Try these concepts with real-world exercises

Start Practicing Now