GBase-8A-MPP-Cluster-产品培训-SQL指南

InsertPictureHereGBASE版权所有©2004-2014GBase8aMPPCluster产品开发指南南大通用数据技术股份有限公司目录2存储过程和函数三SQL语法介绍二数据类型和语言基础一SQL使用注意事项四应用程序接口简介五数据类型—数值类型3数据分类数据类型数值型INTEGER、TINYINT、SMALLINT、BIGINT、DECIMAL、FLOAT、DOUBLE字符型CHAR、VARCHAR、TEXT日期型DATE、TIME、DATETIME、TIMESTAMP大对象数据类型BLOBOracle的DATE:公元前4712年1月1日到公元9999年12月31日ORACLETIMESTAMP:可以定义毫秒精度，GBaseTIMESTAMP类型只精确到秒DATETIME:公元0001年1月1日到公元9999年12月31日TIMESTAMP:公元1970年1月1日08时0分1秒到公元2038年01月01日00时59分59秒4GBase8aMPPCluster的数据类型数值型范围类型名称最小值最大值TINYINT-127127SMALLINT-3276732767INT(INTEGER)-21474836472147483647BIGINT-9.22337E+189.22337E+18FLOAT[(M[,D])]-3.40E+383.40E+38DOUBLE[(M,D)]-1.7976931348623157E+3081.7976931348623157E+308DECIMAL[(M[,D])]-(1E+M-1)/(1E+D)(1E+M-1)/(1E+D)严格的数值数据类型（INTEGER，STINYINT，MALLINT，BIGINT，DECIMAL）；近似的数值数据类型（FLOAT和DOUBLE）。与其它数据库数据类型对应关系5oracle默认varchar(4000)，可以不写，gbase必须写oraclenumber灵活与oracle对比需手工改写的数据类型oracleGBase备注BFILE、RAW(size)、LONGRAWBLOB上限：32KCLOB、LONG、NCLOBTEXT上限：32K[N]CHAR(len)[N]CHAR(size)size255（定长）[N]VARCHAR(size)size65535（变长）NVARCHAR2(len)、VARCHAR2(len[BYTE|CHAR])VARCHAR(len)size：32KB（utf8）BINARY_DOUBLEDOUBLEBINARY_FLOATFLOATTIMESTAMP[(precision)]WITHTIMEZONE、TIMESTAMP[(precision)]WITHLOCALTIMEZONETIMESTAMP时区信息无法保存REALDECIMAL高精度decima替代NUMBER(p,s)gbase所有数值类型依赖于精度、范围、和实际需求6GBase8aMPPCluster的系统函数函数类型函数举例字符串函数CONCAT(),INSERT(),SUBSTRING()...数值函数RAND(),ROUND(X),TRUNCATE(X,D)...日期和时间函数ADDDATE,CURTIME(),DATE_FORMAT...位函数|按位或,左移操作(BIGINT)...加密函数SHA1(str)，SHA(str),PASSWORD(str)...信息函数BENCHMARK(count,expr),VERSION()...辅助函数FORMAT(X,D),SLEEP(duration)...聚集函数AVG([DISTINCT]expr),COUNT(expr)...OLAP函数COUNTOVER，GROUPBYCUBE...转换函数ROWID...具体函数使用参看《GBase8aClusterSQL参考手册》7函数差异举例8ORACLE与GBase对日期和时间运算ORACLE：当前时间前1秒sysdate-1/(24*60*60)当前时间前1分sysdate-1/(24*60)当前时间前1小时sysdate-1/24当前时间前1天sysdate-1两个给定时间相差的秒数to_number(to_date('10:00:00','hh24:mi:ss')-to_date('08:00:00','hh24:mi:ss'))*86400本周第一天trunc(sysdate,'day')GBase对应：date_add(sysdate(),interval-1second)date_add(sysdate(),interval-1minute)date_add(sysdate(),interval-1hour)adddate(sysdate(),-1)timestampdiff(second,to_date('08:00:00','hh24:mi:ss'),to_date('10:00:00','hh24:mi:ss'))adddate(sysdate(),-(weekday(sysdate())+1))函数差异举例•Oracle的rownum针对结果集，从1开始。Rowid是物理地址，针对表•GBase可使用limit替换rownum，可用临时表替换临时结果集•GBase的rowid从0开始，rowid是序列号•Oraclelength()同gbasechar_length()•Oraclelengthb()同gbaselength()9函数差异举例10•取结果集中指定位置的记录oracle：rownum针对结果集，从1开始SELECT*FROMTABLErownum=100gbase：使用limit替换rownum，可用临时表替换临时结果集语法：SELECT*FROMTABLELIMITstart_rows,rows;Oracletrim(‘空格’)nullGBasetrim(‘空格’)‘’处理以下流程时需注意：OracleIftrim(‘’)isnullthen…..GBaseIftrim(‘’)isnullortrim(‘’)=’’then…返回受上一语句影响的行数OracleGBase使用隐式游标SQL%ROWCOUNT使用函数ROW_COUNT()11函数差异举例oracle系统函数有的不带括号，gbase系统函数后面必须有括号标识符数据库、表、列和别名都是标识符，允许的语法规则。标识符最大长度（字符）允许的字符数据库52除去`\.*\/:?|的可显示字符（graphcharacter0x21--0x7e）。表52一个文件名中允许的任何字符，除了‘/’，‘\’,或‘.’。列52所有的字符。多个标识符组成。组合名称的各个组成成分应该用句号字符‘.’分割开。可用下面多种形式引用一个列：列引用含义col_name列col_name来自查询所用的任何一个表中对应字段。tbl_name.col_name列col_name来自当前数据库中的表tbl_name。db_name.tbl_name.col_name列col_name来自数据库db_name中的表tbl_name。`column_name`该字段是一个关键词或包含特殊字符。12常见标识符问题•在做INSERTINTOSELECT的时候，建议如果select的字段有计算，最好是指定一个别名，这样既可以使SQL更加易懂，也避免出现超长报错。•示例：Insertintoselect(casewhenb.AMBS_OWNING_BRANCH_NBRisnotnullthenb.AMBS_OWNING_BRANCH_NBRelse‘xxxxxxxxx’end)……；insertintoselect的时候，会产生临时表，而(casewhenb.AMBS_OWNING_BRANCH_NBRisnotnullthenb.AMBS_OWNING_BRANCH_NBRelse‘xxxxxxxxx’end)作为临时表的字段名字，长度超过了GBase规定的长度会报错而且含义不清晰1314SQL语法介绍二GBase8aMPPCluster的SQL综合介绍•符合SQL92标准•集群跟单机版的SQL语法基本相同。•集群表类型中区分分布表、复制表、nocopies表、临时表•支持#，--，/**/三种注释风格•每条SQL命令默认以分号;表示结束•SQL执行默认为自动提交commit•也可以更换限定符结束（在存储过程中需要更换）•DELIMITER限定符15GBase8aMPPCluster的数据库对象及DDL数据库对象或操作属性使用及约束数据库支持数据库实例的创建、修改、删除；字符集，支持gbk和utf8字符集；表支持表的创建、修改（包括表名称重命名）、删除（DELETE、TRUNCATE）；支持分布表、复制表、临时表、nocopies表等表类型视图支持视图的创建、修改、删除；索引支持GLOBAL、LOCAL级别的hash索引创建、删除；支持多列hash索引的创建、修改；数据库对象16GBase8aMPPCluster的数据库对象及DDL数据库对象使用及约束存储过程支持用户自定义存储过程的创建、删除；自定义function支持用户自定义创建function；用户集群支持账户的创建，删除，更名；用户权限支持对用户的赋权及权限回收；分布列DDL创建表时如果需要指定distributedby列，只能指定表中一列为distributedby列；数据库对象17详细信息可查看《GBase8aMPPClusterSQL参考手册》18SQL语法—DDL(数据库)创建数据库CREATEDATABASE[IFNOTEXISTS]database_name;CREATEDATABASEifnotexiststest;删除数据库DROPDATABASEIFEXISTStest;SQL语法—DDL（表）创建表gbaseCREATETABLEt1(avarchar(10)NOTNULL,bintAUTO_INCREMENT,PRIMARYKEY(b));gbaseCREATETABLEti(cINT,KEYidx_hash_tc(c)USINGHASHglobal);gbaseCREATETABLEstudent2(stu_noVARCHAR(10))SELECTstu_no,birthdayFROMstudent;gbaseCREATETABLEnew_studentLIKEstudent;注意事项PRIMARYKEY、CHECK语法支持，不起实际效用19SQL语法—DDL（表）修改表gbaseALTERTABLEtADDcolumncvarchar(10)null;gbaseALTERTABLEtDROPcolumnb;gbaseALTERTABLEtRENAMEttt2;gbaseALTERTABLEtCHANGEbdvarchar(10);gbaseALTERTABLEtMODIFYbvarchar(10)FIRST;注意事项：不支持改变列的数据类型、改变列的属性（NOTNULL，默认值、注释）、改变表的字符集；20SQL语法—DDL（表）修改表gbaseALTERTABLEtSHRINKSPACE;gbaseALTERTABLEt1ALTERaCOMPRESS(1);gbaseALTERTABLEt2ALTERcompress(5,5);注意事项：释放空间以物理文件为单位释放；压缩模式改变只对后续入库数据有效21SQL语法—DDL（表）重命名表gbaseRENAMETABLEtest.ntotest.m;删除表中所有行gbaseTRUNCATETABLEb;删除表droptablet1;22分布表•分布表•分布表可以使数据按指定的策略分布存储在不同的主机上，从而实现分布式数据存储和分布式计算，解决大数据存储容量扩展和计算性能扩展的问题。•分布策略：采用hash分布、ran