Shubham Athawane

Posted on Dec 24, 2021 • Updated on Oct 26, 2022

MySQL Cheat sheet

#mysql #javascript #beginners #database

Creating tables 📑

CREATE TABLE BookHistory (
    Auther VARCHAR(129),
    title VARCHAR(129),
    btype VARCHAR(129),
    year CHAR(4)
);

To drop table column

ALTER TABLE tableName DROP column-Name;
ex.
ALTER TABLE BookHistory DROP Auther;

How to delete Data from a MySQL table ?

// Delete Statement is used to delete data, 

DELETE FROM table_name WHERE column_name = VALUE
EX. 
DELETE FROM BookHistory WHERE title = 'JungleBook'

Inserting Value into Table.

INSERT INTO table_name (Column1, Column2, Column3 ) VALUES (value1, value3, value3);
EX. 
INSERT INTO BookHistory (Author, title, btype, year) VALUES ("James Camron", "Avatar", "Adventure", 2006);

Update column name.

ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name;
EX. 
ALTER TABLE BookHistory RENAME COLUMN btype TO Book_type;

Add new column in table

ALTER TABLE table_name ADD column_name DATATYPE;
EX. 
ALTER TABLE BookHistory ADD price INT(4);

The Select Command : DQL- DATA QUERY LANGUAGE Command.

Used to visualize the table content.

SELECT * FROM table_name;
EX.
SELECT * FROM BookHistory;

// It will show all data present inside the BookHistory table

We can use SELECT command to retrieve specific data from the table using WHERE clause . Like below

SELECT * FROM table_name WHERE condition;
EX.
SELECT * FROM BookHistory WHERE Book_type = "hacking";

MySQL numeric Data types

MySQL has numeric data types for Integer, Fixed-point, Floating-Point and bit etc.

*Numeric can be singed or unsinged

1. TINYINT 6. FLOAT

2. SMALLINT 7. DOUBLE

3. MEDIUMINT 8. BIT

4. INT

5. BIGINT
String Data type.

1. CHAR 5. TINYBLOB

2. VARCHAR 6. MEDIUMBLOB

3. BINARY 7. LONGBLOB

4. VARBINARY
Temporal Data Types in MySQL
1. DATE - A date value in 'CCYY-MM-DD'
2. TIME- Time in 'HH:MM:SS'
3. DATETIME - Date-Time - 'CCVV-MM-DD HH:MM:SS'
4. TIMESTAMP - 'CCVV-MM-DD' HH:MM:SS
5. YEAR - CCYY or YY
Create user in MySQL

1. TINYINT	6. FLOAT
2. SMALLINT	7. DOUBLE
3. MEDIUMINT	8. BIT
4. INT
5. BIGINT

1. CHAR	5. TINYBLOB
2. VARCHAR	6. MEDIUMBLOB
3. BINARY	7. LONGBLOB
4. VARBINARY

CREATE USE 'user-name' IDENTIFIED BY 'sample-password';

What are the "VIEWS" ?

→ In MySQL , A view consists of a set of rows that returned if particular query is executed.

→ It also known as "Virtual Table"

→ Advantages : Simplicity, security, not consume any memory, maintainability.

How to you create & Execute VIEWS in MySQL ?

→ We can create views using the CREATE VIEW Statement;

-> A view is table in database that has no values, The views are created by joining one or more tables.
-> Syntax for creating Views
```
CREATE [or REPLACE] VIEW view_name AS SELECT columns FROM TABLES [ WHERE CONDITION ]
```
SELECT AND command.

SELECT * FROM cust_tbl WHERE f_name = "shubham" AND cust_id > 3;

Truncate : It removes complete data without removing it’s structure. It is a DDL Command

TRUNCATE TABLE table_name;
EX.
TRUNCATE TABLE BookHistory;

Update Command in MySQL.

UPDATE 'table_name' SET 'column_name' = 'new_value' [WHERE CONDITION];
EX.
UPDATE BookHistory SET 'Auther' = 'James Bond' WHERE Auther = "JB";

BETWEEN : Get values between particular condition.

SELECT * FROM cus_tbl WHERE ID = 8 AND 11;

Find version of installed MySQL.

Type Following Command.

SHOW VARIABLES LIKE "%version%";

ENUM and SET.

ENUM data type is used in the MySQL datatypes to select any one value from the predefined list. Ex

CREATE DATABASE newEnum;
CREATE TABLE Clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50), 
    memberShip ENUM('silver', 'gold', 'Diamond'),
    interested SET('Movie', 'Music', 'concert');
)

Cannot set multiple in ENUM but we can set multiple values in SET.

What is different between Primary key and Foreign Key ?

-> The database table uses Primary key to identify each row uniquely, It is necessary to declare a primary key on those tables that require tp create a relationship among them. - One or More field of table can be declared as primary key

