DM8_SQL语言（三）达梦数据库数据定义语句二：管理表

管理表

一、表定义语句

用户数据库建立后，就可以定义基表来保存用户数据的结构。DM 数据库的表可以分为 两类，分别为数据库内部表和外部表，数据库内部表由数据库管理系统自行组织管理，而外 部表在数据库的外部组织，是操作系统文件。其中内部表包括：数据库基表、HUGE 表和水平分区表。手册中如无明确说明均指数据库基表。下面分别对这各种表的创建与使用进行详 细描述。

1.定义数据库基表

用户数据库建立后，就可以定义基表来保存用户数据的结构。需指定如下信息：

1. 表名、表所属的模式名
2. 列定义
3. 完整性约束

语法格式

CREATE [[GLOBAL] TEMPORARY] TABLE <表名定义> <表结构定义>;
<表名定义> ::= [<模式名>.] <表名>
<表结构定义>::=<表结构定义 1> | <表结构定义 2>
<表结构定义 1>::= (<列定义> {
   ,<列定义>} [,<表级约束定义>{
   ,<表级约束定义>}]) [ON COMMIT <DELETE | PRESERVE> ROWS] [<空间限制子句>] [<STORAGE 子句>][<压缩子句>][< 高级日志子句>] [<add_log 子句>]  [<DISTRIBUTE 子句>]
<表结构定义 2>::= [ON COMMIT <DELETE | PRESERVE>	ROWS] [<空间限制子句 >][<STORAGE 子句>] [<压缩子句>]AS <不带 INTO 的 SELECT 语句>[<add_log 子句>] [<DISTRIBUTE子句>];

<列定义> ::= <不同类型列定义> [<列定义子句>] [<STORAGE 子句>][<存储加密子句>]
<不同类型列定义> ::=<普通列定义>|<虚拟列列定义>
<普通列定义>::= <列名> <数据类型>
<虚拟列列定义> ::= <列名>[<数据类型>] [GENERATED ALWAYS]AS (<虚拟列定义>) [VIRTUAL] [VISIBLE]
<列定义子句> ::=DEFAULT <列缺省值表达式> |<IDENTITY 子句> |<列级约束定义> |DEFAULT <列缺省值表达式> <列级约束定义> |<IDENTITY 子句> <列级约束定义> |<列级约束定义> DEFAULT <列缺省值表达式> |<列级约束定义> <IDENTITY 子句>
<IDENTITY 子句> ::= IDENTITY [(<种子>,<增量>)]
<列级约束定义> ::= <列级完整性约束>{
   ,<列级完整性约束>}
