MY SQL

WHAT IS DATA?
Data is a collection of a distinct small unit of information. It can be
used in a variety of forms like text, numbers, media, bytes, etc. it can
be stored in pieces of paper or electronic memory, etc.

Word 'Data' originated from the word 'datum' that means 'single
piece of information.' It is the plural of the word datum.

In computing, Data is information that can be translated into a form

for efficient movement and processing. Data is interchangeable.

WHAT IS DATABASES?
The database is a collection of interrelated data which is used to retrieve,
insert and delete the data efficiently. It is also used to organize the data in
the form of a table, schema, views, and reports, etc.

For example: The college Database organizes the data about the admin,
staff, students and faculty ,Fees ,etc.

Using the database, you can easily retrieve, insert, and delete the
information.

DBMS
Database Management System: The software which is used to manage databases
is called Database Management System (DBMS). For Example, MySQL, Oracle, etc.
are popular commercial DBMS used in different applications. DBMS allows users
the following tasks:

Data Definition: It helps in the creation, modification, and removal of definitions

that define the organization of data in the database.

Data Updation: It helps in the insertion, modification, and deletion of the actual
data in the database.

Data Retrieval: It helps in the retrieval of data from the database which can be
used by applications for various purposes.

User Administration: It helps in registering and monitoring users, enforcing data

security, monitoring performance, maintaining data integrity, dealing with
concurrency control, and recovering information corrupted by unexpected failure.
KEY FEATURES OF DBMS
1. Data modeling
2. Data storage and retrieval
3. Concurrency control
4. Data integrity and security
5. Backup and recovery

DBMS TOOLS
1. MYSQL
2. Oracle
3. SQL server
4. Sybase
5. MS ACCESS

WHAT IS RDBMS?
● RDBMS stands for Relational Database Management System.
● RDBMS is a program used to maintain a relational database.
● RDBMS is the basis for all modern database systems such as MySQL,Microsoft SQL
Server, Oracle, and Microsoft Access.
● RDBMS uses SQL queries to access the data in the database.

WHAT IS SQL?
SQL :- Structured query language. It provides a protocols of how to data store
and retrieve easily. SQL uses certain commands like Create, Drop, Insert, etc. to
carry out the required tasks.
These SQL commands are mainly categorized into three categories as:
● DDL – Data definition language is used to create and modify the structure of
database objects in the database.
CREATE , ALTER,TRUNCATE,DROP

● DML – Data manipulation language the SQL commands that deals with the
manipulation of data present in the database belong to DML or Data Manipulation
Language and this includes most of the SQL statements.
INSERT, DELETE, UPDATE,SELECT

● DCL – Data control language includes commands such as GRANT and REVOKE
which mainly deal with the rights, permissions, and other controls of the database
system.
FIELDS WHERE WE USE DBMS
● Telecom : There is a database to keep call records, customer details, timing. Without
database system it is hard to maintain that huge amount of data that keeps updating every
millisecond.

● Banking system : for storing customer info, tracking day to day credit and debit
transaction and generating book statement etc. All this work has been done with the help
of database management system.

● Sales : To store customer information, product information and invoice bill.

● Airlines : To travel through airlines we make early reservation this reservation

information with flight schedule is stored in database.

● Education sector : database system are frequently used in school and collages to store
and retrieve the data regarding student details, staff details etc.

● Online shopping : you must be aware of the online shopping website such as amazon,
flipkart etc. in this we store all the data like information, address, credit details and
invoice etc.

DBMS ARCHITECTURE
The DBMS design depends upon its architecture. The basic client/server
architecture is used to deal with a large number of PCs, web servers,
database servers and other components that are connected with networks.

The client/server architecture consists of many PCs and a workstation which

are connected via the network.

DBMS architecture depends upon how users are connected to the database
to get their request done.
INSTALLATION OF MY SQL
1. Install the updated version of MYSQL, like the current version is MYSQL Installer 8.0.36.

2. Download setup > start installation.

3. Install Mysql server , router , shell and workbench and execute all the files to install.
4. Set strong MYSQL router password.
5. Finish all installation programme.
6. Go to file explorer in PC > c:/ drive > programme file > MYSQL > mysql server > Bin
> copy the folder path.
7. Search “edit the system environment variables” in PC.
8. Click on environment variables > select “path” > click on “edit” > click on “new” > paste
the mysql bin path > click on “ok”.

a)
 b)

c)
9. Open “Command prompt” in a system > type mysql --version > then type mysql -u root -p >
enter MYSQL rooter password.
COMMAND FUNCTIONS OF MYSQL
(;) :- it is used to end command.

(Create databases command)

