We have created the perfect tables to hold our data, but so far they are empty. It is time that we populate them. We delayed this moment as altering tables with data is more difficult than when they are empty.

In order to insert this data, we will use the INSERT INTO command. This command will take the name of the table, the fields that you want to populate, and the data for each field. Note that you can choose not to specify the value for a field, and there are different reasons to do this, which are as follows:

There are different reasons that can cause an INSERT INTO command to fail:

Let's take a look at how to add rows. Let's start with our customer table, adding one basic and one premium, as follows:

mysql> INSERT INTO customer (firstname, surname, email, type)
    -> VALUES ("Han", "Solo", "[email protected]", "premium");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer (firstname, surname, email, type)
    -> VALUES ("James", "Kirk", "enter@prise", "basic");
Query OK, 1 row affected (0.00 sec)

Note that MySQL shows you some return information; in this case, it shows that there was one row affected, which is the row that we inserted. We did not provide an ID, so MySQL just added the next ones in the list. As it is the first time that we are adding data, MySQL used the IDs 1 and 2.

Let's try to trick MySQL and add another customer, repeating the e-mail address field that we set as unique in the previous section:

mysql> INSERT INTO customer (firstname, surname, email, type)
    -> VALUES ("Mr", "Spock", "enter@prise", "basic");
ERROR 1062 (23000): Duplicate entry 'enter@prise' for key 'email'

An error is returned with an error code and an error message, and the row was not inserted, of course. The error message usually contains enough information in order to understand the issue and how to fix it. If this is not the case, we can always try to search on the Internet using the error code and note what either the official documentation or other users have to say about it.

In case you need to introduce multiple rows to the same table and they contain the same fields, there is a shorter version of the command, in which you can specify the fields and then provide the groups of values for each row. Let's take a look at how to use it when adding books to our book table, as follows:

mysql> INSERT INTO book (isbn,title,author,stock,price) VALUES
    -> ("9780882339726","1984","George Orwell",12,7.50),
    -> ("9789724621081","1Q84","Haruki Murakami",9,9.75),
    -> ("9780736692427","Animal Farm","George Orwell",8,3.50),
    -> ("9780307350169","Dracula","Bram Stoker",30,10.15),
    -> ("9780753179246","19 minutes","Jodi Picoult",0,10);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

As with customers, we will not specify the ID and let MySQL choose the appropriate one. Note also that now the amount of affected rows is 5 as we inserted five rows.

How can we take advantage of the explicit defaults that we defined in our tables? Well, we can do this in the same way as we did with the primary keys: do not specify them in the fields list or in the values list, and MySQL will just use the default value. For example, we defined a default value of 1 for our book.stock field, which is a useful notation for the book table and the stock field. Let's add another row using this default, as follows:

mysql> INSERT INTO book (isbn,title,author,price) VALUES
    -> ("9781416500360", "Odyssey", "Homer", 4.23);
Query OK, 1 row affected (0.00 sec)

Now that we have books and customers, let's add some historic data about customers borrowing books. For this, use the numeric IDs from book and customer, as in the following code:

mysql> INSERT INTO borrowed_books(book_id,customer_id,start,end)
    -> VALUES
    -> (1, 1, "2014-12-12", "2014-12-28"),
    -> (4, 1, "2015-01-10", "2015-01-13"),
    -> (4, 2, "2015-02-01", "2015-02-10"),
    -> (1, 2, "2015-03-12", NULL);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0