Manipulating Tables

Now that you can use the mysql program and run the commands to create a database, you're ready to build on this knowledge and start manipulating tables.

This section will show you how to create a table, drop a table, view a table's description, and alter a table's column declarations.

Creating a Table

You can create a table using the CREATE TABLE command, whose syntax is as follows:

CREATE [TEMPORARY] TABLE
  [IF NOT EXISTS] table_name
  [(create_definition,...)]
  [table_options] [select_statement]

Before looking at the many options for CREATE TABLE, let's try a simple example. Say that we want to create a database for a content management system called cms, and within it a table called articles. We could use the following, first creating the database cms, then selecting the same database, and finally creating the table:

mysql> CREATE DATABASE cms;
Query OK, 1 row affected (0.06 sec)
mysql> use cms;
Database changed
mysql> CREATE TABLE articles (
    -> article_id INT(9) NOT NULL auto_increment,
    -> headline TEXT NOT NULL,
    -> date_post DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> text_body TEXT,
    -> text_summary TEXT,
    -> who_created INT(9) DEFAULT NULL,
    -> email_sent INT(1) NOT NULL DEFAULT '0',
    -> date_email DATETIME DEFAULT NULL,
    -> who_approved INT(9) DEFAULT NULL,
    -> pic VARCHAR(255) DEFAULT NULL,
    -> PRIMARY KEY  (article_id)
    -> );

Query OK, 0 rows affected (0.03 sec)

The first line of the CREATE statement contains the name of the table we want to create (articles). This is followed by the column definitions, one per line. The line containing PRIMARY KEY specifies the column (article_id), which will be the primary key for the table.

In the CREATE TABLE line, we could add IF NOT EXISTS to avert an error message if a table with that name exists already. CREATE TABLE will not overwrite an existing table.

Creating a Temporary Table

You can optionally use the statement to create a temporary table by using the TEMPORARY keyword.

A temporary table exists only for the life of the current database connection. It automatically is deleted when the connection is closed or dies. Two different connections can use the same name for a temporary table without conflicting with each other.

Temporary tables can be useful when queries get complex. For example, you might want to assemble some data from another table and do some processing or updates on it without affecting the main table. Versions of MySQL prior to 4.1 have no subselect capability, and temporary tables are a convenient way of performing the equivalent of a subselect.

Here's an example dialogue to create a temporary table called tmp with a single column:

mysql> CREATE TEMPORARY TABLE tmp (
    -> name VARCHAR(100) NULL);
Query OK, 0 rows affected (0.00 sec)
							

Creating a Table from a SELECT

A SELECT query produces a resultset of data that has been extracted from another table (you will study it in more detail in Day 8, “Querying Data.”) You can use CREATE TABLE to create your new table with this result data from a SELECT in a single statement.

CREATE TABLE...SELECT extracts column declarations and data from the SELECT query's resultset and uses them to both create and populate the new table.

You can create a normal or a temporary table from a SELECT, and the creation of a temporary table is shown in the following example:

mysql> CREATE TEMPORARY TABLE tmp
    -> SELECT subscriber_id, name
    -> FROM subscribers
    -> WHERE name LIKE 'John%';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

Now let's display the contents of the new table:

mysql> SELECT * FROM tmp;
+---------------+------------+
| subscriber_id | name       |
+---------------+------------+
|             2 | John Brown |
+---------------+------------+
1 row in set (0.00 sec)

The new table takes its column names and declarations from the SELECT query; there's no way to declare them separately. If you want to make the column names different, you have to use an alias in your SELECT.

You would run the SELECT query like this, using the keyword AS to name the alias of subcriber_id as id:

mysql> CREATE TEMPORARY TABLE tmp
    -> SELECT subscriber_id AS id,
    -> name
    -> FROM subscribers
    -> WHERE name LIKE 'John%';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM tmp;
+----+------------+
| id | name       |
+----+------------+
|  2 | John Brown |
+----+------------+
1 row in set (0.00 sec)

Notice from the output that the column subscriber_id had its name changed to id in the table we created by the AS id clause.

...real_column_name AS alias_name
								

...real_table_name AS alias_name
								

Specifying Table Types

Through the table_options parameter of CREATE TABLE, you can specify the MySQL table type. You can choose from any of the table types shown in Table 6.1.

ISAM, HEAP, and MyISAM are available for MySQL versions 3.23.6 or later. MERGE, BDB, and InnoDB are available only for MySQL versions 4.0 onward, and you will need to have compiled and configured MySQL with support for the BDB or InnoDB table types if you want to use them. You'll learn more about these table types in Day 17, “Transactions and Table Locking.”

Use the following syntax for your table_options:

TYPE = table_type
							

Therefore, to create a table of the type InnoDB, use

TYPE = InnoDB

