A Step-by-Step MySQL Tutorial

A Step-by-Step MySQL Tutorial

6 mins read1.2K Views Comment
clickHere
Updated on Oct 13, 2022 09:52 IST

In this MySQL tutorial article, we will learn MySQL from the basics, including topics such as what a database is, database management system, types of DBMS, MySQL basic commands, and much more. So, let’s dive deeper into the article.

2022_03_Tutorial.jpg

With 79 zettabytes of data created, captured, and consumed globally, database management systems play an essential role in data handling and data manipulation processes. MySQL is one of the most popular relational databases present in the market today to handle data with ease. In this MySQL tutorial, I will be discussing the following topics:

What is a Database?

Consider a container full of toys having various shapes, colors and are of different materials. All the distinct pieces of information of the toys are the data collected about the toys, and the place to store this information is known as a database.

So, a database is nothing but a large container where data can be stored. Organizations, industries across various verticals use data to manipulate and generate meaningful insights on a daily basis.

To do the same, we require a database management system.

What is a Database Management System?

A software application that interacts with the applications and the users to capture, consolidate, manipulate and analyze the data is known as a database management system.  

Types of DBMS

There are four types of database management systems present in today’s market.

  • Relational DBMS (RDBMS): Stores data in relation to another piece of data in the database.
  • Network DBMS: Stores data with multiple relations to form an extensive network and link multiple records. 
  • Hierarchical DBMS:  Stores data which have a predecessor-successor type of relationship. It has a tree-like structure with the nodes representing records and branches representing fields. 
  • Object-oriented DBMS: Stores data in objects, along with instructions for the data.

MySQL is a relational database management system popularly used in multiple organizations and industries.

You can also explore: What is the Difference Between DBMS and RDBMS?

What is MySQL?

MySQL is a free, open-source relational database management system, which is fast, reliable and is very easy to use. This scalable system works on multiple platforms and is now powered by Oracle. 

MySQL follows the Client-Server architecture, wherein the end-users are the clients who access multiple resources from the server. For the ease of users, MySQL comes with a simple graphical user interface to write commands and fetch the data from the server.

Moving ahead with this MySQL tutorial, let us understand the steps to download and install MySQL.

You can also explore: SQL Tutorial : Basic to Advance

Download & Install MySQL

There are two versions offered by the MySQL installer to install the MySQL database server – mysql-installer-web-community-8.0.28.0.msi and mysql-installer-community-8.0.28.0.msi.

To start with the setup, you first need to go to the MySQL Installer from https://dev.mysql.com/downloads/installer/  and choose one of the above versions to download the setup.

Once the setup is downloaded, double-click on the setup to start configuring and follow the wizard to complete the configuration.

For a step-by-step guide to download and install MySQL, you can refer to the following article – How to Install MySQL on Windows 10?

MySQL Commands

All the commands in MySQL can be segregated into the following categories:

  • Data Definition Language (DDL) – Consists of commands that can be used to define a database. Few of the most popular commands are CREATE, DROP, TRUNCATE, ALTER etc.
  • Data Manipulation Language (DML) –  Consists of commands used to manipulate the data present in the database. Few of the most popular commands are SELECT, INSERT, UPDATE, DELETE etc.
  •  Data Control Language (DCL) – Consists of commands used to control the rights and permissions of the database. Few of the most popular commands are GRANT, INVOKE etc.
  • Transaction Control Language (TCL) – Consists of commands used to deal with the transaction of the database. Few of the most popular commands are COMMIT, ROLLBACK, SAVEPOINT etc.

For more information, you can also explore: 75 Most Popular MySQL Commands

In this article, I am going to consider the following database to explain to you the most popular MySQL Commands.

Patients Database

Patient ID Patient Name Sex Age Address Postal Code State Country RegDate
01 Sheela F 23 Flat no 201, Vasavi Heights, Yakutapura  500023 Telangana India 03/03/2020
02 Rehan M 21 Building no 2, Yelahanka 560063 Karnataka India 13/11/2020
03 Anay M 56 H No 1, Panipat 132140 Haryana India 12/12/2021
04 Mahira F 42 House no 12, Gandhinagar 382421 Gujarat India 28/01/2022
05 Nishant M 12 Sunflower Heights, Thane 400080 Maharashtra India 05/01/2022

Popular MySQL Commands

CREATE

Used to create a database, table or index. In the syntax, replace the word schema with table/ index to create a table or an index.

Syntax:

CREATE SCHEMA SchemaName;
 

Example:

CREATE SCHEMA PatientsDatabase;
 

DROP

Used to delete a database, table or column. In the syntax, replace the word schema with table/ column to delete a table or a column.

Syntax:

DROP SCHEMA SchemaName;
 

Example:

DROP SCHEMA PatientsDatabase;
 

ALTER

Used to manipulate columns or constraints by addition, modification or deletion. 

Syntax:

ALTER TABLE TableName
ADD ColumnName datatype;
 

Example:

ALTER TABLE Patients
ADD INT BP;
 

SELECT

Used to select a set of data points from a database. It stores the data returned as output in the result set.

Syntax:

SELECT column1, column2, ...
FROM TableName;
 

Example:

SELECT PatientName, State FROM Patients;

INSERT

Used to insert new data items into a table

Syntax:

INSERT INTO TableName (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
 
--OR
 
INSERT INTO TableName
VALUES (value1, value2, value3, ...);
 
 

Example:

INSERT INTO Patients(PatientID, PatientName, Sex, Age, Address, PostalCode, State, Country, RegDate)
VALUES ('06', 'Veena',’F’,59, 'H-No 29, Banjara Hills', 'Hyderabad', '500046', 'India',05/06/2021);
 
INSERT INTO Patients
VALUES ('06', 'Veena',’F’,59, 'H-No 29, Banjara Hills', 'Hyderabad', '500046', 'India',05/06/2021);
 

UPDATE

Used to modify the existing data items in a table.

Syntax:

UPDATE TableName
SET ColumnName1 = Value1, ColumnName2 = Value2, ...
WHERE condition;
 

Example:

UPDATE Patients
SET PatientName = 'Afsana', PostalCode= '500034'
WHERE PatientID = 1;
 

DELETE

Used to delete the existing data items in a table.

Syntax:

DELETE FROM TableName
WHERE condition;
 

Example:

DELETE FROM Patients
WHERE PatientName='Anay';
 

RENAME

Used to rename a table or a database

Syntax:

RENAME TABLE TableName1 TO TableName2;
 

Example:

RENAME TABLE Patients TO PatientsInfo;
 

USE

Used by the clients to inform which database to be chosen to perform all data manipulation tasks.

Syntax:

USE SchemaName;

Example:

USE PatientsDatabase;
 

TRUNCATE

Used to delete the data present in the tables, without deleting the table. 

Syntax:

TRUNCATE TABLE TableName;

Example:

TRUNCATE TABLE Patients;
 

For a detailed walkthrough of all the MySQL commands, you can refer to my article – Most Popular MySQL Commands

With this, we end this article on MySQL Tutorial. We hope you found it informative. 

Top Trending Tech Articles:
Career Opportunities after BTech | Online Python Compiler | What is Coding | Queue Data Structure | Top Programming Language | Trending DevOps Tools | Highest Paid IT Jobs | Most In Demand IT Skills | Networking Interview Questions Features of Java | Basic Linux Commands | Amazon Interview Questions

Recently completed any professional course/certification from the market? Tell us what you liked or disliked in the course for more curated content.

Click here to submit its review with Shiksha Online.

About the Author

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio

Comments