Oracle Common functions

Under Sqlplus, implement the medium-to-English character set conversion
Alter session set nls_language= ' AMERICAN ';
Alter session set Nls_language= ' Simplified Chinese ';
Key points of knowledge:
I. Operations related to tables
1) Build Table

CREATE TABLE Test as SELECT * from dept; --copy data and structure from known tables
CREATE TABLE Test as SELECT * from dept where 1=2; --Copy the structure from a known table but excluding data
2) Insert data:
INSERT INTO Test select * from dept;

Two, operator
Arithmetic operators: +-*/You can use the
Join operator in a SELECT statement: | | Select Deptno| | dname from dept; 
comparison operator:> >= =! = < <= like between are null in
logical operators: Not and or 
Set operators: Intersect, Union, union All, minus 
Requirements: The number of columns for the corresponding collection and Same data type
the label in the query that cannot contain a long column
is the label of the first collection
when you use order BY, you must use a positional ordinal, and you cannot use a column name
Example: Use of Set operators:
Intersect, Union, Union All, minus 
SELECT * FROM emp intersect select * from EMP where deptno=10;--
Select * from emp minus SELECT * From EMP where deptno=10;--
SELECT * from EMP where deptno=10 union SELECT * from EMP where deptno in (10,20);--not including duplicate rows  
Select * from EMP where deptno=10 UNION ALL SELECT * from EMP where deptno in (10,20);--including duplicate rows

III, common ORACLE functions
Sysdate for system date dual to virtual table
One) Date function [focus on first four date functions]
1,add_months [Return date plus (minus) The date after the specified month (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 months_between (sysdate+20, sysdate) as "months" from dual//based on how many days per month +31, February +28 (or29), April +30 is full one months
Select Sysdate S1, Months_between (' January-April -04 ', sysdate) S2,
Months_between (' January-April-04 ', ' January-February -04 ') S3 from dual
4,next_day (d,day): Returns the date of the next week, day 1-7 or Sunday-Saturday, 1 means Sunday  
Select Sysdate S1, Next_day (sysdate,1) S2,
Next_day (sysdate, ' Sunday ') S3 from dual 
5,round[rounded to the nearest date] (day: rounded to the nearest Sunday)
Select Sysdate S1,
Round (sysdate) S2,
Round (sysdate, ' Year ') year,
Round (sysdate, ' Month ') month,
Round ( Sysdate, ' Day ') days from dual
6,trunc[truncated to the nearest date] 
Select Sysdate S1,
trunc (sysdate) S2,
Trunc ( Sysdate, ' Year ') year,
Trunc (sysdate, ' Month ') month,
Trunc (sysdate, ' Day ') days from dual
7, returns the latest date in the list of dates
Select Greatest (' January-January-04 ', ' April-January-04 ', ' October-February -04 ') from dual

Two) character functions (can be used for literal characters or database columns)
1, String intercept
Select substr (' abcdef ', 1,3) from dual
2, finding the substring location
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 preamble 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, returns the letter corresponding to the ASCII value
Select CHR from dual
7, calculating the string length
Select Length (' abcdef ') from dual
8,initcap (capitalized), lower (lowercase), upper (uppercase)
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 (' ABCDE ', ' eac ', ' 1x2xxx ') from DUAL;--XB2D1

11,lpad [Left refill] rpad [right padding] (for controlling output format)
Select Lpad (' Func ', s1, ' = '), Rpad (' func ', '-') ' S2 from dual;
Select Lpad (dname,14, ' = ') from dept;
The decode[implements If. Then logic]
Select Deptno,decode (deptno,10, ' 1 ', 20, ' 2 ', 30, ' 3 ', ' other ') from dept;
c) Number function
1, take the whole function (ceil up, floor down rounding)
Select Ceil (66.6) N1,floor (66.6) N2 from dual;
2, exponentiation (Power) and square root (sqrt)
Select Power (3,2) n1,sqrt (9) N2 from dual;
3, redundancy
Select mod (9,5) from dual;
4, returns a fixed number of decimal digits (round: Rounding, Trunc: direct truncation)
Select Round (66.667,2) N1,trunc (66.667,2) N2 from dual;
5, the sign of the return value (positive return is 1, negative number is-1)
Select sign ( -32), sign (293) from dual;
IV) Conversion function
1,to_char () [Converts 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 character type to date type]
INSERT into EMP (empno,hiredate) VALUES (8000,to_date (' 2004-10-10 ', ' yyyy-mm-dd '));
3, To_number () converted to numeric type
Select To_number (To_char (sysdate, ' Hh12 ')) from dual; Number of hours displayed as a number
(v) Other functions
User
Returns the user name of the login
Select User from Dual;
Vsize:
The number of bytes required to return an expression
Select Vsize (' HELLO ') from dual;
NVL (EX1,EX2):
EX1 value is null returns EX2, otherwise the value itself is returned EX1 (common)
Example: If an employee does not have a commission, 0 is displayed, otherwise the Commission is displayed
Select COMM,NVL (comm,0) from EMP;
Nullif (EX1,EX2):
value is equal to NULL, otherwise the first value is returned
Example: If wages and commissions are equal, the display is empty, otherwise the wages are displayed
Select Nullif (Sal,comm), sal,comm from EMP;
COALESCE:
Returns the first non-empty expression in a list
Select Comm,sal,coalesce (comm,sal,sal*10) from EMP;
NVL2 (EX1,EX2,EX3):
If Ex1 is not empty, display ex2, otherwise ex3
such as: View the names of employees with commissions and their commissions
Select NVL2 (Comm,ename, ') as Havecommname,comm from EMP;
VI) Grouping functions
Max min avg count sum
1, the entire result set is a group
1) 30 of the department's maximum wage, minimum wage, average wage, total number, number of people working, number of jobs and sum of wages
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 by and having groups
1) The highest wage, minimum wage, total number, number of workers, number of jobs and sum of wages in accordance with the Department group
Select Deptno, Max (ename), Max (SAL),
Min (ename), Min (Sal),
AVG (SAL),
Count (*), COUNT (Job), COUNT (Distinct (job)),
Sum (SAL) from the EMP group by DEPTNO;
2) Department 30 maximum wage, minimum wage, total number, number of jobs, number of jobs and sum of wages
Select Deptno, Max (ename), Max (SAL),
Min (ename), Min (Sal),
AVG (SAL),
Count (*), COUNT (Job), COUNT (Distinct (job)),
Sum (SAL) from the EMP group by DEPTNO have deptno=30;
3, StdDev returns the standard deviation of a set of values
Select Deptno,stddev (SAL) from the EMP group by DEPTNO;
Variance returns the variance difference of a set of values
Select Deptno,variance (SAL) from the EMP group by DEPTNO;
4, GROUP BY with rollup and cube operators
Rollup statistics and final subtotals by the first column in a group
Cube statistics and final subtotal by all columns grouped
Select Deptno,job, sum (SAL) from the EMP group by Deptno,job;
Select Deptno,job, sum (SAL) from the EMP Group by Rollup (Deptno,job);
Cube generates statistics and final subtotals for all columns within a group
Select Deptno,job, sum (SAL) from the EMP Group by Cube (Deptno,job);

