Download and Install MySQL 8 Version on Windows

Open the following URL in the browser

https://dev.mysql.com/downloads/installer/

Click on Download button to download MySQL Server 8

Click on No thanks which starts downloading MySQL server 8

After downloading, to install MySQL Server 8 click on the downloaded file (or) run the downloaded
file (mysql-installer-community-8.0.29.0)

Click Next as given below

Click Next as given below

Click Yes as given below

Click Execute as given below

Click Next as given below

Click Next as given below and make a note of port number 3306 where MySql Server is running
Click Next as given below

Enter Root password as “root” and Retype Password as “root” (You can give password as you wish)
And Click Next
Click Next as given below

Click Execute as given below

Click Finish as given below

Click Next as given below

Click Finish as given below

Click Next as given below

Type the password as “root” and click Check

Click Next as given below

Click Execute as given below

Click Finish as given below

Click Next as given below

Click Finish to complete the Installation

To open mysql prompt

In Search, search for “MySQL 8.0 Command Line Client” and Click MySQL 8.0 Command Line Client to
open mysql> prompt

Enter password as “root” as given in the below screen

SQL Command Types
➢ DDL – Data Definition Language – CREATE, ALTER, DROP, TRUNCATE
➢ DML – Data Manipulation Language – INSERT, UPDATE, DELETE
➢ DQL – Data Query Language – SELECT
➢ TCL – Transaction Control Language – COMMIT, ROLLBACK

MySQL Data Types

- int
- float
- char
- varchar
- date

Creating a Database in mysql

mysql>CREATE DATABASE mydb;

Using Database
mysql>use mydb;

Note: In SQL every statement should end with a semicolon (;)

CREATE TABLE Command

Used to create a table with the specified columns

syntax
CREATE TABLE tablename (Column-Name-1 Datatype(size), Column-Name-2 Datatype(size), ….
Column-Name-n Datatype(size));

EX:
CREATE TABLE Books (bno int(3),bname varchar(10),price float(4));

DROP TABLE Command

Used to delete the given table

Syntax
DROP TABLE tablename;
Ex:
DROP TABLE Books;

DESC Command
Used to give the structure of the table

Syntax
DESC tablename;

Ex:
DESC Books;
INSERT Command
Used to insert the records into the table

Syntax
INSERT INTO tablename VALUES (value-1, value-2, … , value-n);
- Number of values should be equal to the number of columns of the table

INSERT INTO tablename (Column-1, Column-2, …. , Column-n) VALUES (value-1,value-2,….value-n);

- Number of values should be equal to the number of columns given in the command

Ex:
INSERT INTO Books VALUES (111,’java’,500);
INSERT INTO Books (bno,bname) VALUES (222,’xml’);
Inserting Multile Rows in one single insert command
Insert into Books values (333,’html’,400),(444,’css’,550),(555,’hibernate’,650);

UPDATE Command
Used to modify or update the existing records of the table

Syntax
UPDATE tablename SET column=newvalue,column=newvalue,…column=newvalue
[WHERE condition];

Note: WHERE condition is optional

Ex:
UPDATE Books SET price = 700; //for all records price will be updated to 700
UPDATE Books SET price = 800 WHERE bno = 222;

DELETE Command
Used to delete the records of the table

Syntax
DELETE FROM tablename [WHERE condition];
Where condition is optional

Ex:
DELETE FROM Books; //All records of Books table will be deleted
DELETE FROM Books WHERE bno=222;
DELETE FROM Books WHERE price>=500 AND price<=700;

TCL Commands

Commit
Used to save the transactions

mysql>commit

Rollback
Used to undo the uncommitted transactions

mysql>rollback
TRUNCATE Command
Used to delete all the records of the table

Syntax
TRUNCATE TABLE tablename;

Ex:
TRUNCATE TABLE Books;

