MySQL Commands Cheat-Sheet

MySQL Commands Cheat Sheet

This cheat sheet contains all the most commonly used MySQL commands that a user may need while working with the MySQL database. All the MySQL commands are briefly defined with the syntax. This cheat sheet will help the user to work more efficiently and faster with MySQL.

MySQL Command-Line Client Commands

The command-line commands in Mysql are used to establish the connection between the user and the database server. You need to have a specific username and password to make the connection with the database. To connect with the server we use the syntax given below:

To make the connection with a specific database, use the syntax given below:

To make a new password, use the syntax given below:

To exit from the MySQL console, we use the command given below:

Working with Database

The database is defined as a collection of a large number of information stored logically so that the user can retrieve this data easily anytime they want. MySQL stores the data in tabular form. As we know that MySQL database is a relational database management system, it stores the data in an organized tabular form rather than storing them in a large documented format. It stores the data in a tabular form in separate tables and there are various rows and columns inside the tables which makes it possible to store the data in a more organized manner. Using the MySQL commands in a database, users can interact with the data stored in the database. Let us see the various commands that we need to interact with the database.

• CREATE DATABASE: This MySQL command is used to create the database in your system. The syntax for this command is as follows:

• USE: This command is sued to move and switch to the database on which you are working. The syntax for this command is as follows:

• DROP DATABASE: This MYSQL command is used to delete a database permanently. This command also deletes all the files related to that specific database. The syntax for this command is as follows:

• SHOW DATABASE: This MySQL command is used to print and see the name of all the databases available in your system. The syntax for this command is as follows:

Working with tables

A table in MySQL is defined as the structural storage in which we can store the data. There are rows and columns in a table which is used to store separate data. Each of the rows and columns in a MySQL table has an individual index which is used to retrieve the data efficiently and fast. Let us see the various MySQL commands that we need to interact with the tables in MySQL.

• CREATE TABLE: This command is used to create a new table in the existing database. This command is also used to check if the table name exists or not. The syntax for this command is as follows:

• INSERT INTO: This command is a data manipulation command that is used to add some new records to the existing table. The user must have to keep in mind the number of data that needs to add to the table must match the number of columns of the table. The syntax to use this command is as follows:

• DROP: This MYSQL command is used to delete a table permanently. This command also deletes all the files related to that specific table. The syntax for this command is as follows:

• SHOW TABLES: This MySQL command is used to print and see the name of all the tables available in your system. The syntax for this command is as follows:

• ALTER TABLE and ADD: This command is used to make some changes in the table. The ALTER command tells the system that the user wants to do some changes in the table of the database. After that using the ADD command tells the system that the user wants to add some column to the table. To use these commands we use the following syntax:

• ALTER TABLE and DROP: This command is used to make some changes in the table. The ALTER command tells the system that the user wants to do some changes in the table of the database. After that using the DROP command tells the system that the user wants to remove some columns from the table. To use these commands we use the following syntax:

• ADD PRIMARY KEY: This command is used to add a primary key to the table. The syntax of this command is as follows:

• DROP PRIMARY KEY: This command is used to remove a primary key from the table. The syntax of this command is as follows:

• ADD INDEX: This command is used to add an index to some particular column of the table. The syntax of this command is as follows:

• DESCRIBE: This command is used to show all the structures of a particular table. The syntax of this command is as follows:

Working with Indexes

Indexes in MySQL are simply defined as the position of s specific data. As we know, MySQL is a relational database management system and it stores all the data using rows and columns in a table. The index represents the position of data in terms of rows and columns. Let us see the various MySQL commands related to Indexes.

• CREATE INDEX: This command is used to index the existing table of the database. The syntax of this command is as follows:

• DROP INDEX: This command is used to remove an index permanently from the table. The syntax of this command is as follows:

Working with View

View in MySQL has defined das as a virtual table that is created by joining two or more two tables virtually. Let us see the various MySQL commands related to Views.

• CREATE VIEW: This command is used to create a virtual table by combining two or more two tables in the database. The syntax of this command is as follows:

