oracle 高级查询（一）

聚合函数：

select max(stu_score),min(stu_id),avg(stu_score),sum(stu_score),count(stu_id) from student;

转换为字符：

select to_char(sysdate,'yyyy-mm-dd') from dual;

转换为日期：

 select to_date('2017-08-20','yyyy-mm-dd') from dual;

日期相减:

select to_date('2017-08-20','yyyy-mm-dd')-sysdate from dual;

查询指定字符在字符串中的下标

select instr('this is a str','i') from dual;

截取字符串从指定下标开始

select substr('this is a str',6) from dual;

截取字符串从指定下标取对应个数

select substr('this is a str',5,2) from dual;

转换为数字：

select to_number('1021') from dual;

从左开始填充，填充位数为6-字符串位数，填充内容为第三个参数

select lpad('aa',6,'-') from dual;

查询两个日期间的月数

 select months_between(sysdate,to_date('2017-01-01','yyyy-mm-dd')) from dual;

向下取整

select floor(20.5) from dual;

向上取整

select ceil(20.5) from dual;

四舍五入

select round(20.5) from dual;

取指定位数

select trunc(100.5555,1) from dual;

生成随机数并取整

select floor(dbms_random.value(100,1000)) from dual;

生成0-1间的随机数

select dbms_random.value from dual;

替换字符串中的字符

 update card set id=replace(id,'i','1');

替换字符，嵌套写法

update card set id=replace(replace(id,'d','kk'),'f','dd');

给12-13,13-15,15-14...........排序

select listnumber from student
 order by
 o_number(substr(listnumber,1,instr(listnumber,'-')-1)),
 to_number(substr(listnumber,instr(listnumber,'-')+1));