<列级完整性约束> ::= [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 <加密算法>
<加密模式> ::= <透明加密模式> | <半透明加密模式>
<透明加密模式> ::= AUTO
<半透明加密模式> ::= MANUAL
<散列选项> ::= HASH WITH [<密码引擎名>.]<散列算法> [<加盐选项>]
<加盐选项> ::= [NO] SALT
<加密算法> ::= <DES_ECB | DES_CBC | DES_CFB|DES_OFB|DESEDE_ECB| DESEDE_CBC | DESEDE_CFB|DESEDE_OFB | AES128_ECB |AES128_CBC | AES128_CFB | AES128_OFB | AES192_ECB |AES192_CBC | AES192_CFB | AES192_OFB | AES256_ECB | AES256_CBC | AES256_CFB | AES256_OFB | RC4>
<散列算法> ::= <MD5 | SHA1 | SHA224 | SHA256 | SHA384 | SHA512>

<表级约束定义>::=[CONSTRAINT <约束名>] <表级约束子句>[<失效生效选项>]
<表级约束子句>::=<表级完整性约束>
<表级完整性约束> ::=<唯一性约束选项> (<列名> {
   ,<列名>}) [USING INDEX TABLESPACE{
    <表空间名> | DEFAULT}]|FOREIGN KEY (<列名>{
   ,<列名>}) <引用约束> |CHECK (<检验条件>)
<空间限制子句> ::=DISKSPACE LIMIT <空间大小>|DISKSPACE UNLIMITED
<压缩子句> ::=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 <实例名>
<列表分布项> ::= VALUES (<表达式>{
   ,<表达式>}) ON <实例名>
<不带 INTO 的 SELECT 语句> ::= <查询表达式>|<带参数查询语句>
<带参数查询语句>::=<子查询>|(<带参数查询语句>)

参数

1. <模式名>	指明该表属于哪个模式，缺省为当前模式；
2. <表名>	指明被创建的基表名，基表名最大长度 128 字节；
3. <列名>	指明基表中的列名，列名最大长度 128 字节；
4. <数据类型>	指明列的数据类型；
5. <列缺省值表达式>	如果之后的 INSERT 语句省略了插入的列值，那么此项为列值指定一个缺省值，可以通过 DEFAULT 指定一个值。DEFAULT 表达式串的长度不能超过2048 字节；
6. <列级完整性约束定义>中的参数：
	1) NULL	指明指定列可以包含空值，为缺省选项。
	2) NOT NULL	非空约束，指明指定列不可以包含空值；
	3) UNIQUE	唯一性约束，指明指定列作为唯一关键字；
	4) PRIMARY KEY	主键约束，指明指定列作为基表的主关键字；
	5) CLUSTER PRIMARY KEY	主键约束，指明指定列作为基表的聚集索引（也叫聚簇索引）主关键字；
	6) NOT CLUSTER PRIMARY KEY	主键约束，指明指定列作为基表的非聚集索 引主关键字；
	7) CLUSTER KEY	指定列为聚集索引键，但是是非唯一的；
	8) CLUSTER UNIQUE KEY	指定列为聚集索引键，并且是唯一的；
	9) USING INDEX TABLESPACE <表空间名>	指定索引存储的表空间；
	10) REFERENCES	指明指定列的引用约束。引用约束要求引用对应列类型必须基本一致。所谓基本，是因为 CHAR 与 VARCHAR，BINARY 与 VARBINARY，TINYINT、SMALLINT 与 INT 在此被认为是一致的。如果有 WITH INDEX选项，则为引用约束建立索引，否则不建立索引，通过其他内部机制保证约束正确性；
	11) CHECK 检查约束，指明指定列必须满足的条件；
	12) NOT VISIBLE 列不可见，当指定某列不可见时，使用 SELECT *进行查询时将不添加该列作为选择列。使用INSERT 无显式指定列列表进行插入时，值列表不能包含隐藏列的值。
7. <表级完整性约束>中的参数：
	1) UNIQUE	唯一性约束，指明指定列或列的组合作为唯一关键字；
	2) PRIMARY KEY	主键约束，指明指定列或列的组合作为基表的主关键字。指明 CLUSTER，表明是主关键字上聚集索引；指明 NOT CLUSTER，表明是主关键字上非聚集索引；
	3) USING INDEX TABLESPACE <表空间名>	指定索引存储的表空间；
	4) FOREIGN KEY	指明表级的引用约束，如果使用 WITH INDEX 选项，则为引用约束建立索引，否则不建立索引，通过其他内部机制保证约束正确性；
	5) CHECK 检查约束，指明基表中的每一行必须满足的条件；
	6) 与列级约束之间不应该存在冲突。
