create table -- mysql

Basic syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]

[table_options] [select_statement]

TEMPORARY：该关键字表示用create table新建的表为临时表，此表在当前会话结束后将自动消失。临时表主要被应用于存储过程中，对于目前尚不支持存储过程的MySQL，该关键字一般不用。

IF NOT EXISTS：实际上是在建表前加上一个判断，只有该表目前尚不存在时才执行create table操作。用此选项可以避免出现表已经存在无法再新建的错误。

tbl_name：你所要创建的表的表名。该表名必须符合标识符规则。通常的做法是在表名中仅使用字母、数字及下划线。例如titles、our_sales、my_user1等都应该算是比较规范的表名。

create_definition:

col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]

[PRIMARY KEY] [reference_definition]

or PRIMARY KEY (index_col_name,...)

or KEY [index_name] (index_col_name,...)

or INDEX [index_name] (index_col_name,...)

or UNIQUE [INDEX] [index_name] (index_col_name,...)

or [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)

[reference_definition]

or CHECK (expr)

col_name：表中列的名字。必须符合标识符规则，而且在表中要唯一。

type：列的数据类型。有的数据类型需要指明长度n，并用括号括起。目前MySQL提供的数据类型详见MySQL进阶_列类型篇。

NOT NULL | NULL：指定该列是否允许为空。如果既不指定NULL也不指定NOT NULL，列被认为指定了NULL。

DEFAULT default_value：为列指定默认值。如果没有为列指定默认值，MySQL自 动地分配一个。如果列可以取NULL作为值，缺省值是NULL。如果列被声明为NOT NULL，缺省值取决于列类型： 1、对于没有声明AUTO_INCREMENT属性的数字类型，缺省值是0。对于一个AUTO_INCREMENT列，缺省值是在顺序中的下一个值。 2、对于除TIMESTAMP的日期和时间类型，缺省值是该类型适当的“零”值。对于表中第一个TIMESTAMP列，缺省值是当前的日期和时间。3、对 于除ENUM的字符串类型，缺省是空字符串。对于ENUM，缺省值是第一个枚举值。

AUTO_INCREMENT：设置该列有自增属性，只有整型列才能设置此属性。当你插入NULL值或0到一个AUTO_INCREMENT列中时，列被 设置为value 1，在这里value是此前表中该列的最大值。AUTO_INCREMENT顺序从1开始。每个表只能有一个AUTO_INCREMENT列，并且它必须 被索引。

CREATE TABLE examples

from:

Basic CREATE TABLE statement

A very basic CREATE TABLE statement which should work in any SQL database:

mysql> CREATE TABLE example (

id INT,

data VARCHAR(100)

);

Query OK, 0 rows affected (0.03 sec)

Creating a table with a particular storage engine

MySQL provides a variety of different table types with differing levels of functionality. The usual default, and most widely used, is MyISAM. Other storage types must be explicitly defined:

mysql> CREATE TABLE example_innodb (

id INT,

data VARCHAR(100)

) TYPE=innodb;

Query OK, 0 rows affected (0.03 sec)

Note that beginning with MySQL 4.1 ENGINE=innodb is the preferred method of defining the storage type.

Use SHOW CREATE TABLE (see ) to check that MySQL has created the table as you defined it.

Creating a table with auto_increment

Often you'll want to be able to automatically assign a sequential value to a column:

mysql> CREATE TABLE example_autoincrement (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

data VARCHAR(100)

);

Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO example_autoincrement (data)

-> VALUES ('Hello world');

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM example_autoincrement;

+----+-------------+

| id | data |

+----+-------------+

| 1 | Hello world |

+----+-------------+

1 row in set (0.01 sec)

Creating a table with the current timestamp

Often it's useful to have an automatic timestamp on each record. The MySQL special datatype TIMESTAMP enables you to keep track of changes to a record:

mysql> CREATE TABLE example_timestamp (

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

data VARCHAR(100),

cur_timestamp TIMESTAMP(8)

);

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO example_timestamp (data)

VALUES ('The time of creation is:');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM example_timestamp;

+----+--------------------------+---------------------+

| id | data | cur_timestamp |

+----+--------------------------+---------------------+

| 1 | The time of creation is: | 2004-12-01 20:37:22 |

+----+--------------------------+---------------------+

1 row in set (0.00 sec)

mysql> UPDATE example_timestamp 

SET data='The current timestamp is: ' 

WHERE id=1;

Query OK, 1 row affected (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM example_timestamp;

+----+---------------------------+---------------------+

| id | data | cur_timestamp |

+----+---------------------------+---------------------+

| 1 | The current timestamp is: | 2004-12-01 20:38:55 |

+----+---------------------------+---------------------+

1 row in set (0.01 sec)

The column cur_timestamp is automagically updated every time the record is changed.

Creating a table with TIMESTAMP DEFAULT NOW()

MySQL supports the construct TIMESTAMP DEFAULT NOW() only from verson 4.1:

CREATE TABLE example_default_now (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

data VARCHAR(100),

created TIMESTAMP DEFAULT NOW()

);

In this case the column created retains its initial value and is not changed during subsequent updates.

For versions prior to 4.1, the only workaround is to create two timestamp columns in a table, and explicitly set the second one when inserting the record. Remember: the first TIMESTAMP will be automagically updated on each record update.

Viewing a table definition

For basic information on table columns, use DESC tablename:

mysql> DESC example;

+-------+--------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| data | varchar(100) | YES | | NULL | |

+-------+--------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

Exact definition of the table:

mysql> SHOW CREATE TABLE example;

+---------+------------------------------------------------+

| Table | Create Table |

+---------+------------------------------------------------+

| example | CREATE TABLE `example` (

`id` int(11) default NULL,

`data` varchar(100) default NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+---------+------------------------------------------------+

1 row in set (0.00 sec)

(this example from MySQL 4.1)