Inserting Data into the Database
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
insert into customers
set name=’Michael Archer’,
address=’12 Adderley Avenue’,
Also notice that we specified a
value for the
column when adding
Julie Smith and ignored that column when adding the other customers.You might recall
that when you set up the database,you created
as the primary key for the
table,so this might seem strange.However, you specified the field as
.This means that,if you insert a row with a
value or no value in
this field,MySQL will generate the next number in the auto increment sequence and
insert it for you automatically.This behavior is pretty useful.
You can also insert multiple rows into a table at once.Each row should be in its own
set of parentheses,and each set of parentheses should be separated by a comma.
Only a few other variants are possible with
.After the word
keyword means the system may wait
and insert later when data is not being read from the table.The
that your inserted data will be buffered.If the server is busy, you can continue running
queries rather than having to wait for this
operation to complete.
Immediately after this,you can optionally specify
.This means that if you try
to insert any rows that would cause a duplicate unique key,they will be silently ignored.
Another alternative is to specify
ON DUPLICATE KEY UPDATE expression
at the end of
statement.This can be used to change the duplicate value using a normal
statement (covered later in this chapter).
We’ve put together some simple sample data to populate the database.This is just a
series of simple
statements that use the multirow insertion approach.You can find
the script that does this in the file
on the CD accompa-
nying this book.It is also shown in Listing 10.1.
Listing 10.1 book_insert.sql—SQL to Populate the Tables for Book-O-Rama
insert into customers values
(3, ‘Julie Smith’, ‘25 Oak Street’, ‘Airport West’),
(4, ‘Alan Wong’, ‘1/47 Haines Avenue’, ‘Box Hill’),
(5, ‘Michelle Arthur’, ‘357 North Road’, ‘Yarraville’);
insert into orders values
(NULL, 5, 69.98, ‘2000-04-02’),
(NULL, 3, 49.99, ‘2000-04-15’),