四、Oracle数据类型及函数
1. 字符串类型及函数
三种字符串类型
- char(n)：固定长度字符串，假如长度不足n，右边空格补齐
- varchar(n): 可变长度字符串，假如长度不足n，右边不会补齐
- varchar2(n): 可变长度字符串，Oracle官方推荐使用，向后兼容性好
char(n) VS varchar2(n) : char(n)查询效率相对较高，varchar2(n)存储空间相对较小
向左补齐全字符串：lpad()
select lpad(stuno,6,'0') from user;
向右补齐全字符串：rpad()
select rpad(stuno,6,'0') from user;
返回字符串小写：lower()
select lower(username) from user;
返回字符串大写：upper()
select upper(username) from user;
返回首字符大写：initcap()
select initcap(username) from user;
返回字符串长度：length()
select length(username) from user;
截取字符串：substr() 索引从1开始
select substr(username,1,2) from user;
获取字符串出现的位置：instr()2表示第几个位置开始查 最后一个参数表示出现第几次
select instr(password,'23',2,2)from user;
删除左侧空格：ltrim()
select ltrim(username) from user;
删除右侧空格：rtrim()
select rtrim(username) from user;
删除两侧空格：trim()
select trim(username) from user;
串联字符串：concat()
select concat(username,password) from user;
反转字符串：reverse()
select reverse(username) from user;
2. 数值类型及函数
Number(precision,scale)
Precision 代表精度 范围【1，38】
scale 代表小数位的位数， 范围【-84，127】
求绝对值：abs()
select abs(n1) from number where id = 1
四舍五入：round()
select round(n1,2) from number where id = 1
向上取整：ceil()
select ceil(n1) from number where id = 1
向下取整:floor()
select floor(n1) from number where id = 1
取模：mod()
select mod(5,3) from dual
正负性：sign()
select sign(n1) from number where id = 1
求平方根：sqrt()
select sqrt(9) from dual
求乘方：power()
select mod(2,3) from dual
截取：trunc()
select mod(123.456,3) from dual
格式化数值：to_char() 常见的字符匹配有0、9、，、$、FM、L、C
select to_char(123.45,'0000.000') from dual 0123.450
select to_char(123.45,'9999.999') from dual 123.450 整数位不用自动补0
select to_char(123123,'99,999,999.99') from dual 123,123.00
select to_char(123123.3,'FM99,999,999.99') from dual 123,123.3 FM可以去前面的空格
select to_char(123123.3,'$99,999,999.99') from dual $123,123.30
select to_char(123123.3,'L99,999,999.99') from dual ¥123，123.30
select to_char(123123.3,'99,999,999.99C') from dual 123，123.30CNY
3. 日期类型及函数
date包含信息：century（世纪）、year、month、day、hour、minute、second
timestamp（时间戳）一般用于日期时间要求非常精准的情况下，精确到毫秒级
//获取当前日期和时间戳
insert into t_date values(1,sysdate,systimestamp)
select sysdate from dual
select systimestamp from dual
添加月份：add_months
select add_months(d1,2) from t_date where id = 1;
返回指定日期月份最后一天：last_day
select last_day(d1) from t_date where id = 1;
返回两个日期的相差月数：months_between
select months_between(d1,d3) from t_date where id = 1;
返回特定日期之后的一周内的日期：next——day
select next_day(d1,2) from t_date where id = 1;
截取日期：trunc
select trunc(d1,'YYYY') from t_date where id = 1;
select trunc(d1,'MM') from t_date where id = 1;
select trunc(d1,'DD') from t_date where id = 1;
select trunc(d1,'HH') from t_date where id = 1;
返回日期的某个域：extract()
select extract(year from syadate) from dual;
select extract(Hour from systimestamp) from dual;
转化成字符串：to_char()
select to_char(d1,'YYYY-MM-DD') from t_date where id = 1
4. 其他处理函数
常用的聚合函数：
求最大值：max
select max(sal) from emp
求最小值：min
select min(sal) from emp
求平均值：avg
select avg(sal) from emp
求和：sum
select sum(sal) from emp
统计记录数：count
select count(sal) from emp
空值处理：nvl
select ename.nvl(sal,0) from emp
分页:rownum
select * from(select a.* ,rownum rn from (select * from emp) a where rownum <= 10) where m>5
oracle中的运算：
select 2+1 from dual;
条件判断式：
between and 范围
in 包含
like 模糊查询
