Difference between revisions of "CSC220 Database Management Systems"
(→Example) |
(→Insert Data) |
||
Line 50: | Line 50: | ||
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'ruler','1.5'); | INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'ruler','1.5'); | ||
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'markers','2.5'); | INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'markers','2.5'); | ||
+ | |||
+ | |||
+ | 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'); |
Revision as of 07:17, 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
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'); 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'); 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');