I Single-Row Functions

Note: dual is a virtual table and cannot save any data. There is only one field and one row of records

1. Semantics

The single line function returns a result for each record

2. Character function

1.lower(): convert to lowercase, upper(): convert to uppercase, initcap initial capital

select lower(ename) from emp;

2.concat(): connect two values, equal||

select concat(ename,sal)from emp;

3.substr ('', n1,n2) n1 specifies the position, n2 specifies the length, starts from 1, omits n2, intercepts from n1 to the end, n1 is a negative value, and n are inverted

select substr('hello',2,2) from dual;

4.length() gets the length of the string

select length('hello')from dual;

5.instr(s1,s2,n1,n2) returns the position of the n2 occurrence of the string S2 starting from n1 in s1. The default value of n1 and n2 is 1

select instr('string','r')from dual;
--The result is 3

6.lpad(s1,n1,s2) s1 is filled with n1 lengths (total length) from the left by s2

 select lpad('hi',3,'hello') from dual;
 --The result is hhi

7.rpad() is the same as above, but it is filled from the right

8.trim() removes characters at the beginning or end of the string

select trim('s' from 'sssis') from dual;
--The result is i

9.replace(s1,s2,s3) replace s2 in s1 with s3

select replace('strrring','r','a')from dual;
--The result is staaaing

3. Numerical function

1.round(n1,n2): round the n1 value n2 to several digits, and a negative value is acceptable

select round(203,-1)from dual;
--200

2.trunc(n1,n1):n1 is the numerical value, n2 is the intercepted length, which can be negative, and {is not rounded

select trunc(223,-2) from dual;--200

3.MOD(m,n) takes the remainder of m/n

select mod(223,2) from dual;
--1

4. Date function

1. The default date format in Oracle is RR format

Dd-mon-rr December 20, 1998, i.e.'20-december-98 '

For the current year (0-49), take (0-49) of this century and (50, 99) of the last century

2.sysdate system current date

select sysdate from dual;

3. Addition and subtraction of dates

 select (sysdate - hiredate)/365 year,(sysdate - hiredate) day from dual

4.months_between(d1,d2) returns the number of months between two dates

select months_between(sysdate,hiredate) month from emp;

5.add_months(d1,n1)d1 is the date, and d1 returns the date after n1 months

select add_months(sysdate,1) from dual;

6.next_day(d,w) returns the week w after the d date

select next_day(sysdate,'Friday') from dual;

7.last_day(d) returns the last day of the specified day month

select last_day(sysdate) from dual;

8.round(d,'m ') returns the date after d date is rounded. M can be month,year

select round(sysdate,'month') from dual;

9.trunc(d,'m ') returns the date after d date interception. M can be month,year

select trunc(sysdate,'month') from dual;

10.extract() returns the year, month or day of the specified date

select sysdate, extract (year from sysdate) from dual;
--2021

5. Conversion function

1.to_char() to characters

select to_char(hiredate,'day') from emp;
--It shows the day of the week

2.to_ Convert date (date, format) to date

select to_date('2020-10-10','yyyy-mm-dd') from dual;  

3.to_number() is converted to a number

select to_number('25') from dual;
--Only in this way can they be converted into numbers

6. General functions

1.case multi condition comparison

select ename,deptno,
                 (case deptno
                       when 10 then 'Sales Department'
                       when 20 then 'R & D department'
                       when 30 then 'Management Department'
                       else 'nothing' end
                 ) 
                 from emp;  

2.nvl(par1,num) refers to a null value as a specific value

select nvl(null,3) from dual;
--3

3.coalesce(par1...) Returns the first parameter that is not empty. There are no limits on parameters

select coalesce(null,null,3) from dual;
--3

II The multiline function returns one result for multiple records

1. Five aggregate functions

1.max() calculate the maximum value

select max(sal) from emp;

2.min(): calculate the minimum value

select min(hiredate) from emp;

3.avg(): calculate the average value. avg filters the null value and does not include the null value in the average calculation

select avg(comm) from emp;--550
select avg(nvl(comm,0))from emp;--157

4.sum(): Sum

select sum(hiredate) from emp;

5.count(): calculate the total number of records, do not filter null values, and filter null values for the above four

select count(empno) from emp;

2.group by grouping function

1. Features

After where, before order by

All common fields in the select list (i.e. fields without aggregate functions, etc.) must be placed in group by

The fields that appear in group by do not necessarily appear in select

Processing multiple pieces of data returns one result, or the table can be divided into multiple groups, and the whole table can be regarded as a group

2. Group by multiple columns, that is, group by is followed by multiple fields

select deptno,job
from emp
where deptno=10
group by deptno,job;

3. If the condition is filter aggregate function, you can't use where to filter, you can only use having

having can only be performed on the basis of grouping. Filtering is a grouping function

select deptno,max(sal)
from emp
group by deptno
having max(sal)>500;

III select execution order

1.from  2.where  3.group by    4.having 5. select 6. orderby

Note: functions can be nested with each other