Oracle 中的sql函数以及分页

gtxyzz 批处理 2023-02-27 548浏览 0

SELECT LPAD('Page 1',15,'*.') "LPAD example"  FROM DUAL;

1.分页查询

（1）方法一：使用 between and 来实现分页

select * from 
(
  select emp.*,rownum rn from emp
)
where rn between 4 and 6

（2）方法二：使用 rownum 来实现分页

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

（3）方法三：使用rownum 三层来实现分页

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

得到的结果如下：

Oracle 中的sql函数以及分页

2.分析函数

（1）rank() :rank()函数返回一个唯一的值，当遇到相同的数据时，所有相同数据的排名是一样的，

同时会在最后一条相同记录和下一条不同记录之间空出排名。

select ename,deptno,sal,
rank() over(partition by deptno order by sal desc) "RANK"
--dense_rank() over(partition by deptno order by sal desc)"dense_rank"
--row_number() over(partition by deptno order by sal desc)"row_number"
from emp

结果：

Oracle 中的sql函数以及分页

（2）dense_rank() :dense_rank()函数返回一个唯一的值，当遇到相同数据时，所有相同的数据的排名都是一样的。

select ename,deptno,sal,
--rank() over(partition by deptno order by sal desc) "RANK"
dense_rank() over(partition by deptno order by sal desc)"dense_rank"
--row_number() over(partition by deptno order by sal desc)"row_number"
from emp

Oracle 中的sql函数以及分页

（3）row_number():row_number()函数返回一个唯一的值，当遇到相同数据时，排名按照记录集中记录的顺序依次递增。

select ename,deptno,sal,
--rank() over(partition by deptno order by sal desc) "RANK"
--dense_rank() over(partition by deptno order by sal desc)"dense_rank"
row_number() over(partition by deptno order by sal desc)"row_number"
from emp

Oracle 中的sql函数以及分页

（4）合并结果：

select ename,deptno,sal,
rank() over(partition by deptno order by sal desc) "RANK",
dense_rank() over(partition by deptno order by sal desc)"dense_rank",
row_number() over(partition by deptno order by sal desc)"row_number"
from emp

Oracle 中的sql函数以及分页

3.SQL函数

（1）字符函数

1.大小写控制函数

lower():全部小写

SELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase"  FROM DUAL;

Oracle 中的sql函数以及分页

upper()：全部大写

SELECT UPPER('abc') "Uppercase"   FROM dual;

Oracle 中的sql函数以及分页

initcap()：首字母大写

SELECT initcap('MR. SCOTT MCMILLAN') "initcap"  FROM DUAL;

Oracle 中的sql函数以及分页

2.字符控制函数

concat:拼接字符串

select concat('Happy','Boy') from dual;

Oracle 中的sql函数以及分页

substr:截取字符串

substr(‘要截取的字符串’,起始位置) 说明：位置从1开始

select substr('HappyBoy',4) from dual;

Oracle 中的sql函数以及分页

substr(‘要截取的字符,起始位置,取几个字符)

select substr('HappyBoy',2,3) from dual;

Oracle 中的sql函数以及分页

length(‘字符串’)：字符个数统计
lengthb(‘字符串’)：字节个数统计

select length('快乐') 字符数，lengthb('快乐') as 字节数 from dual;

Oracle 中的sql函数以及分页

instr(‘大字符串’,’小字符串’)返回小字符串在大字符串中出现的位置

SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) "Instring"  FROM DUAL;

Oracle 中的sql函数以及分页

lpad()和rpad()

SELECT LPAD('Page 1',15,'*.') "LPAD example"  FROM DUAL;

Oracle 中的sql函数以及分页

SELECT ename, RPAD(' ', sal/1000/1, '*') "Salary"   FROM emp   WHERE deptno=20   ORDER BY ename, "Salary";

Oracle 中的sql函数以及分页

trim()

select trim('a' from 'ahappy') from dual;

Oracle 中的sql函数以及分页

（2）日期函数

1.日期函数

months_between()两个日期相差的月数

SELECT MONTHS_BETWEEN (TO_DATE('02-02-1995','MM-DD-YYYY'),TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"  FROM DUAL;

Oracle 中的sql函数以及分页

add_months():向指定日期中加上若干月数
Oracle 中的sql函数以及分页

SELECT TO_CHAR(ADD_MONTHS(hiredate, 1), 'DD-MON-YYYY') "Next month"  FROM emp   WHERE ename = 'JONES';

Oracle 中的sql函数以及分页

2.日期相减

两个日期相差的天数

select floor(sysdate-to_date('2015-12-12','yyyy-MM-dd')) from dual;

Oracle 中的sql函数以及分页

两个日期相差的月数

select months_between(sysdate,to_date('20151212','yyyyMMdd')) from dual;

Oracle 中的sql函数以及分页

（3）转换函数
1.隐式转换

select * from emp where hiredate='17-12月-80';

Oracle 中的sql函数以及分页

2.显式转换

Oracle 中的sql函数以及分页

to_char()对日期的转换

select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') from dual;

Oracle 中的sql函数以及分页

to_char()对数字的转换

Oracle 中的sql函数以及分页

select to_char(sal,'L9999.99') from emp;

Oracle 中的sql函数以及分页

（4）数字函数

1.round():四舍五入

select round(12.45,1) from dual;

Oracle 中的sql函数以及分页

2.trunc

SELECT TRUNC(TO_DATE('2015-12-12','yyyy-MM-dd'), 'YEAR')  "New Year" FROM DUAL;

select trunc(15.71,1) "trunc" from dual;

Oracle 中的sql函数以及分页

（5）通用函数

1.nvl()滤空函数

SELECT SAL*12 工资，comm 奖金，sal*12+nvl(comm,0) from emp;

Oracle 中的sql函数以及分页

2.nvl2()滤空函数

SELECT SAL*12 工资，comm 奖金，sal*12+nvl2(comm,comm,0) from emp;

Oracle 中的sql函数以及分页

3.不用滤空函数

SELECT SAL*12 工资，comm 奖金，sal*12 from emp;

Oracle 中的sql函数以及分页

（6）decode函数

SELECT product_id,       
DECODE (warehouse_id, 
1, 'Southlake',                              
2, 'San Francisco',                              
3, 'New Jersey',                             
4, 'Seattle',                                
'Non domestic') "Location"   FROM inventories 
 WHERE product_id < 1775  ORDER BY product_id, "Location";