Managing Databases, Tables, and Indexes (Web Database Applications with PHP & MySQL)

3.4.1. Creating Databases

The CREATE DATABASE statement can create a new, empty database without any tables or data. The following statement creates a database called winestore:

mysql> CREATE DATABASE winestore;

To work with a database, the command interpreter requires the user to be using a database before SQL statements can be issued. Different command interpreters have different methods for using a database and these aren't part of the SQL standard. In the MySQL interpreter, you can issue the command:

mysql> use winestore

For the rest of this chapter, we omit the mysql> prompt from the command examples.

3.4.2. Creating Tables

After issuing the use winestore command, you then usually issue commands to create the tables in the database, as shown in Example 3-1. (You already created the tables in the winestore database in Section 3.2 of this chapter). Let's look at one of these tables, the customer table. The statement that created this table is shown in Example 3-2.

Example 3-2. Creating the customer table with SQL

CREATE TABLE customer (
  cust_id int(5) DEFAULT '0' NOT NULL auto_increment,
  surname varchar(50) NOT NULL,
  firstname varchar(50) NOT NULL,
  initial char(1),
  title varchar(10),
  addressline1 varchar(50) NOT NULL,
  addressline2 varchar(50),
  addressline3 varchar(50),
  city varchar(20) NOT NULL,
  state varchar(20),
  zipcode varchar(5),
  country varchar(20) DEFAULT 'Australia',
  phone varchar(15),
  fax varchar(15),
  email varchar(30) NOT NULL,
  salary int(7),
  birth_date date( ),
  PRIMARY KEY (cust_id),
  KEY names (surname,firstname)
);

The CREATE TABLE statement has three parts:

Following the CREATE TABLE statement is a free-form table name—in this case customer.

Following an opening bracket is a list of attribute names, types, and modifiers.
After the attribute list is a list of keys; that is, information defining what attributes satisfy the uniqueness constraints of a primary key and what attributes are to be indexed for fast access.

A table name may contain any character except a forward slash / or a period, and the name is usually the name of an entity created in the ER model. Attribute names may contain any character, and there are many possible data types for attributes. Details of selected commonly used types are shown in Table 3-1.

Table 3-1. Common SQL data types for attributes

Data type	Comments
int(`length`)	Integer; used for IDs, age, counters, etc.
float(`length``,decimals`)	Floating-point number; used for currency, measurements, etc.
timestamp(`length`)	Updates each time the row is modified or can be manually set. A length of 14 (the default) displays an attribute containing date and time in the format YYYYMMDDHHMMSS. Length 12 displays YYMMDDHHMMSS, 8 displays YYYYMMDD, and 6 displays YYMMDD.
char(`length`)	A space-padded, fixed-length text string.
varchar(`length`)	An unpadded, variable-length text string with a specified maximum length.
blob	An attribute that stores up to 64 KB of data.

For situations where the data stored is always much smaller or larger than the maximum possible value, many attribute types have variants of tiny, small, medium, and big. For example, int has variants smallint, mediumint, and bigint.

Modifiers may be applied to attributes. Two common modifiers are NOT NULL—data can't be added without this attribute having a value—and DEFAULT, which sets the data to the value that follows when no data is supplied.

Identifier attributes—an example in the customer table is the cust_id attribute—often have the modifier auto_increment. The auto_increment modifier automatically writes a unique number into an attribute when no value is supplied. For example, if you insert 10 customer rows into the customer table, you can automatically generate a cust_id of 11 by inserting NULL (or zero) as the value for cust_id. Only one attribute in each table can have the auto_increment modifier.

All numeric attributes have optional zerofill and unsigned modifiers. The former left-pads a value with zeros up to the size of the attribute type. The latter allows only positive values to be stored and roughly doubles the maximum positive value that can be stored.

Inserting NULL into a TIMESTAMP (or another date or time type) attribute stores the current date and time. What is stored in the attribute depends on its length. For example, if the attribute has the type TIMESTAMP(12), both the time and date are stored in the format YYMMDDHHMMSS. If today is January 3, 2002 and time is 10:43:23, the value stored is 020103104323.

More details on attribute types and modifiers can be found in Section 7.7 of the manual.html file distributed with MySQL.

The final component of the CREATE TABLE statement is a specification of key constraints and indexes that are required. In Example 3-2, we specify that the unique identifier is the cust_id attribute by adding the statement PRIMARY KEY (cust_id). The PRIMARY KEY constraint has two restrictions: the attribute must be defined as NOT NULL, and any value inserted must be unique. It is good practice to explicitly state a PRIMARY KEY for all tables; determining primary keys from an ER model is discussed in Appendix C.

We also show in our example another KEY definition; KEY is a synonym for INDEX. In this case, we have defined a KEY names (surname, firstname) to permit fast access to data stored in the customer table by a combination of surname and firstname values. In many cases—without yet knowing what kinds of queries will be made on the database—it is difficult to determine what indexes should be specified. MySQL permits up to 16 indexes to be created on any table, but unnecessary indexes should be avoided. Each index takes additional space, and it must be updated as the data stored in the table is inserted, deleted, and modified. We discuss index tuning in Section 3.10.

3.4.3. Altering Tables and Indexes

Indexes can be added or removed from a table after creation. For example, to add an index to the customer table, you can issue the following statement:

ALTER TABLE customer ADD INDEX cities (city);

To remove an index from the customer table, use the following statement:

ALTER TABLE customer DROP INDEX names;

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. We don't discuss statements for altering the database in this book; many examples can be found in Section 7.8 of the manual.html file that is distributed with MySQL.

3.4.4. Displaying Database Structure with SHOW

Details of databases, tables, and indexes can be displayed with the SHOW command. The SHOW command isn't part of the SQL standard and is MySQL-specific. It can be used in several ways:

SHOW DATABASES

Lists the databases that are accessible by the MySQL DBMS.

SHOW TABLES

Shows the tables in the database once a database has been selected with the use command.

SHOW COLUMNS FROM tablename

Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table. For example:

SHOW COLUMNS FROM customer

shows the attribute information for the customer table. DESCRIBE table produces the same output.

SHOW INDEX FROM tablename

Presents the details of all indexes on the table, including the PRIMARY KEY. For example:

SHOW INDEX FROM customer

shows that there are two indexes, the primary index and the names index.

SHOW STATUS

Reports details of the MySQL DBMS performance and statistics.