创建表

📄字数 6.7K

👁️阅读量加载中...

数据库启动完成后，可创建表保存用户数据。

一、语法总览

1.1 语法格式

1.2 参数说明

OptTemp：指定创建的临时表类型，请参阅临时表
if_not_exists：指定是否在表不存在时才创建表：
- 未指定：直接执行创建表，若表已存在则报告错误
- IF NOT EXISTS：先判断表是否存在，若表不存在则创建表，否则不执行任何操作
table_name：指定表的标识符，请参阅标识符
table_elements：定义表的列和其他元素，请参阅表元素
on_commit_del：仅支持临时表，指定事务提交后的行为：
- 未指定：当事务提交时，保留临时表中的行
- ON COMMIT DELETE ROWS：当事务提交时，删除临时表中的所有行
- ON COMMIT PRESERVE ROWS：当事务提交时，保留临时表中的行
opt_phyical_props：指定可选的物理属性，请参阅物理属性
opt_partitioning_clause：指定可选的表一级分区配置，请参阅表一级分区
opt_subpartitioning_clause：指定可选的表二级分区配置，请参阅表二级分区
opt_constraint_props：指定可选的表外联约束，请参阅约束
opt_store_props：指定可选的存储属性，如存储位置、文件格式等，请参阅存储属性
opt_comment：指定备注信息，便于后续管理与维护，请参阅对象备注
opt_encrypt：指定用于数据存储加密的加密机名称，请参阅加密机

1.3 示例

示例1
创建一个人力资源模式、一张部门表以及一张雇员表：

sql

-- 创建人力资源模式
SQL> CREATE SCHEMA sch_hr;

-- 创建部门表
SQL> CREATE TABLE IF NOT EXISTS sch_hr.tab_departments(
         department_id NUMERIC(4,0) IDENTITY(1,1) PRIMARY KEY, -- 自增序列
         department_name VARCHAR(50) NOT NULL CONSTRAINT ck_name CHECK (LENGTH(department_name) >= 2) -- 值检查约束
     );

-- 创建雇员表
SQL> CREATE TABLE IF NOT EXISTS sch_hr.tab_employees (
         -- 列元素以及内联约束
         employee_id INT IDENTITY(1,1) PRIMARY KEY, -- 自增序列
         employee_name VARCHAR(50) NOT NULL CONSTRAINT ck_name CHECK (LENGTH(employee_name) >= 2), -- 值检查约束
         email VARCHAR(100) DEFAULT 'someone@example.com', -- 默认值
         hire_date DATE NOT NULL DEFAULT SYSDATE, -- 默认值
         salary NUMERIC(10,2) CHECK (salary > 0), -- 值检查约束
         department_id NUMERIC(4,0) CONSTRAINT fk_dept REFERENCES sch_hr.tab_departments(department_id), -- 外键约束
         -- 外联约束
         CONSTRAINT uk_email UNIQUE (email), -- 唯一约束
         CONSTRAINT ck_salary CHECK (salary <= 100000) -- 值检查约束
     )
     -- 一级范围分区
     PARTITION BY RANGE (salary)
     PARTITIONS (
         part1 VALUES LESS THAN (5000),
         part2 VALUES LESS THAN (10000),
         part3 VALUES LESS THAN (MAXVALUES)
     )
     -- 二级哈希分区
     SUBPARTITION BY HASH (employee_id) SUBPARTITIONS 2
     -- 存储参数
     PCTFREE 10
     -- 表备注
     COMMENT '雇员信息表';

-- 查询表tab_employees的表结构
 SQL> SELECT ut.table_name,uc.col_name,uc.not_null,uc.def_val,uc.on_null,ut.parti_key
      FROM SYS_COLUMNS uc JOIN SYS_TABLES ut ON uc.table_id=ut.table_id
      WHERE ut.table_name='tab_employees';

+---------------+---------------+----------+-----------------------+---------+-----------+
|  TABLE_NAME   |   COL_NAME    | NOT_NULL |        DEF_VAL        | ON_NULL | PARTI_KEY |
+---------------+---------------+----------+-----------------------+---------+-----------+
| TAB_EMPLOYEES | EMPLOYEE_ID   | T        | <NULL>                | 0       | "SALARY"  |
| TAB_EMPLOYEES | EMPLOYEE_NAME | T        | <NULL>                | 0       | "SALARY"  |
| TAB_EMPLOYEES | EMAIL         | F        | 'someone@example.com' | 0       | "SALARY"  |
| TAB_EMPLOYEES | HIRE_DATE     | T        | "SYSDATE"             | 0       | "SALARY"  |
| TAB_EMPLOYEES | SALARY        | F        | <NULL>                | 0       | "SALARY"  |
| TAB_EMPLOYEES | DEPARTMENT_ID | F        | <NULL>                | 0       | "SALARY"  |
+---------------+---------------+----------+-----------------------+---------+-----------+

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