1. SHOW DATABASE/SHOW TABLES
( this is used to view existed databases/Tables in database in MYSQL server)
Command :- mysql> show database;
There are 4 already given databases
a) Information_schema
b) Mysql
c) Performance_schema
d) sys
2. CREATE A NEW DATABASE
( this is used to create a new database in mysql server)
Command :- mysql> create database name(ex:- amazon);

3. USE DATABASE
(this command is used to use newly created database or any existing databases)
Command:- mysql> use database name(ex:amazon);

4. CREATE A TABLE IN DATABASE

(this command is used to create a table in database)
( there are “fields” are used to define a table and which is needed in a table)
Command:- mysql>create table tablename{ex: customer}( ID{field name} int, Name char(20),
phone_number varchar(15) , Address varchar(50));

There are fiew syntax used to help to define the fields are:-
a) Int :- this is known as “integer” , it contains numbers from 0 - 9, In this syntax we can
only enter upto 9 nos only (ex:-123456789)
b) Char :- this is known as “character” , this syntax is used for “text” only.
c) Varchar :- In this syntax we can use numbers and text together.

5. DESCRIBE TABLE
(this command is used to view table)
Command :- mysql> desc tablename(ex:- customer);
6. INSERT DATA
(this command is used to enter data in table of a database)
Command:- mysql> insert into tablename(ex:customer) values(1001,”ABC” , ‘9874563210”,”H-
20 AABBCC”);

* insert data into specific values

Command:- mysql >insert into tablename(customer)[ id , name , address)
-> values(“001”,”nisha”,h-21 nangloi”);

7. VIEW DATA IN A TABLE

(this command is used to view data in a table of a database)
command:-mysql>select * from tablename (ex:customer);

ASSIGNMENT:- Create a given below database

DELETE DATABASE
( this command is used to delete existing database)
Command:- mysql> drop database database name(amazon);
“SELECT” COMMAND
1. SELECT
The SELECT statement is used to select data from a database.
Command :- mysql> select field name(ID) from tablename(customer);

2. MULTIPLE SELECT
The multiple SELECT statement is used to select multiple data from a
database.
Command:- mysql> select field name(ID),field name(Name),field
name(address) from tablename(customer);

3. SELECT DISTINCT SYNTAX

The following SQL statement selects only the DISTINCT values from the
"Country" column in the "Customers" table:It counts and returns the
number of different (distinct) countries in the "Customers" table:
Command: mysql > select distinct field name(COUNTRY) from
tablename(customer);
( It only shows how many countries have been used in table)

4. MULTIPLE SELECT DISTINCT SYNTAX

Command:- mysql > select distinct field name(ID),field name(name)
>- from tablename(customer);

“WHERE” COMMAND
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
Command:- mysql> select * from tablename(customer)
>- where fieldname(name) = “ABC”;

There are few operators used in “where” command

operator description command

 = Equal WHERE Price = 10;

> Greater than WHERE Price > 10;

< Less than WHERE Price < 10;

>= Greater than or equal WHERE Price >= 10;

<= Less than or equal WHERE Price <= 10;

<> Not equal. Note: In some WHERE Price <> 10;

versions of SQL this
operator may be written
as !=

BETWEEN Between a certain range WHERE Price BETWEEN

10 AND 20;

LIKE Search for a pattern WHERE City LIKE 's%';

IN To specify multiple WHERE City IN

possible values for a ('Paris','London');
column

OR To show limited data of a WHERE

table Country='Mexico’ Or
country = 'UK';

“ALTER” COMMAND
There are 4 types of alter command is used are:-
1) Add
2) Drop
3) Modify
4) Change
ADD COMMAND
(To add a column in a table)
Command :- mysql > alter table tablename(customer) add new field
name(pincode int);

DROP COMMAND
(To delete a column in a table)
Command:- mysql > alter table tablename(customer) drop fieldname (pincode);
MODIFY COMMAND
(To change the data type of a column in a table)
Command:- mysql>alter table tablename(customer) modify field name(ID) varchar(20);

CHANGE COMMAND
( TO change the data or change one field to another field in a table)
Command:- mysql>alter table tablename(customer) change field name(name)
newfield name (customer_name) char(20);
( Note :-char(20) = this syntax is not different from previous and new field in a
table)
UPDATE CLAUSE
An update clause is used to update the data with a reference of given table.
1. Where clause
2. Same data
Where clause
Where clause is used to update the data with a reference field of table.
Command:- mysql > update tablename(customer) set fieldname( supplierid = “1”) where
fieldname (country = “germany”);

Same data clause

Same data update clause is use to update the same data in many fields in a table.
Command:- mysql> update tablename(customer) set field(pin_code = “110085”);
And , Or and Not Operator
AND :- “and “ operator is used to find out the different / multiple data at a same time.
Command :- mysql> select * from customer(tablename)
-> where country(field) = ‘germany’ and city(field) = ‘berlin’