Vii. Temporary tables
A table that exists only during a session or during transaction processing.
Temporary tables allocate space dynamically when inserting data
Create global temporary table temp_dept
(DNO number,
Dname VARCHAR2 (10))
on commit delete rows;
INSERT into temp_dept values (' ABC ');
Commit
SELECT * from Temp_dept; -No data display, automatic data removal
On commit preserve rows: The table can always exist during the session (preserves data)
On commit Delete rows: Transaction end purge data (automatically delete table data at end of transaction)

========================== Connection Operation ================================

For example, there are two tables, A, B:

A table B
A B c D
1 0 4 7
2 9 1 5


SELECT * from a b where a.a = B.C
Equivalent to
SELECT * from A join B on a.a = B.C
Results:
A B c D
1 0 1 5

Left JOIN connection:
SELECT * from a b where a.a = B.C (+)
Equivalent to
SELECT * from A LEFT join B on a.a = B.C
That is, a-table query is the main, with the results of the B table that satisfies the A.A = B.C condition is included in the query
Results:
A B c D
1 0 1 5
2 9


Right connection:
SELECT * from a b where a.a (+) = B.C
Equivalent to
SELECT * from A right join B on a.a = B.C
This query is a left-hand connection, that is, a-table query, which comes with a query for results in table B that satisfies the A.A = B.C condition
Results:
C D a B
1 5 1 0
4 7

===============================================================


=====================Oracle function ===============================
DECODE:
Decode (condition, value 1, translation value 1, value 2, translation value 2,... Value n, translation value n, default value)

