Difference between revisions of "CSC220 Database Management Systems"
(Created page with '--~~~~ ---- __TOC__ =Example= * Users, purchasing items from a store. * 3 tables: users, orders, items CREATE TABLE `users` ( `userId` INT(1) NOT NULL AUTO_INCREMENT, `…') |
(→Example) |
||
Line 10: | Line 10: | ||
* 3 tables: users, orders, items | * 3 tables: users, orders, items | ||
+ | ==Create Tables== | ||
CREATE TABLE `users` ( | CREATE TABLE `users` ( | ||
`userId` INT(1) NOT NULL AUTO_INCREMENT, | `userId` INT(1) NOT NULL AUTO_INCREMENT, | ||
Line 31: | Line 32: | ||
PRIMARY KEY (`itemId`) | PRIMARY KEY (`itemId`) | ||
) ENGINE=MYISAM DEFAULT CHARSET=utf8; | ) 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'); |
Revision as of 08:14, 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');