DM7_SQL语言使用手册

DM7_SQL语言使用手册
结构化查询语言 SQL(Structured Query Language)是在 1974 年提出的一种关系数据库语言。由于 SQL 语言接近英语的语句结构，方便简洁、使用灵活、功能强大，倍受用户及计算机工业界的欢迎，被众多计算机公司和数据库厂商所采用，经各公司的不断修改、扩充和完善，SQL 语言最终发展成为关系数据库的标准语言。SQL 的第一个标准是 1986 年 10 月由美国国家标准化组织(ANSI)公布的 ANSIX3.135-1986 数据库语言 SQL，简称 SQL-86，1987 年国际标准化组织(ISO)也通过了这一标准。以后通过对 SQL-86 的不断修改和完善，于 1989 年第二次公布了 SQL 标准ISO/IEC 9075-1989(E)，即 SQL-89。1992 年又公布了 SQL 标准 ISO/IEC 9075：1992，即 SQL-92。最新的 SQL 标准是 SQL-3(也称 SQL-99)，1999 年作为 ISO/IEC9075：1999《信息技术——数据库语言 SQL》发布。我国也相继公布了数据库语言 SQL 的国家标准。SQL 成为国际标准以后，其影响远远超出了数据库领域。例如在 CAD、软件工程、人工智能、分布式等领域，人们不仅把 SQL 作为检索数据的语言规范，而且也把 SQL 作为检索图形、图象、声音、文字等信息类型的语言规范。目前，世界上大型的著名数据库管理系统均支持 SQL 语言，如 Oracle、Sybase、SQL Server、DB2 等。在未来相当长的时间里，SQL 仍将是数据库领域以至信息领域中数据处理的主流语言之一。由于不同的 DBMS 产品，大都按自己产品的特点对 SQL 语言进行了扩充，很难完全符合 SQL 标准。目前在 DBMS 市场上已将 SQL 的符合率作为衡量产品质量的重要指标，并研制成专门的测试软件，如 NIST。目前，DM SQL-92 入门级符合率达到 100%，过渡级符合率达到 95%，并且部分支持更新的 SQL-99 标准。同时 DM 还兼容 Oracle 11g 和 SQLServer 2008 的部分语言特性。本章主要介绍 DM 系统所支持的 SQL 语言——DM_SQL 语言。

