CREATE [TEMPORARY] TABLE [IF NOT EXISTS] [db_name.]tbl_name [(create_definition, ...)] [table_options] [[IGNORE | REPLACE] select_statement]
Where create_definition is:
{ col_name type [NOT NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [PRIMARY KEY]
| PRIMARY KEY (index_col_name, ...)
| INDEX [index_name] (index_col_name, ...)
| UNIQUE [INDEX] [index_name] (index_col_name, ...) }
In this code, type is a MySQL column type (see "MySQL Column Types"), and index_col_name is as described for ALTER TABLE ADD INDEX in "ALTER TABLE".
CREATE TABLE is used to create a new table called tbl_name in the current database (or in a specific database if db_name.tbl_name is specified instead). If TEMPORARY is specified, the table disappears upon termination of the connection by which it was created. Creating a temporary table with the same name as an existing table will hide the existing table from the current client session until the temporary table is deleted or the session ends; however, other clients will continue to see the original table.
Assuming TEMPORARY is not specified, this query will fail if a table with the given name already exists, unless IF NOT EXISTS is specified. A CREATE TABLE query will also fail if you don't have the required privileges.
Most of the time, the name of the table will be followed by a series of column declarations (create_definition above). Each column definition includes the name and data type for the column, and any of the following options:
NOT NULL
This specifies that the column may not be left empty (NULL). Note that NULL is a special 'no value' value, which is quite different from, say, an empty string (''). A column of type VARCHAR, for instance, which is set NOT NULL may be set to '' but will not be NULL. Likewise, a NOT NULL column of type INT may contain zero (0), which is a value, but it may not contain NULL, as this is not a value.
DEFAULT default_value
DEFAULT lets you specify a value to be given to a column when no value is assigned in an INSERT statement. When this option is not specified, NULL columns (columns that don’t have the NOT NULL option set) will be assigned a value of NULL when they are not given a value in an INSERT statement. NOT NULL columns will instead be assigned a 'default default value' (an empty string (''), zero (0), '0000-00-00', or a current timestamp, depending on the data type of the column.
AUTO_INCREMENT
As described in "Getting Started with MySQL", an AUTO_INCREMENT column will automatically insert a number that is one greater than the current highest number in that column, when NULL is inserted. AUTO_INCREMENT columns must also be NOT NULL, and either a PRIMARY KEY or UNIQUE.
PRIMARY KEY
This option specifies that the column in question should be the primary key for the table; that is, the values in the column must uniquely identify each of the rows in the table. This forces the values in this column to be unique, and speeds up searches for items based on this column by creating an index of the values it contains.
UNIQUE
Very similar to PRIMARY KEY, this option requires all values in the column to be unique, and indexes the values for high speed searches.
The table_options portion of the CREATE TABLE query is used to specify advanced properties of the table, and is described in detail in the MySQL Reference Manual.
The select_statement portion of the CREATE TABLE query allows you to create a table from the results of a SELECT query (see "SELECT"). When you create this table, you don’t have to declare the columns that correspond to those results separately. This type of query is useful if you want to obtain the result of a SELECT query, store it in a temporary table, and then perform a second SELECT query on it. To some extent, this may be used to make up for MySQL’s lack of support for sub-selects[1], which allow you to perform the same type of operation in a single query.
[1]Support for sub-selects is planned for inclusion in MySQL 4.1