文档中心

创建表 ​

数据库启动完成后，可自定义基表保存用户数据。

主要语法结构 ​

语法格式 ​

sql
createtabstmt::=
CREATE [opt_temp] TABLE [opt_if_not_exists] [schema.] tab_name (table_elements)
    [on_commit_del] [opt_partitioning_clause] [opt_subpartitioning_clause]
    [opt_constraint_props] [opt_store_props] [opt_comment]

参数说明 ​

  • CREATE：关键字，表示创建操作。
  • [opt_temp]：可选参数，用于指定创建的是临时表。有效选项包括：
    • TEMPORARY
    • TEMP
    • LOCAL TEMPORARY
    • LOCAL TEMP
    • GLOBAL TEMPORARY
    • GLOBAL TEMP
  • TABLE：关键字，表示创建的对象是表。
  • [opt_if_not_exists]：可选参数，如果表已经存在，则不执行创建操作。使用 IF NOT EXISTS 关键字。
  • [schema.]：可选参数，用于指定表所属的模式。如果省略，则使用默认模式。
  • tab_name：表的名称。
  • (table_elements)：括号内定义表的列和其他元素。
  • [on_commit_del]：对于临时表，定义事务提交后如何处理临时表的数据。
  • [opt_partitioning_clause]：指定表的分区信息，有助于提高大型表的查询性能。
  • [opt_subpartitioning_clause]：子分区信息，进一步细化分区规则。
  • [opt_constraint_props]：表级别的约束属性，如主键、外键、唯一性等。
  • [opt_store_props]：存储属性，比如存储位置、文件格式等。
  • [opt_comment]：对表或列的注释，方便后续管理和维护。

表元素定义table_elements ​

语法格式 ​

sql
table_elements::=
    col_elements[,col_elements]...

参数说明 ​

col_elements：定义表的列和其他元素。

列元素定义col_elements ​

语法格式 ​

sql
col_elements::=
    col_name type [IDENTITY(B,S)] [NOT NULL | NULL] [DEFAULT default_value] 
    [UNIQUE | PRIMARY KEY | CHECK(expression)] 
    [COMMENT 'string'] [reference_definition]
|   CONSTRAINT name [CHECK(expression) | UNIQUE(columnList) | PRIMARY KEY(columnList) | FOREIGN KEY opt_fk_name(columnList) reference_definition]

参数说明 ​

  • col_name type [IDENTITY(B,S)] [NOT NULL | NULL] [DEFAULT default_value]：定义列的基本属性。
    • col_name：列名。
    • type：列的数据类型。
    • [IDENTITY(B,S)]：可选参数，表示列是否为自增列及其起始值和步长。
    • [NOT NULL | NULL]：可选参数，指定列是否允许空值。
    • [DEFAULT default_value]：可选参数，指定列的默认值。
  • [UNIQUE | PRIMARY KEY | CHECK(expression)]：可选参数，用于定义列级别的唯一性、主键或检查约束。
    • UNIQUE: 列的值必须唯一。
    • PRIMARY KEY: 列为主键。
    • CHECK(expression): 列值必须满足某个条件。
  • [COMMENT 'string']：可选参数，为列添加注释。
  • [reference_definition]：可选参数，用于定义外键约束。
  • CONSTRAINT name [CHECK(expression) | UNIQUE(columnList) | PRIMARY KEY(columnList) | FOREIGN KEY opt_fk_name(columnList) reference_definition]：对表中列或列的组合设置的一种限制条件。
    • name: 约束的名称，用于标识该约束。
    • CHECK(expression): 检查约束，确保列的值满足某个条件。
    • UNIQUE(columnList): 唯一约束，确保指定列的组合值是唯一的。
    • PRIMARY KEY(columnList): 主键约束，确保指定列的组合值是唯一的，并且不允许为空。
    • FOREIGN KEY opt_fk_name(columnList) reference_definition: 外键约束，确保指定列的值引用另一个表中的主键值。
      • opt_fk_name: 可选的外键名称。
      • columnList: 要定义为外键的列列表。

外键定义reference_definition ​

语法格式 ​

