CSC220 Lab 5 MySQL

Creating the Databases and the Users

• This section is just for completeness and does not have to be performed. Only the admin of the database can create databases. Just read this section and start working on the next one.

• for all accounts from aa to ao, run the query

CREATE DATABASE `220a-ac`CHARACTER SET utf8 COLLATE utf8_general_ci;

• Similarly, create users with same name

 INSERT INTO mysql.user ( HOST, USER, PASSWORD, select_priv, insert_priv,
      update_priv, delete_priv,  create_priv, drop_priv, reload_priv, shutdown_priv, 
      process_priv,  file_priv, grant_priv, references_priv, index_priv, alter_priv, 
      show_db_priv, super_priv, create_tmp_table_priv, lock_tables_priv, 
      execute_priv, repl_slave_priv, repl_client_priv, create_view_priv, show_view_priv,
      create_routine_priv, alter_routine_priv, create_user_priv )
      VALUES ( 'localhost', '220a-ac', PASSWORD('AStrangeWordUsedForPassword'), 
      'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N',
      'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' );

• Then make that user get all privileges on the database with the same name

  DELETE FROM mysql.db WHERE USER='220a-ac' AND HOST='localhost';
  DELETE FROM mysql.tables_priv WHERE USER='220a-ac' AND HOST='localhost';
  DELETE FROM mysql.columns_priv WHERE USER='220a-ac' AND HOST='localhost';
  DELETE FROM mysql.procs_priv WHERE USER='220a-ac' AND HOST='localhost';
  INSERT INTO mysql.db(HOST, db, USER, Select_priv, Insert_priv, Update_priv, "
          Delete_priv, Create_priv, Drop_priv, Grant_priv, References_priv, Index_priv,"
          Alter_priv, create_tmp_table_priv, lock_tables_priv, Create_view_priv, Show_view_priv,"
          Create_routine_priv, Alter_routine_priv, Execute_priv) "
         VALUES( 'localhost', '220a-ac', '220a-ac', 'Y','Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y',"
          'Y', 'Y', 'Y', 'Y' );

Login to the Database

This section is only visible to computers located at Smith College

Create Tables

• You are going to create 3 tables: users, orders, and items
  • users contains an Id and a name field.
  • orders contains an Id for the order, an Id for the user ordering, and an item ordered
  • items contains and Id, a name, and a price

Users

• Click on the SQL tab
• enter the following query in the text area:

CREATE TABLE `users` (
`userId` INT(1) NOT NULL AUTO_INCREMENT,
`userName` VARCHAR(100) DEFAULT NULL,
PRIMARY KEY  (`userId`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

• Click the Go button

• You should see a new table appear in the left, blueish, column. Click on it.
• Click on the Browse and Structure tabs to see two different views of the table.

Orders

• similarly, create the Orders table:

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;

Items

• And finally create the Items table:

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;

Summary

• You should have 3 tables in your database:

Delete Table

• Just to see how this is done, you are going to delete (drop) one of the tables from your database, and recreate it.

• Make phpMyAdmin show you the same page as shown in the image above.

• Select the table you want to delete

• In the With Selected, pick drop

• then click on Go

• confirm.

• The table should be gone.

• You may want to recreate the table with a new CREATE TABLE query.

Insert Data in Tables

• The trick is to type the INSERT query once in the SQL box, and change just the value, and submit the query many times.

Inserting into Users

• Create several users with the following query:

INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Marie');

• Verify that the information ends up in the table, as expected.

Inserting into Items

• Create several items with the following query:

INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'pencil','1');

Inserting into Orders

• Similarly for the table Orders. The query below indicates that the user whose Id is 1 ordered an item whose Id is 2

INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','2');

Modifying information already in the database

• The query below modifies the Item whose Id is 2 and changes its itemName to eraser and its price to 0.5.

UPDATE `items` SET  `itemName`='eraser',`price`='0.5' WHERE `itemId`='2';

• Modify a few entries in your 3 different tables, just to get familiar with the UPDATE command.

The Fun Part: Retrieving Information with SELECT

• Play with the queries below, then figure out queries that can be used to answer the questions:

Main reference for SELECT: http://dev.mysql.com/doc/refman/5.0/en/select.html

Simple SELECT 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 ORDER BY `orderId` DESC;

Question 1

Use a query to list all the user names in alphabetical order

Sub Queries

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


SELECT `itemName` FROM `items` WHERE `itemId` IN ( SELECT `itemId` FROM `orders` WHERE `userId`=1 );

Question 2

What has User #2 bought?

Question 3

Who are the customers who bought Item #1? List them alphabetically

Question 4

There's a recall on Items 2, 3 and 4. List the name of all the users who bought any of these items.

Triple Nested Queries

• Figure out what the following query does, but analyzing it. Start with the most nested one. Run it and modify the user name.

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

Question 5

Generate a query that dispays the name of the customers what bought an items specified by its name.

Generate a query that displays the items ordered by two customers specified by name.