3.5. Inserting, Updating, and Deleting Data
The Data Manipulation Language (DML) encompasses all SQL statements used for manipulating data. There are four statements that form the DML statement set: SELECT, INSERT, DELETE, and UPDATE. We describe the last three statements in this section. While SELECT is also part of DML, we cover it in its own section, Section 3.6. Longer worked examples using all the statements can be found in the section Section 3.8.
3.5.1. Inserting Data
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 and—because 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 auto_increment and DEFAULT modifiers.
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.
126.96.36.199. Bulk loading into a database
Another data insertion method is to bulk-load data from a formatted ASCII text file. A formatted text file is usually a comma-delimited (also known as a comma-separated) or tab-delimited file, where the values to be inserted are separated by comma or tab characters, respectively.
The statement LOAD DATA INFILE can bulk-load data from a file. This is nonstandard SQL. For example, consider the following customer information that has been exported from a legacy spreadsheet program:
0,"Marzalla","Dimitria","F","Mrs","171 Titshall Cl","","","St Albans","WA","7608","Australia", "(618)63576028","","email@example.com", "1969-08-11","35000"
The data might be saved in the file customer.cdf. Note that the attribute values are in the same order as the attributes in the winestore customer table; most export wizards in spreadsheet software allow data to be reorganized as it is exported. Also, note that the first value is 0 and, because this value will be inserted into the cust_id attribute, the auto_increment feature assigns the next available cust_id value; inserting 0 has the same effect as inserting NULL.
The file can be inserted into the customer table using the statement:
LOAD DATA INFILE 'customer.cdf' INTO TABLE customer FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
If quotation marks form part of an attribute, they must be escaped using backslashes:
"RMB 123, \"The Lofty Heights\""
Spreadsheet software often automatically escapes quotation marks in strings when data is exported.
188.8.131.52. Transferring data between databases and DBMSs
For many databases—particularly those in which legacy data is being redeployed into a DBMS—most of the data insertion occurs as the database is created. A common approach is to create a script that contains SQL statements that can be repeatedly replayed; it's the approach we used to create the winestore database. This has the advantage that the script can be run on many different DBMSs, and it makes migration easier than with the LOAD DATA INFILE approach.
To remove and partially rebuild the winestore database, we might author a script containing the statements shown in Example 3-3.
Example 3-3. Script for creating and inserting winestore data
DROP DATABASE winestore; CREATE DATABASE winestore; use winestore CREATE TABLE customer ( cust_id int(5) NOT NULL auto_increment, surname varchar(50) NOT NULL, firstname varchar(50) NOT NULL, initial char(1), title varchar(10), addressline1 varchar(50) NOT NULL, addressline2 varchar(50), addressline3 varchar(50), city varchar(20) NOT NULL, state varchar(20), zipcode varchar(5), country varchar(20), phone varchar(15), fax varchar(15), email varchar(30) NOT NULL, birthdate date( ), salary int(7), PRIMARY KEY (cust_id), KEY names (surname,firstname) ); INSERT INTO customer VALUES (NULL,'Marzalla','Dimitria', 'F','Mrs','171 Titshall Ccl','','','St Albans','WA', '7608','Australia','(618)63576028','', 'Dimitria@Lucaston. com','1969-08-11',35000); INSERT INTO customer VALUES (NULL,'LaTrobe','Anthony', 'Y','Mr','125 Barneshaw St','','','Westleigh','WA','865 5','Australia','(618)73788578','(618)73786674', 'Anthony@Karumba.com','1952-03-10',54000); INSERT INTO customer VALUES (NULL,'Fong','Nicholas','K','Mr','99 Kinsala Pl', '','','Stormlea','NSW','6400','Australia', '(612)85534220','(612)85535180','Nicholas@Torquay.com', '1942-06-29',170000); INSERT INTO customer VALUES (NULL,'Stribling','James','','Mr','6 Woodburne Pl','','', 'Legana','QLD','6377','Australia','(617)66603522', '','James@Murrabit.com', '1943-11- 22',25000);
The script in Example 3-3, which has been saved to a file winestore.database, can be replayed using the MySQL command and a shell redirection:
% mysql -ppassword < winestore.database
This script runs the command interpreter with the statements and commands listed in the file winestore.database.
Data that is already managed in a MySQL database can be extracted using the utility mysqldump:
% mysqldump -ppassword winestore > winestore.database
The statements to DROP, CREATE, and use the database can be manually added with an editor to permit replaying of the script. We manually added the first three lines of Example 3-3 after using mysqldump to create the script.
To use the script to create a duplicate database, winestore2, for testing, you can change the first three lines of Example 3-3 to:
DROP DATABASE winestore2; CREATE DATABASE winestore2; use winestore2
3.5.2. Deleting Data
DELETE FROM customer;
deletes all data in the customer table but doesn't remove the table. In contrast, dropping the table removes the data and the table.
A DELETE statement with a WHERE clause can remove specific rows; WHERE clauses are frequently used in querying, and they are explained later in Section 3.8.3. Consider a simple example:
DELETE FROM customer WHERE cust_id = 1;
This deletes the customer with cust_id=1. Consider another example:
DELETE FROM customer WHERE surname = 'Smith';
This removes all rows for customers with the surname Smith.
3.5.3. Updating Data
UPDATE customer SET email = lower(email);
This replaces the string values of all email attributes with the same string in lowercase. The function lower( ) is one of many functions discussed later in Section 3.9.
UPDATE customer SET title = 'Dr' WHERE cust_id = 7;
This updates the title attribute of customer #7. Consider a second example:
UPDATE customer SET zipcode = '3001' WHERE city = 'Melbourne';
Copyright © 2003 O'Reilly & Associates. All rights reserved.