XAMPPInstallation

XAMPP

•Simple,lightweightApachedistributionthatmakesitextremelyeasyfordeveloperstocreatealocalwebserverfortestinganddeploymentpurposes.

•AllowsyoutovisualizeandmanageyourdatabasesfromyourbrowserusingthephpMyAdminapplication

XAMPPinstallation

URL:https://www.apachefriends.org/index.html

XAMPPinstallation

XAMPPinstallation

XAMPPinstallation

XAMPPinstallation

XAMPPinstallation

XAMPPinstallation

XAMPPinstallation

XAMPPControlPanel

XAMPPControlPanel

AccessingphpMyAdmin

•Inyourbrowser:◦ http://localhost/phpmyadmin/

•phpMyAdminisadatabase

managementtool forMySQL

compatibledatabases.

DatabasemanipulationinphpMyAdmin

•UsingphpMyAdminyoucan:•Createdatabases•Createtables• Setrelationsbetweentables•Accessandmanipulatedata

Example1

DatabaseName:Bookstore

Book(BookID,title,author,language,edition,price,publisherID)

Publisher(ID,Name,address)

Createadatabase

Createatable

Booktable

Publishertable

Settinguprelations

SELECT

INSERT

INSERT

DELETE

DELETE

UPDATE

UPDATE

AddausertoaccesstheDB

Exercise2

DatabaseName:WebProject

Teacher(ID,name,password)

Course(ID,Name,teacher,semester)

Createadatabase

Createatable

CREATE TABLE `webProject`.`teacher` ( id` INT NOT NULL , `name`VARCHAR(200) NOT NULL , `password` VARCHAR(200) NOT NULL ,PRIMARY KEY (`id`)) ENGINE = InnoDB;

CREATE TABLE `webProject`.`course` ( `id` INT NOT NULL , `name`VARCHAR(200) NOT NULL , `teacherID` INT NOT NULL , `semester`VARCHAR(200) NOT NULL , PRIMARY KEY ( id`), INDEX (`teacher`))ENGINE = InnoDB;

Settinguprelation

ALTERTABLE`course`ADDFOREIGNKEY(`teacher`)REFERENCES`teacher`(`id`) ONDELETERESTRICTONUPDATERESTRICT;

AddausertoaccesstheDB

CREATEUSER'mywebProj'@'localhost'IDENTIFIEDWITHmysql_native_password;GRANTUSAGEON*.*TO'mywebProj'@'localhost'REQUIRENONEWITHMAX_QUERIES_PER_HOUR0MAX_CONNECTIONS_PER_HOUR0

MAX_UPDATES_PER_HOUR0MAX_USER_CONNECTIONS0;SETPASSWORDFOR'mywebProj'@'localhost'='***';GRANTALLPRIVILEGESON`webProject`.*TO'mywebProj'@'localhost';

Insert

INSERT INTO `teacher` (`id`, `name`, `password`) VALUES ('1', 'sadeem', '11111'), ('2', 'noura', ‘2222')

INSERT INTO `course` (`id` , `name`, `teacherID`, `semester`) VALUES ('1', 'swe381', '2', 'fall2016') , ( '2', 'swe381', '2 ', 'spring2017'), ('3', 'swe481', '1', 'fall2016') , ( '4', 'swe481', '1','spring2017')

Select

What does this select statement do?

SELECT course.name FROM course INNER JOIN teacher ON teacher.id=course.teacherID and teacher.name='sadeem'

Delete

What does this delete statement do?

Delete from course where name='swe381' and semester='fall2016'

Drop

Droppingtablecourse

Drop table course;

Evaluation

1.createtablesofbelowdatabasewiththerelation

DatabaseName:Restaurants

Restaurant(ID,name,license,owner)

Branch(ID,name,location,manager,phoneNo,restaurantID)

2.insertvalues

3.selectallbranchesof“pizzahut”

4.delete“domino’s”restaurant

ID name license owner

1 domino’s 12-435 JamesMonaghan

2 pizzahut 326-556 DanCarney

ID name location manager phoneNo restaurantID

1 KingFahadRdAlMurabba Omar 920000910 2

2 AlImamAshShafii

AlManar Ahmed 920000910 2

3 UmarIbnAbdulAziz

ArRabwah Adam 920012345 1