Difference between revisions of "CSC220 Database Management Systems"

From dftwiki3
Jump to: navigation, search
(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 07:14, 6 October 2010

--D. Thiebaut 12:08, 6 October 2010 (UTC)



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');