Blog

MySQL Cheat Sheet: The Ultimate Guide to Essential MySQL Commands

When you’re managing a WordPress website, developing a web application, or administering a MySQL server, this cheat sheet contains the commands you’ll use most often.

Understanding Where to Run MySQL Commands

Before you begin, it’s important to know where different commands are entered.

There are two types of commands in this guide:

Shell Commands

These are entered into your operating system’s command line.

Examples include:

  • Windows Command Prompt
  • Windows PowerShell
  • macOS Terminal
  • Linux Terminal
  • SSH session

Shell commands usually begin with programs like:

mysql
mysqldump

SQL Commands

SQL commands are entered after you’ve connected to MySQL or inside a graphical database tool such as:

  • phpMyAdmin
  • MySQL Workbench
  • DBeaver
  • Adminer
  • HeidiSQL
  • TablePlus

These commands begin with SQL keywords such as:

SELECT
INSERT
UPDATE
DELETE
CREATE
ALTER
DROP
SHOW

Connect to MySQL

Where to run these commands

Run these commands from:

  • Terminal
  • PowerShell
  • Command Prompt
  • SSH

Do not enter these into phpMyAdmin.

Connect to MySQL:

mysql -u username -p

Connect directly to a database:

mysql -u username -p database_name

Connect to a remote server:

mysql -h hostname -u username -p

Database Commands

Where to run these commands

These commands can be run:

  • After connecting through the MySQL command line
  • In phpMyAdmin’s SQL tab
  • MySQL Workbench
  • DBeaver
  • Adminer

Show all databases:

SHOW DATABASES;

Create a database:

CREATE DATABASE database_name;

Delete a database:

DROP DATABASE database_name;

⚠️ Permanently deletes the database.

Select a database:

USE database_name;

Show the currently selected database:

SELECT DATABASE();

Table Commands

Where to run these commands

Use these commands from:

  • MySQL CLI
  • phpMyAdmin
  • MySQL Workbench
  • DBeaver

Show tables:

SHOW TABLES;

Describe a table:

DESCRIBE table_name;

or

DESC table_name;

Create a table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Rename a table:

RENAME TABLE users TO customers;

Delete a table:

DROP TABLE users;

Delete all rows while keeping the table:

TRUNCATE TABLE users;

Insert Data

Where to run these commands

These commands work in:

  • phpMyAdmin
  • MySQL Workbench
  • MySQL CLI
  • DBeaver

Insert one record:

INSERT INTO users
(first_name,last_name,email)
VALUES
('John','Doe','john@example.com');

Insert multiple records:

INSERT INTO users
(first_name,last_name)
VALUES
('John','Doe'),
('Jane','Smith'),
('Bob','Johnson');

Viewing Data

Where to run these commands

Run these anywhere SQL queries are supported.

View every row:

SELECT *
FROM users;

View selected columns:

SELECT first_name,email
FROM users;

Return only 10 rows:

SELECT *
FROM users
LIMIT 10;

Sort alphabetically:

SELECT *
FROM users
ORDER BY first_name ASC;

Sort newest first:

SELECT *
FROM users
ORDER BY created_at DESC;

Filtering Data

Where to run these commands

Supported by every SQL editor.

Equal:

SELECT *
FROM users
WHERE id = 5;

Greater than:

WHERE id > 10;

Less than:

WHERE id < 50;

Not equal:

WHERE id != 5;

AND:

SELECT *
FROM users
WHERE active=1
AND state='TX';

OR:

SELECT *
FROM users
WHERE state='AZ'
OR state='TX';

IN:

SELECT *
FROM users
WHERE state IN ('AZ','TX','CA');

BETWEEN:

SELECT *
FROM orders
WHERE total BETWEEN 100 AND 500;

LIKE starts with:

WHERE first_name LIKE 'J%';

LIKE ends with:

WHERE email LIKE '%gmail.com';

LIKE contains:

WHERE last_name LIKE '%son%';

NULL values:

WHERE phone IS NULL;

NOT NULL:

WHERE phone IS NOT NULL;

Updating Data

Where to run these commands

Run in any SQL editor.

Update one record:

UPDATE users
SET email='new@email.com'
WHERE id=1;

⚠️ Always include a WHERE clause unless you intend to update every record.

Deleting Data

Where to run these commands

Run in any SQL editor.

Delete one row:

DELETE FROM users
WHERE id=1;

Delete every row:

DELETE FROM users;

Aggregate Functions

Where to run these commands

Supported in every SQL editor.

Count rows:

SELECT COUNT(*)
FROM users;

Average:

SELECT AVG(price)
FROM products;

Sum:

SELECT SUM(price)
FROM products;

Maximum:

SELECT MAX(price)
FROM products;

Minimum:

SELECT MIN(price)
FROM products;

Distinct values:

SELECT DISTINCT state
FROM users;

GROUP BY and HAVING

Where to run these commands

Run in any SQL editor.

