MySQL Cheat Sheet

Updated May 31, 2023

Introduction to MySQL Cheat Sheet

MySQL Cheat Sheet is a collection of all frequently used queries on a single page. It can include queries for working with databases, tables, indexes, views, triggers, stored procedures, and stored functions. It might also include the basic queries to retrieve data from a table, search for data in a table, modify data in a table, get a count of rows, etc. A list of such queries together forms a cheat sheet of MySQL. Mostly the queries to CREATE, DROP, UPDATE, and INSERT are mentioned in the cheat sheet.

Queries of MySQL Cheat Sheet

Let’s have a look at some basic frequently used queries in MySQL.

1. Database-related queries

Queries that are most commonly used at the database level are below:

• To create a DB:

The syntax will be:

CREATE DATABASE IF NOT EXISTS db_name;

Sample Query:

CREATE DATABASE IF NOT EXISTS trial;

Output:

New DB:

To Use a DB:

The syntax will be:

USE db_name;

Sample Query:

USE trial;

Output:

• To Show all DB in the current server:

The syntax will be:

SHOW DATABASES;

Sample Query:

SHOW DATABASES;

Output:

All DB:

• To drop a DB:

The syntax will be:

DROP DATABASE IF EXISTS db_name;

Sample Query:

DROP DATABASE IF EXISTS trial;

Output:

2. Table-related queries

Queries that are most commonly used at a table level are below:

• To Show all tables in a DB:

The syntax will be:

SHOW TABLES;

Sample Query:

SHOW TABLES;

Double-click on the DB from which the list of tables will be pulled.

Output:

Table list in DB ‘world’:

• To create a table:

The syntax will be:

CREATE TABLE IF NOT EXISTS table_name (
Column_list
);

Sample Query:

CREATE TABLE IF NOT EXISTS SAMPLE (
Id INT,
Emp VARCHAR (50),
JOB VARCHAR (50);

Output:

• To ADD a new column to an existing table:

The syntax will be:

ALTER TABLE TABLE_NAME
ADD COLUMN COLUMN_NAME;

Sample Query:

ALTER TABLE SAMPLE
ADD COLUMN TITLE VARCHAR (50);

Output:

• To DROP a new column to an existing table:

The syntax will be:

ALTER TABLE TABLE_NAME
DROP COLUMN COLUMN_NAME;

Sample Query:

ALTER TABLE SAMPLE
DROP COLUMN TITLE;

Output:

• To show a column of a table:

The syntax will be:

DESCRIBE TABLE_NAME;

Sample Query:

DESCRIBE SAMPLE;

Output:

• To display details of a column in a table:

The syntax will be:

DESCRIBE TABLE_NAME COLUMN_NAME;

Sample Query:

DESCRIBE SAMPLE Id;

Output:

• To drop a table:

The syntax will be:

DROP TABLE IF EXISTS TABLE_NAME;

Sample Query:

DROP TABLE IF EXISTS SAMPLE;

Output:

3. Index-related queries

Queries that are most commonly used at the indexes level are below:

• To create an index:

The syntax will be:

CREATE INDEX index_name
ON table_name (column_name);

Sample Query:

CREATE INDEX TRIAL
ON COUNTRY (CODE,NAME);

Output:

• To drop an index:

The syntax will be:

DROP INDEX Index_name
ON table_name;

Sample Query:

DROP INDEX TRIAL
ON COUNTRY;

Output:

4. Triggers-related queries

Queries that are most commonly used on triggers are below:

• To create the trigger:

The syntax will be:

DELIMITER $$
CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
BEGIN
-- statements
END$$
DELIMITER ;
Sample Query:
DELIMITER $$
CREATE TRIGGER Updated_Sales_Data
AFTER UPDATE
ON InitialSales FOR EACH ROW
BEGIN
IFOLD.qty<>new.qtyTHEN
INSERT INTO Sales Updates(sales_Id,InitialQuantity, UpdatedQuantity)
VALUES(old.prodId, old.qty, new.qty);
END IF;
END$$
DELIMITER;

Output:

• To drop a trigger:

The syntax will be:

DROP TRIGGER trigger_name;

Sample Query:

DROP TRIGGER Updated_Sales_Data;

Output:

5. Stored procedure-related queries

Queries that are most commonly used upon stored procedures are below:

• To create a stored procedure:

Syntax will be

DELIMITER $$
CREATE PROCEDURE procedure_name(parameter_list)
BEGIN
body;
END $$
DELIMITER ;

• To drop a stored procedure:

The syntax will be:

DROP PROCEDURE IF EXISTS procedure_name;

6. Stored Functions related queries

Queries that are most commonly used upon stored functions are below:

• To create a stored function:

Syntax will be
DELIMITER $$
CREATE FUNCTION function_name(parameter_list)
RETURNS datatype
NOT DETERMINISTIC
BEGIN
-- statements
END $$
DELIMITER ;

• To drop a stored function:

The syntax will be:

DROP FUNCTION IF EXISTS function_name;

7. Queries to retrieve data from the table

Queries that are most commonly used to retrieve data from a table are below:

• To select all data:

SELECT * FROM table_name;

• Select specific columns only:

SELECT COLUMN1, COLUMN2 …
FROM TABLE_NAME;

• To select only distinct values from a column:

SELECT DISTINCT COLUMN_NAME
FROM TABLE_NAME;

• Retrieve data depending upon a “WHERE” condition:

SELECT COLUMN_NAMES
FROM table_name
WHERE CONDITION;

• To select data based on multiple tables: we have options to use JOIN, INNER JOIN, LEFT JOIN, RIGHT JOIN, etc.

INNER JOIN:

SELECT COLUMN_NAMES
FROM table_1_name
INNER JOIN table_2_name
ON coindition;

LEFT JOIN

SELECT COLUMN_NAMES
FROM table_1_name
LEFT JOIN table_2_name
ON coindition;

RIGHT JOIN:

SELECT COLUMN_NAMES
FROM table_1_name
RIGHT JOIN table_2_name
ON condition;

• To retrieve the count of rows in a table:

SELECT COUNT (*)
FROM TABLE_NAME;

• To retrieve data in a specific sorted order:

SELECT COLUMN_NAMES
FROM table_name
ORDER BY column_name ASC|DESC;

• ASC for ascending order
• DESC for descending order
• To retrieve data in a grouped class:

SELECT COLUMN_NAMES
FROM table_name
GROUP BY column_names ;

• To filter out data with the ‘having’ condition:

SELECT COLUMN_NAMES
FROM table_name
GROUP BY column_name
HAVING condition ;

8. Queries to modify data in the table

Queries that are most commonly used to modify data in a table are below:

• To insert data into a table:
• One row at a time:

INSERT INTO table_name(column_names)
VALUES(value);

• Multiple rows on a single go:

INSERT INTO table_name(column_names)
VALUES(value1),
(value2),
(value3);

• Update data of all rows in a table:

UPDATE TABLE_NAME
SET COLUMN_NAME = VALUE;

• Update based on a given condition:

UPDATE TABLE_NAME
SET COLUMN_NAME = VALUE;
WHERE CONDITION;

• To DELETE all rows from a table:

DELETE FROM TABLE_NAME;

• To delete rows based on a condition:

DELETE FROM TABLE_NAME
WHERE CONDITION;

Recommended Articles

We hope that this EDUCBA information on “MySQL Cheat Sheet” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. MySQL EXTRACT()
2. Examples of MySQL Limit
3. MySQL Index
4. MySQL Alias