76
243
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
syntax:
insert into customers
set name=’Michael Archer’,
address=’12 Adderley Avenue’,
city=’Leeton’;
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 you set up the database,you created
customerid
as the primary key for the
customers
table,so this might seem strange.However, you specified the field as
AUTO_INCREMENT
.This means that,if you insert a row with a
NULL
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
INSERT
.After the word
INSERT
,you can
add
LOW_PRIORITY
or
DELAYED
.The
LOW_PRIORITY
keyword means the system may wait
and insert later when data is not being read from the table.The
DELAYED
keyword means
that your inserted data will be buffered.If the server is busy, you can continue running
queries rather than having to wait for this
INSERT
operation to complete.
Immediately after this,you can optionally specify
IGNORE
.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
the
INSERT
statement.This can be used to change the duplicate value using a normal
UPDATE
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
INSERT
statements that use the multirow insertion approach.You can find
the script that does this in the file
\chapter10\book_insert.sql
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
use books;
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’),
