Spring Semester 2007
| |
Spring Semester 2007 |
1. Here's how you can copy tables from someone else (if the tables exist and are good):
2. Second, here are two examples of scripts that extract info from the database.Script started on Tue Aug 03 09:10:06 2004 burrowww.cs.indiana.edu% mysql -ua348 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11268 to server version: 4.0.18-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql> use a348 Database changed mysql> show tables like '%ninliu%HW%'; +------------------------------+ | Tables_in_a348 (%ninliu%HW%) | +------------------------------+ | ninliu_HW3_enrollment | | ninliu_HW3_matches | | ninliu_HW3_participation | | ninliu_HW3_players | | ninliu_HW3_spectators | | ninliu_HW3_venues | +------------------------------+ 6 rows in set (0.04 sec) mysql> show tables like 'lbird%'; +-------------------------+ | Tables_in_a348 (lbird%) | +-------------------------+ | lbird_board | | lbird_message | | lbird_newtable | | lbird_person | +-------------------------+ 4 rows in set (0.04 sec) mysql> describe ninliu_HW3_enrollment; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | matchID | varchar(30) | YES | | NULL | | | tickets | decimal(4,0) | YES | | NULL | | | universityID | varchar(25) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> create table lbird_HW3_enrollment select * from ninliu_HW3_enrollment; Query OK, 22 rows affected (0.04 sec) Records: 22 Duplicates: 0 Warnings: 0 mysql> show tables like 'lbird%'; +-------------------------+ | Tables_in_a348 (lbird%) | +-------------------------+ | lbird_HW3_enrollment | | lbird_board | | lbird_message | | lbird_newtable | | lbird_person | +-------------------------+ 5 rows in set (0.05 sec) mysql> describe ninliu_HW3_enrollment; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | matchID | varchar(30) | YES | | NULL | | | tickets | decimal(4,0) | YES | | NULL | | | universityID | varchar(25) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table lbird add key (matchID, universityID); Query OK, 22 rows affected (0.07 sec) Records: 22 Duplicates: 0 Warnings: 0 mysql> describe lbird_HW3_enrollment; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | matchID | varchar(30) | YES | MUL | NULL | | | tickets | decimal(4,0) | YES | | NULL | | | universityID | varchar(25) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table lbird_HW3_enrollment add primary key (matchID, universityID); Query OK, 22 rows affected (0.07 sec) Records: 22 Duplicates: 0 Warnings: 0 mysql> describe lbird_HW3_enrollment; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | matchID | varchar(30) | | PRI | | | | tickets | decimal(4,0) | YES | | NULL | | | universityID | varchar(25) | | PRI | | | +--------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> select * from lbird_HW3_enrollment; +---------+---------+----------------+ | matchID | tickets | universityID | +---------+---------+----------------+ | 1 | 120 | Illinois | | 1 | 100 | IU Bloomington | | 2 | 140 | Illinois | | 2 | 60 | Purdue | | 3 | 20 | U of Minnesota | | 3 | 220 | Michigan | | 4 | 300 | Michigan | | 4 | 80 | U of Minnesota | | 5 | 180 | Iowa | | 6 | 120 | Purdue | | 6 | 210 | IU Bloomington | | 7 | 150 | IU Bloomington | | 7 | 200 | Iowa | | 8 | 180 | Illinois | | 9 | 120 | U of Minnesota | | 9 | 40 | Illinois | | 10 | 200 | Purdue | | 11 | 120 | Iowa | | 11 | 80 | Purdue | | 12 | 60 | IU Bloomington | | 12 | 60 | U of Minnesota | | 12 | 240 | Michigan | +---------+---------+----------------+ 22 rows in set (0.00 sec) mysql> exit Bye burrowww.cs.indiana.edu% exit burrowww.cs.indiana.edu% script done on Tue Aug 03 09:14:11 2004
The first script is available here, the code is listed below.
#!/usr/bin/perl
use DBI;
use CGI;
$q = new CGI;
print "Content-type: text/html\n\n";
$DB = "DBI:mysql:a348"; # data source name (database)
$username = "a348"; # username
$password = "a348AG"; # password
$dbh = DBI->connect($DB, $username, $password, {PrintError => 0})
|| die "Couldn't open database: ", $DBI::errstr;
$query = "select * from dgerman_HW3_players";
$sth = $dbh->prepare($query) || die $dbh->errstr;
$sth->execute() || die $sth->errstr;
print qq{
<table border cellpadding=2>
<tr><th> Username
<th> First Name
<th> Last Name
<th> Picture
<th> Address
<th> City
<th> State
<th> Player rate per match
};
while (my $row = $sth->fetch) {
my(@values) = @$row;
print "<tr>";
foreach $value (@values) {
print "<td>$value";
}
}
print "</table>";
$sth->finish;
#!/usr/bin/perl
use DBI;
use CGI;
$q = new CGI;
print "Content-type: text/html\n\n";
$DB = "DBI:mysql:a348"; # data source name (database)
$username = "a348"; # username
$password = "a348AG"; # password
$dbh = DBI->connect($DB, $username, $password, {PrintError => 0})
|| die "Couldn't open database: ", $DBI::errstr;
$query = "select * from dgerman_HW3_players";
$sth = $dbh->prepare($query) || die $dbh->errstr;
$sth->execute() || die $sth->errstr;
print qq{
<table border cellpadding=2>
<tr>
<th> First Name
<th> Last Name
<th> City
<th> Player rate per match
};
while (my $row = $sth->fetch) {
my(@values) = @$row;
print "<tr>";
foreach $i (1, 2, 5, 7) {
print "<td>", $values[$i];
}
}
print "</table>";
$sth->finish; #!/usr/bin/perl
use DBI;
$DB = "DBI:mysql:a348"; # data source name (database)
$username = "a348"; # username
$password = "a348AG"; # password
$dbh = DBI->connect($DB,
$username,
$password, {PrintError => 0}) ||
die "Couldn't open database: ", $DBI::errstr;
print "I have opened the database...\n";
$query = qq{
create table dgerman_HW3_players (
username varchar(8) primary key,
firstName varchar(20),
lastName varchar(20),
picture varchar(120),
address varchar(120),
city varchar(30),
state varchar(2),
playerRatePerMatch
decimal(12, 2)
)
};
print " Getting ready to create dgerman_players... ";
$dbh->do($query) || die $dbh->errstr;
print "done.\n Getting ready to populate tables with data. \n";
open (INPUT, "players_data");
$line = <INPUT>;
@names = split(/,/, $line);
while ($line = <INPUT>) {
($uname, $first, $last, $pic, $addr, $city, $st, $rate) =
split(/,/, $line, 8);
$st =~ s/\s//g;
chop($rate);
$query = qq{
insert into dgerman_HW3_players values
('$uname',
'$first',
'$last',
'$pic',
'$addr',
'$city',
'$st',
$rate
)
};
# print $query, "\n";
$dbh->do($query) || die $dbh->errstr;
}
close(INPUT); Here's what the database looks like now (and two queries):burrowww.cs.indiana.edu% ls -l total 6 -rw-r--r-- 1 dgerman faculty 704 Feb 19 11:50 players_data -rwx------ 1 dgerman faculty 2438 Feb 19 11:50 script burrowww.cs.indiana.edu% ./script I have opened the database... Getting ready to create dgerman_players... done. Getting ready to populate tables with data. burrowww.cs.indiana.edu% cat players_data username, firstName, lastName, picture , address , city , state, playerRatePerMatch cbarkley, Charles , Barkley , some URL, Sir Charles Avenue , Phoenix , AZ , 450.00 lbird , Larry , Bird , some URL, Freedom Lick Rd. , Naples , FL , 350.00 mjordan , Michael , Jordan , some URL, Jordan Air Way , Chicago , IL , 500.00 rmiller , Reggie , Miller , some URL, Miller Time Road , Indianapolis, IN , 150.00 tduncan , Tim , Duncan , some URL, Duncan at the Tim , Houston , TX , 210.00 tkukoc , Toni , Kukoc , some URL, Petar Zrinski Street, New Zagreb , PA , 50.00 burrowww.cs.indiana.edu%
mysql> describe dgerman_HW3_players; +--------------------+---------------+------+-----+---------+-------+---------------------------------+ | Field | Type | Null | Key | Default | Extra | Privileges | +--------------------+---------------+------+-----+---------+-------+---------------------------------+ | username | varchar(8) | | PRI | | | select,insert,update,references | | firstName | varchar(20) | YES | | NULL | | select,insert,update,references | | lastName | varchar(20) | YES | | NULL | | select,insert,update,references | | picture | varchar(120) | YES | | NULL | | select,insert,update,references | | address | varchar(120) | YES | | NULL | | select,insert,update,references | | city | varchar(30) | YES | | NULL | | select,insert,update,references | | state | char(2) | YES | | NULL | | select,insert,update,references | | playerRatePerMatch | decimal(12,2) | YES | | NULL | | select,insert,update,references | +--------------------+---------------+------+-----+---------+-------+---------------------------------+ 8 rows in set (0.00 sec) mysql> select firstName, lastName, city, playerRatePerMatch from dgerman_HW3_players; +-----------+----------+---------------+--------------------+ | firstName | lastName | city | playerRatePerMatch | +-----------+----------+---------------+--------------------+ | Charles | Barkley | Phoenix | 450.00 | | Larry | Bird | Naples | 350.00 | | Michael | Jordan | Chicago | 500.00 | | Reggie | Miller | Indianapolis | 150.00 | | Tim | Duncan | Houston | 210.00 | | Toni | Kukoc | New Zagreb | 50.00 | +-----------+----------+---------------+--------------------+ 6 rows in set (0.00 sec) mysql> select * from dgerman_HW3_players; +----------+-----------+----------+-----------+-----------------------+---------------+-------+--------------------+ | username | firstName | lastName | picture | address | city | state | playerRatePerMatch | +----------+-----------+----------+-----------+-----------------------+---------------+-------+--------------------+ | cbarkley | Charles | Barkley | some URL | Sir Charles Avenue | Phoenix | AZ | 450.00 | | lbird | Larry | Bird | some URL | Freedom Lick Rd. | Naples | FL | 350.00 | | mjordan | Michael | Jordan | some URL | Jordan Air Way | Chicago | IL | 500.00 | | rmiller | Reggie | Miller | some URL | Miller Time Road | Indianapolis | IN | 150.00 | | tduncan | Tim | Duncan | some URL | Duncan at the Tim | Houston | TX | 210.00 | | tkukoc | Toni | Kukoc | some URL | Petar Zrinski Street | New Zagreb | PA | 50.00 | +----------+-----------+----------+-----------+-----------------------+---------------+-------+--------------------+ 6 rows in set (0.01 sec) mysql>