Table A
A b C
1 3 0
3 2 9
1 0 5
3 7 7
5 5 9

Select Decode (a.a,1,4,3,8), a.b,a.c form A
Meaning: If the value of A.A is 1, the value of A.A is queried as 4, and if the value of A.A is 3, the value of A.A is queried as 8
Results:
A b C
4 3 0
8 2 9
4 0 5
8 7 7
5 5 9

Select Decode (a.a,1,4,8), a.b,a.c form A
Meaning: If the value of A.A is 1, the value of A.A is queried as 4, otherwise the value of A.A is queried as 8
Results:
A b C
4 3 0
8 2 9
4 0 5
8 7 7
8 5 9

Select Decode (a.a,1,4,3,8,7), a.b,a.c form A
Meaning: If the value of A.A is 1, the value of A.A is queried as 4, if the value of A.A is 3, the value of A.A is queried as 8, otherwise the query is 7
Results:
A b C
4 3 0
8 2 9
4 0 5
8 7 7
7 5 9
===============================================================


==========================sequence=====================================

Sequence:create SEQUENCE Locations_seq
INCREMENT by 1
START with 1
MAXVALUE 9900
MINVALUE 1

SELECT Locations_seq.currval from DUAL;
SELECT Locations_seq.nextval from DUAL;

============================sequence===================================



============================ function ===================================
Date function:
Add_months (d,n) Date d plus n months
Last_day (d) Date of the last day of the month containing D
Month_between (d,e) Number of months between date D and E, E precedes D
New_time (d,a,b) a time zone date and time D in the B time zone date and time
Next_day (d,day) Date D, day of the week specified by day
Sysdate current system date and time
Greatest (D1,d2,... dn) The last date in the list of dates given
Least (D1,K2,... dn) The earliest date in the list of dates given
To_char (d [, FMT]) Date D converted to a string in the format specified by FMT
The To_date (St [, FMT]) string St is converted to a date value in the format specified by FMT, and if FMT is omitted, the ST is in the default format
Round (d [, FMT]) Date D rounded to the nearest date in FMT-specified format
Trunc (d [, FMT]) Date D truncated to most recent date by FMT-specified format

Grouping functions:
AVG ([Distinct/all] n) The average of column n
Count ([all] *) returns the number of rows in the query range including duplicate values and null values
Count ([Distinct/all] n) Number of rows with non-null values
Max ([Distinct/all] n) The maximum value of the column or expression
MIN ([Distinct/all] n) The minimum value of the column or expression
Stdev ([Distinct/all] n) standard deviation of the column or expression, ignoring null values
SUM ([Distinct/all] n) The sum of the column or expression
Variance ([Distinct/all] n) The variance of the column or expression, ignoring null values

Numeric functions:
Absolute value of ABS (m) m
MoD (m,n) m is removed after n remainder
Power (M,n) m n-Th Square
Round (M[,n]) m rounded to the value of n bits after the decimal point (n defaults to 0)
Trunc (M[,n]) m truncates the value of n-bit decimal digits (n defaults to 0)

Character functions:
Initcap (ST) returns ST capitalize each word, all other letters lowercase
Lower (ST) returns ST all lowercase letters for each word
Upper (ST) return St capitalize all letters in each word
Concat (ST1,ST2) Returns the end of St for St2 St1 (available operator "| |" ）
Lpad (St1,n[,st2]) returns the right-aligned st,st to the left of St1 with a st2 padding until the default length of N,st2 is a space
Rpad (St1,n[,st2]) returns the left-aligned st,st to the right of st1 with ST2 padding until the default length of N,st2 is a space
LTrim (St[,set]) returns ST,ST as the character in the set that is removed from the left until the first one is not set. By default, it refers to a space
RTrim (St[,set]) returns ST,ST to the right to remove characters from the set until the first one is not in set. By default, it refers to a space
Replace (St,search_st[,replace_st]) replaces each search_st that appears in St with Replace_st, returning an St. By default, delete Search_st
SUBSTR (St,m[,n]) n= returns the substring of the St string, starting at the m position and taking n characters long. The default is to return to the St end
Length (ST) Value, returns the number of characters in St
InStr (St1,st2[,m[,n]]) value, returns the position of St1 starting at the first m character, st2 the nth occurrence, and the default value of M and N is 1

Oracle Common functions