O'Reilly logo

MySQL in a Nutshell by Russell J.T. Dyer

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Name

CREATE TABLE

Synopsis

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table 
{[(definition)][options]|[[AS] SELECT...]|[LIKE table]}

Use this statement to create a new table within a database. This statement has many clauses and options. However, when creating a basic table, you can omit many of them. You can use the TEMPORARY keyword to create a temporary table that is used only for the current connection thread and not accessible by other users. You can use the IF NOT EXISTS flag to suppress error messages caused by attempting to create a table by the same name as an existing one. After the table name is given, either the table definition is given (i.e., a list of columns and their datatypes) along with table options or properties, or a table can be created based on another table. You create a table based on the schema of another table with a SELECT statement or with a LIKE clause. These two possibilities are covered at the end of this statement's explanation. Here is a simple example of how you can use this statement:

CREATE TABLE clients
   (client_id INT AUTO_INCREMENT PRIMARY KEY,
    client_name VARCHAR(75), telephone CHAR(15));

This creates a table with three columns. The first column is called client_id and may contain integers. It will be incremented automatically as records are created. It will also be the primary key field for records, which means that no duplicates are allowed and that the rows will be indexed off of this column. The second column, client_name, is a variable-width, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required