Creating the Database

only for RuBoard - do not distribute or recompile

In this section, you will create and fill the database with the initial data load. You will put all the statements except the create database into a file, which you can use to reload the database later if necessary.

Listing 6.1 assumes that a MySQL server is installed and running on the target machine.

First, start the MySQL command line tool by typing this line:

 % mysql

This changes the prompt to mysql> .

Then create the database:

 mysql> CREATE DATABASE sesi;

Listing 6.1 creates the SESI database for Chapters 6 and 7. It drops and refreshes the tables with the data being transferred in from the sample text files.

Usage (from the command prompt):

 %mysql < listing.6.1

 %mysql -t < listing.6.1

The -t option sends the output of the select statements to the screen in a table-like format (like you would get from the mysql> prompt).

As you can see, C-style comments will work (note the sections that start with /* and end with */). However, be careful not to use any semicolons, single quotation marks, or double quotation marks inside the comments! This can cause the parser to think there is something it should be handling, in which case it will issue an error and terminate.

Listing 6.1 Create and Fill the Database For Specialty Electrical Supply, Inc.

use sesi; DROP TABLE if exists tbl_items; CREATE TABLE tbl_items ( item varchar(8) NOT NULL PRIMARY KEY, description varchar(50) NOT NULL, price decimal(4,2) NOT NULL ); /* In the following statement and the second LOAD TABLE statement * further in the file, note that the fully qualified path name * has been put in for the file. This is necessary because MySQL * will look in the database directory by default, not in the current * local directory or your project directory. * Also note that the text files being imported are tab-delimited, * and they have the same number of fields as the table has columns. * In the following statement, the new_master_item.txt file has been * output from another application (probably a spreadsheet) and * copied into place so that the LOAD DATA INFILE statement will * work correctly. It contains the master list of items that the * company sells. * * Remember: the "/mnt/DOS_hda2..." path name is the one for the * test machine that was used to build this application. * Substitute the location you use on your machine. */ LOAD DATA INFILE "/mnt/DOS_hda2/newriders/book/ch6/new_item_master.txt" INTO TABLE tbl_items; /* Check for the success of the import. It should be approximately * 600 records. Compare that to the text file in the statement for the * exact count. */ select count(*) from tbl_items; DROP TABLE if exists tbl_customers; CREATE TABLE tbl_customers ( num smallint NOT NULL PRIMARY KEY, name varchar(100) NOT NULL, ship_to_addr1 varchar(100), ship_to_addr2 varchar(100), ship_to_city varchar(35), ship_to_state char(2), ship_to_zip varchar(10), bill_to_addr1 varchar(100), bill_to_addr2 varchar(100), bill_to_city varchar(35), bill_to_state char(2), bill_to_zip varchar(10), contact_first varchar(50), contact_last varchar(50), phone varchar(12), title varchar(50), comments varchar(255) ); /* In the following LOAD DATA INFILE statement, the cust_mast.txt * file has been exported from elsewhere and put into the location * stated. It contains the customer data. It is the customer master * file. */ LOAD DATA INFILE "/mnt/DOS_hda2/newriders/book/ch6/cust_mast.txt" INTO TABLE tbl_customers; /* Again, check the success of the import operation. This time, * look for approximately 170 records. The best way to verify * that is to know the record count in the text file and to * compare it to the result returned by the next statement. */ select count(*) from tbl_customers; /* Now alter tbl_customers to be auto-incremented on the first column. * See the Alter Table statement, which is next after this comment * block. * The following statement explains why you need to ALTER the * table that was just created and filled. * For the cust_mast.txt file data, the first data field is the * customer number, which is the primary key of the table. In the * MySQL database, this column should be an auto-incrementing field. * However, creating the table with the first field set to auto- * increment may cause problems because the customer numbers are not * necessarily complete, nor do they start at 1. Therefore, in this * case, to avoid problems with the LOAD DATA INFILE statement, * you issue an ALTER TABLE statement to change the attributes * on the num field so it will auto-increment after the initial data load. * Also note that it is not necessary to restate PRIMARY KEY. * If you attempt to put it in the ALTER TABLE statement, MySQL * will issue a duplicate primary key error message. * One final comment: If you end one of these files with a comment, * it will produce an error when you attempt to feed it to MySQL. * Apparently, it expects a semicolon as the last character * in the file. */ ALTER TABLE tbl_customers MODIFY num SMALLINT NOT NULL AUTO_INCREMENT;

only for RuBoard - do not distribute or recompile