Mysql lab

Due November 11, 2011

Introduction

SQL (Structured Query Language) is a standard language for creating, accessing, and manipulating databases. A database is a collection of tables. Each table consists of several rows of data called records. Records in a table have special fields called keys to facilitate fast access.

Getting started

The database server runs on the machine called cs.mvnu.edu. You will start Mysql on cs.mvnu.edu. In the commands substitute your account for “skon”.

1. Open a terminal window and log on to cs.mvnu.edu.

ssh cs.mvnu.edu

2. Start mysql with your username and have it prompt for a password.

skon@Taris:~$ mysql -u skon -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 72024 Server version: 5.1.49-1ubuntu8 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

3. Display the databases that you have access to.

mysql> show databases;

Database
information_schema
airline
skon

3 rows in set (0.00 sec)

4. Select your database to work on.

mysql> use skon;

Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed

5. Check to see if the database contains any tables. If there are any delete them using the drop table command.

mysql> show tables;

Tables_in_skon
shop

1 row in set (0.00 sec)

mysql> drop table shop;

Query OK, 0 rows affected (0.00 sec)

mysql>show tables; Empty set (0.00 sec)

6. Creating and Accessing Tables
a. Create a table for florists.

mysql> create table florists (
->CompanyId char(3),
-> CompanyName char(20),
-> Address char(20),
-> City char(10),
-> Zipcode char(5),
-> Phone char(10),
-> StartPrice real(6,2),
-> primary key( CompanyId )
-> );

Query OK, 0 rows affected (0.00 sec)

b. Check the properties of the newly created table.

mysql> describe florists;

Field Type Null Key Default Extra
CompanyId char(3) NO PRI    
CompanyName char(20) YES   NULL  
Address char(20) YES   NULL  
City char(10) YES   NULL  
Zipcode char(5) YES   NULL  
Phone char(10) YES   NULL  
StartPrice double(6,2) YES   NULL  

7 rows in set (0.00 sec)

c. Insert some records into florists.

mysql> insert into florists values
-> (’KAU’, ’Kukkakauppa’, ’320 W. Quincy’, ’Hancock’, ’49930’,
-> ’9064822060’, 9.99);

mysql> insert into florists values
-> (’BIR’, ’Blue Iris’, ’403 Shelden Av.’, ’Houghton’, ’49931’,
-> ’9064822583’, 19.99);

mysql> insert into florists values
-> (’CFL’, ’Calumet Floral’, ’221 Fifth Street’, ’Calumet’, ’49913’
-> ’9063371711’, 9.99);

mysql> insert into florists values
-> (’ECO’, ’Econo Foods’, ’1000 Sharon Avenue’, ’Houghton’, ’49931’
-> ’9064879675’, 1.99);

mysql> insert into florists values
-> (’SLE’, ’Flowers by Sleeman’, ’1201 Memorial Rd.’, ’Houghton’, ’
-> ’9064824023’, 17.99);

mysql> insert into florists values
-> (’KAU’, ’Kukkakauppa’, ’320 W. Quincy’, ’Hancock’, ’49930’,
-> ’9064822060’, 9.99);

mysql> insert into florists values
-> (’KAT’, ’Kathy\’s Country Flowers’, ’109 W. Quincy’, ’Hancock’,
-> ’9064826261’, 9.99);

d. Display the records in the table.

mysql> select * from florists;

CompanyId CompanyName Address City Zipcode Phone StartPrice
BIR Blue Iris 403 Shelden Av. Houghton 49931 9064822583 9.99
CFL Calumet Floral 221 Fifth Street Calumet 49913 9063371711 9.99
ECO Econo Foods 1000 Sharon Avenue Houghton 49931 9064879675 1.99
SLE Flowers by Sleeman 1201 Memorial Rd. Houghton 49931 9064824023 17.99
KAU Kukkakauppa 320 W. Quincy Hancock 49930 9064822060 9.99
KAT Kathy’s Country Flow 109 W. Quincy Hancock 49930 9064826261 9.99

6 rows in set (0.00 sec)

e. You should not be able to enter records with duplicate keys.

mysql> insert into florists values
-> (’KAU’, ’abc’, ’abc’, ’abc’, ’12345’, ’9061234567’, 9.99); ERROR 1062 (23000): Duplicate entry ’KAU’ for key 1

f. Display only two columns.

mysql> select address, city from florists;

address city
403 Shelden Av. Houghton
221 Fifth Street Calumet
1000 Sharon Avenue Houghton
1201 Memorial Rd. Houghton
320 W. Quincy Hancock
109 W. Quincy Hancock

