Comprehensive MySQL Cheat Sheet For Quick Reference

Refer to this comprehensive MySQL Cheat Sheet with syntax, examples and tips for a quick reference:

MySQL is one of the most popular and widely used Relational Database Management Systems that is based on Structured Query Language i.e. SQL.

In this tutorial, we will see a concise summary of all the most widely used commands in MySQL with Syntaxes and examples. We will also have a look at some tips and tricks that can be used while connecting and using MySQL Server instances.

=> Visit Here To Read The Ultimate MySQL Training Series

MySQL Cheat Sheet

MySQL Cheat Sheet

MySQL Cheat Sheet is meant to have a concise introduction to all the broad MySQL topics along with useful tips.

MySQL Installation

MySQL server is available for installation on different platforms like Windows, OSX, Linux, etc. All the related details can be found in this tutorial.

If you are just getting started and don’t want to set it up on your machine, then you can simply use MySQL as a docker container and try to learn things about MySQL. You can refer to the MySQL Docker Image section in this tutorial.


We will briefly discuss the different categories of data types provided by MySQL.

CategoriesDescriptionMySQL Supported Data types
Numeric Data TypesAll data types dealing with fixed point or floating point numbers.Integer Data types - BIT, TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
Fixed Point types - DECIMAL
Floating Point types - FLOAT and DOUBLE
DatetimeThese data types are used for having columns containing dates, timestamp, datetime values.DATETIME
StringUsed for storing textual data typed - example names, address etc.CHAR, VARCHAR
BinaryUsed to store textual data in Binary format.BINARY, VARBINARY
Blob & TextSupport String data types but columns that have content more than the supported values for CHAR data typed - Ex storing entire book text.BLOB - TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
BooleanUsed to store Boolean type values -like True and False.BOOLEAN
JsonUsed for storing column values as JSON strings.JSON
EnumUsed for storing columns having fixed set of values - ex Categories in an ecommerce website.ENUM

For a detailed introduction of different data types, please refer to this tutorial.

MySQL Comments

Single-Line Comments

MySQL single-line comments can be created using a double hyphen ‘–’.

Anything till the end of the line is considered to be a part of the comment.


-- This is comment 

Multi-Line Comments

Multi-line comments start with /* and end with */ –

Anything between these 2 start and end characters, would be treated as a part of the comment.

/* This is 
Multi line
Comment */

Connecting to MySQL Through Command Line

MySQL can be connected using GUI tools like Sequel Pro or MySQL workbench which are freely available tools and other paid ones like table plus etc.

While GUI tools are intuitive, during a lot of times, connecting to the command line makes more sense due to restrictions for tools installation, etc.

To connect to a MySQL command prompt through a command line on a Windows or OSX or Linux machine, you can use the below command.

mysql -u root -p

Once this is entered, you would be prompted to enter a password. If the password was correctly entered, then you should land on the MySQL server being connected and the ready-to-execute commands.

Types of SQL Commands

Let’s first understand the different types of commands available for any SQL-based database (Example MySQL or MsSQL or PostGreSQL).

DDL (Data Definition Language)

This category of commands is used to create or update a database schema or table.



DML (Data Manipulation Language)

This category of commands is used to manipulate data within the MySQL tables.



DQL (Data Query Language)

These types of commands are used to query data from the tables in the MySQL database.

SELECT is the only command and it is the most widely used one too.

DCL (Data Control Language)

This category of commands is used to control access within the database. For example, granting different privileges to the users.



Data Administration Commands

These types of commands are used to show the structure of the database objects, show table status, show different attributes of the given table, etc.


  • SHOW DATABASES: Show all databases within the server instance.
  • SHOW TABLES: Show tables within a database.
  • SHOW COLUMNS FROM {tableName}: Show columns for a given tableName.

Transaction Control Commands

These commands are used to control and manage database transactions.


  • COMMIT: Tell the database to apply the changes
  • ROLLBACK: Let the database know to rollback or revert the changes applied since the last commit.

Commonly Used Commands With Examples

In this section, we will see examples of the most commonly used MySQL commands. We will use some test schema and data defined in the next topic as shown below.

Test Schema Info

Database – employee


  • employee_details – with columns
    • empId – INT (primary key, not null, auto increment)
    • empName – VARCHAR(100),
    • city – VARCHAR(50),
    • dep_id – refer value from dept_id(emp_departments) (FOREIGN KEY)
  • emp_departments
    • dept_id – INT (primary key, not null, auto increment)
    • dept_name – VARCHAR(100)


