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'

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;

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;

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;

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).

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).

MySQL Commands Cheat Sheet

This article includes a one-page MySQL commands reference sheet. You can save the cheat sheet in PDF format by clicking the Download MySQL Cheat Sheet button below.

Conclusion

It is essential to know the most common MySQL commands when working with databases. As there is an extensive number of commands, don’t bother learning them all by heart.

Download the cheat sheet and keep it close at hand to find the command you need.