780 likes | 1.27k Views
XAMPP Installation. SWE381: Web Application Development. XAMPP. Simple, lightweight Apache distribution that makes it extremely easy for developers to create a local web server for testing and deployment purposes.
E N D
XAMPP Installation SWE381: Web Application Development
XAMPP • Simple, lightweight Apache distribution that makes it extremely easy for developers to create a local web server for testing and deployment purposes. • Allows you to visualize and manage your databases from your browser using the phpMyAdmin application
XAMPP installation • URL: https://www.apachefriends.org/index.html
Accessing phpMyAdmin • In your browser: • http://localhost/phpmyadmin/ • phpMyAdmin is a database • management tool for MySQL • compatible databases.
Database manipulation in phpMyAdmin • Using phpMyAdmin you can: • Create databases • Create tables • Set relations between tables • Access and manipulate data
Example 1 • Database Name: Bookstore • Book (BookID, title, author, language, edition, price, publisherID) • Publisher (ID, Name, address )
Exercise 2 • Database Name: WebProject • Teacher (ID, name, password) • Course (ID, Name, teacher, semester )
Create a table 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;
Setting up relation ALTER TABLE `course` ADD FOREIGN KEY (`teacher`) REFERENCES `teacher`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
Add a user to access the DB CREATE USER 'mywebProj'@'localhost' IDENTIFIED WITH mysql_native_password;GRANT USAGE ON *.* TO 'mywebProj'@'localhost' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;SET PASSWORD FOR 'mywebProj'@'localhost' = '***';GRANT ALL PRIVILEGES ON `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 • Dropping table course Drop table course;
Evaluation • 1. create tables of below database with the relation • Database Name: Restaurants • Restaurant (ID, name, license , owner) • Branch (ID, name, location, manager, phoneNo, restaurantID) • 2. insert values • 3. select all branches of “pizza hut” • 4. delete “domino’s” restaurant