达梦数据库管理系统
DM SQL 语言使用手册
武汉华工达梦数据库有限公司
2005年09月
- I -
目 录
第 1 章 结构化查询语言 DM_SQL 简介 ..................................................................................1
1.1 DM_SQL 语言的特点 ......................................................................................................1
1.2 保留字与标识符...........................................................................................................2
1.3 字符集...........................................................................................................................2
1.4 DM_SQL 语言的功能及语句 ......................................................................................3
1.5 DM_SQL 所支持的数据类型 ......................................................................................4
1.5.1 常规数据类型 ....................................................................................................4
1.5.2 日期时间数据类型 ............................................................................................7
1.5.3 多媒体数据类型 ..............................................................................................10
1.6 DM_SQL 语言支持的表达式 .................................................................................... 11
1.6.1 数值表达式 ......................................................................................................12
1.6.2 字符串表达式 ..................................................................................................14
1.6.3 时间值表达式 ..................................................................................................15
1.6.4 时间间隔值表达式 ..........................................................................................17
1.6.5 运算符的优先级 ..............................................................................................18
1.7 DM_SQL 语言支持的数据库模式 ............................................................................19
第 2 章 本手册中的实例说明..................................................................................................20
2.1 实例说明.....................................................................................................................20
2.2 实例中的数据.............................................................................................................21
2.3 参考脚本.....................................................................................................................22
第 3 章 数据定义语句..............................................................................................................26
3.1 数据库定义语句.........................................................................................................26
3.2 数据库修改语句.........................................................................................................27
3.3 数据库删除语句.........................................................................................................28
3.4 设置当前数据库语句.................................................................................................28
3.5 用户定义语句.............................................................................................................29
3.6 用户删除语句.............................................................................................................29
3.7 登录定义语句.............................................................................................................30
3.8 登录修改语句.............................................................................................................31
3.9 登录删除语句.............................................................................................................33
3.10 模式定义语句...........................................................................................................33
3.11 模式删除语句 ...........................................................................................................35
3.12 基表定义语句...........................................................................................................35
3.13 基表修改语句...........................................................................................................40
3.14 基表删除语句 ........................................................................................................45
3.15 全表删除语句...........................................................................................................46
3.16 索引定义语句...........................................................................................................47
3.17 索引删除语句...........................................................................................................48
- II -
3.18 序列定义语句...........................................................................................................48
3.19 序列删除语句...........................................................................................................50
3.20 全文索引定义语句 ...................................................................................................50
3.21 全文索引修改语句 ...................................................................................................51
3.22 全文索引删除语句 ...................................................................................................52
第 4 章 数据查询语句和全文检索语句 ..................................................................................53
4.1 单表查询.....................................................................................................................55
4.1.1 简单查询..........................................................................................................55
4.1.2 带条件查询 ......................................................................................................56
4.1.3 集函数..............................................................................................................59
4.1.4 情况表达式 ......................................................................................................60
4.2 连接查询.....................................................................................................................63
4.3 子查询.........................................................................................................................68
4.3.1 标量子查询 ......................................................................................................68
4.3.2 表子查询..........................................................................................................69
4.3.3 派生表子查询 ..................................................................................................72
4.3.4 定量比较..........................................................................................................73
4.3.5 带 EXISTS 谓词的子查询...............................................................................74
4.4 查询的交.....................................................................................................................75
4.5 GROUP BY 和 HAVING 子句...................................................................................76
4.5.1 GROUP BY 子句的使用 .................................................................................76
4.5.2 HAVING 子句的使用......................................................................................77
4.6 ORDER BY 子句........................................................................................................78
4.7 选取前几条数据.........................................................................................................79
4.8 全文检索.....................................................................................................................80
第 5 章 数据的插入、删除和修改 ..........................................................................................85
5.1 数据插入语句.............................................................................................................85
5.2 数据修改语句.............................................................................................................89
5.3 数据删除语句.............................................................................................................90
5.4 伪列的使用.................................................................................................................91
5.4.1 ROWID ............................................................................................................91
5.4.2 UID 和 USER...................................................................................................91
5.5 DM 自增列的使用......................................................................................................91
5.5.1 DM 自增列定义...............................................................................................91
5.5.2 SET IDENTITY_INSERT 属性......................................................................93
第 6 章 视图..............................................................................................................................95
6.1 视图的定义.................................................................................................................95
6.2 视图的删除.................................................................................................................98
6.3 视图的查询.................................................................................................................98
6.4 视图数据的更新.........................................................................................................99
- III -
6.5 视图的作用.............................................................................................................101
第 7 章 嵌入式 SQL ...............................................................................................................102
7.1 SQL 前缀和终结符 ..................................................................................................102
7.2 宿主变量...................................................................................................................103
7.2.1 输入和输出变量 ............................................................................................103
7.2.2 指示符变量 ....................................................................................................104
7.3 服务器登录与退出...................................................................................................104
7.3.1 登录服务器 ....................................................................................................104
7.3.2 退出服务器 ....................................................................................................105
7.4 游标的定义与操纵...................................................................................................106
7.4.1 定义游标语句 ................................................................................................106
7.4.2 打开游标语句 ................................................................................................107
7.4.3 拨动游标语句 ................................................................................................108
7.4.4 关闭游标语句 ................................................................................................109
7.4.5 关于可更新游标 ............................................................................................ 110
7.4.6 游标定位删除语句 ........................................................................................ 110
7.4.7 游标定位修改语句 ........................................................................................ 111
7.5 单元组查询语句.......................................................................................................112
7.6 动态 SQL ..................................................................................................................114
7.6.1 EXECUTE IMMEDIATE 立即执行语句......................................................114
7.6.2 PREPARE 准备语句......................................................................................115
7.6.3 EXCUTE 执行语句 .......................................................................................115
7.7 异常处理................................................................................................................... 116
第 8 章 函数............................................................................................................................ 118
8.1 数值函数...................................................................................................................122
8.2 字符串函数...............................................................................................................129
8.3 日期时间函数...........................................................................................................140
8.4 空值判断函数...........................................................................................................150
8.5 类型转换函数...........................................................................................................151
8.6 杂类函数...................................................................................................................153
8.7 系统函数...................................................................................................................153
第 9 章 数据库元信息............................................................................................................157
9.1 数据字典查询语句...................................................................................................157
9.2 数据字典表结构.......................................................................................................158
9.3 信息模式...................................................................................................................158
第 10 章 一致性和并发性 ......................................................................................................160
10.1 DM 事务相关语句..................................................................................................160
10.1.1 事务的开始..................................................................................................160
10.1.2 事务的结束..................................................................................................160
10.1.3 保存点相关语句 ..........................................................................................161
- IV -
10.1.4 设置事务隔离级及读写特性 ......................................................................163
10.2 DM 手动上锁语句..................................................................................................163
第 11 章 存储模块.................................................................................................................166
11.1 存储模块的定义 .....................................................................................................166
11.2 存储模块的删除 .....................................................................................................171
11.3 存储模块的控制语句 .............................................................................................172
11.3.1 语句块 ..........................................................................................................173
11.3.2 赋值语句 ......................................................................................................174
11.3.3 条件语句 ......................................................................................................175
11.3.4 循环语句 ......................................................................................................176
11.3.5 EXIT 语句 ....................................................................................................178
11.3.6 调用语句 ......................................................................................................178
11.3.7 RETURN 语句 .............................................................................................180
11.3.8 NULL 语句 ..................................................................................................180
11.3.9 GOTO 语句 ..................................................................................................180
11.3.10 RAISE 语句................................................................................................181
11.3.11 打印语句 ....................................................................................................182
11.4 存储模块的异常处理 .............................................................................................182
11.4.1 异常变量的说明 ..........................................................................................182
11.4.2 异常的抛出 ..................................................................................................182
11.4.3 异常处理器 ..................................................................................................183
11.4.4 异常处理用法举例 ......................................................................................183
11.5 存储模块的 SQL 语句............................................................................................185
11.5.1 游标 ..............................................................................................................185
11.5.2 动态 SQL......................................................................................................187
11.5.3 游标变量 ......................................................................................................187
11.5.4 返回查询结果集 ..........................................................................................188
11.5.5 SQL 语句应用举例......................................................................................188
第 12 章 触发器 ......................................................................................................................192
12.1 触发器的定义.........................................................................................................192
12.1.1 触发器类型..................................................................................................194
12.1.2 触发器激发顺序 ..........................................................................................197
12.1.3 新、旧行值的引用 ......................................................................................198
12.1.4 触发器谓词..................................................................................................200
12.1.5 变异表..........................................................................................................201
12.1.6 设计触发器的原则 ......................................................................................203
12.2 触发器的删除.........................................................................................................203
12.3 禁止和允许触发器.................................................................................................204
12.4 触发器应用举例.....................................................................................................206
12.4.1 使用触发器实现审计功能 ..........................................................................206
