SQL examples

• Complete mysql documentation at mysql.com
• Mysql tuturial at mysql.com
• Joel's mysql/DBI install guide

• show databases;
  shows all available databases on your system
• create database NAME;
  creates a new database called NAME
• use NAME;
  switches to the NAME database as the current database
• show tables;
  lists all the tables in the current database
• desc TABLE;
  shows the table defininition for TABLE (desc stands for "describe")

SQL command examples:

# show the number of rows in the table
select count(1) from phonebook;

# returns all columns and rows from the phonebook table
select * from phonebook;

# returns the name and phone columns for all rows from the phonebook table
select name, phone from phonebook;

# returns the name column from the phonebook table where id=2
# (if id is the primary key then only one row is returned--why???)
select name from phonebook where id=2;

# returns all rows from the phonebook table where the phone value is '555-1212'
# and the city value is 'Seattle'
select name, phone from phonebook where phone='555-1212' and city='Seattle';

# returns the name column for all rows from the phonebook table where the 
# name value starts with 'Joe'.  That is, 'Joe', 'Joel', and 'Joel Grow'
# all match.  the % is a wildcard.  
select name from phonebook where name like 'Joe%';

# insert a new row into the phonebook table
insert into phonebook (name, phone) values ('Igor Stravinsky', '1-800-789-9878');

# update row 7 in the table, setting the name column to 'Fred'
update phonebook set name='Fred' where id=7;

# update row 3 in the table, setting the name column to 'Sarah' and
# the city column to 'Seattle'
update phonebook set name='Sarah', city='Seattle' where id=3;

# update ALL ROWS in the table to have 'Portland' for their city
update phonebook set city='Portland';

# delete row 4 from the phonebook (be careful with this command!)
delete from phonebook where id=4;

mysql> create table phonebook (id int(6) primary key auto_increment, 
name varchar(50), phone varchar(25));

Then type "show tables;" again to make sure the new table shows up. Now try "desc phonebook" (remember "desc" means describe--you can always type out "describe" if you prefer that) to see your new table definition.
Now insert a few rows of data:

mysql> insert into phonebook (name, phone) values ('Wilma Flintstone', '1-800-987-6543');
mysql> insert into phonebook (name, phone) values ('Barney Rubble', '1-800-987-1234');

#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect('dbi:mysql:mydb:localhost', 'username', 'password')
             or die "Can't connect to db: " . DBI::errstr;

my $sql =<<End_of_SQL;
SELECT name, phone
FROM phonebook
End_of_SQL

my $sth = $dbh->prepare($sql) or die "Can't prepare: " . $dbh->errstr;

$sth->execute or die "Error with select: " . $sth->errstr;

while (my $rh_row = $sth->fetchrow_hashref) {
    my $name  = $rh_row->{name};
    my $phone = $rh_row->{phone};

    print "Name: $name	Phone: $phone
";
}

$dbh->disconnect;