[Reprinted] common Oracle Functions

Sysdate indicates that the system date dual is a virtual table.

　　Date Functions[Focus on the first four date functions]

1. add_months [returns the date after the specified month plus (minus) (Before)]

Select sysdate S1, add_months (sysdate, 10) S2,

Add_months (sysdate, 5) S3 from dual;

2. last_day [returns the date of the last day of the month]

Select last_day (sysdate) from dual;

3. months_between [returns the number of months between dates]

Select sysdate S1, months_between ('1-January 1, April-04 ', sysdate) S2,

Months_between ('1-April-04 ', '1-February-04') S3 from dual

4. next_day (D, day): returns the date of the next week. Day is 1-7 or Sunday-Saturday. 1 indicates Sunday.

Select sysdate S1, next_day (sysdate, 1) S2,

Next_day (sysdate, 'sunday') S3 from dual

5. Round [round to the nearest date] (Day: round to the nearest Sunday)

Select sysdate S1,

Round (sysdate) S2,

Round (sysdate, 'Year') Year,

Round (sysdate, 'month') month,

Round (sysdate, 'day') day from dual

6. trunc [truncation to the closest date]

Select sysdate S1,

Trunc (sysdate) S2,

Trunc (sysdate, 'Year') Year,

Trunc (sysdate, 'month') month,

Trunc (sysdate, 'day') day from dual

7. Return the latest date in the date list

Select greatest ('01-January-04 ', '04-January-04', '10-February-04 ') from dual

　　Character Functions(Can be used for literal characters or database columns)

1. String Truncation

Select substr ('abcdef', 1, 3) from dual

2. Locate the substring

Select instr ('abcfdgfdhd', 'fd ') from dual

3. String connection

Select 'hello' | 'Hello world' from dual;

4. 1) Remove spaces from the string

Select ltrim ('abc') S1,

Rtrim ('zhang ') S2,

Trim ('zhang ') S3 from dual

2) Remove the leading and suffix

Select trim (Leading 9 from 9998767999) S1,

Trim (trailing 9 from 9998767999) S2,

Trim (9 from 9998767999) S3 from dual;

5. returns the ASCII value of the first letter of the string.

Select ASCII ('A') from dual

6. Return the letter corresponding to the ASCII Value

Select CHR (97) from dual

7. Calculate the string length

Select length ('abcdef ') from dual

8. initcap (uppercase for the first letter), lower (smaller write), and upper (greater capital)

Select lower ('abc') S1,

Upper ('def ') S2,

Initcap ('efg') S3 from dual;

9. Replace

Select Replace ('abc', 'B', 'xy') from dual;

10. Translate

Select translate ('abc', 'B', 'xx') from dual; -- X is 1 bit

11. lpad [left filling] rpad [Right filling] (used to control the output format)

Select lpad ('func', 15, '=') S1, rpad ('func', 15, '-') S2 from dual;

Select lpad (dname, 14, '=') from Dept;

12. Decode [Implement the IF... then logic]

Select deptno, decode (deptno, 10, '1', 20, '2', 30, '3', 'others') from Dept;

　　Numeric Functions

1. Take the integer function (Ceil rounded up and floor rounded down)

Select Ceil (66.6) N1, floor (66.6) N2 from dual;

2. Power and square root (SQRT)

Select power (3, 2) N1, SQRT (9) N2 from dual;

3. Remainder

Select Mod (9,5) from dual;

4. Return a fixed number of decimal places (round: rounding, trunc: truncation)

Select round (66.667, 2) N1, trunc (66.667, 2) N2 from dual;

5. symbol of the return value (positive number is returned as 1, negative number is-1)

Select sign (-32), sign (293) from dual; Conversion Function

1. to_char () [convert the date and number types to character types]

1) Select to_char (sysdate) S1,

To_char (sysdate, 'yyyy-mm-dd') S2,

To_char (sysdate, 'yyyy') S3,

To_char (sysdate, 'yyyy-mm-dd hh12: MI: ss') S4,

To_char (sysdate, 'hh24: MI: ss') S5,

To_char (sysdate, 'day') S6 from dual;

2) Select Sal, to_char (SAL, '$99999') N1, to_char (SAL, '$99,999') N2 from EMP

2. to_date () [convert the character type to the date type]

Insert into EMP (empno, hiredate) values (8000, to_date ('2017-10-10 ', 'yyyy-mm-dd '));

3. to_number () to numeric type

Select to_number (to_char (sysdate, 'hh12') from dual; // number of hours displayed

　　Other functions

User:

Return the Login User Name

Select User from dual;

Vsize:

Returns the number of bytes required by the expression.

Select vsize ('hello') from dual;

Nvl (ex1, ex2 ):

If the ex1 value is null, ex2 is returned. Otherwise, ex1 (commonly used) is returned)

For example, if an employee has no commission, 0 is displayed; otherwise, the Commission is displayed.

Select comm, nvl (Comm, 0) from EMP;

Nullif (ex1, ex2 ):

Returns NULL if the value is equal. Otherwise, the first value is returned.

For example, if the salary is equal to the Commission, it is blank; otherwise, the salary is displayed.

Select nullif (SAL, comm), Sal, comm from EMP;

Coalesce:

Returns the first non-empty expression in the list.

Select comm, Sal, coalesce (Comm, Sal, Sal * 10) from EMP;

Nvl2 (ex1, ex2, EX3 ):

If ex1 is not empty, ex2 is displayed; otherwise, EX3 is displayed.

For example, check the name of an employee with a Commission and their commission.

Select nvl2 (Comm, ename, ') as havecommname, comm from EMP;

　　Grouping Functions

Max min AVG count sum

1. The entire result set is a group.

1) obtain the highest salary, minimum wage, average salary, total number of employees, number of jobs, and total salary of Department 30.

Select max (ename), max (SAL ),

Min (ename), min (SAL ),

AVG (SAL ),

Count (*), count (job), count (distinct (job )),

Sum (SAL) from EMP where deptno = 30;

2. group with group by and having

1) Calculate the highest wage, minimum wage, total number of employees, number of employees, number of types of work, and total wage by group of departments

Select deptno, max (ename), max (SAL ),

Min (ename), min (SAL ),

AVG (SAL ),

Count (*), count (job), count (distinct (job )),

Sum (SAL) from EMP group by deptno;

2) highest salary of Department 30, minimum wage, total number of employees, number of jobs, and total salary

Select deptno, max (ename), max (SAL ),

Min (ename), min (SAL ),

AVG (SAL ),

Count (*), count (job), count (distinct (job )),

Sum (SAL) from EMP group by deptno having deptno = 30;

3. stddev returns the standard deviation of a set of values

Select deptno, stddev (SAL) from EMP group by deptno;

Variance returns the variance difference of a set of values

Select deptno, variance (SAL) from EMP group by deptno;

4. Group by with rollup and cube Operators

Rollup performs statistics and subtotal based on the first column of the group.

The cube calculates statistics based on all the columns in the group and returns the subtotal.

Select deptno, job, sum (SAL) from EMP group by deptno, job;

Select deptno, job, sum (SAL) from EMP group by rollup (deptno, job );

Cube generates statistics of all columns in the group and the final Subtotal

Select deptno, job, sum (SAL) from EMP group by cube (deptno, job );