The CREATE statement is used to create a table in MySQL. The general syntax to create a table in MySQL is:

CREATE TABLE tableName
(
    fieldName1 dataType(size) [NULL | NOT NULL]
    fieldName2 dataType(size) [NULL | NOT NULL]
);

If NULL is specified, the field is allowed to be left empty. If NOT NULL is specified, the field must be given a value. In the absence of either a NULL or NOT NULL, NULL is assumed.

Data Types of Fields

The following lists the data types avaiable in MySQL.

Primary Keys

A primary key is a field in a table that uniquely identifies a record. The PRIMARY KEY attribute may be used when defining the field name to create a primary key, as in the following example.

fieldName INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

Alternatively, the PRIMARY KEY attribute may be used to specify the primary key after the defining the fields, as in the following example:

CREATE TABLE product
(
    prodID INT UNSIGNED AUTO_INCREMENT,
    description VARCHAR(100),
    PRIMARY KEY(prodID)
);

The second method is ideal should you want to create a compound primary key. A compound primary key is where more than one field is used to uniquely identify a unique record. The field names are separated with commas. Consider a table used with a search engine for a site. The primary key could comprise the Directory name, and the page name. The following example illustrates creating a table with a compound primary key.

createSearch.sql
CREATE TABLE search
(
    Category VARCHAR(100) NOT NULL,
    Page VARCHAR(20) NOT NULL,
    Directory VARCHAR(255) NOT NULL,
    LinkName VARCHAR(255) NOT NULL,
    Keywords MEDIUMTEXT NOT NULL,
    Desription VARCHAR(255) NOT NULL,
    PRIMARY KEY(Page, Directory)
);

The file may then be used with MySQL as follows:

mysql> \. createSearch.sql

Providing Default Values

Default values may be provided for fields using the DEFAULT attribute. The following example uses a default value of index.html, should a Page name not be provided for the search table.

createSearch.sql

CREATE TABLE search
(
    Category VARCHAR(100) NOT NULL,
    Page VARCHAR(20) DEFAULT 'index.html',
    Directory VARCHAR(255) NOT NULL,
    LinkName VARCHAR(255) NOT NULL,
    Keywords MEDIUMTEXT NOT NULL,
    Desription VARCHAR(255) NOT NULL,
    PRIMARY KEY(Page, Directory)
);

Examining Tables

You can examine the structure of a table using the DESCRIBE statement. The following displays the structure of the search table.

mysql> DESCRIBE search;

The DESCRIBE statement may be shorted to DESC. The following achieves the same thing.

mysql> DESC search;

You may also use the SHOW FIELDS FROM statement to achieve the same thing.

mysql> SHOW FIELDS FROM search;

Listing All Tables

You can list all tables in the database using the SHOW TABLES statement. The following example lists all tables in the current database.

mysql> SHOW TABLES;