8. ON COMMIT<DELETE | PRESERVE>ROWS 用来指定临时表（TEMPORARY）中的数据是事务级或会话级的，缺省情况下是事务级。ON COMMIT DELETE ROWS：指定临时表是事务级的，每次事务提交或回滚之后，表中所有数据都被删除； ON COMMIT PRESERVE ROWS：指定临时表是会话级的，会话结束时才清空表；
9. <检验条件> 指明表中一列或多列能否接受的数据值或格式；
10. <查询表达式>和<子查询> 定义请查看数据查询章节；
11. STORAGE 项中：BRANCH、NOBRANCH 是堆表创建关键字，堆表为“扁平 B 树表”。这两个参数用来指定堆表并发分支 BRANCH 和非并发分支 NOBRANCH 的数目。<BRANCH 数>取值范围为 1~64，<NOBRANCH 数>取值范围为 1~64。
	1) NOBRANCH：指定创建的表为堆表，并发分支个数为 0，非并发分支个数为 1；
	2) BRANCH（<BRANCH 数>, <NOBRANCH 数>）：指定创建的表为堆表，并发分支个数为<BRANCH 数>，非并发个数为<NOBRANCH 数>；
	3) BRANCH	<BRANCH 数>：指定创建的表为堆表，并发分支个数为<BRANCH 数>，非并发分支个数为 0。
12. CLUSTERBTR 指定创建的表为非堆表，即普通 B 树表；
13. <虚拟列定义>指明定义虚拟列的表达式；
14. <高级日志子句>指定创建日志辅助表；
15. <add_log 子句> 开启表的物理逻辑日志记录功能，缺省为不开启。<add_log 子句>开启时和 INI 参数 RLOG_IGNORE_TABLE_SET=1 时功能一样。

语句功能

供 DBA 或具有 CREATE_TABLE 权限的用户定义基表。 

使用说明

1. <表名>指定了所要建立的基表名。在一个<模式>中，<基表名>、<视图名>均不相同。如果<模式名>缺省，则缺省为当前模式。若指定 TEMPORARY，则表示该表为一个临时 表，只在一个会话中有效，当一个会话结束，该临时表被自动清空。表名需要是合法的标识 符，且满足 SQL 语法要求。当表名以―##‖开头时，该表为全局临时表；
2. TEMPORARY 临时表不支持压缩 compress 功能；
3. GLOBAL 目前仅支持 GLOBAL 临时表，因此建临时表时是否指定 GLOBAL 效果是一样的；
4. 表名最大长度为 128 个字节；
5. 所建基表至少要包含一个<列名>指定的列，在一个基表中，各<列名>不得相同。一张基表中至多可以包含 2048 列；
6. 虚拟列上存在约束时，不支持使用 dmfldr 导入；
7. <DEFAULT 子句>指定列的缺省值，如：DEFAULT DATE '2015-12-26'；
8. 如果未指明 NOT NULL，也未指明<DEFAULT 子句>，则隐含为 DEFAULT NULL；
9. 自增列不能使用<DEFAULT 子句>；
10. <列缺省值表达式>的数据类型必须与本列的<数据类型>一致。缺省值表达式存在以下几点约束：
	1) 仅支持只读系统函数或指定 FOR CALCULATE 创建的存储函数；
	2) 不支持表列；
	3) 不支持包变量或语句参数；
	4) 不支持查询表达式；
	5) 不支持 LIKE；
	6) 不支持 CONTAINS 表达式。
