MySQL Cheatsheet: 50+ Quick Powerful Commands

/ Cheatsheets / By

MySQL Cheatsheet is a must-have reference for developers, database administrators, and students learning SQL. Whether you’re just starting out or managing complex production systems, this MySQL Cheatsheet provides all the core commands, syntax, and examples you need in one place.

Why Use a MySQL Cheatsheet?

MySQL is the most widely used open-source relational database management system (RDBMS). It powers applications from WordPress to enterprise systems. A MySQL Cheatsheet saves time by providing quick access to essential commands without searching through documentation.

Instead of memorizing every query, you can rely on this concise guide to boost productivity, reduce errors, and improve your SQL confidence.

Basic Syntax and Connection Commands

The foundation of every MySQL session begins with connecting to the database and running basic commands.

-- Connect to MySQL
mysql -u username -p

-- Show all databases
SHOW DATABASES;

-- Select a database
USE mydatabase;

-- Check current database
SELECT DATABASE();

These commands ensure you’re working in the correct database environment before running queries.

Data Definition Language (DDL)

DDL commands define the structure of your database. They create, modify, or delete objects like tables and indexes.

-- Create a database
CREATE DATABASE mydatabase;

-- Create a table
CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE
);

-- Modify a table
ALTER TABLE customers ADD COLUMN age INT;

-- Delete a table
DROP TABLE customers;

DDL changes are permanent and cannot be rolled back without backups.

Data Manipulation Language (DML)

DML commands handle inserting, updating, and deleting data. These operations form the backbone of CRUD (Create, Read, Update, Delete).

-- Insert data
INSERT INTO customers (name, email, age)
VALUES ('Alice Johnson', 'alice@example.com', 30);

-- Update data
UPDATE customers
SET age = 31
WHERE id = 1;

-- Delete a row
DELETE FROM customers WHERE id = 2;

DML queries directly affect stored data, so use transactions when making bulk changes.

Data Query Language (DQL)

DQL commands are all about retrieving information. The SELECT statement is the most common query in MySQL.

-- Select all records
SELECT * FROM customers;

-- Filter rows
SELECT name, email FROM customers WHERE age > 25;

-- Sort results
SELECT * FROM customers ORDER BY age DESC;

-- Limit results
SELECT * FROM customers LIMIT 5;

By combining WHERE, ORDER BY, and LIMIT, you can refine queries to retrieve only relevant data.

Data Control Language (DCL)

DCL manages permissions and security inside MySQL databases. Proper use of DCL ensures data safety.

-- Create a new user
CREATE USER 'devuser'@'localhost' IDENTIFIED BY 'strongpassword';

-- Grant privileges
GRANT SELECT, INSERT ON mydatabase.* TO 'devuser'@'localhost';

-- Revoke privileges
REVOKE DELETE ON mydatabase.* FROM 'devuser'@'localhost';

-- Remove a user
DROP USER 'devuser'@'localhost';

Security best practices recommend using least privilege principles.

Data Administration Commands

Database administrators (DBAs) rely on these commands for backups, restores, and server monitoring.

# Backup a database
mysqldump -u username -p mydatabase > backup.sql

# Restore a database
mysql -u username -p mydatabase < backup.sql

-- Show current users
SELECT user, host FROM mysql.user;

-- Check server status
SHOW STATUS;

-- Optimize a table
OPTIMIZE TABLE customers;

Routine backups and optimizations are critical for database health.

Advanced Queries and Joins

Real-world databases use multiple tables. Joins link related data for meaningful insights.

-- Inner Join
SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

-- Left Join
SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

-- Aggregate functions
SELECT AVG(age) AS avg_age FROM customers;

These queries make reporting and analytics possible directly within MySQL.

Indexes and Performance Optimization

Indexes speed up queries but can slow down inserts if overused.

-- Create index
CREATE INDEX idx_name ON customers(name);

-- Show indexes
SHOW INDEXES FROM customers;

-- Remove index
DROP INDEX idx_name ON customers;

Use EXPLAIN before queries to analyze performance and identify bottlenecks.

Transactions and ACID Properties

Transactions ensure data integrity by grouping multiple operations.

-- Begin transaction
START TRANSACTION;

-- Execute queries
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Commit changes
COMMIT;

-- Rollback if error
ROLLBACK;

Transactions guarantee Atomicity, Consistency, Isolation, and Durability (ACID).

Stored Procedures and Functions

Reusable blocks of SQL reduce repetition and improve maintainability.

-- Create stored procedure
DELIMITER //
CREATE PROCEDURE GetAllCustomers()
BEGIN
  SELECT * FROM customers;
END //
DELIMITER ;

-- Call procedure
CALL GetAllCustomers();

Stored procedures enhance performance and encapsulate business logic.

Views and Virtual Tables

Views act as saved queries for simplifying complex operations.

-- Create a view
CREATE VIEW adult_customers AS
SELECT name, email FROM customers WHERE age >= 18;

-- Query a view
SELECT * FROM adult_customers;

-- Drop a view
DROP VIEW adult_customers;

They improve readability but may impact performance if overused.

Best Practices for MySQL Users

  • Always backup databases before major changes.
  • Use indexes wisely to balance speed and storage.
  • Follow the principle of least privilege for user permissions.
  • Optimize queries with EXPLAIN before deploying.
  • Regularly update MySQL for security patches.

FAQ on MySQL Cheatsheet

Q1: What is a MySQL Cheatsheet?

A MySQL Cheatsheet is a quick reference guide containing essential SQL commands, syntax, and examples for working with MySQL databases.

Q2: Who should use a MySQL Cheatsheet?

Both beginners and advanced developers use a MySQL Cheatsheet to quickly recall commands and avoid errors in daily database operations.

Q3: Does this MySQL Cheatsheet cover advanced topics?

Yes. It includes queries, joins, transactions, stored procedures, views, and administration commands beyond just basics.

Q4: Can I rely only on a MySQL Cheatsheet?

While a cheatsheet is helpful, it’s best used alongside MySQL documentation and practical experience for deeper understanding.

Q5: Is this MySQL Cheatsheet updated for 2025?

Yes. All commands and examples here follow MySQL 8.0 standards, ensuring accuracy and compatibility with current systems.

Related Posts

Scroll to Top