This article describes the functions in linux. In linux, functions are divided into single line functions and group functions. Single line functions are divided into string functions, number functions, date functions and other functions. Group functions are divided into max, min, avg, sum and count.

1 system, environment and preconditions

• win10 64
• oracle 11g https://www.jianshu.com/p/1609289f4c5f

2 operation

• 1 start cmd command line as system administrator

   

  

  Start cmd as system administrator

2. Single line function

• String function

# Connect scott from the windows command line
sqlplus scott/tiger
# Length finds the string length, where dual is a pseudo table
select length('zhangli') from dual;
# instr find subscript of substring
select instr('zhangli','ang') from dual;
# initCap first character capitalized
select initCap('zhangli') from dual;
# lower to lowercase
select lower('ZHangli') from dual;
# upper capitalization
select upper('zhangli') from dual;
# Replace replace
select replace('zhangli','ang','ANG') from dual;
# trim before and after space
select trim('  zhangli  ') from dual;
# concat splicing
select concat('zhang','li') from dual;
# lpad left splicing
select lpad('zhangli',10,'#') from dual;
# rpad right splicing
select rpad('zhangli',10,'#') from dual;

• Numeric function

# Round round
select round(3.141592,4) from dual;
# trunc truncation
select trunc(3.1415,2) from dual;
# Rounding down
select floor(3.14) from dual;
# Rounding up
select ceil(3.14) from dual;
# Modulo [remainder]
select mod(10,3) from dual;

• Date function

# Get current date
select sysdate from dual;
# Convert date to string
select to_char(sysdate,'yyyy-mm-dd') from dual;
# Convert string to date
select to_date('2008-08-08' , 'yyyy-mm-dd') from dual;
# Find the difference between two dates
select sysdate-to_date('2008-08-08' , 'yyyy-mm-dd') from dual;
# For the last day of the month
select last_day(sysdate) from dual;

• Other functions

# nvl has only two parameters. The left most parameter is not empty
select nvl(null,'ali') from dual;--Printing ali
select nvl('zhangl','xiaoli') from dual;--Printing zhangli
# nvl2 has three parameters. The first parameter is an expression. If the first parameter is empty, the second parameter will be taken. Otherwise, the third parameter will be taken
select nvl2('zhangli','hello','world') from dual; --Printing hello
select nvl2(null,'hello','world') from dual; --Printing world
# coalesce has more than two parameters. The first one is not empty
select coalesce (null,null,null, 123) from dual; --Take 123
select coalesce ('zhangli',null) from dual; --take zhangli
# decode judgment
select decode('zhangli','zhangli',4,'xiaoli',5) from dual;--Get 4
select decode('xiaoli','zhangli',4,'xiaoli',5) from dual;--Get 45
# case when then end
select case sal 
  when 3000 then 'Three thousand'
  when 5000 then 'Five thousand'
  end from emp;

3. Group function

#Query the company's maximum wage, minimum wage, average wage, total number of employees and total wage
select max(sal), min(sal),avg(sal),count(1),sum(sal) from emp;
#Query the maximum wage, minimum wage, average wage, total number of employees and total wage of each department of the company
select deptno, max(sal), min(sal),avg(sal),count(1),sum(sal) from emp group by deptno;
# Query the maximum wage, minimum wage, average wage, total number of employees and total wage of 30 departments of the company
select deptno, max(sal), min(sal),avg(sal),count(1),sum(sal) from emp group by deptno having deptno=30;
#Print information about the highest paid employees
select * from emp where sal >=(select max(sal) from emp);

These are single line functions and group functions in oracle.