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:混合情况
SELECT POSSTR('FFC ＤＳＦ ',' Ｆ ') FROM CBB721
