Oracle Basic Operations

View database Character Set encoding
Select USEREVN (' language ') from dual;

Oracle Common functions:
LOWER (' AAAAA '), convert all characters to lowercase
UPPER (' aaaaasss ') to convert all characters to uppercase
Initcap (' Atsichuan Chengdu '), capitalized each character first letter
CONCAT (' Hello ', ' world '), connect 2 characters
SUBSTR (' Atsichuan ', 1, 5), starting from the first bit to intercept 5 bits
INSTR (' HelloWorld ', ' o '), where the character first appears ' O '
Length (' HelloWorld '), character lengths
Trim (' Hellow world '), remove the character end-to-end space trim (' H ' from ' HELLOHWORLDH ') to remove the character end-to-end h--(Ellohworld), indicating trim can only remove the character end-to-end, the middle cannot be removed
Lpad (' SSS ', 5, ' & '), left-justified (&&SSS)
Rpad (' CCC ', 5, ' * '), right-aligned (ccc**)
Replace (' Ancasa ', ' a ', ' d ') replaces a character with a D
Convert to Amount type To_char (Salary, ' $99999999.99 ')

ROUND rounding ROUND (157.9321)---158 ROUND (157.9321,2)--157.93 ROUND (157.9321,-2)--100
TRUNC truncation TRUNC (69.9923, 2)---69.99 TRUNC (69.9923)---TRUNC (69.9923,-1)---60
MoD for remainder mod (100)---

Date function
Months_between (sysdate,hire_date) Number of months between 2 dates
Add_months Add month add_months (sysdate,2)----2017/12/12 10:52:34 add_months (sysdate,-3)---2017/7/12 10:52:34 (Sysdate is 20171012)
Next_day (sysdate, ' Thursday ') date of the day of the next one weeks (Sysdate is 20171012)
Last_day refers to the last day of this month Last_day (sytsdate)---(Sysdate is 20171012)
Round (sysdate, ' mm ')---20171001 round (sysdate, ' month ')---20171001
Trunc (Sysdate, ' year ')---2017/1/1 trunc (sysdate, ' mm ')--2017/10/1 trunc (sysdate, ' DD ')--2017/10/12 trunc (sysdate, ' hh ') ')---2017/10/12 11:00:00
Wm_concat The function can place the query result in a cell;

DECODE (field or Field operation, value 1, value 2, value 3)
The result of this function is that when the value of the field or Field operation is equal to the value 1 o'clock, the function returns a value of 2, otherwise the value 3 is returned.
Decode (condition, value 1, return value 1, value 2, return value 2, value 3, return value 3 ...) Value N, return value N, default value)
The function has the following meanings:
IF condition = value 1 Then
return value 1
IF condition = value 2 Then
return value 2
IF condition = value 3 Then
return value 3
....
ELSE IF Condition = value n Then
return value N
ELSE
RETURN Default Value
END IF

Merge into usage
Basic syntax for Merge:
Merge into Table[alias]
Using table or SQL Query[alias]
On condition
When matched then
Update set .....
When isn't matched then
Insert values ....

The TRUNCATE TABLE command quickly deletes all records in the datasheet, but retains the data table structure. The deleted data is not recoverable. (Delete very fast)

The delete from table command deletes data stored in the system rollback segment, where the data can be recovered.

Drop table will delete the structure of the table that is dependent on the constraint (constrain), trigger (trigger), index, and the stored procedure/function that depends on the table will remain, but become invalid state.

Exits

Between and between two values (inclusive boundary)

Oracle Basic Operations