Using INSERT

The INSERT SQL statement is used to insert rows into a table. We'll begin by looking at an example. Again, you can type these statements directly into the MySQL monitor or into a file, or you can download this file from the book's Web site.

Some sample insert statements are shown in Listing 5.1.

Listing 5.1 employee_data.sql
use employee;

delete from department;
insert into department values
(42, 'Finance'),
(128, 'Research and Development'),
(NULL, 'Human Resources'),
(NULL, 'Marketing');

delete from employee;
insert into employee values
(7513,'Nora Edwards','Programmer',128),
(9842, 'Ben Smith', 'DBA', 42),
(6651, 'Ajay Patel', 'Programmer', 128),
(9006, 'Candy Burnett', 'Systems Administrator', 128);

delete from employeeSkills;
insert into employeeSkills values
(7513, 'C'),
(7513, 'Perl'),
(7513, 'Java'),
(9842, 'DB2'),
(6651, 'VB'),
(6651, 'Java'),
(9006, 'NT'),
(9006, 'Linux');

delete from client;
insert into client values
(NULL, 'Telco Inc', '1 Collins St Melbourne', 'Fred Smith', '95551234'),
(NULL, 'The Bank', '100 Bourke St Melbourne', 'Jan Tristan', '95559876');

delete from assignment;
insert into assignment values
(1, 7513, '2003-01-20', 8.5);

You'll see that before we insert any data into each table, we are running a DELETE statement—again, this isn't necessary, but it will clean out any test data that you may have inserted so far. We'll come back to the DELETE statement in the next section.

Also note that we have inserted the sample data to match the examples in Chapter 3, "Database Design Crash Course." We have added some additional rows as well.

All of these INSERT statements are pretty similar. Let's look at the first one to see how it works:

insert into department values
(42, 'Finance'),
(128, 'Research and Development'),
(NULL, 'Human Resources'),
(NULL, 'Marketing');

We specify the table that we want to insert data into on the first line—in this case, department. Here, we are inserting four rows into the table. You may recall that the department table has two columns, departmentID and name. (You can check this for yourself by running a describe department command.)

In the first two rows, we have specified the departmentID that we want to use. Let's look back at the definition of departmentID. You may recall that in the last chapter, we declared it as

departmentID int not null auto_increment primary key

Because this is an auto_increment column, we can specify the value or let MySQL calculate it for us. (Usually, in this case, we would let MySQL allocate a number, but there may be cases like this one in which we have an existing number we want to use.)

In the rows for Human Resources and Marketing, you will see that we have left the departmentID as NULL. This will allow the auto_increment to do its magic and allocate a value. Let's see what we get from this INSERT statement.

If you look through the various INSERT statements, you will see that when we insert data into a string or date type, we enclose it in single quotes, for example, 'Research and Development'. When it is a numerical type, you should not use quotes.

If we are enclosing data in quotes, what do we do when the data contains quotes? The answer is that we need to escape the quotes. In simple terms, we need to put a backslash (\) in front of the single quote, for example, 'O\'Leary'.

Obviously, this brings up the question, "What do we do if we want a backslash to be just a backslash, without any special meaning?" In this case, we need to escape the backslash in the same way—replace the backslash with two backslashes (\\).

We retrieve data from the database using the SELECT statement. We will cover SELECT fairly exhaustively in the next few chapters. For the moment, we only need to know that typing

select * from tablename;

will return all the data currently stored in a table.

If you type

select * from department;

you should get output similar to the following:

+---------------+----------------------+
| departmentID | name |
+---------------+----------------------+
| 42 | Finance |
| 128 | Research and Develop |
| 129 | Human Resources |
| 130 | Marketing |
+---------------+----------------------+
4 rows in set (0.01 sec)

You should be able to see that the effect of auto_increment is a value which is one greater than the highest current value in the column.

The general form of the INSERT statement from the MySQL manual is as follows:

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
VALUES ((expression | DEFAULT),...),(...),...
[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...

or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] tbl_name
SET col_name=(expression | DEFAULT), ...
[ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

The examples we have looked at all follow the first form of the expression. You will note that the keyword INTO is optional. We could leave it out and begin our query with insert employee values, but we find it a little harder to read.

With this first form, we need to list values for each column value in each row in the same order as the columns are in the table. For example, we had to specify the departmentID first, followed by the name, because that's the way the department table is structured. As demonstrated, this form allows us to insert multiple rows in a table with a single INSERT statement.

The second form ends in a SELECT statement. Rather than inserting values manually, this allows us to retrieve data from another table or tables in the database and store it in this table.

The third form allows us to specify which columns data should be inserted into. An example of using INSERT in this way is

insert into department
set name='Asset Management';

This form only allows you to insert a single row at a time, but you don't need to specify values for all the columns. In this case, we are setting a value for only the name. All the unspecified values will either take their default value, if one is specified, or be NULL. In this case, departmentID will be set to NULL, which causes the auto_increment to work its magic and generate a new departmentID for us. (You can check this by typing select * from department again.)

There are a couple of optional clauses in the INSERT statement. Let's briefly go through what these do:

We can specify that an INSERT should be LOW PRIORITY or DELAYED. Both of these clauses will cause the insertion to be delayed until no client is trying to read from the table. The difference between them is that LOW PRIORITY will block the inserting client and DELAYED will not. What this means is that if you run a LOW PRIORITY insert, you may wait for some time before you can continue running queries in your client. With DELAYED, you will be told OK and can continue running queries, but you need to remember that the insert will not be performed until the table is not in use.

Specifying IGNORE is chiefly useful when you are inserting multiple rows. Normally, if one of the rows you are trying to insert clashes with an existing row's PRIMARY KEY or UNIQUE value, an error will occur and the insert will be aborted. If you specify IGNORE, the error will be ignored and the insert will continue and will attempt to insert the next row.

We can specify that a column should contain its default value by specifying DEFAULT as the value for that column.

The ON DUPLICATE KEY UPDATE clause allows us to deal elegantly with clashing primary key or unique values. We follow this clause with an UPDATE statement that we can use to change the primary key or unique value in the row already in the table so that it no longer clashes with the new row.

The following short example demonstrates a common style of use for the ON DUPLICATE KEY UPDATE clause:

create table warning
(
employeeID int primary key not null references employee(employeeID),
count int default 1
) type =InnoDB;

insert into warning (employeeID)
values (6651)
on duplicate key update count=count+1;

This clause is very useful for situations in which you want to not only record unique events, but also take some action, such as incrementing a counter when non-unique events occur. Any sort of logging would be a good example, but in keeping with the employee database we have been using, we will record employees who have been given a warning in the table warning.

To record somebody's warning, we run this INSERT statement. Because count has a default value of 1 and we are not specifying a value in the INSERT, it will be 1 the first time the insert is done for each employeeID. Subsequent inserts with the same employeeID will trigger the ON DUPLICATE KEY UPDATE clause and will increment the counter.