Metropolitan State University

ICS 325

Building Services on the Internet

Lab 11

Creating, Updating, and Deleting Tables in MySQL

(Linux Redhat 7.1)

§ Log onto "redhat.ics.metrostate.edu" using your class account, then do the following (the $ represents the prompt so do not type it in):

$ mysql –u f04325?? -p

$ Enter Password:

$ use f04325??

1. Create Database Table for Food Items in a Food Order System.

(example of the CREATE TABLE command )

CREATE TABLE p3foodItems(

id INTEGER PRIMARY KEY,

name VARCHAR(20) NOT NULL,

description TEXT,

price FLOAT(5,2) NOT NULL,

picName VARCHAR(20),

categoryID INTEGER

);

2. Create Database Table for Food Categories in a Food Order System.

CREATE TABLE p3categories(

id INTEGER PRIMARY KEY,

name VARCHAR(20) NOT NULL,

description TEXT,

picName VARCHAR(20)

);

3. To see the names of the tables you’ve created, type

SHOW TABLES;

4. Create Database Table for Customers of a Food Order System.

CREATE TABLE p3customers(

id INTEGER PRIMARY KEY,

fName VARCHAR(20),

lName VARCHAR(30) NOT NULL,

address VARCHAR(40) NOT NULL,

city VARCHAR(30),

state VARCHAR(2),

zip VARCHAR(5) NOT NULL,

phone VARCHAR(15)

);

5. To display the description of the schema for the table you just created, type

DESCRIBE p3customers;

6. Add Table fields missed in the Customers Table.

(example of using the Alter Table command)

ALTER TABLE p3customers

ADD username VARCHAR(8) NOT NULL;

ALTER TABLE p3customers

ADD password VARCHAR(8) NOT NULL;

7. Create Database Table for an order invoice in a Food Order System.

(example of using auto_increment)

CREATE TABLE p3orderInfo(

id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,

customerID INTEGER NOT NULL,

ccNumber VARCHAR(20),

orderDate DATETIME,

fillDate DATETIME,

shipDate DATETIME,

deliverDate DATETIME);

8. Create Database Table for Items List in the order invoice

(example of setting multiple primary keys).

CREATE TABLE p3orderList(

orderID INTEGER NOT NULL,

foodItemsID INTEGER NOT NULL,

quantity TINYINT NOT NULL,

PRIMARY KEY(orderID, foodItemsID));

9. Understand how drop works.

CREATE TABLE test(

test1 VARCHAR(10));

DROP TABLE test;

10. To exit, type

$ EXIT

Copyright © Jigang Liu, Dave Valentine, Sue Fitzgerald

Database Diagram