11. 如果列定义为 NOT NULL，则当该列插入空值时会报错；
12. 约束被 DM 用来对数据实施业务规则，完成对数据完整性的控制。DM_SQL 中主要定义了以下几种类型的约束：非空约束、唯一性约束、主键约束、引用约束和检查约束。如果完整性约束只涉及当前正在定义的列，则既可定义成列级完整性约束，也可以定义成表级 完整性约束；如果完整性约束涉及到该基表的多个列，则只能在语句的后面定义成表级完整 性约束。定义与该表有关的列级或表级完整性约束时，可以用 CONSTRAINT<约束名>子句 对约束命名，系统中相同模式下的约束名不得重复。如果不指定约束名，系统将为此约 束自动命名。经定义后的完整性约束被存入系统的数据字典中，用户操作数据库时，由 DBMS 自动检查该操作是否违背这些完整性约束条件。
	1) 非空约束主要用于防止向一列添加空值，这就确保了该列在表中的每一行都存 在一个有意义的值。
		a) 该约束仅用于列级；
		b) 如果定义了列约束为 NOT NULL，则其<列缺省值表达式>不能将该列指定为 NULL；
		c) 空值即为未知的值，没有大小，不可比较。除关键字列外，其列可以取空值。不可取空值的列要用 NOT NULL 进行说明。
	2) 唯一性约束主要用于防止一个值或一组值在表中的特定列里出现不止一次，确保数据的完整性。
		a) 唯一性约束是通过唯一索引来实现的。创建了一个唯一索引也就创建了一 个唯一性约束；同样的，创建了一个唯一性约束，也就同时创建了一个唯 一索引，这种情况下唯一索引是由系统自动创建的；
		b) NULL 值是不参加唯一性约束的检查的。DM 系统允许插入多个 NULL 值。对于组合的唯一性约束，只要插入的数据中涉及到唯一性约束的列有一个 或多个 NULL 值，系统则认为这笔数据不违反唯一性约束。
	3) 主键约束确保了表中构成主键的一列或一组列的所有值是唯一的。主键主要用 于识别表中的特定行。主键约束是唯一性约束的特例。
		a) 可以指定多个列共同组成主键，最多支持 63 个列；
		b) 主键约束涉及的列必须为非空。通常情况下，DM 系统会自动在主键约束涉及的列上自动创建非空约束；
		c) 每个表中只能有一个主键；
		d) 主键约束是通过创建唯一索引来实现的。DM 系统允许用户自己定义创建主键时，通过 CLUSTER 或 NOT CLUSTER 关键字来指明创建索引的类型。CLUSTER 指明该主键是创建在聚集索引上的，NOT CLUSTER 指明该主键是创建在非聚集索引上的。缺省情况下，主键是创建在非聚集索引上的。堆表和列存储表不允许建立聚集主键；
		e) 表中不能同时包含聚集主键和多媒体数据类型列。
	4) 引用约束用于保证相关数据的完整性。引用约束中构成外键的一列或一组列，其值必须至少匹配其参照的表中的一行的一个主键或唯一键值。我们把这种数据的相关性称为引用关系，外键所在的表称为引用表，外键参照的表称为被引 用表。
		a) 引用约束指明的被引用表上必须已经建立了相关主键或唯一索引。也就是 说，必须保持引用约束所引用的数据必须是唯一的；
		b) 引用约束的检查规则：
			i.插入规则：外键的插入值必须匹配其被引用表的某个键值。
			ii.更新规则：外键的更新值必须匹配被引用表的某个键值。当修改被引用表中的主键值时，如果定义约束时的选项是 NO ACTION，且更新结果会违反引用约束则不允许更新；如果定义的是 SET NULL 则将引用表上的相关外键值置为 NULL；如果定义的是 CASCADE，那么引用表上的相关外键值将被修改为同样的值；如里定义的是 SETDEFAULT，则把引用列置为该列的缺省值。
			iii.删除规则：当从被引用表中删除一行数据时，如果定义约束时的选项是 NO ACTION，就不删除引用表上的相关外键值；如果定义的是 SET NULL 则将引用表上的相关外键值置为 NULL；如果定义的是CASCADE，那么引用表上的相关外键值将被删除；如里定义的是 SETDEFAULT，则把每个引用列置为―<列缺省值表达式>‖规则中所指定的缺省值。
		c) NULL 值不参加引用约束的检查。受引用约束的表，如果要插入的涉及到 引用约束的列值有一个或多个 NULL，则认为插入值不违反引用约束；
		d) MPP 环境下，引用列和被引用列都必需包含分布列，且分布情况完全相同；
		e) MPP 环境下，不支持创建 SET NULL 或 SET DEFAULT 约束检查规则的引用约束。
	5) 检查约束用于对将要插入的数据实施指定的检查，从而保证表中的数据都符合 指定的限制。<检验条件>必须是一个有意义的布尔表达式，其中的每个列名 必须是本表中定义的列，但列的类型不得为多媒体数据类型，并且不应包含子 查询、集函数。
