SQL-3

Basic MySQL Tutorial
Table of Contents
Accessing MySQL
Creating Tables
Inserting Data
Querying Data
Practice
References

1. Accessing MySQL (back to top)
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.com