MySQL – MariaDB – Inserting Data

How to install Ubuntu Server 19.10

After you have created a database and tables, the next step is to
insert data. I’m intentionally using the word insert
because the most common and basic way to enter data into a table is
with the SQL statement INSERT. It’s easier to
learn the language of MySQL and MariaDB, if you use the keywords to describe
what you are doing. In this chapter, we will cover the INSERT
statement, its different syntax, and many of its options. We’ll use the
tables that we created in Chapter 4 and altered
in Chapter 5. We’ll also look at some related
statements on retrieving or selecting data, but they will be covered in
greater detail in Chapter 7.

When going through this chapter, participate. When examples are given
showing the INSERT statement and other SQL statements, try
entering them on your server using the mysql client. At the end of the chapter are some
exercises—do them. They require you to enter data in the tables that you
created in Chapter 4. In doing the exercises,
you may have to refer back to the examples in this chapter and in Chapter 4. This will help to reinforce what you’ve
read. When you’re done, you should feel comfortable entering data in MySQL
and MariaDB.

The Syntax

The INSERT statement adds rows of data into a table. It can add a single row or
multiple rows at a time. The basic syntax of this SQL statement is:

INSERT INTO table [(column, …)]
  VALUES (value, …), (…), …;

The keywords INSERT INTO are followed by the name of
the table and an optional list of columns in parentheses. (Square brackets
in a syntax indicate that the bracketed material is optional.)
Then comes the keyword VALUES and a pair of parentheses containing a list of values for each
column. There are several deviations of the syntax, but this is the basic
one. Commas separate the column names within the first list, and the
values within the second.

Let’s go through some examples that will show a few of the simpler
syntaxes for the INSERT statement. Don’t try to enter these
on your system. These are generic examples using INSERT to
add data to nonexistent tables.

Here’s a generic example of the INSERT statement with
the minimum required syntax:

INSERT INTO books
VALUES('The Big Sleep', 'Raymond Chandler', '1934');

This example adds text to a table called books.
This table happens to contain only three columns, so we don’t bother to
list the columns. But because there are three columns, we have to specify
three values, which will go into the columns in the order that the columns
were defined in CREATE TABLE. So in our example,
The Big Sleep will be inserted into the first column
of the table, Raymond Chandler will go into the
second column, and 1934 will go into the
third.

For columns that have a default value set, you can rely on the
server to use that value and omit the column from your INSERT
statement. One way to do this is by entering a value of DEFAULT or NULL, as shown in the following example:

INSERT INTO books
VALUES('The Thirty-Nine Steps', 'John Buchan', DEFAULT);

MySQL will use the default value for the third column. If the
default value is NULL—the usual default value if none is specified—that’s
what the statement will put in the column for the row. For a column
defined with AUTO_INCREMENT, the server will put the next number in the sequence for that
column.

Another way to use defaults is to list just the columns into which
you want to enter non-default data, like so:

INSERT INTO books
(author, title)
VALUES('Evelyn Waugh','Brideshead Revisited');

Note that this example lists just two columns within parentheses.
It’s also significant that the statement lists them in a different order.
The list of values must match the order of the list of columns. For the
third column (i.e., year) of this table, the default value
will be inserted.

When you have many rows of data to insert into the same table, it
can be more efficient to insert all of the rows in one SQL statement. To
do this, you need to use a slightly different syntax for the
INSERT statement. Just add more sets of values in
parentheses, each set separated by a comma. Here’s an example of
this:

INSERT INTO books
(title, author, year)
VALUES('Visitation of Spirits','Randall Kenan','1989'),
      ('Heart of Darkness','Joseph Conrad','1902'),
      ('The Idiot','Fyodor Dostoevsky','1871');

This SQL statement enters three rows of data into the
books table. Notice that the set of column names and the
VALUES keyword appear only once. Almost all SQL statements
allow only one instance of each clause (the VALUES clause in
this case), although that clause may contain multiple items and lists as
it does here.

Practical Examples

Let’s get back to the rookery database that we created and
altered in Chapters 4 and 5 for more involved examples of inserting
data into tables. If you haven’t created those tables yet, I recommend you
go back and do that before proceeding with this chapter.

Your natural tendency when putting data into a database will be to
start by adding data to the main or primary table of the database first
and to worry about ancillary or reference tables later. That will work
well enough, but you may be creating more work for yourself than needed.
Starting with the main table is more interesting, and entering data in
reference tables is more tedious. But that’s the way of databases: they
are tedious. It’s inescapable.

Nevertheless, we don’t have to create all of the tables we will need
for a database before entering data; we don’t need to enter data into all
of the secondary tables before working on the primary tables. It will be
difficult to plan ahead for all of the possible tables that will be
needed. Instead, database development is generally always a work in
progress. You will often add more tables, change the schema of existing
tables, and shift large blocks of data from one table to another to
improve performance and to make the management of the database easier.
That takes some of the tediousness out of databases and makes database
management interesting.

With that approach in mind, we’ll enter data in some of the tables,
using some simple logic to decide which table to work on first. Remember
how we are categorizing birds: a bird species is a member of a bird
family, and a bird family is part of a bird order. The birds
table needs the family_id to join with the
bird_families table, and the bird_families table
needs an order_id from the bird_orders table to
join with it. So, we’ll add data to bird_orders first, then
to bird_families, and then to birds.

Most people don’t know the scientific names of birds, bird families,
and bird orders. However, you can find this information on Wikipedia and
sites dedicated specifically to bird-watching and ornithology. But there’s
no need for you to do research about birds to participate in this book.
I’ll provide you with the information to enter a few rows for each table,
and you can download complete tables from my website.

The Table for Bird Orders

Before entering data in the bird_orders table, let’s
remind ourselves of the structure of the table by executing the
following SQL statement:

DESCRIBE bird_orders;

+-------------------+--------------+------+-----+---------+----------------+
| 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 representing 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 (unless we told MySQL otherwise).

