CS 4950 Spring 2016
Lab 7
Find a computer and log into the
OSX side using your CS login name and password.
Open Firefox and find the class web
page.
Open TextWrangler - use the finder
window and go to applications.
Open a new text file, called Lab7.txt, to record your work today -
you will paste in your command lines and the results of running
them. The header of the file should look like:
Your Name
CS 4950
Lab 7
May 12, 2016
Find the Terminal - use the finder
window and go to applications and then utilities.
Today we are going to work on the things
we learned in Chapter 15:
- Open a database
- Make tables
- Insert data into the tables
- Learn to use some MySQL queries
The SQL server is on Hopper. I will give you a paper with your
account information and instructions on how to login. Since we will
only be using it for a short time, changing your password is
optional.
All work today will be done int the Terminal, with cuting and
pasting into your Lab7.txt file in TextWrangler. Run the commands
below
(they are also in your pcfb/examples/mysql_commands.txt):
(~) ssh hopper
Password: this is your usual cs password
yourlogin@hopper:(~) mysql
-u <yourlogin> -p
Enter password: this is you database password
mysql> SHOW DATABASES;
mysql> USE <yourlogin>;
mysql> CREATE TABLE specimens (
-> specimen_id INTEGER NOT NULL
AUTO_INCREMENT PRIMARY KEY,
-> vehicle TINYTEXT,
-> dive INTEGER,
-> date DATE,
-> lat FLOAT,
-> lon FLOAT,
-> depth FLOAT,
-> notes TEXT
-> );
mysql> INSERT INTO specimens SET
-> vehicle='Tiburon',
-> dive=596,
-> date='2003-07-03',
-> lat=36.602,
-> lon=-122.375,
-> depth=1190,
-> notes='holotype';
mysql> INSERT INTO specimens SET
vehicle='Tiburon', dive=515,
date='2002-11-24', lat=36.7000,
lon=-122.0330, depth=1156.0,
notes='';
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO specimens SET
vehicle='Tiburon', dive=531,
date='2003-03-13', lat=24.3170,
lon=-109.2030, depth=1144.0,
notes='';
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO specimens SET
vehicle='Tiburon', dive=547,
date='2003-03-31', lat=24.2340,
lon=-109.6670, depth=1126.0,
notes='';
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO specimens SET vehicle='JSL
II', dive=1411,
date='1986-09-16', lat=39.9400,
lon=-70.2383, depth=518.0,
notes='paratype';
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO specimens SET vehicle='JSL
II', dive=930,
date='1984-08-18', lat=40.0838,
lon=-69.0502, depth=686.0,
notes='Youngbluth (1989)';
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO specimens SET vehicle='JSL
II', dive=3457,
date='2003-09-26', lat=40.2962,
lon=-68.1113, depth=862.0,
notes='Francesc Pages (personal communication)';
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO specimens SET
vehicle='Ventana', dive=1575,
date='1999-03-11', lat=36.7040,
lon=-122.0420, depth=767.0,
notes='';
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO specimens SET
vehicle='Ventana', dive=1777,
date='2000-06-16', lat=36.7100,
lon=-122.0450, depth=934.0,
notes='';
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO specimens SET
vehicle='Ventana', dive=2243,
date='2002-09-09', lat=36.7080,
lon=-122.0640, depth=1001.0,
notes='';
Query OK, 1 row affected (0.00 sec)
mysql> specimens;
mysql> SELECT * FROM specimens;
CREATE TABLE ctd (
-> ctd_id INTEGER NOT NULL
AUTO_INCREMENT PRIMARY KEY,
-> clock DATETIME,
-> vehicle TINYTEXT,
-> dive INTEGER,
-> depth FLOAT,
-> temperature FLOAT,
-> salinity FLOAT,
-> oxygen FLOAT,
-> lat FLOAT,
-> lon FLOAT
-> );
You will need to edit the path to your
directory. This will allow you to read in files to the ctd
table.
mysql> LOAD DATA LOCAL INFILE
'~mmartin/pcfb/examples/ctd/Marrus_ctdTib515.txt' INTO TABLE ctd
FIELDS TERMINATED BY ',' IGNORE 1 LINES
(clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET
dive=515;
Query OK, 491 rows affected (0.02 sec)
Records: 491 Deleted: 0 Skipped: 0 Warnings: 0
mysql> LOAD DATA LOCAL INFILE
'~mmartin/pcfb/examples/ctd/Marrus_ctdTib531.txt' INTO TABLE ctd
FIELDS TERMINATED BY ',' IGNORE 1 LINES
(clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET
dive=531;
Query OK, 1348 rows affected, 1746 warnings (0.02 sec)
Records: 1348 Deleted: 0 Skipped: 0 Warnings: 1746
mysql> LOAD DATA LOCAL INFILE
'~mmartin/pcfb/examples/ctd/Marrus_ctdTib547.txt' INTO TABLE ctd
FIELDS TERMINATED BY ',' IGNORE 1 LINES
(clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET
dive=547;
Query OK, 486 rows affected, 3 warnings (0.01 sec)
Records: 486 Deleted: 0 Skipped: 0 Warnings: 3
mysql> LOAD DATA LOCAL INFILE
'~mmartin/pcfb/examples/ctd/Marrus_ctdTib596.txt' INTO TABLE ctd
FIELDS TERMINATED BY ',' IGNORE 1 LINES
(clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET
dive=596;
Query OK, 760 rows affected (0.01 sec)
Records: 760 Deleted: 0 Skipped: 0 Warnings: 0
mysql> LOAD DATA LOCAL INFILE
'~mmartin/pcfb/examples/ctd/Marrus_ctdVen1575.txt' INTO TABLE ctd
FIELDS TERMINATED BY ',' IGNORE 1 LINES
(clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET
dive=1575;
Query OK, 100 rows affected, 4 warnings (0.01 sec)
Records: 100 Deleted: 0 Skipped: 0 Warnings: 4
mysql> LOAD DATA LOCAL INFILE
'~mmartin/pcfb/examples/ctd/Marrus_ctdVen1777.txt' INTO TABLE ctd
FIELDS TERMINATED BY ',' IGNORE 1 LINES
(clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET
dive=1777;
Query OK, 210 rows affected (0.00 sec)
Records: 210 Deleted: 0 Skipped: 0 Warnings: 0
mysql> LOAD DATA LOCAL INFILE
'~mmartin/pcfb/examples/ctd/Marrus_ctdVen2243.txt' INTO TABLE ctd
FIELDS TERMINATED BY ',' IGNORE 1 LINES
(clock,vehicle,depth,temperature,salinity,oxygen,lat,lon) SET
dive=2243;
Query OK, 343 rows affected (0.01 sec)
Records: 343 Deleted: 0 Skipped: 0 Warnings: 0
This is going to be big
mysql> SELECT * FROM ctd;
Now for some experiments with
queries. You should make notes about what these do and feel
free to experiment.
mysql> UPDATE ctd SET vehicle='TIBURON' WHERE vehicle='tibr';
mysql> UPDATE ctd SET vehicle='VENTANA' WHERE vehicle='vnta';
mysql> SELECT COUNT(*) FROM specimens;
mysql> SELECT COUNT(*) FROM ctd;
mysql> SELECT vehicle,date FROM specimens;
mysql> SELECT DISTINCT vehicle FROM specimens;
mysql> SELECT vehicle,COUNT(*) FROM specimens GROUP BY vehicle;
mysql> SELECT vehicle,dive,COUNT(*) FROM ctd GROUP BY vehicle,
dive;
mysql> SELECT vehicle, AVG(depth) FROM specimens GROUP BY
vehicle;
mysql> SELECT specimens.vehicle, specimens.dive, MIN(ctd.depth),
-> ctd.temperature, ctd.salinity, ctd.oxygen
-> FROM ctd, specimens
-> WHERE specimens.notes="holotype"
-> AND ctd.vehicle=specimens.vehicle AND
ctd.dive=specimens.dive
-> AND ctd.depth >= specimens.depth;
Turn it in:
Go to the CS Homework Submission System: https://www.cs.csustan.edu/cshomework/
Choose: instructor Martin
Spring 2016
CS4950
Lab7
Upload your Lab7.txt file as prompted.