基本信息
源码名称：GBase 8a MPP Cluster SQL参考手册
源码大小：4.08M
文件格式：.pdf
开发语言：SQL
更新时间：2022-11-07
友情提示：（无需注册或充值，赞助后即可获取资源下载链接）
嘿，亲！知识可是无价之宝呢，但咱这精心整理的资料也耗费了不少心血呀。小小地破费一下，绝对物超所值哦！如有下载和支付问题，请联系我们QQ(微信同号)：813200300
本次赞助数额为： 2 元×
微信扫码支付：2 元
×
请留下您的邮箱，我们将在2小时内将文件发到您的邮箱
源码介绍
GBase 8a MPP Cluster SQL参考手册
GBase 8a MPP Cluster SQL 参考手册介绍 GBase 8a MPP Cluster 中可以使用的 SQL 语句，包括数据类型、操作符和函数、DDL 和 DML 语句，以及存储过程和自定义函数，手册中还提供了示例以供读者参考
目 录
前言 ............................................................. 1
手册简介 ..................................................... 1
公约 ......................................................... 2
1 数据类型 ..................................................... 3
1.1 数值类型 ............................................... 3
1.1.1 TINYINT .......................................... 4
1.1.2 SMALLINT ......................................... 4
1.1.3 INT .............................................. 4
1.1.4 BIGINT ........................................... 4
1.1.5 FLOAT ............................................ 5
1.1.6 DOUBLE ........................................... 7
1.1.7 DECIMAL .......................................... 8
1.1.8 NUMERIC ......................................... 11
1.2 字符类型 .............................................. 11
1.2.1 CHAR ............................................ 11
1.2.2 VARCHAR ......................................... 12
1.2.3 TEXT ............................................ 13
1.3 二进制数据类型 ........................................ 13
1.4 日期和时间类型 ........................................ 13
1.4.1 DATE ............................................ 14
1.4.2 TIME ............................................ 15
1.4.3 DATETIME ........................................ 16
1.4.4 TIMESTAMP........................................ 18
2 SQL语言基础 ................................................. 21
2.1 数值 .................................................. 21
2.1.1 字符串 .......................................... 21
2.1.2 数字 ............................................ 24
2.1.3 十六进制值....................................... 24
2.1.4 布尔值 .......................................... 26
2.1.5 NULL值 .......................................... 26
2.2 数据库、表、列和别名 .................................. 27
2.3 标识符限定词 .......................................... 28
2.4 注释语法 .............................................. 29
2.5 用户变量 .............................................. 30
GBase 8a MPP Cluster SQL参考手册
II 南大通用数据技术股份有限公司
3 操作符和函数 ................................................ 32
3.1 操作符 ................................................ 32
3.1.1 操作符优先级 .................................... 32
3.1.2 圆括号 .......................................... 33
3.1.3 比较函数和操作符 ................................ 34
3.1.4 逻辑操作符 ...................................... 49
3.1.5 转换操作符和函数 ................................ 53
3.1.6 日期算术运算 .................................... 59
3.2 控制流函数 ............................................ 60
3.2.1 CASE ............................................ 60
3.2.2 DECODE .......................................... 61
3.2.3 IF(expr1,expr2,expr3) ........................... 62
3.2.4 IFNULL(expr1,expr2) ............................. 63
3.2.5 NULLIF(expr1,expr2) ............................. 64
3.3 字符串函数 ............................................ 65
3.3.1 ASCII(str) ...................................... 65
3.3.2 BIN(N) .......................................... 66
3.3.3 BIT_LENGTH(str) ................................. 66
3.3.4 CHAR(N,...) ..................................... 67
3.3.5 CHAR_LENGTH(str) ................................ 68
3.3.6 CHARACTER_LENGTH(str) ........................... 69
3.3.7 CONCAT(str1,str2,...) ........................... 69
3.3.8 CONCAT_WS(separator,str1,str2,...) ............... 70
3.3.9 CONV(N,from_base,to_base)........................ 71
3.3.10 ELT(N,str1,str2,str3,...)........................ 72
3.3.11 EXPORT_SET ...................................... 73
3.3.12 FIELD(str,str1,str2,str3,...) .................... 74
3.3.13 FIND_IN_SET(str,strlist)......................... 75
3.3.14 HEX(N_or_S) ..................................... 76
3.3.15 INSERT(str,pos,len,newstr) ....................... 77
3.3.16 INSTR() ......................................... 78
3.3.17 LCASE(str) ...................................... 81
3.3.18 LEFT(str,len) ................................... 81
3.3.19 LENGTH(str) ..................................... 81
3.3.20 LOAD_FILE(file_name) ............................ 82
3.3.21 LOCATE() ........................................ 82
3.3.22 LOWER(str) ...................................... 84
GBase 8a MPP Cluster SQL参考手册
南大通用数据技术股份有限公司 III
3.3.23 LPAD(str,len,padstr) ............................. 84
3.3.24 LTRIM(str)....................................... 85
3.3.25 MAKE_SET(bits,str1,str2,...) ..................... 85
3.3.26 MID(str,pos,len) ................................. 87
3.3.27 NVL(string1,replace_with) ........................ 87
3.3.28 OCT(N) .......................................... 88
3.3.29 ORD(str) ........................................ 89
3.3.30 REPEAT(str,count) ................................ 90
3.3.31 REPLACE(str,from_str,to_str) ..................... 90
3.3.32 REVERSE(str) ..................................... 91
3.3.33 RIGHT(str,len) ................................... 91
3.3.34 RPAD(str,len,padstr) ............................. 92
3.3.35 RTRIM(str)....................................... 92
3.3.36 SUBSTRING() ...................................... 93
3.3.37 SUBSTRING_INDEX(str,delim,count) ................. 94
3.3.38 TO_CHAR(number,[FORMAT]) ......................... 95
3.3.39 TO_CHAR(datetime,[FORMAT]) ...................... 102
3.3.40 TO_NUMBER(expr) ................................. 113
3.3.41 TRANSLATE(char,from_string,to_string) ........... 114
3.3.42 TRIM ........................................... 115
3.3.43 UCASE(str)...................................... 117
3.3.44 UNHEX(str)...................................... 117
3.3.45 UPPER(str)...................................... 118
3.3.46 字符串转换类型函数 .............................. 119
3.3.47 expr LIKE pat [ESCAPE 'escape-char'] ............ 120
3.3.48 expr NOT LIKE pat [ESCAPE 'escape-char'] ........ 123
3.3.49 expr REGEXP pat, expr RLIKE pat ................. 124
3.3.50 expr NOT REGEXP pat, expr NOT RLIKE pat ......... 126
3.3.51 STRCMP(expr1,expr2) ............................. 126
3.3.52 正则表达式函数 .................................. 127
3.4 数值函数 ............................................. 137
3.4.1 算术操作符...................................... 137
3.4.2 数学函数 ....................................... 140
3.5 日期和时间函数 ....................................... 165
3.5.1 夏令时 ......................................... 167
3.5.2 ADDDATE()....................................... 171
3.5.3 ADDTIME(expr,expr2) ............................. 172
GBase 8a MPP Cluster SQL参考手册
IV 南大通用数据技术股份有限公司
3.5.4 ADD_MONTHS(date,number[,mode]) .................. 173
3.5.5 CONVERT_TZ(dt,from_tz,to_tz) .................... 175
3.5.6 CURDATE() ...................................... 177
3.5.7 CURRENT_DATE，CURRENT_DATE() .................... 178
3.5.8 CURRENT_TIME，CURRENT_TIME() .................... 178
3.5.9 CURRENT_TIMESTAMP，CURRENT_TIMESTAMP() .......... 179
3.5.10 CURTIME() ...................................... 180
3.5.11 DATE(expr) ..................................... 181
3.5.12 DATEDIFF(expr,expr2) ........................... 182
3.5.13 DATE_ADD()，DATE_SUB() ......................... 183
3.5.14 DATE_FORMAT(date,FORMAT)........................ 188
3.5.15 DAY(date) ...................................... 191
3.5.16 DAYNAME(date) .................................. 191
3.5.17 DAYOFMONTH(date) ............................... 192
3.5.18 DAYOFWEEK(date) ................................ 193
3.5.19 DAYOFYEAR(date) ................................ 193
3.5.20 EXTRACT(type FROM date)......................... 194
3.5.21 FROM_DAYS(N) ................................... 196
3.5.22 FROM_UNIXTIME() ................................ 196
3.5.23 GET_FORMAT() ................................... 197
3.5.24 HOUR(time) ..................................... 199
3.5.25 LAST_DAY(date) ................................. 199
3.5.26 LOCALTIME，LOCALTIME() ......................... 201
3.5.27 LOCALTIMESTAMP，LOCALTIMESTAMP() ................ 202
3.5.28 MAKEDATE(year,dayofyear)........................ 203
3.5.29 MAKETIME(hour,minute,second) .................... 204
3.5.30 MICROSECOND(expr) .............................. 204
3.5.31 MINUTE(time) ................................... 206
3.5.32 MONTH(date) .................................... 207
3.5.33 MONTHNAME(date) ................................ 207
3.5.34 NOW() .......................................... 208
3.5.35 PERIOD_ADD(P,N) ................................ 208
3.5.36 PERIOD_DIFF(P1,P2) ............................. 209
3.5.37 QUARTER(date) .................................. 210
3.5.38 SECOND(time) ................................... 210
3.5.39 SEC_TO_TIME(seconds) ........................... 210
3.5.40 STR_TO_DATE(str,format)......................... 211
GBase 8a MPP Cluster SQL参考手册
南大通用数据技术股份有限公司 V
3.5.41 SUBDATE()....................................... 213
3.5.42 SUBTIME(expr,expr2) ............................. 215
3.5.43 SYSDATE 、SYSDATE() ............................. 215
3.5.44 TIME(expr)...................................... 216
3.5.45 TIMEDIFF(expr,expr2) ............................ 216
3.5.46 TIMESTAMP....................................... 217
3.5.47 TIMESTAMPADD .................................... 218
3.5.48 TIMESTAMPDIFF ................................... 219
3.5.49 TIME_FORMAT(time,format) ........................ 220
3.5.50 TIME_TO_SEC(time) ............................... 220
3.5.51 TO_DATE(string,format) .......................... 221
3.5.52 TO_DAYS(date) ................................... 230
3.5.53 TRUNC(date/datetime[, format]) .................. 231
3.5.54 UNIX_TIMESTAMP .................................. 234
3.5.55 UTC_DATE，UTC_DATE() ............................ 235
3.5.56 UTC_TIME，UTC_TIME() ............................ 235
3.5.57 UTC_TIMESTAMP，UTC_TIMESTAMP() .................. 236
3.5.58 WEEK(date[,mode]) ............................... 236
3.5.59 WEEKDAY(date) ................................... 240
3.5.60 WEEKOFYEAR(date) ................................ 241
3.5.61 YEAR(date)...................................... 242
3.5.62 YEARWEEK(date)，YEARWEEK(date,mode) ............. 242
3.6 其它函数 ............................................. 243
3.6.1 位函数 ......................................... 243
3.6.2 加密函数 ....................................... 247
3.6.3 信息函数 ....................................... 249
3.6.4 辅助函数 ....................................... 255
3.7 用于GROUP BY子句的函数和修饰语 ........................ 259
3.7.1 GROUP BY（聚集）函数 ............................ 259
3.8 OLAP函数 ............................................. 281
3.8.1 GROUP BY类函数 .................................. 282
3.8.2 非GROUP BY类函数 ................................ 287
3.9 ROWID函数 ............................................ 303
4 SQL语法 .................................................... 307
4.1 DDL语句 .............................................. 307
4.1.1 DATABASE ....................................... 307
4.1.2 TABLE .......................................... 308
GBase 8a MPP Cluster SQL参考手册
VI 南大通用数据技术股份有限公司
4.1.3 VIEW ........................................... 342
4.1.4 INDEX .......................................... 346
4.1.5 预租磁盘 ....................................... 350
4.1.6 列和表的压缩 ................................... 352
4.1.7 行列混存 ....................................... 360
4.1.8 表级、列级的缓存装载与释放 ...................... 369
4.2 DML语句 .............................................. 370
4.2.1 INSERT ......................................... 371
4.2.2 UPDATE ......................................... 375
4.2.3 DELETE ......................................... 383
4.2.4 SELECT ......................................... 385
4.2.5 SELECT的优化 ................................... 396
4.2.6 JOIN ........................................... 401
4.2.7 UNION .......................................... 408
4.2.8 INTERSECT ...................................... 413
4.2.9 MINUS .......................................... 414
4.2.10 MERGE .......................................... 416
4.2.11 EXCEPT ......................................... 425
4.3 查询结果导出语句 ..................................... 426
4.3.1 SELECT INTO OUTFILE ............................ 426
4.3.2 rmt:SELECT INTO OUTFILE ... ..................... 462
4.4 集群批量加载语句 ..................................... 470
4.4.1 集群加载语法 ................................... 470
4.4.2 使用约束 ....................................... 477
4.4.3 集群加载使用示例 ............................... 481
4.5 GBase 8a MPP Cluster其它语句 ......................... 501
4.5.1 DESCRIBE ....................................... 501
4.5.2 USE ............................................ 502
4.5.3 KILL ........................................... 503
4.5.4 SET ............................................ 504
4.5.5 PAUSE/CONTINUE ................................. 506
4.6 数据库管理语句 ....................................... 507
4.6.1 帐号管理 ....................................... 507
4.6.2 权限管理 ....................................... 510
4.6.3 用户组管理 ..................................... 515
4.6.4 SHOW管理语句 ................................... 517
4.7 HINT机制 ............................................. 540
GBase 8a MPP Cluster SQL参考手册
南大通用数据技术股份有限公司 VII
4.8 同义词 ............................................... 542
4.8.1 同义词权限...................................... 543
4.8.2 创建同义词...................................... 543
4.8.3 使用同义词...................................... 545
4.8.4 删除同义词...................................... 547
5 存储过程、函数 ............................................. 548
5.1 概述 ................................................. 548
5.1.1 存储过程 ....................................... 548
5.1.2 函数 ........................................... 549
5.2 创建存储过程、函数 ................................... 549
5.3 修改存储过程、函数 ................................... 555
5.4 删除存储过程、函数 ................................... 555
5.5 调用存储过程、函数 ................................... 556
5.6 查看存储过程、函数的状态 ............................. 558
5.7 存储过程所支持的流程结构和语句 ........................ 559
5.7.1 DELIMITER....................................... 559
5.7.2 BEGIN...END ..................................... 560
5.7.3 DECLARE ........................................ 560
5.7.4 SET ............................................ 564
5.7.5 SELECT...INTO... ................................ 565
5.7.6 IF ............................................. 566
5.7.7 ITERATE ........................................ 567
5.7.8 CASE ........................................... 568
5.7.9 LOOP ........................................... 571
5.7.10 REPEAT ......................................... 572
5.7.11 WHILE .......................................... 573
5.7.12 LEAVE .......................................... 574
5.7.13 静态游标（CURSOR） .............................. 575
5.7.14 动态游标（REF CURSOR） .......................... 582
5.8 使用限制 ............................................. 589
6 EVENT事件 .................................................. 592
6.1 创建事件 ............................................. 592
6.1.1 创建语法 ....................................... 592
6.1.2 事件调度器...................................... 594
6.1.3 示例 ........................................... 596
6.1.4 注意事项 ....................................... 597
6.2 查看事件 ............................................. 597
GBase 8a MPP Cluster SQL参考手册
VIII 南大通用数据技术股份有限公司
6.3 修改事件 ............................................. 598
6.4 删除事件 ............................................. 599
附录........................................................... 600
GBase 8a MPP Cluster分析型数据库保留字 ...................... 600
A.......................................................... 600
B.......................................................... 600
C.......................................................... 600
D.......................................................... 601
E.......................................................... 601
F.......................................................... 601
G.......................................................... 602
H.......................................................... 602
I.......................................................... 602
J.......................................................... 602
K.......................................................... 602
L.......................................................... 603
M.......................................................... 603
N.......................................................... 603
O.......................................................... 603
P.......................................................... 604
R.......................................................... 604
S.......................................................... 604
T.......................................................... 605
U.......................................................... 605
V.......................................................... 605
W.......................................................... 605
X.......................................................... 605
Y.......................................................... 606
Z.......................................................... 606
前言 ............................................................. 1
手册简介 ..................................................... 1
公约 ......................................................... 2
1 数据类型 ..................................................... 3
1.1 数值类型 ............................................... 3
1.1.1 TINYINT .......................................... 4
1.1.2 SMALLINT ......................................... 4
1.1.3 INT .............................................. 4
1.1.4 BIGINT ........................................... 4
1.1.5 FLOAT ............................................ 5
1.1.6 DOUBLE ........................................... 7
1.1.7 DECIMAL .......................................... 8
1.1.8 NUMERIC ......................................... 11
1.2 字符类型 .............................................. 11
1.2.1 CHAR ............................................ 11
1.2.2 VARCHAR ......................................... 12
1.2.3 TEXT ............................................ 13
1.3 二进制数据类型 ........................................ 13
1.4 日期和时间类型 ........................................ 13
1.4.1 DATE ............................................ 14
1.4.2 TIME ............................................ 15
1.4.3 DATETIME ........................................ 16
1.4.4 TIMESTAMP........................................ 18
2 SQL语言基础 ................................................. 21
2.1 数值 .................................................. 21
2.1.1 字符串 .......................................... 21
2.1.2 数字 ............................................ 24
2.1.3 十六进制值....................................... 24
2.1.4 布尔值 .......................................... 26
2.1.5 NULL值 .......................................... 26
2.2 数据库、表、列和别名 .................................. 27
2.3 标识符限定词 .......................................... 28
2.4 注释语法 .............................................. 29
2.5 用户变量 .............................................. 30
GBase 8a MPP Cluster SQL参考手册
II 南大通用数据技术股份有限公司
3 操作符和函数 ................................................ 32
3.1 操作符 ................................................ 32
3.1.1 操作符优先级 .................................... 32
3.1.2 圆括号 .......................................... 33
3.1.3 比较函数和操作符 ................................ 34
3.1.4 逻辑操作符 ...................................... 49
3.1.5 转换操作符和函数 ................................ 53
3.1.6 日期算术运算 .................................... 59
3.2 控制流函数 ............................................ 60
3.2.1 CASE ............................................ 60
3.2.2 DECODE .......................................... 61
3.2.3 IF(expr1,expr2,expr3) ........................... 62
3.2.4 IFNULL(expr1,expr2) ............................. 63
3.2.5 NULLIF(expr1,expr2) ............................. 64
3.3 字符串函数 ............................................ 65
3.3.1 ASCII(str) ...................................... 65
3.3.2 BIN(N) .......................................... 66
3.3.3 BIT_LENGTH(str) ................................. 66
3.3.4 CHAR(N,...) ..................................... 67
3.3.5 CHAR_LENGTH(str) ................................ 68
3.3.6 CHARACTER_LENGTH(str) ........................... 69
3.3.7 CONCAT(str1,str2,...) ........................... 69
3.3.8 CONCAT_WS(separator,str1,str2,...) ............... 70
3.3.9 CONV(N,from_base,to_base)........................ 71
3.3.10 ELT(N,str1,str2,str3,...)........................ 72
3.3.11 EXPORT_SET ...................................... 73
3.3.12 FIELD(str,str1,str2,str3,...) .................... 74
3.3.13 FIND_IN_SET(str,strlist)......................... 75
3.3.14 HEX(N_or_S) ..................................... 76
3.3.15 INSERT(str,pos,len,newstr) ....................... 77
3.3.16 INSTR() ......................................... 78
3.3.17 LCASE(str) ...................................... 81
3.3.18 LEFT(str,len) ................................... 81
3.3.19 LENGTH(str) ..................................... 81
3.3.20 LOAD_FILE(file_name) ............................ 82
3.3.21 LOCATE() ........................................ 82
3.3.22 LOWER(str) ...................................... 84
GBase 8a MPP Cluster SQL参考手册
南大通用数据技术股份有限公司 III
3.3.23 LPAD(str,len,padstr) ............................. 84
3.3.24 LTRIM(str)....................................... 85
3.3.25 MAKE_SET(bits,str1,str2,...) ..................... 85
3.3.26 MID(str,pos,len) ................................. 87
3.3.27 NVL(string1,replace_with) ........................ 87
3.3.28 OCT(N) .......................................... 88
3.3.29 ORD(str) ........................................ 89
3.3.30 REPEAT(str,count) ................................ 90
3.3.31 REPLACE(str,from_str,to_str) ..................... 90
3.3.32 REVERSE(str) ..................................... 91
3.3.33 RIGHT(str,len) ................................... 91
3.3.34 RPAD(str,len,padstr) ............................. 92
3.3.35 RTRIM(str)....................................... 92
3.3.36 SUBSTRING() ...................................... 93
3.3.37 SUBSTRING_INDEX(str,delim,count) ................. 94
3.3.38 TO_CHAR(number,[FORMAT]) ......................... 95
3.3.39 TO_CHAR(datetime,[FORMAT]) ...................... 102
3.3.40 TO_NUMBER(expr) ................................. 113
3.3.41 TRANSLATE(char,from_string,to_string) ........... 114
3.3.42 TRIM ........................................... 115
3.3.43 UCASE(str)...................................... 117
3.3.44 UNHEX(str)...................................... 117
3.3.45 UPPER(str)...................................... 118
3.3.46 字符串转换类型函数 .............................. 119
3.3.47 expr LIKE pat [ESCAPE 'escape-char'] ............ 120
3.3.48 expr NOT LIKE pat [ESCAPE 'escape-char'] ........ 123
3.3.49 expr REGEXP pat, expr RLIKE pat ................. 124
3.3.50 expr NOT REGEXP pat, expr NOT RLIKE pat ......... 126
3.3.51 STRCMP(expr1,expr2) ............................. 126
3.3.52 正则表达式函数 .................................. 127
3.4 数值函数 ............................................. 137
3.4.1 算术操作符...................................... 137
3.4.2 数学函数 ....................................... 140
3.5 日期和时间函数 ....................................... 165
3.5.1 夏令时 ......................................... 167
3.5.2 ADDDATE()....................................... 171
3.5.3 ADDTIME(expr,expr2) ............................. 172
GBase 8a MPP Cluster SQL参考手册
IV 南大通用数据技术股份有限公司
3.5.4 ADD_MONTHS(date,number[,mode]) .................. 173
3.5.5 CONVERT_TZ(dt,from_tz,to_tz) .................... 175
3.5.6 CURDATE() ...................................... 177
3.5.7 CURRENT_DATE，CURRENT_DATE() .................... 178
3.5.8 CURRENT_TIME，CURRENT_TIME() .................... 178
3.5.9 CURRENT_TIMESTAMP，CURRENT_TIMESTAMP() .......... 179
3.5.10 CURTIME() ...................................... 180
3.5.11 DATE(expr) ..................................... 181
3.5.12 DATEDIFF(expr,expr2) ........................... 182
3.5.13 DATE_ADD()，DATE_SUB() ......................... 183
3.5.14 DATE_FORMAT(date,FORMAT)........................ 188
3.5.15 DAY(date) ...................................... 191
3.5.16 DAYNAME(date) .................................. 191
3.5.17 DAYOFMONTH(date) ............................... 192
3.5.18 DAYOFWEEK(date) ................................ 193
3.5.19 DAYOFYEAR(date) ................................ 193
3.5.20 EXTRACT(type FROM date)......................... 194
3.5.21 FROM_DAYS(N) ................................... 196
3.5.22 FROM_UNIXTIME() ................................ 196
3.5.23 GET_FORMAT() ................................... 197
3.5.24 HOUR(time) ..................................... 199
3.5.25 LAST_DAY(date) ................................. 199
3.5.26 LOCALTIME，LOCALTIME() ......................... 201
3.5.27 LOCALTIMESTAMP，LOCALTIMESTAMP() ................ 202
3.5.28 MAKEDATE(year,dayofyear)........................ 203
3.5.29 MAKETIME(hour,minute,second) .................... 204
3.5.30 MICROSECOND(expr) .............................. 204
3.5.31 MINUTE(time) ................................... 206
3.5.32 MONTH(date) .................................... 207
3.5.33 MONTHNAME(date) ................................ 207
3.5.34 NOW() .......................................... 208
3.5.35 PERIOD_ADD(P,N) ................................ 208
3.5.36 PERIOD_DIFF(P1,P2) ............................. 209
3.5.37 QUARTER(date) .................................. 210
3.5.38 SECOND(time) ................................... 210
3.5.39 SEC_TO_TIME(seconds) ........................... 210
3.5.40 STR_TO_DATE(str,format)......................... 211
GBase 8a MPP Cluster SQL参考手册
南大通用数据技术股份有限公司 V
3.5.41 SUBDATE()....................................... 213
3.5.42 SUBTIME(expr,expr2) ............................. 215
3.5.43 SYSDATE 、SYSDATE() ............................. 215
3.5.44 TIME(expr)...................................... 216
3.5.45 TIMEDIFF(expr,expr2) ............................ 216
3.5.46 TIMESTAMP....................................... 217
3.5.47 TIMESTAMPADD .................................... 218
3.5.48 TIMESTAMPDIFF ................................... 219
3.5.49 TIME_FORMAT(time,format) ........................ 220
3.5.50 TIME_TO_SEC(time) ............................... 220
3.5.51 TO_DATE(string,format) .......................... 221
3.5.52 TO_DAYS(date) ................................... 230
3.5.53 TRUNC(date/datetime[, format]) .................. 231
3.5.54 UNIX_TIMESTAMP .................................. 234
3.5.55 UTC_DATE，UTC_DATE() ............................ 235
3.5.56 UTC_TIME，UTC_TIME() ............................ 235
3.5.57 UTC_TIMESTAMP，UTC_TIMESTAMP() .................. 236
3.5.58 WEEK(date[,mode]) ............................... 236
3.5.59 WEEKDAY(date) ................................... 240
3.5.60 WEEKOFYEAR(date) ................................ 241
3.5.61 YEAR(date)...................................... 242
3.5.62 YEARWEEK(date)，YEARWEEK(date,mode) ............. 242
3.6 其它函数 ............................................. 243
3.6.1 位函数 ......................................... 243
3.6.2 加密函数 ....................................... 247
3.6.3 信息函数 ....................................... 249
3.6.4 辅助函数 ....................................... 255
3.7 用于GROUP BY子句的函数和修饰语 ........................ 259
3.7.1 GROUP BY（聚集）函数 ............................ 259
3.8 OLAP函数 ............................................. 281
3.8.1 GROUP BY类函数 .................................. 282
3.8.2 非GROUP BY类函数 ................................ 287
3.9 ROWID函数 ............................................ 303
4 SQL语法 .................................................... 307
4.1 DDL语句 .............................................. 307
4.1.1 DATABASE ....................................... 307
4.1.2 TABLE .......................................... 308
GBase 8a MPP Cluster SQL参考手册
VI 南大通用数据技术股份有限公司
4.1.3 VIEW ........................................... 342
4.1.4 INDEX .......................................... 346
4.1.5 预租磁盘 ....................................... 350
4.1.6 列和表的压缩 ................................... 352
4.1.7 行列混存 ....................................... 360
4.1.8 表级、列级的缓存装载与释放 ...................... 369
4.2 DML语句 .............................................. 370
4.2.1 INSERT ......................................... 371
4.2.2 UPDATE ......................................... 375
4.2.3 DELETE ......................................... 383
4.2.4 SELECT ......................................... 385
4.2.5 SELECT的优化 ................................... 396
4.2.6 JOIN ........................................... 401
4.2.7 UNION .......................................... 408
4.2.8 INTERSECT ...................................... 413
4.2.9 MINUS .......................................... 414
4.2.10 MERGE .......................................... 416
4.2.11 EXCEPT ......................................... 425
4.3 查询结果导出语句 ..................................... 426
4.3.1 SELECT INTO OUTFILE ............................ 426
4.3.2 rmt:SELECT INTO OUTFILE ... ..................... 462
4.4 集群批量加载语句 ..................................... 470
4.4.1 集群加载语法 ................................... 470
4.4.2 使用约束 ....................................... 477
4.4.3 集群加载使用示例 ............................... 481
4.5 GBase 8a MPP Cluster其它语句 ......................... 501
4.5.1 DESCRIBE ....................................... 501
4.5.2 USE ............................................ 502
4.5.3 KILL ........................................... 503
4.5.4 SET ............................................ 504
4.5.5 PAUSE/CONTINUE ................................. 506
4.6 数据库管理语句 ....................................... 507
4.6.1 帐号管理 ....................................... 507
4.6.2 权限管理 ....................................... 510
4.6.3 用户组管理 ..................................... 515
4.6.4 SHOW管理语句 ................................... 517
4.7 HINT机制 ............................................. 540
GBase 8a MPP Cluster SQL参考手册
南大通用数据技术股份有限公司 VII
4.8 同义词 ............................................... 542
4.8.1 同义词权限...................................... 543
4.8.2 创建同义词...................................... 543
4.8.3 使用同义词...................................... 545
4.8.4 删除同义词...................................... 547
5 存储过程、函数 ............................................. 548
5.1 概述 ................................................. 548
5.1.1 存储过程 ....................................... 548
5.1.2 函数 ........................................... 549
5.2 创建存储过程、函数 ................................... 549
5.3 修改存储过程、函数 ................................... 555
5.4 删除存储过程、函数 ................................... 555
5.5 调用存储过程、函数 ................................... 556
5.6 查看存储过程、函数的状态 ............................. 558
5.7 存储过程所支持的流程结构和语句 ........................ 559
5.7.1 DELIMITER....................................... 559
5.7.2 BEGIN...END ..................................... 560
5.7.3 DECLARE ........................................ 560
5.7.4 SET ............................................ 564
5.7.5 SELECT...INTO... ................................ 565
5.7.6 IF ............................................. 566
5.7.7 ITERATE ........................................ 567
5.7.8 CASE ........................................... 568
5.7.9 LOOP ........................................... 571
5.7.10 REPEAT ......................................... 572
5.7.11 WHILE .......................................... 573
5.7.12 LEAVE .......................................... 574
5.7.13 静态游标（CURSOR） .............................. 575
5.7.14 动态游标（REF CURSOR） .......................... 582
5.8 使用限制 ............................................. 589
6 EVENT事件 .................................................. 592
6.1 创建事件 ............................................. 592
6.1.1 创建语法 ....................................... 592
6.1.2 事件调度器...................................... 594
6.1.3 示例 ........................................... 596
6.1.4 注意事项 ....................................... 597
6.2 查看事件 ............................................. 597
GBase 8a MPP Cluster SQL参考手册
VIII 南大通用数据技术股份有限公司
6.3 修改事件 ............................................. 598
6.4 删除事件 ............................................. 599
附录........................................................... 600
GBase 8a MPP Cluster分析型数据库保留字 ...................... 600
A.......................................................... 600
B.......................................................... 600
C.......................................................... 600
D.......................................................... 601
E.......................................................... 601
F.......................................................... 601
G.......................................................... 602
H.......................................................... 602
I.......................................................... 602
J.......................................................... 602
K.......................................................... 602
L.......................................................... 603
M.......................................................... 603
N.......................................................... 603
O.......................................................... 603
P.......................................................... 604
R.......................................................... 604
S.......................................................... 604
T.......................................................... 605
U.......................................................... 605
V.......................................................... 605
W.......................................................... 605
X.......................................................... 605
Y.......................................................... 606
Z.......................................................... 606