Database Reference
In-Depth Information
Other Possibilities
A few times in this chapter, I mentioned that the
INSERT
statement offers extra options. In
this section, we'll cover some of them. You may not use these often in the beginning, but
you should know about them.
Inserting Emphatically
Besides the basic syntax of the
INSERT
statement,there is a more emphatic syntax that in-
volves mapping individual columns to data given. Here's an example in which information
on another bird family is inserted into the
bird_families
table; enter it in
mysql
to see
how you like the visceral feel of this syntax:
INSERT INTO
bird_families
SET
scientific_name
=
'Rallidae'
,
order_id
=
113
;
This syntax is somewhat awkward. However, there's less likelihood of making a mistake
with this syntax, or at least it's less likely that you will enter the column names or the data
in the wrong order, or not give enough columns of data. Because of its rigidity, most people
don't normally use this syntax. But the precision it offers makes it a preferred syntax for
some people writing automated scripts. It's primarily popular because the syntax calls for
naming the column and assigning a value immediately afterwards, in a key/value pair
format found in many programming languages. This makes it easier to visually
troubleshoot a programming script. Second, if the name of a column has been changed or
deleted since the creation of a script using this syntax, the statement will be rejected by the
server and data won't be entered into the wrong columns. But it doesn't add any functional-
ity to the standard syntax that we've used throughout the chapter, as long as you list the
columns explicitly in the standard syntax. Plus, you can insert only one row at a time with
this syntax
Inserting Data from Another Table
INSERT
can be combinedwith a
SELECT
statement (we covered this briefly in
that the examples in this section get complicated. You're not expected to do the examples
in this section; just read along.
Earlier in this chapter, we entered data for a few bird families — 13 so far. You have the
option of downloading the table filled with data from my site, but I had to get the data else-
where (or endure manually entering 228 rows of data on bird families). So I went to Cor-
nell University's website. The Cornell Lab of Ornithologyteaches ornithology and is a