If you don't specify TYPE, the MyISAM table type will be used by default.

Listing Tables

You can list the tables of a database using the SQL:

SHOW TABLES

For example, if you are connected to a database within mysql, you can do this:

mysql> SHOW TABLES;
						

Dropping a Table

Dropping a table means deleting the table definition and all data in the table.

You can drop a table using

DROP TABLE table_name
						

For example:

DROP TABLE subscribers

drops the table subscribers.

Altering a Table

After a table has been created, it's still possible to change the specifications of its columns. You can use the ALTER TABLE statement, which takes the following form:

ALTER TABLE table_name action_list
						

You can make multiple changes at a time to the same table. Just specify an action_list of changes separated by commas. An action list can include any number of these actions.

The possible actions for modifying columns are shown in Table 6.2.

In Table 6.2, column_name always represents the current name of the column. The column_declaration is the new declaration, in the same format as if it were in a CREATE TABLE statement. This must include the name of the column after the change, even if the name does not change. new_table_name is the name of the table after the table is renamed.

For example, to add a column to the subscribers table—say, date_of_birth—do this:

mysql> ALTER TABLE subscribers
    -> ADD COLUMN date_of_birth DATETIME;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> DESCRIBE subscribers;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| subscriber_id | int(9)       |      | PRI | NULL    | auto_increment |
| name          | varchar(100) | YES  |     | NULL    |                |
| email         | varchar(100) |      |     |         |                |
| date_of_birth | datetime     | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MySQL adds the column and, because there happen to be four rows in the table already, tells us that four rows were affected.

To drop the column again, do this:

mysql> ALTER TABLE subscribers
    -> DROP COLUMN date_of_birth;
						

Now we'll be a little more ambitious. We'll add the date_of_birth column again, but this time put it immediately after the name column, using the AFTER keyword:

ADD COLUMN date_of_birth DATETIME AFTER name

To make things interesting, we'll also modify the email column to make it 200 characters long rather than 100:

MODIFY email VARCHAR(200)

Run the combined statement with commas separating the actions, like this:

mysql> ALTER TABLE subscribers
    -> ADD COLUMN date_of_birth DATETIME AFTER name,
    -> MODIFY email VARCHAR(200);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> DESCRIBE subscribers;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| subscriber_id | int(9)       |      | PRI | NULL    | auto_increment |
| name          | varchar(100) | YES  |     | NULL    |                |
| date_of_birth | datetime     | YES  |     | NULL    |                |
| email         | varchar(200) | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Notice how we separated the two actions with a comma. We could make all sorts of changes to the table like this.

In this example, the change made to the email field was fairly safe: the field got longer so that no data would be lost. However, if making fields shorter, be aware that in rows where there's too much data for the new field size, the data will be truncated down to the shorter length.

In addition to changing columns, you can alter the table_options you saw just now in the definition of CREATE TABLE. Although there are many possibilities for table_options not documented here, one thing you may want to use it to alter is the table type. For example, you can change the table from a MyISAM table to an InnoDB table:

mysql> ALTER TABLE subscribers
    -> TYPE = InnoDB;
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0

You can use ALTER TABLE to add and drop indexes on tables. If you're not sure about using indexes yet, don't worry; we'll cover that in Day 9. For now, let's just say that there are ways of making your table operations more efficient, and they can enforce rules such as uniqueness of data in a given column. An index (also known as a key) behaves like an external column to the table, thus an index can be dropped without affecting any data. Here's how to add an index:

ADD INDEX [index_name] (index_columns)
ADD PRIMARY KEY (index_columns)
ADD UNIQUE [index_name] (index_columns)

where index_columns is the name of the column to index, or a list of column names separated with commas. index_name is optional, and, if omitted, the name of the first indexed column will be used as the name of the index.

To drop indexes:

DROP INDEX (index_name)

which is the same as

DROP KEY (index_name)

and to drop a primary key:

DROP PRIMARY KEY

For example, to make name an index, you can do this:

mysql> ALTER TABLE subscribers
    -> ADD INDEX name (name);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> DESCRIBE subscribers;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| subscriber_id | int(9)       |      | PRI | NULL    | auto_increment |
| name          | varchar(100) | YES  | MUL | NULL    |                |
| date_of_birth | datetime     | YES  |     | NULL    |                |
| email         | varchar(15)  | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

The MUL in the Key field against name tells us that it's now a “multiple” key (that is, a non-unique key).

You could also ask MySQL to show the indexes using the SHOW command again. You might like to try this:

mysql> SHOW INDEXES FROM subscribers;
						

or

mysql> SHOW KEYS FROM subscribers;
						

To drop the index again, do this:

mysql> ALTER TABLE subscribers
    -> DROP INDEX name;
						

Remember, this drops only the index, not the original column or its data.