Before tackling the more advanced features, here's a quick review of MySQL usage. Pretend you've just landed a new client, a car dealership. The manager at the dealership wants you to create a web- site that will help them keep track of their car inventories. No problem, you say, and you sit down and begin planning out their web application.

During your planning, you come up with a simple multi-table database scheme to hold the data for their car lots. For the purposes of these exercises, you've decided to create database tables that resemble Figure 1.
What you've created is a very simple database structure that involves four tables. In the New_Vehicles table, you'll be storing data for, obviously enough, new cars. Properties such as mileage, price, color, and description can be specified for each car in stock. The Used_Vehicles table has a nearly identical structure to New_Vehicles, but is designed to hold records for, as you might guess, used cars. The Used_Vehicles table has a couple additional columns provided to indicate whether the vehicle is certified or has a warranty, and a column for mileage. The last two tables, Make and Model, store common make and model designations for the various brands the dealer sells.

Creating the Databases

  The first thing you're going to need to do is create the database and tables in MySQL. Begin by starting  up your MySQL client at the command line, or if you have another program you feel more comfortable  with, and can enter MySQL commands directly, feel free to use that.

      shell$ mysql
  This will bring up the MySQL monitor, which should look similar to the following (if you're using the  command-line client):

     Welcome to the MySQL monitor. Commands end with ; or g.
      Your MySQL connection id is 2 to server version: 4.1.11-log
      Type `help;' or `h' for help. Type `c' to clear the buffer.

      mysql>
 

  In many situations, you'll need to use a login name and password to access the MySQL client. To do so,  just add -u username -p at the end of the mysql command, and MySQL will prompt you for the password, like so:

      shell$ mysql -u username -p

Using the MySQL command-line client is a great way to become familiar with most of the commands and functionality of MySQL, which often times might be automated in a GUI client.

Enter the following commands to create the database, and call it "VehicleInventory":

    mysql> CREATE DATABASE VehicleInventory;

Now that you've created the database, you need to set it as the active database for the remainder of your queries:

    mysql> USE VehicleInventory;

 Next, create the tables for your database. The basic SQL statement to create a table in MySQL is:

     CREATE TABLE tablename (column_definitions);
 

 To create the four vehicle information tables, you can use the following:
     mysql> CREATE TABLE Makes (make_id int PRIMARY KEY AUTO_INCREMENT, make_name
     varchar(100) );
     mysql> CREATE TABLE Models (model_id int PRIMARY KEY AUTO_INCREMENT, model_name
     varchar(200), make_id int );
     mysql> CREATE TABLE New_Vehicles (vehicle_id int PRIMARY KEY AUTO_INCREMENT,
     model_id int, modelyear int, price decimal(10,2), color varchar(200), description  text );
     mysql> CREATE TABLE Used_Vehicles (vehicle_id int PRIMARY KEY AUTO_INCREMENT,
     model_id int, modelyear int, mileage int, price decimal(10,2), color varchar(200),   certified tinyint, warranty tinyint, description text );
 

 In a nutshell, you simply use the MySQL CREATE TABLE command, followed by the name of the table, and then list the column names and types in parentheses. You'll also note that an auto-incrementing primary key is added to each table, to help you uniquely identify rows after they are added.

Adding Information

 Now that you've got your tables created, you need to add some data to them. For that, you're going to use the INSERT statement. The MySQL INSERT command typically follows this format:

    INSERT INTO tablename (column_list) VALUES (new_values);

 Start by adding some initial data to the Makes table:

    INSERT INTO Makes (make_id, make_name) VALUES (1, `Ford'), (2, `Honda'), (3,`Volkswagen'),
(4, `Toyota'), (5, `Chevrolet');
 

 Here, the basic SQL INSERT syntax is used, but with an added twist. In MySQL, you can specify a comma-delimited set of column values, instead of just one set, to populate multiple rows at once. Normally, you might leave out the make_id column when inserting into the Makes table -- the auto- incremented ID would automatically assign them a number. In this case, you specify the ID so the rows you'll soon add to other tables will have valid foreign keys.

  Use the following to add data to the rest of the tables (remember you can download this code at  www.wrox.com and save yourself some typing if you'd like):

      INSERT INTO Models (model_id, model_name, make_id) VALUES (1, `Explorer', 1), (2,`Accord', 2), (3, `Golf', 3), (4, `Tacoma', 4), (5, `Corvette', 5);
      INSERT INTO New_Vehicles (model_id, modelyear, color, description) VALUES      (1, 2005, `Dark Blue', `Popular SUV; great for moving people or cargo across town,  or wherever your adventures may take you.'),  (NULL, 2004, `Graphite Pearl', `Last of the 2004 stock! Must sell! Loaded, all  options!'),      (4, 2005, `Radiant Red', `Come drive this terrific truck! 4WD, extended cab, many   options.'),      (5, 2005, `Daytona Sunset Orange Metallic', `The all new C6 Corvette, the next generation of classic American sportscars. 400HP V8; Very fast!'),      (1, 2005, `Dark Blue', `Popular SUV; great for moving people or cargo across town, or wherever your adventures may take you.');
      INSERT INTO Used_Vehicles (model_id, modelyear, color, mileage, certified, warranty, description) VALUES  (1, 2000, `White', 64800, 0, 0, `Good condition; One owner; 4WD, V6'),  (2, 2003, `Deep Green Pearl', 27300, 0, 1, `Excellent condition, off-lease vehicle;  Must see!'),
      (3, 2004, `Reflex Silver', 3800, 1, 1, `R32 Limited edition; Low miles; Understated and surprisingly functional; Loaded.'), (4, 1993, `Red', 152000, 0, 0, `Runs decent; 4WD; Tinted windows, aftermarket   stereo; Needs a little TLC.'); 

  Note that there is a NULL value for the model_id of the second vehicle you added to the New_Vehicles  table. You'll find out why later on.
 

Retrieving Information

  You've spent all that time creating your database tables and populating them with data; now it's time to  get some of that data back out. For this, you will use the SELECT statement. A simplified form of SELECT looks something like this:

      SELECT [column_names] FROM [table] WHERE [criteria]

  If you wanted to retrieve a list of the colors of your used cars, for example, you would use something  similar to the following:
     SELECT color FROM Used_Vehicles;

  which would return the following:

      +-------------------------+      
      |      color              |
      +-------------------------+
      | White                 |
      | Deep Green Pearl |
      | Reflex Silver         |   
      | Red                    |
      +-------------------------+ 

 Or perhaps, you want a listing of all the mileages for cars older than the 2001 model year:

    SELECT mileage, modelyear FROM Used_Vehicles WHERE modelyear < 2001;

 Running this query shows the following:

     +--------------+---------------+
     | mileage    | modelyear |
     +--------------+---------------+
     |     64800 |        2000 |
     |   152000 |        1993 |
     +--------------+---------------+ 

 Simple enough, but what if you spotted an error, and wanted to change an existing record?