13. 可以使用空间限制子句 DISKSPACE LIMIT 来限制表的最大存储空间，以 M 为单位，取值范围为 1 到 1048576，关键字 UNLIMITED 表示无限制。系统不支持查询建表情 况下指定空间限制；
14. 可以使用 STORAGE 子句指定表的存储信息：
	1) 初始簇数目：指建立表时分配的簇个数，必须为整数，最小值为 1，最大值为256，缺省为 1；
	2) 下次分配簇数目：指当表空间不够时，从数据文件中分配的簇个数，必须为整数，最小值为 1，最大值为 256，缺省为 1；
	3) 最小保留簇数目：当删除表中的记录后，如果表使用的簇数目小于这个值，就不再释放表的空间，必须为整数，最小值为 1，最大值为 256，缺省为 1；
	4) 表空间名：在指定的表空间上建表，表空间必须已存在，缺省为该用户的默认表空间；
	5) 填充比例：指定存储数据时每个数据页和索引页的充满程度，取值范围从 0 到100。默认值为 0，等价于 100，表示全满填充。插入数据时填充比例的值越低，可由新数据使用的空间就越多；更新数据时填充比例的值越大，更新导致出现的页分裂的几率越大。同样，创建索引时，填充比例的值越低，可由新索 引项使用的空间也就越多；
	6) BRANCH 和 NOBRANCH：指定 BRANCH 和 NOBRANCH 的个数；
	7) CLUSTERBTR：当 INI 参数 LIST_TABLE = 1 时，指定 CLUSTERBTR，则建立的表为普通 B 树表而非堆表；
	8) WITH COUNTER：在表上维护当前表内的行数；WITHOUT COUNTER：表上只维护一个非实时的大概的行数；对用户的影响：例如 SELECT COUNT(*) FROM test; 如果表 test 是 WITH COUNTER 属性，服务器直接取行数返回即可，可以快速响应；如果表 test 是 WITHOUT COUNTER 属性，服务器需要先扫描 B 树获取行数返回后才能响 应。不同的场景，根据需要灵活选择 COUNTER 属性。WITH COUNTER 属性 可以通过 ALTER TABLE 语句修改。若省略该选项，默认是 WITH COUNTER 属性。
	9) USING LONG ROW：支持超长记录存储。当 DM 行存储的记录长度超过页大 小一半时，先尝试将过长的变长字符串转换为行外 BLOB 存储，如果转换后仍 超长则报错。临时表、HUGE 表、外部表不支持 USING LONG ROW 选项。水 平分区子表的 USING LONG ROW 选项自动采用与主表保持一致的方式，两者 不同的情况下，直接忽略水平分区子表的 USING LONG ROW 选项。
15. <压缩子句> 只是语法支持，功能已经取消；
16. 记录的列长度总和不超过块长的一半，VARCHAR 数据类型的长度是指数据定义长度，实际是否越界还需要判断实际记录的长度，而 CHAR 类型的长度是实际数据长度。与此类似的还有 VARBINARY 和 BINARY 数据类型。因此，对于 16K 的块，可以定义 CREATETABLE TEST(C1 VARCHAR(8000),C2 INT)，但是不能定义 CREATE TABLE TEST(C1 CHAR(8000)，C2 INT)；
17. DM 具备自动断句功能；
18. 在对列指定存储加密属性时，对用户的数据在保存到物理介质之前使用指定的加密算法加密，防止数据泄露；
19. 加密算法可以是系统中已经存在的算法名称，可选的算法可以在 V$CIPHERS 中获 取，也可以使用第三方加密库中的算法，将已实现的第三 方加密动态库放到bin目录下的文件夹external_crypto_libs中，DM 支持加载多个第三方加密动态库，然后重启 DM 服务器即可引用其中的算法；需要注意的是：以“ NOPAD ” 结尾的加密算法需要用户保证原始数据长度是BLOCK_SIZE 的整数倍，