MySQL Command Cheat Sheet: Quick Reference

You can find a download PDF copy at the bottom of the cheat sheet.

Logging In and Out

Log in to MySQL.

mysql -u username -p

Log out of MySQL.

exit;

Working with Databases

Create a database.

CREATE DATABASE database_name;

Delete a database (be cautious).

DROP DATABASE database_name;

List all databases.

SHOW DATABASES;

Select a database to work with.

USE database_name;

Working with Tables

Create a table.

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);

Delete a table.

DROP TABLE table_name;

Show table structure.

DESCRIBE table_name;

Rename a table.

RENAME TABLE old_table TO new_table;

Data Manipulation

Insert a new row into a table.

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Update data in a table.

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

Delete data from a table.

DELETE FROM table_name WHERE condition;

Select data from a table.

SELECT column1, column2 FROM table_name WHERE condition;

Querying and Filtering

Filter data using WHERE clause.

SELECT * FROM table_name WHERE column_name = value;

Sort data using ORDER BY.

SELECT * FROM table_name ORDER BY column_name ASC/DESC;

Limit the number of results.

SELECT * FROM table_name LIMIT num_rows;

Aggregation Functions

Count rows.

SELECT COUNT(*) FROM table_name;

Calculate the average.

SELECT AVG(column_name) FROM table_name;

Find the maximum value.

SELECT MAX(column_name) FROM table_name;

Find the minimum value.

SELECT MIN(column_name) FROM table_name;

Joining Tables

Inner join.

SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Left join.

SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

Right join.

SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

Backup and Restore

Export data (backup).

mysqldump -u username -p database_name > backup_file.sql

Import data (restore).

mysql -u username -p database_name < backup_file.sql

Data Types

Numeric Types

• INT - Integer (whole number)
• TINYINT - Tiny integer (small whole number)
• SMALLINT - Small integer
• MEDIUMINT - Medium integer
• BIGINT - Big integer
• FLOAT - Floating-point number (single precision)
• DOUBLE - Double-precision floating-point number
• DECIMAL - Exact numeric with a fixed number of decimal places

String Types

• CHAR(n) - Fixed-length character string
• VARCHAR(n) - Variable-length character string
• TEXT - Long text string
• BINARY(n) - Fixed-length binary string
• VARBINARY(n) - Variable-length binary string
• BLOB - Binary large object (for storing binary data)

Date and Time Types

• DATE - Date (YYYY-MM-DD)
• TIME - Time (HH:MM:SS)
• DATETIME - Date and time (YYYY-MM-DD HH:MM:SS)
• TIMESTAMP - Timestamp (YYYY-MM-DD HH:MM:SS)
• YEAR - Year (YYYY)

Boolean Type

• BOOL - Boolean (0 for false, 1 for true)

Enumerated Types

• ENUM - Enumerated type (a list of values)

Set Type

• SET - Set of values

Binary Large Objects (BLOBs)

• TINYBLOB - Tiny binary large object
• BLOB - Binary large object
• MEDIUMBLOB - Medium binary large object
• LONGBLOB - Long binary large object

Spatial Data Types (for geographic data)

• GEOMETRY - Geometric objects (points, lines, polygons, etc.)
• POINT - Point in a two-dimensional space
• LINESTRING - Curve in a two-dimensional space
• POLYGON - Polygonal shape in a two-dimensional space
• GEOMETRYCOLLECTION - Collection of geometric objects
• MULTIPOINT - Collection of points
• MULTILINESTRING - Collection of curves
• MULTIPOLYGON - Collection of polygonal shapes.

Function - Common Function

String Functions

• CONCAT(str1, str2, ...) - Concatenate strings.
• UPPER(str) - Convert a string to uppercase.
• LOWER(str) - Convert a string to lowercase.
• SUBSTRING(str, start, length) - Extract a substring from a string.
• LENGTH(str) - Get the length of a string.
• TRIM(str) - Remove leading and trailing spaces from a string.
• REPLACE(str, from_str, to_str) - Replace occurrences of a substring in a string.
• LEFT(str, length) - Get a specified number of characters from the left of a string.
• RIGHT(str, length) - Get a specified number of characters from the right of a string.

Numeric Functions

• ABS(x) - Get the absolute value of a number.
• ROUND(x, d) - Round a number to a specified number of decimal places.
• CEIL(x) - Round up to the nearest integer.
• FLOOR(x) - Round down to the nearest integer.
• RAND() - Generate a random number between 0 and 1.
• MAX(expr1, expr2, ...) - Find the maximum value in a set of expressions.
• MIN(expr1, expr2, ...) - Find the minimum value in a set of expressions.
• SUM(expr) - Calculate the sum of values in a column.
• AVG(expr) - Calculate the average of values in a column.

Date and Time Functions

• NOW() - Get the current date and time.
• CURDATE() - Get the current date.
• CURTIME() - Get the current time.
• DATE_FORMAT(date, format) - Format a date as a string.
• TIMESTAMPDIFF(unit, datetime1, datetime2) - Calculate the difference between two timestamps.
• DATE_ADD(date, INTERVAL expr unit) - Add an interval to a date or datetime.
• DATE_SUB(date, INTERVAL expr unit) - Subtract an interval from a date or datetime.

Conditional Functions

• IF(expr, true_value, false_value) - Return one value if a condition is true, and another if it's false.
• CASE - Perform conditional logic within a query.

Aggregate Functions (used with GROUP BY)

• COUNT(expr) - Count the number of rows.
• SUM(expr) - Calculate the sum of values.
• AVG(expr) - Calculate the average of values.
• MAX(expr) - Find the maximum value.
• MIN(expr) - Find the minimum value.

Math Functions

• SQRT(x) - Calculate the square root.
• POW(x, y) - Raise x to the power of y.
• LOG(x) - Natural logarithm of x.
• EXP(x) - Exponential function e^x.

End.

This is a quick reference guide for MySQL database. This only covers some of the essentials that we think you will appreciate when working with MySQL databases.

Download PDF

You can find the downloadable file in the link below: