Using MySQL

This tutorial needs to be updated for Thor. Thor uses WIMP instead of LAMP and this tutorial assumes a LAMP environment.

WIMP = Windows IIS MySQL PHP
LAMP = Linux Apache MySQL PHP

This tutorial is for students at East Norfolk Sixth Form College (ENSFC). Anyone else can use the tutorial but you will need access to your own MySQL server. At college you must have a user account on the Thor server. Alternatively install MySQL and set up an account on your own server.
At ENSFC, to get an account, use the form on the Thor home page.
You can run this tutorial at home if you have installed MySQL on a Windows machine or if you have access to Linux or FreeBSD.
This step is needed to make an SSH connection to a remote server. Skip this if you are using your own WIndows machine.
Run an SSH client program to log into your account.
SSH is a secure version of Telnet. It stands for Secure Shell.
Our SSH client is called PuTTY.
Enter the following information to run the SSH client. You can download PuTTY for home use.

NOTE: Use your own MySQL server IP address. The ENSFC server Thor has this address 10.6.0.16.
Log into your MySQL account using your own username and password. On Windows, run mysql.exe.
If you get a security warning, click Yes.
There can be a delay between typing your username and being asked for your password. This delay effectively defeats automatic password cracking programs.
When you type your password, for your security, it will not appear on screen.
To get access to the MySQL server type this command and press Enter. (NB use your own user name and password.)

mysql -u nbauers -p (This is the same on Linux and Windows)
The first time you use MySQL, you may need to create a database.
Do this once only.
Name your database like this ...
nbauers_WhatEverYouLike
Replace nbauers with your own username. Replace WhatEverYouLike with any meaningful name.

Type these commands and press Enter. Remember to replace nbauers with your user name for example "fbloggs".

show databases;                      Find out what databases already exist
create database nbauers_test;   Creates a database called nbauers
drop database nbauers_test;     Deletes an entire database - be careful with this command!
create database nbauers_test;
Once you have created your database, type this command to make use of it.

use nbauers_test;

Here is an example for the user, nbauers and the database called nbauers.
To see what databases have been created, use this command.

show databases;
Here is an example of the code used to create a table. Make your own version of this using NOTEPAD. Copy the SQL from notepad and paste it into your PuTTY window (with a right mouse click). By using NOTEPAD, if there is an error in your SQL, you only have to fix the error and not re-type it all.
Once you have created table/s, you can see them by typing

show tables;
Here is the code to insert a record into the table, above. Once again make your own version of this using NOTEPAD. Copy the SQL from notepad and paste it into your PuTTY window (with a right mouse click). If there is an error in your SQL, you only have to fix the error and not re-type it all.
Here is the code for a complete MySQL session including the SQL needed to display your table contents.
From now on you can use standard SQL commands.
There are good tutorial sites for learnng SQL.

http://www.w3schools.com/sql/ and http://sqlzoo.net/

Hints

Open a Notepad window and type all your SQL statements into it.
This gives you a permanent record of the SQL statements you used.
Remember to save this document.
To run an SQL statement, highlight it in the notepad window and use CTRL+C to copy it.
Then paste it into the MySQL monitor window with a right mouse click.
Our MySQL server is not guaranteed to be secure.
This means that you need to back up all your SQL statements in your
college network account so you can re-create your work quickly
after any "accidents" on the server. See hint 1.

Home | w3schools | SqlZoo

Here is a complete script where three tables are created. The scenario is a video hire shop.

CUSTOMER-----====< LOAN >====-----FILM

Tables are created.
They are populated with data.
A SELECT query is then run on the three tables. Who has borrowed whch film?
This script was run by nbauers.
You should replace references to nbauers with your own username.
You can copy from this script by highlighting and using CRTL+C.
You can paste into PuTTY by right clicking the mouse.

CREATE DATABASE nbauers;

USE nbauers;

CREATE TABLE customer
(
customer_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(5),
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL,
date_of_birth DATE
);

insert into customer
(
title, last_name, first_name, date_of_birth
)
VALUES
(
"Mr", "Bloggs", "Fred", "1997-12-22"
);

SELECT * FROM customer;

create table film
(
film_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
film_title VARCHAR(50) NOT NULL
);

show tables;

insert into film
(
film_title
)
VALUES
(
"Lord of the Rings"
);

create table loan
(
customer_id MEDIUMINT UNSIGNED NOT NULL,
film_id MEDIUMINT UNSIGNED NOT NULL
);

show tables;

insert into loan
(
customer_id, film_id
)
VALUES
(
"1", "1"
);

insert into customer
(
title, last_name, first_name, date_of_birth
)
VALUES
(
"Ms", "Smith", "Sally", "1982-11-18"
);

insert into customer
(
title, last_name, first_name, date_of_birth
)
VALUES
(
"Mrs", "Zonk", "Zebadee", "1950-10-14"
);

insert into customer
(
title, last_name, first_name, date_of_birth
)
VALUES
(
"Mr", "Jones", "Sam", "1947-04-27"
);

insert into film
(
film_title
)
VALUES
(
"Lord of the Rings"
);

insert into film
(
film_title
)
VALUES
(
"War and Peace"
);

insert into film
(
film_title
)
VALUES
(
"ScoobyDoo"
);

SELECT * FROM film;

insert into loan
(
customer_id, film_id
)
VALUES
(
"2", "3"
);

insert into loan
(
customer_id, film_id
)
VALUES
(
"3", "2"
);

insert into loan
(
customer_id, film_id
)
VALUES
(
"4", "4"
);

SELECT * FROM loan;

SELECT
customer.first_name,
customer.last_name,
film.film_title
FROM
customer, loan, film
WHERE
customer.customer_id = loan.customer_id
AND
loan.film_id = film.film_id;