This article mainly talks about the functions commonly used in Oracle, as well as the knowledge of type conversion.

Note: date type is troublesome, but date format is indispensable in practical application.

Single-Row Functions

Single line function: only one parameter input and only one result output

--1,Study lower/upper/initcap Functions, using dual Dummy table
select lower('www.BAIdu.COM') from dual; --lower()Turn lowercase
select upper('www.BAIdu.com') from dual; --upper()Turn capitalization
select initcap('www.BAIdu.COM') from dual; --initcap()title case

--2,Study concat/substr function
--concat Connection string (concatenation of two strings)
select concat('hello','Hello') from dual; --  Correct
select concat('hello','Hello','world') from dual; 
-- error  concat Connection string, only two parameters

--Multiple parameters
select 'hello' || 'Hello' || 'world' from dual; --Correct
select concat('hello',concat('Hello','world')) from dual;--Correct, not recommended, poor reading

--substr Substring
select substr('hello Hello',5,3) from dual;
--5 The first character is 1, which is processed in both Chinese and English
--3 Indicates to take several consecutive characters

--3,Study length/lengthb Function, encoded as UTF8/GBK(Hello)，One Chinese 3/2 Byte length, one English byte
select length('hello Hello') from dual;      --length()Character length 7
select lengthb('hello Hello') from dual;    -- lengthb()Byte length 11

--4,Study instr/lpad/rpad Function,
--instr Find the first occurrence from left to right, starting from 1
select instr('helloworld', 'o') from dual;
--Note: return 0 not found, case sensitive
--lpad If the number of digits is not enough, start filling from the left, and if it is more than the number, cut off the redundant ones
select lpad('hello',10,'#')from dual;
--rpad Start filling from the right side, and if it is over, cut off the rest
select rpad('hello',3,'#')from dual;

--5,Study trim/replace function
--trim Take out the characters on both sides of the string
select trim('o' from 'ooooheooooollooooooooooo') from dual;
--replace Replace specified characters
select replace ('hello','l','L') from dual;

--6,Study round/trunc/mod Function acts on numerical type

select round(3.1415,3) from dual;  --Keep 3 decimal places, according to rounding method
select trunc(3.1415,3) from dual;  --Keep 3 decimal places and cut directly
select mod(10,3) from dual;        --Yu Yunsuan

--Current date: sysdate = 06-2 month-18

--Study round Act on date type( month)
select round(sysdate,'month')from dual;

--Study round Act on date type( year)
select round(sysdate,'year')from dual;

--Study trunc Act on date type( month)
select trunc(sysdate,'month')from dual;

--Study trunc Act on date type( year)
select trunc(sysdate,'year')from dual;

--7,Show yesterday, today, tomorrow's date, date type +- numerical value = Date type
select sysdate-1 "Yesterday", sysdate "Today", sysdate+1 "Tomorrow" from dual;

--8,Display the approximate working age and date of the employee in the form of year and month-date=Value, assuming: 365 days in a year, 30 days in a month
select ename "Full name", round(sysdate-hiredate,0)/30 "month" from emp;
select ename "Full name", round(sysdate-hiredate,0)/365 "year" from emp;

--9,Use months_between Function to calculate exactly how many months to the end of the year (from now to 2018/12/31 Day)
select months_between('31-12 month-18',sysdate) from dual;

--Use months_between Function to display the working age of an employee in the form of an exact month
select ename "Full name" ,months_between(sysdate,hiredate) from emp;
--10,Study add_months Function, what's the date of today next month
select add_months(sysdate,1) from dual;

--Study add_months Function, what's the date last month
select add_months(sysdate,-1) from dual;

--10,Study next_day Function, starting from today, what is the next Wednesday [Chinese platform]
--Note: If today is Monday or Tuesday and the next Wednesday, it means this Wednesday
--     If today is or after Wednesday, next Wednesday is the next Wednesday in life
select next_day(sysdate,'Wednesday') from dual;

--Study next_day Function, starting from today, what is the next Wednesday [Chinese platform]
select next_day(next_day(sysdate,'Wednesday'),'Wednesday') from dual;

