Web Database Applications With Php And Mysql (2nd Edition) [Electronic resources] نسخه متنی

5.4 Inserting, Updating, and Deleting Data

There are four major statements
for working with data in SQL: SELECT,
INSERT, DELETE, and
UPDATE. We describe the latter three statements in
this section. SELECT is covered it in its own
section later in this chapter.

5.4.1 Inserting Data

Having created a database and the
accompanying tables and indexes, the next step is to insert data into
the tables. Inserting a row can follow two different approaches. We
show both approaches by inserting the same data for a new customer,
Lucy Williams.

Consider an example of the first approach using the
customer table:

INSERT INTO customer VALUES (1,'Williams','Lucy','E',3,
'272 Station St','Carlton North','VIC','3054',12,'(613)83008460',
'2002-07-02');

The statement creates a new row in the customer
table, then the first value 1 is inserted into the
first attribute, cust_id. The
second value 'Williams' is inserted into the
second attribute surname,
'Lucy' into firstname, and so
on.

The number of values inserted is the same as the number of attributes
in the table (and an error is generated if the number of values
doesn't match the number of attributes). If you
don't want to supply data for an attribute, you can
include NULL instead of a value (as long as the
attribute isn't defined as NOT
NULL and NULL is valid for that data
type). For example, to create a partial customer row, you could use:

INSERT INTO customer VALUES (1,'Williams','Lucy',NULL,3,
NULL,NULL,NULL,NULL,12,NULL,NULL);

To create an INSERT statement using this first
format, you need to know the ordering of the attributes in the table.
You can discover the table structure by typing
SHOW COLUMNS
FROM customer into the MySQL
command interpreter or by reviewing the CREATE
TABLE statement used to create the table. The
SHOW statement is described in detail in Chapter 15.

If you want to insert more than one row, you can write more than one
INSERT statement. Alternatively, you can write one
INSERT statement and separate each row with a
comma. Consider an example that uses the latter approach and inserts
the details for two customers:

INSERT INTO customer VALUES (1,'Williams','Lucy','E',3,
'272 Station St','Carlton North','VIC','3054',12,'(613)83008460',
'2002-07-02'), (2,'Williams','Selina','J',4,'12 Hotham St',
'Collingwood','VIC','3066',12,'(613)99255432','1980-06-03');

This approach is the fastest way to insert data into MySQL.

Data can also be inserted using a second approach. Consider this
example:

INSERT INTO customer SET cust_id = 1, surname = 'Williams', 
firstname = 'Lucy', initial='E', title_id=3, 
address='272 Station St', city='Carlton North',
state='VIC', zipcode='3054', country_id=12,
phone='(613)83008460', birth_date='2002-07-10';

In this approach, the attribute name is listed, followed by the
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. This can save you lots of typing when a row has
many attributes but is sparsely populated with values. For example,
to create a partial customer row, you could use:

INSERT INTO customer SET cust_id = 653, surname = 'Williams',
firstname = 'Lucy', title_id = 3, country_id = 12;

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 (cust_id, surname, city) 
VALUES (1, 'Williams','North Carlton');

When inserting data, non-numeric attributes must be enclosed in
either single or double quotes. If a string contains single quotation
marks, the string can be 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, as in the
string `Steve
O\'Dwyer'. Numeric values
can also be enclosed in quotes but they aren't
mandatory.

There are other ways to insert data in addition to those discussed
here. For example, a popular variation is to insert data from another
table using a query or to insert data from a formatted text file.
These two approaches and other variants are discussed in Chapter 15.

5.4.1.1 Defaults

If you don't include the value for an attribute, it
is set to the DEFAULT value if
it's supplied in the table definition or to
NULL otherwise (if it is valid for the attribute
to be NULL). If an attribute is defined as being
NOT NULL and does not have a
DEFAULT value, the value that's
set depends on the attribute type; for example, integer attributes
are set to 0 (which causes an auto_increment
attribute to be populated with a new identifier, as discussed next)
and strings to the empty string. However, rather than worry about
what happens, we recommend that you define a
DEFAULT value for any attribute that you
don't always want to list in an
INSERT statement. Even if you want
NULL to be inserted when nothing is provided, you
can define it as the DEFAULT.

Inserting NULL into a TIMESTAMP
(or any date or time type) attribute stores the current date and
time. Inserting 0 into a TIMESTAMP attribute
doesn't have the same effect as inserting
NULL, because 0 is a valid date and time
combination.

5.4.1.2 Auto-increment

MySQL provides a non-standard SQL auto_increment
modifier that makes management of primary keys easy; most other
database servers provide a similar non-standard feature. The goal of
using auto_increment is to make sure that each row
in your table has a unique primary key so that you can refer to it in
other tables; as discussed previously, this is a common requirement
in databases.

The following is a simple table definition that uses the
auto_increment feature to create a unique value
for the primary key:

CREATE TABLE names (
id smallint(4) NOT NULL auto_increment,
name varchar(20),
PRIMARY KEY (id)
);

You can insert data into this table by setting only the
name attribute:

INSERT INTO names SET name = "Bob";

In this example, the id is set to the next
available identifier because the default value of an integer
attribute is 0 and this invokes the auto_increment
feature.

In general, when you insert NULL (or zero) as the
next value for an attribute with the
auto_increment modifier, the value that is stored
is the maximum value + 1. For example, if there are already 10 rows
in the names table with id
values of 1 to 10, inserting a row with NULL as
the id (or not providing an id
and invoking the default behavior) creates a row with an
id value of 11.

The auto_increment modifier is a useful feature
when you want to insert data with a unique primary key, but
don't want to have to read the data first to
determine the next available value to use. As we show you later in
Chapter 8, this also helps avoid concurrency
problems (and, therefore, the need for locking) when several users
are using the same database. The disadvantage is that
it's a proprietary MySQL feature. However, we also
show you how to develop a generic approach to managing identifiers in
Chapter 9 and we also show you how
it's done with PHP's PEAR DB.

Only one attribute in a table can have the
auto_increment modifier.

The result of an auto_increment modifier can be
checked with the MySQL-specific function last_insert_id(
). For the previous example, you can check which
id was created with the statement:

SELECT last_insert_id( );

This statement reports:

+------------------+
| last_insert_id( ) |
+------------------+
|               11 |
+------------------+
1 row in set (0.04 sec)

You can see that the new row has id=11. To check
an identifier value, the function should be called immediately after
inserting the new row.

5.4.2 Deleting Data

The
DELETE

statement removes data from tables.
For example, the following deletes all data in the
customer table but doesn't
remove the table:

DELETE FROM customer;

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 5.5.
Consider a simple example:

DELETE FROM customer WHERE cust_id = 1;

This deletes the customer with a cust_id value of
1. Consider another example:

DELETE FROM customer WHERE surname = 'Smith';

This removes all rows for customers with a surname
value of Smith.

5.4.3 Updating Data

Data can be updated using a similar
syntax to the INSERT statement. Consider an
example:

UPDATE customer SET state = upper(state);

This replaces the string values of all state
attributes with the same string in uppercase. The function
upper( ) is one of many MySQL functions
discussed in Chapter 15.

You can update more than one attribute in a statement. For example,
to set both the state and city to uppercase, use:

UPDATE customer SET state = upper(state), city = upper(city);

The UPDATE statement is also often used with the
WHERE clause. For example:

UPDATE customer SET surname = 'Smith' WHERE cust_id = 7;

This updates the surname attribute of customer #7.
Consider a second example:

UPDATE customer SET zipcode = '3001' WHERE city = 'Melbourne';

This updates the zipcode of all rows with a
city value Melbourne.

After an UPDATE is completed, MySQL returns the
number of rows that were changed. If MySQL finds that a value
doesn't need to be changed (because
it's already set to the value you want to change it
to), it isn't updated and isn't
included in the count that's returned.