Oracle Course archive, next day

Salary: Wages

ORDER BY: Sort

Desc: Descending

Hire: Employment

Single-line function

It's seven days a week.

January is not necessarily 30 days

Trunc: Intercept

Dual: Empty table

Last: Finally

Month: Months

Round: Rounding Method

SUBSTR: String interception

Length: string lengths

Replace: string content substitution

Upper: Convert lowercase letters to uppercase

Lower: converting uppercase characters to lowercase letters

Initcap: Converting a word into an uppercase letter

Date-date = Number (indicates number of days)

Date-Number = Date

Date + Number = Date

Rounded function: ★

Select Round (123.45,1) from dual 123.5

+ 1: Rounding takes a decimal point after one

Select Round (123.45,-1) from dual 120

-1: Rounded up to 10 bits

Select Round (123.45,0) from dual 123

0: Rounding Take integers

Trunc: Intercept

Truncate the decimal function----trunc (), such as:
A) SELECT trunc (789.536) from dual; The default is to keep only the integer digits, and the decimal place does not do rounding operations;
b) SELECT trunc (789.536, 2) from dual; Keep two decimal places, fractional digits do not do rounding operations;
c) SELECT trunc (789.536,-2) from dual; When the integer digits are discarded from the low to the high, the number of digits above 5 is likewise discarded, making 0 regards

Date:

B) Function:
A) Months_between ()----Find out the number of months in the specified range, such as: SELECT empno, ename, round (Months_between (sysdate), hiredate) from EMP;
b) add_months ()---Add the specified number of months to the specified date, such as: SELECT add_months (Sysdate, 3) from dual;
c) Next_day ()----to find the number of weeks of the week that are the same as the week of the week, such as: SELECT Last_day (sysdate, ' Sunday ') from dual;
d) last_day ()---to find the date of the last day of this month, such as: SELECT Last_day (sysdate) from dual;

Char: String type

Conversion functions: Converting from one data type to another data type;
A) A little introduction to date wildcards:
1) Year: Y, the year is four digits, therefore uses the YYYY to represent the year;
2) Month: M, month is two digits, so use mm to indicate the month;
3) Day: D, Day is two digits, so use DD to denote the month;

B) Function:
1) To_char ()----converted to a string, such as:
SELECT empno, ename, To_char (hiredate, ' yyyy ') year, To_char (HireDate, ' mm ') month, To_char (hiredate, ' DD ') Day from EMP ; This instance completes the split work of the date, which is to remove the year, month, and day, respectively, for formatting use.
SELECT empno, ename, To_char (hiredate, ' yyyy-mm-dd ') from EMP; localization of completion dates;
SELECT empno, ename, To_char (hiredate, ' yyyy-mm-dd ') from EMP; Remove the leading 0, the completion date localization.
SELECT empno, ename, To_char (Sal, ' 99,999 ') from EMP; Wages are displayed with a comma for each three digits, and the placeholder can use only the number 9.
SELECT empno, ename, To_char (Sal, ' $99,999 ') from EMP; The salary is shown in US dollar form.
SELECT empno, ename, To_char (Sal, ' l99,999 ') from EMP; Localized wage format, "L" or "L" for "local" abbreviations, for localization.
2) To_number ()---converted to numbers such as:
SELECT to_number (' 123 ') + to_number (' 789 ') from dual; The string "123" and the string "789" are converted to numbers for arithmetic.
3) To_date ()---converted to a date.
SELECT to_date (' 2011-01-10 ', ' YYYY-MM-DD ') from dual; Converts the string date format to the Oracle built-in date format.
5, General function: NVL function, decode function.
1) the NVL () function is used to set the value of a change to another default value, the usual term four null value to 0 when calculating, the example is as follows:
SELECT empno, ename, NVL (comm, 0), (sal + NVL (comm, 0)) * Income from EMP;
2) the DECODE () function is similar to the If...else If...else statement, which is used to match the data one by one and to calculate the structure, such as:
SELECT DECODE (1, 1, ' content is 1 ', 2, ' content is 2 ', 3, ' content is 3 ') from dual;
SELECT empno Employee number, ENAME employee name, hiredate hire date, DECODE (Job, ' clerk ', ' salesman ', ' salesman ', ' Salesperson ', ' MANGER ', ' manager ', ' President ', ' president ') position from EMP;

NVL: Replace value

Oracle Course archive, next day