sql
reference_definition::=
    REFERENCES tbl_name [(index_col_name,...)]
    [ON {UPDATE | DELETE} {CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
    [ON DELETE {CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

参数说明 ​

  • REFERENCES tbl_name [(index_col_name,...)]：指定外键引用的目标表及列。
    • tbl_name: 引用的表的名称。
    • index_col_name,...: 引用表中的列列表。通常情况下，这些列是引用表的主键或唯一键。
  • [ON UPDATE {CASCADE | SET NULL | NO ACTION | SET DEFAULT}]：可选参数，定义更新操作时的行为。
  • [ON DELETE {CASCADE | SET NULL | NO ACTION | SET DEFAULT}]：可选参数，定义删除操作时的行为。
    • CASCADE: 表示当父表记录变更时子表受影响记录的相应字段值随之变更，当父表记录删除时，子表中受影响记录跟着删除。
    • SET NULL: 表示父表更改或删除，子表记录的相应字段值置为空值。
    • NO ACTION: 表示当父表记录变更时，导致子表中某些记录不再满足外键约束，系统将禁止父表作变更，其效果为更改父表的事务将被回滚。
    • SET DEFAULT: 表示父表更改或删除时，子表相应记录的相应字段值置为默认值，若该字段无默认值则置空。

事务提交行为on_commit_del ​

语法格式 ​

sql
on_commit_del::=
    ON COMMIT DELETE ROWS
|   ON COMMIT PRESERVE ROWS

参数说明 ​

  • ON COMMIT DELETE ROWS：当事务提交时，删除临时表中的所有行。
  • ON COMMIT PRESERVE ROWS：当事务提交时，保留临时表中的行。

表一级分区定义opt_partitioning_clause ​

语法格式 ​

sql
opt_partitioning_clause::=
    PARTITION BY RANGE (name_list) [INTERVAL expr {DAY|MONTH|YEAR}] PARTITIONS (range_parti_item[,range_parti_item]...)
|   PARTITION BY LIST (name_list) PARTITIONS (list_parti_item[,list_parti_item]...)
|   PARTITION BY HASH (name_list) PARTITIONS ICONST
|   PARTITION BY HASH (name_list) PARTITIONS (name_list)

参数说明 ​

  • PARTITION BY RANGE (name_list) [INTERVAL expr {DAY|MONTH|YEAR}] PARTITIONS (range_parti_item[,range_parti_item]...)：按范围分区。
  • PARTITION BY LIST (name_list) PARTITIONS (list_parti_item[,list_parti_item]...)：按列表分区。
  • PARTITION BY HASH (name_list) PARTITIONS ICONST：按哈希值分区，ICONST 是分区的数量。
  • PARTITION BY HASH (name_list) PARTITIONS (name_list)：按哈希值分区，name_list 是分区的名称列表。

表二级分区定义opt_subpartitioning_clause ​

语法格式 ​

sql
opt_subpartitioning_clause::=
    SUBPARTITION BY HASH (name_list) SUBPARTITIONS ICONST
|   SUBPARTITION BY HASH (name_list) SUBPARTITIONS (name_list)
|   SUBPARTITION BY LIST (name_list) SUBPARTITIONS (list_parti_item[,list_parti_item]...)
|   SUBPARTITION BY RANGE (name_list) SUBPARTITIONS (range_parti_item[,range_parti_item]...)

参数说明 ​

  • SUBPARTITION BY HASH (name_list) SUBPARTITIONS ICONST：定义哈希子分区。
  • SUBPARTITION BY HASH (name_list) SUBPARTITIONS (name_list)：定义哈希子分区，使用名称列表。
  • SUBPARTITION BY LIST (name_list) SUBPARTITIONS (list_parti_item[,list_parti_item]...)：定义列表子分区。
  • SUBPARTITION BY RANGE (name_list) SUBPARTITIONS (range_parti_item[,range_parti_item]...)：定义范围子分区。

列约束属性opt_constraint_props ​

语法格式 ​

sql
opt_constraint_props::=
    UNIQUE (name_list)
|   PRIMARY KEY
|   CONSTRAINT colid
|   [UNIQUE (name_list) | PRIMARY KEY | CONSTRAINT colid]...

参数说明 ​

  • UNIQUE (name_list)：定义唯一性约束。
  • PRIMARY KEY：定义主键约束。
  • CONSTRAINT colid：定义约束的名称。
  • [UNIQUE (name_list) | PRIMARY KEY | CONSTRAINT colid]...：可以定义多个约束。

表存储定义信息store_prop ​

语法格式 ​

sql
store_prop::=
    PCTFREE expr
|   PCTUSED expr
|   COPY NUMBER expr
|   {COMPRESS|NOCOMPRESS}

参数说明 ​

  • PCTFREE expr：定义块中预留的空间百分比。
  • PCTUSED expr：定义块中已使用的空间百分比。
  • COPY NUMBER expr：定义复制的数量。
  • {COMPRESS|NOCOMPRESS}：定义是否压缩数据。

注释opt_comment ​

语法格式 ​

sql
opt_comment::=
    COMMENT SCONST

参数说明 ​

COMMENT 'string'：为表或列添加注释。

示例 ​

  • 示例1
    在sysdba模式下创建一张名为test_tab的表，该表包含6列，该表按照“所属省份”进行列表分区，分区键值包括：北京、四川、重庆以及其他值（若无OTHERVALUES分区，插入所属省份值非指定列表中的值则无法插入，OTHERVALUES范围分区中对应MAXVALUES），并且指定该表存储版本数为2（COPY NUMBER在多节点下有效且不能大于3；单机默认为1，指定大于1无效）。

    sql
    SQL> CREATE TABLE sysdba.test_tab(
-- 列定义并给每列添加注释
kid INT IDENTITY(1,1) COMMENT '身份ID',
name CHAR(10) NOT NULL COMMENT '姓名',
age TINYINT DEFAULT '0' COMMENT '年龄',
birth DATETIME COMMENT '生日',
proc VARCHAR(10) COMMENT '所属省份',
comments VARCHAR COMMENT '备注')
-- 分区定义
PARTITION BY LIST(proc)
PARTITIONS(('北京'),('四川'),('重庆'),(OTHERVALUES))COPY NUMBER 2;

  • 示例2
    在sysdba模式下创建一张人员信息表test_tab2，该表以生日字段进行范围分区，且该分区为自动扩展分区，起始分区为1900年1月1日，后续系统将根据插入生日时间进行自动扩展。

    sql
    SQL> CREATE TABLE sysdba.test_tab2(
-- 列定义并给每列添加注释
kid INT IDENTITY(1,1) COMMENT '身份ID',
name CHAR(10) NOT NULL COMMENT '姓名',
age TINYINT DEFAULT '0' COMMENT '年龄',
birth DATETIME COMMENT '生日',
proc VARCHAR(10) COMMENT '所属省份',
comments VARCHAR COMMENT '备注')
-- 分区定义
PARTITION BY RANGE(birth) INTERVAL 1 MONTH
PARTITIONS(part1 VALUES LESS THAN('1900-01-01 00:00:00'))
-- 给表添加注释
COMMENT '人员信息表';

  • 示例3
    创建一个与原表同名但属性不同的表，此操作不会对原表产生影响。

    sql
    SQL> CREATE TABLE test_tab(id int);
SQL> SELECT dt.table_name,dt.table_type FROM dba_tables dt WHERE dt.table_name='TEST_TAB';
TABLE_NAME | TABLE_TYPE | 
------------------------------------------------------------------------------
TEST_TAB| 0 |
SQL> SELECT * FROM test_tab;
ID
------------------------------------------------------------------------------

-- 创建一个与原不表不同列属性的表此处会返回警告
SQL> CREATE TABLE IF NOT EXISTS test_tab(id int,name varchar(20));
Warnning: [E9016] 同名Table对象TEST_TAB已存在
-- 上述操作不对原表产生影响
SQL> SELECT dt.table_name,dt.table_type FROM dba_tables dt WHERE dt.table_name='TEST_TAB';
TABLE_NAME | TABLE_TYPE | 
------------------------------------------------------------------------------
TEST_TAB| 0 |

SQL> SELECT * FROM test_tab;
ID
------------------------------------------------------------------------------