DECIMAL类型语法：DECIMAL[(精度 [, 标度])]功能：与 NUMERIC 相似。DEC 类型语法：DEC[(精度[, 标度])]功能：与 DECIMAL 相同。NUMBER 类型语法：NUMBER[(精度[, 标度])]功能：与 NUMERIC 相同。INTEGER 类型语法：INTEGER功能：用于存储有符号整数，精度为 10，标度为 0。取值范围为：-2147483648(-231)～ 2147483647(231-1)。INT 类型语法：INT功能：与 INTEGER 相同。BIGINT 类型语法：BIGINT功 能 ： 用 于 存 储 有 符 号 整 数 ， 精 度 为 19 ，标度为 0 。 取 值 范 围 为 ：-9223372036854775808(-263)～9223372036854775807(263-1)。TINYINT 类型语法：TINYINT功能：用于存储有符号整数，精度为 3，标度为 0。取值范围为：-128 ～ 127。BYTE 类型语法：BYTE功能：与 TINYINT 相似，精度为 3，标度为 0。SMALLINT 类型语法：SMALLINT功能：用于存储有符号整数，精度为 5，标度为 0。取值范围为：-32768(-215) ～32767(215-1)。4第 1 章 结构化查询语言 DM_SQL 简介BINARY 类型语法：BINARY[(长度)]功能：BINARY 数据类型用来存储定长二进制数据。缺省长度为 1 个字节。最大长度由数据库页面大小决定，具体最大存储长度算法与 CHAR 类型的相同，其在表达式计算中的长度上限也与 CHAR 类型相同，为 32767。BINARY 常量以 0x 开始，后面跟着数据的十六进制表示。例如 0x2A3B4058。VARBINARY 类型语法：VARBINARY[(长度)]功能：VARBINARY 数据类型用来存储变长二进制数据，用法类似 BINARY 数据类型，可以指定一个不超过 8188 的正整数作为数据长度。缺省长度为 8188 个字节。VARBINARY数据类型的实际最大存储长度由数据库页面大小决定，具体最大长度算法与 VARCHAR 类型的相同，其在表达式计算中的长度上限也与 VARCHAR 类型相同，为 32767。2. 近似数值数据类型FLOAT 类型语法：FLOAT[(精度)]功能：FLOAT 是带二进制精度的浮点数。该类型直接使用标准 C 语言中 DOUBLE。精度值设置无实际意义，精度设置用于保证数据移植的兼容性，实际精度在达梦内部是固定的。精度处于范围（1~126）时忽略精度，超过此范围直接报错。FLOAT 取值范围-1.7*10308 ～ 1.7*10308。DOUBLE 类型语法：DOUBLE[(精度)]功能：DOUBLE 是带二进制精度的浮点数。DOUBLE 类型的设置是为了移植的兼容性。该类型直接使用标准 C 语言中 DOUBLE。精度与取值范围用法与 FLOAT 完全一样。REAL 类型语法：REAL功能：REAL 是带二进制精度的浮点数，但它不能由用户指定使用的精度，系统指定其二进制精度为 24，十进制精度为 7。取值范围-3.4*1038 ～ 3.4*1038。DOUBLE PRECISION 类型语法：DOUBLE PRECISION[(精度)]功能：该类型指明双精度浮点数。DOUBLE PRECISION 类型的设置是为了移植的兼容性。 该类型直接使用标准 C 语言中 DOUBLE。精度与取值范围用法与 FLOAT 完全一样。目录
第 1 章 结构化查询语言 DM_SQL 简介 ..................................................................... 1
1.1 DM_SQL 语言的特点 ...................................................................................................... 1
1.2 保留字与标识符 ............................................................................................................. 2
1.3 DM_SQL 语言的功能及语句 .......................................................................................... 2
1.4 DM_SQL 所支持的数据类型 .......................................................................................... 3
1.4.1 常规数据类型 ..................................................................................................... 3
1.4.2 位串数据类型 ..................................................................................................... 5
1.4.3 日期时间数据类型 ............................................................................................. 6
1.4.4 多媒体数据类型 ............................................................................................... 10
1.5 DM_SQL 语言支持的表达式 ........................................................................................ 10
1.5.1 数值表达式 ....................................................................................................... 10
1.5.2 字符串表达式 ................................................................................................... 13
1.5.3 时间值表达式 ................................................................................................... 13
1.5.4 时间间隔值表达式 ........................................................................................... 15
1.5.5 运算符的优先级 ............................................................................................... 16
1.6 DM_SQL 语言支持的数据库模式 ................................................................................ 16
第 2 章 手册中的示例说明 ....................................................................................... 18
2.1 示例库说明 ................................................................................................................... 18
2.2 参考脚本 ....................................................................................................................... 26
2.2.1 创建示例库 ....................................................................................................... 26
2.2.2 创建模式及表 ................................................................................................... 26
2.2.3 插入数据 ........................................................................................................... 33
第 3 章 数据定义语句 ............................................................................................... 50
3.1 数据库修改语句 ........................................................................................................... 50
3.2 管理用户 ....................................................................................................................... 54
3.2.1 用户定义语句 ................................................................................................... 54
3.2.2 修改用户语句 ................................................................................................... 59
3.2.3 用户删除语句 ................................................................................................... 62
3.3 管理模式 ....................................................................................................................... 63
3.3.1 模式定义语句 ................................................................................................... 63
3.3.2 设置当前模式语句 ........................................................................................... 65
3.3.3 模式删除语句 ................................................................................................... 65
3.4 管理表空间 ................................................................................................................... 66
3.4.1 表空间定义语句 ............................................................................................... 66
3.4.2 修改表空间语句 ............................................................................................... 67
3.4.3 表空间删除语句 ............................................................................................... 69
3.4.4 表空间失效文件检查 ....................................................................................... 70
目录
II
3.4.5 表空间失效文件恢复准备 ............................................................................... 70
3.4.6 表空间失效文件恢复 ....................................................................................... 70
3.5 管理 HTS 表空间 .......................................................................................................... 71
3.5.1 创建 HTS 表空间 .............................................................................................. 71
3.5.2 删除 HTS 表空间 .............................................................................................. 71
3.6 管理表 ........................................................................................................................... 72
3.6.1 基表定义语句 ................................................................................................... 72
3.6.2 基表修改语句 ................................................................................................. 106
3.6.3 基表删除语句 .............................................................................................. 121
3.6.4 基表数据删除语句 ......................................................................................... 122
3.6.5 事务型 HUGE 表数据重整 ............................................................................. 122
3.7 管理索引 ..................................................................................................................... 123
3.7.1 索引定义语句 ................................................................................................. 123
3.7.2 索引修改语句 ................................................................................................. 128
3.7.3 索引删除语句 ................................................................................................. 130
3.8 管理位图连接索引...................................................................................................... 131
3.8.1 位图连接索引定义语句 ................................................................................. 131
3.8.2 位图连接索引删除语句 ................................................................................. 133
3.9 管理全文索引 ............................................................................................................. 133
3.9.1 全文索引定义语句 ......................................................................................... 133
3.9.2 全文索引修改语句 ......................................................................................... 134
3.9.3 全文索引删除语句 ......................................................................................... 135
3.10 管理空间索引 ........................................................................................................... 136
3.11 管理数组索引 ........................................................................................................... 136
3.11.1 数组索引定义语句 ...................................................................................... 137
3.11.2 数组索引修改语句 ...................................................................................... 137
3.11.3 数组索引使用 .............................................................................................. 137
3.11.4 数组索引删除语句 ...................................................................................... 139
3.12 管理序列 ................................................................................................................... 139
3.12.1 序列定义语句 .............................................................................................. 139
3.12.2 序列修改语句 .............................................................................................. 142
3.12.3 序列删除语句 .............................................................................................. 144
3.13 管理 SQL 域 .............................................................................................................. 144
3.13.1 创建 DOMAIN ............................................................................................... 144
3.13.2 使用 DOMAIN ............................................................................................... 145
3.13.3 删除 DOMAIN ............................................................................................... 146
3.14 管理上下文 ............................................................................................................... 146
3.14.1 创建上下文 ................................................................................................... 146
3.14.2 删除上下文 ................................................................................................... 148
3.15 管理目录 ................................................................................................................... 149
3.15.1 创建目录 ....................................................................................................... 149
3.15.2 删除目录 ....................................................................................................... 149
3.16 设置当前会话 ........................................................................................................... 150
3.16.1 时区信息 ....................................................................................................... 150
目录
III
3.16.2 日期串语言 ................................................................................................... 150
3.17 注释语句 ................................................................................................................... 151
3.18 设置 INI 参数 .......................................................................................................... 151
3.18.1 设置参数值 ................................................................................................... 152
3.18.2 设置仅对当前会话起作用 ........................................................................... 153
3.19 修改系统语句 ........................................................................................................... 153
3.20 设置列、索引生成统计信息 ................................................................................... 154
第 4 章 数据查询语句 ............................................................................................. 156
4.1 单表查询 ..................................................................................................................... 168
4.1.1 简单查询 ......................................................................................................... 168
4.1.2 带条件查询 ..................................................................................................... 170
4.1.3 集函数 ............................................................................................................. 173
4.1.4 分析函数 ......................................................................................................... 177
4.1.5 情况表达式 ..................................................................................................... 194
4.2 连接查询 ..................................................................................................................... 198
4.2.1 交叉连接 ......................................................................................................... 198
4.2.2 自然连接(NATURAL JOIN) ...................................................................... 199
4.2.3 JOIN … USING ............................................................................................ 199
4.2.4 JOIN…ON ....................................................................................................... 200
4.2.5 自连接 ............................................................................................................. 200
4.2.6 内连接(INNER JOIN) ................................................................................ 201
4.2.7 外连接(OUTER JOIN) ................................................................................ 202
4.3 子查询 ......................................................................................................................... 206
4.3.1 标量子查询 ..................................................................................................... 206
4.3.2 表子查询 ......................................................................................................... 207
4.3.3 派生表子查询 ................................................................................................. 210
4.3.4 定量比较 ......................................................................................................... 210
4.3.5 带 EXISTS 谓词的子查询 ............................................................................ 211
4.3.6 多列表子查询 ................................................................................................. 212
4.4 WITH 子句 ................................................................................................................. 213
4.4.1 WITH FUNCTION 子句 ................................................................................ 213
4.4.2 公用表表达式子句 ......................................................................................... 214
4.5 合并查询结果 .............................................................................................................. 216
4.6 GROUP BY 和 HAVING 子句 ..................................................................................... 218
4.6.1 GROUP BY 子句的使用 ................................................................................ 218
4.6.2 ROLLUP 的使用 ............................................................................................. 219
4.6.3 CUBE 的使用 .................................................................................................. 220
4.6.4 GROUPING 的使用 ........................................................................................ 222
4.6.5 GROUPING SETS 的使用 ............................................................................ 223
4.6.6 GROUPING_ID 的使用 ................................................................................. 224
4.6.7 GROUP_ID 的使用 ........................................................................................ 225
4.6.8 HAVING 子句的使用 ..................................................................................... 226
4.7 ORDER BY 子句 ......................................................................................................... 226
目录
IV
4.8 FOR UPDATE 子句 .................................................................................................... 228
4.9 TOP 子句 ..................................................................................................................... 229
4.10 LIMIT 限定条件 ...................................................................................................... 230
4.10.1 LIMIT 子句 ................................................................................................. 230
4.10.2 ROW_LIMIT 子句 ....................................................................................... 231
4.11 全文检索 ................................................................................................................... 232
4.12 层次查询子句 ........................................................................................................... 234
4.12.1 层次查询子句 .............................................................................................. 234
4.12.2 层次查询相关伪列 ...................................................................................... 235
4.12.3 层次查询相关操作符 .................................................................................. 235
4.12.4 层次查询相关函数 ...................................................................................... 235
4.12.5 层次查询层内排序 ...................................................................................... 236
4.12.6 层次查询的限制 .......................................................................................... 236
4.13 并行查询 ................................................................................................................... 240
4.14 ROWNUM .................................................................................................................... 241
4.15 数组查询 ................................................................................................................... 242
4.16 查看执行计划与执行跟踪统计 ............................................................................... 243
4.16.1 EXPLAIN ..................................................................................................... 243
4.16.2 EXPLAIN FOR ............................................................................................ 243
4.17 SAMPLE 子句 ........................................................................................................... 244
第 5 章 数据的插入、删除和修改 ......................................................................... 246
5.1 数据插入语句 ............................................................................................................. 246
5.2 数据修改语句 ............................................................................................................. 251
5.3 数据删除语句 ............................................................................................................. 253
5.4 MERGE INTO 语句 .................................................................................................... 255
5.5 伪列的使用 ................................................................................................................. 258
5.5.1 ROWID ............................................................................................................ 258
5.5.2 UID 和 USER.................................................................................................. 258
5.5.3 TRXID ............................................................................................................ 258
5.5.4 SESSID .......................................................................................................... 258
5.6 DM 自增列的使用 ....................................................................................................... 258
5.6.1 DM 自增列定义............................................................................................... 258
5.6.2 SET IDENTITY_INSERT 属性 ................................................................. 259
第 6 章 视图 ............................................................................................................. 262
6.1 视图的作用 ............................................................................................................... 262
6.2 视图的定义 ................................................................................................................. 263
6.3 视图的删除 ................................................................................................................. 266
6.4 视图的查询 ................................................................................................................. 266
6.5 视图的编译 ................................................................................................................. 267
6.6 视图数据的更新 ......................................................................................................... 268
第 7 章 物化视图 ..................................................................................................... 270
目录
V
7.1 物化视图的定义 ..................................................................................................... 270
7.2 物化视图的修改 ..................................................................................................... 273
7.3 物化视图的删除 ..................................................................................................... 274
7.4 物化视图的更新 ..................................................................................................... 275
7.5 物化视图允许的操作 ............................................................................................ 275
7.6 物化视图日志的定义 ............................................................................................ 275
7.7 物化视图日志的删除 ............................................................................................ 276
7.8 物化视图的限制 ..................................................................................................... 277
7.8.1 物化视图的一般限制 ..................................................................................... 277
7.8.2 物化视图的分类 ............................................................................................. 277
7.8.3 快速刷新通用约束 ......................................................................................... 277
7.8.4 物化视图信息查看 ......................................................................................... 278
第 8 章 函数 ............................................................................................................. 280
8.1 数值函数 ..................................................................................................................... 286
8.2 字符串函数 ................................................................................................................. 298
8.3 日期时间函数 ............................................................................................................. 320
8.4 空值判断函数 ............................................................................................................. 338
8.5 类型转换函数 ............................................................................................................. 340
8.6 杂类函数 ..................................................................................................................... 342
第 9 章 一致性和并发性 ......................................................................................... 346
9.1 DM 事务相关语句 ....................................................................................................... 346
9.1.1 事务的开始 ..................................................................................................... 346
9.1.2 事务的结束 ..................................................................................................... 346
9.1.3 保存点相关语句 ............................................................................................. 347
9.1.4 设置事务隔离级及读写特性 ......................................................................... 348
9.2 DM 手动上锁语句 ....................................................................................................... 349
第 10 章 外部函数 ................................................................................................... 352
10.1 C 外部函数 ............................................................................................................... 352
10.1.1 生成动态库 .................................................................................................. 352
10.1.2 C 外部函数创建 ........................................................................................... 354
10.1.3 举例说明 ...................................................................................................... 355
10.2 JAVA 外部函数 ........................................................................................................ 357
10.2.1 生成 jar 包 ................................................................................................. 358
10.2.2 JAVA 外部函数创建 ................................................................................... 358
10.2.3 举例说明 ...................................................................................................... 359
第 11 章 包 .............................................................................................................. 361
11.1 创建包 ....................................................................................................................... 361
11.1.1 创建包规范 .................................................................................................. 361
11.1.2 创建包主体 .................................................................................................. 362
11.2 重编译包 ................................................................................................................... 364
目录
VI
11.3 删除包 ....................................................................................................................... 364
11.3.1 删除包规范 .................................................................................................. 364
11.3.2 删除包主体 .................................................................................................. 365
11.4 应用实例 ................................................................................................................... 365
第 12 章 类类型 ....................................................................................................... 369
12.1 普通 CLASS 类型 ...................................................................................................... 369
12.1.1 声明类 .......................................................................................................... 370
12.1.2 实现类 .......................................................................................................... 371
12.1.3 重编译类 ...................................................................................................... 373
12.1.4 删除类 .......................................................................................................... 374
12.1.5 类的使用 ...................................................................................................... 374
12.2 JAVA CLASS 类型 .................................................................................................. 376
12.2.1 定义 JAVA 类 ............................................................................................... 377
12.2.2 重编译 JAVA 类 ........................................................................................... 379
12.2.3 删除 JAVA 类 ............................................................................................... 379
12.2.4 类的使用 ...................................................................................................... 379
第 13 章自定义类型 ................................................................................................. 381
13.1 创建类型 ................................................................................................................... 381
13.2 创建类型体 ................................................................................................................ 382
13.3 重编译类型 ................................................................................................................ 383
13.4 删除类型 .................................................................................................................... 384
13.4.1 删除类型 ....................................................................................................... 384
13.4.2 删除类型体 ................................................................................................... 384
13.5 自定义类型的使用 .................................................................................................... 384
13.5.1 使用规则 ...................................................................................................... 384
13.5.2 应用实例 ...................................................................................................... 385
第 14 章 触发器 ....................................................................................................... 386
14.1 触发器的定义 ........................................................................................................... 386
14.1.1 表触发器 ....................................................................................................... 386
14.1.2 事件触发器 ................................................................................................... 396
14.1.3 时间触发器 ................................................................................................... 406
14.2 触发器替换 ............................................................................................................... 407
14.3 设计触发器的原则 .................................................................................................... 407
14.4 触发器的删除 ........................................................................................................... 408
14.5 禁止和允许触发器 ................................................................................................... 408
14.6 触发器的重编 ........................................................................................................... 409
14.7 触发器应用举例 ....................................................................................................... 409
14.7.1 使用触发器实现审计功能 .......................................................................... 410
14.7.2 使用触发器维护数据完整性 ...................................................................... 410
14.7.3 使用触发器保障数据安全性 ...................................................................... 411
14.7.4 使用触发器生成字段默认值 ...................................................................... 412
目录
VII
第 15 章 同义词 ....................................................................................................... 414
15.1 创建同义词 ............................................................................................................... 414
15.2 删除同义词 ............................................................................................................... 415
第 16 章 外部链接 ................................................................................................... 417
16.1 创建外部链接 ........................................................................................................... 417
16.2 删除外部链接 ........................................................................................................... 420
16.3 使用外部链接 ........................................................................................................... 421
第 17 章 闪回查询 ................................................................................................... 422
17.1 闪回查询子句 ........................................................................................................... 422
17.2 闪回版本查询 ........................................................................................................... 424
17.3 闪回事务查询 ........................................................................................................... 426
第 18 章 JSON ......................................................................................................... 427
18.1 数据类型 .................................................................................................................... 427
18.1.1 string ...................................................................................................... 427
18.1.2 number ...................................................................................................... 428
18.1.3 true、false ........................................................................................... 428
18.1.4 null ........................................................................................................... 430
18.1.5 object ...................................................................................................... 431
18.1.6 array ........................................................................................................ 431
18.2 函数 ........................................................................................................................... 431
18.2.1 json_value ............................................................................................ 431
18.2.2 json_query ............................................................................................ 432
18.2.3 函数参数详解 ............................................................................................ 433
18.3 使用 IS JSON/IS NOT JSON 条件 .................................................................... 435
18.4 视图 ............................................................................................................................ 438
18.5 一个简单的例子 ........................................................................................................ 439
第 19 章 高级日志 ................................................................................................... 442
19.1 简介 ........................................................................................................................... 442
19.2 使用须知 ................................................................................................................... 442
19.3 语法 ........................................................................................................................... 442
19.3.1 管理日志辅助表 .......................................................................................... 442
19.3.2 使用日志辅助表的规则与约束................................................................... 443
19.3.3 日志辅助表结构 .......................................................................................... 444
19.3.4 系统过程 ...................................................................................................... 444
19.4 使用高级日志同步数据的原则 ............................................................................... 445
19.5 应用实例 ................................................................................................................... 447
19.5.1 创建不带主键的源表 .................................................................................. 447
19.5.2 创建带主键的源表 ...................................................................................... 450
附录 1 关键字和保留字 .......................................................................................... 453
目录
VIII
附录 2 SQL 语法描述说明...................................................................................... 457
附录 3 系统存储过程和函数 .................................................................................. 460
1) INI 参数管理 ............................................................................................................... 460
2) 系统信息管理 ................................................................................................................ 465
3) 备份恢复管理 ................................................................................................................ 480
4) 定时器管理 .................................................................................................................... 509
5) 数据复制管理 ................................................................................................................ 513
6) 模式对象相关信息管理 ................................................................................................ 522
7) 数据守护管理 ................................................................................................................ 532
8) MPP 管理 ....................................................................................................................... 537
9) 日志与检查点管理 ........................................................................................................ 540
10) 数据库重演 ................................................................................................................. 541
11) 统计信息 ..................................................................................................................... 542
12) 资源监测 ..................................................................................................................... 548
13) 类型别名 ..................................................................................................................... 555
14) 杂类函数 ..................................................................................................................... 557
15) 编目函数调用的系统函数 .......................................................................................... 568
16) BFILE ......................................................................................................................... 577
17) HUGE 表备份还原 ...................................................................................................... 578
18) 定制会话级 INI 参数 ................................................................................................ 579
19) 为 SQL 指定 HINT ...................................................................................................... 581
20) 时区设置 ..................................................................................................................... 583
附录 4 DM 技术支持 ................................................................................................ 586