Writing a Script to Build a Table

It is very important to understand how to create tables by hand in SQL, because your programs will have to do this same work. However, it's very tedious to write your SQL code in the MySQL window directly. When you create real data applications, you'll often have to build and rebuild your data tables several times before you are satisfied with them, and this would be awkward directly in the command line interface. Also, as you are writing programs that work with your database, you will likely make mistakes that corrupt the original data. It's good to have a script ready that can easily rebuild the database with test data even if something goes wrong. Most programmers create a script of SQL commands with a text editor (you can use the same editor that you write your PHP code in) and use the SOURCE command to load that code in. Below is an SQL script for creating the phonelist database.

## build phone list
## for mySQL

USE chapter7;
DROP TABLE IF EXISTS phoneList;

CREATE TABLE phoneList (
  id INT PRIMARY KEY,
  firstName VARCHAR(15),
  lastName VARCHAR (15),
  email VARCHAR(20),
  phone VARCHAR(15)
);

INSERT INTO phoneList
VALUES (
  0, 'Andy', 'Harris', 'aharris@cs.iupui.edu', '123-4567'
);

SELECT * FROM phoneList;

This code isn't exactly like the code I used in the interactive session, because there are a few more features that are especially handy when you create SQL code in a script.

Creating Comments in SQL

SQL is actually a language. Although it isn't technically a programming language, it has many of the same features. Like PHP and other languages, SQL supports several types of comment characters. The # sign is often used to signify a comment in SQL. Comments are especially important when you save a group of SQL commands in a file for later reuse. These comments can help you remember what type of database you were trying to build. It's critical to put basic comments in your scripts so you will understand what they should do later.

Dropping a Table

It may seem strange to talk about deleting a table from a database before you've built one, but often (as in this case) a database is created using a script. Before you create a new table, you should check to see if it already exists, and if it does, delete it with the DROP command. The

DROP TABLE IF EXISTS phoneList;

command does exactly that. If the phoneList table currently exists, it will be deleted, so there will be no confusion.

Running a Script with SOURCE

You can create an SQL script with any text editor. It is common to save SQL scripts with the .sql extension. Inside MySQL, you can use the SOURCE command to load and execute an external script. Figure 7.9 shows MySQL after I ran the buildPhonelist.sql script.

Figure 7.9: The SOURCE command allows you to read in SQL instructions from a file.

TRAP

In Windows I often drag a file from a directory view into a command line program like MySQL. Windows will copy the entire filename over, but it will include double quotes, which will cause problems for the MySQL interpreter. If you drag a filename into MySQL, you will need to edit out the quote characters so that MySQL will read the file correctly.