Difference between revisions of "CSC220 Database Management Systems"

From dftwiki3
Jump to: navigation, search
(Insert Data)
(Insert Data)
Line 64: Line 64:
 
  INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'5','4');
 
  INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'5','4');
 
  INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'5','3');
 
  INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'5','3');
 +
 +
==Table-related operations==
 +
 +
* to delete a table: "DROP TABLE"
 +
* to erase a table without deleting it: "TRUNCATE TABLE"
 +
 +
==Some queries==
 +
 +
SELECT * FROM `items`;
 +
 +
SELECT `itemName` FROM `items`;
 +
 +
SELECT `itemName` FROM `items`WHERE `itemId`=2;
 +
 +
SELECT * FROM `users` ORDER BY `userName` ASC;
 +
 +
SELECT * FROM `orders` WHERE `userId`=1;
 +
 +
SELECT `itemName` FROM `items` WHERE `itemId` IN ( 1, 2, 3 );
 +
 +
SELECT `itemName` FROM `items` WHERE `itemId` IN ( SELECT `itemId` FROM `orders` WHERE `userId`=1 );
 +
 +
SELECT `itemName` FROM `items` WHERE `itemId` IN (
 +
            SELECT `itemId` FROM `orders` WHERE `userId` IN  (
 +
                          SELECT `userId` FROM `users` WHERE `userName`='Marie') );

Revision as of 07:35, 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

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

Table-related operations

  • to delete a table: "DROP TABLE"
  • to erase a table without deleting it: "TRUNCATE TABLE"

Some queries

SELECT * FROM `items`;

SELECT `itemName` FROM `items`;

SELECT `itemName` FROM `items`WHERE `itemId`=2;

SELECT * FROM `users` ORDER BY `userName` ASC;

SELECT * FROM `orders` WHERE `userId`=1;

SELECT `itemName` FROM `items` WHERE `itemId` IN ( 1, 2, 3 );

SELECT `itemName` FROM `items` WHERE `itemId` IN ( SELECT `itemId` FROM `orders` WHERE `userId`=1 );
SELECT `itemName` FROM `items` WHERE `itemId` IN ( 
            SELECT `itemId` FROM `orders` WHERE `userId` IN  ( 
                         SELECT `userId` FROM `users` WHERE `userName`='Marie') );