Before entering the orders of birds, let’s prime the
order_id by initially setting 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 Chapter 5). 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 insert 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"),
      ('Gaviiformes', "Loons"),
      ('Podicipediformes', "Grebes"),
      ('Procellariiformes', "Albatrosses, Petrels"),
      ('Sphenisciformes', "Penguins"),
      ('Pelecaniformes', "Pelicans"),
      ('Phaethontiformes', "Tropicbirds"),
      ('Ciconiiformes', "Storks"),
      ('Cathartiformes', "New-World Vultures"),
      ('Phoenicopteriformes', "Flamingos"),
      ('Falconiformes', "Falcons, Eagles, Hawks"),
      ('Gruiformes', "Cranes"),
      ('Pteroclidiformes', "Sandgrouse"),
      ('Columbiformes', "Doves and Pigeons"),
      ('Psittaciformes', "Parrots"),
      ('Cuculiformes', "Cuckoos and Turacos"),
      ('Opisthocomiformes', "Hoatzin"),
      ('Strigiformes', "Owls"),
      ('Struthioniformes', "Ostriches, Emus, Kiwis"),
      ('Tinamiformes', "Tinamous"),
      ('Caprimulgiformes', "Nightjars"),
      ('Apodiformes', "Swifts and Hummingbirds"),
      ('Coraciiformes', "Kingfishers"),
      ('Piciformes', "Woodpeckers"),
      ('Trogoniformes', "Trogons"),
      ('Coliiformes', "Mousebirds"),
      ('Passeriformes', "Passerines");

As large as that statement was, it inserted only two of the four
columns into each row. I left out order_id, which I know
will be assigned by the server with a value that starts at what I asked
for, 100, and increments for each row. The default of NULL will be
assigned to the order_image column, and we can insert
images later if we want. However, we can’t pretend the columns don’t
exist. If we enter an INSERT statement and don’t provide
data for one or more of the columns that we specify, MySQL will reject
the SQL statement and return an error message like this one:

ERROR 1136 (21S01):
Column count doesn't match value count at row 1

This indicates that we didn’t give the server the number of
columns it was expecting.

By now, I hope you see why I created a special table dedicated to
orders and made it so you have to enter each name only here, and not on
every single bird in the main table. Given the bird_orders
table, you can use numbers in the order_id column to
represent a bird order in the bird_families table. This is
one of the benefits of a reference table. Typing in numbers is easier
than typing in a scientific name each time, and should reduce the
frequency of typos.

The Table for Bird Families

Now that the bird_orders table is filled with data,
let’s next add some data to the bird_families table. First,
execute the following statement:

DESCRIBE bird_families;

This SQL statement will show you the layout of the columns for the
bird_families table. We also need to know the
order_id for the order of the families we will enter. To
start, we’ll enter a row for the Gaviidae bird
family. This happens to be the family to which the Great
Northern Loon
belongs—a bird we entered already in the
birds table. The Gaviidae family is
part of the Gaviiformes order of birds. So enter
the following on your server to determine the order_id for
that order:

SELECT order_id FROM bird_orders
WHERE scientific_name = 'Gaviiformes';

+----------+
| order_id |
+----------+
|      103 |
+----------+

Now let’s enter the Gaviidae family in the
bird_families table. We’ll do that like so:

INSERT INTO bird_families
VALUES(100, 'Gaviidae',
"Loons or divers are aquatic birds found mainly in the Northern Hemisphere.",
103);

This adds the name and description of the bird family,
Gaviidae, into the bird_families
table. You may have noticed that although the family_id
column is set to increment automatically, I put a value of 100 here.
That’s not necessary, but it’s another way of instituting my style of
starting with an identification number that has a few digits. A
family_id of 1 for an elegant and ancient bird family like
that of the loons sounds either presumptuous or lame to me. By giving it
a specific value, I’ll not only give an ID of 100 to
Gaviidae, but ensure that the server will give 101
to the next family I insert.

If we try to enter the INSERT statement with the
correct number of columns, but not in the order the server expects to
receive the data based on the schema for the table, the server may
accept the data. It will generate a warning message if the data given
for the columns don’t match the column types. For instance, suppose we
had tried to add another row to the same table—this one for the bird
family, Anatidae, the family for the Wood Duck,
another bird we entered already in the birds table. Suppose
further that we had tried to give the data in a different order from the
way the columns are organized in the table. The server would accept the
SQL statement and process the data as best it can, but it would not work
the way we might want. The following example shows such a
scenario:

INSERT INTO bird_families
VALUES('Anatidae', "This family includes ducks, geese and swans.", NULL, 103);
Query OK, 1 row affected, 1 warning (0.05 sec)

Notice that in this SQL statement we put the family’s name first,
then the description, then NULL for the family_id, and 103
for the order_id. MySQL is expecting the first column to be
a number or DEFAULT or NULL. Instead, we gave it text.
Notice that the status line returned by mysql after the INSERT statement
says, Query OK, 1 row affected, 1 warning. That
means that one row was added, but a warning message was generated,
although it wasn’t displayed. We’ll use the SHOW WARNINGS statement like so to see
the warning message:

SHOW WARNINGS \G

*************************** 1. row ***************************
  Level: Warning
   Code: 1366
Message: Incorrect integer value: 'Anatidae' for column 'family_id' at row 1
1 row in set (0.15 sec)

Here we can see the warning message: the server was expecting an
integer value, but received text for the column, family_id.
Let’s run the SELECT statement to see what we have now in
the bird_families table:

SELECT * FROM bird_families \G

*************************** 1. row ***************************
        family_id: 100
  scientific_name: Gaviidae
brief_description: Loons or divers are aquatic birds
                   found mainly in the Northern Hemisphere.
         order_id: 103
*************************** 2. row ***************************
        family_id: 101
  scientific_name: This family includes ducks, geese and swans.
brief_description: NULL
         order_id: 103

The first row is fine; we entered it correctly, before. But
because MySQL didn’t receive a good value for the family_id
column for the row we just entered, it ignored what we gave it and
automatically set the column to 101—the default value based on
AUTO_INCREMENT. It took the description text that was
intended for brief_description column and put that in the
scientific_name column. It put the NULL we meant for the
family_id column and put it in the
brief_description column. This row needs to be fixed or
deleted. Let’s delete it and try again. We’ll use the DELETE statement like this:

DELETE FROM bird_families
WHERE family_id = 101;

This will delete only one row: the one where the
family_id equals 101. Be careful with the
DELETE statement. There’s no UNDO statement,
per se, when working with the data like this. If you don’t include the
WHERE clause, you will delete all of the data in the table. For this
table, which has only two rows of data, it’s not a problem to re-enter
the data. But on a server with thousands of rows of data, you could lose
plenty of data—permanently, if you don’t have a backup copy. Even if you
do have a backup of the data, you’re not going to be able to restore the
data quickly or easily. So be careful with the DELETE
statement and always use a WHERE clause that limits greatly
the data that’s to be deleted.

Let’s re-enter the data for the duck family,
Anatidae, but this time we’ll try a different
syntax for the INSERT statement so that we don’t have to
give data for all of the columns and so that we can give data in a
different order from how it’s structured in the table:

INSERT INTO bird_families
(scientific_name, order_id, brief_description)
VALUES('Anatidae', 103, "This family includes ducks, geese and swans.");