We will insert dummy data in both the tables.

  • emp_departments
  • employee_details
1Shyam SundarAgra
2Rebecaa JohnsonLondon
3Rob EamesSan Francisco

Creating / Deleting / Viewing Database

To create a new database.


To display all the databases for the given MySQL server instance.


To delete the database.


Note: In the place of the word DATABASE, SCHEMA can also be used.



Please refer to our tutorials on CREATE DATABASE here.

Creating / Deleting Tables

We will be creating a table against the table info in the test data section as below:

  • employee_details – with columns.
    • empId – INT (primary key, not null, auto-increment),
    • empName – VARCHAR(100),
    • city – VARCHAR(50),
    • dept_id – refer value from dept_id(emp_departments) (FOREIGN KEY)
  • emp_departments
    • deptId – INT (primary key, not null, auto-increment),
    • dept_name – VARCHAR(100),

Let’s write the CREATE commands for both tables.

Note: In order to CREATE a table in a given database, the DATABASE should exist before creating the table.

Here, we will first CREATE the employee DATABASE.


Now we will create an emp_departments table – Notice the Use of keywords PRIMARY KEY and AUTO_INCREMENT

CREATE TABLE employee.emp_departments(deptId INT PRIMARY KEY

Now we will create the employee_details table. Notice the use of the FOREIGN KEY constraint which refers to the deptId column from the emp_departments table.

CREATE TABLE employee.employee_details(empId INT PRIMARY KEY
 AUTO_INCREMENT NOT NULL, empName VARCHAR(100), city VARCHAR(50), dept_id INT,

For more details around the MySQL CREATE TABLE command, chheck here.

PRIMARY KEY: A Primary key is nothing but a unique way to define a row in a database. It can just be one column Example, – employeeId would be unique for each and every employee or it can also be a combination of 2 or more columns that would uniquely identify a row.

FOREIGN KEY: FOREIGN KEYS are used to establish relations between tables. It’s used to connect 2 or more tables with the help of a common column.

For example, in the above tables the employee_details and emp_departments – the field dept_id is common between 2 and hence it can be used as a FOREIGN KEY.

To understand more about PRIMARY and FOREIGN keys in MySQL, please refer to our tutorial here.

Creating / Deleting Indexes

INDEXES are used to store the rows in a particular order which would help in faster retrieval. By default, PRIMARY KEYS & FOREIGN KEYS are already indexed. We can create an index on any column we desire.

For example, for table emp_details, let’s try to create an Index on the empName column.

CREATE INDEX name_ind ON employee.employee_details(empName);

Similar to tables and databases, INDEXES can also be dropped or deleted using the DROP INDEX command.

DROP INDEX name_ind ON employee.employee_details;

Modifying Tables: Add Column

Let’s now add a new column named empAge of type INT in the employee_details table.

ALTER TABLE employee.employee_details ADD COLUMN empAge INT;

Modifying Tables: Update Column

A lot of times it’s required to update existing columns: For example, changing the data types.

Let’s see an example where we are changing the datatype of the city field in the employee_details table from VARCHAR(50) to VARCHAR(100).

ALTER TABLE employee.employee_details MODIFY COLUMN city VARCHAR(100);

Inserting Data: MySQL INSERT

Let’s now see how you can INSERT data into an existing table. We will add some rows in emp_departments and then some employee data in the employee_details table.

INSERT INTO employee.emp_departments(deptName)
INSERT INTO employee.employee_details(empName, city, dept_id)
 VALUES('Shyam Sundar','Agra',1),('Rebecaa Johnson','London',3),
('Rob Eames','San Francisco',4),('Jose','Guatemala',1),('Bobby','Jaipur',2);

Querying Data: MySQL SELECT

Probably the most widely used command i.e SELECT is used to query the data from one (or more) tables in a Database. The SELECT command is supported by all the databases supporting the SQL standards.

Let’s see some examples of using the SELECT QUERY


Select all the records from the employee_details table.

SELECT * FROM employee.employee_details;



Let’s suppose, we just want employee details who are with dept_id = 1

SELECT * FROM employee.employee_details where dept_id=1;



ORDER BY is used when it’s desired to have the result in ascending or descending order.

Let’s run the same example to have names sorted in Ascending order.

SELECT * FROM employee.employee_details order by empName ASC;



MySQL provides JOINS to combine data from 2 or multiple tables based on a JOIN condition. There are different types of JOINS but the most commonly used one is INNER JOIN.

INNER JOINUsed to combine 2 (or more tables) and return matching data based on the join condition.

-Full Outer Join
-Left Outer Join
-Right Outer Join
OUTER JOINs return matching data based on conditions and non matching rows depending on the type of join used.
LEFT OUTER JOIN - would return matching rows and all rows from table on Left side of Join
RIGHT OUTER JOIN - would return matching rows and all rows from table on Right side of Join
FULL OUTER JOIN - return matching rows and unmatching rows from both left and right tables.
CROSS JOINThis type of join is cartesian product and would return all the combinations of each row in both the tables.
Ex if table A has m records and table B has n records - then cross Join of table A and table B would have mxn records.
SELF JOINIt's similar to CROSS JOIN - where the same table is joined to itself.
This is useful in situations for example where you have an employee table with both emp-id and manager-id columns - so to find manager details for an employee you can do a SELF JOIN with the same table.

As we have now inserted data into our test schema. Let’s try applying INNER JOIN on these 2 tables.

We will query the table and list down the employee names and department names in the result.

SELECT emp_details.empName, dep_details.deptName FROM employee.employee_details emp_details
INNER JOIN employee.emp_departments dep_details ON emp_details.dept_id = dep_details.deptId

The output would be as below:


For more details about MySQL JOINS, please refer to our tutorial here.


To UPDATE one or more rows depending on the match condition, MySQL UPDATE can be used.

Let’s use the existing employee_details table and update the employee name with Id = 1 to Shyam Sharma (from the current value of Shyam Sundar).

UPDATE employee.employee_details SET empName='Shyam Sharma' WHERE empId=1;

For more details about the MySQL UPDATE command, please refer to our detailed tutorial here.


MySQL GROUP BY command is used to GROUP or AGGREGATE rows having the same column values together.

Let’s see an example, where we want to find the count of the no. of employees in each department.

We can use GROUP BY for such queries.

SELECT dept_id, COUNT(*) AS total_employees FROM employee.employee_details GROUP BY dept_id;


MySQL Shell Commands

Just like how we use MySQL with the help of GUI clients like MySQL Workbench or Sequel Pro or many others, it’s always possible to connect to MySQL through a command line prompt or more commonly known as the shell.

This is available with the MySQL Standard installation.

To connect with a given user and password, you can use the command below.

./mysql -u {userName} -p

For example, to connect with a user named “root”, you can use.

./mysql -u root -p

This -p represents that you want to connect with a password – once you enter the above command – you would be prompted for a password.

The correct password will open a shell ready to accept SQL commands.

accept SQL commands

The commands can be entered similar to the way in which we execute the commands in GUI tools. Here the execution would happen, as soon as you press enter.

For example, let’s try to run a command to show databases.

On the shell, you could simply run.

show databases;

You would see a list of databases displaying in the terminal.

databases displaying in the terminal

Note: To view the list of all the available shell command options, please visit the official page here.

MySQL Port

MySQL uses the default port as 3306 which is used by mysql clients. For clients like MySQL shell X Protocol, the port defaults to 33060 (which is 3306 x 10).

To view the value of the port configuration, we can run a command as MySQL Query.



For MySQL X Protocol port, you can get the value of mysqlx_port.

SHOW VARIABLES LIKE 'mysqlx_port';


MySQL Functions

In addition to standard queries using SELECT, you can also use several inbuilt functions provided by MySQL.

Aggregate Functions

To illustrate AGGREGATE FUNCTIONS – let’s add a new column – employee salary of type INT and set the value equal to something hypothetical – for example, empId x 1000.

ALTER TABLE employee.employee_details ADD COLUMN empSalary INT;
UPDATE employee.employee_details SET empSalary = 1000 * empId;

Let’s do a SELECT to see the updated data in the employee_details table.

SELECT * FROM employee.employee_details;

Aggregate Functions

Aggregate functions are used to generate aggregation or combined results for multiple rows in a table.

The available Aggregate functions are:

AVG()Used to fund the average value for a given numeric type column
Example: Find average salary of all employees
SELECT AVG(empSalary) FROM employee.employee_details;
COUNT()Used to COUNT the no of rows against a given condition
Example: Select Count of employees having salary < 3000
SELECT COUNT(*) FROM employee.employee_details WHERE empSalary < 3000
SUM()Used to calculate the SUM of a numeric column against all matching rows.
Example: Lets find the SUM of employee SALARIES for employee IDs 1,2 & 3
SELECT SUM(empSalary) FROM employee.employee_details WHERE empId IN (1,2,3)
MAX()Used to find out the Maximum value of a numeric column against given matching conditions.
Example: Find Maximum salary from the employee_details
SELECT MAX(empSalary) FROM employee.employee_details;
MIN()Used to find out the Minimum value of a numeric column against given matching conditionsSELECT MIN(empSalary) FROM employee.employee_details;

DateTime Functions

Used to manipulate columns having date-time values.

FunctionDescriptionExample / Syntax
CURDATEGet the current date.
curdate(), CURRENT_DATE() and CURRENT_DATE can be used synonymously
SELECT curdate();
CURTIMEGets the current time in hh:mm:yy unless precision is specified.For precision upto microseconds we can use - curtime(6)
SELECT curtime();
SELECT curtime(6);
NOWGets the current timestamp - which is the current date time value.
Default format
Yyyy-mm-dd hh:mm:ss
Other variations - now(6) - get time upto microseconds
SELECT now();
ADDDATEAdds a specified duration to the given dateSELECT ADDDATE('2020-08-15', 31);
// output
It can also be called for a specific interval - like MONTH, WEEK
// output
2021-01-27 00:00:00
ADDTIMEAdds a time interval to the given date time valueSELECT ADDTIME('2021-01-21 12:10:10', '01:10:00');
SUBDATE & SUBTIMESimilar to ADDDATE and ADDTIME, SUBDATE and SUBTIME are used to subtract date and time intervals from the given input values.SELECT SUBDATE('2021-01-20', INTERVAL `1 WEEK)
SELECT SUBTIME('2021-01-21 12:10:10', '01:10:00');

To refer to a detailed introduction to MySQL DATETIME Functions, refer to our detailed tutorial here.

String Functions

Used to manipulate String values in the existing columns in the table. For example, Concatenating columns having String values, concatenate external characters to String, splitting strings, etc.

Let’s have a look at some of the commonly used String functions below.

FunctionDescriptionExample / Syntax
CONCATAdds 2 or more string values togetherSELECT CONCAT("Hello"," World!");
// Output
Hello World!
CONCAT_WSConcates 2 or more strings with a separatorSELECT CONCAT_WS("-","Hello","World");
LOWERConverts the given string value to lowercase.SELECT LOWER("Hello World!");
hello world!
REPLACEReplace all occurrences of a given String with the specified String.SELECT REPLACE("Hello", "H", "B");
REVERSEReturns the given String in a reverse orderSELECT REVERSE("Hello");
UPPERConverts the given String value to UPPER CASESELECT UPPER("Hello");
SUBSTRINGExtracts a substring from the given stringSELECT SUBSTRING("Hello",1,3);
//Output (3 characters starting first index)
TRIMTrims leading and trailing spaces from the given StringSELECT TRIM(" HELLO ");
//Output (leading and trailing spaces removed)


In this section, we will see some of the commonly used tips/shortcuts to enhance productivity and perform things faster.

Executing SQL Script Using Command Line

A lot of times we have SQL scripts in the form of files – having .sql extension. These files can either be copied over to the editor and executed through GUI applications like Workbench.

However, it’s simpler to execute these files through the command line.

You can use something like

mysql -u root -p employee < fileName.sql

Here ‘root’ is the username, ‘employee’ is the database name, and the name of the SQL file is – fileName.sql

Once executed you will be prompted for a password and then the SQL file would get executed for the specified database.

Getting Current MySQL Version

In order to get the current version of the MySQL Server instance, you can run a simple query below:


For more details about MySQL Version, please refer to our tutorial.

Using MySQL EXPLAIN to Get MySQL Server’s Query Plan

MySQL EXPLAIN is an administrative command that can be executed for any SELECT command to understand the way in which MySQL is fetching the data.

It’s useful when someone is doing performance tuning of the MySQL server.


EXPLAIN SELECT * FROM employee.employee_details WHERE empId = 2

Using MySQL EXPLAIN to get MySQL server’s Query plan

Getting a Random Record From a Table in MySQL

If you are looking to fetch a random row from a given MySQL table, then you can use the ORDER BY RAND() clause


SELECT * FROM employee.employee_details ORDER BY RAND() LIMIT 1

The above query would return 1 randomly selected row from the employee_detail table.


In this tutorial, we learned the different concepts of MySQL, right from Installation, to connecting to the server instance, command types, and small examples of the command usage.

We also learned about the different IN-BUILT MySQL functions for Aggregation, Functions to Manipulate Strings, Function to work with Date and Time values, etc.

=> Simple MySQL Training Guide For ALL