5. single-row functions, multi-row functions, character functions, numeric functions, date functions, and Data Types

1 multi-line function (understanding: there are multiple inputs, but only one result is output) SQLselectcount (*) fromemp; COUNT (*) ----------- 14B character function LowerselectLower (Hello) to lowercase, convert upper (hello) to uppercase, and convert initcap (hellowoRld) to fromdual.

1 multi-line function (understanding: there are multiple inputs, but only one result is output) SQLselect count (*) from emp; COUNT (*) ------------- 14 B character function Lower select Lower (Hello) to Lower case, upper (hello) to upper case, initcap (hello woRld) to upper case from dual; Result: convert small to upper case

1 multi-line function (understanding: there are multiple inputs, but only one result is output)

SQL> select count (*) from emp;

COUNT (*)

-------------

14

B-character function Lower

Select Lower ('hello') to lowercase,

Convert upper ('hello') to uppercase,

Initcap ('Hello World') is capitalized

From dual;

Result:

Convert small to uppercase letters

---------------------

HelloHELLO Hello World

C-character function concat:

Select concat ('hello', 'World') from dual

Concat function nesting:

Select concat ('hello', 'World'), 'cccc') function nested from dual;

Function nesting

--------------

Helloworldcccc

D character function substr ()

SUBSTR (a, B) --- removes the string from the BTH in

Substr (a, B, c) from the B-th character in a, c ....

Select substr ('abcdef111111', 2) from the second substring,

Substr ('abcdef111111', 2, 4) 4 characters

From dual;

Obtain 4 from the second digit.

-----------------------

Bcdefg1111 bcde

E length characters lengthb bytes

Note: One Chinese character, two bytes

Select length ('Chinese abc') characters,

Lengthb ('abc in China') bytes

From dual;

Running result:

Number of characters

--------------------

5

F instr (), lpad (), rpad (), trim () function

Select instr ('abcdefg', 'efg') to locate the substring from dual;

Substring location

----------------

5

Selectlpad ('abc', 10, '*') lpad, rpad ('abc', 10, '*') rpad from dual;

LPAD RPAD

--------------------

****** Abcd ******

-- Trim removes the specified character. The character can be a space or a space.

Selecttrim ('A' from 'abcdefg') from dual;

TRIM ('

------

BCDEFg

2. Numeric Functions

Second Parameter	Description
2> 0	Retain 2 bits
= 0	Reserved to a single bit
-1	Retain to 10 bits
-2	Keep to bits (to see 10 rounded in)

Selectround (45.926, 2) AA,

Round (45.926, 1) BB,

Round (45.926, 0) CC,

Round (45.926) DD,

Round (45.926,-1) EE,

Round (45.926,-2) FF

From dual;

Result:

AA BB CC DD EE FF

------------------------------------------------------------

45.93 45.9 46 46 50 0

3. Date Functions

Calculation of time .......

Oracle Database date contains date and Time

Mysql 3 data types: date, time, and times.

A selectto_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual;

TO_CHAR (SYSDATE, 'yy

-------------------

2014 to 10-0716: 34: 17

B ---- yesterday, today, tomorrow

Select sysdate-1 yesterday,

Sysdate today,

Sysdate + 1 tomorrow

From dual;

Selectto_char (sysdate-1, 'yyyy-mm-dd hh24: mi: ss') yesterday,

Sysdate today,

Sysdate + 1 tomorrow

From dual;

C ---- query the employee's start time, which is displayed by week, month, and year .....

Select (sysdate-hiredate)/7 weeks,

(Sysdate-hiredate)/30 months,

(Sysdate-hiredate)/365

From emp;

Select (sysdate-hiredate)/7 weeks,

(Sysdate-hiredate)/30 months,

(Sysdate-hiredate)/365.

From emp;

Week month year

------------------------------

1763.95625 411.589791 33.8292979

1754.67053 409.423125 33.6512157

1754.38482 409.356458 33.6457363

D. query the employee's start time and number of months of employment

Selectename,

(Sysdate-hiredate)/30 estimated month,

MONTHS_BETWEEN (sysdate, hiredate) function calculation month

From emp

Select ename,

(Sysdate-hiredate)/30 estimated month,

MONTHS_BETWEEN (sysdate, hiredate) function calculation month

From emp;

ENAME estimation month function calculation month

------------------------------

SMITH 411.589872 405.699876

ALLEN 409.423205 403.603102

WARD 409.356539 403.538586

JONES 408.056539 402.183747

MARTIN 402.089872 396.345037

Selectnext_day (sysdate, 'saturday') from dual;

NEXT_DAY (SYSDA

--------------

September 11-10-14

4. Data Type Conversion

Select * from emp where hiredate> '01-August 1-81'

A date-related type conversion

-81, which is larger than this date, all employee information is implemented in three ways

(1): implicit type conversion

Select *

From emp

Where hiredate> '01-August 1-81 ';

(2): Convert string to date .. convert display type

Select *

From emp

Where hiredate> to_date ('1970-01-010', 'yyyy-mm-dd hh24: mi: ss ');

(3): converts a date to a string of the date type:

Select *

From emp

Where to_char (hiredate, 'yyyy-mm-ddhh24: mi: ss')> '2017-01-01 02:03:04'

5. convert numbers and strings

A -- query the employee's salary: two decimal local currency code kilobytes

Y1, 250.00

Select empno, ename, to_char (sal, 'd9', 999.99 ')

Fromemp

Result:

Empno ename TO_CHAR (SAL, '2009, 999

---------------------------------------

7369 SMITH ￥800.00

7499 ALLEN ￥1,600.00

7521 WARD ￥1,250.00

7566 JONES $2,975.00

7654 MARTIN $1,250.00

7698 BLAKE $2,850.00

7782 CLARK ￥2,450.00

B converts the character ￥1,250.00 into a number .....

Select to_number ('$1,250.00', '2009, 999.99 ') from dual;

TO_NUMBER ('$1,250.00', '2009, 100 ')

-----------------------------------

1250

6 General Functions

A. Raise salary for employees: President: 1000 MANAGER: 800 others: up 500,

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

Analysis: Pre-and Post-paid

If (job = 'President ')

SAL + 1000

Else if (job = 'manager ')

SAL + 800

Else

SAL + 500

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

Knowledge points used:

CASE expr WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THENreturn_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

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

Conversion:

CASE job when 'President 'then sal + 1000

When 'manager' then sal + 800

Elsesal + 500

END

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

Select ename, job, sal salary before going up,

(

CASE job when 'President 'then sal + 1000

When 'manager' then sal + 800

Elsesal + 500

END

)

Post-increase salary

From emp

Bytes -----------------------------------------------------------------------------------------------------

Result:

Ename job salary after salary increase

---------------------------------------

Smith clerk 800 1300

Allen salesman 1600 2100

Ward salesman 1250 1750

Jones manager 2975 3775

Martin salesman 1250 1750

Blake manager 2850 3650

Clark manager 2450 3250

Scott analyst 3000 3500

King president 5000 6000

Turner salesman 1500 2000

Adams clerk 1100 1600

James clerk 950 1450

Ford analyst 3000 3500

Miller clerk 1300 1800

14 rows have been selected.

Method B

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

Syntax:

DECODE (col | expression, search1, result1

[, Search2, result2,...,]

[, Default])

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

Conversion:

Decode (job, 'President ', sal + 10000, 'manager', sal + 800, sal + 500)

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

Select ename, job, sal salary before going up,

(

Decode (job, 'President ', sal + 10000, 'manager', sal + 800, sal + 500)

)

Post-increase salary

From emp;