MySQL Commands/Cheat-Sheet

MySQL cheat sheet provides a single page that contains all of the most commonly used commands and statements. This one-page cheat sheet helps us to work with MySQL database more effectively and quickly.

MySQL is an open-source widely used RDBMS database that provides high performance and scalable web-based and embedded database applications for the customers. It has many useful commands and statements to work with web-based and embedded database applications. The command in MySQL is a powerful directive used to perform a specific task or service. They are very helpful for every developer to know and use these queries for interacting with the MySQL databases in real-time. MySQL commands are based on a structured query language (SQL) which can support various operating systems such as Linux, UNIX, macOS, and Windows.

We can see a list of all commands in MySQL using the below query:

Let us see the most commonly used commands and statements that we need to create and manage the database in MySQL.

MySQL command-line client commands

This command allows us to connect with MySQL Server with a username and passwords using below syntax.

If you want to connect with a particular database, use this syntax:

If you want to set a new password, use this syntax:

We can use the "exit" command to quit the MySQL command-line client.

We can clear the console window in Linux using the below command:

It is to be noted that there is no command available for Windows to clear the console window of MySQL.

Working with Database

A database stores the organized collection of records that can be accessed and manage by the user very easily. It holds the data into tables, rows, columns, and indexes that help us to find the relevant information quickly. We can create a database using the below syntax. It also checks the database name, whether it already exists or not.

If you want to change the current database with another database on which you are working, use the below syntax:

We can delete a particular database along with its associated files permanently using the below syntax:

To show all databases in the current server, use this syntax:

Working with tables

A table is a collection of related data stored in a row and column format within a database. We can create a new table using the below syntax. It also checks the table name, whether it already exists or not.

We can use the INSERT statement to add a record into the table, which is given below:

We can delete a particular table along permanently using the below syntax:

To show all tables in the current database, use this syntax:

We can add a new column to the table or can drop any column, use the following statements:

We can add a primary key into the table, or can remove it from the table, use the following statements:

If you want to add an index with a specific name on a column, use this syntax:

We can show the structure of a table using the below statement:

We can show the column information of a table using the below statement:

Working with Indexes

An index is a data structure that allows us to the faster retrieval of records on a database table. If we want to add an index in the table, we will use the statement as follows:

To drop an index, use this statement:

Working with View

The View in MySQL is used to create a virtual table by joining one or more tables. We can create or replace a VIEW by using SELECT statements as follows:

We can remove a view using the below statement:

We can rename a view using the below statement:

If you want to show all the views available in the database, use the below statement:

Working with Stored Procedure

A stored procedure is a group of SQL statements with a specific name that accepts some inputs and performs a certain task. It saves time to write the same code again and again in a database. We can create a stored procedure in MySQL using the below statements:

If you want to remove an existing stored procedure from the database, use the below statement:

If you want to show all procedures available in the database, use the below statement:

Working with Triggers

A trigger is a procedural code in a database that automatically executed whenever certain events on a particular table or view in the database occur. We can create a new trigger using the below statements:

If you want to remove an existing trigger, use the below statement:

If you want to show all triggers available in the database, use the below statement:

Working with Stored Functions

A stored function in MySQL is a set of SQL statements that perform some task/operation and return a single value. It is one of the types of stored programs in MySQL. When you will create a stored function, make sure that you have a CREATE ROUTINE database privilege. The syntax of creating a stored function in MySQL is given below:

If you want to remove an existing trigger, use the below statement:

If you want to show all triggers available in the database, use the below statement:

Popular queries based on tables

Here, we will see some of the essential queries executed on tables to find the relevant information.

If we want to show all the table data, use this syntax:

If we want to show the data of some selected columns from a table, use this syntax:

We can get filtered result from the table using the below statement:

Sometimes our tables contain duplicates rows. But we want to show only the unique(distinct) rows, in that case, the below statements will be executed:

Sometimes, the column name of a table is not appropriate for the output. In that case, we can use the below statement to change the column name in the result:

If we want to get the output from more than one table, we need to use the JOIN operation. The syntax for joining two tables is given below:

We can count the number of rows with this statement:

We can sort the result set using the following statement:

If we want to group rows into the result set, use the below syntax:

If we want to update the rows of the table, use the below syntax:

We can delete all records from the table using the below syntax:

Searching data from the table

We can search for data using the LIKE clause, as shown below:

We can also search for text using a regular expression with RLIKE operator, as shown below:

Control Option to the Administrator

Here, we will see the features that provide control options to the administrators and users on the database.

The grant statement enables system administrators to assign privileges and roles to the MySQL user accounts for accessing the database.

The revoke statement enables system administrators to revoke privileges and roles to the user accounts on the database in the past.