sql

-- 创建原表
SQL> CREATE TABLE tab_if_not_exists(id INT);

-- 查询原表信息
SQL> SELECT table_name,table_type FROM dba_tables WHERE table_name='tab_if_not_exists';
+-------------------+------------+
|    TABLE_NAME     | TABLE_TYPE |
+-------------------+------------+
| TAB_IF_NOT_EXISTS | 0          |
+-------------------+------------+

-- 查询原表数据
SQL> SELECT * FROM tab_if_not_exists;
+----+
| ID |
+----+
+----+

-- 创建一张与原表不同列属性的表此处会返回警告
SQL> CREATE TABLE IF NOT EXISTS tab_if_not_exists(id INT,name VARCHAR(20));
Warning: [E9016] 同名Table对象TAB_IF_NOT_EXISTS已存在

-- 上述操作不对原表产生影响，再次查询原表信息
SQL> SELECT table_name,table_type FROM dba_tables WHERE table_name='tab_if_not_exists';
+-------------------+------------+
|    TABLE_NAME     | TABLE_TYPE |
+-------------------+------------+
| TAB_IF_NOT_EXISTS | 0          |
+-------------------+------------+

-- 再次查询原表数据
SQL> SELECT * FROM tab_if_not_exists;
+----+
| ID |
+----+
+----+

二、临时表

按照表的生命周期的不同，可分为普通表、局部临时表与全局临时表三种类型，其主要区别如下：

普通表：结构与数据均持久化存储且全局可见，会话结束时，结构与数据依旧存在
局部临时表：结构与数据仅在当前会话中存在，会话结束时，结构与数据立即销毁
全局临时表：结构持久化存储且全局可见，但数据在每个会话中独立存在、相互隔离，会话结束时，结构依旧存在，但当前会话的数据将立即销毁

注意

若要使用全局临时表，必须先启用参数support_global_tab，请参阅系统配置参数support_global_tab
临时表相较于普通表，除了生命周期不同，还有一定的使用限制：
- 临时表不支持分区
- 临时表不支持外键约束
- PL/SQL中临时表不支持任何约束

2.1 语法格式

2.2 参数说明

未指定：创建为普通表
TEMPORARY
TEMP
LOCAL TEMPORARY
LOCAL TEMP
均创建为局部临时表
GLOBAL TEMPORARY
GLOBAL TEMP
均创建为全局临时表

2.3 示例

示例1
创建一个人力资源模式、一张全局临时部门表以及一张局部临时雇员表。

sql

-- 启用全局临时表功能
SQL> SET support_global_tab ON;

-- 创建人力资源模式
SQL> CREATE SCHEMA sch_hr_temp;

-- 创建部门表
SQL> CREATE GLOBAL TEMP TABLE IF NOT EXISTS sch_hr_temp.tab_departments(
         department_id NUMERIC(4,0) IDENTITY(1,1) PRIMARY KEY, -- 自增序列
         department_name VARCHAR(50) NOT NULL CONSTRAINT ck_name CHECK (LENGTH(department_name) >= 2) -- 值检查约束
     );

-- 创建雇员表
SQL> CREATE TEMP TABLE IF NOT EXISTS sch_hr_temp.tab_employees (
         -- 列元素以及内联约束
         employee_id INT IDENTITY(1,1) PRIMARY KEY, -- 自增序列
         employee_name VARCHAR(50) NOT NULL CONSTRAINT ck_name CHECK (LENGTH(employee_name) >= 2), -- 值检查约束
         email VARCHAR(100) DEFAULT 'someone@example.com', -- 默认值
         hire_date DATE NOT NULL DEFAULT SYSDATE, -- 默认值
         salary NUMERIC(10,2) CHECK (salary > 0), -- 值检查约束
         department_id NUMERIC(4,0), -- 临时表不支持创建外键和被其他表外键引用
         -- 外联约束
         CONSTRAINT uk_email UNIQUE (email), -- 唯一约束
         CONSTRAINT ck_salary CHECK (salary <= 100000) -- 值检查约束
     )
     -- 事务提交时，保留临时表数据
     ON COMMIT PRESERVE ROWS
     -- 存储参数
     PCTFREE 10
     -- 表备注
     COMMENT '雇员信息表';

