MySQL

Is a very popular open source relational database

management system(RDBMS).

What is MySQL?
 Is a RDBMS
 Is a open source
 Is free
 Is ideal for both small & large applications
 Fast, reliable, scalable & easy to use
 Cross platform(Multiple operating systems)
 Was released in 1995
 Developed, distributed & supported by Oracle
corporation.
 Founder name is Ulf Michael

Who uses MySQL?

Websites like Facebook, Youtube, Uber,…

What is RDBMS?
Is a program used to maintain a relational database.
RDBMS is the basis for all modern database systems
like MySQL, Microsoft SQL server, Oracle & Microsoft
Access.

RDBMS uses SQL queries to access the data in the

database.
What is database table?
A table is a collection of related data entries & consists
of columns & rows.
Column holds specific information about every record
in the table
Row is each individual entry that exists in a table
Roll No Name Address
1 Arvind Hyderabad
2 Ananya Miyapur

MySql Create Database:

The create database statement is a
DDL(Data Defination Language) this
statement is used to create a new db in
MySQL RDBMS.
Syntax:
CREATE DATABASE databasename;

Verification: Once the db is created we can check in the

list of databases using SHOW statement.
Show databases;

MySQL Create Table Statement:

To create a table in MySQL RDBMS we use CREATE
TABLE statement.
Syntax:
CREATE TABLE table_name(
Col1 datatype,
 Col2 datatype,
Primary key(one or more columns)
);

create database mysql_7;

show databases;
use mysql_7;
create table customers(
ID int auto_increment,
Name varchar(20) not null,
AGE int not null,
SALARY decimal(12,2),
ADDRESS char(25),
primary key(ID)
);
desc customers;

AUTO_Increment: is MySQL automatically increments

the value in the ID column by one each new record is
add.
NOT NULL: is being used cause we do not want this
field to be NULL. If a user tries to create a record with
null value in that field, MySQL will raise error.
PRIMARY KEY: It ensures that every record in that
column is unique.

Verification:
 Once we have finished creating a table we can
check whether it has been created successfully or not
we use the below query.
desc customers;

MySQL Insert query:

To insert data into MySQL table we use INSERT
statement.
Syntax:
INSERT into table_name(col1, col2, ..)
VALUES(val1, val2,…)