To let us give only three columns in this SQL statement, and in a
different order, we put the names of the columns in parentheses before
the set of values. Listing the names of the columns is optional,
provided data is in the correct format for all of the columns and in
order. Because we are not doing that with this SQL statement, we had to
list the columns for which we are giving data, matching the order that
the data is given in the VALUES clause in the set of values
and in parentheses. Basically, we’re telling the server what each value
represents; we’re mapping the data to the correct columns in the table.
Again, for the columns that we don’t provide data or don’t name in the
SQL statement, the server will use the default values. Let’s see what we
have now for data in the bird_families table:

SELECT * FROM bird_families \G

*************************** 1. row ***************************
        family_id: 100
  scientific_name: Gaviidae
brief_description: Loons or divers are aquatic birds
                   found mainly in the Northern Hemisphere.
         order_id: 103
*************************** 2. row ***************************
        family_id: 102
  scientific_name: Anatidae
brief_description: This family includes ducks, geese and swans.
         order_id: 103

That’s better. Notice that the server put the family name,
Anatidae, in the scientific_name
column, per the mapping instructions stipulated in the
INSERT statement. It also assigned a number to the
family_id column. Because the family_id for
the previous row was set to 101, even though we deleted it, the server
remembers elsewhere in MySQL that the count is now at 101. So it
incremented that number by 1 to set this new row to 102. You could
change the value of this row and reset the counter (i.e., the
AUTO_INCREMENT variable for the column of the table), but
it’s generally not important.

Let’s prepare now to enter some more bird families. We’ll keep the
data simple this time. We’ll give only the scientific name and the order
identification number. To do that, we need to know the
order_id of each order. We’ll execute this SQL statement to
get the data we need:

SELECT order_id, scientific_name FROM bird_orders;

+----------+---------------------+
| order_id | scientific_name     |
+----------+---------------------+
|      100 | Anseriformes        |
|      101 | Galliformes         |
|      102 | Charadriiformes     |
|      103 | Gaviiformes         |
|      104 | Podicipediformes    |
|      105 | Procellariiformes   |
|      106 | Sphenisciformes     |
|      107 | Pelecaniformes      |
|      108 | Phaethontiformes    |
|      109 | Ciconiiformes       |
|      110 | Cathartiformes      |
|      111 | Phoenicopteriformes |
|      112 | Falconiformes       |
|      113 | Gruiformes          |
|      114 | Pteroclidiformes    |
|      115 | Columbiformes       |
|      116 | Psittaciformes      |
|      117 | Cuculiformes        |
|      118 | Opisthocomiformes   |
|      119 | Strigiformes        |
|      120 | Struthioniformes    |
|      121 | Tinamiformes        |
|      122 | Caprimulgiformes    |
|      123 | Apodiformes         |
|      124 | Coraciiformes       |
|      125 | Piciformes          |
|      126 | Trogoniformes       |
|      127 | Coliiformes         |
|      128 | Passeriformes       |
+----------+---------------------+

Now let’s enter one hefty INSERT statement to insert
a bunch of bird families into the bird_families table. We
just list each set of data within its own parentheses, separated by
commas. After consulting our bird-watching guides, we determine which
families belong to which orders and then enter this in the mysql client:

INSERT INTO bird_families
(scientific_name, order_id)
VALUES('Charadriidae', 109),
      ('Laridae', 102),
      ('Sternidae', 102),
      ('Caprimulgidae', 122),
      ('Sittidae', 128),
      ('Picidae', 125),
      ('Accipitridae', 112),
      ('Tyrannidae', 128),
      ('Formicariidae', 128),
      ('Laniidae', 128);

This statement enters 10 rows of data in one batch. Notice that we
didn’t have to list the names of the columns for each row. Notice also
that we didn’t mention the family_id column in this SQL
statement. The server will assign automatically the next number in the
column’s sequence for that field. And we didn’t give the statement any
text for the brief_description column. We can enter that
later if we want.

If you want a heftier bird_family table with more
rows and the brief descriptions, you can download it later from my site.
This is enough data for now. Let’s execute the SELECT statement to get the
family_id numbers. We’ll need them when we enter
birds in the birds table:

SELECT family_id, scientific_name
FROM bird_families
ORDER BY scientific_name;

+-----------+-----------------+
| family_id | scientific_name |
+-----------+-----------------+
|       109 | Accipitridae    |
|       102 | Anatidae        |
|       106 | Caprimulgidae   |
|       103 | Charadriidae    |
|       111 | Formicariidae   |
|       100 | Gaviidae        |
|       112 | Laniidae        |
|       104 | Laridae         |
|       108 | Picidae         |
|       107 | Sittidae        |
|       105 | Sternidae       |
|       110 | Tyrannidae      |
+-----------+-----------------+

I added an extra tweak to the previous SELECT
statement: an ORDER BY clause, ensuring that the results
would be ordered alphabetically by the scientific name of the order.
We’ll cover the ORDER BY clause in more depth in Chapter 7.

We’re now ready to enter data in the birds table. The
table already has a Killdeer, a small shore bird that is part of the
Charadriidae family. Let’s prepare to enter a few
more shore birds from the same family as the Killdeer. Looking at the
preceding results, we can determine that the family_id is
103, because the Killdeer is in the
Charadriidae family. Incidentally, the values for
the family_id column might be different on your
server.

Now that we have the family_id for shore birds, let’s
look at the columns in the birds table and decide which
ones we’ll set. To do that, let’s use the SHOW COLUMNS statement like this:

SHOW COLUMNS FROM birds;

+------------------------+--------------+------+-----+-------+----------------+
| Field                  | Type         | Null | Key |Default| Extra          |
+------------------------+--------------+------+-----+-------+----------------+
| bird_id                | int(11)      | NO   | PRI | NULL  | auto_increment |
| scientific_name        | varchar(100) | YES  | UNI | NULL  |                |
| common_name            | varchar(255) | YES  |     | NULL  |                |
| family_id              | int(11)      | YES  |     | NULL  |                |
| conservation_status_id | int(11)      | YES  |     | NULL  |                |
| wing_id                | char(2)      | YES  |     | NULL  |                |
| body_id                | char(2)      | YES  |     | NULL  |                |
| bill_id                | char(2)      | YES  |     | NULL  |                |
| description            | text         | YES  |     | NULL  |                |
+------------------------+--------------+------+-----+-------+----------------+

The results are the same as for the DESCRIBE
statement. However, with SHOW COLUMNS, you can retrieve a
list of columns based on a pattern. For instance, suppose you just want
a list of reference columns—columns that we labeled with the ending,
_id. You could enter this:

SHOW COLUMNS FROM birds LIKE '%id';

+------------------------+---------+------+-----+---------+----------------+
| Field                  | Type    | Null | Key | Default | Extra          |
+------------------------+---------+------+-----+---------+----------------+
| bird_id                | int(11) | NO   | PRI | NULL    | auto_increment |
| family_id              | int(11) | YES  |     | NULL    |                |
| conservation_status_id | int(11) | YES  |     | NULL    |                |
| wing_id                | char(2) | YES  |     | NULL    |                |
| body_id                | char(2) | YES  |     | NULL    |                |
| bill_id                | char(2) | YES  |     | NULL    |                |
+------------------------+---------+------+-----+---------+----------------+

We used the percent sign ( %) as a wildcard—the asterisks
won’t work here—to specify the pattern of any text that starts with any
characters but ends with _id. For a large table, being able
to refine the results like this might be useful. When naming your
columns, keep in mind that you can search easily based on a naming
pattern (e.g., %_id). Incidentally, if you add the
FULL flag to this SQL statement (e.g., SHOW FULL
COLUMNS FROM birds;
), you can get more information on each
column. Try that on your system to see the results.

The Table for Birds

That was interesting, but let’s get back to data entry—the focus
of this chapter. Now that we have been reminded of the columns in the
birds table, let’s enter data on some of shore birds. Enter
the following in mysql:

INSERT INTO birds
(common_name, scientific_name, family_id)
VALUES('Mountain Plover', 'Charadrius montanus', 103);

This adds a record for the Mountain Plover.
Notice that I mixed up the order of the columns, but it still works
because the order of the values agrees with the order of the columns. We
indicate that the bird is in the family of
Charadriidae by giving a value of 103
for the family_id. There are more columns that need data,
but we’ll worry about that later. Let’s now enter a few more shore
birds, using the multiple-row syntax for the INSERT
statement:

INSERT INTO birds
(common_name, scientific_name, family_id)
VALUES('Snowy Plover', 'Charadrius alexandrinus', 103),
('Black-bellied Plover', 'Pluvialis squatarola', 103),
('Pacific Golden Plover', 'Pluvialis fulva', 103);

In this example, we’ve added three shore birds in one statement,
all of the same family of birds. This is the same method that we used
earlier to enter several bird families in the bird_families
table and several bird orders in the bird_orders table.
Notice that the number for the family_id is not enclosed
here within quotes. That’s because the column holds integers, using the
INT data type. Therefore, we can pass exposed numbers like
this. If we put them in quotes, MySQL treats them first like characters,
but then analyzes them and realizes that they are numbers and stores
them as numbers. That’s the long explanation. The short explanation is
that it doesn’t usually matter whether numbers are in quotes or
not.

Now that we have entered data for a few more birds, let’s connect
a few of our tables together and retrieve data from them. We’ll use a
SELECT statement, but we’ll give a list of the tables to
merge the data in the results set. This is much more complicated than
any of the previous SELECT statements, but I want you to
see the point of creating different tables, especially the reference
tables we have created. Try entering the following SQL statement on your
server:

SELECT common_name AS 'Bird',
       birds.scientific_name AS 'Scientific Name',
       bird_families.scientific_name AS 'Family',
       bird_orders.scientific_name AS 'Order'
FROM birds,
     bird_families,
     bird_orders
WHERE birds.family_id = bird_families.family_id
AND bird_families.order_id = bird_orders.order_id;

+-----------------------+----------------------+--------------+---------------+
| Bird                  | Scientific Name      | Family       | Orders        |
+-----------------------+----------------------+--------------+---------------+
| Mountain Plover       | Charadrius montanus  | Charadriidae | Ciconiiformes |
| Snowy Plover          | Charadrius alex...   | Charadriidae | Ciconiiformes |
| Black-bellied Plover  | Pluvialis squatarola | Charadriidae | Ciconiiformes |
| Pacific Golden Plover | Pluvialis fulva      | Charadriidae | Ciconiiformes |
+-----------------------+----------------------+--------------+---------------+

In this SELECT statement, we are connecting together three tables. Before looking at
the columns selected, let’s look at the FROM clause. Notice
that all three tables are listed, separated by commas. To assist you in
making sense of this statement, I’ve added some indenting. The table
names don’t need to be on separate lines, as I have laid them
out.

MySQL strings these three tables together based on the WHERE clause. First, we’re telling
MySQL to join the birds table to the
bird_families table where the family_id from
both tables equal or match. Using AND, we then give another condition in the WHERE
clause. We tell MySQL to join the bird_families table to
the bird_orders table where the order_id from
both tables are equal.

That may seem pretty complicated, but if you had a sheet of paper
in front of you showing thousands of birds, and a sheet of paper
containing a list of bird families, and another sheet with a list of
orders of birds, and you wanted to type on your screen a list of bird
with their names, along with the family and order to which each
belonged, you would do the same thing with your fingers, pointing from
keywords on one sheet to the keyword on the other. It’s really intuitive
when you think about it.

Let’s look now at the columns we have selected. We are selecting
the common_name and scientific_name columns
from the birds table. Again, I’ve added indenting and put
these columns on separate lines for clarity. Because all three tables
have columns named scientific_name, we must include the
table name for each column (e.g., birds.scientific_name) to
eliminate ambiguity. I’ve added also an AS clause to each
column selected to give the results table nicer column headings. The
AS clause has nothing to do with the tables on the server; it
affects only what you see in your output. So you can choose the column
headings in the results through the string you put after the
AS keyword.

Let’s take a moment to consider the results. Although we entered
the scientific name of each family and order referenced here only once,
MySQL can pull them together easily by way of the family_id
and order_id columns in the tables. That’s economical and
very cool.

As I said before, the SQL statement I’ve just shown is much more
complicated than anything we’ve looked at before. Don’t worry about
taking in too much of it, though. We’ll cover this kind of SQL statement
in Chapter 7. For now, just know that this
is the point of what we’re doing. The kind of inquiries we can make of
data this way is so much better than one big table with columns for
everything. For each shore bird, we had to enter only 103 for the
family_id column and didn’t have to type the scientific
name for the family, or enter the scientific name of the order for each
bird. We don’t have to worry so much about typos. This leverages your
time and data efficiently.

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 involves 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 functionality 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 combined with a SELECT statement (we covered this
briefly in Chapter 5). Let’s look at an
example of how it might be used. Before you do, I’ll warn you 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 elsewhere (or endure
manually entering 228 rows of data on bird families). So I went to
Cornell University’s website. The Cornell Lab of Ornithology teaches ornithology and is a leading authority on the
subject. On their site, I found a table of data that’s publicly
available. I loaded the table into the rookery database on
my server and named it cornell_birds_families_orders.
Here’s how the table is structured and how the data looks:

DESCRIBE cornell_birds_families_orders;

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| fid         | int(11)      | NO   | PRI | NULL    | auto_increment |
| bird_family | varchar(255) | YES  |     | NULL    |                |
| examples    | varchar(255) | YES  |     | NULL    |                |
| bird_order  | varchar(255) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

SELECT * FROM cornell_birds_families_orders
LIMIT 1;

+-----+---------------+----------+------------------+
| fid | bird_family   | examples | bird_order       |
+-----+---------------+----------+------------------+
|   1 | Struthionidae | Ostrich  | Struthioniformes |
+-----+---------------+----------+------------------+

This is useful. I can take the family names, use the
examples for the brief description, and use them
both to finish the data in the bird_families table. I don’t
need their identification number (i.e., fid) for each bird
family—I’ll use my own. What I need is a way to match the value of the
bird_order column in this table to the
scientific_name in the bird_orders table so
that I can put the correct order_id in the
bird_families table.

There are a couple of ways I could do that. For now, I’ll add
another column to my bird_families table to take in the
bird_order column from this table from Cornell. I’ll use
the ALTER TABLE statement, as described in Chapter 5, and enter the following on my
server:

ALTER TABLE bird_families
ADD COLUMN cornell_bird_order VARCHAR(255);

With this change, I can now execute the following SQL statement to
copy the data from the Cornell table to my table containing data on bird
families:

INSERT IGNORE INTO bird_families
(scientific_name, brief_description, cornell_bird_order)
SELECT bird_family, examples, bird_order
FROM cornell_birds_families_orders;

Look closely at this syntax. It may be useful to you one day. It
starts with the normal syntax of the INSERT statement, but
where we would put the VALUES clause, we instead put a
complete SELECT statement. The syntax of the
SELECT portion is the same as we’ve used so far in other
examples in this book. It’s simple, but neat and very powerful.

Conceptually, you can think of the embedded SELECT
statement creating multiple rows, each containing values in the order
you specify in the SELECT. These values work just like a
VALUES clause, feeding values into the parent
INSERT statement and filling the columns I carefully
specify in the right order.

One thing is different at the start of the previous
INSERT statement. I’ve added the IGNORE option. I used this
because the bird_families table already had data in it.
Because the scientific_name column is set to
UNIQUE, it does not permit duplicate values. If a
multiple-row INSERT statement like this encounters any errors, it will fail and return
an error message. The IGNORE flag instructs the server to
ignore any errors it encounters while processing the SQL statement, and
to insert the rows that may be inserted without problems. Instead of
failing and showing an error message, warning messages are stored on the
server for you to look at later. When the server is finished, if you
want, you can run the SHOW WARNINGS statement to see which rows
of data weren’t inserted into the table. This is a graceful solution if
you just want the server to process the rows that aren’t duplicates and
to ignore the duplicates.

Now that the data has been inserted, I’ll run the following SQL
statement from mysql to look at the
last row in the table—the first rows contain the data I entered
previously:

SELECT * FROM bird_families
ORDER BY family_id DESC LIMIT 1;

+-----------+-----------------+-----------------+----------+-------------------+
| family_id | scientific_name |brief_description| order_id | cornell_bird_order|
+-----------+-----------------+-----------------+----------+-------------------+
|       330 | Viduidae        | Indigobirds     |     NULL | Passeriformes     |
+-----------+-----------------+-----------------+----------+-------------------+

In the SELECT statement here, I added an ORDER BY clause to order the results set
by the value of the family_id. The DESC after
it indicates that the rows should by ordered in descending order based
on the value of family_id. The LIMIT clause
tells MySQL to limit the results to only one row. Looking
at this one row of data, we can see that the INSERT
INTO…SELECT
statement worked well.

A Digression: Setting the Right ID

Our INSERT from the previous section helped me fill
my table with data I took from a free database, but it’s still missing
data: the bird order for each bird. I defined my own orders of birds in
the bird_orders table, giving each order an arbitrary
order_id. However, the Cornell data had nothing to do with
the numbers assigned when I created my bird_orders table.
So now I need to set the value of the order_id column to
the right order_id from the bird_orders
table—and to figure out that value, I have to find the order in the
cornell_bird_order column.

This is a bit complicated, but I am showing my process here to
illustrate the power of relational databases. Basically, I’ll join my
own bird_orders table to the data I got from Cornell. I
loaded the bird orders from Cornell into a
cornell_bird_order field. I have the exact same orders in
the scientific_name field of my bird_orders
table. But I don’t want to use the scientific name itself when I label
each individual bird: instead, I want a number (an
order_id) to assign to that bird.

I need to set the value of the order_id column to the
right order_id from the bird_orders table. To
figure out that value, I have to find the order in the
cornell_bird_order column.

For that, I’ll use the UPDATE statement. Before I change any data with UPDATE, though,
I’ll construct a SELECT statement for testing. I want to
make sure my orders properly match up with Cornell’s. So I’ll enter this
on my server:

SELECT DISTINCT bird_orders.order_id,
cornell_bird_order AS "Cornell's Order", 
bird_orders.scientific_name AS 'My Order'
FROM bird_families, bird_orders
WHERE bird_families.order_id IS NULL
AND cornell_bird_order = bird_orders.scientific_name
LIMIT 5;

+----------+------------------+------------------+
| order_id | Cornell's Order  | My Order         |
+----------+------------------+------------------+
|      120 | Struthioniformes | Struthioniformes |
|      121 | Tinamiformes     | Tinamiformes     |
|      100 | Anseriformes     | Anseriformes     |
|      101 | Galliformes      | Galliformes      |
|      104 | Podicipediformes | Podicipediformes |
+----------+------------------+------------------+

We’re testing a WHERE clause here that we’ll use later when updating our
bird_families table. It’s worth looking at what a
WHERE clause give us before we put all our trust in it and
use it in an UPDATE statement.

This WHERE clause contains two conditions. First, it
changes the bird_families table only where the
order_id hasn’t been set yet. That’s kind of a sanity
check. If I already set the order_id field, there is no
reason to change it.

After the AND comes the second condition, which is
more important. I want to find the row in my bird_orders
table that has the right scientific name, the scientific name assigned
by Cornell. So I check where cornell_bird_order equals the
scientific_name in the bird_orders
table.

This shows how, if you want to change data with INSERT…SELECT,
REPLACE, or UPDATE, you can test your
WHERE clause first with a SELECT statement. If
this statement returns the rows you want and the data looks good, you
can then use the same WHERE clause with one of the other
SQL statements to change data.