三、表元素

3.1 语法格式

3.2 参数说明

column_name：指定列标识符，请参阅标识符
type_name：指定列的数据类型，请参阅数据类型
opt_serial：指定可选的列的自增序列，请参阅自增序列
ColQualList：指定可选的内联约束，请参阅约束
opt_comment：指定列的备注，便于后续管理维护，请参阅列备注

3.3 示例

请参阅主要语法结构章节提供的示例。

四、自增序列

自增序列用于自动生成唯一的数值，通常作为表的主键。可通过系统配置参数def_identity_mode或会话级配置参数identity_mode控制自增序列的插入值填充模式。

自增序列是一组序列值，可以通过系统表sys_columns获取自增序列的ID号，即SERIAL_ID，再通过SERIAL_ID在系统表sys_sequences中查找对应自增序列的详细信息。

4.1 语法格式

4.2 参数说明

未指定：不设置自增序列
AUTO_INCREMENT：设置自增序列，等同于IDENTITY(1,1)
IDENTITY：设置自增序列，等同于IDENTITY(1,1)
IDENTITY( init_value , step_value )：设置自增序列，参数1为初始值，参数2为步长

4.3 示例

示例1
创建一张具有自增序列的表，然后查询自增序列的CURR_VAL当前值与STEP_VAL步长。

sql

-- 创建表
SQL> CREATE TABLE tab_serial_1 (id INT IDENTITY(5,2));

-- 查询自增序列当前值与步长
SQL> SELECT curr_val, step_val FROM sys_sequences seq
     INNER JOIN sys_columns col ON seq.seq_id = col.serial_id
     INNER JOIN sys_tables tab ON col.table_id = tab.table_id
     WHERE tab.table_name = 'tab_serial_1';
+----------+----------+
| CURR_VAL | STEP_VAL |
+----------+----------+
| 5        | 2        |
+----------+----------+

示例2
创建一张自增序列初始值为0，步长为-1的表，并插入数据。

sql

-- 创建表
SQL> CREATE TABLE tab_serial_2 (
         id INT IDENTITY(0,-1) PRIMARY KEY,
         name VARCHAR(50)
     );

-- 插入数据
SQL> INSERT INTO tab_serial_2 (name) VALUES ('小白'),('小何');

-- 查询数据
SQL> SELECT * FROM tab_serial_2;
+----+------+
| ID | NAME |
+----+------+
| 0  | 小白 |
| -1 | 小何 |
+----+------+

五、物理属性

5.1 语法格式

5.2 参数说明

未指定：所有物理属性将使用默认值，默认创建为堆表，请参阅各物理属性 未指定 值
ORGANIZATION HEAP：创建为堆表
ORGANIZATION EXTERNAL ( ... )：创建为外部文件表
- opt_file_type：外部文件类型
  - 未指定：指定为TEXT_FILE文本类型
  - TYPE type_name：指定文件类型，可选值为：
    - ROW：指定为HEAP_FILE堆类型，使用虚谷堆存储格式组织文件数据，分隔符设置无效
    - TXT：指定为TEXT_FILE文本类型，使用文本与分隔符组织文件数据
- opt_default_dir：指定外部文件位置默认前缀目录
  - 未指定：指定为/根目录
  - DEFAULT DIR dir_path：指定外部文件位置默认前缀目录为 dir_path
- ACCESS PARAMETERS ( ... )：指定访问参数值
  - 未指定：所有访问属性将使用默认值，请参阅各访问参数 未指定 值
  - RECORDS DELIMITED BY terminated_chars：指定记录分隔符为 terminated_chars，未指定为\n换行符，每行记录必须以此结束
  - FIELDS TERMINATED BY terminated_chars：指定字段分隔符为 terminated_chars，未指定为单个空格符，每列字段必须以此结束
- LOCATION file_path：指定外部文件位置，最终文件路径为默认前缀目录拼接此位置

注意

外部文件表最终文件路径必须位于虚谷数据库自有目录之下，请参阅系统映射文件参数配置。

5.3 示例

示例1
创建一张外部文件表，假定使用默认系统映射文件参数配置。
首先在虚谷自有目录下准备内容如下的数据文件./XHOME/TEMP/data.txt：

txt

col11|col21|col31|$col12|col22|col32|$

然后创建外部文件表并指向上述数据文件：

sql

SQL> CREATE TABLE tab_external(
         c1 VARCHAR, c2 VARCHAR, c3 VARCHAR
     )
     ORGANIZATION EXTERNAL (
         TYPE TXT
         DEFAULT DIR '/TEMP'
         ACCESS PARAMETERS (
             RECORDS DELIMITED BY '$'
             FIELDS TERMINATED BY '|'
         )
         LOCATION 'data.txt'
     );

