Inserting Data - Learning MySQL and MariaDB - page 109

+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-------------------+--------------+------+-----+---------+----------------+
| order_id | int(11) | NO | PRI | NULL |
auto_increment |
| scientific_name | varchar(255) | YES | UNI | NULL
| |
| brief_description | varchar(255) | YES | | NULL
| |
| order_image | blob | YES | | NULL
| |
+-------------------+--------------+------+-----+---------+----------------+
As you can see, this table has only four columns: an identification number that will be
used by the bird_families to join to this table, a column for the scientific name of
the bird order, a column for the description of the order; and a column with an image rep-
resenting each order of birds. The order_id column starts with 1 for the first bird order
and is set automatically to the next number in sequence each time we add a bird order (un-
less we told MySQL otherwise).
Before entering the orders of birds, let's prime the order_id by initiallysetting the
AUTO_INCREMENT variable to 100, so that all of the bird order identification numbers
will be at least three digits in length. The numbering means nothing to MySQL; it's only a
matter of personal style. To do this, we'll use the ALTER TABLE statement (covered in
Chapter5 ) . Enter the following in the mysql client:
ALTER TABLE bird_orders
AUTO_INCREMENT = 100 ;
This SQL statement alters the table bird_orders , but only the value set on the server
for the AUTO_INCREMENT variable for the specified table. This will set the order_id
to 100 for the first order that we enter in our bird_orders table.
Let's now enter the orders of birds. We can quickly enter a bunch of orders using the
multiple-row syntax for the INSERT statement. Because there are only 29 modern orders
of birds, let's enter all of them. The following gigantic SQL statement is what I used to in-
sert data into the bird_orders table; you can download the table from my site or enter
the SQL statement in mysql (perhaps by cutting and pasting it from an ebook):
INSERT INTO bird_orders ( scientific_name , brief_description )
VALUES ( 'Anseriformes' , "Waterfowl" ),
( 'Galliformes' , "Fowl" ),
( 'Charadriiformes' , "Gulls, Button Quails, Plovers" ),