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
curtime
返回系统时间
curdate 返回系统日期
date
返回一个日期型值
example: date(‘1980-12-07’)
day
返回参数的日期值部分
example:date1等于2000-03-15
date2等于1999-12-31
day
(date1-date2)
结果15
dayofmonth
返回参数日期部分
amc等于’2006-01-01’
dayofmonth(amc)
结果
1
dayofweek
返回星期值1~7，1-星期日；7-星期六
amc等于’2006-01-01’
dayofmonth(amc)
结果
1
dayofyear
返回值1~366
amc等于’2006-02-01’
dayofmonth(amc)
结果
32
days
返回参数日期与’0001-01-01’的天数差
example:
amc1等于’2006-02-01’
amc2等于’2006-01-01’
days(amc1)-days(amc2)
结果
31
hour
返回参数小时部分，参数为时间或时间戳类型