DEV Community

Cover image for MySQL Cheatsheet
yaswanthteja
yaswanthteja

Posted on

MySQL Cheatsheet

SQL is a standard language for accessing and manipulating databases.

What is SQL?

  • SQL stands for Structured Query Language
  • SQL lets you access and manipulate databases
  • SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

  • Every table is broken up into smaller entities called fields. The fields in the Customers table consist of CustomerID, CustomerName, ContactName, Address, City, PostalCode and Country. A field is a column in a table that is designed to maintain specific information about every record in the table.

  • A record, also called a row, is each individual entry that exists in a table. For example, there are 91 records in the above Customers table. A record is a horizontal entity in a table.

  • A column is a vertical entity in a table that contains all information associated with a specific field in a table.

Database

It is defined as a collection of interrelated data stored together to serve multiple applications.

MySQL Elements

MySQL has certain elements that play an important role in querying a database.

Literals

Literals refer to a fixed data value

17 #It is a numeric literal
"abc" #It is a text literal
12.5 #It is a real literal

Enter fullscreen mode Exit fullscreen mode

Data Types

Data types are means to identify the type of data.


#Numeric

INT -- Integer data type
TINYINT
SMALLINT
MEDIUMINT
BIGINT

FLOAT(M,D) -- Floating point data type
DOUBLE(M,D) -- Double data type also stores decimal values
DECIMAL(M,D) -- Decimal data type

Enter fullscreen mode Exit fullscreen mode

Data and Time

#Data and Time 

DATE -- Date data type (YYYY-MM-DD)
DATETIME -- It's a date and time combination (YYYY-MM-DD HH:MM:SS)
TIME -- It stores time (HH:MM:SS)
Enter fullscreen mode Exit fullscreen mode

String/Text

#String/Text 

CHAR(M) -- Character data type
VARCHAR(M) -- Variable character data type
BLOB or TEXT
Enter fullscreen mode Exit fullscreen mode

NULL Values

If a column has no value, then it is said to be NULL

Comments

A comment is a text that is not executed.

/* This is a multi-line
comment in MySQL */

# It is a single-line commend

-- It is also a single-line comment
Enter fullscreen mode Exit fullscreen mode

MySQL Simple Calculations

You can perform simple calculations in MySQL, just by using the Select command, there's no need to select any particular database to perform these commands.

Addition

It will add two numbers

Select 5+8;
Enter fullscreen mode Exit fullscreen mode

Subtraction

It will subtract the second number from first

Select 15-5;
Enter fullscreen mode Exit fullscreen mode

Multiplication

It will give the product of supplied numbers

Select 5*5;
Enter fullscreen mode Exit fullscreen mode

Division

It will divide the number.

Select 24/4;

Enter fullscreen mode Exit fullscreen mode
  • SQL is not a case-sensitive language

Accessing Database

These commands allow one to check all the databases and tables

Show command

It will show all the databases in the system

Show databases;
Enter fullscreen mode Exit fullscreen mode

It will show all the tables in a selected database.

show tables;
Enter fullscreen mode Exit fullscreen mode

Use command

It will start using the specified database i.e. now you can create tables in the selected database.

use database_name;
Enter fullscreen mode Exit fullscreen mode

Creating tables

These commands allow you to create the table in MySQL.

Create table command

This query is used to create a table in the selected database.

Create table <table-name>
(<column_name> <data_type>,
<column_name> <data_type>,
<column_name> <data_type>);
Enter fullscreen mode Exit fullscreen mode

Insert command

It will add data into the selected table

Insert into <table_name> [<column-list>]
Values (<value1>,<value2>...);
Enter fullscreen mode Exit fullscreen mode

Inserting NULL values

This query will add NULL value in the col3 of the selected table.

Inset into <table-name> (col1, col2,col3) 
Values (val1,val2,NULL);
Enter fullscreen mode Exit fullscreen mode

Inserting Dates

It will add the following data into the selected column of the table

Insert into <table_name> (<col_name>) 
Values ('2022-05-10');
Enter fullscreen mode Exit fullscreen mode

Select Command

A select query is used to fetch the data from the database

Selecting All Data

It will retrieve all the data of the selected table

Select * From <table_name>;
Enter fullscreen mode Exit fullscreen mode

Selecting Particular Rows

It will retrieve all the data of the row that will satisfy the condition.

Select * from <table_name>
Where <condition_to_satisfy>;
Enter fullscreen mode Exit fullscreen mode

Selecting Particular Columns

It will retrieve data of selected columns that will satisfy the condition

Select column1, column2 from <table_name>
Where <condition_to_satisfy>;
Enter fullscreen mode Exit fullscreen mode

DISTINCT Keyword

It will retrieve only distinct data i.e. duplicate data rows will get eliminated

Select DISTINCT <column_name> from <table_name>;
Enter fullscreen mode Exit fullscreen mode

ALL Keyword