Question: What is the difference between DELETE FROM Books and TRUNCATE TABLE Books?
Answer: In case of DELETE we can rollback the transaction where as in case of TRUNCATE we cannot
rollback.

Note: All DDL commands are auto committed

SELECT Command
used to retrieve/read records of the table

syntax
SELECT */column-names FROM tablename
[WHERE condition]
[ORDER BY column-1, column-2, ... , column-n]
[GROUP BY Column]
[HAVING condition];

All the four clauses are optional

* indicates all columns

Ex:
SELECT * FROM Books;
SELECT bno,bname FROM Books;

WHERE clause
used to filter the records of the table based on the condition

SELECT * FROM Books WHERE bno=222;

SELECT * FROM Books WHERE price>=500 and price<=700;

ORDER BY Clause
used to arrage the records in ascending or descending order

syntax
SELECT * FROM tablename ORDER BY column-1, column-2, ..., column-n;

Ex:
SELECT * FROM Books ORDER BY price;//ascending
SELECT * FROM Books ORDER BY price DESC;//descending
SELECT * FROM Books ORDER BY price, bname;
Functions
• sum()
• avg()
• count()
• max()
• min()
•
Ex:
mysql>select sum(price) "Total Price" from Books;

Total Price
4550

mysql>select avg(price) "Avg Price" from books;

Avg Price
568.75

mysql>select count(*) "No of Records" from Books;

No of Records
8

mysql>select max(price) "Max Price" from Books;

Max Price
800

mysql>select min(price) "Min Price" from Books;

Min Price
350

GROUP BY Clause
used to group the records based on the given column

Ex:

Employee Table
--------------------------------------
eno ename salary dno
--------------------------------------
111 xxx 5000 10
222 yyy 6000 20
333 zzz 7000 30
444 ppp 5500 10
Ex:
mysql>select dno "Dept No",sum(salary) "Total Salary" from employee group by dno;

Dept No Total Salary

-----------------------------
30 7000
20 6000
10 10500
------------------------------

mysql>select dno "Dept No",count(*) "No of Emps" from employee group by dno;

Dept No No of Emps
-------------------------------
30 1
20 1
10 2
-------------------------------

HAVING Clause
- used to filter the records after grouping the records
- need to be used only with group by clause

Ex:
mysql>select dno "Dept No",sum(salary) "Total Salary" from employee group by dno having
sum(salary)<10000;

Dept No Total Salary

-----------------------------
30 7000
20 6000
------------------------------

ALTER Table command

used to modify/alter the existing table.

By using ALTER Table command, we can do the following

- rename the table
- add new columns
- modify existing columns
- rename columns
- drop/delete columns
Ex:

mysql>alter table books rename to tempbooks;

mysql>alter table tempbooks rename to books;
mysql>alter table books add author varchar(10);
mysql>alter table books modify bname varchar(15);
mysql>alter table books change bname bookname varchar(15);
mysql>alter table books drop column author;
Table Constraints
The conditions which are applied on the table columns are called as constraints

Constraints
➢ primary key
- which is unique and not null and used to identify the row uniquely
➢ not null
- does not accept null values (mandatory columns)
➢ unique
- does not allow duplicate values and accepts null values
➢ check
- used to check a condition for the column
➢ foreign key
- a primary key (pk) of one table acting as an ordinary key in another table and
mainly used to relate the tables is called as foreign key

Ex:
Department table
dno(pk) dname
--------------------------------------------
10 hr
20 finance
30 training
---------------------------------------------

Employee table
eno(pk) ename salary dno(fk)
------------------------------------------------------------------------------------
111 xxx 5000 10
222 yyy 6000 30
333 zzz 4000 40 (error)

Ex:
mysql>create table department (dno number(2) primary key, dname varchar(10) not null);
mysql>create table employee (eno number(3) primary key, ename varchar(10) not null,
salary number(4) check (salary >=5000 and salary <=8000), dno number(2),
constraint fk_dno foreign key (dno) references department(dno));