The SELECT statement just shown is similar to the one
we executed in the previous section of this chapter when we queried the
birds, bird_families, and
bird_orders tables in the same SQL statement. There is,
however, an extra option added to this statement: the
DISTINCT option. This selects only rows in which all of the
columns are distinct. Otherwise, because more than five bird families
are members of the Struthioniformes order, and I
limited the results to five rows (i.e., LIMIT 5), we would
see the first row repeated five times. Adding the DISTINCT
flag returns five distinct permutations and is thereby more reassuring
that the WHERE clause is correct.

Because the results look good, I’ll use the UPDATE
statement to update the data in the bird_families table.
With this statement, you can change or update rows of data. The basic
syntax is to name the table you want to update and use the
SET clause to set the value of each column. This is like
the syntax for the SELECT statement in Inserting Emphatically. Use the WHERE
clause you tested to tell MySQL which rows to change:

UPDATE bird_families, bird_orders
SET bird_families.order_id = bird_orders.order_id
WHERE bird_families.order_id IS NULL
AND cornell_bird_order = bird_orders.scientific_name;

This is fairly complicated, so let’s reiterate what’s happening
here: the UPDATE statement tells MySQL to set the
order_id in the bird_families table to the
value of the order_id of the corresponding row in the
bird_orders table—but thanks to the AND
clause, I do the update only where the cornell_bird_order
equals the scientific_name in the bird_orders
table.

That’s plenty to take in, I know. We’ll cover this statement in
more detail in Chapter 8.

Let’s see the results now. We’ll execute the same SQL statement we
did earlier, but limit it to four rows this time to see a bit
more:

SELECT * FROM bird_families
ORDER BY family_id DESC LIMIT 4;

+-----------+-----------------+---------------------+----------+
| family_id | scientific_name | brief_description   | order_id |
+-----------+-----------------+---------------------+----------+
|       330 | Viduidae        | Indigobirds         |      128 |
|       329 | Estrildidae     | Waxbills and Allies |      128 |
|       328 | Ploceidae       | Weavers and Allies  |      128 |
|       327 | Passeridae      | Old World Sparrows  |      128 |
+-----------+-----------------+---------------------+----------+

That seems to have worked. The order_id column for
the Viduidae bird family now has a value other than
NULL. Let’s check the bird_orders to see whether that’s the
correct value:

SELECT * FROM bird_orders
WHERE order_id = 128;

+----------+-----------------+-------------------+-------------+
| order_id | scientific_name | brief_description | order_image |
+----------+-----------------+-------------------+-------------+
|      128 | Passeriformes   | Passerines        | NULL        |
+----------+-----------------+-------------------+-------------+

That’s correct. The order_id of 128 is for
Passeriformes, which is what the Cornell table said
is the order of the Viduidae family. Let’s see
whether any rows in bird_families are missing the
order_id:

SELECT family_id, scientific_name, brief_description
FROM bird_families
WHERE order_id IS NULL;

+-----------+-------------------+----------------------+
| family_id | scientific_name   | brief_description    |
+-----------+-------------------+----------------------+
|       136 | Fregatidae        | Frigatebirds         |
|       137 | Sulidae           | Boobies and Gannets  |
|       138 | Phalacrocoracidae | Cormorants and Shags |
|       139 | Anhingidae        | Anhingas             |
|       145 | Cathartidae       | New World Vultures   |
|       146 | Sagittariidae     | Secretary-bird       |
|       147 | Pandionidae       | Osprey               |
|       148 | Otididae          | Bustards             |
|       149 | Mesitornithidae   | Mesites              |
|       150 | Rhynochetidae     | Kagu                 |
|       151 | Eurypygidae       | Sunbittern           |
|       172 | Pteroclidae       | Sandgrouse           |
|       199 | Bucconidae        | Puffbirds            |
|       200 | Galbulidae        | Jacamars             |
|       207 | Cariamidae        | Seriemas             |
+-----------+-------------------+----------------------+

For some reason, the data didn’t match the 15 rows in the
bird_orders table. I had to determine why these didn’t
match. Let’s look at how I resolved a couple of them.

I looked up the name of the order to which the Osprey belongs and
found that there are two possible names:
Accipitriformes and
Falconiformes. Cornell used the
Accipitriformes, whereas my
bird_orders table has the
Falconiformes (i.e., order_id 112).
I’ll use that one and update the bird_families
table:

UPDATE bird_families
SET order_id = 112
WHERE cornell_bird_order = 'Accipitriformes';

I could have used the family_id in the
WHERE clause, but by doing what I did here, I discovered two more bird
families that are in the Accipitriformes order and
updated all three in one SQL statement. Digging some more, I found that
four of these bird families are part of a new order called
Suliformes. So I added that order to the
bird_orders table and then updated the rows for those
families in the bird_families table. This method of
clean-up is common when creating a database or when importing large
amounts of data from another database.

Next, I’ll do some clean-up by dropping the extra column I added
( cornell_bird_order) to the bird_families
table and the cornell_birds_families_orders table:

ALTER TABLE bird_families
DROP COLUMN cornell_bird_order;

DROP TABLE cornell_birds_families_orders;

That set of examples was complicated, so don’t be discouraged if
you were confused by it. In time, you will be constructing more complex
SQL statements on your own. In fact, you will come to look at what I did
here and realize that I could have performed the same tasks in fewer
steps. For now, I wanted to show you the power of MySQL and MariaDB, as
well as their communities. I mention the communities because in the
MySQL and MariaDB communities, you can sometimes find tables with data
like this that you can download for free and then manipulate for your
own use, thus saving you plenty of work and taking some of the ever
pesky tediousness out of database management. There are other methods
for bulk importing data, even when it’s not in a MySQL table. They’re covered in Chapter 15.

Replacing Data

When you’re adding massive amounts of data to an existing table
and you’re using the multiple-row syntax, you could have a problem if
one of the fields you’re importing gets inserted into a key field in the
table, as in the preceding example with the bird_families
table. In that example, the scientific_name column was a
key field, set to UNIQUE so that there is only one entry in
the birds_families table for each bird family. When MySQL
finds a duplicate key value while running an INSERT statement, an error is generated
and the entire SQL statement will be rejected. Nothing will be inserted
into the table.

You would then have to edit the INSERT statement,
which might be lengthy, to remove the duplicate entry and run the
statement again. If there are many duplicates, you’d have to run the SQL
statement many times, watch for error messages, and remove duplicates
until it’s successful. We avoided this problem in the previous example
by using the IGNORE option with the
INSERT statement. It tells MySQL to ignore the errors, not
insert the rows that are duplicates, and insert the ones that
aren’t.