It will retrieve all the data of the selected column

Select ALL <column_name> from <table_name>;
Enter fullscreen mode Exit fullscreen mode

Column Aliases

It is used to give a temporary name to a table or a column in a table for the purpose of a particular query

Select <column1>,<column2> AS <new_name>
From <table_name>;
Enter fullscreen mode Exit fullscreen mode

Condition Based on a Range

It will only retrieve data of those columns whose values will fall between value1 and value2 (both inclusive)

Select <co11>, <col2> 
From <table_name>
Where <value1> Between <value2>;
Enter fullscreen mode Exit fullscreen mode

Condition Based on a List

Select * from <table_name> 
Where <column_name> IN (<val1>,<val2>,<val3>);
Enter fullscreen mode Exit fullscreen mode
"Select * from <table_name> 
Where <column_name> NOT IN (<val1>,<val2>,<val3>);"
Enter fullscreen mode Exit fullscreen mode

Condition Based on Pattern Match

Select <col1>,<col2> 
From <table_name>
Where <column> LIKE 'Ha%';
Enter fullscreen mode Exit fullscreen mode
Select <col1>,<col2> 
From <table_name>
Where <column> LIKE 'Ha__y%';
Enter fullscreen mode Exit fullscreen mode

Searching NULL

It returns data that contains a NULL value in them

Select <column1>, <column2>
From <table_name> Where <Val> IS NULL;
Enter fullscreen mode Exit fullscreen mode

SQL Constraints

SQL constraints are the rules or checks enforced on the data columns of a table

NOT NULL

It will create a table with NOT NULL constraint to its first column

Create table <table_name>
( <col1> <data_type> NOT NULL,
<col2> <data_type>,
<col3> <data_type>);
Enter fullscreen mode Exit fullscreen mode

DEFAULT

DEFAULT constraint provides a default value to a column

Create table <table_name>
( <col1> <data_type> DEFAULT 50,
<col2> <data_type>,
<col3> <data_type>);
Enter fullscreen mode Exit fullscreen mode

UNIQUE

UNIQUE constraint ensures that all values in the column are different

Create table <table_name>
( <col1> <data_type> UNIQUE,
<col2> <data_type>,
<col3> <data_type>);
Enter fullscreen mode Exit fullscreen mode

CHECK

CHECK constraint ensures that all values in a column satisfy certain conditions

Create table <table_name>
( <col1> <data_type> CHECK (condition),
<col2> <data_type>,
<col3> <data_type>);
Enter fullscreen mode Exit fullscreen mode

Primary Key

Primary key is used to uniquely identify each row in a table

Create table <table_name>
( <col1> <data_type> Primary Key,
<col2> <data_type>,
<col3> <data_type>);
Enter fullscreen mode Exit fullscreen mode

Foreign Key

CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
Enter fullscreen mode Exit fullscreen mode

Viewing Table Structure

Desc or Describe command

It allows you to see the table structure

Desc <table_name>;
Enter fullscreen mode Exit fullscreen mode

Modifying Data

Update Command

It will update the values of selected columns

Update <table_name>
SET <col1> = <new_value>, <col2> = <new_value>
Where <condition>;
Enter fullscreen mode Exit fullscreen mode

Deleting Data or Delete Command

It will delete the entire row that will satisfy the condition

Delete From <table_name>
Where <condition>;
Enter fullscreen mode Exit fullscreen mode

Ordering Records

Order by clause is used to sort the data in ascending or descending order of specified column

order by clause

It will return records in the ascending order of the specified column name's data

Select * from <table_name> order by <column_name>;
Enter fullscreen mode Exit fullscreen mode

It will return records in the descending order of the specified column name's data

Select * from <table_name> order by <column_name> DESC;
Enter fullscreen mode Exit fullscreen mode

Ordering data on multiple columns

It will return records in the ascending order of column1 and descending order of column2

Select * From <table_name> order by <column1> ASC, <column2> DESC;
Enter fullscreen mode Exit fullscreen mode

Grouping Result

It is used to arrange identical data into groups so that aggregate functions can work on them

Group by clause

It allows you to group two or more columns and then you can perform aggregate function on them

Select <column>, Count(*) from <table_name> group by <column>;
Enter fullscreen mode Exit fullscreen mode

Having clause

Having clause is used to put conditions on groups

Select avg(<column>), sum(<column>) from <table_name> group by <column_name> having <condition_to_satisfy>;
Enter fullscreen mode Exit fullscreen mode

Altering Table

These commands allow you to change the structure of the table

To Add New Column

It will add a new column in your table

Alter Table <table_name>
Add <new_column>;
Enter fullscreen mode Exit fullscreen mode

To Modify Old Column

It will update the data type or size of old column

Alter Table <table_name>
Modify <old_column_name> [<new_data_type><size>];
Enter fullscreen mode Exit fullscreen mode

To Change Name of Column

It will change the name of the old column in the table

Alter Table Change <old_column_name> <new_column_name><data_type>;
Enter fullscreen mode Exit fullscreen mode

Dropping Table or DROP command

It will delete the complete table from the database

Drop table <table_name>;
Enter fullscreen mode Exit fullscreen mode

MySQL Functions:

There are many functions in MySQL that perform some task or operation and return a single value

Text/String Functions

Text function work on strings

Char Function

It returns the character for each integer passed

Select Char(72,97,114,114,121);
Enter fullscreen mode Exit fullscreen mode

Concat Function

It concatenates two strings

Select Concat("My","Database");
Enter fullscreen mode Exit fullscreen mode

Lower/Lcase

It converts a string into lowercase

Select Lower("My Database");
Enter fullscreen mode Exit fullscreen mode

Upper/Ucase

It converts a string into uppercase

Select Upper("mydatabase");
Enter fullscreen mode Exit fullscreen mode

Substr

It extracts a substring from a given string

Select Substr(string,m,n);
Enter fullscreen mode Exit fullscreen mode

Trim

It removes leading and trailing spaces from a given string

Select Trim(leading ' ' FROM ' My Data');
Enter fullscreen mode Exit fullscreen mode

Instr

It searches for given second string into the given first string

Select Instr(String1,String2);
Enter fullscreen mode Exit fullscreen mode

Length

It returns the length of given string in bytes

Select Length(String)
Enter fullscreen mode Exit fullscreen mode

Numeric Functions

Numeric function works on numerical data and returns a single output

MOD

It returns modulus of two numbers

Select MOD(11,4);
Enter fullscreen mode Exit fullscreen mode

Power

It returns the number m raised to the nth power

Select Power(m,n);
Enter fullscreen mode Exit fullscreen mode

Round

It returns a number rounded off number

Select Round(15.193,1);
Enter fullscreen mode Exit fullscreen mode

Sqrt

It returns the square root of a given number

Select Sqrt(69);
Enter fullscreen mode Exit fullscreen mode

Truncate

It returns a number with some digits truncated

Select Truncate(15.75,1);
Enter fullscreen mode Exit fullscreen mode

Date/Time Functions

These are used to fetch the current date and time and allow you to perform several operations on them

Curdate Function

It returns the current date

Select Curdate();
Enter fullscreen mode Exit fullscreen mode

Date Function

It extracts the date part of the expression

Select Date('2021-12-10 12:00:00');
Enter fullscreen mode Exit fullscreen mode

Month Function

It returns the month from the date passed

Select Month(date);
Enter fullscreen mode Exit fullscreen mode

Day Function

It returns the day part of a date

Select Day(date);
Enter fullscreen mode Exit fullscreen mode

Year Function

It returns the year part of a date

Select Year(date);
Enter fullscreen mode Exit fullscreen mode

Now Function

It returns the current date and time

Select now();
Enter fullscreen mode Exit fullscreen mode

Sysdate Function

It returns the time at which function executes

Select sysdate();
Enter fullscreen mode Exit fullscreen mode

Aggregate Functions

Aggregate functions or multiple row functions work on multiple data and returns a single result

AVG Function

It calculates the average of given data

Select AVG(<column_name>) "Alias Name" from <table_name>;
Enter fullscreen mode Exit fullscreen mode

COUNT Function

It counts the number of rows in a given column

Select Count(<column_name>) "Alias Name" from <table_name>;
Enter fullscreen mode Exit fullscreen mode

MAX Function

It returns the maximum value from a given column

Select Max(<column_name>) "Alias Name" from <table_name>;
Enter fullscreen mode Exit fullscreen mode

MIN Function

It returns the minimum value from a given column

Select Min(<column_name>) "Alias Name" from <table_name>;
Enter fullscreen mode Exit fullscreen mode

SUM Function

It returns the sum of values in given column

Select Sum(<column_name>) "Alias Name" from <table_name>;
Enter fullscreen mode Exit fullscreen mode

MySQL Joins

Join clause is used to combine or merge rows from two or more tables based on a related attribute

INNER JOIN

It returns all rows from multiple tables where the join condition is satisfied. It is the most common type of join.

SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

LEFT OUTER JOIN

It returns all rows from the left-hand table specified in the ON condition and only those rows from the other table where the join condition is fulfilled.

SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

RIGHT OUTER JOIN

It returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the join condition is satisfied

SELECT columns FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

FULL JOIN

It combines the results of both left and right outer joins

SELECT column_name FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;
Enter fullscreen mode Exit fullscreen mode

SELF JOIN

In this join, table is joined with itself

SELECT column_name FROM table1 T1, table1 T2 WHERE condition;
Enter fullscreen mode Exit fullscreen mode

Note:

In Windows
If You Want To Download This Cheatsheet Just Press contol button & p button(crtl+p) and in the destinaion select save as pdf
Image description

Discussion (0)