MySQL For Beginners

SQL Image by Freepik

SQL is an acronym for Structured Query Language. It is a database Language that is used to query and operate database system. In other word, it is defined as programming language used for managing and manipulating relational databases.

What is MySQL ?

MySQL is a database software. It is used to store, retrieve, modify, manage and administrate database. MySQL is basically a Relational Database Management System (RDBMS) that uses SQL to query the database.

Database and Database Management System

Database (DB) is a container where data can be collected systematically so that managing and manipulation of those data are easy. It is an organized collection of data.

For Example:- An online telephone directory uses a database to store their data such as Name, Address, Phone Number and other contact details.

Whereas, Database Management System (DBMS) is basically a software, where we can store, retrieve, define and manage our data in a database. It is an application that creates and accesses the database.

Well-known DBMS include : MySQL, PostgreSQL, Microsoft SQL Server, Oracle, MongoDB.

SQL Commands

SQL commands are the instructions that are used to communicate with database to perform tasks, functions and queries with data.

Types of commands in MYSQL

1. DDL [Data Definition Language]

2. DML [Data Manipulation Language]

3. DQL [Data Query Language]

4. DCL [Data Control Language]

5. TCL [Transaction Control Language]

Let’s discuss about them one by one.

1. DDL

DDL is a SQL Command to define the database schema. It is used to create or modify the structure of database object in the DB. DDL commands are Auto-commit i.e. we cannot use Rollback command to restore the data. In simple terms, Auto-commit permanently saves all the changes in the database. The commands used in DDL are:

i. Create

ii. Drop

iii. Alter

iv. Truncate

v. Rename

Basic SQL Syntax

It is important to note that SQL is not case-sensitive i.e. we can use upper case or lower case for same syntax and it won’t throw any error.

Create :- To Create a Table or Database

1. Creating a Database

create database customer_details;

If the database is already created then it will throw an error, It’s best to use this type of code.

create database if not exists customer_details; 

Once you have created a database, you want to use those database.

use customer_details;

2. Creating a Table

create table customer(            
customer_id int,
customer_name varchar(50),
Age int,
Gender varchar(6),
location varchar(50));

3. To see table structure

show tables;

4. To describe schema of table

describe customer;

Drop :- Drop Table or Database

1. To drop Database

drop Table customer_details;

2. To drop table

drop Table customer;

Alter :- To add column (or) to Modify the datatype

# To add new Column
alter table customer add column Occupation varchar(20);     

# To add new column after gender
alter table customer add column contact_number char(10) after gender;  

# Modifing the structure of the table (uses only for modifing the data type)
alter table customer modify contact_number varchar(12) ;   

# Adding new column on first index position
alter table customer add column customer_id int first;      

# Droping a column
alter table customer drop column Occupation;               

Truncate: To Remove all the records without removing structure of the table.

truncate table customer;

Rename: Renaming Table Name

# To change table name of customer to customer_info

alter table customer rename customer_info; 

2. DML

DML is a SQL Command used to manipulate the data present in the database. They are not Auto-commit i.e. we can use Rollback command to restore the data. The commands used in DML are:

i. Insert

ii. Update

iii. Delete

Insert: Insert command is used to insert a row (or) value into a Table

insert into customer values(1,'Vishal',25,'Male','Bangalore'),
                           (2,'Karan',28,'Male','Mysore'),
                           (3,'Avni',24,'Female','Kolkata');

Update: Update is used to update a value in a particular row

# To change name of customer of Avni to Priya

Update customer set customer_name='Priya' where customer_name='Avni';

Delete: Delete is used to Delete Particular Row

# To delete all the records of Vishal

Delete from customer where customer_name='Vishal'; 

3. DQL

DQL is a SQL Command used to fetch data from database. So, in order to perform queries on data within schema objects, We use DQL commands. The commands used in DQL are:

i. Select

Select: To fetch or retrieve data from database.

select * from customer where gender = 'Female';

4. DCL

DCL is a SQL Command that is used to give privileges (permission) to the user to perform any operation in the database or to take back permission from the user. The commands used in DCL are:

i. Grant

ii. Revoke

# Granting a permision to the userone to use the select statement on customer

Grant Select                   
on customer
to 'userone'; 


# Taking back that permission by using revoke statement 

Revoke select                
on customer
from 'userone';


# Now will give all the permission to the userone
Grant all
on customer
to 'userone';

# Now will take back all the permission from the userone
Revoke all
on customer
from 'userone';

5. TCL

TCL is a SQL Command that is used to control the execution of the Transaction. TCL commands can only be used with DML commands like Insert, Delete or Update. The commands used in TCL are:

i. Commit → To save all transactions to database.

ii. Rollback → To undo transactions that have not already been saved to database.

iii. Save point → To roll the transaction back to a certain point without rolling back the entire transaction.

# Before using TCL, We must set autocommit = 0 (false)
set autocommit = 0;   


# Now will commit the transaction

truncate table customer;
select * from customer;
insert into customer values(1,'Vishal',25,'Male','Bangalore'),
                           (2,'Karan',28,'Male','Mysore'),
                           (3,'Avni',24,'Female','Kolkata');
commit;

# Here, I deleted records of Vishal but i don't commit, So I can rollback the information

Delete from customer where customer_name='Vishal';
select * from customer;                               

# Now will use rollback to restore Vishal's information to the database

rollback;
select * from customer;   # Now We'll see, we have rollback the information of Vishal to the db



savepoint savepoint1_all_info;
Update customer set customer_name='Priya' where customer_name='Avni';
savepoint savepoint2_all_info;

select * from customer;
rollback to savepoint1_all_info;
select * from customer;           # We'll see that we are rolling back to the savepoint 



    

Note:

Comments: Comments are used to explain the section of SQL statements or to prevent execution of the SQL statements.

Types of SQL comments :

# Single line comment

- -Single line comment

/*
Multiple line comments
*/

Difference Between Drop, Delete and Truncate

Drop :

• Drop is a DDL Command.
• It removes both structure and record stored in the table.
• We cannot use Rollback command to restore the data because it is Auto commit.

Truncate :

• Truncate Removes all the records without removing structure of the table.

• It removes all the records without removing structure of the table.

• We cannot use rollback command to restore the data because it is Auto commit.

• Truncate command works faster than Delete Command.

Delete :

• Delete is a DML command.

• It is used to delete one or more records from a table.

• It is comparatively slower than the truncate command.

• We can use Rollback Command to restore the data because it does not Auto commit.