SQL functions and paging in Oracle

SELECT Lpad (' Page 1 ', 15, ' *. ') "Lpad example" from  DUAL;

1. Paging Query

(1) method one: use between and to achieve paging

SELECT * FROM (  select Emp.*,rownum rn from EMP) where RN between 4 and 6

(2) Method two: Using RowNum to achieve paging

SELECT * FROM (  select Emp.*,rownum rn from EMP  where rownum<=6) where rn>=4

(3) method three: Using rownum three layer to achieve paging

SELECT * FROM (  select Emp.*,rownum rn  from  (    select * from emp  ) EMP  where rownum<=6) where RN >=4

The results are as follows:

2. Analysis functions

(1) Rank (): the rank () function returns a unique value, and when the same data is encountered, all the same data is ranked the same,

The rankings are also vacated between the last same record and the next different record.

Select Ename,deptno,sal,rank () over (partition by deptno ORDER BY sal Desc) ' Rank '--dense_rank () over (partition by Deptno O Rder by sal Desc) ' Dense_rank '--row_number () over (partition by deptno ORDER BY sal Desc) ' Row_number ' from emp

Results:

(2) Dense_rank ():d the Ense_rank () function returns a unique value, and when the same data is encountered, all the same data is ranked the same.

Select Ename,deptno,sal,--rank () over (partition by deptno ORDER BY sal Desc) "Rank" Dense_rank () over (partition by Deptno O Rder by sal Desc) ' Dense_rank '--row_number () over (partition by deptno ORDER BY sal Desc) ' Row_number ' from emp

(3) Row_number (): the Row_number () function returns a unique value that, when the same data is encountered, is incremented sequentially in the order in which the records are recorded in the recordset.

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) Result of merger:

Select Ename,deptno,sal,rank () over (partition by deptno ORDER BY sal Desc) "Rank", Dense_rank () over (partition by DEPTNO or Der by sal Desc) "Dense_rank", Row_number ()-Over (partition by deptno ORDER BY sal Desc) "Row_number" from emp

3.SQL function

(1) Character function

1. Casing control function

Lower (): all lowercase

Select LOWER (' MR SCOTT MCMILLAN ') "lowercase" from  DUAL;

Upper (): All caps

Select UPPER (' abc ') "Uppercase" from   dual;

Initcap (): Capitalize first letter

Select Initcap (' MR SCOTT MCMILLAN ') "Initcap" from  DUAL;

2. Character control functions

Concat: Stitching Strings

Select Concat (' Happy ', ' Boy ') from dual;

SUBSTR: Intercepting strings

substr (' String to intercept ', starting position) Description: Position starting from 1

Select substr (' Happyboy ', 4) from dual;

substr (' character to intercept, start position, take a few characters ')

Select substr (' Happyboy ', 2,3) from dual;

Length (' string '): Number of characters counted
LENGTHB (' string '): Number of bytes counted

Select Length (' Happy ') number of characters, LENGTHB (' happy ') as bytes from dual;

InStr (' Large string ', ' small string ') returns the position of a small string in a large string

Select INSTR (' CORPORATE floor ', ' OR ', 3, 2) "instring" from  DUAL;

Lpad () and Rpad ()

SELECT Lpad (' Page 1 ', 15, ' *. ') "Lpad example" from  DUAL;

Select Ename, Rpad (", SAL/1000/1, ' * ')" Salary "from the   emp   WHERE deptno=20   ORDER by ename," Salary ";

Trim ()

Select Trim (' A ' from ' Ahappy ') from dual;

(2) Date function

1. Date function

Months_between () Number of months between two dates

Select Months_between (to_date (' 02-02-1995 ', ' mm-dd-yyyy '), to_date (' 01-01-1995 ', ' mm-dd-yyyy ') "MONTHS"  

Add_months (): Add a number of months to the specified date

Select To_char (Add_months (hiredate, 1), ' dd-mon-yyyy ') "Next month" from  emp   WHERE ename = ' JONES ';

2. Subtraction of dates

Two days of difference between dates

Select Floor (sysdate-to_date (' 2015-12-12 ', ' Yyyy-mm-dd ')) from dual;

Two months of difference between dates

Select Months_between (sysdate,to_date (' 20151212 ', ' YyyyMMdd ')) from dual;

(3) Conversion function
1. Implicit conversions

SELECT * from emp where hiredate= ' 1 July-December-80 ';

2. Explicit conversions

To_char () Conversion of dates

Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual;

To_char () Conversion of numbers

Select To_char (Sal, ' L9999.99 ') from EMP;

(4) Number function

1.round (): Rounding

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) General functions

1.NVL () Filter empty function

SELECT sal*12 Salary, comm Bonus, SAL*12+NVL (comm,0) from EMP;

2.NVL2 () Filter empty function

SELECT sal*12 Salary, comm Bonus, SAL*12+NVL2 (comm,comm,0) from EMP;

3. Do not filter empty function

SELECT sal*12 Salary, comm Bonus, sal*12 from EMP;

(6) Decode function

SELECT product_id,       
1, ' Southlake ',                              2, ' San Francisco ',                              3, ' New Jersey ',                             4, ' Seattle ',                                ' Non domestic ')   from Inventories  WHERE product_id < 1775  ORDER by product_id, "location";

SQL functions and paging in Oracle