To create a table in MySQL, a CREATE TABLE statement is used. This statement is very complex because it requires defining all columns, data types and other parameters that make a column.

However, a table may be created with basic information only, and that is column name(s) and data type(s). In such as case, other parameters will be set to their default values. Below you can see a basic syntax for MySQL Table Creation.

Syntax

CREATE TABLE table_name

   (

   Column_name1 Data_type [DEFAULT value],

   Column_name2 Data_type [DEFAULT value],

   Column_name3 Data_type [DEFAULT value],

   ?..

   );

A more realistic syntax for creating tables looks like this:

CREATE TABLE [IF NOT EXISTS] table_name(column_name1, column_name2,?) engine=table_type;

Here we can again see IF_NOT_EXISTS statement, which is optional and should be used to prevent errors. Furthermore, when creating tables in MySQL, it is always a good idea to define keys and other constraints, as well as AUTO_INCREMENT if needed, immediately at the columns declarations. At the end of the statement, the storage engine should be declared as well.

Creating table by using a sub-query

A table may be also created by using a sub-query. The following example shows how to do just that:

Creating table with sub-queries:

CREATE TABLE table_name (colname1,colname2) AS (subquery);

The following example creates a table 'dept30' that contains details of all employees working in department 30:

CREATE TABLE  dept30

   AS SELECT empno, ename, sal * 12 annsal, hiredate

   FROM emp

   WHERE deptno = 30;

NOT NULL

The NOT NULL constraint enforces a column to always contain a value. If it's omitted, the value remain default for that particular option.

Example

Creating basic table in MySQL with NOT NULL constrains:

CREATE TABLE customer

   (

   Cust_id NUMBER(2)  NOT NULL,

   LastName VARCHAR2(14),

   FirstName VARCHAR2(14)   NOT NULL,

   Address VARCHAR2(20),

   Telno NUMBER(20)

   ) engine = MyISAM;