Specifications

Inserting Data into the Database
Before you can do a lot with a database, you need to store some data in it. The way you will
most commonly do this is with the SQL INSERT statement.
Recall that RDBMSs contain tables, which in turn contain rows of data organized into
columns. Each row in a table normally describes some real-world object or relationship, and
the column values for that row store information about the real-world object. We can use the
INSERT statement to put rows of data into the database.
The usual form of an INSERT statement is
INSERT [INTO] table [(column1, column2, column3,...)] VALUES
(value1, value2, value3,...);
For example, to insert a record into Book-O-Rama’s Customers table, you could type
insert into customers values
(NULL, “Julie Smith”, “25 Oak Street”, “Airport West”);
You can see that we’ve replaced table with the name of the actual table we want to put the
data in, and the values with specific values. The values in this example are all enclosed in
double quotes. Strings should always be enclosed in pairs of single or double quotes in
MySQL. (We will use both in this book.) Numbers and dates do not need quotes.
There are a few interesting things to note about the INSERT statement.
The values we specified will be used to fill in the table columns in order. If you want to fill in
only some of the columns, or if you want to specify them in a different order, you can list the
specific columns in the columns part of the statement. For example,
insert into customers (name, city) values
(“Melissa Jones”, “Nar Nar Goon North”);
This approach is useful if you have only partial data about a particular record, or if some fields
in the record are optional. You can also achieve the same effect with the following syntax:
insert into customers
set name=”Michael Archer”,
address=”12 Adderley Avenue”,
city=”Leeton”;
You ’ll also notice that we specified a NULL value for the customerid column when adding Julie
Smith and ignored that column when adding the other customers. You might recall that when
we set the database up, we created customerid as the primary key for the Customers table, so
this might seem strange. However, we specified the field as AUTOINCREMENT. This means that, if
Working with Your MySQL Database
C
HAPTER 9
9
W
ORKING WITH
YOUR MYSQL
DATABASE
209
12 7842 CH09 3/6/01 3:36 PM Page 209