AS400SQL常用函数和数据类型.doc

AS400 SQL常用函数和数据类型 [CODE] 查几条记录： select * from tablename fetch first 5 rows only select length(fieldname) from tablename 查找字段内容的长度。 查看某个范围： select * from tablename where rrn(tablename)=4 and rrn(tablename)=20 字符转换为数字： Digit(character)1．Avg?????????????计算平均值 Example:?SELECT?AVG(PRSTAFF) FROM?PROJECT WHERE?DEPTNO?=D11 2．Count?????????????计算符合条件的纪录行数 Example1:?SELECT?COUNT(*) FROM?EMPLOYEE WHERE?SEX?=F Example2:?SELECT?COUNT(DISTINCT?WORKDEPT) FROM?EMPLOYEE WHERE?SEX=F 3．Max????????????返回一个数据集里的最大值 Example:?SELECT?MAX(SALARY)?/12 FROM?EMPLOYEE 4.min?????????????返回一个数据集里的最小值 Example:?SELECT?MIN(SALARY)?/12 FROM?EMPLOYEE 5．SUM?????????????求和 EXAMPLE:?SELECT?SUM(BONUS) FROM?EMPLOYEE WHERE?JOB?=CLERK 6.?ABS?or?ABSVAL??????????计算绝对值 EXAMPLE:?SELECT?ABS(BONUS) FROM?EMPLOYEE WHERE?JOB?=CLERK 7.?CEILING?????????????????小数位进位 EXAMPLE:?SELECT?CEILING(?3.5), CEILING(?3.1), CEILING(-3.1), CEILING(-3.5), FROM?TABLEX This?example?returns: 4.0?4.0?-3.0?-3.0 8.CONCAT??????????????????????????字符串连结 EXAMPLE:SELECT?CONCAT(FIRSTNME,?LASTNAME)?AS?FULLNAME FROM?AUTHOR? 11.substring?or?substr????字符串截取 Example: SELECT?*?FROM?PROJECT WHERE?SUBSTR(PROJNAME,1,10)?=OPERATION? 挑选字段PROJNAME前十位等于常量OPERATION?的所有记录 12．Round??????????四舍五入 Example1: SELECT?ROUND(873.726,?2), ROUND(873.726,?1), ROUND(873.726,?0), ROUND(873.726,?-1), ROUND(873.726,?-2), ROUND(873.726,?-3), ROUND(873.726,?-4) FROM?TABLEX 结果： 0873.730?0873.700?0874.000?0870.000?0900.000?1000.000?0000.000 Example2: SELECT?ROUND(?3.5,?0), ROUND(?3.1,?0), ROUND(-3.1,?0), ROUND(-3.5,?0) FROM?TABLEX 结果： 4.0?3.0?-3.0?-4.0 13．UCASE?or?UPPER????????转换为大写字符串 UCASE(abcdef)??结果为’ABCDEF’ 14.?LOWER?or?LCASE???????转换为小写字符串 lower(aBcDef)??结果为’abcdef 15.locate????????????????????????????????返回源串在目标串出现的的一个位置 EXAMPLE SELECT?LOCATE(?ＦＦ?C,FF?ＦＦ?CCC,1)?FROM?CBB721? 16.?POSITION?or?POSSTR????返回源串在目标串出现的的一个位置 EXAMPLE1:半角情况 SELECT?POSITION(D?IN?FFCD)?FROM?CBB721 EXAMPLE2:混合