OR :- “OR” operator is used to find out the result of different fields belonging from same data
type
Command:- mysql > select * from customer(tablename)
-> where country(field) = ‘germany’ or city(field) = ‘berlin’
Or
-> where city(field) = ‘mexico’ and city(field) = ‘berlin’

NOT :- “NOT”operator is used to find out the data which is not required/necessary.
Command:- mysql>select * from customer(tablename)
-> where not country(field) = ‘germany’
IS NULL OPERATOR
(“is null” operator is used to get only “null” value data from data types in table)
( it only shows the data whose value is “null”)
Command;- mysql > select customer_name(field) , contact_name(field) , address(field) from
customer(tablename)
-> where supplier_id (field with null values) is null;

IS NOT NULL OPERATOR

(“is not null” operator is used to get data without “null” values in a datatype of a table)
( it only shows those data those don’t have null values)
Command;- mysql > select customer_name(field) , contact_name(field) , address(field) from
customer(tablename)
-> where supplier_id (field with null values) is not null;
Order clause
There are two order clause ascending and descending

Ascending order clause

( this clause to arrange data in ascending order)
Command :- mysql > select * from customer(table name) order by country(field);

● (order by several columns)

Command :- mysql > select * from customer(table name) order by country(field), city(field);

● (Arrange order in another reference)

Command:- mysql>select name(field) from customer(table name) order by id(reference field)
asc;

Descending order clause

( this clause to arrange data in descending order)
Command :- mysql > select * from customer(table name) order by country(field) desc;

● (Arrange order in another reference)

Command:- mysql>select name(field) from customer(table name) order by id(reference field)
desc;

● (order by several columns)

Command :- mysql > select * from customer(table name) order by country(field), city(field)
desc;

Order arrange in ascending and descending order

Command:- mysql > select from customer(table name)
-> order by country(field) asc , city(field) desc;
LIMIT OPERATOR
The LIMIT clause is used to specify the number of records
There are three types of limit operators:
a) Limit operator
b) Limit offset
c) Where limit

Limit operator
Command :- mysql > select * from customer(table name) limit 3(no. Of data want);

Limit Offset
(limit offset is used to get data dropping the beginning no. of data)
Command :- mysql > select * from customer(table name) limit 3(no. Of data want) offset
2(dropping no. of data)

Where limit
(where limit is used to get specify no. of data using where clause)
Command :- mysql > select * from customer(table name) where country(field) = “germany” limit
3(no. Of data want)
ARITHMETICAL OPERATOR
A) MINIMUM
B) MAXIMUM
C) COUNT
D) SUM
E) AVERAGE

MINIMUM OPERATOR
MINIMUM operator is used to get minimum no. of data from a data type
Command :- mysql > select min (price)(fieldname) as smallestprice from customer(tablename);
Command:- mysql>select min(feildname) from customer(tablename);

MAXIMUM OPERATOR
MAXIMUM operator is used to get maximum no. of data from a data type
Command :- mysql > select max (price)(fieldname) as largestprice from customer(tablename);

COUNT OPERATOR
It is used to count the no. of data given in databases
Command:- mysql > select count(productname)(fieldname) from customer(tablename);

SUM OPERATOR
It is used to sum the no. of data given in databases
Command:- mysql > select sum(price)(fieldname) from customer(tablename);

AVERAGE OPERATOR
It is used to average the no. of data given in databases
Command:- mysql > select avg(price)(fieldname) from customer(tablename);
LIKE OPERATOR
( like operator is used for find out the special or specific character from a given databases)
Command:- mysql> select * from customer(table name)
-> where customer_name(field name) like “a%”;

% = Represents zero or more characters

_ = Represents a single character

LIKE COMMANDS MEANING

'a%' Finds any values that start with "a"

'%a' Finds any values that end with "a"

'%an%' Finds any values that have "an"

'_n%' Finds any values that have "n" in the

second position

'a_%' Finds any values that start with "a"

and are at least 2 characters in length

'a__%' Finds any values that start with "a"

and are at least 3 characters in length

'a%e' Finds any values that start with "a"

