Learning MySQL and MariaDB (2015)

Part III. Basics of Handling Data

The main point of a database is data. In Part II, you learned how to create and alter tables. As interesting as that may have been, the data that will go in tables is essential. If you felt a little confused when creating and altering tables in the previous chapters, it may be because it’s difficult to envision how tables and their columns will come into play with data, without having more experience adding data.

In this part, we will explore some of the fundamental ways in which data may be entered into a database and inserted into tables. This will be covered in Chapter 6, Inserting Data. It primarily involves the INSERT statement. The SQL statement for retrieving data from tables is the SELECTstatement, which is covered extensively in Chapter 7, Selecting Data. You’ve seen both of these SQL statements in use several times in the previous chapters. However, in the next two chapters you will learn more about the various syntax and options for each of them, and you will be given plenty of practical examples of their use.

Data often needs to be changed and sometimes deleted, so in Chapter 8, Updating and Deleting Data we’ll take a look at how to update and delete data. This chapter will help you to learn how to use the UPDATE and the DELETE statements to do these common tasks. These are important for managing data in a database.

The final chapter of this part, Chapter 9, Joining and Subquerying Data, is an advanced one. It’s not too difficult to follow, but you should definitely not rush through it. In it, you will learn how to select data from one or more tables, and to use that data as a basis for inserting, selecting, updating, or deleting data in other tables. Thus, you should make sure that you’ve mastered the material in the previous chapters before skipping ahead to Chapter 9.

In each chapter of this part, there are practical examples that are used to explain the various SQL statements and related factors. You should enter those examples into your server. Even if you are reading this book from a digital version on your computer, I recommend highly that you manually type all of the SQL statements you are instructed to enter. It may seem like a little thing, but the process of typing them will aid your learning process and help you remember the syntax and the deviations of each SQL statement. When you make a mistake and type something incorrectly, you’ll get an error message. Deciphering error messages is part of being a good MySQL and MariaDB developer. If you copy and paste everything as I present it to you, you will only confirm the accuracy of the book’s examples, and you will learn only a little. It’s easy to learn when you don’t make any mistakes. It’s more difficult, but you will learn more when you manually enter the SQL statements and get errors and then have to determine where you went wrong.

At the end of each chapter of this part, as with almost all of the chapters in this book, there are exercises. For the same reasons that you should enter the SQL statements in the examples throughout the chapters, you should also complete the exercises. This is not just a book to be read. It’s meant to be a tool to help you to learn MySQL and MariaDB. To accomplish that, you must do more than just read the chapters: you need to participate, experiment, and research. If you make this kind of effort, you will benefit greatly from this book. This is probably the most essential part of the book, so you should fully engage with these concluding chapters.

Chapter 6. Inserting Data

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 thefamily_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;

+-------------------+--------------+------+-----+---------+----------------+

+-------------------+--------------+------+-----+---------+----------------+

| order_id | int(11) | NO | PRI | NULL | auto_increment |

+-------------------+--------------+------+-----+---------+----------------+

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. Theorder_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_orderstable; 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_imagecolumn, 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 thebird_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 Loonbelongs — 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 DELETEstatement 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 thefamily_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;

+------------------------+--------------+------+-----+-------+----------------+

+------------------------+--------------+------+-----+-------+----------------+

| bird_id | int(11) | NO | PRI | NULL | auto_increment |

| family_id | int(11) | YES | | NULL | |

| conservation_status_id | int(11) | 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';

+------------------------+---------+------+-----+---------+----------------+

+------------------------+---------+------+-----+---------+----------------+

| bird_id | int(11) | NO | PRI | NULL | auto_increment |

| family_id | int(11) | YES | | NULL | |

| conservation_status_id | int(11) | 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 SELECTstatements, 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;

+-----------------------+----------------------+--------------+---------------+

+-----------------------+----------------------+--------------+---------------+

+-----------------------+----------------------+--------------+---------------+

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 thebird_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;

+-------------+--------------+------+-----+---------+----------------+

+-------------+--------------+------+-----+---------+----------------+

| fid | int(11) | NO | PRI | NULL | auto_increment |

+-------------+--------------+------+-----+---------+----------------+

SELECT * FROM cornell_birds_families_orders

LIMIT 1;

+-----+---------------+----------+------------------+

+-----+---------------+----------+------------------+

+-----+---------------+----------+------------------+

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 thebird_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;

+-----------+-----------------+-----------------+----------+-------------------+

+-----------+-----------------+-----------------+----------+-------------------+

+-----------+-----------------+-----------------+----------+-------------------+

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 thescientific_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 thecornell_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;

+-----------+-----------------+---------------------+----------+

+-----------+-----------------+---------------------+----------+

| 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;

+----------+-----------------+-------------------+-------------+

+----------+-----------------+-------------------+-------------+

+----------+-----------------+-------------------+-------------+

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 thebird_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 calledSuliformes. 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, thescientific_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 theINSERT 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 INSERTstatement. 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

2.
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

3.
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.