Basic functions in oracle

In is equal to any member in the brackets, such as select * from dept where deptno in ('10', '20', '30', '40 ');
Not in is not equal to any member in the brackets, such as select * from dept where deptno not in ('10', '20', '30', '40 ');
Between A and B is greater than or equal to A and less than or equal to B, such as select * from emp where sal beteen 1000 and 1500;
Not between A and B is not greater than or equal to A and less than or equal to B, such as select * from emp where sal beteen 1000 and 1500;
Like '% A %' to include A to the sub-string such as select * from emp where ename like '% A % ';
> = Greater than or equal to <= less than or equal
 
 
Common numeric Functions
Function return value sample display
Ceil (n) is the smallest integer greater than or equal to the value n select ceil (10.6) from dual; 11
Floor (n) is less than or equal to the maximum integer n select floor (10.6) from dual; 10
Mod (m, n) m divided by the remainder of n. If n = 0, m select mod () from dual; 2 is returned.
Power (m, n) m's n select power (3, 2) from dual; 9
Round (n, m) rounds n to 5, retain the m digits after the decimal point select round (1234.5678, 2) from dual; 1234.57
Sign (n) if n = 0, 0 is returned. Otherwise, if n> 0, 1 is returned. If n <0,-1 select sign (12) from dual; 1 is returned.
The square root of sqrt (n) n select sqrt (25) from dual; 5
 

Common Character Functions
Initcap (char) Replace the first character of each string with the capital select initicap ('Mr. ecop') from dual; mr. ecop
Lower (char) converts the entire string to lowercase select lower ('Mr. ecop') from dual; MR. ecop
Replace (char, str1, str2) string replace all str1 with str2 select replace ('Scott ','s', 'Boy ') from dual; Boycott
Substr (char, m, n) extract the n-character substring starting from m ('abcdef', 2, 2) from dual; BC
Length (char) Evaluate the length of a string select length ('acd') from dual; 3
| Union operator select 'abcd' | 'efgh' from dual; ABCDEFGH
Instr (char, search_string, startpos, occurrence)
From the string, start from the position startpos, find the location where search_string is occurrence times
Select instr ('cvmn ', 'V', 1, 1) from dual;
Date Functions
Select sysdate from dual;
Last_day select last_day (sysdate) from dual;
Add_months (d, n) select add_months (sysdate, 2) from dual after the current date d;
Months_between (d, n) date d and n difference the number of months select months_between (sysdate, to_date ('2013', 'yyyymmdd') from dual;
Select next_day (sysdate, 'monday') from dual;
Day format: 'monday' Monday
'Tuesday' Tuesday
'Wednesday' Wednesday'
'Thursday' Thursday
'Friday'
'Saturday'
'Sunday' Sunday

Date functions in special format
The last digit of Y, YY, or YYY, two digits, three select to_char (sysdate, 'yyy') from dual;
Q quarter, 1-3 is the first quarter of select to_char (sysdate, 'q') from dual;
MM Month count select to_char (sysdate, 'mm') from dual;
In the month of RM, Rome indicates select to_char (sysdate, 'rm ') from dual; IV
The month name, represented by 9 characters, is select to_char (sysdate, 'month') from dual;
Select to_char (sysdate, 'ww ') from dual;
W select to_char (sysdate, 'w') from dual;
The day of DDD that year, July 001, and July 032 select to_char (sysdate, 'ddd ') from dual;
DD select to_char (sysdate, 'dd') from dual;
Select to_char (sysdate, 'D') from dual; such as Sunday
The abbreviation of the day in the DY week is select to_char (sysdate, 'dy ') from dual; such as sun
Select to_char (sysdate, 'hh12') from dual;
Select to_char (sysdate, 'hh24') from dual;
Mi minutes select to_char (sysdate, 'mi') from dual;
Ss seconds select to_char (sysdate, 'ss') from dual;
Select to_char (sysdate, 'yyyy-MM-DD HH: 24: mi: ss') from dual;

To_number () select to_number ('000000') from dual; 88877
To_char () converts a number to a string select to_char (88877) from dual; '123'
 
Set serveroupt on;
Dbms_output.put_line ('Hello World ')
--- Output hello world

Set heading off because a data file is being created, the header is not required
Set pagesize 0 does not require Paging
Set linesize 80 set the maximum size of the row
Set echo off tells SQL plus not to echo the statement when executing the statement
Set feedback off disable SQL plus from displaying the number of rows that meet the query conditions to be retrieved
Col sales format 999,999,999
Append add text to the end of the current row
Change/old/new/replace the old text with the new text in the current line
Change/text delete wenb from current row
Del delete current row
Input text adds a row after the current row
List displays all rows in the buffer.
List n displays the nth row in the buffer
List m n displays m to n
Decode () function update test set id = decode (id, 'B', 'D', id), parentid = decode (parentid, 'B', 'D ', parentid );