Think mySQL!

Create the Database File

The first step is to create the database file. Start the mySQL client and type the following command:

mysql> create database guestdb;
Query OK, 1 row affected (0.00 sec)

mysql>

The command creates the the database, guestdb. If successful, you should receive a response like the one shown above.

After the database is created, we must select it to continue with our database building.

mysql> use guestdb;
Database changed
mysql>

Now with our database selected, we are ready for the next step.

Create a Table

Next, you need to create a database table to hold your data. The SQL here defines a table. Below is the command and the output from the command. The database created below will be used in a web guestbook application.

mysql> create table guestTbl
-> (msgID int not null primary key auto_increment,
-> name varchar(30),
-> email varchar(30),
-> msgFrom varchar(30),
-> msgDate timestamp(14),
-> msgBody text);
Query OK, 0 rows affected (0.11 sec)

mysql>

In this example, the create command creates a table named guestTbl. The table has six fields described below.

msgID - a unique number assigned to each entry. The field hold an integer value that is automatically incremented each time an entry is added.

name - a 30 character field holding the name of the guest.

email - a 30 character field holding the email address of the guest.

msgFrom - a 30 character field holding the location of the guest.

msgDate - the date and time the entry is added to the database.

msgBody - a text field holding a text message entered by the guest.

Our next step is to add data to the table.

Add data to the Table

Now we need to add some sample data to the table, so we can actually run some queries against it.

mysql> insert into guestTbl
-> (msgID, name, email, msgFrom, msgDate, msgBody) values
-> (NULL, 'Test User1', 'testuser@test.com','Smallville USA',now(),
-> 'This is the body for message 1.');
Query OK, 1 row affected (0.27 sec)

mysql>

The above command adds one row to the database. If we repeat this process for three other users, we have enough data to perform a query on the table.

Query the Database

Now we can query our database. To retrieve all the data in the table, perform the following command.

mysql> select * from guestTbl;
+-------+------------+--------------------+----------------+
----------------+---------------------------------+
| msgID | name | email |
msgFrom | msgDate | msgBody |
+-------+------------+--------------------+----------------+
----------------+---------------------------------+
| 1 | Test User1 | testuser@test.com |
Smallville USA | 20001117154626 | This is the body for message 1. |
| 2 | Test User2 | testuser2@test.com |
Smalltown USA | 20001117155043 | This is the body for message 2. |
| 3 | Test User3 | testuser3@test.com |
Smallcity CA | 20001117155201 | This is the body for message 3. |
| 4 | Test User4 | testuser4@test.com |
LittleTown CO | 20001117155319 | This is the body for message 4. |
+-------+------------+--------------------+----------------+
----------------+---------------------------------+
4 rows in set (0.06 sec)

mysql>

First off, you'll notice that if the data does not fit on a line, it is wrapped. All the data entered for the four test users is there. We can get cleaner looking output by selecting a couple of fields.

For example:

mysql> select name,email,msgFrom from guestTbl;
+------------+--------------------+----------------+
| name       | email              | msgFrom        |
+------------+--------------------+----------------+
| Test User1 | testuser@test.com  | Smallville USA |
| Test User2 | testuser2@test.com | Smalltown USA |
| Test User3 | testuser3@test.com | Smallcity CA |
| Test User4 | testuser4@test.com | LittleTown CO |
+------------+--------------------+----------------+

4 rows in set (0.05 sec)

mysql>

Quite a bit more readable.