MySQL Command Cheat Sheet: Quick Reference

In this cheat sheet, we are going to outline some of the fundamentals of when working with a MySQL database. This will include common command, the data types, and functions.

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.