Having created a database and the accompanying tables and indexes, the next step is to insert data. Inserting a row of data into a table can follow two different approaches. We illustrate both approaches by inserting the same data for a new customer, Dimitria Marzalla.
Consider an example of the first approach using the customer table:
INSERT INTO customer VALUES (NULL,'Marzalla','Dimitria', 'F','Mrs', '171 Titshall Cl','','','St Albans','WA', '7608','Australia','(618)63576028','', 'email@example.com','1969-11-08',35000);
In this approach a new row is created in the customer table, then the first value listed-in this case, a
NULL-is inserted into the first attribute of customer. The first attribute of customer is
cust_id has the
auto_increment modifier and this is the first row-a 1 is inserted as the
cust_id. The value "Marzalla" is then inserted into the second attribute
surname, "Dimitria" into
firstname, and so on. The number of values inserted must be the same as the number of attributes in the table. To create an
INSERT statement in this format, you need to understand the ordering of attributes in the table.
The number inserted by an
auto_increment modifier can be checked with the MySQL-specific function
last_insert_id( ). In this example, you can check which
cust_id was created with the statement:
SELECT last_insert_id( );
This statement reports:
+------------------+ | last_insert_id( ) | +------------------+ | 1 | +------------------+ 1 row in set (0.00 sec)
You can see that the new row has
cust_id=1. To check a value, the function should be called immediately after inserting a new row.
When inserting data, nonnumeric attributes must be enclosed in either single or double quotes. If a string contains single quotation marks, the string is enclosed in double quotation marks. For example, consider the string "Steve O'Dwyer". Likewise, strings containing double quotation marks can be enclosed in single quotation marks. An alternative approach is to escape the quotation character by using a backslash character; for example, consider the string 'Steve O\'Dwyer'. Numeric attributes aren't enclosed in quotes.
The same insertion can also be performed using a second approach. Consider this example:
INSERT INTO customer SET surname = 'Marzalla', firstname = 'Dimitria', initial='F', title='Mrs', addressline1='171 Titshall Cl', city='St Albans', state='WA', zipcode='7608', country='Australia', phone='(618)63576028', firstname.lastname@example.org', birthdate='1969-11-08', salary=35000;
In this approach, the attribute name is listed, followed by an assignment operator, "
=", and then the value to be assigned. This approach doesn't require the same number of values as attributes, and it also allows arbitrary ordering of the attributes.
cust_id isn't inserted, and it defaults to the next available
cust_id value because of the combination of the
The first approach can actually be varied to function in a similar way to the second by including parenthesized attribute names before the
VALUES keyword. For example, you can create an incomplete customer row with:
INSERT INTO customer (surname,city) VALUES ('Smith','Sale');
Other approaches to loading data using a similar syntax are also possible. A popular variation is to insert data into a table from another table using a query, and it's discussed briefly in Section 3.8.3.