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.sqluse 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:
|