6 rows in set (0.00 sec)

g. Display only one column. Notice that it will list duplicates.

mysql> select city from florists;

city
Houghton
Calumet
Houghton
Houghton
Hancock
Hancock

6 rows in set (0.00 sec)

h. Use distinct to eliminate duplicates.

mysql> select distinct city from florists;

city
Houghton
Calumet
Hancock

3 rows in set (0.00 sec)

i. Display only the florists in Houghton.

mysql> select * from florists where city=’Houghton’;

CompanyId CompanyName Address City Zipcode Phone StartPrice
BIR Blue Iris 403 Shelden Av. Houghton 49931 9064822583 9.99
ECO Econo Foods 1000 Sharon Avenue Houghton 49931 9064879675 1.99
SLE Flowers by Sleeman 1201 Memorial Rd. Houghton 49931 9064824023 17.99

3 rows in set (0.00 sec)

j. Display those that have a start price of $10.00 or under.

mysql> select * from florists where StartPrice <= 10;

CompanyId CompanyName Address City Zipcode Phone StartPrice
BIR Blue Iris 403 Shelden Av. Houghton 49931 9064822583 9.99
CFL Calumet Floral 221 Fifth Street Calumet 49913 9063371711 9.99
ECO Econo Foods 1000 Sharon Avenue Houghton 49931 9064879675 1.99
KAU Kukkakauppa 320 W. Quincy Hancock 49930 9064822060 9.99
KAT Kathy’s Country Flow 109 W. Quincy Hancock 49930 9064826261 9.99

5 rows in set (0.00 sec)

k. Try combining conditions using and.

mysql> select * from florists where
-> city=’Houghton’ and StartPrice <= 10;

CompanyId CompanyName Address City Zipcode Phone StartPrice
BIR Blue Iris 403 Shelden Av. Houghton 49931 9064822583 9.99
ECO Econo Foods 1000 Sharon Avenue Houghton 49931 9064879675 1.99

2 rows in set (0.00 sec)

l. Try a condition with between.

mysql> select * from florists where StartPrice between 8 and 10;

CompanyId CompanyName Address City Zipcode Phone StartPrice
BIR Blue Iris 403 Shelden Av. Houghton 49931 9064822583 9.99
CFL Calumet Floral 221 Fifth Street Calumet 49913 9063371711 9.99
KAU Kukkakauppa 320 W. Quincy Hancock 49930 9064822060 9.99
KAT Kathy’s Country Flow 109 W. Quincy Hancock 49930 9064826261 9.99

4 rows in set (0.00 sec)

m. Insert a record and then delete it.

mysql> insert into florists values
-> (’ABC’, ’abc’, ’abc’, ’abc’, ’12345’, ’9061234567’, -1); Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from florists;

count(*)
7

1 row in set (0.00 sec)

mysql> delete from florists where StartPrice < 0; Query OK, 1 row affected (0.00 sec)

mysql> select count(*) from florists;

count(*)
6

1 row in set (0.00 sec)

n. Try ordering the records.

mysql> select CompanyName , Phone, StartPrice
-> from florists order by StartPrice ASC;

CompanyName Phone StartPrice
Econo Foods 9064879675 1.99
Blue Iris 9064822583 9.99
Calumet Floral 9063371711 9.99
Kukkakauppa 9064822060 9.99
Kathy’s Country Flow 9064826261 9.99
Flowers by Sleeman 9064824023 17.99

6 rows in set (0.01 sec)

o. Try updating a record.

mysql> select CompanyName , Phone, StartPrice from florists;

CompanyName Phone StartPrice
Blue Iris 9064822583 9.99
Calumet Floral 9063371711 9.99
Econo Foods 9064879675 1.99
Flowers by Sleeman 9064824023 17.99
Kukkakauppa 9064822060 9.99
Kathy’s Country Flow 9064826261 9.99

6 rows in set (0.00 sec)

mysql> update florists
-> set Phone = ’9063371711’
-> where CompanyName = ’Calumet Floral’;
Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0

mysql> select CompanyName , Phone, StartPrice from florists;

CompanyName Phone StartPrice
Blue Iris 9064822583 9.99
Calumet Floral 9063371711 9.99
Econo Foods 9064879675 1.99
Flowers by Sleeman 9064824023 17.99
Kukkakauppa 9064822060 9.99
Kathy’s Country Flow 9064826261 9.99

6 rows in set (0.00 sec)

p. Next time, we will access the database using Php;

mysql> exit; Bye

To turn in

Copy the contents of your terminal buffer. paste them is a word document, and submit to Moodle.
Topic revision: r5 - 2011-12-05 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback