# MySql - Overview


In this blog, we are going to learn and recall about ** MySQL ** Database fundamentals and commands


<iframe src="https://giphy.com/embed/FUncLC0uG07Dw1upZ4" width="480" height="480" frameBorder="0" class="giphy-embed" allowFullScreen></iframe>

## What is MySQL?

- MySQL is the most popular Open Source Relational SQL database management system. 

- MySQL is one of the best RDBMS being used for developing web-based software applications.


## RDBMS Terminology

Before we proceed to explain the MySQL database system, let us revise a few definitions related to the database.

- ** Database ** − A database is a collection of tables, with related data.

- ** Table ** − A table is a matrix with data. A table in a database looks like a simple spreadsheet.

- ** Column ** − One column (data element) contains data of one and the same kind, for example the column postcode.

- ** Row ** − A row (= tuple, entry or record) is a group of related data, for example the data of one subscription.

- ** Redundancy ** − Storing data twice, redundantly to make the system faster.

- ** Primary Key ** − A primary key is unique. A key value can not occur twice in one table. With a key, you can only find one row.

- **Foreign Key** − A foreign key is the linking pin between two tables.

- ** Compound Key ** − A compound key (composite key) is a key that consists of multiple columns, because one column is not sufficiently unique.

- ** Index ** − An index in a database resembles an index at the back of a book.

- ** Referential Integrity ** − Referential Integrity makes sure that a foreign key value always points to an existing row.


## MySQL Database

MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. MySQL is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL is becoming so popular because of many good reasons −

- MySQL is released under an open-source license. So you have nothing to pay to use it.

- MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages.

- MySQL uses a standard form of the well-known SQL data language.

- MySQL works on many operating systems and with many languages including PHP, PERL, C, C++, JAVA, etc.

- MySQL works very quickly and works well even with large data sets.

- MySQL is very friendly to PHP, the most appreciated language for web development.

- MySQL supports large databases, up to 50 million rows or more in a table. The default file size limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a theoretical limit of 8 million terabytes (TB).

- MySQL is customizable. The open-source GPL license allows programmers to modify the MySQL software to fit their own specific environments.


Hope you can get the basic knowledge about MySQL 

<iframe src="https://giphy.com/embed/GCvktC0KFy9l6" width="450" height="480" frameBorder="0" class="giphy-embed" allowFullScreen></iframe>



Let's go further Hands-on part


<iframe src="https://giphy.com/embed/FRT9eogpwgTou7LEF6" width="480" height="480" frameBorder="0" class="giphy-embed" allowFullScreen></iframe>


## How to install MySQL ?

you can install  ** MySql shell (CLI) ** or you can install ** MySQL workbench(GUI) **

check the below links 

