Table Management (Managing and Using MySQL, 2nd Edition)

3.3. Table Management

You should now feel comfortable connecting to a database on a MySQL server. For the rest of the chapter, you can use either the test database that comes with MySQL or your own play database. Using the SHOW command, you can display a list of tables in the current database the same way you used it to show databases. In a brand new installation, the test database has no tables. The following shows the output of the SHOW TABLES command when connected to the mysql system database:

mysql> USE mysql;
Database changed
mysql> SHOW TABLES;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.00 sec)

These are the six system tables MySQL requires to do its work. To see what one of these tables looks like, you can use the DESCRIBE command:

mysql> DESCRIBE db;
+-----------------+-----------------+------+-----+---------+-------+
| Field           | Type            | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host            | char(60) binary |      | PRI |         |       |
| Db              | char(64) binary |      | PRI |         |       |
| User            | char(16) binary |      | PRI |         |       |
| Select_priv     | enum('N','Y')   |      |     | N       |       |
| Insert_priv     | enum('N','Y')   |      |     | N       |       |
| Update_priv     | enum('N','Y')   |      |     | N       |       |
| Delete_priv     | enum('N','Y')   |      |     | N       |       |
| Create_priv     | enum('N','Y')   |      |     | N       |       |
| Drop_priv       | enum('N','Y')   |      |     | N       |       |
| Grant_priv      | enum('N','Y')   |      |     | N       |       |
| References_priv | enum('N','Y')   |      |     | N       |       |
| Index_priv      | enum('N','Y')   |      |     | N       |       |
| Alter_priv      | enum('N','Y')   |      |     | N       |       |
+-----------------+-----------------+------+-----+---------+-------+
13 rows in set (0.36 sec)

This output describes each column in the table showing its data type, whether it can contain null values, what kind of key it is, any default values, and extra information. If all this means nothing to you, don't worry. We will describe each of these elements as the chapter progresses.

You should now be ready to create your first table. First, connect back to the test database that comes with a clean MySQL install:

USE test;

Make sure you connect to the test database first, because you definitely do not want to add tables to the mysql database. The table, a structured container of data, is the most basic concept of a relational database. Before adding data to a table, you must define the table's structure. Consider the following layout:

+---------------------------------+
|             people              |
+-------------+-------------------+
| name        | char(10) not null |
| address     | text(100)         |
| id          | int               |
+-------------+-------------------+

Not only does the table contain the names of the columns, but it also contains the types of each field as well as any additional information the fields may have. A field's data type specifies what kind of data the field can hold. SQL data types are similar to data types in other programming languages. The full SQL standard allows for a large range of data types. MySQL implements most of them as well as a few MySQL-specific types.

The general syntax for table creation is:

CREATE TABLE table_name (
                         column_name1 type [modifiers]
                      [, column_name2 type [modifiers]]
)

TIP: What constitutes a valid identifier (a name for a table or column) varies between DBMSs. MySQL allows up to 64 characters in an identifier, supports the character $ in identifiers, and lets identifiers start with a valid number. More importantly, however, MySQL considers any valid letter for your local character set to be a valid letter for identifiers.

A column is the individual unit of data for a row within a table. A table may have any number of columns, but too many columns can make a table inefficient. This is where good database design, discussed in Chapter 7, becomes important. By creating properly normalized tables, you can join tables to perform searches across data housed in more than one table. We discuss the mechanics of a join later in the chapter.

Consider the following CREATE statement:

CREATE TABLE USER (
    USER_ID    BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    USER_NAME  CHAR(10)        NOT NULL,
    LAST_NAME  VARCHAR(30),
    FIRST_NAME VARCHAR(30),
    OFFICE     CHAR(2)         NOT NULL DEFAULT 'NY');

This statement creates a table called USER with five columns: USER_ID, USER_NAME, LAST_NAME, FIRST_NAME, and OFFICE. After each column name comes the data type for that column, followed by any modifiers.

The NOT NULL modifier indicates that the column may not contain any null values. If you try to assign a null value to that column, SQL will generate an error. Actually, there are a couple of exceptions to this rule. First, if the column is defined as AUTO_INCREMENT, a null value will cause a value to be generated automatically. (We cover auto-incrementing later in the chapter.) The second exception is when you specify a default value for a column, as we have for the OFFICE column in the previous example. In this case, the OFFICE column is assigned the default value of 'NY' when you assign a null value. (We will discuss data types and the PRIMARY KEY modifier later in this chapter.)

Like most things in life, destruction is much easier than creation. The command to drop a table from the database is:

DROP TABLE table_name

This command completely removes all traces of that table from the database. MySQL removes all data within the specified table from existence. If you have no backups of the table, you absolutely cannot recover from this action. The moral of this story is to always keep backups and be very careful about dropping tables. You will thank yourself for it someday.

With MySQL, you can specify more than one table to delete by separating the table names with commas. For example, DROP TABLE people, animals, plants would delete the three named tables. You can also use the IF EXISTS modifier to avoid an error, should the table not exist when you try to drop it. This modifier is useful for huge scripts designed to create a database and all its tables. Before creating the database, run a DROP TABLE IF EXISTS table_name command.