CSC220 Database Management Systems
--D. Thiebaut 12:08, 6 October 2010 (UTC)
Contents
Example
- Users, purchasing items from a store.
- 3 tables: users, orders, items
Create Tables
CREATE TABLE `users` ( `userId` INT(1) NOT NULL AUTO_INCREMENT, `userName` VARCHAR(100) DEFAULT NULL, PRIMARY KEY (`userId`) ) ENGINE=MYISAM DEFAULT CHARSET=utf8; CREATE TABLE `orders` ( `orderId` INT(1) NOT NULL AUTO_INCREMENT, `userId` INT(1) DEFAULT NULL, `itemId` INT(1) DEFAULT NULL, PRIMARY KEY (`orderId`) ) ENGINE=MYISAM DEFAULT CHARSET=utf8; CREATE TABLE `items` ( `itemId` INT(1) NOT NULL AUTO_INCREMENT, `itemName` VARCHAR(100) DEFAULT NULL, `price` FLOAT DEFAULT NULL, PRIMARY KEY (`itemId`) ) ENGINE=MYISAM DEFAULT CHARSET=utf8;
Insert Data
- Add 5 users to the database (Marie, John, Manon, Rob, Elaine)
INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Marie'); INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'John'); INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Manon'); INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Rob'); INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Elaine');
- Add 6 items and their price (pencil, eraser, paper, ink, ruler, markers)
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'pencil','1'); INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'erase','0.5'); UPDATE `items` SET `itemId`='2',`itemName`='eraser',`price`='0.5' WHERE `itemId`='2'; INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'paper','5'); INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'ink','30'); INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'ruler','1.5'); INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'markers','2.5');
- Create some user orders of items. e.g. Marie buys a pencil, an eraser, paper, and ink.
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','1'); INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','2'); INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','3'); INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','4'); INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'2','1'); INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'3','1'); INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'3','4'); INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'5','4'); INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'5','3');