--Study next_day Function, starting from today, what is the next Sunday of the next Wednesday [Chinese platform]
select next_day(next_day(sysdate,'Wednesday'),'Sunday') from dual;

--11,Study last_day Function, what is the last day of the month
select last_day(sysdate) from dual;

--Study last_day Function, what is the last day of the month
select last_day(sysdate)-1 from dual;

--Study last_day Function, what is the last day of the next month
select last_day(add_months(sysdate,1)) from dual;

--Study last_day Function, what is the last day of the previous month
select last_day(add_months(sysdate,-1)) from dual;

General function and conditional judgment function

--General function: parameter type can be number or varchar2 or date type
--1,Use NVL(a,b)General function to count the annual income of employees, NVL()For any type, i.e(number/varchar2/date)
select ename,sal*12+NVL(comm,0) from emp;

--2,Use NVL2(a,b,c)General function, if a Not for NULL，take b Value, otherwise c Value, annual income of employees 
select ename,sal*12+NVL2(comm,comm,0) from emp;

--3,Use NULLIF(a,b)General function, in case of consistent type, if a And b Same, return NULL，Otherwise return a，Compare 10 and 10.0 Are they the same?
select NULLIF(10,10.0) from dual;
select NULLIF(10,10.1) from dual;

/*4,Using the case expression in SQL99 standard general syntax, the position is analyst's, salary + 1000; position is manager's, salary + 800; position is other, salary + 400
case field 
     when Condition 1 then expression 1
     when Condition 2 then expression 2
     else Expression n
end 
Please refer to the manual of < MySQL 5. X > - 12.2*/
select ename "Full name",job "position",sal "Wage increase",
       case job
        when 'ANALYST' then sal+1000
        when 'MANAGER' then sal+800
            else sal+400
       end "Post rising wages"
from emp; 

/*5,Using the decode() function in oracle special syntax, position is analyst's, salary + 1000; position is manager's, salary + 800; position is other, salary + 400
decode(Field, condition 1, expression 1, condition 2, expression 2,... Expression n)*/
select ename "Full name",job "position",sal "Wage increase",
       decode(job,'ANALYST',sal+1000,'MANAGER',sal+800,sal+400) "Post rising wages"
from emp; 

Single quotes appear as follows:
1) String, for example: 'hello'
2) Date type, for example: '17-12-80'
3) To / to date (date, 'YYYY-MM-DD HH24:MI:SS')

Double quotes appear as follows:
1) Column alias, for example: select ename "name" from emp
2) To char / to date (date, YYYY, MM, DD, HH24:MI:SS)) '

Multi row function

Multiline function or grouping function: it can have multiple parameter inputs and only one result output

/*
Function: a program segment with certain functions written in advance by oracle server, which is built in oracle server for users to call 
Single line function: input a parameter and output a result, for example: upper ('baidu. Com ') - > baidu.com
 Multiline function: input multiple parameters, or internal scan multiple times, and output a result, for example: count (*) - > 14
*/

--1,Statistics emp Total number of employees in the table
select count(*) from emp;
-- *No. is applicable to the case of less table fields. If there are more fields and more scans, the efficiency is low. It is recommended to use a non null Unique field, usually primary key 

--2,Count the number of non repetitive departments in the company
select count(distinct deptno) from emp;

--3,Count the number of employees with Commission
select count(comm) from emp;
--Note: these multiple line functions do not count NULL value

--4,Total wage, average wage, rounded to the nearest 0 decimal places
select sum(sal) "Total wages",round(avg(sal),0) "average wage" from emp;

--5,Query the maximum wage and minimum wage in the employee table
select max(sal) "Maximum wage",min(sal) "minimum wage" from emp;

--6,The earliest and the latest employee
select max(hiredate) "Latest entry time",min(hiredate) "Earliest entry time" from emp;

--Multiline function: count/sum/avg/max/min

--7,Calculate the average wage of the department according to the Department, and take the integer as the average wage, and use the truncation method
select deptno "Department number",trunc(avg(sal),0) "Department average wage"
from emp
group by deptno;

--8,(Continue)Query the Department whose average salary is more than 2000 yuan
select deptno "Department number",trunc(avg(sal),0) "Department average wage"
from emp
group by deptno
having trunc(avg(sal),0) > 2000; 