[MySql shell](https://dev.mysql.com/downloads/shell/)

[Work bench](https://dev.mysql.com/downloads/workbench/)





## MySQL Database

A database is used to store the collection of records in an organized form. It allows us to hold the data into tables, rows, columns, and indexes to find the relevant information frequently. We can access and manage the records through the database very easily





### Create Database

```
CREATE DATABASE cloudnloud-db;  

``` 

### Select Database


```
USE cloudnloud-db; 

``` 


### Drop Database

We can drop/delete/remove a MySQL database quickly with the MySQL ** DROP DATABASE ** command

```
DROP DATABASE cloudnloud-db;  

``` 

### List All Database

```
 SHOW DATABASES; 

``` 




## Table & Views

### CREATE Table

A table creation command requires three things:

- Name of the table
- Names of fields
- Definitions for each field


```
CREATE TABLE [IF NOT EXISTS] table_name(  
    column_definition1,  
    column_definition2,  
    ........,  
    table_constraints  
);  
``` 


```

CREATE TABLE members(  
    id int NOT NULL AUTO_INCREMENT,  
    name varchar(45) NOT NULL,  
    occupation varchar(35) NOT NULL,  
    age int NOT NULL,  
    PRIMARY KEY (id)  
);  

``` 




### ALTER Table



```
ALTER TABLE table_name  
ADD new_column_name column_definition  
[ FIRST | AFTER column_name ];  
``` 


```
ALTER TABLE members  
ADD mem_age varchar(40) NOT NULL;  
``` 



### TRUNCATE Table

The TRUNCATE statement in MySQL removes the complete data without removing its structure.


```
TRUNCATE TABLE members;  
``` 


### Drop Table

 This statement removes the complete data of a table along with the whole structure or definition permanently from the database


```
DROP TABLE  members;
``` 


### MySQL Views

the View is a virtual table created by a query by joining one or more tables


```
CREATE [OR REPLACE] VIEW view_name AS    
SELECT columns    
FROM tables    
[WHERE conditions]; 
``` 


```
CREATE VIEW trainer AS    
SELECT course_name, trainer     
 FROM courses; 
``` 




## MySQL Queries


### INSERT Record


```
insert into members values(101,'rahul','delhi');  
``` 


### UPDATE Record


```
update members set name='bob', city='london' where id=101;  
``` 


### DELETE Record

```
delete from members where id=101;  
``` 


### SELECT Record


```
SELECT * from members;  
``` 


 ** Are you able to understand? **

<iframe src="https://giphy.com/embed/XMBJ0l20sNWEM" width="480" height="320" frameBorder="0" class="giphy-embed" allowFullScreen></iframe>

then read further



## MySQL Clauses

### MySQL WHERE

** WHERE ** Clause is used with SELECT, INSERT, UPDATE and DELETE clause to filter the results

```
SELECT *  
FROM members  
WHERE address = 'Mau';  
``` 


### MySQL DISTINCT

** DISTINCT ** clause is used to remove duplicate records from the table and fetch only the unique records


```
SELECT DISTINCT expressions  
FROM tables  
[WHERE conditions];  


SELECT DISTINCT address  
FROM members;  

``` 


### MySQL ORDER BY

** ORDER BY ** Clause is used to sort the records in ascending or descending order


```

SELECT expressions  
FROM tables  
[WHERE conditions]  
ORDER BY expression [ ASC | DESC ];  


SELECT *  
FROM members  
WHERE address = 'Lucknow'  
ORDER BY mem_name;  

``` 



### MySQL GROUP BY

GROUP BY Clause is used to collect data from multiple records and group the result by one or more columns.


```
SELECT expression1, expression2, ... expression_n,   
aggregate_function (expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n;  


SELECT address, COUNT(*)  
FROM   members   
GROUP BY address;   

``` 


### MySQL HAVING

 ** HAVING ** Clause is used with GROUP BY clause. It always returns the rows where the condition is TRUE.



```

SELECT expression1, expression2, ... expression_n,   
aggregate_function (expression)  
FROM tables  
[WHERE conditions]  
GROUP BY expression1, expression2, ... expression_n  
HAVING condition;  



SELECT mem_name, SUM(working_hours) AS "Total working hours"  
FROM members  
GROUP BY mem_name  
HAVING SUM(working_hours) > 5;  
``` 


## MySQL Conditions

### MySQL AND

AND condition is used with SELECT, INSERT, UPDATE or DELETE statements to test two or more conditions in an individual query



```
WHERE condition1  
AND condition2  
...  
AND condition_n; 


SELECT *  
FROM members  
WHERE mem_firstname = 'Ajeet'  
AND mem_id > 3;   
``` 



### MySQL OR


```
SELECT *  
FROM members  
WHERE mem_firstname = 'Ajeet'  
OR mem_id > 100;  
``` 


### MySQL AND OR


```
SELECT *  
FROM members  
WHERE (course_name = 'Java' AND mem_name = 'Aryan')  
OR (mem_id < 2); 

``` 

### MySQL LIKE


```
SELECT mem_name  
FROM members  
WHERE address LIKE 'Luck%';  
``` 


### MySQL IN


```
SELECT *  
FROM members  
WHERE mem_name IN ('Ajeet', 'Vimal', 'Deepika');  
``` 

### MySQL NOT


```
SELECT *  
FROM members  
WHERE mem_name NOT IN ('Ajeet','Vimal','Deepika');  
``` 


### MySQL IS NULL


```
SELECT *  
FROM members  
WHERE mem_name IS NULL;  
``` 

### MySQL IS NOT NULL


```
SELECT *  
FROM members  
WHERE mem_name IS NOT NULL;  
``` 

### MySQL BETWEEN


```
SELECT *  
FROM members  
WHERE mem_id BETWEEN 1 AND 3;  
``` 



## MySQL JOIN

It is used to retrieve data from multiple tables

There are three types of MySQL joins:


- INNER JOIN 
- LEFT JOIN 
- RIGHT JOIN



 ** Inner JOIN ** 


```

SELECT columns  
FROM table1   
INNER JOIN table2  
ON table1.column = table2.column;  

``` 


** Left Join **



```
SELECT columns  
FROM table1  
LEFT [OUTER] JOIN table2  
ON table1.column = table2.column;  

``` 


** Right Join **


```
SELECT columns  
FROM table1  
RIGHT [OUTER] JOIN table2  
ON table1.column = table2.column;  
``` 






## Aggregate Functions

### MySQL count()


```

SELECT COUNT (aggregate_expression)    
FROM table_name    
[WHERE conditions];    


SELECT COUNT(mem_name) FROM members;

``` 


### MySQL sum()


```

SELECT SUM(aggregate_expression)    
FROM tables    
[WHERE conditions];  

SELECT SUM(working_hours) AS "Total working hours" FROM members;

``` 


### MySQL avg()


```
SELECT AVG(aggregate_expression)    
FROM tables    
[WHERE conditions];  


SELECT AVG(working_hours) Avg_working_hours FROM members;  

``` 


### MySQL min()


```
SELECT MIN ( DISTINCT aggregate_expression)  
FROM table_name(s)  
[WHERE conditions];  

SELECT MIN(income) AS Minimum_Income FROM members;

``` 


### MySQL max()

```
SELECT MAX(income) AS "Maximum Income" FROM members;  
``` 



### MySQL first()


```
SELECT column_name  
FROM table_name  
LIMIT 1; 

SELECT mem_name   
FROM members  
LIMIT 1;

``` 


### MySQL last()


```
SELECT column_name  
FROM table_name  
ORDER BY column_name DESC  
LIMIT 1;  

SELECT mem_name   
FROM members  
ORDER BY mem_id DESC  
LIMIT 1;  
``` 


Hope you got some good insights after reading this blog

<iframe src="https://giphy.com/embed/NEvPzZ8bd1V4Y" width="459" height="480" frameBorder="0" class="giphy-embed" allowFullScreen></iframe>



** Keep Learning, Keep Growing **



## 🍯 Contributors:

- [Karthikeyan S](https://www.linkedin.com/in/herbie36/)
- [Karuppasamy PKN](https://www.linkedin.com/in/ksamypkn/)
- [Deepika Karunakaran](https://www.linkedin.com/in/deepi19/)
- [Veera solaiyappan](https://www.linkedin.com/in/veera26/)
- [Aadhityaa SB](https://www.linkedin.com/in/aadhi06/)


## Community and Social Footprints :

- [GitHub](https://github.com/cloudnloud)
- [Twitter](https://twitter.com/cloudnloud)
- [YouTube Cloud DevOps Free Trainings](https://www.youtube.com/c/CloudnLoud)
- [Linkedin Page](https://www.linkedin.com/company/cloudnloud/)
- [Linkedin Group](https://www.linkedin.com/groups/9124202/)
- [Discord Channel](https://discord.com/invite/vbjRQGVhuF)
- [Dev](https://dev.to/cloudnloud)