Oracle函数（常见的20种）+三层嵌套分页...等多种Oracle的sql(常用)

-----20种函数

--1.返回字符串,并将所有的字符小写

select lower('AaBbCcDd')AaBbCcDd from dual;

--2.返回字符串,并将所有的字符大写
select upper('AaBbCcDd') upper from dual;

--3.返回指定值的绝对值
select abs(100),abs(-100) from dual;
--4.四舍五入
select round('1.32') from dual;
--5.日期转换 之间的月份
select months_between(to_date('2017-1-7','yyyy-MM-dd'),to_date('1999-1-7','yyyy-MM-dd')) from dual;
--6.给某个日期增加N个月
select add_months(sysdate,1) from dual;
--7.开方根
select sqrt(9) from dual;
--8.截取  从第一个截， 截3个
select substr('过渡函数客房',1,3) from dual;

--9.第三个字母开始，找到第三个or算出位置
select instr('corporate floor','or',3,2) "Instring" from dual;
--10.数字转换特定格式，价格
select to_char(123.223,'L999,99') from dual;
--11，拼接  不同
select '1.23'||2 from dual;
select '1.23'+'2' from dual;
--12.拼接
select to_number('1.23')+2 from dual;
--13
select sal*12工资,comm 奖金,sal*12+nvl2(comm,1,0) from emp;

---14,返回日期列表中最晚日期
select greatest('01-1月-04','04-1月-04','10-2月-04') from dual
---15.去掉字符串中的空格
   select ltrim(' abc') s1,
    rtrim('zhang ') s2,
    trim(' zhang ') s3 from dual;
--16.计算字符串长度 
   select length('abcdef') from dual
--17.返回登录的用户名称 
    select user from dual;
-- 18.返回表达式所需的字节数
    select vsize('HELLO') from dual;
--19.聚合函数
    select deptno,sum(sal),avg(sal),count(0),max(sal),min(sal)
from emp group by deptno
--20.分析函数--一般是排名用的
select ename,deptno,
rank() over(partition by deptno order by sal) rank,
dense_rank() over(partition by deptno order by sal) dense_rank,
row_number() over(partition by deptno order by sal) row_number
from emp

Oracle函数：

create table userinfo2
(id number primary key not null)

select * from userinfo2

-----日期转换
select months_between(to_date('2017-1-7','yyyy-MM-dd'),to_date('1999-1-7','yyyy-MM-dd')) from dual;
--给某个日期增加N个月
select add_months(sysdate,1) from dual;
--四舍五入
select round('1.23') from dual;
---------------------------------------------------------------
-----数字函数
--1.取绝对值
select abs(-3) from dual;
--2.开方根
select sqrt(9) from dual;
-----------------------------------
select Initcap('abc') from dual;


select substr('过渡函数客房',1,3) from dual;

select instr('corporate floor','or',3,2) "Instring"
from dual;

---------------------------
-----to_char()
--数字转换特定格式，例如京东商品的价格
select to_char(123.223,'L999,99') from dual;
--日期转换
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
--number 
select '1.23'||2 from dual;
select '1.23'+'2' from dual;
select to_number('1.23')+2 from dual;
---------------------------------------------------------------------------
select sal*12工资,comm 奖金,sal*12+nvl2(comm,1,0) from emp;

select sal*12工资,comm 奖金,sal*12+nvl2(comm,comm,0) from emp;
select sal*12工资,comm 奖金,sal*12+nvl(comm,0) from emp;
select sal*12工资,comm 奖金,sal*12+comm from emp;
-------------------------------------------------------------
select emp.*,rowid,rownum from emp

select emp.*,rownum from emp
where rownum<7
-----查询第三条记录
select * from
(
select emp.*,rownum as rn from emp
) 
where rn=3
---------------------------------

---emp中第二页的数据  4-6条数据

select * from
（
select emp.*,rownum rn from emp
where rownum<=6
）
where rn>=4

--三层嵌套查询 加上倒序 sal

select * from
（
   select emp.*,rownum rn from 
    （
       select * from emp order by sal desc
     ）emp
   where rownum<=6
）
where rn>=4




------三层嵌套查询 分页




select * from
(
select emp.*,rownum rn from
 (
select * from emp
) emp
where rownum<=6
) 
where rn>=4

================================================================================================================
select * from emp
----------四舍五入
select round(sal,1) from emp
-----字符函数  substr(ename,1)截取全部
select substr(ename,1,2),
substr(ename,0,2),
substr(ename,1)，length(trim(ename))
 from emp
----------------------------------
select sysdate+1/4 from dual
select to_number('112'),to_char(1122.2345,'9999.99'),
to_date('1986-12-12','yyyy-MM-dd'),
to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from dual
----------------------------
select * from emp 

select t.sal,t.comm,
(t.sal+nvl(t .comm,0)) gz, 
(t.sal+nvl2(t .comm,comm+1000,0)) gz2,
decode(t .comm,null,0,300,900,t.comm) gz3
from emp t
--------------------------聚合函数
select deptno,sum(sal),avg(sal),count(0),max(sal),min(sal)
from emp group by deptno
--查看部员工超过10人以上的数据
select deptno ,sum(sal),avg(sal),count(0),max(sal),min(sal)
from emp group by deptno having count(*)>3 --and sal>1000
--having只可以是聚合函数
----------------------------------------------
----------------分析函数：做排名用的
--row_number 
select ename,deptno,
rank() over(partition by deptno order by sal) rank,
dense_rank() over(partition by deptno order by sal) dense_rank,
row_number() over(partition by deptno order by sal) row_number
from emp
-------------------------
----联合查询 union ,union all, intersect ,minus
select rowid,rownum,emp.* from emp
-----x
select rownum,ename from emp 
where rownum>=5
and rownum<=10
--分页
select ename,rn from
(select rownum as rn,ename from emp
where rownum<=10) t 
where t.rn>=5
 
select * from emp

select empno,rownum from emp order by empno
 

======================================================================================
select * from student 

select DISTINCT stuname from student


 -------------------------
 select * from student
 insert into student(stuno,stuname,stuage) values(6,'妞妞'，32)
 --------删除重复记录 中 旧的记录
delete from student  where rowid not in
(
select max(rowid) from student
group by stuname,stuage
having(count(*)=1)

union

select max(rowid) from student
group by stuname,stuage
having(count(*)>1)
)
================================================================================
---------------查询第三条数据
select * from
(
select emp.*,rownum as rn from emp
) 
where rn=3
---
select * from 
(
select ename,rownum rn from emp where rownum<=3
) temp
where temp.rn=3
-------------------
select * from emp order by sal desc

-----------------
------薪资排名第五的人
select * from
(
select rownum rn, sal from 
(
select * from emp order by sal desc 
)
)
 where rn=5
 ------------排名改分页
 select * from
(
select rownum rn, sal from 
(
select * from emp order by sal desc 
)
where rownum<=10
)
 where rn>=6