Follow

Follow
MySql - Overview

MySql - Overview

Veera solaiyappan's photo
Veera solaiyappan
ยทOct 4, 2022ยท

7 min read

Table of contents

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

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

Let's go further Hands-on part

How to install MySQL ?

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

check the below links

MySql shell

Work bench

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?

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

Keep Learning, Keep Growing

๐Ÿฏ Contributors:

Community and Social Footprints :

Did you find this article valuable?

Support Cloudnloud Tech Community by becoming a sponsor. Any amount is appreciated!

Learn more about Hashnode Sponsors
ย 
Share this