MySQL Cheat Sheet

Introduction to MySQL Cheat Sheet

MySQL Cheat Sheet is just a collection of all frequently used MySQL 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, modifying data in a table, getting a count of rows, and so on. 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;

Make sure to double click on the DB from which list of tables is to 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 show 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 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;

• To 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;

• To 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;

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

This is a guide to MySQL Cheat Sheet. Here we discuss the list of some basic frequently used queries together forms a cheat sheet of MySQL. You may also have a look at the following articles to learn more –

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