and ends with "e"
LOGIC OPERATOR
A) ALL OPERATOR
( It shows the all results in a database)
COMMAND:- SELECT * FROM Products( TABLE NAME)
-> WHERE Price > ALL (SELECT Price FROM Products WHERE Price > 50);
B) AND OPERATOR
( IT shows the result where two different conditions are true)
COMMAND:- SELECT * FROM customer( TABLE NAME)
-> WHERE City = "London" AND Country = " ";
C) ANY OPERATOR
( it shows if any condition is “true” it shows the all result)
COMMAND:- SELECT * FROM Products( TABLE NAME)
-> WHERE Price > ANY (SELECT Price FROM Products WHERE Price > 100);
D) BETWEEN OPERATOR
( it shows the result between two different ranges)
COMMAND:- SELECT * FROM Products( TABLE NAME)
-> WHERE Price BETWEEN 10 AND 20;
E) EXISTS OPERATOR
( it shows the value given which is exist is shows the result, if not is shows “empty set”)
COMMAND:- SELECT * FROM Products( TABLE NAME)
-> WHERE Price > EXIST (SELECT Price FROM Products WHERE Price > 50);
OR
COMMAND:- SELECT * FROM Products( TABLE NAME)
-> WHERE Price > ALL (SELECT Price FROM Products WHERE Price > 500);
F) IN OPERATOR
( it shows the result which is given)
COMMAND:- SELECT * FROM customer( TABLE NAME)
->WHERE City IN ('Paris','London');
G) NOT OPERATOR
( it shows the result which is not required)
COMMAND:- SELECT * FROM customer( TABLE NAME)
-> WHERE City NOT LIKE 's%';
H) OR OPERATOR
( This operator is used to find out the result of different fields belonging from same data type)
COMMAND:- SELECT * FROM customer( TABLE NAME)
-> WHERE City = "London" OR Country = "UK";
I) SOME OPERATOR
( it shows the limited data)
COMMAND:- SELECT * FROM Products( TABLE NAME)
-> WHERE Price > SOME (SELECT Price FROM Products WHERE Price > 50);
PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a table
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key; and in the table, this primary key can
consist of single or multiple columns (fields).

IN CASE OF CREATE TABLE

Command :- mysql > create table person(Table name)((id int , name char,etc) add primary
key(ID);

IN CASE OF ALTER TABLE

Command :- mysql > alter table person(Table name) add primary key(person_id);

DROP PRIMARY KEY

Command :- mysql > alter table person(Table name) drop primary key

PRIMARY KEY ON MULTIPLE COLUMNS

Command :- mysql > alter table person(Table name) add constraint pk_person(Table name)
primary key(person_id(field) , name(field));
FOREIGN KEY
● The FOREIGN KEY constraint is used to prevent actions that would destroy
links between tables.
● A FOREIGN KEY is a field (or collection of fields) in one table, that refers to
the PRIMARY KEY in another table.
● The table with the foreign key is called the child table, and the table with
the primary key is called the referenced or parent table.
● Primary key should be in first table and as well as to those column who
will link to another table.
● Foreign key basically used to link one table to another table with same
connection.
Create the following two tables:-
Table 1 called “person”
Table 2 called “ordertable”

IN CASE OF CREATE TABLE

Command :- mysql > create table ordertable( table 2)( orderid int , ordernumber int , person_id
int ,foreign key(person_id) reference person(table 1)(person_id)

IN CASE OF ALTER TABLE

Command :- mysql > alter table ordertable(TABLE 2) add foreign key(person_id) references
person(TABLE 1)(person_id);
INSERT DATA INTO FOREIGN KEY
Command :- mysql > insert into ordertable values(“5” , “87452”,”3”)

Note :- data should be in within (person_id) where foreign key is applied on (person_id) .
if insert value which is different from (person_id) will not accepted.

NOTE :- first data should be added in primary key or table 1 then data will allow to insert
into foreign key or table 2

( in the following screen first data should be added in “table 1” wherein “primary key” then data
will inserted into “table 2” wherein “foreign key”
DROP FOREIGN KEY

COMMAND :- mysql > alter table ordertable(table 2) drop foreign key fk_person(table
1)ordertable(table 2);
INTEGRATION IN EXCEL BY ODBC
1. Download mysql odbc connector MSI version.
2. open setup of odbc connector >
JOINS
A JOIN clause is used to combine rows from two or more tables, based on a
related column between them.
The primary key should be in Table 1.
Foreign keys should be in Table 2.

Table 1 (person_id) = primary key

Table 2 (person_id) = foreign key

Types of joins
1. Inner join
2. Left join
3. Right join
4. Cross join

Inner join
The INNER JOIN keyword selects records that have matching values in both
tables
Command :- select * from person(table 1) inner join ordertable(table 2) on
person(table1).person_id(field) = ordertable(table2).person_id(field)
(table 1)Person_id = primary key
(table 2)Person_id = foreign key
LEFT JOIN

It is used to join table 1 data to with table 2

It is used to join left side data of a given table

Command:- mysql > select * from person(table 1) left join ordertable(table 2)

on person(table1).person_id(field) = ordertable(table2).person_id(field)

RIGHT JOIN

It is used to join table 2 data with table 1

It is used to join right side data of a given table

Command:- mysql > select * from person(table 1) right join ordertable(table 2)

on person(table1).person_id(field) = ordertable(table2).person_id(field)

CROSS JOIN
It is used to join each data to another data table of each other’
Command:- mysql > select * from person(table 1) cross join ordertable(table 2)
on person(table1).person_id(field) = ordertable(table2).person_id(field)