There may be times, though, when you don’t want to ignore the
duplicate rows, but replace duplicate rows in the table with the new
data. For instance, in the UPDATE example in the previous
section, we have newer and better information, so we prefer to overwrite
duplicate rows. In situations such as this, instead of using
INSERT, you could use the REPLACE statement. With it, new rows of
data will be inserted as they would with an INSERT
statement. Any rows with the same key value (e.g., same
scientific_name code) will replace the matching row already
in the table. This can be very useful, and not difficult. Let’s look at
an example:

REPLACE INTO bird_families
(scientific_name, brief_description, order_id)
VALUES('Viduidae', 'Indigobirds & Whydahs', 128),
('Estrildidae', 'Waxbills, Weaver Finches, & Allies', 128),
('Ploceidae', 'Weavers, Malimbe, & Bishops', 128);

Query OK, 6 rows affected (0.39 sec)
Records: 3  Duplicates: 3  Warnings: 0

Notice that the syntax is the same as an INSERT
statement. The options all have the same effect as well. Also, multiple
rows may be inserted, but there’s no need for the IGNORE
option because duplicates are just overwritten.

Actually, when a row is replaced using the REPLACE
statement, it’s first deleted completely and the new row is then
inserted. For any columns without values, the default values for the
columns will be used. None of the previous values are kept. So be
careful that you don’t replace a row that contains some data that you
want. When you update a row with REPLACE, you can’t choose
to replace some columns and leave the others unchanged.
REPLACE replaces the whole row, unlike UPDATE. To change just specific
columns, use the UPDATE statement.

There are a couple of things that you should notice about this
REPLACE statement and the content we entered. You can see
something unusual in the results message. It says that six rows were
affected by this SQL statement: three new records and three duplicates.
The value of six for the number of rows affected may seem strange. What
happened is that because three rows had the same value for the
scientific_name, they were deleted. And then three new rows
were added with the new values, the replacements. That gives a total of
six affected rows: three deleted and three added.

The results contain no warnings, so all went well as far as MySQL
knows. Let’s look at the data for one of the bird families we changed in
the bird_families table, the Viduidae
family:

SELECT * FROM bird_families
WHERE scientific_name = 'Viduidae' \G

*************************** 1. row ***************************
        family_id: 331
  scientific_name: Viduidae
brief_description: Indigobirds & Whydahs
         order_id: 128

It may not be apparent, but everything was replaced. This row has
a new value in the family_id column. If you look earlier in
this chapter at the row for this family, you’ll see that the
family_id was 330. Because it was the last row in the
table, when a new row was created for its replacement, 331 was assigned
to it. The brief_description has the new value; it said
before only Indigobirds.

The REPLACE statement is useful for replacing all of
the data for a duplicate row and inserting new rows of data for data
that isn’t already in a given table. It has the potential problem of
replacing all of the columns when you might want to replace only some of
them. Also, in the previous examples, if the
scientific_name column was not UNIQUE or
otherwise a key column, new rows would be created for the three families
we tried to replace with the REPLACE statement.

Priorities When Inserting Data

On a busy MySQL or MariaDB server, there will be times when many people will access the
server at the same time. There will be times when SQL statements are
entered simultaneously from different sources, perhaps many at the same
instant. The server must decide which statements to process
first.

Statements that change data ( INSERT,
UPDATE, and DELETE) take priority over read statements ( SELECT
statements). Someone who is adding data to the server seems to be more
important than someone reading data. One concern is that the one
inserting data might lose the connection and lose its opportunity. The
user retrieving data, in contrast, can generally wait. For example, on a
website that uses MySQL to store purchases, a customer entering an order
will take priority over another customer who is just browsing through
the list of products.

When the server is executing an INSERT statement for
a client, it locks the related tables for exclusive access and forces
other clients to wait until it’s finished. This isn’t the case with
InnoDB: it locks the rows, rather than the entire table. On a busy MySQL
server that has many simultaneous requests for data, locking a table
could cause users to experience delays, especially when someone is
entering many rows of data by using the multiple-row syntax of the
INSERT statement.

Rather than accept the default priorities in MySQL, you can
instead set the priority for an INSERT. You can decide
which SQL statements need to be entered as soon as possible and which
can wait. To specify you preferences, the INSERT statement
offers priority options. Enter them between the INSERT
keyword and the INTO keyword. There are three of them:
LOW_PRIORITY, DELAYED, and
HIGH_PRIORITY. Let’s look at each of them.

Lowering the priority of an insert

For an example of LOW_PRIORITY, suppose that we’ve just received a file from a large
bird-watcher group with thousands of rows of data related to bird
sightings. The table is a MySQL dump file, a simple text file
containing the necessary SQL statements to insert the data into a
table in MySQL. We open the dump file with a text editor and see that
it contains one huge INSERT statement that will insert
all of the bird sightings (i.e., bird_sightings) with one
SQL statement into a table on our server. We haven’t created a table
like this yet, but you can imagine what it might contain.

When the INSERT statement in the dump file from the
large bird-watcher group is run, it might tie up our server for quite
a while. If there are users who are in the middle of retrieving data
from the bird_sightings table, we might prefer that those
processes finish before starting our huge INSERT
statement. The LOW_PRIORITY option instructs MySQL to
enter the rows when it’s finished with whatever else it’s doing.
Here’s an abbreviated version of how we would do that:

INSERT LOW_PRIORITY INTO bird_sightings
…

Of course, a real INSERT will have all the column
and value listings you want where I left the ellipsis (three
dots).

The LOW_PRIORITY flag puts the INSERT
statement in a queue, waiting for all of the current and pending
requests to be completed before it’s performed. If new requests are
made while a low priority statement is waiting, they are put ahead of
it in the queue. MySQL does not begin to execute a low priority
statement until there are no other requests waiting.

The table is locked and any other requests for data from the
table that come in after the INSERT statement starts must
wait until it’s completed. MySQL locks the table once a low priority
statement has begun so it will prevent simultaneous insertions from
other clients. The server doesn’t stop in the middle of an insert to
allow for other changes just because of the LOW_PRIORITY
setting. Incidentally, LOW_PRIORITY and
HIGH_PRIORITY aren’t supported by InnoDB tables. It’s
unnecessary because it doesn’t lock the table, but locks the relevant
rows.

