MySQL provides more than one way to create tables:
- You can create an empty table, either by specifying its definition explicitly or by using the definition of an existing table.
- You can create a table populated from the result of a SELECT statement.
- You can create temporary tables.
A new table can be created from an explicit definition by using a CREATE TABLE statement that includes the table name and a list of columns. Each column has its own name and definition. The table definition may also include index definitions. To create a table, give its name followed by a list of column definitions within parentheses:
CREATE TABLE table_name (column_definitions);
CREATE TABLE tbl_name (id INT NOT NULL);
If you try to create a table that already exists, an error occurs. If you simply want to ensure that the table exists, add an IF NOT EXISTS clause to the statement:
CREATE TABLE IF NOT EXISTS tbl_name (i INT);
More complex tables have multiple columns, with the column definitions separated by commas. The following table definition includes, in addition to an id column, two 30-character columns for storing last names and first names, and a column for storing date values. All columns are declared NOT NULL to indicate that they require non-NULL values.
CREATE TABLE tbl_name
(
id INT NOT NULL,
first_name CHAR(30) NOT NULL,
last_name CHAR(30) NOT NULL,
dob DATE NOT NULL
);
While creating a table, you can provide index definitions in addition to the column definitions. Indexes are useful for speeding up queries by reducing record lookup time. Here's a simple table that includes two index definitions. The first creates an index on the id column and requires each id value to be unique. The second index definition creates a two-column index on the last_name and first_name columns of the table:
CREATE TABLE tbl_name
(
id INT NOT NULL,
first_name CHAR(30) NOT NULL,
last_name CHAR(30) NOT NULL,
UNIQUE (id),
INDEX (last_name, first_name)
);
Every table is created using one of the storage engines supported by the server. To specify a storage engine when you create a table, include an ENGINE = engine_name option in the CREATE TABLE statement. The following statement creates t as an InnoDB table:
CREATE TABLE tbl_name
(
id INT NOT NULL,
first_name CHAR(30) NOT NULL,
last_name CHAR(30) NOT NULL,
UNIQUE (id),
INDEX (last_name, first_name)
) ENGINE = InnoDB;