Difference between revisions of "CSC220 Database Management Systems"
(→Insert Data) |
(→Insert Data) |
||
Line 35: | Line 35: | ||
==Insert Data== | ==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,'Marie'); | ||
Line 42: | Line 43: | ||
INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Elaine'); | 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,'pencil','1'); | ||
Line 51: | Line 53: | ||
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'markers','2.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','1'); |
Revision as of 08:19, 6 October 2010
--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');