INFORMIX-SQL语法与规范

一、INFORMIXSQL语法DML(DataManipulationLanguagecommands)数据操纵语言DDL(DataDefinitionLanguagecommands)数据定义语言TCC(TransactionControlcommands)事务控制语言SCC(SystemControlcommands)系统控制语言1、AlterALTERTABLE表名[ADD新列名数据类型[DEFAULT缺省值][完整性约束名][BEFORE列名]][DROP列名][MODIFY[列名数据类型[NULL|NOTNULL]]];ALTERTABLE表名[ADDCONSTRAINT[UNIQUE(列名)][DISTINCT(列名)][PRIMARYKEY(列名)][FOREIGNKEY(列名)REFERENCES表名.(列名)ONDELETECASCADE][CONSTRAINT约束名]][DROPCONSTRAINT约束名][MODIFYNEXTSIZE数值(单位KB)][LOCKMODE(PAGE|ROW|TABLE)];2、RenameRENAMECOLUMN表名.旧字段名TO表名.新字段名;RENAMETABLE旧表名TO新表名;RENAMEDATABASE旧数据库名TO新数据库名;3、GrantGRANT[CONNECT|RESOURCE|DBA]TOPUBLIC|用户;GRANT[ALL|,SELECT|,INSERT|,UPDATE|,DELETE]ON表名TOPUBLIC|用户;GRANTEXECUTEON存储过程名称TOPUBLIC|用户;4、RevokeREVOKE[CONNECT|RESOURCE|DBA]FROMPUBLIC|用户;REVOKE[ALL|,SELECT|,INSERT|,UPDATE|,DELETE]ON表名FROMPUBLIC|用户;REVOKEEXECUTEON存储过程名称FROMPUBLIC|用户;5、LoadLOADFROM文件名[DELIMITER‘delimiter’]INSERTINTO表名(字段);6、UnloadUNLOADTO文件名[DELIMITER‘delimiter’]复合select语句;7、UpdateStatisticsUPDATESTATISCTICSFORPROCEDURE存储过程名;UPDATESTATISCTICS[LOW|MIDIUM|HIGH]FORTABLE[表名|synonym[(字段名)]][DROPDISTRIBUTIONS];二、INFORMIXSPL语法1、CallCALLPROCEDURE存储过程名称(参数)[RETURNING返回值参数];2、ContinueCONTINUE[FOR|FOREACH|WHILE];3、DefineDEFINEGLOBAL变量[数据类型[DEFAULT缺省值]]|[REFERENCES[BYTE|TEXT]];DEFINE变量[数据类型|[REFERENCES[BYTE|TEXT]|PROCEDURELIKE表名.列名];4、ExitEXIT[FOR|FOREACH|WHILE]5、ForFOR变量=开始TO结束[STEP步长]ENDFOR;FOR变量IN范围表达式ENDFOR;6、Foreach7、IfIF条件THEN语句[ELIF条件THEN语句][ELSE语句]ENDIF8、LetLET变量=数据(可以是表达式，并且可以多个一起赋值);9、OnException10、RaiseException11、ReturnRETURN[表达式WHILERESUME];12、System13、Trace14、WhileWHILE条件语句ENDWHILE;三、标量函数1、数值标量函数ABS(n)求n的绝对值MOD(n,m)求n被m除的余数POW(n,m)求n的m次方ROUND(n,m)把n四舍五入，保留m位小数ROOT(source,index)大于最大TRUNC(source,+,-,position)截位2、字符型函数LENGTH(char)求char的长度CHAR_LENGTH(char)求char的长度LOWER(char)把char中的字符改成小写UPPER(char)把char中的字符改成大写LPAD(char1,n[,char2])从左侧用char2补齐char1至长度nRPAD(char1,n[,char2])从右侧用char2补齐char1至长度nTRIM(char)去左右空格SUBSTR(str,n,m)在str中从位置n开始截取长度为m的字符串REPLACE(source_string,old_str,new_str)3、日期型函数TODAY返回当前数据库服务器系统日期CURRENT返回当前数据库服务器系统日期和时间DAY(dateordatetime)返回1--31MONTH(dateordatetime)返回1--12WEEKDAY(dateordatetime)返回0—6YEAR(dateordatetime)返回四位年份4、空值函数NVL(参数1,参数2)如参数1不是空值，返回其值，否则返回参数2的值6、条件标量函数ExecuteEXECUTEPROCEDURE存储过程名称(参数)[RETURNINGprocedure_var]CaseCASEWHEN条件THEN动作…………ELSE动作ENDCASE字段WHEN值THEN值…………ELSE值ENDselectmobile_number,join_date,casewhenmobile_number’13902318000’andmobile_number’13902318999’then‘斗门’whenmobile_number’13802318000’andmobile_number’13902318999’then‘珠海’……………else‘珠海’end,pinpai_code,sfzh_codefromf01_customer;selectmobile_number,casepinpai_codewhen“0”then“全球通”when“1”then“模拟机”when“2”then“本地通”when“4”then“大众通”else“全球通”end,join_datefromf01_customer空值转换Nvlselectmobile_number,nvl(spending_code,999)fromf01_customer多重条件判断语句:Decodeselectmobile_number,decode(pinpai_code,’0’,’全球通’,’1’,’模拟机’,’2’,’本地通’,’4’,’大众通’,’全球通’)fromf01_customer四、集合函数AVG(e)求一组行中e值的平均值COUNT(e)计算一组行中e值为非空值的行数COUNT(*)计算表中的行数MAX(e)计算一组行中e值的最大值MIN(e)计算一组行中e值的最小值SUM(e)计算一组行中e值的总和五、数据类型smallint16位元的整数interger32位元的整数decimal(p,s)p精确值和s大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点後有几位数。如果没有特别指定，则系统会设为p=5;s=0float32位元的实数double64位元的实数char(n)n长度的字串，n不能超过254varchar(n)长度不固定且其最大长度为n的字串，n不能超过4000graphic(n)和char(n)一样，不过其单位是两个字元double-bytes，n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字vargraphic(n)可变长度且其最大长度为n的双字元字串，n不能超过2000date包含了年份、月份、日期time包含了小时、分钟、秒timestamp包含了年、月、日、时、分、秒、千分之一秒六、INFORMIXSQL使用常识1、语句书写层次分明，风格尽量保持一致2、强制使用/不使用索引强制使用索引：select*fromempwhereempno0;select*fromempwhereename‘’；强制不使用索引：select*fromempwhereempno+00;select*fromempwhereename||‘’=’scott‘3、有效利用索引避免对索引列进行计算select*fromempwheresal*0.2500改为select*fromempwheresal2500避免在索引列使用NOTNOT改为=NOT=改为NOT=改为NOT改为=NOT=改为避免在索引列使用NULLselect*fromempwheresalISNOTNULL改为select*fromempwheresal04、减少访问数据库的次数select*fromempwhereempno=1234;select*fromempwhereempno=4321;改为selecta.*,b.*fromempa,empbwherea.empno=1234andb.empno=4321;selectenamefromempwhereempno=1234andsal=3000;selectenamefromempwhereempno=1234andsal=5000;改为SelectDecode(sal,3000,ename,null)ename1,Decode(sal,5000,ename,null)ename2Fromempnowhereempno=1234;5、update语句时要注意后面的WHERE条件，特别是在子查询之外的WHERE条件。如：updatef03_billingsetspending_code=(selectspending_codefromf04_spendingwheremonth=f03_billing.monthandmobile_number=f03_billing.mobile_number)wheremonth=2000086、pdq的使用的误区（不是越大越好）有时在多个session已打开pdq的情况下，如果这时候有可能出现session等待7、表初始空间大小的估算CreateorreplacefunctionTab_init_size(I_column_lengthNumber,I_rowsNumber,I_pctfreeNumberdefault10)ReturnNumberIsO_size_MbInteger;M_column_avgConstantNumber(3,2):=0.85;M_block_sizeConstantNumber(8):=2*1024;M_block_headConstantNumber(8):=90;M_row_pre_blockNumber(10);--I_column_length:字符型的是其定义长度，数值型5为2Bytes,=5为1bytesBeginM_Row_pre_block:=trunc(((M_block_size-M_block_head)*((100-I_pctfree)/100))/I_column_length*M_column_avg)+1;O_size_mb:=Ceil(((I_rows/M_row_pre_block)*M_block_size)/(1024*1024));ReturnO_size_Mb;End;