MYSQL(CLASS 2)

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, BLOB
Complete table of all column types on mysql.com
3. Inserting Data (back to top)
Basic commands on mysql command line:
% mysql

mysql> USE wbluhm;

mysql> SELECT * FROM books;

mysql> INSERT INTO books
-> VALUES ("My Life", "Mickey Mouse", "Disney",
-> "Biography", "What can I say?", 9.95);

mysql> SELECT * FROM books;

mysql> DELETE FROM books WHERE title="My Life";

mysql> SELECT * FROM books;

mysql> exit;

%
Inserting data with .sql scripts:

SQL script example: populate_books.sql

-- populates the books table

INSERT INTO books
VALUES ("MySQL", "Paul DuBois", "New Riders", "MySQL",
"Excellent book, but doesn't cover Java API", 49.99);

INSERT INTO books
VALUES ("Beginning XML", "David Hunter", "Wrox", "XML",
"Well recommended, fairly comprehensive", 39.99);

INSERT INTO books
VALUES ("Java How to Program", "Paul Deitel", "Prentice Hall", "Java",
"Good textbook, extremely detailed", 68.00);

INSERT INTO books
VALUES ("Thinking in Java", "Bruce Eckel", "Prentice Hall", "Java",
"Well written, free on the web", 0.00);

INSERT INTO books
VALUES ("The Java Programming Language", "Ken Arnold", "Addison Wesley", "Java",
"Considered to be from the source", 37.95);

INSERT INTO books
VALUES ("Learning Perl", "Randal Schwartz", "O'Reilly", "Perl",
"Not a bad start", 29.95);

INSERT INTO books
VALUES ("Programming Perl", "Larry Wall", "O'Reilly", "Perl",
"Usually considered THE reference", 44.95);

INSERT INTO books
VALUES ("Effective Perl Programming", "Joseph Hall", "Addison Wesley", "Perl",
"Great tips, not for beginners", 34.95);

% mysql wbluhm < populate_books.sql

% mysql wbluhm

mysql> SELECT * FROM books;

mysql> DELETE FROM books;

mysql> SELECT * FROM books;

mysql> exit;

%
Inserting data from files

data file: books.data

MySQL Paul DuBois New Riders MySQL ... 49.99
Beginning XML David Hunter Wrox XML ... 39.99
...
Effective Perl Programming Joseph Hall Perl ... 34.95

% mysql

mysql> USE wbluhm

mysql> LOAD DATA INFILE '/users/science/wbluhm/mysql/books.data'
-> INTO TABLE books
-> FIELDS TERMINATED BY '\t'
-> LINES TERMINATED BY '\n'
-> IGNORE 0 LINES
-> (title, author, publisher, topic, comment, price);

mysql> SELECT * FROM books;

mysql> exit;

%
4. Querying Data (back to top)
Basic commands on mysql command line:
% mysql

mysql> USE wbluhm;

mysql> SELECT * FROM books;

mysql> SELECT title, price
-> FROM books
-> ;

mysql> SELECT title, author
-> FROM books
-> WHERE topic="Java";

mysql> SELECT title, author, price
-> FROM books
-> WHERE topic="Java"
-> ORDER BY price;

mysql> SELECT title, author, price
-> FROM books
-> WHERE topic="Java"
-> ORDER BY price DESC;

mysql> USE samp_db;

mysql> SHOW TABLES;

mysql> DESCRIBE president;

mysql> SELECT last_name, first_name, birth
-> FROM president
-> WHERE death IS NULL
-> ORDER BY last_name;

mysql> DESCRIBE student;

mysql> DESCRIBE absence;

mysql> SELECT student.name, absence.date
-> FROM student JOIN absence
-> WHERE student.student_id = absence.student_id;

mysql> exit;

%
Querying data with .sql scripts:

SQL script example: select_java_books.sql

SELECT title, author, price FROM books
WHERE topic="Java"
ORDER BY price;

% mysql wbluhm < select_java_books.sql

% mysql -t wbluhm < select_java_books.sql