- 论坛徽章:
- 0
|
- 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(' FF C','FF FF CCC',1) FROM CBB721
- 16. POSITION or POSSTR 返回源串在目标串出现的的一个位置
- EXAMPLE1:半角情况
- SELECT POSITION('D' IN 'FFCD') FROM CBB721
- EXAMPLE2:混合情况
- SELECT POSSTR('FFC DSF ',' F ') FROM CBB721
复制代码 |
|