<h2>Database</h2>
<p>It is defined as a collection of interrelated data stored together to serve multiple applications.</p>
<h3>&nbsp;</h3>
<h2>MySQL Elements</h2>
<p>MySQL has certain elements that play an important role in querying a database.</p>
<h3>Literals</h3>
<p>Literals refer to a fixed data value</p>
<pre class="language-sql"><code>17 #It is a numeric literal
"Harry" #It is a text literal
12.5 #It is a real literal</code></pre>
<h3>Data Types</h3>
<p>Data types are means to identify the type of data.</p>
<pre class="language-sql"><code>#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</code></pre>
<pre class="language-sql"><code>#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)</code></pre>
<pre class="language-sql"><code>#String/Text 

CHAR(M) -- Character data type
VARCHAR(M) -- Variable character data type
BLOB or TEXT</code></pre>
<h3>NULL Values</h3>
<p>If a column has no value, then it is said to be NULL</p>
<h3>Comments</h3>
<p>A comment is a text that is not executed.</p>
<pre class="language-sql"><code>/* This is a multi-line
comment in MySQL */

# It is a single-line commend

-- It is also a single-line comment</code></pre>
<h2>MySQL Simple Calculations</h2>
<p>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.</p>
<h3>Addition</h3>
<p>It will add two numbers</p>
<pre class="language-sql"><code>Select 5+8;</code></pre>
<h3>Subtraction</h3>
<p>It will subtract the second number from first</p>
<pre class="language-sql"><code>Select 15-5;</code></pre>
<h3>Multiplication</h3>
<p>It will give the product of supplied numbers</p>
<pre class="language-sql"><code>Select 5*5;</code></pre>
<h3>Division</h3>
<p>It will divide the number</p>
<pre class="language-sql"><code>Select 24/4;</code></pre>
<pre class="language-sql"><code>-- SQL is not a case-sensitive language</code></pre>
<h2>Accessing Database</h2>
<p>These commands allow one to check all the databases and tables</p>
<h3>Show command</h3>
<p>It will show all the databases in the system</p>
<pre class="language-sql"><code>Show databases;</code></pre>
<p>It will show all the tables in a selected database</p>
<pre class="language-markup"><code>show tables;</code></pre>
<h3>Use command</h3>
<p>It will start using the specified database i.e. now you can create tables in the selected database</p>
<pre class="language-sql"><code>use database_name;</code></pre>
<h2>Creating tables</h2>
<p>These commands allow you to create the table in MySQL</p>
<h3>Create table command</h3>
<p>This query is used to create a table in the selected database</p>
<pre class="language-sql"><code>Create table &lt;table-name&gt;
(&lt;column_name&gt; &lt;data_type&gt;,
&lt;column_name&gt; &lt;data_type&gt;,
&lt;column_name&gt; &lt;data_type&gt;);</code></pre>
<h3>Insert command</h3>
<p>It will add data into the selected table</p>
<pre class="language-sql"><code>Insert into &lt;table_name&gt; [&lt;column-list&gt;]
Values (&lt;value1&gt;,&lt;value2&gt;...);</code></pre>
<h3>Inserting NULL values</h3>
<p>This query will add NULL value in the col3 of the selected table</p>
<pre class="language-sql"><code>Inset into &lt;table-name&gt; (col1, col2,col3) 
Values (val1,val2,NULL);</code></pre>
<h3>Inserting Dates</h3>
<p>It will add the following data into the selected column of the table</p>
<pre class="language-sql"><code>Insert into &lt;table_name&gt; (&lt;col_name&gt;) 
Values ('2021-12-10');</code></pre>
<h2>Select Command</h2>
<p>A select query is used to fetch the data from the database</p>
<h3>Selecting All Data</h3>
<p>It will retrieve all the data of the selected table</p>
<pre class="language-sql"><code>Select * From &lt;table_name&gt;;</code></pre>
<h3>Selecting Particular Rows</h3>
<p>It will retrieve all the data of the row that will satisfy the condition</p>
<pre class="language-sql"><code>Select * from &lt;table_name&gt;
Where &lt;condition_to_satisfy&gt;;</code></pre>
<h3>Selecting Particular Columns</h3>
<p>It will retrieve data of selected columns that will satisfy the condition</p>
<pre class="language-sql"><code>Select column1, column2 from &lt;table_name&gt;
Where &lt;condition_to_satisfy&gt;;</code></pre>
<h3>DISTINCT Keyword</h3>
<p>It will retrieve only distinct data i.e. duplicate data rows will get eliminated</p>
<pre class="language-sql"><code>Select DISTINCT &lt;column_name&gt; from &lt;table_name&gt;;</code></pre>
<h3>ALL Keyword</h3>
<p>It will retrieve all the data of the selected column</p>
<pre class="language-sql"><code>Select ALL &lt;column_name&gt; from &lt;table_name&gt;;</code></pre>
<h3>Column Aliases</h3>
<p>It is used to give a temporary name to a table or a column in a table for the purpose of a particular query</p>
<pre class="language-sql"><code>Select &lt;column1&gt;,&lt;column2&gt; AS &lt;new_name&gt;
From &lt;table_name&gt;;</code></pre>
<h3>Condition Based on a Range</h3>
<p>It will only retrieve data of those columns whose values will fall between value1 and value2 (both inclusive)</p>
<pre class="language-sql"><code>Select &lt;co11&gt;, &lt;col2&gt; 
From &lt;table_name&gt;
Where &lt;value1&gt; Between &lt;value2&gt;;</code></pre>
<h3>Condition Based on a List</h3>
<pre class="language-sql"><code>Select * from &lt;table_name&gt; 
Where &lt;column_name&gt; IN (&lt;val1&gt;,&lt;val2&gt;,&lt;val3&gt;);</code></pre>
<pre class="language-sql"><code>"Select * from &lt;table_name&gt; 
Where &lt;column_name&gt; NOT IN (&lt;val1&gt;,&lt;val2&gt;,&lt;val3&gt;);"</code></pre>
<h3>Condition Based on Pattern Match</h3>
<pre class="language-sql"><code>Select &lt;col1&gt;,&lt;col2&gt; 
From &lt;table_name&gt;
Where &lt;column&gt; LIKE 'Ha%';</code></pre>
<pre class="language-sql"><code>Select &lt;col1&gt;,&lt;col2&gt; 
From &lt;table_name&gt;
Where &lt;column&gt; LIKE 'Ha__y%';</code></pre>
<h3>Searching NULL</h3>
<p>It returns data that contains a NULL value in them</p>
<pre class="language-sql"><code>Select &lt;column1&gt;, &lt;column2&gt;
From &lt;table_name&gt; Where &lt;Val&gt; IS NULL;</code></pre>
<p>&lt;|AD|&gt;</p>
<h2>SQL Constraints</h2>
<p>SQL constraints are the rules or checks enforced on the data columns of a table</p>
<h3>NOT NULL</h3>
<p>It will create a table with NOT NULL constraint to its first column</p>
<pre class="language-sql"><code>Create table &lt;table_name&gt;
( &lt;col1&gt; &lt;data_type&gt; NOT NULL,
&lt;col2&gt; &lt;data_type&gt;,
&lt;col3&gt; &lt;data_type&gt;);</code></pre>
<h3>DEFAULT</h3>
<p>DEFAULT constraint provides a default value to a column</p>
<pre class="language-sql"><code>Create table &lt;table_name&gt;
( &lt;col1&gt; &lt;data_type&gt; DEFAULT 50,
&lt;col2&gt; &lt;data_type&gt;,
&lt;col3&gt; &lt;data_type&gt;);</code></pre>
<h3>UNIQUE</h3>
<p>UNIQUE constraint ensures that all values in the column are different</p>
<pre class="language-sql"><code>Create table &lt;table_name&gt;
( &lt;col1&gt; &lt;data_type&gt; UNIQUE,
&lt;col2&gt; &lt;data_type&gt;,
&lt;col3&gt; &lt;data_type&gt;);</code></pre>
<h3>CHECK</h3>
<p>CHECK constraint ensures that all values in a column satisfy certain conditions</p>
<pre class="language-sql"><code>Create table &lt;table_name&gt;
( &lt;col1&gt; &lt;data_type&gt; CHECK (condition),
&lt;col2&gt; &lt;data_type&gt;,
&lt;col3&gt; &lt;data_type&gt;);</code></pre>
<h3>Primary Key</h3>
<p>Primary key is used to uniquely identify each row in a table</p>
<pre class="language-sql"><code>Create table &lt;table_name&gt;
( &lt;col1&gt; &lt;data_type&gt; Primary Key,
&lt;col2&gt; &lt;data_type&gt;,
&lt;col3&gt; &lt;data_type&gt;);</code></pre>
<h3>Foreign Key</h3>
<pre class="language-sql"><code>CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);</code></pre>
<h2>Viewing Table Structure</h2>
<h3>Desc or Describe command</h3>
<p>It allows you to see the table structure</p>
<pre class="language-sql"><code>Desc &lt;table_name&gt;;</code></pre>
<h2>Modifying Data</h2>
<h3>Update Command</h3>
<p>It will update the values of selected columns</p>
<pre class="language-sql"><code>Update &lt;table_name&gt;
SET &lt;col1&gt; = &lt;new_value&gt;, &lt;col2&gt; = &lt;new_value&gt;
Where &lt;condition&gt;;</code></pre>
<h2>Deleting Data</h2>
<h3>Delete Command</h3>
<p>It will delete the entire row that will satisfy the condition</p>
<pre class="language-sql"><code>Delete From &lt;table_name&gt;
Where &lt;condition&gt;;</code></pre>
<h2>Ordering Records</h2>
<p>Order by clause is used to sort the data in ascending or descending order of specified column</p>
<h3>order by clause</h3>
<p>It will return records in the ascending order of the specified column name's data</p>
<pre class="language-sql"><code>Select * from &lt;table_name&gt; order by &lt;column_name&gt;;</code></pre>
<p>It will return records in the descending order of the specified column name's data</p>
<pre class="language-sql"><code>Select * from &lt;table_name&gt; order by &lt;column_name&gt; DESC;</code></pre>
<h3>Ordering data on multiple columns</h3>
<p>It will return records in the ascending order of column1 and descending order of column2</p>
<pre class="language-sql"><code>Select * From &lt;table_name&gt; order by &lt;column1&gt; ASC, &lt;column2&gt; DESC;</code></pre>
<h2>Grouping Result</h2>
<p>It is used to arrange identical data into groups so that aggregate functions can work on them</p>
<h3>Group by clause</h3>
<p>It allows you to group two or more columns and then you can perform aggregate function on them</p>
<pre class="language-sql"><code>Select &lt;column&gt;, Count(*) from &lt;table_name&gt; group by &lt;column&gt;;</code></pre>
<h3>Having clause</h3>
<p>Having clause is used to put conditions on groups</p>
<pre class="language-sql"><code>Select avg(&lt;column&gt;), sum(&lt;column&gt;) from &lt;table_name&gt; group by &lt;column_name&gt; having &lt;condition_to_satisfy&gt;;</code></pre>
<h2>Altering Table</h2>
<p>These commands allow you to change the structure of the table</p>
<h3>To Add New Column</h3>
<p>It will add a new column in your table</p>
<pre class="language-sql"><code>Alter Table &lt;table_name&gt;
Add &lt;new_column&gt;;</code></pre>
<h3>To Modify Old Column</h3>
<p>It will update the data type or size of old column</p>
<pre class="language-sql"><code>Alter Table &lt;table_name&gt;
Modify &lt;old_column_name&gt; [&lt;new_data_type&gt;&lt;size&gt;];</code></pre>
<h3>To Change Name of Column</h3>
<p>It will change the name of the old column in the table</p>
<pre class="language-sql"><code>Alter Table Change &lt;old_column_name&gt; &lt;new_column_name&gt;&lt;data_type&gt;;</code></pre>
<h2>Dropping Table</h2>
<h3>DROP command</h3>
<p>It will delete the complete table from the database</p>
<pre class="language-sql"><code>Drop table &lt;table_name&gt;;</code></pre>
<p>&lt;|AD|&gt;</p>
<h2>MySQL Functions:</h2>
<p>There are many functions in MySQL that perform some task or operation and return a single value</p>
<h2>Text/String Functions</h2>
<p>Text function work on strings</p>
<h3>Char Function</h3>
<p>It returns the character for each integer passed</p>
<pre class="language-sql"><code>Select Char(72,97,114,114,121);</code></pre>
<h3>Concat Function</h3>
<p>It concatenates two strings</p>
<pre class="language-sql"><code>Select Concat("Harry","Bhai");</code></pre>
<h3>Lower/Lcase</h3>
<p>It converts a string into lowercase</p>
<pre class="language-sql"><code>Select Lower("Harry Bhai");</code></pre>
<h3>Upper/Ucase</h3>
<p>It converts a string into uppercase</p>
<pre class="language-sql"><code>Select Upper("CodeWithHarry");</code></pre>
<h3>Substr</h3>
<p>It extracts a substring from a given string</p>
<pre class="language-sql"><code>Select Substr(string,m,n);</code></pre>
<h3>Trim</h3>
<p>It removes leading and trailing spaces from a given string</p>
<pre class="language-sql"><code>Select Trim(leading ' ' FROM ' Harry Bhai');</code></pre>
<h3>Instr</h3>
<p>It searches for given second string into the given first string</p>
<pre class="language-sql"><code>Select Instr(String1,String2);</code></pre>
<h3>Length</h3>
<p>It returns the length of given string in bytes</p>
<pre class="language-sql"><code>Select Length(String)</code></pre>
<h2>Numeric Functions</h2>
<p>Numeric function works on numerical data and returns a single output</p>
<h3>MOD</h3>
<p>It returns modulus of two numbers</p>
<pre class="language-sql"><code>Select MOD(11,4);</code></pre>
<h3>Power</h3>
<p>It returns the number m raised to the nth power</p>
<pre class="language-sql"><code>Select Power(m,n);</code></pre>
<h3>Round</h3>
<p>It returns a number rounded off number</p>
<pre class="language-sql"><code>Select Round(15.193,1);</code></pre>
<h3>Sqrt</h3>
<p>It returns the square root of a given number</p>
<pre class="language-sql"><code>Select Sqrt(144);</code></pre>
<h3>Truncate</h3>
<p>It returns a number with some digits truncated</p>
<pre class="language-sql"><code>Select Truncate(15.75,1);</code></pre>
<h2>Date/Time Functions</h2>
<p>These are used to fetch the current date and time and allow you to perform several operations on them</p>
<h3>Curdate Function</h3>
<p>It returns the current date</p>
<pre class="language-sql"><code>Select Curdate();</code></pre>
<h3>Date Function</h3>
<p>It extracts the date part of the expression</p>
<pre class="language-sql"><code>Select Date('2021-12-10 12:00:00');</code></pre>
<h3>Month Function</h3>
<p>It returns the month from the date passed</p>
<pre class="language-sql"><code>Select Month(date);</code></pre>
<h3>Day Function</h3>
<p>It returns the day part of a date</p>
<pre class="language-sql"><code>Select Day(date);</code></pre>
<h3>Year Function</h3>
<p>It returns the year part of a date</p>
<pre class="language-sql"><code>Select Year(date);</code></pre>
<h3>Now Function</h3>
<p>It returns the current date and time</p>
<pre class="language-sql"><code>Select now();</code></pre>
<h3>Sysdate Function</h3>
<p>It returns the time at which function executes</p>
<pre class="language-sql"><code>Select sysdate();</code></pre>
<h2>Aggregate Functions</h2>
<p>Aggregate functions or multiple row functions work on multiple data and returns a single result</p>
<h3>AVG Function</h3>
<p>It calculates the average of given data</p>
<pre class="language-sql"><code>Select AVG(&lt;column_name&gt;) "Alias Name" from &lt;table_name&gt;;</code></pre>
<h3>COUNT Function</h3>
<p>It counts the number of rows in a given column</p>
<pre class="language-sql"><code>Select Count(&lt;column_name&gt;) "Alias Name" from &lt;table_name&gt;;</code></pre>
<h3>MAX Function</h3>
<p>It returns the maximum value from a given column</p>
<pre class="language-sql"><code>Select Max(&lt;column_name&gt;) "Alias Name" from &lt;table_name&gt;;</code></pre>
<h3>MIN Function</h3>
<p>It returns the minimum value from a given column</p>
<pre class="language-sql"><code>Select Min(&lt;column_name&gt;) "Alias Name" from &lt;table_name&gt;;</code></pre>
<h3>SUM Function</h3>
<p>It returns the sum of values in given column</p>
<pre class="language-sql"><code>Select Sum(&lt;column_name&gt;) "Alias Name" from &lt;table_name&gt;;</code></pre>
<h2>MySQL Joins</h2>
<p>Join clause is used to combine or merge rows from two or more tables based on a related attribute</p>
<h3>INNER JOIN</h3>
<p>It returns all rows from multiple tables where the join condition is satisfied. It is the most common type of join.</p>
<pre class="language-sql"><code>SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;</code></pre>
<h3>LEFT OUTER JOIN</h3>
<p>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.</p>
<pre class="language-sql"><code>SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column;</code></pre>
<h3>RIGHT OUTER JOIN</h3>
<p>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</p>
<pre class="language-sql"><code>SELECT columns FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column;</code></pre>
<h3>FULL JOIN</h3>
<p>It combines the results of both left and right outer joins</p>
<pre class="language-sql"><code>SELECT column_name FROM table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name WHERE condition;</code></pre>
<h3>SELF JOIN</h3>
<p>In this join, table is joined with itself</p>
<pre class="language-sql"><code>SELECT column_name FROM table1 T1, table1 T2 WHERE condition;</code></pre>
<p>&nbsp;</p>
<div><a class="btn btn-danger" role="button" href="https://api.codewithharry.com/media/blogFiles/mysql-cheatsheet/MySQL_Cheatsheet_CodeWithHarry.pdf" target="_blank" rel="noopener"> Download this Cheatsheet</a></div>
<div>&nbsp;</div>

MySQL Cheatsheet

Add a new comment