SQL-1

Accessing MySQL on the command line
log into your workstation
edit .alias in vi
% vi .alias
enter the following in vi:
alias mysql /users/science/wilfred/local/mysql/bin/mysql
save and quit vi editor
:wq
(The last three steps need to be performed only once.)
Type
% source .alias
% mysql -h multivac
Or
log into multivac (used to be spirit)
ssh multivac
and type:
% source .alias
% mysql
The database that you can use is the same one as your username, plus samp_db (currently, more may be granted later).
You may create tables, drop tables, etc. inside your own database. samp_db or others which you may browse may not be deleted, or dropped.
Accessing MySQL from any machine
Your_Shell% mysql -h multivac.sdsc.edu -u wbluhm --password="" wbluhm
(This assumes that you have the client installed on your machine and the PATH set properly.)
Executing MySQL scripts on the command line (outside of MySQL)
% mysql wbluhm < myscript.sql

% mysql -t wbluhm < myscript.sql
Accessing MySQL from Perl or Java APIs

Perl Example: JavaBooks.pl
# queries the books table in database wbluhm on multivac

use DBI;
use strict;

my ($dsn) = "DBI:mysql:wbluhm:multivac.sdsc.edu"; # data source name
my ($user_name) = "wbluhm"; # user name
my ($password) = ""; # password
my ($dbh, $sth); # database and statement handles
my (@ary); # array for rows returned by query

# connect to database
$dbh = DBI->connect ($dsn, $user_name, $password, { RaiseError => 1 });

# issue query
my $query = qq{ SELECT title, author, comment
FROM books
WHERE topic="Java" };
$sth = $dbh->prepare ( $query );
$sth->execute ();

# read results of query, then clean up
while (@ary = $sth->fetchrow_array ())
{
print join (" ", @ary), "\n";
}
$sth->finish ();

$dbh->disconnect ();
exit (0);

Java Example: JavaBooks.java
// queries the books table in database wbluhm on multivac

import java.sql.*;
public class JavaBooks {
public static void main(String[] args) {

try {
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
}
catch (Exception E) {
System.err.println("Unable to load driver");
E.printStackTrace();
}

try {
Connection C = DriverManager.getConnection(
"jdbc:mysql://multivac.sdsc.edu/wbluhm","wbluhm","");
Statement Stmt = C.createStatement();

ResultSet RS = Stmt.executeQuery
("SELECT title, author, comment " +
" FROM books WHERE topic=\"Java\"");

while (RS.next()) {
System.out.print("\"" + RS.getString(1) + "\"");
System.out.print(" by " + RS.getString(2));
System.out.println(": " + RS.getString(3));
}
C.close();
RS.close();
Stmt.close();
}
catch (SQLException E) {
System.out.println("SQLException: " + E.getMessage());
System.out.println("SQLState: " + E.getSQLState());
System.out.println("VendorError: " + E.getErrorCode());
}
}
}

2. Creating Tables (back to top)
Basic commands on mysql command line:
% mysql

mysql> SHOW DATABASES;

mysql> USE wbluhm;

mysql> SHOW TABLES;

mysql> CREATE TABLE dummy ( first_name CHAR(20) , last_name CHAR(20) );

mysql> SHOW TABLES;

mysql> DESCRIBE dummy;

mysql> DROP TABLE dummy;

mysql> SHOW TABLES;

mysql> exit;

%
Creating tables from .sql scripts

SQL script example: create_books.sql

-- creates the table books in database wbluhm

CREATE TABLE books
(
title CHAR(50) NOT NULL,
author CHAR(30) NOT NULL,
publisher CHAR(30),
topic CHAR(20),
comment CHAR(100),
price FLOAT
)

% mysql wbluhm < create_books.sql
For more examples, see some of the scripts from the samp_db database in
/users/science/wbluhm/mysql/create_*.sql
Column types
Some basic column types: INTEGER, FLOAT, CHAR, DATE, TIME, BLOBComplete table of all column types on mysql.