MySQL Commands Cheat Sheet

Introduction

MySQL is the most popular open-source relational database management system. It is used to store data into predefined tables and structure it through assigned relations (hence the name relational database).

You can add, change, and extract data using Structured Query Language (SQL). The programming language SQL is the primary tool used to communicate with your MySQL database.

Therefore, to create, modify, and work with relational databases, you need to run the appropriate SQL commands.

In this tutorial, you will find the most important MySQL commands as well as a downloadable cheat sheet.

MySQL Commands

Below you will find a list of commonly used MySQL commands and their definitions.

To see a full list of commands, please refer to the MySQL Cheat Sheet included at the bottom of the article.

Users and Privileges

Display the current user name and hostname:

USER()

Create a MySQL user:

CREATE USER 'user'@'host';

Grant a specified type of privilege to a user on an object:

GRANT privileges_name ON object TO user;

Set a password for the current user:

SET PASSWORD='password'

Note: Deploy a workload-optimized system for your MySQL database. Check out phoenixNAP’s dedicated servers offering!

Working With Databases in MySQL

Create a new database:

CREATE DATABASE database_name;

Access a database:

USE database_name;

Delete a database (and drop all tables):

DROP DATABASE database_name;

List all databases on the MySQL server:

SHOW DATABASES;

List all MySQL users:

SELECT user FROM mysql.user;

Note: To rename a database in MySQL without cPanel, create a new database and import the data. The MySQL command to rename a database was removed in MySQL 5.1.23 for security reasons.

Working With Tables in MySQL

Create a new table in a database with the specified column name and datatype:

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

Display the table structure:

DESCRIBE table_name

Drop a table:

DROP TABLE table_name;

List all tables in the database:

SHOW TABLES;

To modify columns in a table, use the ALTER TABLE command.
For example, to add a column to a table, use the command:

ALTER TABLE table_name
ADD column_name datatype;

Select and retrieve values from all columns in a table:

SELECT * FROM table_name;

Note: If you are interesting in checking the size of the table in MySQL, read our article how to check MySQL database and table size.

Working With Indexes in MySQL

Create an index for a table:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Delete an index from a table:

ALTER TABLE table_name
DROP INDEX index_name;

Note: Never lose MySQL data again. Learn how to backup and restore a MySQL database.

Working With Views in MySQL

Create a view (virtual table) with the specified content:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Delete a view:

DROP VIEW view_name;

MySQL Data Types

When creating columns in a table, you need to specify their name and data type. Use MySQL data types to specify what kind of value the column will store.

Common data types:

INT – Integer values from -2147483648 to 2147483647.
DECIMAL – Exact decimal values specified in the format (M, D); the maximum number of digits for M is 65, while for D it is 35.
CHAR – Fixed-length character strings that specify the maximum number of characters it stores (up to 254 characters).
VARCHAR – Variable-length strings that specify the maximum number of characters it stores (up to 65535).
DATE – Stores data values in the format YYYY-MM-DD.
DATETIME – Contains both date and time, but has a range from 1970-01-01 00:00:01′ UTC to ‘2038-01-19 03:14:07’ UTC.
TEXT – Stores character strings, typically those with in a longer form (up to 65535 characters).

Note: Instead of setting up a separate MySQL server, you can also deploy MySQL in a Docker container.

MySQL Functions

MySQL comes with many built-in functions. These functions allow you to manipulate data.

Some of the most commonly used MySQL functions include:

Date and time functions
Mathematical functions
String functions
Aggregate functions
Comparison functions
Flow control functions

Date and Time Functions

DATEDIFF – Calculate the number of days between two dates.
DAY – Return the day of the month of the specified date.
DATE_ADD – Add a time/date interval to a date value.
DATE_SUB – Subtract a time/date interval from a data value.
DATE_FORMAT – Format a date based on the specified format.

Mathematical Functions

ABS – return the absolute value.
ACOS – Return the arc cosine.
ROUND – Return the argument.
SIGN – Return the sign of the argument.
TRUNCATE – Truncate a number to the specified number of decimal places.

String Functions

ASCII – Return ASCII value of left-most character.
BIN – Return a binary representation of a number.
LOG – Returns the natural logarithm of a specified number.
MATCH – Do a full-text search.
TRIM – Return a string without any prefixes or suffixes.

Aggregate Functions

AVG – Return the average of values.
BIT_AND – Return bitwise AND.
COUNT – Return the number of rows.
MAX – Return the highest value.
SUM – Return the summation of values.

Comparison Functions and Operations

> – Value is greater than.
>= – Value is greater than or equal to.
BETWEEN...AND – Value is within the specified range.
IS_NOT_NULL – Test for NOT NULL values.
IS_NULL – Test for NULL values.

Flow Control Functions

CASE – A case operator that allows you to add if-else logic to a query.
IF – Return a value based on a specified condition.
IFNULL – Return the first argument if it is NOT NULL (or the second if it is).
NULLIF – If the first argument is equal to the second argument, the result returns NULL (if not, it returns the first argument).