لطفا منتظر باشید ...
15.3 Manipulating Data and Databases
In this section, we show you how to alter a
database's structure after you've
created it. We also expand on the topics of creating, inserting,
deleting, and updating data, including how to work with external
files and multiple tables, and optimizing queries.
15.3.1 Altering Databases
Altering
a table is unusual: most of the time, you'll define
the structure of a table before you create it and you
won't change it during its lifetime. However,
indexes, attributes, modifiers, and other features of a table can be
changed after creation, and this is sometimes a useful feature when
you want to add a new index that supports a new query, modify an
attribute type or length when needed, or tune your database.
Adding indexes is a popular use of the ALTER TABLE
statement. For example, to add an index to the
customer table, you can run:
ALTER TABLE customer ADD INDEX cities (city);
The label cities is the name of the new index and
the attribute that's indexed is
city.
To remove the same index from the customer
table, use:
ALTER TABLE customer DROP INDEX cities;
This removes the index, not the attribute.
The DROP statement discussed in Chapter 5 can also be used to remove an index. For
example:
DROP INDEX cities ON customer;
Behind the scenes, MySQL converts this to an ALTER
TABLE statement.
The ALTER TABLE statement can
also be used to add, remove, and alter all other aspects of the
table, such as attributes and the primary index. For example, to add
a new fax attribute to the
customer table, you can use:
ALTER TABLE customer ADD fax varchar(15);
To remove the attribute fax, use:
ALTER TABLE customer DROP fax;
To change the cust_id attribute from type
int to smallint, you can use:
ALTER TABLE customer MODIFY cust_id smallint;
You can use a similar syntax to rename an attribute
cust_id to id:
ALTER TABLE customer CHANGE cust_id id smallint;
You can also change attribute lengths using a similar syntax:
ALTER TABLE customer MODIFY surname char(10);
You can even rename the customer table to
clients:
ALTER TABLE customer RENAME clients;
This isn't an exhaustive list of things you can do
with ALTER TABLE: for example, as we show in
"Table Types," you can use it to
alter the table type after creation. The complete syntax of the
examples we've shown and many more examples can be
found in Section 6.5.4 of the MySQL manual.
Be careful when altering your tables. For example, if you rename
attributes then your associative access to those attributes in PHP
will need modification. If you reduce the maximum length of an
attribute, then values that exceed the new length will be truncated
to fit; for numbers, this means that if the old value exceeds the new
maximum value then the new maximum value is stored, while for other
types it means that they are right truncated.
15.3.2 More on Inserting Data
In this section, we show you how to
insert data from one or more tables into another table, create a new
table using a query, replace existing data with new data, bulk load
data from a text file into a database, and cache insertions in a
buffer so that they can be optimized.
15.3.2.1 Using INSERT with SELECT
In the previous chapter, we
showed you how to insert data using three different techniques. In
this section, we show you how insertion and querying can be closely
tied together using a nested querying approach with the
INSERT INTO ... SELECT statement. This is useful
for copying data and, if needed, modifying the data as it is copied.
Consider an example where you want to create a permanent record of
the total sales to each customer. First of all,
let's create a simple table to store the customer
and sales details:
CREATE TABLE salesuntilnow
(
cust_id int(5) NOT NULL,
surname varchar(50),
firstname varchar(50),
totalsales decimal(5,2),
PRIMARY KEY (cust_id)
) type=MyISAM;
Now, you can issue a nested INSERT INTO ... SELECT
statement to populate the new table with the customer details and the
total sales:
INSERT INTO salesuntilnow (cust_id, surname, firstname, totalsales)
SELECT customer.cust_id, surname, firstname, SUM(price)
FROM customer INNER JOIN items USING (cust_id)
GROUP BY items.cust_id;
The four attributes listed in the SELECT statement
are mapped to the four attributes listed in the INSERT
INTO statement. For example, the
customer.cust_id in the SELECT
statement is mapped into cust_id in the
salesuntilnow table. Note that unlike other
nested queries, the SELECT statement
isn't surrounded by brackets (and MySQL will
complain if you try to include them). Note also that the
VALUES keyword isn't used with
the INSERT statement.
Here's a query on the new table:
SELECT * from salesuntilnow;
It output the following results in part:
+---------+-----------+-----------+------------+
| cust_id | surname | firstname | totalsales |
+---------+-----------+-----------+------------+
| 1 | Rosenthal | Joshua | 925.80 |
| 2 | Serrong | Martin | 1535.07 |
| 3 | Leramonth | Jacob | 896.27 |
| 4 | Keisling | Perry | 979.17 |
| 5 | Mockridge | Joel | 240.70 |
| 6 | Ritterman | Richard | 448.72 |
| 7 | Morfooney | Sandra | 972.74 |
| 8 | Krennan | Betty | 69.98 |
There are two sensible limitations when inserting with a
SELECT statement: first, the query
can't contain an ORDER BY, and
second, the FROM clause can't
contain the target table of the INSERT
INTO.
15.3.2.2 Using CREATE TABLE with SELECT
You can create a table and insert data
from one or more other tables in a single step. For example, you can
create the salesuntilnow table we created in the
previous section and insert the sales data in one query.
Here's how it's done:
CREATE TABLE salesuntilnow
SELECT customer.cust_id, surname, firstname, SUM(price)
FROM customer INNER JOIN items USING (cust_id) GROUP BY items.cust_id;
The result is exactly the same as in the previous section, except
that you don't have explicit control over the
definition of the attribute names and types, and the indexes.
Instead, the attribute names are copied from the
SELECT statement, and the types are chosen by
MySQL (though they are usually the same as the source attributes). No
indexes are created; you need to add indexes afterwards using
ALTER TABLE. In this example, the table has the
following structure (as shown by running SHOW
COLUMNS FROM salesuntilnow):
+------------+--------------+-------------------+------+-----+---------+-------+
| Field | Type | Collation | Null | Key | Default | Extra |
+------------+--------------+-------------------+------+-----+---------+-------+
| cust_id | int(5) | binary | | | 0 | |
| surname | char(50) | latin1_swedish_ci | YES | | NULL | |
| firstname | char(50) | latin1_swedish_ci | YES | | NULL | |
| SUM(price) | double(19,2) | binary | YES | | NULL | |
+------------+--------------+-------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
This isn't ideal: an attribute with the name
SUM(price) is difficult to reference (because
it's confused by MySQL with the aggregate function
SUM).
In MySQL 4.1, you can explicitly choose attribute names, types, and
lengths using a variation of the previous approach, and you can
create indexes. Using this method, you provide a comma-separated list
of attribute names, types, lengths, and modifiers. You then add any
index definitions. Here's the previous example
rewritten using this approach:
CREATE TABLE salesuntilnow (cust_id int(5) NOT NULL,
surname varchar(50),
firstname varchar(50),
totalsales decimal(5,2),
primary key (cust_id))
SELECT customer.cust_id, surname, firstname, SUM(price) AS totalsales
FROM customer INNER JOIN items USING (cust_id) GROUP BY items.cust_id;
In this example, the table that's created has the
following structure (again as shown by running SHOW COLUMNS
FROM salesuntilnow):
+------------+--------------+-------------------+------+-----+---------+-------+
| Field | Type | Collation | Null | Key | Default | Extra |
+------------+--------------+-------------------+------+-----+---------+-------+
| cust_id | int(5) | binary | | PRI | 0 | |
| surname | varchar(50) | latin1_swedish_ci | YES | | NULL | |
| firstname | varchar(50) | latin1_swedish_ci | YES | | NULL | |
| totalsales | decimal(5,2) | binary | YES | | NULL | |
+------------+--------------+-------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Note that in the SELECT query, you must alias
attributes so that they match the attribute names in the new table
(if the attribute names aren't the same). In our
example, we alias SUM(price) AS totalsales, so
that it is stored in the totalsales attribute in
the new table. If you don't include an alias, an
extra attribute is added to the new table; you can use this as a
feature if you want to add an attribute without defining it.
MySQL 4.1 also allows you to create a new table with exactly the same
structure as an existing table. For example, to create the
salesuntilyesterday table, with the exact
structure of salesuntilnow (including any
indexes), use:
CREATE TABLE salesuntilyesterday LIKE salesuntilnow;
This doesn't copy any data.
15.3.2.3 Replacing data
The INSERT INTO ...
SELECT statement inserts new data. If you want to change
existing data, REPLACE or
UPDATE should be used instead. If you get a
complaint about duplicate primary key insertion, the problem is that
you tried to INSERT where there was already a row
with that key.
You can tell MySQL to ignore errors when using
INSERT by including the IGNORE
modifier. Consider an example, where we want to write data into the
salesuntilnow table:
INSERT IGNORE INTO salesuntilnow (cust_id, surname, firstname, totalsales)
SELECT customer.cust_id, surname, firstname, SUM(price)
FROM customer INNER JOIN items USING (cust_id)
GROUP BY items.cust_id;
This query runs without complaint, but won't insert
any new row that has the same primary key as a row
that's already in the
salesuntilnow table. So, for example, if
there's a row that has a cust_id
value of 1 in the salesuntilnow table, any data
returned from the SELECT statement for that
customer will be silently ignored.
But what about if you want to override data using a simple
INSERT statement? Let's suppose
you've loaded the winestore
database, and now you want to repeat the insertion of the first
customer with some amended details:
INSERT INTO customer VALUES (1,'Rosenthal','John','B',1,
'34 Mellili Cres','Earlwood','VIC','6750',12, '(613)83008461',
'1969-01-26');
If you execute the statement, MySQL complains (as it should) about a
duplicate key value being used. In this example, you can solve the
problem by writing an UPDATE statement to change
values, or you can use the REPLACE statement
instead of UPDATE:
REPLACE INTO customer VALUES (1,'Rosenthal','John','B',1,
'34 Mellili Cres','Earlwood','VIC','6750',12, '(613)83008461',
'1969-01-26');
The REPLACE statement reports:
Query OK, 2 rows affected (0.00 sec)
Two rows are reported as changed because the old row is first
deleted, and then the new row is inserted. This shows you the
difference between UPDATE and
REPLACE: you can use UPDATE
only when a row exists, but you can use REPLACE
even if the row hasn't yet been created (and MySQL
will just silently skip the deletion step). If you do use
REPLACE instead of INSERT into
an empty table, you'll find that
REPLACE works the same as
INSERT and reports that only one row was affected.
The REPLACE statement supports the same syntax as
INSERT: all different approaches to insertion that
are described in Chapter 5 work with
REPLACE.
15.3.2.4 Bulk loading a file into a database
A common need is to load data from a
formatted ASCII text file into a database. 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. Lines, which map to rows in a
table, are usually terminated with a carriage return. For example,
consider the following winery information that has been exported from
a legacy spreadsheet program:
1, "Hanshaw Estates Winery", 2
2, "De Morton and Sons Wines", 5
3, "Jones's Premium Wines", 3
4, "Borg Daze Premium Wines", 5
5, "Binns Group", 6
6, "Davie Brook Vineyard", 3
7, "Eglington Creek Premium Wines", 4
8, "McKay Station Vineyard", 4
9, "Dennis and Sons Wines", 5
10, "Beard Brothers Vineyard", 4
The data in this example is saved in the file
winery.csv. We've organized the
attribute values into the same order as the attributes in the
winestore winery table. Most spreadsheet
software allows data to be reorganized and manipulated as it is
exported. We've also used the spreadsheet to create
unique primary key values for each row as the first attribute. If
you're using a Unix platform, or avoiding
spreadsheets, you'll find awk
is almost the only tool you'll ever need for
line-by-line data manipulation; there's also a
Microsoft Windows version available.
The MySQL statement LOAD DATA
INFILE
is used to load formatted
data from a file into a database. This is nonstandard SQL. The
winery.csv file can be inserted into the
winery table using the statement:
LOAD DATA INFILE 'winery.cdf' INTO TABLE winery
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
If quotation marks form part of an attribute, they must be escaped
using backslashes. For example:
"Smith's \"Lofty Heights\" Winery"
Spreadsheet software usually automatically escapes quotation marks in
strings when data is exported.
More detail on the LOAD DATA INFILE statement,
including other options to specify data formats and techniques to
control its priority, are discussed in Section 6.4.9 of the MySQL
manual.
15.3.2.5 Delayed insertion
If your application is under heavy
load, you can use the MySQL-specific
DELAYED
modifier for insertion. It works only
with the default MyISAM table type that's discussed
in Section 15.6.
Here's an example:
INSERT DELAYED INTO customer VALUES (1,'Rosenthal','John','B',1,
'34 Mellili Cres','Earlwood','VIC','6750',12, '(613)83008461',
'1969-01-26');
This modifier causes an INSERT statement to be
stored in a buffer at the database server so that it can be run later
together with any other statements that are in the insert buffer for
that table. This has two advantages: first, it allows the client to
continue without waiting for the query to execute; and, second, it
allows MySQL to optimize the insertion process by working with many
rows at once. The main drawback is that you can't
get sensible information about the result of the insertion process.
For example, if you execute the previous example and you already have
a row with a cust_id value of 1,
you'll still receive a message that indicates the
process worked (even though it didn't):
Query OK, 1 row affected (0.01 sec)
In addition, this modifier is faster only if the application is under
heavy load; if it isn't, don't use
DELAYED because your insertion will run slower.
15.3.3 More on Deleting Data
Our discussion of the
DELETE
statement in Chapter 5 focused on simple examples with one table.
In this section, we show you how to delete using a join query and how
to delete from more than one table with a single query. We also show
you a few tricks to speed up your deletes.
As with the SELECT statement, you can include a
join condition in the WHERE clause of a
DELETE, and you can delete rows from more than one
table in a single statement. For example, suppose you want to remove
all orders and related
items rows, if the order was placed prior to 1
March 2000. You can do this with the following query:
DELETE orders, items FROM orders, items WHERE orders.cust_id=items.cust_id
AND orders.order_id=items.order_id AND orders.date < "2000/03/01";
The syntax is a little different from a SELECT
statement: the table names from which rows should be deleted are
listed after the DELETE statement and the tables
that are used in the join condition are listed after the
FROM statement. Another example shows why this is
needed: let's suppose you want to delete all
wineries from the Barossa Valley region (but not the region itself).
Here's the query:
DELETE winery FROM region, winery WHERE winery.region_id=region.region_id
AND region_name = "Barossa Valley";
The query only affects the winery table, but it
uses both the winery and
region tables to discover which rows should be
deleted.
You can also use the advanced join operators in
DELETE statements. For example, our first query in
this section can be rewritten using the INNER JOIN
syntax as:
DELETE orders, items FROM orders INNER JOIN items
USING (cust_id, order_id) WHERE orders.date < "2000/03/01";
You can also use nested queries (as long as the inner query
doesn't reference data that's being
deleted), GROUP BY, and HAVING
in DELETE statements. You can also use
ORDER BY in a single-table
DELETE, but that doesn't make
much sense unless you're combining it with the
LIMIT modifier so that only some rows are removed;
ORDER BY and LIMIT
can't be used with multi-table deletes.
If you're deleting all of the data from one table,
there's a faster alternative than using
DELETE. The
TRUNCATE
statement drops a table (deleting the
data and the table structure), and then recreates the table
structure. Here's an example:
TRUNCATE customer;
Its only significant limitation is that it doesn't
report how many rows were deleted from the table. Also, it works on
only one table.
You can add a QUICK
modifier to a
DELETE statement, but this works only with tables
of the default MyISAM table type. For example:
DELETE QUICK FROM customer WHERE cust_id < 100;
The QUICK option causes lazy deletion of index
entries, and this can speed up large or frequent delete operations.
If you use the MyISAM table type, an occasional clean up of the table
after deletion will reduce file size and speed up subsequent queries.
You can do this with the OPTIMIZE
TABLE
statement:
OPTIMIZE TABLE customer;
15.3.4 More on Updating Data
Our UPDATE examples in
Chapter 5 are simple. In this section, we show
you how to include a join condition in an update and how to avoid
errors that can occur.
You can use joins in
UPDATE
statements. For example, here's a query that adds a
note to the end of the order delivery instructions for all customers
who live in the state of Western Australia (WA):
UPDATE customer, orders
SET instructions = CONCAT(instructions, " Ship using rail.")
WHERE customer.cust_id = orders.cust_id AND customer.state = "WA";
The CONCAT( )
function joins two or more
strings together, and is used in this example to add the additional
instruction to the end of the current instruction;
it's discussed later in Section 15.4.
You can also use the INNER JOIN and LEFT
JOIN clauses with an UPDATE. For
example, our previous query could be rewritten as:
UPDATE customer INNER JOIN orders USING (cust_id)
SET instructions = CONCAT(instructions, " Ship using rail.")
WHERE customer.state = "WA";
You can also use nested queries for updates, with the limitation that
the inner query can't read data
that's being updated by the outer query.
There's also no problem in using GROUP
BY and HAVING. What's
more, you can also use ORDER BY to update rows in
a specific order, but that's useful only if
you're combining it with the
LIMIT modifier so that only some rows are
affected.
Last of all, you can add the keyword IGNORE to an
update so that MySQL won't abort even if an error is
encountered: this is useful if you're preparing a
set of SQL statements, and want them all to run even if something
goes wrong. Here's an example:
UPDATE IGNORE customer SET cust_id = 1 WHERE cust_id = 2;
In this case, because there's already another row
with this unique cust_id, MySQL shows it
hasn't done anything but doesn't
complain either:
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
