1单行函数(单行函数只处理单个行,并且为每行返回一个结果)
1.1字符函数
◆CONCAT(x,y),将x和y拼接起来,并返回新字符串
◆INITCAP(x)将字符串转换为每个词首字母为大写,其他字母为小写
◆INSTR(str, find_string [, start] [, count])
返回指定字符串find_string在str中的位置。可以指定开始搜索的位置start,也可以提供该字符串出现的次数(第)count(次)。
start和count默认为1,表示从字符串开始的位置开始搜索,并返回第一次出现的位置
◆LENGTH(x) 返回表达式中的字符数
◆LOWER(x) ,UPPER(x) 转换大小写
◆LPAD(x, width [, pad_string]),RPAD(x, width [, pad_string]) 在字符串左/右侧填充pad_string字符,以使总字符宽度为width,默认填充空格,汉字占2位
◆LTRIM(x [, trim_string]),RTRIM(x [, trim_string]),TRIM(trim_string FROM x),从x字符串左/右侧/两端去除所有的trim_string字符串,默认去除空格
◆NVL(x, value) 将一个NULL值转换为另外一个值。如果x是NULL值的话返回value值,否则返回x值本身
◆NVL2(x, value1, value2) 如果x不为NULL值,返回value1,否则返回value2
◆REPLACE(x, search_string, replace_string) 从字符串x中搜索search_string字符串,并使用replace_string字符串替换
◆SUBSTR(x, start [, length]) 返回字符串中的指定的字符,
★从start个位置开始
★长度为length个字符
★如果length省略,则将返回一直到字符串末尾的所有字符
★如果start是负数,则从末尾开始算起
1.2数字函数
◆ABS(value) 返回value的绝对值
◆CEIL(value) 返回大于或等于value的最小整数 如: SELECT CEIL(5.8),CEIL(-5.2) FROM dual; 返回:6 和 -5
◆FLOOR(value) 返回小于或等于value的最大整数 如: SELECT FLOOR(5.8),FLOOR(-5.2) FROM dual; 返回:5 和 -6
◆POWER(value,n) 返回value的n次幂
◆MOD(m,n) 返回m和n取余数的结果
◆SQRT(value) 对value进行开平方
◆TRUNC(value,n) 对value进行截断
★ n>0,保留n位小数; TRUNC(5.75,1) ==> 5.7
★ n<0,则保留-n位整数位(小数点左边指定位数后面的部分截去,均以0记); TRUNC(5.75,-1) ==> 0 截整数部分,只保留整数,自右往左截-n位
★ n=0,则去掉小数部分 TRUNC(5.75,0) ==> 5
◆ROUND(value[,n]) 对value进行四舍五入,保存小数点右侧的n位。
★ n>0,保留n位小数;ROUND(5.75,1)==> 5.8
★ n<0,则保留-n位整数位(小数点左边指定位数后面的部分四舍五入); ROUND(5.75,-1) ==> 10
★ n=0,则去掉小数部分 ROUND(5.75)==> 5
如果n不填,则n默认为0.
1.3转换函数(将值从一种类型转换成另外一种类型,或者从一种格式转换为另外一种格式)
◆TO_CHAR(x [, format]) 将x转化为字符串。 format为转换的格式,可以为数字格式或日期格式
★数字格式 SELECT TO_CHAR(12345.67, '99,999.99') FROM dual;
☆ 9 确定宽度,前导补空,后导补0
☆ 0 确定宽度,前后导都补0
☆ $ 美元
☆ L 当地的货币符号
☆ . 小数点
☆ ,分隔符
★日期格式 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
☆ q 季度1-4
☆ ww 年里面的第几周
☆ 当前时间减去7分钟的时间 sysdate - interval '7' MINUTE
☆ 当前时间减去7小时的时间 sysdate - interval '7' hour
☆ 当前时间减去7天的时间 sysdate - interval '7' day
☆ 当前时间减去7月的时间 sysdate - interval '7' month
☆ 当前时间减去7年的时间 sysdate - interval '7' year
☆ 时间间隔乘以一个数字 sysdate - 8*interval '7' hour
◆TO_DATE(x [,format]) 将x字符串转换为日期
规则同TO_CHAR
☆ TO_DATE('2012-3-15','YYYY-MM-DD‘)
◆TO_NUMBER(x [, format]) 将x转换为数字。可以指定format格式
☆ TO_NUMBER('970.13') + 25.5
☆ TO_NUMBER('$12,345.67', '$99,999.99') , TO_NUMBER('¥12,345.67', 'L99,999.99')
☆ TO_NUMBER(to_char(sysdate, 'yyyy'))
◆CAST(x AS type) 将x转换为指定的兼容的数据库类型
☆ CAST(12345.67 AS VARCHAR2(10) )
☆ CAST('05-7月-07' AS DATE)
☆ CAST(12345.678 AS NUMBER(10,2))
2聚集函数(亦称分组函数、聚合函数。聚集函数可以对行集进行操作,并且为每组给出一个结果)
格式:select 组函数(表达式)/表达式 from 子句 group by 表达式
注意:非group by 中出现的表达式 如果要出现在select当中,必须使用组函数
★ 聚集函数可以使用任何有效的表达式
★ NULL值在聚集函数中将被忽略
★ 可以在聚集函数中使用DISTINCT关键字,排除重复值
★ 筛选条件如果使用了组函数,则应使用having筛选
◆AVG(x):返回x的平均值
◆COUNT(x):返回统计的行数
◆MAX(x):返回x的最大值
◆MIN(x):返回x的最小值
◆SUM(x):返回x的总计值
◆MEDIAN(x):返回中间值
◆STDDEV(x):返回标准偏差
◆VARIANCE(x):返回x的方差
聚集函数的分组应用,有时需要对表中的行进行分组,然后统计每组的信息: