3.5.1. Inserting Data

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','',
          'dimitria@lucaston.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',
      email='dimitria@lucaston.com',
      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');

3.5.2. Deleting Data

The statement:

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.

3.5.3. Updating Data

UPDATE customer SET email = lower(email);

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';