One potential inconvenience with an INSERT
LOW_PRIORITY
statement is that your mysql client will be tied up waiting for the
statement to be completed successfully by the server. So if you’re
inserting data into a busy server with a low priority setting using
the mysql client, your client could
be locked up for minutes, maybe even hours, depending on how busy the
server is at the time. Using LOW_PRIORITY causes your
client to wait until the server starts the insert, and then the client
is locked, as well as the related tables on the server are
locked.

Delaying an INSERT

As an alternative, you can use the DELAYED option
instead of the LOW_PRIORITY option. This is
deprecated in 5.6.6 of MySQL. However, if you’re using an older
version, this is how you would use it:

INSERT DELAYED INTO bird_sightings
…

This is very similar to LOW_PRIORITY; MySQL will
take the request as a low-priority one and put it on its list of tasks
to perform when it has a break. The difference and advantage is that
it will release the mysql client
immediately so that the client can go on to enter other SQL statements
or even exit. Another advantage of this method is that multiple
INSERT DELAYED requests are batched together for block
insertion when there is a gap in server traffic, making the process
potentially faster than INSERT LOW_PRIORITY.

The drawback to this choice is that the client is never informed
whether the delayed insertion is actually made. The client gets back
error messages when the statement is entered—the statement has to be
valid before it will be queued—but it’s not told of problems that
occur after the SQL statement is accepted by the server.

This brings up another drawback: delayed insertions are stored
in the server’s memory. So if the MySQL daemon dies or is manually
killed, the inserts are lost and the client is not notified of the
failure. You’ll have to manually check the database or the server’s
logs to determine whether the inserts failed. As a result, the
DELAYED option is not always a good alternative.

Raising the priority of an INSERT

The third priority option for the INSERT statement
is HIGH_PRIORITY. INSERT
statements by default are usually given higher priority over read-only
SQL statements so there would seem to be no need for this option.
However, the default of giving write statements priority over read
statements (e.g., INSERT over SELECT) can be
removed. Post-Installation touched on the
configuration of MySQL and MariaDB. One of the server options that may
be set is --low-priority-updates. This will make write
statements by default a low priority statement, or at least equal to
read-only SQL statements. If a server has been set to this default
setting, you can add the HIGH_PRIORITY option to an
INSERT statement to override the default setting of
LOW_PRIORITY so that it has high priority over read
statements.

Summary

At this point, you should have a good understanding of MySQL and
MariaDB. You should understand the basic structure of a database and its
tables. You should now see the value of having smaller multiple tables.
You should no longer envision a database as one large table or like a
spreadsheet. You should have a good sense of columns and how to enter data
into them, especially if you have done all of the exercises at the end of
the previous two chapters. You should not be overwhelmed at this
point.

Chapter 7 delves more deeply into how
to retrieve data from tables using the SELECT statement. We
have already touched on this SQL statement several times. However, you saw
only a sampling of how you might use SELECT in this chapter
and in previous ones, to give you a sense of why we were creating and
adding data the way we did to tables. The next chapter will cover the
SELECT statement in much more detail.

The INSERT, SELECT, and the
UPDATE statements are the most used SQL statements. If you
want to learn MySQL and MariaDB well, you need to know these statements
well. You need to know how to do the basics, as well as be familiar with
the more specialized aspects of using SELECT. You’ll
accomplish that in the next chapter.

Before moving on to the next chapter, though, complete the following
exercises. They will help you to retain what you’ve learned about the
INSERT statement in this chapter. Don’t skip them. This is
useful and necessary to building a solid foundation in learning MySQL and
MariaDB.

Exercises

Here are some exercises to get practice using the
INSERT statement and a few others that we covered in this
chapter. So that these exercises won’t be strictly mundane data entry, a
couple of them call for you to create some tables mentioned in this
chapter. The practice of creating tables will help you to understand data
entry better. The process of entering data will help you to become wiser
when creating tables. Both inform each other.

  1. In the exercises at the end of Chapter 4, you were asked to create a table
    called birds_body_shapes. This table will be used for
    identifying birds. It will be referenced from the birds
    table by way of the column called body_id. The table is
    to contain descriptions of body shapes of birds, which is a key factor
    in identifying birds: if it looks like a duck, walks like a duck, and
    quacks like a duck, it may be a goose—but it’s definitely not a
    hummingbird. Here is an initial list of names for general shapes of
    birds:

    Hummingbird
    Long-Legged Wader
    Marsh Hen
    Owl
    Perching Bird
    Perching Water Bird
    Pigeon
    Raptor
    Seabird
    Shore Bird
    Swallow
    Tree Clinging
    Waterfowl
    Woodland Fowl

    Construct an INSERT statement using the
    multiple-row syntax—not the emphatic method—for inserting data into
    the birds_body_shapes table. You’ll have to set the
    body_id to a three-letter code. You decide on that, but
    you might base it somewhat on the names of the shapes themselves
    (e.g., Marsh Hen might be
    MHN and Owl might be simply
    OWL). Just make sure each ID is unique. For the
    body_shape column, use the text I have just shown, or
    reword it if you want. For now, skip the third column,
    body_example.

  2. You were asked also in the exercises at the end of Chapter 4 to create another table for
    identifying birds, called birds_wing_shapes. This
    describes the shapes of bird wings. Here’s an initial list of names
    for general wing shapes:

    Broad
    Rounded
    Pointed
    Tapered
    Long
    Very Long

    Construct an INSERT statement to insert these items
    into the birds_wing_shapes table using the emphatic
    syntax—the method that includes the SET clause. Set the
    wing_id to a two-letter code. You decide these values, as
    you did earlier for body_id. For the
    wing_shape column, use the text just shown. Don’t enter a
    value for the wing_example column yet.

  3. The last bird identification table in which to enter data is
    birds_bill_shapes. Use the INSERT statement
    to insert data into this table, but whichever multiple-row method you
    prefer. You determine the two-letter values for bill_id.
    Don’t enter values for bill_example. Use the following
    list of bill shapes for the value of bill_shape:

    All Purpose
    Cone
    Curved
    Dagger
    Hooked
    Hooked Seabird
    Needle
    Spatulate
    Specialized
  4. Execute a SELECT statement to view the row from the
    birds_body_shapes table where the value of the
    body_shape column is Woodland Fowl.
    Then replace that row with a new value for the body_shape
    column. Replace it with Upland Ground Birds. To
    do this, use the REPLACE statement, covered in Replacing Data. In the VALUES
    clause of the REPLACE statement, provide the same value
    previously set for the body_id so that it is not
    lost.

    After you enter the REPLACE statement, execute a
    SELECT statement to retrieve all the rows of data in the
    birds_body_shapes table. Look how the data changed for
    the row you replaced. Make sure it’s correct. If not, try again either
    using REPLACE or UPDATE.

Comments are closed.