SELECT state,
COUNT(*)
FROM users
GROUP BY state;
SELECT state,
COUNT(*)
FROM users
GROUP BY state
HAVING COUNT(*) > 5;

JOIN Commands

Where to run these commands

Supported by:

  • phpMyAdmin
  • MySQL CLI
  • MySQL Workbench
  • DBeaver

INNER JOIN

SELECT users.first_name,
orders.total
FROM users
INNER JOIN orders
ON users.id = orders.user_id;

LEFT JOIN

SELECT users.first_name,
orders.total
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;

RIGHT JOIN

SELECT users.first_name,
orders.total
FROM users
RIGHT JOIN orders
ON users.id = orders.user_id;

ALTER TABLE Commands

Where to run these commands

Run these in:

  • MySQL CLI
  • phpMyAdmin
  • MySQL Workbench

⚠️ Always back up your database before altering a table.

Add a column:

ALTER TABLE users
ADD phone VARCHAR(20);

Modify a column:

ALTER TABLE users
MODIFY phone VARCHAR(50);

Rename a column:

ALTER TABLE users
RENAME COLUMN phone TO mobile;

Delete a column:

ALTER TABLE users
DROP COLUMN mobile;

Index Commands

Where to run these commands

Run in any SQL editor.

Create an index:

CREATE INDEX idx_email
ON users(email);

Delete an index:

DROP INDEX idx_email
ON users;

User Management

Where to run these commands

These commands generally require administrator (root) privileges.

Run from:

  • SSH
  • MySQL CLI
  • MySQL Workbench

Many shared hosting providers manage users through cPanel or Plesk instead.

Create user:

CREATE USER 'newuser'@'localhost'
IDENTIFIED BY 'StrongPassword';

Grant privileges:

GRANT ALL PRIVILEGES
ON database_name.*
TO 'newuser'@'localhost';

Reload privileges:

FLUSH PRIVILEGES;

Revoke privileges:

REVOKE ALL PRIVILEGES
ON database_name.*
FROM 'newuser'@'localhost';

Delete user:

DROP USER 'newuser'@'localhost';

Backup and Restore

Where to run these commands

Run these from:

  • SSH
  • Terminal
  • Command Prompt
  • PowerShell

Do not enter these into phpMyAdmin.

Backup:

mysqldump -u username -p database_name > backup.sql

Restore:

mysql -u username -p database_name < backup.sql

Server Information

Where to run these commands

Supported by:

  • MySQL CLI
  • phpMyAdmin
  • MySQL Workbench

Current user:

SELECT USER();

Current database:

SELECT DATABASE();

MySQL version:

SELECT VERSION();

Running processes:

SHOW PROCESSLIST;

Kill a query:

KILL process_id;

Transactions

Where to run these commands

Supported in every SQL editor.

Start:

START TRANSACTION;

Save changes:

COMMIT;

Undo changes:

ROLLBACK;

Comments

Single line:

-- This is a comment

or

# This is a comment

Multi-line:

/*
This
is
a
comment
*/

Exit MySQL

From the MySQL prompt:

EXIT;

or

QUIT;

Common Data Types

Data Type Description
INT Integer
BIGINT Large Integer
FLOAT Floating Point Number
DECIMAL Precise Decimal
VARCHAR(n) Variable Length String
CHAR(n) Fixed Length String
TEXT Long Text
DATE Date
TIME Time
DATETIME Date & Time
TIMESTAMP Timestamp
BOOLEAN True / False
JSON JSON Document
BLOB Binary Data

Common Comparison Operators

Operator Description
= Equal
!= Not Equal
<> Not Equal
> Greater Than
< Less Than
>= Greater Than or Equal
<= Less Than or Equal
BETWEEN Between Values
IN Match Multiple Values
LIKE Pattern Matching
IS NULL Null Value
IS NOT NULL Not Null

Wildcards

Wildcard Meaning
% Zero or More Characters
_ One Character

MySQL Best Practices

  • Always back up your database before making structural changes.
  • Never run UPDATE or DELETE without confirming your WHERE clause.
  • Use prepared statements in your applications to prevent SQL injection.
  • Index columns that are frequently searched or joined.
  • Grant users only the permissions they require (principle of least privilege).
  • Use transactions for related operations that should either all succeed or all fail.
  • Test complex SELECT statements before converting them into UPDATE or DELETE queries.
  • Use descriptive table and column names to make your database easier to maintain.
  • Regularly back up and optimize your databases.

Quick Reference

SHOW DATABASES;
USE database_name;
SHOW TABLES;
DESC table_name;

SELECT * FROM table_name;

INSERT INTO table_name (...) VALUES (...);

UPDATE table_name
SET column='value'
WHERE id=1;

DELETE FROM table_name
WHERE id=1;

CREATE TABLE table_name (...);

ALTER TABLE table_name
ADD column_name VARCHAR(255);

DROP TABLE table_name;

CREATE DATABASE database_name;

DROP DATABASE database_name;

EXIT;