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;