六、存储属性

6.1 语法格式

6.2 参数说明

PCTFREE：指定块中预留空间的百分比，取值范围为[0,99]，未指定值或0值时块中预留的空间请参阅系统配置参数block_pctfree
PCTUSED：指定块中预留空间的（100-num）百分比，取值范围为[1,100]，未指定值或100值时块中预留的空间请参阅系统配置参数block_pctfree
HOTSPOT：指定插入热点数，取值范围为[0,max_hotspot_num]，未指定值或0值时为1个，请参阅插入热点、系统配置参数max_hotspot_num
COPY NUMBER：指定数据副本数量，取值范围为[1,3]，未指定值时请参阅系统配置参数default_copy_num
注意
当部署为单机模式时，此参数无实际作用。
COMPRESS：指定启用行数据压缩，未指定值时为禁用，请参阅行数据压缩
NOCOMPRESS：指定禁用行数据压缩，未指定值时为禁用，请参阅行数据压缩
ZONE BY zone_name：指定表存储在 zone_name 域内节点分配，未指定值时在空闲域分配，请参阅存储域
提示
存储域相关属性，仅在启用存储域功能时有效，请参阅系统配置参数zone_enable。
ZONE BY zone_id：指定表存储域标记，取值范围为[0,2147483647]
- 未指定：在空闲域分配
- 等于0：在全域分配
- 大于0：在指定域ID为 zone_id 的域中分配
ZONE BY LOCAL：指定在全域分配，但不包含空闲域

6.3 示例

示例1
创建一张设置了多项存储属性的表，假定已启用存储域。

sql

SQL> CREATE TABLE tab_store_props(c1 INT)
     PCTFREE 15     -- 块中预留15%空间
     HOTSPOT 1      -- 插入热点数为1个
     COPY NUMBER 3  -- 副本数量为3个
     COMPRESS       -- 启用存储压缩
     ZONE BY LOCAL; -- 在全域分配，但不包含空闲域

示例2
创建一张压缩表。查询表是否启用行数据压缩，可通过查询系统表sys_tables的COMPRESS_LEVEL字段，确认表是否启用行数据压缩功能，当COMPRESS_LEVEL=1时为已启用行数据压缩。

sql

-- 创建压缩表
SQL> CREATE TABLE tab_compress(c1 INTEGER, c2 VARCHAR) COMPRESS;

-- 查询表是否开启行数据压缩功能
SQL> SELECT table_name,compress_level FROM sys_tables WHERE table_name='tab_compress';
+--------------+----------------+
|  TABLE_NAME  | COMPRESS_LEVEL |
+--------------+----------------+
| TAB_COMPRESS | 1              |
+--------------+----------------+

七、插入热点

虚谷数据库中插入热点用于提升数据插入性能，数据插入时，将创建与插入热点数相同的新存储，多线程并行插入数据。

八、行数据压缩

虚谷数据库支持行级别的行数据压缩，行数据压缩可以节省磁盘存储空间和降低磁盘I/O。另外，可以在缓冲池中缓存更多的数据，这样就可以提高缓冲池命中率。但是，使用行数据压缩与解压缩也需要占用更多的CPU处理周期。数据行压缩节省出来的存储空间和额外对CPU的性能消耗在实际应用中需要做一个权衡，对于不同的业务环境和数据，采用数据行压缩方案不一定是最佳的方案，有时可能会适得其反。

默认情况下，行数据压缩功能为禁用状态，可通过在创建表对象时，尾部加上COMPRESS关键字开启表对象的行数据压缩功能，或尾部加上NOCOMPRESS关键字关闭表对象的行数据压缩功能。

创建表 ​

一、语法总览 ​

1.1 语法格式 ​

1.2 参数说明 ​

1.3 示例 ​

二、临时表 ​

2.1 语法格式 ​

2.2 参数说明 ​

2.3 示例 ​

三、表元素 ​

3.1 语法格式 ​

3.2 参数说明 ​

3.3 示例 ​

四、自增序列 ​

4.1 语法格式 ​

4.2 参数说明 ​

4.3 示例 ​

五、物理属性 ​

5.1 语法格式 ​

5.2 参数说明 ​

5.3 示例 ​

六、存储属性 ​

6.1 语法格式 ​

6.2 参数说明 ​

6.3 示例 ​

七、插入热点 ​

八、行数据压缩 ​

创建表