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

MySQL DATA TYPES

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

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.

Example:

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

Examples:

• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• CREATE SCHEMA
• CREATE VIEW

DML (Data Manipulation Language)

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

Examples:

• INSERT
• UPDATE
• DELETE

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.

Examples:

• GRANT
• REVOKE
• ALTER PASSWORD

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.

Examples:

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

Examples:

• 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

Tables

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

Data

We will insert dummy data in both the tables.

• emp_departments

• employee_details

Creating / Deleting / Viewing Database

To create a new database.

CREATE DATABASE test-db;

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

SHOW DATABASES;

To delete the database.

DROP DATABASE test-db

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

Example:

CREATE SCHEMA test-db

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.

CREATE DATABASE IF NOT EXISTS employee;

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
 AUTO_INCREMENT NOT NULL, deptName VARCHAR(100));

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,
CONSTRAINT depIdFk FOREIGN KEY(dept_id)  REFERENCES
 emp_departments(deptId) ON DELETE CASCADE ON UPDATE CASCADE)

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)
 VALUES('SALES'),('HR'),('MARKETING'),('TECHNOLOGY');

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

Simple SELECT

Select all the records from the employee_details table.

SELECT * FROM employee.employee_details;

SELECT with WHERE

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

SELECT * FROM employee.employee_details where dept_id=1;

SELECT With ORDER BY

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 JOINS

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.

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.

MySQL UPDATE

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

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.

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.

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.

SHOW VARIABLES LIKE 'port';

//Output
3306

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

SHOW VARIABLES LIKE 'mysqlx_port';

//Output
33060

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 are used to generate aggregation or combined results for multiple rows in a table.

The available Aggregate functions are:

DateTime Functions

Used to manipulate columns having date-time values.

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.

Tips

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:

SELECT VERSION();

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.

Example:

EXPLAIN SELECT * FROM employee.employee_details WHERE empId = 2

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

Example:

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

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

Conclusion

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