• DROP VIEW: This command is used to permanently remove the view from the database. The syntax of this command is as follows:

• RENAME TABLE: This MySQL command is used to change the name of the view that we have created in the database. The syntax of this command is as follows:

• SHOW FULL TABLES: This command is used to display all the available views in your database. The syntax of this command is as follows:

Working with Stored Procedure

A stored procedure in MySQL is defined as a group of statements that can accept an input and using this input, it performs a specific task. The stored procedure saves the time of users and prevents the user to write the same code again and again. Let us see the various Stored procedure MySQL commands below.

• CREATE PROCEDURE: This command is used to make a store procedure in the existing database. The syntax of this command is as follows:

• DROP PROCEDURE: This command is used to permanently remove a stored procedure from the database. The syntax of this command is as follows:

• SHOW PROCEDURE: This MySQL command is used to display all the available stored procedures that we have created in our database. The syntax of this command is as follows:

Working with Triggers

A trigger in MySQL is defined as a set of code that automatically gets executed when a certain event occurs in the database. These triggers may get executed concerning a specific event, or view, that gets created in the database. Let us see the various commands related to Triggers.

• CREATE TRIGGERS: This command is used to create a Trigger in your database. The syntax of this command is as follows:

• DROP TRIGGERS: This command is used to permanently remove the trigger that we have created in our database. The syntax of this command is as follows:

• SHOW TRIGGERS: This command is used to display all the triggers that we have created in our database. The syntax of this command is as follows:

Working with Stored Functions

A stored function in MySQL is defined as a set of queries that are used to perform some specific task in the database and provide a result as the output of the function. While defining a function, it is a must that the user has the privilege to the database CREATE ROUTINE.

• CREATE FUNCTION: This command is used to create a function in our database. The syntax of this command is as follows:

• DROP FUNCTION: This command is used to permanently remove a created function from the database. The syntax of this command is as follows:

• SHOW FUNCTION STATUS: This command is used to display the list of all the functions that we have created in our database. The syntax of this command is as follows:

Working with JOINS

Joins in MYSQL is defined as a clause that is used to get some result from two tables different tables if there is any same column present between them. All the types of JOINS are described below with syntax.

Inner Join Inner joins are used to display the result from all the rows of all the tables, where ever the provided condition is satisfied. The syntax of INNER JOIN is as follows:

Left join Left joins are used to display all the rows from the left table and all the rows from the right table where the ON condition satisfies. The syntax of LEFT JOIN is as follows:

Right Join Right joins are used to display all the rows from the right table and all the rows from the left table where the ON condition satisfies. The syntax of RIGHT JOIN is as follows:

Full Outer Join Full outer join in MySQL is used to return all the rows from both tables if there is a match in the provided condition of both tables. The syntax of FULL Outer Join is as follows:

Popular Queries Based on Tables

Now, let us see the various popular operations that we need to do regularly on the tables of a database. We will perform these operations with the SELECT command.

To display all the columns of a table, we use the following syntax:

To display some specific columns from the table, we use the following syntax:

To display some filtered results from the table according to the user, we use the following syntax:

There may be some duplicate rows in your table. So, to display unique rows from the table, we use the following syntax:

To display the number of rows present in a table, we use the following syntax:

To display the result in sorted format (ascending or descending order), we use the following syntax:

To change the name of the table of the database, we use the following syntax:

To delete all the data and records from a specific table, we use the syntax given below:

Searching Data from the Table

The LIKE clause is also used with MySQL commands to search some data from the table. To do this, we use the command given below:

There is a separate RLIKE operator that is used to text in the database using this regular expression. To do this, we use the command given below:

Control Option to the Administrator

Two commands are using which we can manipulate the access permission of a database to a specific user. This is known as Control Options MySQL commands. These commands are used by the administrator of the database. Let us see these commands below:

• GRANT: This MySQL command is used by the administrator of the database to provide permission to other users so that they can get access to that specific database. The syntax of this command is as follows:

• REVOKE: This MySQL command is used by the administrator of the database to restrict the permission from other users so that they can not get access to that specific database. The syntax of this command is as follows: