Oracle 中的sql函數以及分頁

標籤：src   ati   order   pad   大小   .sql   順序   大小寫   隱式   

 

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

得到的結果如下：

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

結果：

  （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

  （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

 （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

3.SQL函數

  （1）字元函數

        1.大小寫控制函數

            lower():全部小寫

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

             upper()：全部大寫

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

            initcap()：首字母大寫

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

     2.字元控制函數

       concat:拼接字串

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

        substr:截取字串   

        substr(‘要截取的字串‘,起始位置)   說明：位置從1開始
          

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

         substr(‘要截取的字元,起始位置,取幾個字元)

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

          length(‘字串‘)：字元個數統計
        lengthb(‘字串‘)：位元組個數統計

select length(‘快樂‘) 字元數，lengthb(‘快樂‘) as 位元組數 from dual;

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

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

       lpad()和rpad()

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

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

       trim()
     

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

 （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; 

        add_months():向指定日期中加上若干月數

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

       2.日期相減

   兩個日期相差的天數

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

兩個日期相差的月數

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

（3）轉換函式
       1.隱式轉換

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

   

      2.顯式轉換

       to_char()對日期的轉換

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

to_char()對數位轉換

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

（4）數字函數

       1.round():四捨五入

select round(12.45,1) from dual;

       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;

（5）通用函數

       1.nvl()濾空函數

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

         2.nvl2()濾空函數

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

      3.不用濾空函數

SELECT SAL*12 工資，comm 獎金，sal*12 from emp;

（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";

 

 

 

 

 

 

 

 

 

 

 

 

        

 

Oracle 中的sql函數以及分頁