创建与管理表
创建表
通过查询CREATETABLE命令帮助如下所示:
Command: CREATE TABLE Description: define a new table Syntax: CREATE [[GLOBAL | LOCAL] {TEMPORARY | TEMP}] TABLE table_name ( -->指定表类型:全局|本地临时 [ { column_name data_type [ DEFAULT default_expr ] [column_constraint [ ... ] [ ENCODING ( storage_directive [,...] ) ] -->指定表编码 ] | table_constraint -->指定表约束 | LIKE other_table [{INCLUDING | EXCLUDING} {DEFAULTS | CONSTRAINTS}] ...} [, ... ] ] [column_reference_storage_directive [, . ] ) [ INHERITS ( parent_table [, ... ] ) ] -->指定表继承关系 [ WITH ( storage_parameter=value [, ... ] ) -->指定存储空间 [ ON COMMIT {PRESERVE ROWS | DELETE ROWS | DROP} ] [ TABLESPACE tablespace ] -->指定表空间 [ DISTRIBUTED BY (column, [ ... ] ) | DISTRIBUTED RANDOMLY ] -->指定分布列 [ PARTITION BY partition_type (column) -->指定分区列 [ SUBPARTITION BY partition_type (column) ] -->指定子分区列 [ SUBPARTITION TEMPLATE ( template_spec ) ] [...] ( partition_spec ) | [ SUBPARTITION BY partition_type (column) ] [...] ( partition_spec [ ( subpartition_spec [(...)] ) ] )
where storage_parameter is: -->指定创建表存在的参数: APPENDONLY={TRUE|FALSE} -->指定是否可以appendonly BLOCKSIZE={8192-2097152} -->指定表块大小 ORIENTATION={COLUMN|ROW} -->指定表旋转方式 COMPRESSTYPE={ZLIB|QUICKLZ|RLE_TYPE|NONE} -->指定表的压缩方式 COMPRESSLEVEL={0-9} -->指定表的压缩级别 FILLFACTOR={10-100} -->指定表的占空因数 OIDS[=TRUE|FALSE] -->指定表的对象标识符
where column_constraint is: -->指定列约束如下: [CONSTRAINT constraint_name] -->约束名称 NOT NULL | NULL -->是否为空 | UNIQUE [USING INDEX TABLESPACE tablespace] -->唯一[使用索引表空间] [WITH ( FILLFACTOR = value )] | PRIMARY KEY [USING INDEX TABLESPACE tablespace] -->主键 [WITH ( FILLFACTOR = value )] | CHECK ( expression ) -->其它表达式约束
and table_constraint is: -->指定表约束如下: [CONSTRAINT constraint_name] -->指定表约束名称 UNIQUE ( column_name [, ... ] ) -->指定唯一的列名等 [USING INDEX TABLESPACE tablespace] -->唯一[使用索引表空间] [WITH ( FILLFACTOR=value )] | PRIMARY KEY ( column_name [, ... ] ) -->主键 [USING INDEX TABLESPACE tablespace] [WITH ( FILLFACTOR=value )] | CHECK ( expression ) -->其它表达式约束
where partition_type is: -->指定分区类型:LIST|RANGE LIST | RANGE
where partition_specification is: -->指定分区说明:包含分区元素 partition_element [, ...]
and partition_element is: -->指定分区元素说明: DEFAULT PARTITION name -->默认分区名称 | [PARTITION name] VALUES (list_value [,...] ) | [PARTITION name] START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE] [ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ] [ EVERY ([datatype] [number | INTERVAL] 'interval_value') ] | [PARTITION name] END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] [ EVERY ([datatype] [number | INTERVAL] 'interval_value') ] [ WITH ( partition_storage_parameter=value [, ... ] ) ] [column_reference_storage_directive [, ...] ]
[ TABLESPACE tablespace ]
where subpartition_spec or template_spec is:-->指定子分区说明或者模板分区说明 subpartition_element [, ...] and subpartition_element is: DEFAULT SUBPARTITION name | [SUBPARTITION name] VALUES (list_value [,...] ) | [SUBPARTITION name] START ([datatype] 'start_value') [INCLUSIVE | EXCLUSIVE] [ END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] ] [ EVERY ([datatype] [number | INTERVAL] 'interval_value') ] | [SUBPARTITION name] END ([datatype] 'end_value') [INCLUSIVE | EXCLUSIVE] [ EVERY ([datatype] [number | INTERVAL] 'interval_value') ] [ WITH ( partition_storage_parameter=value [, ... ] ) ] [column_reference_storage_directive [, ...] ] [ TABLESPACE tablespace ]
where storage_directive is: -->指定存储策略 COMPRESSTYPE={ZLIB | QUICKLZ | RLE_TYPE | NONE} | COMPRESSLEVEL={0-9} | BLOCKSIZE={8192-2097152}
Where column_reference_storage_directive is: -->指定列参考存储策略
COLUMN column_name ENCODING ( storage_directive [, ... ] ), ... | DEFAULT COLUMN ENCODING ( storage_directive [, ... ] )
|
关于上述命令的说明:
选择列的数据类型
- 对于字符类型,多数选择TEXT或者VARCHAR
- 对于数值类型,尽量选择更小的数据类型
- 对于打算用作连接的列,选择相同的数据