oracle常用函数总结

--oracle常用函数

select abs(-1) from dual; --绝对值
select mod(10,3) from dual; --取余数
select sign(-66) from dual; -- 正数返回1； 负数返回-1； 0返回0
select ceil(1.32) from dual; --向上取整   2
select floor(1.99) from dual;  --向下取整       
select trunc(1.2399,3.9) from dual; -- 先对3.9向下取整为3，再截取小数点后3位
select round(1.2395,3.9) from dual; --先对3.9向下取整为3，再取小数点后3位四舍五入
select chr(11) from dual;           --将ASCII码转化为字符
select ascii('订') from dual;       --将字符转化为ASCII码
select length('aaaaa') from dual;  --判断字符串的长度 5
select substr('abcdefghijk',2,5) from dual;   --截取字符串从第2位开始，截取5位 bcdef
select instr('abcdabcd','a',2,1) from dual;   --判断字符a在字符串中第2位开始第1次出现的位置5
select instr('abcdabcd','w') from dual; --用来判断字符串中是否存在字符a 存在返回值大于0
select upper('acd') from dual;    --大写转换
select lower('ADcD') from dual;   --小写转换
select initcap('you are dog') from dual;--字符首字符大写

--聚合函数
select avg(dt.djl_age) from djl_test dt;
select max(dt.djl_age) from djl_test dt;
select min(dt.djl_age) from djl_test dt;
select count(dt.djl_age) from djl_test dt;
select sum(dt.djl_age) from djl_test dt;
select * from djl_test for update;
--group by having
--先筛选出年龄大于10的数据，然后再按名次和id分组，分组后展示id需大于4的数据
select max(dt.djl_age), dt.djl_name
  from djl_test dt
  where dt.djl_age > 10
 group by dt.djl_name,dt.djl_id
having dt.djl_id >4;


--聚合函数嵌套  分组查询出最大的年龄后再删选最大的年龄
select max(max(dt.djl_age))
  from djl_test dt
  where dt.djl_age > 10
 group by dt.djl_name,dt.djl_id
having dt.djl_id >4;

--通过rownum 进行分页
select dt.*
  from (select t.*, rownum rn from djl_test t) dt
 where dt.rn between 1 and 3;
--通过rowid 对重复的djl_id 进行除重
delete from djl_test
 where rowid not in (select max(rowid) from djl_test group by djl_id)
 
--ROW_NUMBER() OVER (ORDER BY COLUMN1) 的使用
--先将数据进行排序，然后再取ROWNUM
select dt.*
  from (select t.*, ROW_NUMBER() OVER(ORDER BY djl_age desc) rn from djl_test t) dt
 where dt.rn between 1 and 3;


--ROW_NUMBER() OVER (PARTITION BY COLUMN1 OREDER BY COLUMN2)
--先将数据进行分组，再进行排序，然后再取ROWNUM，每组数据都是ROWNUM 从1开始
select t.*,
       row_number() over(partition by djl_name order by djl_age desc) rn
  from djl_test t   
 
--随机函数0 - 1.0之间的随机数
SELECT DBMS_RANDOM.value FROM DUAL;
 
--TRUNC(DBMS_RANDOM.VALUE(A,B)) 随机产生A-B之间的值
SELECT TRUNC(DBMS_RANDOM.VALUE(1，100)) FROM DUAL;

--查询表中奇数列
select td.*
  from (select t.*, row_number() over(order by djl_age) rn from djl_test t) td
 where mod(td.rn, 2) = 1

--聚合函数 over() 的使用  查询出最大的年龄和最小的年龄
select djl_name,djl_age,max(djl_age) over(), min(djl_age) over() from djl_test;

--连续求和 over
select sum(djl_age) over(order by djl_id) from djl_test;