Creating and Managing Tables

Tables are more complex structures than databases and (not surprisingly) take more work to create and manage.

These phrases focus on the basics that you need to survive. As there are literally hundreds of options available when creating tables, you will need additional resources to handle more complex tasks, such as setting the default collation for a column or defining tables that will store many terabytes of data.

Listing Some or All Tables in a Database

SHOW TABLES; SHOW TABLES IN database_name; SHOW TABLES LIKE 'word%'; SHOW TABLES IN database_name LIKE 'word%'; 

The first form of the SHOW TABLES command lists all tables in the default database, while the second command allows you to list the tables stored in a specific database. The third and fourth versions of the command show how the LIKE operator can be used with the command.

Tip

As with SHOW DATABASES, you can use the LIKE operator to restrict the list of tables shown by the SHOW TABLES command. See the "Get a Filtered List of Databases" phrase earlier in this chapter for more information on LIKE.

Creating Tables

CREATE TABLE book (   id     SMALLINT UNSIGNED AUTO_INCREMENT NOT NULL,   title  VARCHAR(255) NOT NULL,   author VARCHAR(255) NOT NULL,   cond   ENUM('poor','good','fine','mint') NOT NULL,          PRIMARY KEY (book_id), ); 

Creating tables is a complex topic for two reasons: First, the syntax for creating tables is complex and consists of many elements. Second, the process of choosing how to create the table is even more complex.

Rather than attempt to cover either topic here, a simple example will remind you of what you either know or need to learn. You can refer to the online manual for the full syntax of these commands.

Renaming Tables

RENAME TABLE old_name TO new_name;

The syntax for renaming a table is simple and direct.

Tip

When renaming tables, keep in mind that there might still be users or applications that expect the table to be available under the old name. Use the phrase "Finding Users of a Given Database or Table" in Chapter 6 to spot possible problems.

Deleting Tables

DROP TABLE table_name;

Dropping a table means that, except for copies and backups, the table is gone for good. Use the command with caution.

Tip

Unless storage is an issue, rename tables instead of deleting them. After a few weeks or so, when you are sure that there are no problems, actually delete the table.

Copying Tables

CREATE TABLE new_table LIKE old_table; INSERT new_table SELECT * FROM old_table; 

Copies of existing tables come in very handy when testing destructive queries, installing a new instance of a database-centric application, or so on.

A variety of methods are available for making copies. The two queries in this phrase (when used together) create a complete copy of the structure and data of a table, complete with indexes, table options, and so on.

Tip

There are other ways to copy tables; however, they might be more dangerous, faster, specific to a certain type of table, less complete in what they copy, or a combination of these factors.

You can easily copy a table from one database to another by specifying the database name before the table name. For example:

CREATE TABLE db1.table LIKE db2.table; INSERT db1.table SELECT * FROM db2.table; 

The LIKE operator used in this query does not have the same meaning as the LIKE operator used in WHERE clauses and SHOW queries.