功能描述
用户数据库建立后,就可以定义基表来保存用户数据的结构。DM 数据库的表可以分为两类,分别为数据库内部表和外部表,数据库内部表由数据库管理系统自行组织管理,而外部表在数据库的外部组织,是操作系统文件。其中内部表包括:数据库基表、HUGE 表和水平分区表。手册中如无明确说明均指数据库基表。下面分别对这各种表的创建与使用进行详细描述。
定义数据库基表
用户数据库建立后,就可以定义基表来保存用户数据的结构。需指定如下信息:
1. 表名、表所属的模式名;
2. 列定义;
3. 完整性约束。
语法:
CREATE [[GLOBAL] TEMPORARY] TABLE [IF NOT EXISTS] <表名定义> <表结构定义>;
<表名定义> ::= [<模式名>.] <表名>
<表结构定义>::=<表结构定义 1> | <表结构定义 2>
<表结构定义 1>::= (<列定义> {,<列定义>} [,<表级约束定义>{,<表级约束定义>}])[<属性子句>] [<延迟段分 配 子 句 >] [< 压 缩 子 句 >][< 高 级 日 志 子 句 >] [<add_log 子 句 >] [<DISTRIBUTE 子句>][<AUTO_INCREMENT 子句>]
<表结构定义 2>::= [<属性子句>] [<延迟段分配子句>] [<压缩子句>]AS <不带 INTO 的 SELECT 语句>[<add_log 子句>] [<DISTRIBUTE 子句>];
<列定义> ::= <不同类型列定义> [<列定义子句>] [<STORAGE 子句>][<存储加密子句>][COMMENT '<列注释>']
<不同类型列定义> ::=<普通列列定义>|<虚拟列列定义>
<普通列定义>::= <列名> <数据类型>
<虚拟列列定义> ::= <列名>[<数据类型>] [GENERATED ALWAYS]AS (<虚拟列定义>) [VIRTUAL]
[VISIBLE]
<列定义子句> ::=
DEFAULT [ON NULL] <列缺省值表达式> |
<自增列子句> |
<列级约束定义> |
DEFAULT [ON NULL] <列缺省值表达式> <列级约束定义> |
<自增列子句> <列级约束定义> |
<列级约束定义> DEFAULT [ON NULL] <列缺省值表达式> |
<列级约束定义> <自增列子句>
<自增列子句> ::=
IDENTITY [(<种子>,<增量>)]|
AUTO_INCREMENT
<列级约束定义> ::= <列级完整性约束>{ <列级完整性约束>}
<列级完整性约束> ::= [CONSTRAINT <约束名>] <column_constraint_action> [<失效生效选项>]
<column_constraint_action>::=
[NOT] NULL |
<唯一性约束选项> [USING INDEX TABLESPACE {<表空间名> | DEFAULT}]|
<引用约束> |
CHECK (<检验条件>)|
NOT VISIBLE
<唯一性约束选项> ::=
PRIMARY KEY |
[NOT] CLUSTER PRIMARY KEY |
CLUSTER [UNIQUE] KEY |
UNIQUE
<引用约束> ::= [FOREIGN KEY] REFERENCES [PENDANT] [<模式名>.]<表名>[(<列名>{[,<列名>]})]
[MATCH <FULL|PARTIAL|SIMPLE>][<引用触发动作>] [WITH INDEX]
<引用触发动作> ::=
<UPDATE 规则> [<DELETE 规则>] |
<DELETE 规则> [<UPDATE 规则>]
<UPDATE 规则> ::= ON UPDATE <引用动作>
<DELETE 规则> ::= ON DELETE <引用动作>
<引用动作> ::= CASCADE | SET NULL | SET DEFAULT | NO ACTION
<失效生效选项>::=ENABLE | DISABLE
<STORAGE 子句> ::= STORAGE(<STORAGE 项> {,<STORAGE 项>})
<STORAGE 项> ::=
INITIAL <初始簇数目> |
NEXT <下次分配簇数目> |
MINEXTENTS <最小保留簇数目> |
ON <表空间名> |
FILLFACTOR <填充比例> |
BRANCH <BRANCH 数> |
BRANCH (<BRANCH 数>, <NOBRANCH 数>) |
NOBRANCH |
CLUSTERBTR |
WITH COUNTER |
WITHOUT COUNTER |
USING LONG ROW
<存储加密子句>::= <透明存储加密子句>|<半透明存储加密子句>
<透明存储加密子句>::= <透明存储加密子句 1>|<透明存储加密子句 2>
<透明存储加密子句 1>::= ENCRYPT [<透明加密用法>]
<透明存储加密子句 2>::= ENCRYPT <透明加密用法><散列选项>
<透明加密用法> ::=
WITH <加密算法> [<透明加密选项>] |
<透明加密选项>
<透明加密选项> ::= <透明加密选项 1> |<透明加密选项 2> |<透明加密选项 3>
<透明加密选项 1> ::= AUTO
<透明加密选项 2> ::= AUTO BY <列存储密钥>
<透明加密选项 3> ::= AUTO BY WRAPPED <列存储密钥的密文>
<半透明存储加密子句> ::= ENCRYPT [WITH <加密算法>] MANUAL [<半透明加密选项>][<散列选项>]
<半透明加密选项> ::= <半透明加密选项 1> | <半透明加密选项 2> | <半透明加密选项 3>
<半透明加密选项 1> ::= <可见用户列表>
<半透明加密选项 2> ::= BY <列存储密钥> [<可见用户列表>]
<半透明加密选项 3> ::= BY WRAPPED <列存储密钥的密文> [<可见用户列表>]
<可见用户列表> ::= USER ([<用户名> {,<用户名>}])
<散列选项> ::= HASH WITH <散列算法> [<加盐选项>]
<加盐选项> ::= [NO] SALT
<表级约束定义>::=[CONSTRAINT <约束名>] <表级约束子句>[<失效生效选项>]
<表级约束子句>::=<表级完整性约束>
<表级完整性约束> ::=
<唯一性约束选项> (<列名> {,<列名>}) [USING INDEX TABLESPACE{ <表空间名> | DEFAULT}]|
FOREIGN KEY (<列名>{,<列名>}) <引用约束> |
CHECK (<检验条件>)
<属性子句>::= <表空间子句>|
ON COMMIT <DELETE | PRESERVE> ROWS|
<空间限制子句>|
<STORAGE 子句>
<表空间子句>::=TABLESPACE <表空间名>
<空间限制子句> ::=
DISKSPACE LIMIT <空间大小>|
DISKSPACE UNLIMITED
<延迟段分配子句> ::=
SEGMENT CREATION IMMEDIATE |
SEGMENT CREATION DEFERRED
<压缩子句> ::=
COMPRESS |
COMPRESS (<列名> {,<列名>}) |
COMPRESS EXCEPT (<列名> {,<列名>})
<高级日志子句> ::= WITH ADVANCED LOG
<add_log 子句>::= ADD LOGIC LOG
<DISTRIBUTE 子句> ::=
DISTRIBUTED [RANDOMLY | FULLY]|
DISTRIBUTED BY [<HASH>](<列名> {,<列名>})|
DISTRIBUTED BY RANGE (<列名> {,<列名>})(<范围分布项> {,<范围分布项>})|
DISTRIBUTED BY LIST (<<列名> {,<列名>}>)(<列表分布项> {,<列表分布项>})
<范围分布项> ::=
VALUES LESS THAN (<范围表达式>{,<范围表达式>}) ON <实例名>|
VALUES EQU OR LESS THAN (<范围表达式>{,<范围表达式>}) ON <实例名>
<范围表达式> ::= MAXVALUE | <表达式>
<列表分布项> ::= VALUES (DEFAULT | <表达式>{,<表达式>}) ON <实例名>
<AUTO_INCREMENT 子句>::= AUTO_INCREMENT [=] <起始边界值>
<不带 INTO 的 SELECT 语句> ::= <查询表达式>|<带参数查询语句>
<带参数查询语句>::=<子查询> | (<带参数查询语句>)
参数:
举例:
create table s2.T9(id int primary key);
insert into s2.t9 values(1);commit;
CREATE TABLE S2.T8
(
PRODUCT_REVIEWID INT IDENTITY(1,1),
PRODUCTID INT NOT NULL,
NAME VARCHAR(50) NOT NULL,
REVIEWDATE DATE NOT NULL,
EMAIL VARCHAR(50) NOT NULL,
RATING INT NOT NULL,
COMMENTS TEXT,
PRIMARY KEY(PRODUCT_REVIEWID),
FOREIGN KEY(PRODUCTID) REFERENCES S2.T9(ID),
CHECK(RATING IN(1,2,3,4,5))
);
定义外部表
需指定如下信息:
1. 表名、表所属的模式名;
2. 列定义;
3. 控制文件和数据文件所在目录。
语法:
CREATE EXTERNAL TABLE [IF NOT EXISTS] <表名定义> <表结构定义>;
<表名定义> ::=[<模式名>.]<表名>
<表结构定义> ::= (<列定义> {,<列定义>}) <FROM 子句>
<列定义> ::= <列名> <数据类型> [COMMENT '<列注释>']
<列定义> 参见定义数据库基表说明
<FROM 子句> = <FROM 子句 1> | <FROM 子句 2>
<FROM 子句 1> ::= FROM <控制文件选项>
<FROM 子句 2> ::= FROM DATAFILE <数据文件选项> [<数据文件参数列表>]
<数据文件参数列表> ::= PARMS(<参数选项> {,<参数选项>})
<参数选项> ::=
FIELDS DELIMITED BY <表达式> |
RECORDS DELIMITED BY <表达式>|
ERRORS <n>|
BADFILE '<错误日志文件名称>'|
LOG '<日志文件名称>'|
NULL_STR <NULL 字符串>|
SKIP <跳过行数>|
CHARACTER_CODE <文件字符集>
<控制文件选项> ::= DEFAULT DIRECTORY <目录对象名> LOCATION ('<控制文件名>')
<数据文件选项> ::= DEFAULT DIRECTORY <目录对象名> LOCATION ('<数据文件名>')
参数:
举例:
1、编写数据文件
1|2|3
4|5|6
2、创建目录对象
CREATE OR REPLACE DIRECTORY "EXTDIR_1" AS 'e:\test';
3、建表
CREATE EXTERNAL TABLE EXT_TABLE1(
C1 INT,
C2 INT,
C3 INT
) FROM DATAFILE DEFAULT DIRECTORY EXTDIR_1 LOCATION ('data.txt') PARMS(FIELDS DELIMITED BY '|', RECORDS DELIMITED BY 0x0d0a);
select * from EXT_TABLE1;
定义HUGE表
达梦数据库中,表的数据存储方式分为行存储和列存储。行存储是以记录为单位进行存储的,数据页面中存储的是完整的若干条记录;列存储是以列为单位进行存储的,每一个列的所有行数据都存储在一起,而且一个指定的页面中存储的都是某一个列的连续数据。Huge File System(HFS)是达梦数据库实现的,针对海量数据进行分析的一种高效、简单的列存储机制。列存储表(也称为 HUGE 表)就是建立在 HFS 存储机制上的一种表。
HUGE 表的存储方式有以下几个优点:
(1)同一个列的数据都是连续存储的,可以加快某一个列的数据查询速度;
(2)连续存储的列数据,具有更大的压缩单元和数据相似性,可以获得远优于行存储的压缩效率,压缩的单位是区;
(3)条件扫描借助数据区的统计信息进行精确过滤,可以进一步减少 IO,提高扫描效率;
(4)允许建立二级索引;
(5)支持以 ALTER TABLE 的方式添加或者删除 PK 和 UNIQUE 约束。
DM 支持两种类型的 HUGE 表:非事务型 HUGE 表和事务型 HUGE 表
语法:
CREATE HUGE TABLE [IF NOT EXISTS] <表名定义> <表结构定义>[<PARTITION 子句>][<表空间子
句>][<STORAGE 子句 1>][<压缩子句>][<日志属性>][<DISTRIBUTE 子句>];
<表名定义> ::= [<模式名>.] <表名>
<表结构定义> ::=<表结构定义 1> | <表结构定义 2>
<表结构定义 1> ::= (<列定义> {,<列定义>} [<表级约束定义>{,<表级约束定义>}])
<表结构定义 2> ::= AS <不带 INTO 的 SELECT 语句>[<DISTRIBUTE 子句>]
< 列定义 > ::=< 列 名 > < 数据类型 >[DEFAULT [ON NULL] < 列 缺 省 值 表 达 式 >][< 列级约束定义>][<STORAGE 子句 2>][<存储加密子句>][COMMENT '<列注释>']
<表级约束定义> ::= [CONSTRAINT <约束名>] <表级完整性约束>
<表级完整性约束> ::=
<唯一性约束选项> (<列名> {,<列名>}) [USING INDEX TABLESPACE {<表空间名> | DEFAULT}]|CHECK (<检验条件>)
<列级约束定义> ::= <列级完整性约束>{ <列级完整性约束>}
<列级完整性约束> ::= [CONSTRAINT <约束名>]<huge_column_constraint_action>
<huge_column_constraint_action>::=
[NOT] NULL |
<唯一性约束选项> [USING INDEX TABLESPACE {<表空间名> | DEFAULT}]
<唯一性约束选项> ::=
PRIMARY KEY |
UNIQUE
<存储加密子句> ::= <透明存储加密子句>
<透明存储加密子句>参见 3.5.1.1 定义数据库基表
<PARTITION 子句> 参见 3.5.1.4 定义水平分区表
<表空间子句> ::= TABLESPACE <混合表空间名>
<STORAGE 子句 1> ::= STORAGE(<STORAGE1 子项>,{<STORAGE1 子项>,} ON <混合表空间名>)
<STORAGE1 子项>::=
[SECTION (<区大小>)] |
[INITIAL <文件初始大小>] |
[FILESIZE (<文件大小>)] |
[STAT [NONE| SYNCHRONOUS | ASYNCHRONOUS][ON | EXCEPT ( col_lst )] ] |
[<WITH|WITHOUT> DELTA]
<STORAGE 子句 2> ::= STORAGE(STAT NONE)
<压缩子句> ::=
COMPRESS [LEVEL <压缩级别>] [<压缩类型>]|
COMPRESS [LEVEL <压缩级别>] [<压缩类型>] (<列名> [LEVEL <压缩级别>] [<压缩类型>] {,<列名> [LEVEL <压缩级别>] [<压缩类型>] })|
COMPRESS [LEVEL <压缩级别>] [<压缩类型>] EXCEPT (<列名>{,<列名>})
<压缩类型>::= FOR 'QUERY [LOW | HIGH]'
<DISTRIBUTE 子句> ::=
DISTRIBUTED [RANDOMLY | FULLY]|
DISTRIBUTED BY [HASH](<列名> {,<列名>})|
DISTRIBUTED BY RANGE (<列名> {,<列名>})(<范围分布项> {,<范围分布项>})|
DISTRIBUTED BY LIST (DEFAULT|<<列名> {,<列名>}>)(<列表分布项> {,<列表分布项>})
<范围分布项> ::=
VALUES LESS THAN (<表达式>{,<表达式>}) ON <实例名> |
VALUES EQU OR LESS THAN (<表达式>{,<表达式>}) ON <实例名>
<列表分布项> ::= VALUES (<表达式>{,<表达式>}) ON <实例名>
<日志属性> ::=
LOG NONE|
LOG LAST|
LOG ALL
参数:
举例(若创建数据库时使用参数 HUGE_WITH_DELTA 的缺省值 1,则不支持创建非事务型 HUGE 表):
创建非事务性HUGE表:
CREATE HUGE TABLE T1 (A INT, B INT) STORAGE(WITHOUT DELTA);
创建事务性HUGE表:
CREATE HUGE TABLE T2 (A INT, B INT) STORAGE(WITH DELTA);
定义水平分区表
水平分区包括范围分区、哈希分区和列表分区三种。水平分区表的创建需要通过<PARTITION 子句>指定。
范围(RANGE)分区,按照分区列的数据范围,确定实际数据存放位置的划分方式。
列表(LIST)分区,通过指定表中的某一个列的离散值集,来确定应当存储在一起的数据。范围分区是按照某个列上的数据范围进行分区的,如果某个列上的数据无法通过划分范围的方法进行分区,并且该列上的数据是相对固定的一些值,可以考虑使用 LIST 分区。一般来说,对于数字型或者日期型的数据,适合采用范围分区的方法;而对于字符型数据,取值比较固定的,则适合于采用 LIST 分区的方法。
哈希(HASH)分区,对分区列值进行 HASH 运算后,确定实际数据存放位置的划分方式,主要用来确保数据在预先确定数目的分区中平均分布,允许只建立一个 HASH 分区。在很多情况下,用户无法预测某个列上的数据变化范围,因而无法实现创建固定数量的范围分区或 LIST 分区。在这种情况下,DM 哈希分区提供了一种在指定数量的分区中均等地划分数据的方法,基于分区键的散列值(HASH 值)将行映射到分区中。当用户向表中写入数据时,数据库服务器将根据一个哈希函数对数据进行计算,把数据均匀地分布在各个分区中。在哈希分区中,用户无法预测数据将被写入哪个分区中。
在很多情况下,经过一次分区并不能精确地对数据进行分类,这时需要多级分区表。在进行多级分区的时候,三种分区类型还可以交叉使用。
语法:
CREATE TABLE [IF NOT EXISTS] <表名定义> <表结构定义>;
<表名定义> ::= [<模式名>.] <表名>
<表结构定义>::= (<列定义> {,<列定义>} [,<表级约束定义>{,<表级约束定义>}])<PARTITION子句>[<属性子句>][<压缩子句>] [<ROW MOVEMENT子句>][<add_log子句>][<DISTRIBUTE子句>]
<列定义>、<表级约束定义>、<属性子句>、<压缩子句>、<DISTRIBUTE子句>的语法,参见定义数据库基表说明
<PARTITION子句> ::= PARTITION BY <PARTITION项>
<PARTITION项> ::=
RANGE (<列名>{,<列名>}) [INTERVAL (<间隔表达式>)] [<SUBPARTITION 子句>{,< SUBPARTITION 子句>}}](<RANGE分区项> {,<RANGE分区项>}) |
HASH (<列名>{,<列名>}) [<SUBPARTITION 子句>{,<SUBPARTITION 子句>}}]PARTITIONS <分区数> [<STORAGE HASH 子句>]|
HASH(<列名>{,<列名>})[<SUBPARTITION 子句>{,<SUBPARTITION 子句>}] (<HASH分区项> {,<HASH分区项>})|
LIST(<列名>)[<SUBPARTITION 子句>{,<SUBPARTITION 子句>}](<LIST分区项> {,<LIST分区项>})
<RANGE分区项>::= PARTITION <分区名> VALUES [EQU OR] LESS THAN (< <常量表达式>|<日期函数表达式>|MAXVALUE >{,< <常量表达式>|<日期函数表达式>|MAXVALUE >}) [<表空间子句>][<STORAGE子句>][<子分区描述项>]
<日期函数表达式> ::= <to_date函数表达式> | <to_datetime函数表达式> | <to_timestamp函数表达式>
<HASH分区项>::= PARTITION <分区名> [<表空间子句>][<STORAGE子句>][<子分区描述项>]
<LIST分区项>::= PARTITION <分区名> VALUES (DEFAULT|<表达式>,{<表达式>}>) [<表空间子句>][<STORAGE子句>][<子分区描述项>]
<子分区描述项> ::=
(<RANGE子分区描述项>{,<RANGE子分区描述项>})|
(<HASH子分区描述项>{,<HASH子分区描述项>})|
SUBPARTITIONS <分区数> [<STORAGE HASH 子句>]|
(<LIST子分区描述项>{,<LIST子分区描述项>})
<RANGE子分区描述项> ::= <RANGE子分区项>[<子分区描述项>]
<HASH子分区描述项> ::= <HASH子分区项>[<子分区描述项>]
<LIST子分区描述项> ::= <LIST子分区项>[<子分区描述项>]
<RANGE子分区项> ::=
SUBPARTITION <分区名> VALUES [EQU OR] LESS THAN (<常量表达式|<日期函数表达式>|MAXVALUE>){,<常量表达式|<日期函数表达式>|MAXVALUE>}) [<表空间子句>][<STORAGE子句>]
<HASH子分区项> ::= SUBPARTITION <分区名> [<表空间子句>][<STORAGE子句>]
<LIST子分区项> ::= SUBPARTITION <分区名> VALUES (DEFAULT|<<表达式>,{<表达式>}>) [<表空间子句>][<STORAGE子句>]
<间隔表达式> ::= <日期间隔函数> | <数值常量>
<SUBPARTITION 子句>::= <RANGE子分区模板项>|<HASH子分区模板项>|<LIST子分区模板项>
<RANGE子分区模板项> ::= SUBPARTITION BY RANGE (<列名>{,<列名>})[SUBPARTITION TEMPLATE (<RANGE子分区项> {,<RANGE子分区项>})]
<HASH子分区模板项> ::=
SUBPARTITION BY HASH (<列名>{,<列名>})SUBPARTITION TEMPLATE SUBPARTITIONS <分区数> [<STORAGE HASH 子句>]|
SUBPARTITION BY HASH (<列名>{,<列名>})SUBPARTITION TEMPLATE (<HASH子分区项> {,<HASH子分区项>})
<LIST子分区模板项> ::= SUBPARTITION BY LIST (<列名>{,<列名>})[SUBPARTITION TEMPLATE (<LIST子分区项> {,<LIST子分区项>})]
<STORAGE HASH 子句> ::= STORE IN (<表空间名列表>)
<分区表封锁子句> ::=
LOCK PARTIITONS |
LOCK ROOT
<表空间子句> ::= 3.5.1.1节中<表空间子句>
<STORAGE子句> ::= 3.5.1.1节中<STORAGE子句> | HASHPARTMAP(<哈希分区表定位方式>)
<哈希分区表定位方式> ::= 0|1|2
<ROW MOVEMENT子句> ::=
ENABLE ROW MOVEMENT |
DISABLE ROW MOVEMENT
参数:
举例:
创建一个范围分区表 callinfo,用来记录用户 2024 年的电话通讯信息,包括主叫号码、被叫号码、通话时间和时长,并且根据季度进行分区
CREATE TABLE callinfo1(
caller CHAR(15),
callee CHAR(15),
time DATETIME,
duration INT
)
PARTITION BY RANGE(time)(
PARTITION p1 VALUES LESS THAN ('2024-04-01'),
PARTITION p2 VALUES LESS THAN ('2024-07-01'),
PARTITION p3 VALUES LESS THAN ('2024-10-01'),
PARTITION p4 VALUES EQU OR LESS THAN (MAXVALUE));
查询p1中的数据:
SELECT * FROM callinfo PARTITION (p1);
创建List分区表:
CREATE TABLE sales(
sales_id INT,
saleman CHAR(20),
saledate DATETIME,
city CHAR(10)
)
PARTITION BY LIST(city)(
PARTITION p1 VALUES ('北京', '天津'),
PARTITION p2 VALUES ('上海', '南京', '杭州'),
PARTITION p3 VALUES ('武汉', '长沙'),
PARTITION p4 VALUES ('广州', '深圳'),
PARTITION p5 VALUES (default)
);
创建HASH分区表:
CREATE TABLE sales02(
sales_id INT,
saleman CHAR(20),
saledate DATETIME,
city CHAR(10)
)
PARTITION BY HASH(city)
PARTITIONS 4 STORE IN (ts1, ts2, ts3, ts4);