Threadbare Canvas Productions Online Journal of James Hogan the Web Developer

Handcrafting a MySQL Database

The natural progression for a developer of web applications is for them to eventually be able to create a solid persistent web application. Solid in that is is robust to scalability and persistent in that the data you create while using it gets stored on the cloud, as it were. Cloud computing is a new way of imagining how data will be handled online. Instead of your data residing on a single hard-drive on your home computer or on a web server somewhere your data will be shifting through the cloud as resources are negotiated automatically to handle the breath of demand for your content.

In this example I’m going to show you how to download, install, create and query a MySQL database. In a future post I’ll show how to connect a JSP page to a database and display some content onto a page. This will be the first steps to creating a Content Management System of sorts, but I’ll leave that for now.

So, First step should be to download MySQL server. if you’ve got a Microsoft computer then choose the download that corresponds to your country. After you’ve downloaded and installed everyting agreeing to everything along the way, you should get to the MySQL Server Instance Configuration Wizard:

It’s OK to keep hitting Next here until your asked for a password and choose ‘admin’.

Great, now you have it all installed and you are on your way to having a database up and running on your computer. What you need to do now is turn to your start menu and locate the MySQL command line client. This will allow you to start handcrafting a database:

Add the following line and press return

CREATE DATABASE helloworld;

Well, it does exactly what it says. It creates a database referenced with the name helloworld. Go ahead and execute this line now. MySQL will respond to you if all is well saying:

Query OK, 1 row affected (0.02 sec)

Now that we have our database we can start to have some fun. Next you need to tell MySQL that you want to use the database we’ve just created. So you can execute this command:

USE helloworld;

A this point you should have created a database and told MySQL that you want to use it. So now lets add a table to the database helloworld. Add and execute the following lines to create a table on helloworld name helloworld:

CREATE TABLE helloworld(
HELLO char(10) NOT NULL,
WORLD char(10) NOT NULL
)ENGINE InnoDB;

If all is ok MySQL should respond to you with the following screen:

Now that we’ve got our table created we can go ahead and stick some data in there like so:

INSERT INTO helloworld(HELLO, WORLD)
VALUES("Hello","World");

The previous two lines allow us to populate or table with the data “Hello” and “World”. The fun part comes when we attempt to see the fruits of our labor. Using the following line we can see the data we’ve just created returned by MySQL.

SELECT HELLO, WORLD
FROM helloworld;

Be Amazed you are now a budding database Developer. In a future post I will explain how we can connect to this database and retrieve our Hello and World and stick them into a JSP page. :)