-> When primary key of any table is used in another table as the primary key or anther field for making a database relation then it is called Foreign Key.

-> Primary Key : Identified a record whereas foreign key refers the primary key of another tables. Primary key never accepts not null value. But Foreign key accepts null value.

Filter duplicate values.

-> A DISTINCT keyword is used to identify the duplicate data from table while retrieving the records. EX.

SELECT * FROM items

output:

id	name	type	brand	man_id
1	samsung	mobile	samsung	1
2	iPhone	mobile	apple	2
3	Sony	TV	Sony	3

Using DISTINCT Keyword

SELECT DISTINCT type FROM items;

output:

type
mobile
TV

Which statement is used in a select query statement for partial matching ?

→ REGEXP and LIKE statement can be used in a select query for partial matching.

REGEXP : used to Search records bases on the pattern matching
LIKE : Is used to search any record by matching string at beginning or end or middle of particular filed value.

Ex. 1. REGEXP (Search records start with ‘S’);

SELECT * FROM BookHistory WHERE name REGEXP "^s";

Ex. 2. LIKE

SELECT * FROM BookHistory WHERE name LIKE "A%";

Rename Table

RENAME TABLE table_name TO new_table_name;
EX.
RENAME TABLE items TO Products;

Retrieve a portion of any Column value by using select Query ?

→ * SUBSTR() Function is used to retrieve the portion of any column. EX

OR we can we ‘SUBSTRING’ keyword

SELECT SUBSTR(name, 1, 5) FROM Products;

output:

Samsu
iPhon
Sony

Calculate Sum of any Column of table ?

→ SUM() Function is used to calculate the sum of any column. EX.

SUM(DISTINCT expression)
EX.
SELECT SUM(Price) as total FROM Products;

output:

total
2109.00

The Practical Approach

Fetch FIRST_NAME from worker table.

SELECT First_name as worker_name from Woker;

//will return all first_name 's

Fetch FIRST_NAME as Upper Case

SELECT upper(First_name) FROM Worker

// Will return all name in upper case

Fetch unique Values from Department

SELECT DISTINCT department FROM Worker;

output :

department
HR
ADMIN
ACCOUNT

Find position of alphabets (”a”) in first_name column ‘Amitabh’ from worker.

SELECT INSTR(first_name, BINARY'a') FROM worker WHERE first_name = "Amitabh";

output :

INSTR( first_name, BINARY ’a’ )
5

Remove white Spaces
1. RTRIM : To remove white spaces from right side. EX
```
SELECT RTRIM(first_name) FROM Worker;
```
b. LTRIM : To remove white spaces from left side. EX
```
SELECT LTRIM(Department) FROM Worker;
```
Query to print first_name and salary from worker table into a single column NAME_SALARY

→ We use CONCAT() keyword to get combined result from two or more tables. EX

SELECT CONCAT(first_name, "=" , Salary) AS 'NAME_SALARY' FROM Worker;

Query to print all worker details from worker table order by First_Name Ascending.

SELECT * FROM Worker ORDER BY First_Name ASC;

Print details for worker with First_name as ‘Shubham’ and ‘NICK’ from worker table.

SELECT * FROM Worker WHERE First_Name In('Shubham', 'NICK');

Query to fetch the count of employee working in the department ‘admin’.

→ The count function return cunt of given queries : EX.

SELECT COUNT(*) FROM Worker WHERE Department = 'Admin';

output :

count(4)
4

AGGREGATE FUNCTIONS : -

SQL Aggregate functions is used to perform calculations on multiple row of a single column of a table it return single value.

COUNT()
SUM()
AVG()
MAX()
MIN()

COUNT()

→ Count the number of rows in database; It uses function COUNT(*) that return all rows

SELECT COUNT(*) FROM Worker WHERE Department = 'Admin';

count(4)
4

SUM()

→ SUM() Function is used to calculate the sum of all selected columns. it only work on numeric values. Syntax: sum();

SELECT SUM(salary) FROM Worker;
or 
SELECT

AVG()

→ Used to calculate average value of the numeric type. AVG function return the average of all non-null values. Syntax: AVG();

SELECT Avg(salary) FROM Workder;

MAX():

→ Max function used to find the maximum value of a certain column . This function determines the largest value of all selected values of column. Syntax: MAX();

SELECT MAX(SALARY) FROM Worker;

MIN();

→ MIN used to find minimum value of a certain column. this function determines the smallest value of all selected of a column.

SELECT MIN(SALARY) FROM Worker

Joins

Two relations

Personal
Professional

select * from personal;

+----+----------+---------+-----------+
| id | name     | address | contact   |
+----+----------+---------+-----------+
| 10 | ankit    | kapali  | 838405939 |
| 20 | ankush   | kapali  | 835403953 |
| 30 | akhilesh | mango   | 335502953 |
| 40 | altaf    | sakchi  | 985402953 |
+----+----------+---------+-----------+

select * from professional;

+------------+-------+------+
| work       | dep   | id   |
+------------+-------+------+
| teacher    | bca   |   20 |
| docktor    | mbbs  |   40 |
| teacher    | mba   |   50 |
| programmer | java  |   60 |
| cashier    | icici |   10 |
+------------+-------+------+

1. Natural Join

select * from personal natural join professional;

Output:

+----+--------+---------+-----------+---------+-------+
| id | name   | address | contact   | work    | dep   |
+----+--------+---------+-----------+---------+-------+
| 10 | ankit  | kapali  | 838405939 | cashier | icici |
| 20 | ankush | kapali  | 835403953 | teacher | bca   |
| 40 | altaf  | sakchi  | 985402953 | docktor | mbbs  |
+----+--------+---------+-----------+---------+-------+

2. Inner Join

select name,contact,dep from personal inner join professional on [personal.id](http://personal.id/) = [professional.id](http://professional.id/);

output:


+--------+-----------+-------+
| name   | contact   | dep   |
+--------+-----------+-------+
| ankit  | 838405939 | icici |
| ankush | 835403953 | bca   |
| altaf  | 985402953 | mbbs  |
+--------+-----------+-------+

3. Cross Join

select * from personal cross join professional;

output:

+----+----------+---------+-----------+------------+-------+------+
| id | name     | address | contact   | work       | dep   | id   |
+----+----------+---------+-----------+------------+-------+------+
| 10 | ankit    | kapali  | 838405939 | teacher    | bca   |   20 |
| 20 | ankush   | kapali  | 835403953 | teacher    | bca   |   20 |
| 30 | akhilesh | mango   | 335502953 | teacher    | bca   |   20 |
| 40 | altaf    | sakchi  | 985402953 | teacher    | bca   |   20 |
| 10 | ankit    | kapali  | 838405939 | docktor    | mbbs  |   40 |
| 20 | ankush   | kapali  | 835403953 | docktor    | mbbs  |   40 |
| 30 | akhilesh | mango   | 335502953 | docktor    | mbbs  |   40 |
| 40 | altaf    | sakchi  | 985402953 | docktor    | mbbs  |   40 |
| 10 | ankit    | kapali  | 838405939 | teacher    | mba   |   50 |
| 20 | ankush   | kapali  | 835403953 | teacher    | mba   |   50 |
| 30 | akhilesh | mango   | 335502953 | teacher    | mba   |   50 |
| 40 | altaf    | sakchi  | 985402953 | teacher    | mba   |   50 |
| 10 | ankit    | kapali  | 838405939 | programmer | java  |   60 |
| 20 | ankush   | kapali  | 835403953 | programmer | java  |   60 |
| 30 | akhilesh | mango   | 335502953 | programmer | java  |   60 |
| 40 | altaf    | sakchi  | 985402953 | programmer | java  |   60 |
| 10 | ankit    | kapali  | 838405939 | cashier    | icici |   10 |
| 20 | ankush   | kapali  | 835403953 | cashier    | icici |   10 |
| 30 | akhilesh | mango   | 335502953 | cashier    | icici |   10 |
| 40 | altaf    | sakchi  | 985402953 | cashier    | icici |   10 |
+----+----------+---------+-----------+------------+-------+------+

4. Left outer join

select name, address, dep, work from personal left outer join professional on [personal.id](http://personal.id/) = [professional.id](http://professional.id/);

output:

+----------+---------+-------+---------+
| name     | address | dep   | work    |
+----------+---------+-------+---------+
| ankush   | kapali  | bca   | teacher |
| altaf    | sakchi  | mbbs  | docktor |
| ankit    | kapali  | icici | cashier |
| akhilesh | mango   | NULL  | NULL    |
+----------+---------+-------+---------+

5. Right outer join

select name, address, dep, work from personal right outer join professional on [personal.id](http://personal.id/) = [professional.id](http://professional.id/);

Outupt:

+--------+---------+-------+------------+
| name   | address | dep   | work       |
+--------+---------+-------+------------+
| ankit  | kapali  | icici | cashier    |
| ankush | kapali  | bca   | teacher    |
| altaf  | sakchi  | mbbs  | docktor    |
| NULL   | NULL    | mba   | teacher    |
| NULL   | NULL    | java  | programmer |
+--------+---------+-------+------------+

DEV Community

MySQL Cheat sheet

The Practical Approach

AGGREGATE FUNCTIONS : -

Joins

Two relations

1. Natural Join

2. Inner Join

3. Cross Join

4. Left outer join

5. Right outer join

Top comments (0)

Read next

From $r$ to $Q^*$: Your Language Model is Secretly a Q-Function

Creating your own UI extension points in Umbraco v14 - Part 1: The Basics

API Routes in Next.js - Part 4

Tabs Component with RiotJS (Material Design)