--9,(Continue)Sorted by department average wage in descending order
select deptno "Department number",trunc(avg(sal),0) "Department average wage"
from emp
group by deptno
having trunc(avg(sal),0) > 2000
order by 2 desc;

--10,Except for No.10 department, query the Department whose average wage is more than 2000 yuan, method 1[ having deptno<>10]
select deptno,avg(sal)
from emp
group by deptno
having deptno<>10;

--11,Except for No.10 department, query the Department whose average wage is more than 2000 yuan, method 2[ where deptno<>10]
select deptno,avg(sal)
from emp
where deptno<>10
group by deptno; --promote

--12,Display the maximum value of the average wage of the Department
select max(avg(sal)) "Maximum of average Department wage"
from emp
group by deptno;

--Display the maximum average wage of the Department and the department number?
select max(avg(sal)) "Maximum of average Department wage",deptno "Department number"
from emp
group by deptno;
--error

/*
group by Clause details:
1)All columns of non multiline functions that appear in the select clause, [must] appear in the group by clause
2)For all columns appearing in the group by clause, [can appear but not appear] in the select clause
*/

/*
where And having:
where: 
1)Line filter
2)For the original record
3)Follow from
4)where Can save
5)First execution

having: 
1)Group filter
2)For grouped records
3)Follow group by
4)having Can save
5)Post execution
*/

/*oracle Middle Comprehensive Grammar:
1)select Clause - must
2)from Clause ------ must, I don't know what table to write, just write dual
3)where Clause ------ optional
4)group by Clause - Optional
5)having Clause ----- optional
6)order by Clause -- optional, if column name, alias, expression, field
*/

Three types of conversion

Three types and implicit data type conversion in oracle
(1) varchar2 variable length / char fixed length – > number, for example: '123' - > 123
(2) varchar2/char – > date, for example: '25-april-15' - > 25-april-15 '
(3) number - > VARCHAR2 / char, for example: 123 - > 123 '
(4) date - > VARCHAR2 / char, for example: '25-april-15' - > 25-april-15 '

How oracle implicitly transforms:
1) Is the type of the two sides of = the same
2) If the type of = two sides is different, try to do the conversion
3) When converting, make sure it is legal and reasonable, otherwise the conversion will fail. For example, there will not be 32 days in December, and there will not be 13 months in a year

--1,Query the employees who joined on December 17, 1980 (method 1: Date implicit conversion)
select * from emp where hiredate = '17-12 month-80';

--2,Use to_char(Date,'grid"constant"type')Function to convert a date into a string, in the following format: Tuesday, February 26, 2018
select to_char(sysdate,'yyyy" year "mm" month "dd" day "day') from dual;

--3,Use to_char(Date,'format')Function to convert a date into a string, as shown in the format: 2018-02-06 Today is Tuesday 15:59:15
select to_char(sysdate,'yyyy-mm-dd"Today is"day hh24:mi:ss') from dual;
--Or display as format: 2018-02-06 Today is Tuesday 3:59:55 Afternoon
select to_char(sysdate,'yyyy-mm-dd"Today is"day HH12:MI:SS AM') from dual;

--4,Use to_char(Numerical value'format')Function to convert a value into a string, as shown in the following format: $1,234
select to_char(1234,'$9,999') from dual;

--Use to_char(Numerical value'format')Function to convert a value into a string, as shown in the following format:￥1,234
select to_char(1234,'L9,999') from dual;

--5,Use to_date('Character string','format')Function to query the employees who joined on December 17, 1980 (method 2: Date explicit conversion)
select * from emp where hiredate = to_date('1980 December 17, 2010','yyyy"year"mm"month"dd"day"');
--or
select * from emp where hiredate = to_date('1980#12#17','yyyy"#"mm"#"dd');
--or
select * from emp where hiredate = to_date('1980-12-17','yyyy-mm-dd');

--6,Use to_number('Character string')Function to string'123'Turn to number123
select to_number('123') from dual;


--Be careful:
select '123' + 123 from dual;  --246
select '123' || 123 from dual; --123123