insert into
customers(ID,Name,AGE,SALARY,ADDRESS)values(1,'Ar
vind',32,20000,'Hyderabad');

#Verification using select statment

select * from customers;

insert into
customers(ID,Name,AGE,SALARY,ADDRESS)values
(2,'Ananaya',12,2300.00,'Miyapur'),
(3,'Vihaan',9,1200.00,'DSNR'),
(4,'Rajkumar',20,300.00,'Ameerpet');

#Drop Database is used to delete db along with all data

such as tables, views, index...alter
#Syntax: drop database databasename;
drop database jdbc_6;
show databases;
----***-----
How to use SQL statements:
SQL is used to insert, search, update & delete db
records.

EX:- This statement selects all the records in the

customers table.
Select * from customers;

We should end every statement with semicolon.

The most important SQL commands:

Select – extracts data from a db
Update – updates data in a db
Delete – deletes data from a db
Insert into – inserts new data into a db
Create database – creates a new db
Alter database – modifies a db
Create table – creates a new table
Alter table – modifies a table
Drop table – deletes a table

MySQL Select statement:

Syntax:
Select * from table_name;
Syntax:
 Select col1, col2..
From table_name;
Here col1,col2 are the field names of a table which we
want to select data from.
EX:- To display Name, Age, Address columns of a
customers table.
Select Name, Address, Age from customers;
=================

MySQL Where clause:

This is used to filter records, is used to extract only
those records that fulfill a specified condition.
Syntax:
Select col1, col2…
From table_name
Where condition;

EX:-
#where clause
select * from customers where address='hyderabad';

for text field use single or double inverted comma

for Numbers do not use any inverted comma.

Operators in the Where clause:

Operator Description
= Equal
> Greater than
< Less than
>= Greater than equal
<= Less than equal
<> Not Equal, is some version (!=)
Between Is certain range
Like Search for a pattern
IN To specify multiple possible values for a
column
#where clause
select * from customers where address='hyderabad';

select * from customers where id=2;

#Greater than operator in where clause
select * from customers where salary>2000;

#Not Equal <>

select * from customers where salary<>1000;

#Between certain range

select * from customers where age between 19 and 50;

#Like search for pattern

select * from customers where address like 'a%';

#IN
select * from customers where address
in('Hyderabad','DSNR');
====================
MySQL AND, OR, NOT operators
There where clause can be combined with AND, OR,
NOT operators.
AND, OR operators are used to filter records based on
more than one condition.
The AND operator displays a record if all the conditions
separated by AND are true.
The OR operator displays a record if any of the
conditions separated by OR are true.

Syntax:
AND
Select col1, col2…
From table_name
Where condition1 AND condition2 AND condition3…

EX:-
#AND example
select * from customers where address='Hyderabad'
and name='Arvind';

OR syntax:
Select col1, col2..
From table_name
Where con1 OR con2 or con3;

EX:-
#OR example
select * from customers where address='DSNR' or
name='arv';

NOT syntax:
Select col1, col2…
From table_name
Where NOT condition;

#NOT example
select * from customers where NOT
address='Hyderabad';

Task
Combine AND, OR operator n write a query?
Combine AND, NOT operator n write a query?
---------------********--------------------
MySQL insert into statement
Is used to insert new records in a table.
This can be written in 2 ways
Syntax:
1. Insert into table_name(col1, col2,…)
Values(val1, val2…);
2. Insert into table_name values(val1, val2,…);

EX:-
insert into customers(Name,Age,Salary,Address)
values('Sahasvi',11,5000,'Vpurma');
Note – ID is an auto increment field & will be generated
automatically when a new record is inserted into the
table.

Insert data only in specified columns:-

It is also possible to only insert data in specific columns
EX:-
Insert into customers(Name,Age,Address) values
('Anvi',12,'Nagole');

What is NULL value?

A field with a NULL value is a field with no values.

MySQL Update statement:-

This statement is used to modify the existing records in
a table.
Syntax:
Update table_name
SET col1=val1, col2=val2…
Where condition;

EX:-
#Update statement
update customers set Name='RaoT', Address='Mumbai'
where ID=1;
Note – Where clause in update statement specifies
which record should be updated.
If we do not specify where clause all the records in the
table will be updated.

EX:-
Update customers set salary=0000;

MySQL Delete statement:

This statement is used to delete existing record in a
table
Syntax:-
Delete from table_name where condition;

Note – Where clause in the delete statement specifies

which record to be deleted.
If where clause is not specified all records in the table
will be deleted.

EX:-
#Delete statement
delete from customers where Name='Anvi';

Delete All records:-

This statement will delete all rows in a table without
deleting the table.
Syntax:-
Delete from table_name;

EX:-
Delete from customers;

MySQL Limit clause:-

The Limit clause is used to specify number of records to
return.

Syntax:-
Select col_name
From table_name
Where condition
Limit number;

EX:-
#Limit clause
select * from customers limit 3;
#What if we want to select records 4 to 6, MySQL
provides offset to handle this type.
select * from customers limit 3 offset 2;

#Add a where clause

select * from customers where Address='DSNR' Limit 3;
------------*********----------
MySQL Min & Max Function:
Min() function returns smallest value of the selected
column.
Max() function returns largest value of a the selected
column.
Min() syntax:-
Select MIN(col_name)
From table_name;

Max() syntax:-
Select MAX(col_name)
From table_name;

EX:-
select min(salary) as smallest from customers;
select max(salary) as Largest from customers;

MySQL Count(), Avg() & Sum() functions:-

Count() function returns the number of rows that
matches a specified criteria.
Syntax:
Select count(col_name)
From table_name;

EX:-
select count(ID) from customers;
Note – Null values are not counted.

Avg() function returns the average value of a numeric

column.
Syntax:-
Select avg(col_name)
From table_name;
EX:-
select avg(salary) from customers;

Sum() function returns the total sum of numeric

column.
Syntax:-
Select sum(col_name)
From table_name;

EX:-
select avg(salary) from customers;

MySQL Like operator:-

Like operator is used in a where clause to search for a
specified pattern in a column.
Syntax:-
Select col1, col2..
From table_name
Where column like pattern;

EX:-
#like patterns
select * from customers where Name like 'a%';
select * from customers where name like '%a';
#This finds any value that have "an" in any position
select * from customers where name like '%an%';
#This finds any values that have "r" in the second
position
select * from customers where name like '_a%';
#This finds any values that start with "a" & atleast 2
characters in length
select * from customers where name like 'a__%';
#This finds any values that start with "a" & atleast 3
characters length
select * from customers where name like 'a%a';

MySQL Wildcards:-
Is used to substitute one or more characters in a string.
Wildcard characters are used with like operator. The
like operator is used in where clause to search for a
specified pattern in a column.

Using % wildcard:-
Represents 0 or more characters
Select * from customers where name like ‘arv%’;

Using _ wildcard:-
Represents a single character.
Select * from customers where name like ‘_ana’;

MySQL IN operator:-
This IN operator allows to specify multiple values in
where clause.
Syntax:-
Select col_name
From table_name
Where col_name in(val1, val2…);

EX:-
#IN operator
select * from customers where name in
('RaoT','Sahasvi','Ammu');
select * from customers where name not
in('RaoT','Sahasvi','Ammu');

MySQL Between operator:-

This operator selects values within a given range which
includes numbers, text or dates.

Syntax:-
Select col_name
From table_name
Where col_name between val1 and val2;

EX:-
select * from customers where salary between 1000
and 4000;
select * from customers where salary not between
1000 and 4000;
select * from customers where salary between 1000
and 4000 and id not in(1,2,3);

MySQL Joining tables:-

A join clause is used to combine rows from 2 or more
tables based on a related column between them.

MySQL Joining Tables:-

A Join clause is used to combine rows from 2 or more
tables based on related column b.w them.
Based on common columns from both the tables.
Syntax:-
Select col_name
From table_name1
Inner join table_name2
On table_name1.col_name= table_name2.col_name

EX:-
#create another table
create table orders1(
OID int not null,
date varchar(20) not null,
custimer_id int not null,
Amount decimal(18,2)
);

select * from orders1;

insert into orders1 values

(103, '2022-06-11', 4, 2600.00);

#Joining tables
select id, name, salary, date from customers inner join
orders1 on [Link]=orders1.custimer_id;

Joining Multiple tables using inner join:-

Using inner join query we can join as many tables as
possible.
Syntax:-
Select col_name1, col_nam2…
From table_nam1
Inner join table_name2
On condition_1
Inner join table_name3
On condition_2

EX:-
select OID, Date, Amount, Employee_Name from
customers
inner join orders1
on [Link]=orders1.custimer_id
inner join Employee
on [Link] = [Link];

MySQL Left join keyword:-

The left join keyword returns all records from the left
table(table1) & the matching records from the right
table(table2).

Syntax:-
Select table1.col1, table2.col2..
From table1
Left join table2
On table1.col_name=table2.col_name;

EX:-
#left join query
select ID, Name, salary from Customers left join orders
on [Link] = [Link];

Note – The left join keyword returns all records from

the left table(customers) even if there are not matches
in the right table(orders).

Joining multiple tables with left join:-

Syntax:-
Select col1, col2,…
From table1
Left join table2
On table1.col_name=table2.col_name
Left join table3
On table2.col_name = table3.col_name;

MySQL Right join:-

This query returns all rows from the right table
even if there are no matches in the left table.
 If 0 records are matched in the left table the right
join will still return a row in the result but with a null
value in each column of the left table.
OR
A right join returns all the values from right table, plus
matched values from the left table or null in case of no
matching join.

Syntax:-
SELECT columns
FROM left_table
RIGHT JOIN right_table ON left_table.common_column
= right_table.common_column;

EX:- This statement uses the RIGHT JOIN clause join

the table customers with the table employees.

SELECT
employeeNumber,
customerNumber
FROM
customers
RIGHT JOIN employees
ON salesRepEmployeeNumber = employeeNumber
ORDER BY
employeeNumber;
The column salesRepEmployeeNumber in the
table customers links to the
column employeeNumber in the employees table.

The RIGHT JOIN returns all rows from the

table employees whether rows in the
table employees have matching values in the
column salesRepEmployeeNumber of the
table customers.

If a row from the table employees has no matching

row from the table customers , the RIGHT
JOIN uses NULL for the customerNumber column.

MySQL Self join:-

A self join allows you to join a table to itself. There is no
specific syntax for self join we need to perform using
left join or inner join.
 Assign each instance of the table a unique alias to
differentiate b.w them.
 Specify the join condition
 Select the desired column

EX:- Perform self join on the employees table using

employeeNumber & reportsTo column.

select
concat([Link],',',[Link]) as manager,
concat([Link],',',[Link]) as 'Direct Report'
from
employees e
inner join employees m on
[Link] = [Link]
order by
Manager;

EX:- self join using left join

#self join using left join
select
ifnull(concat([Link],',',[Link]), 'Top
Manager') as 'Manager',
concat([Link],',',[Link]) as 'Direct Report'
from
employees e
left join employees m on
[Link] = [Link]
order by
manager;

MySQL Keywords:-
The select statement allows to select data from 1 or
more tables.
Syntax:-
Select col_name
From table_name;

Using select from statement to retrieve data from a

single column.
#select from statement
select lastname
from employees;

#using select from statement for multiple columns

select lastname, firstname, jobtitle
from employees;

MySQL Order By:-

To sort the rows in the result set we add the order by
clause to the select statement.
Syntax:-
Select col_name
From table_name
Order by
Col1[asc|desc],
Col2[asc|desc];

#Order by query
select
contactLastName,
contactFirstName
from
customers
order by
contactLastName;
#Order by using Descending
select
contactLastName,
 contactFirstName
from
customers
order by
contactLastName desc;
#using Ascending & descending
select
contactLastname,
contactFirstname
from
customers
order by
contactLastname desc,
contactFirstname asc;

MySQL where clause:-

The where clause allows to specify a search condition
for the rows returned by a query.
Syntax:-
Select col_name
From table_name
Where condition;

Q. Get sales report from job title column of employees

table along with firstname & lastname.

MySQL Auto_increment:-
We use this attribute to automatically generate unique
integer values for a column whenever we insert a new
row into the table.
We use Auto_increment attribute for the primary key
column to ensure each row has a unique indentifier.
Syntax:-
Create table table_name
(id int auto_increment primary key,….);

EX:- create table contacts(

Id int auto_increment primary key,
Name varchar(20) Not null,
Email varchar(100) not null);

insert into contacts(name, email)

values('Arvind','arvtts@[Link]');

MySQL Rename table:-

To rename one or more tables we can use the
rename table statement
Syntax:-
Rename table table_name
To new_table_name;

EX:- rename table

rename table contacts to details;
MySQL Add column:-
To add a new column to an existing table we use alter
table statement.
When adding a new column to a table we can specify
its position within a table by using keyword called
“first” if we want the new column to be positioned as
the first column in a table.
Similarly we can use “after existing_col to specify that
we want to add a new column after an existing column.
If we do not specify any position of the new column the
statement will automatically add to the last column in a
table.

Syntax:-
Alter table table_name
Add column new_col_name datatype
[first | after existing_col];

EX:-
create table vendors( id int auto_increment primary
key, lname varchar(20));
insert into vendors(lname, phone)
values('Arvind',1234567890);

select * from vendors;

#Add new column to existing table
alter table vendors
add column phone varchar(16) after lname;
#Adding 2 columns
Alter table vendors
add column email varchar(100) not null,
add column salary decimal(10,2) not null;

alter table vendors

add column email varchar(10) not null; #will throw
error column exists

#To check if the column exists before adding using IF

statement
select
if(count(*) = 1, 'Exist','Not Exist') As result
from information_schema.columns
where
table_schema = 'mysql_7'
and table_name = 'vendors'
and column_name = 'phone';

Q. Create a table with a column as name

Add some data
Later alter table with column id auto_increment

MySQL drop column:-

We want to remove 1 or more columns from a table we
use drop column statement.

Syntax:-
Alter table table_name
Drop column col_name;
EX:-
#Drop column
alter table vendors
drop column email;

alter table vendors

drop column salary,
drop column phone;

alter table vendors

drop column id;

MySQL drop table:-

To remove existing table we use drop table statement.
Syntax:-
Drop table table_name;

EX:-
#Drop table
drop table vendors;

#Drop multiple tables

drop table customers, employee;

MySQL Drop database:-

The drop database statement drops all tables in the
database & deletes the data permanently.

Syntax:-
Drop database[if exists] database_name;

EX:-
#Drop database
drop database mysql_7;

show databases;

MySQL Primary key:-

A primary key is a column or a set of columns that
uniquely identifies each row in the table.
A primary key column consists of unique values.
Syntax1:-
Create table table_name(
Col1 dataType primary key,
Col2,..
);

Syntax2:-
Create table table_name(
Col1 datatype,
Col2 dataType,
Primary key(col1)
);
Syntax3:- Defining primary key for more than 1 col
Create table table_name(
Col1 datatype,
Col2 dataType,
Primary key(col1, col2)
);

Adding primary key to existing table:-

Alter table table_name
Add primary key(col1, col2…);

Note:-
If a primary key consists of multiple columns the
combination of values in these columns must be
unique.
A primary key column cannot contain NULL.

create table products(

id int primary key,
name varchar(255) not null);
select * from products;

insert into products(id, name)

values(1, 'Laptop'),
(2, 'Smartphone'),
(3, 'Wireless Headphones');
#If we enter duplicate value into the primary key
column will get error
insert into products(id,name)
values(1,'Mouse');

#Defining a single column primary key with auto

increment attribute
drop table products;

create table prodcuts(

id int auto_increment primary key,
name varchar(255)not null);

insert into prodcuts(name)

values('Laptop'),
('Smartphone'),
('Mouse');

select * from prodcuts;

#defining multiple column primary key

create table favourties(
customer_id int,
product_id int,
favourties_at timestamp default current_timestamp,
primary key(customer_id, product_id));

#Adding primary key to existing table

create table tags(
id int,
name varchar(222)not null);
alter table tags
add primary key(id);
----****

MySQL Unique constraint:-

To ensure values in a column or a set of columns are
unique.
EX:- Email address, phone number.
Syntax:-
Create table table_name(
Col1 dataType Unique);

Syntax:- to make columns unique

Create table table_name(
Col1 dataType,
Col2 dataType,
Unique (col1, col2)
);

EX:-
#Create a table
create table suppliers(
supplier_id int auto_increment,
name varchar(255) Not Null,
phone varchar(255) not null unique,
 address varchar(255) not null,
primary key(supplier_id), constraint
uc_name_address unique(name,address)
);

#Insert values
insert into suppliers(name,phone,address)
values('ABC Inc',
'1234567890',
'stree 3 road 2 Vpuram');
select * from suppliers;

#insert a different supplier but has the same phone

number that already exists
insert into suppliers(name,phone,address)
values('XYZ Inc',
'1234567890',
'Vpuram');

#Insert into the suppliers table with values that already

exist in columns name & address
insert into suppliers(name,phone,address)
values('ABC Inc',
'0987654321',
'stree 3 road 2 Vpuram');

#Drop a unique constraint

drop index uc_name_address on suppliers;
show index from suppliers;

#Add new unique constraint into existing table

alter table suppliers
add constraint uc_name_address
unique (name,address);

MySQL DateTime column:-

The dateTime data type is used to store both date &
time values.
Syntax:-
Col_name DateTime;

EX:-
#Inserting date & time data type to column
create table events(
id int auto_increment primary key,
event_name varchar(255) not null,
event_time datetime not null
);

#format of datetime YYYY-MM-DD HH:MM:SS

insert into events(event_name, event_time)
values('Mysql', '2025-10-21 07:30:21');

select * from events;

#Inserting current datetime
insert into events(event_name, event_time)
values('MySQL Class',Now());

#Inserting datetime using string

insert into events(event_name,event_time)
values('MySQL Practice',str_to_date('10/28/2024
20:00:00', '%m/%d/%Y %H:%i:%s'));

MySQL BLOB:-
BLOB(Binary Large Object) is a data type allows to store
large binary data like Images, Audio, Video…
There are different types
TINYBLOB 255 bytes 0.25 KB
BLOB 65,535 bytes 64 KB
MEDIUMBLOB 16,777,215 bytes 16 MB
LONGBLOB 4,294,967,295 bytes 4 GB
EX:-
#Working with BLOB
create table images(
id int primary key auto_increment,
title varchar(255) not null,
image_data longblob not null
);

select * from images;

select @secure_file_priv;

insert into images(title, image_data)

values('Matrimonal',load_file('C:/Users/arvind/
Desktop'));

MySQL ENUM:-
ENUM is a string object whose value is chosen from a
list of permitted values defined at the time of column
creation.
Syntax:-
Col_name ENUM(val1, val2…)

EX:-
#working with ENUM
create table tickets(
id int primary key auto_increment,
title varchar(255) not null,
priority enum('Low','Medium', 'High')not null);
insert into tickets(title, priority)
values('Scan for virus in a Computer','High');

select * from tickets;

insert into tickets(title,priority)

values('Upgrade windows OS',1);

insert into tickets(title,priority)

values('Install Chrome in Computer','Medium'),
('Create a new user for login','High');
insert into tickets(title)
values('Refresh the computer');

insert into tickets(title,priority)

values('Invalid ticket',-1);

#Filtering MySQL ENUM vallues

select * from tickets where priority='High';

#Sorting MySQL ENUM values

select title,priority from tickets order by priority desc;

MySQL Text:-
Besides char & varchar character types MySQL
supports TEXT type that provides more features.
TEXT is useful for supporting long format text strings
that can take from 1 byte to 4gb.
EX:- News websites & E-commerce sites

 TINYTEXT: Holds a maximum length of 255 characters (255 bytes).

 TEXT: Holds a maximum length of 65,535 characters (64 KB). Note that the effective
maximum length might be slightly less than 65,535 bytes, depending on the column's
character set.
 MEDIUMTEXT: Holds a maximum length of 16,777,215 characters (16 MB).
 LONGTEXT: Holds a maximum length of 4,294,967,295 characters (4 GB)

EX:-
#Sorting MySQL ENUM values
select title,priority from tickets order by priority desc;

#Working with TEXT types

create table articles(
id int auto_increment primary key,
title varchar(255),
summary tinytext
);
select * from articles;

#Changing from tinytext to text

alter table articles
add column body text not null
after summary;

MySQL Char_length() function:-

Will return the length of a string in characters.
Syntax:-
Char_Length(string)

#char_length funciton
select name, char_length(name)as LengthOfName from
customers;

#concat() function
#This will add 2 or more expressions together
#syntax concat(exp1, exp2...)
select name, concat(Address," ",Salary)as Address from
Customers;
#Field() function -- returns the index position of a value
in a list of values, this function performs a case
insensitive search.
#Syntax:- field(val1,val2...)
select field("c","a","b");

select field("Q","s","q","l");

select field(5,0,1,2,3,4,5);

#Format() function this formats a number to a format

like ex:- "#,###,####"
#syntax:- format(number, decimal_places)

select format(25000.123,0);

#Lcase() function converts a string to lower case

#Syntax lcase(text)
select lcase(Name) as LowerCaseOfCustomerName
from customers;

#Ucase() function converts a string to upper-case

#syntax ucase(text)
select ucase(name) as UpperCaseofCustomerName
from customers;

#Position() function returns the position of the first

occurence of a substring in a string.
#Syntax:- position(substring in string)
select Name, position("a" In Name) from customers;

#Replace() function replaces all occurence of a

substring with a string with a new sub string.
#syntax:- replace(string, substring, newstring)
select replace("SQL","SQL","HTML");
select replace("XYZ ABC XYZ", "X", "M");

#Reverse() function reverses a string & returns the

result
#Syntax:- reverse(String)
select reverse(Name)from customers;

#Space() function returns a string of the specified

number of space characters
#Syntax:- space(number)
#select space(4);

MySQL AddDate() function

Adds a time/date to a date & then returns the date.
Syntax:-
Adddate(date, interval value addunit)
Or
Adddate(date, days)

#AddDate() function
select adddate("2020-05-15 09:30:21", Interval 15
minute);

select adddate("2020-05-15", interval 1 month);

#AddTime() function adds a time interval to a

time/date & returns the time/date time
#Syntax AddTime(datetime, addtime)
select addtime("2023-06-11 09:30:20","2");

#Adding microseconds
select addtime("2023-06-11 09:30:20","2.0000004");

#Adding all hours, minuts, seconds at a time

select addtime("2023-10-21 09:30:20", "2:10:15");

#Curdate() function returns the current date.

select curdate();

select curdate() +1;

===========
MySQL Regular Expression:-
A regular expression is loosely defined as a sequence of
characters that represent a pattern in an input text.
It is used to locate or replace text strings using some
patterns, this pattern can be either single character,
multiple character or word.
Syntax:-
Expression REGEXP pattern;

EX:- finding records in the table whose name starts

from a letter.
#Finding all the records in the customers table whose
name starts with letters
select * from customers where name regexp '^k';

#finding all records whos name ends with sh

select * from customers where name regexp 'sh$';

#finding records whose name contains sh

select * from customers where name regexp 'sh';

#finding records whose name starting with vowel &

ending with ol
select * from customers where name regexp
'^[aeiou].*ol$';

#Finding records whose name starts with consonant

select * from customers where name regexp
'^[^aeiou]';

MySQL RLIKE operator:-

It is used to search data in a database using patterns or
regular expressions also known as pattern matching.
In other words RLIKE operator is used to determine
whether a given regular expression matches a record in
a table or not.
It returns 1 if the record is matched & 0 otherwise.
Syntax:-
Expression RLIKE pattern;

EX:- In place of REGEXP we use RLIKE

select * from customers where name rlike '^ch';

RLIKE on strings:-
This can perform pattern matching not only on
database tables but also on individual strings.
Syntax:-
Select expression RLIKE pattern;

select * from customers where name rlike '^ch';

#Finding if a pattern exists in an individual string or not

select 'Welcome to MySQL' rlike 'To';

select 'Welcome to MySQL' rlike 'Hello';

MySQL NOT like operator:-

Both LIKE & NOTLIKE operator perform matching in a
database table.
Syntax:-
Select col_name from table_name
Where col_name NOTLIKE condition;

EX:- Finding all the records in customers whose name

doesn’t start with letter.
#Finding the record whose name doesnt start with k
select * from customers where name not like 'k%';

#query to display records of customer whose name

doesnt end with ik
select * from customers where name not like '%ik';

#display records where the second character of the

records
select * from customers where name not like '_h%';

Try these
#Query to find record from customers whose name
doesnt start and end with
#The _ underscore represents a single number or
character
select * from customers where name not like 'M_y';

select * from customers where name not like 'k__';

======
MySQL NOT REGEXP operator:-
It is used to retrieve all the records present in a
database that do not satisfy the specified pattern.
Syntax:-
Expression NOT REGEXP pattern.

EX:- query to find all names not starting with letter.

select * from customers where name not regexp '^ra';

#Query to find all the names that not ending

select * from customers where name not regexp 'ik$';

#The specified pattern is null

select 'Welcome to MySQL' not regexp null;

MySQL Regexp_instr() function:-

Is used to match specified patterns with either a string
or the data in database table.
This function returns the starting index of the substring
of a string that matches the specified pattern.
Syntax:-
Regexp_instr(expr,
pattern[pos[occurrence[return_option[match_type]]]])

Expr – The string in which search is performed

Pattern – The pattern that is searched in the string
Pos – position in expr at which to start the search,
default is 1
Occurrence – which occurrence of a match to search
for, default is 1
Return_option – which type of position to return
Match_type – This is a string which consists of various
characters representing the desired features of the
match

EX:-
#Query to find position starts at 5 to find the 1st
occurrence
select regexp_instr('Welcome to MySQL Class','T',5,1,1)
as result;

MySQL Regexp_replace():-
Is used to find & replace occurrences of a string that
match specific pattern.
If There is a match it replaces the string with another.
If there is no match will return to original string.
If the string or pattern is null it returns null.
Syntax:-
Regexp_replace(expr, pattern,
repl[pos[occurrence[match_type]]])

EX:-
#Regexp_Replace function
select regexp_replace('Welcome to MySQL class',
'Welcome','Welll') as result;

select regexp_replace('Welcome to MySQL class',

'M','X',10,1,'i')as result;
MySQL Regexp_substr():-
Is used for pattern matching in a database. This
function retruns substring of a string that matches the
pattern specified.
A pattern is defined as an extended regular expression
or just an ordinary string.
Syntax:-
Regexp_substr(expr,
pattern[pos[occurrence[match_type]]])

EX:-
#Regexp_substr() function
select regexp_substr('Welcome to MySQL class', 'we')
as result;

#If the pattern is not present in the string the result is

returned as null
select regexp_substr('Welcome to MySQL class','Hi')as
result;

#Let us pass 5 as value to the pos parameter so the

search starts from the 5th position, we are passong the
occurrence value as 1
select regexp_substr('Welcome To MySQL class',
'To',5,1,'i')as result;
#Regexp_substr() function
select regexp_substr('Welcome to MySQL class', 'we')
as result;
#If the pattern is not present in the string the result is
returned as null
select regexp_substr('Welcome to MySQL class','Hi')as
result;

#Let us pass 5 as value to the pos parameter so the

search starts from the 5th position, we are passong the
occurrence value as 1
select regexp_substr('Welcome To MySQL class',
'To',5,1,'i')as result;

use demo;
select * from customers;

select regexp_substr(Name,'^Ra')as result from

customers;
*************

ONLINE SHOPPING MANAGEMENT SYSTE

The Online Shopping Management System is a database-driven application designed to
manage customers, products, orders, and payments efficiently.

The system is implemented using MySQL,

This project demonstrates:

 Database design principles

 Normalization
 Relationships
 SQL queries (Basic to Advanced)
 Views, Triggers, Stored Procedures
 Transactions & Security
Objectives of the Project
 To design a relational database for an e-commerce system
 To implement CRUD operations
 To understand primary and foreign key relationships
 To apply normalization techniques
 To implement advanced MySQL features

Database Design
1. Customers
2. Categories
3. Products
4. Orders
5. Order_Items
6. Payments

UML Class Diagram

Customer (1) -------- (M) Orders

Orders (1) -------- (M) Order_Items

Product (1) -------- (M) Order_Items

Category (1) -------- (M) Product

Orders (1) -------- (1) Payment

Relationships Explanation

Relationship Type
Customer → Orders One-to-Many
Orders → Order_Items One-to-Many
Product → Order_Items One-to-Many
Category → Products One-to-Many
Orders → Payments One-to-One

Database Creation

Table Structure with Queries

 Customers Table
  Categories Table
 Products Table
 Orders Table
 Order_Items Table
 Payments Table

CRUD Operations

 Insert
 Select
 Update
 Delete

Advanced Queries

 Join Query
 Total Revenue

View Creation

Stored Procedure

Trigger

Transaction

-----------------**************----------------