Main MySQL Page

MySQL Introduction

Quick Links

• Creating a Database
• Creating a Table
• Entering Data Specifications

MySQL uses standard SQL (Structured Query Language) commands. It is possible to run the mysql program to create a database and its tables and then to populate the tables with data. However, an excellent web-based tool called phpMyAdmin has been developed to facilitate these operations. We will make use of phpMyAdmin to get started.

Since we need some data to work with, we will use some baseball stats from the 1960 Pittsburgh Pirates. The following table contains information about four of the players.

1960 Pittsburgh Pirates
Name	AB	H	BA
Smoky Burgess	337	99	.294
Roberto Clemente	570	179	.314
Bill Mazeroski	539	147	.273
Bob Skinner	571	156	.273

Creating a Database

MySQL allows you to organize your data into tables which are stored in a database. The first step is to create the database.

In your browser, call up the phpMyAdmin page.

In the "Create new database" text box, type in "pirates". It is common practice to name the databases with lowercase letters. For collation, choose latin1_general_cs. Then click on the Create button. phpMyAdmin will process the request, communicating with the MySQL server. A new screen will be displayed which shows the SQL query which was just executed:

CREATE DATABASE 'pirates';

Creating a Table

To start the process of creating a table with phpMyAdmin for our new database, we must make two choices: a name for the table and how many fields to use. For the name of the table, let us use "1960", the year for which the statistics were collected.

For the data above, we need to decide how many fields to use to store the information for each of the players. We could create a field for each of the four columns in the table above. However, if the full name is assigned to a single field with the normal first name/last name order, sorting on the name becomes inordinately difficult. A more useful organization of the data would be to have the two parts of a player's name each placed in its own field. Then a sort could be performed on either field.

We also should have what is know as a "primary key". This field would uniquely identify each row so that there would be no duplicates. This field is typically called the "id" field.

So let us specify 6 as the number of fields and click on "Go".

Entering the Data Specifications

You are now presented with a new page which consists of a table, each line of which permits you to specify information about a field: the name, the data type to be used, and the maximum number of characters to be allowed in the field, among other things.

We will not specify information in all columns for all of the fields. This will result in a non-optimum table definition, but you will learn some important concepts.

The table below shows some of the information we will specify.

Let's start with the ID field. Each record needs to have a unique value in this field. The easiest way is to start with 1 for the first record, then 2 for the second, etc. Since we are only including members of the 1960 Pittsburgh Pirates, we could get by with just two digits. We will specify three to allow for possible later expansion of the database and table. So, let's enter the specifications:

1. Type in id for the first Field.
2. Use the pulldown menu to choose INT for the data type.
3. Type in 3 for the field length.
4. Since we want each record to have an id number that is not null, leave the Null column pulldown menu alone.
5. In the Extra column use the pulldown menu to highlight "auto_increment", which will cause mysql to start with 1 as the value for the first record and to add one to the value for each subsequent record.
6. Next to the Extra column is a column of radio buttons under an icon which contains a picture of a key. Clicking the radio button will cause mysql to ensure that the data for a record's id is always unique.

There are no spaces allowed in field names. Since we need to distinguish between the first and last names, we will use lname as the field name for the former and lname for the latter. Because not all human names are the same length like postal codes we will use VARCHAR as the variable type. Last names tend to be longer than first names, so we will enter different lengths, 20 for lname and 10 for fname.

The next two columns in our table contain integer values. For these we will accept the default length.

The final table column holds decimal values. For these we will use the FLOAT data type.

After filling in the form, click on the Save button.

You should be presented with a display of the just-executed SQL query:

CREATE TABLE `1960` (
`id` INT( 3 ) NOT NULL AUTO_INCREMENT ,
`lname` VARCHAR( 20 ) NOT NULL ,
`fname` VARCHAR( 10 ) NOT NULL ,
`ab` INT,
`hits` INT,
`ave` FLOAT,
PRIMARY KEY ( `id` )
) TYPE = MYISAM ;

You have successfully created your first database, pirates, containing a single table, 1960. However, the table does not yet contain any data.

Using the "Insert" option, add the data as given in the HTML table above, so that you end up with three records in the order given. It does not matter that the data are not sorted on any field other than the id field. We will use PHP requests to the server to present the data the way we would like.