"CREATE TABLE"语法及例子
作者：kider沉寂的列规格变更
（在有些情况下，较早版本的MySQL会静默地更改在CREATE TABLE或ALTER TABLE语句中给定的列规约。在MySQL 5.1中不会进行这类变更。如果使用指定的数据类型无法创建列，则会出现错误。）
CREATE TABLE tbl_name
[(create_definition,...)]
或：
CREATE TABLE tbl_name
[(] LIKE old_tbl_name [)];
create_definition:
column_definition
| ] PRIMARY KEY (index_col_name,...)
| KEY (index_col_name,...)
| INDEX (index_col_name,...)
| ] UNIQUE
(index_col_name,...)
| (index_col_name,...)
| ] FOREIGN KEY
(index_col_name,...)
| CHECK (expr)
column_definition:
col_name type
| KEY]
type:
TINYINT[(length)]
| SMALLINT[(length)]
| MEDIUMINT[(length)]
| INT[(length)]
| INTEGER[(length)]
| BIGINT[(length)]
| REAL[(length,decimals)]
| DOUBLE[(length,decimals)]
| FLOAT[(length,decimals)]
| DECIMAL(length,decimals)
| NUMERIC(length,decimals)
| DATE
| TIME
| TIMESTAMP
| DATETIME
| CHAR(length)
| VARCHAR(length)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT
| TEXT
| MEDIUMTEXT
| LONGTEXT
| ENUM(value1,value2,value3,...)
| SET(value1,value2,value3,...)
| spatial_type
index_col_name:
col_name [(length)]
reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION
table_options: table_option ...
table_option:
{ENGINE|TYPE} = engine_name
| AUTO_INCREMENT = value
| AVG_ROW_LENGTH = value
| CHARACTER SET charset_name
| CHECKSUM = {0 | 1}
| COMMENT = 'string'
| C
| MAX_ROWS = value
| MIN_ROWS = value
| PACK_KEYS = {0 | 1 | DEFAULT}
| PASSWORD = 'string'
| DELAY_KEY_WRITE = {0 | 1}
| ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| UNION = (tbl_name[,tbl_name]...)
| INSERT_METHOD = { NO | FIRST | LAST }
| DATA DIRECTORY = 'absolute path to directory'
| INDEX DIRECTORY = 'absolute path to directory'
partition_options:
PARTITION BY
HASH(expr)
| KEY(column_list)
|RANGE(expr)
|LIST(column_list)
[SUBPARTITION BY
HASH(expr)
| KEY(column_list)
]
[(partition_definition), [(partition_definition)], ...]
partition_definition:
PARTITION partition_name
[VALUES {
LESS THAN (expr) | MAXVALUE
| IN (value_list) }]
[ ENGINE [=] engine-name]
'comment_text' ]
'data_dir']
'index_dir']
max_number_of_rows]
min_number_of_rows]
(tablespace_name)]
node_group_id]
[(subpartition_definition), [(subpartition_definition)], ...]
subpartition_definition:
SUBPARTITION logical_name
[ ENGINE [=] engine-name]
'comment_text' ]
'data_dir']
'index_dir']
max_number_of_rows]
min_number_of_rows]
(tablespace_name)]
node_group_id]
select_statement:
SELECT ... (Some legal select statement)
CREATE TABLE用于创建带给定名称的表。您必须拥有表CREATE权限。
例子：
部分复制表
1、已经有table1存在如下;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| a | int(11) | NO | PRI | NULL | auto_increment |
| b | varchar(30) | YES| | NULL | |
| c | varchar(50) | YES| | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
2、创建（复制）表
mysql> create table table2 select a, b as bb from table1;
Query OK, 6 rows affected (0.09 sec)
Records: 6Duplicates: 0Warnings: 0
3、结果
mysql> desc table2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | int(11) | NO | | 0 | |
| bb | varchar(30) | YES| | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
数据也会有了
mysql> select * from table2;
+----+----------+
| a| bb |
+----+----------+
| 1 | ab |
| 2 | bc |
| 3 | ca |
| 4 | s |
| 5 | Ac |
| 6 | Ba |
+----+----------+
6 rows in set (0.00 sec)
[ 本帖最后由 kider 于 2007-7-26 15:38 编辑 ]
页:
[1]