Shulou(Shulou.com)06/01 Report--

String function is one of the most widely used functions in oracle (the table is the table in the introduction to sql query (2).

A, LOWER (parameter): make the parameter lowercase

For example: query employee information with the name scott (case-insensitive query)

SQL > select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where lower (ename) = 'scott'

The result of the output is:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20

B, UPPER (parameter): make the parameter uppercase

For example: query employee information with the name scott (case-insensitive query)

SQL > select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp where upper (ename) = 'scott'

The result of the output is:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20

C, INITCAP (parameter): capitalize the first letter of the parameter

For example, find the information that the employee number is 7788, and his name is displayed in uppercase.

SQL > select empno,initcap (ename), job,mgr,hiredate,sal,comm,deptno from emp where empno='7788'

The display result is:

EMPNO INITCAP (ENAME) JOB MGR HIREDATE SAL COMM DEPTNO

--

7788 Scott ANALYST 7566 1982-12-9 3000.00 20

D, LENGTH (parameter): returns the length of the parameter

For example: output the length of job with employee number 7788

SQL > select length (job) as "7788 length of job" from emp where empno=7788

The results displayed are as follows:

7788 of job's length

-

seven

E, CONCAT (parameter 1, parameter 2): connect parameter 1 and parameter 2

For example, 7788 of the employee's salary is shown as: salary + bonus

SQL > select empno,ename,job,mgr,hiredate,concat (sal,comm) as "salary", deptno from emp where empno='7788'

The results displayed are as follows:

EMPNO ENAME JOB MGR HIREDATE salary DEPTNO

7788 SCOTT ANALYST 7566 1982-12-9 3000 20

F, SUBSTR (number of parameters, start, intercept): intercept parameters

For example: output the last three digits of job with employee number 7788

SQL > select empno,ename,job,substr (job,length (job)-2 job 3) the last three digits of as "job" from emp where empno=7788; or:

The select empno,ename,job,substr (job,length (job)-2) as "the last three digits of job" from emp where empno=7788; shows the following results:

The last three of EMPNO ENAME JOB job

--

7788 SCOTT ANALYST YST

G, INSTR (parameter, letter): returns the position where the letter first appears

For example: find the location of'T'in the name of user 7788

SQL > select empno,ename,instr (ename,'T') as "location where T first appeared" from emp where empno=7788

The results displayed are as follows:

Where EMPNO ENAME T first appeared

7788 SCOTT 4

H, LPAD (parameter, length, preceding parameter letters): complete to the left

For example: find that the salary of the user is 7788, if it is not enough, add 0 in front of it.

SQL > select empno,ename,sal,lpad (sal,6,'0') as "salary is in 6-digit format" from emp where empno=7788

The results displayed are as follows:

EMPNO ENAME SAL salary is in a six-digit format.

-

7788 SCOTT 3000.00 003000

I, RPAD (parameter, length, complete parameter letters after): complete to the right

For example: find that the salary of the user is 7788, if it is not enough, add 0 in front of it.

SQL > select empno,ename,sal,rpad (sal,6,'0') as "salary is in 6-digit format" from emp where empno=7788

The results displayed are as follows:

EMPNO ENAME SAL salary is in a six-digit format.

-

7788 SCOTT 3000.00 300000

J, TRIM ([both | | leadingtrailing | |] letter from parameter): remove the specified letter from the parameter

For example: find the name of an employee whose employee number is 7788 after removing "T"

SQL > select empno,ename,trim ('T'from ename) as "after T" from emp where empno=7788

Or

SQL > select empno,ename,trim (both 'T'from ename) as "after T" from emp where empno=7788

The results displayed are as follows:

After removing T from EMPNO ENAME,

7788 SCOTT SCO

For example: find the name of an employee whose employee number is 7788 after removing the "T" on the left.

SQL > select empno,ename,trim (leading 'T'from ename) as "after T" from emp where empno=7788

Or:

SQL > select empno,ename,ltrim (ename,'T') as "after T" from emp where empno=7788

The results displayed are as follows:

After removing T from EMPNO ENAME,

7788 SCOTT SCOTT

For example: find the name of an employee whose employee number is 7788 after removing the "T" on the right.

SQL > select empno,ename,trim (trailing 'T'from ename) as "after T" from emp where empno=7788

Or:

SQL > select empno,ename,rtrim (ename,'T') as "after T" from emp where empno=7788

The results displayed are as follows:

After removing T from EMPNO ENAME,

7788 SCOTT SCO

K, REPLACE: (parameter, parameter [, parameter]): the first parameter Operand, the second is the character to be found, the third is the character to be replaced, and if there is no third, delete the character to be found.

For example, replace'T 'with' L'in the name of employee number 7788.

SQL > select empno,ename,replace (ename,'T','L') as "after replacement" from emp where empno=7788

The results displayed are as follows:

After EMPNO ENAME replacement

7788 SCOTT SCOLL

II. Digital function

A, ROUND (parameter): rounded

For example: find 12.89 rounded

SQL > select round (12.89) from dual

The results displayed are as follows:

ROUND (12.89)

-

thirteen

B, TRUNC (parameter 1 [, parameter 2]): truncate. The first parameter is the number to be operated, and the second parameter is optional. If not, the integer part is truncated. If parameter 2 is positive, truncate the number of 'parameter 2' to the right of the decimal point. If it is negative, truncate the number of parameter 2 to the left of the decimal point.

For example: in the default way

SQL > select trunc (12.89) from dual

The results displayed:

TRUNC (12.89)

-

twelve

For example, when the second parameter is positive

SQL > select trunc (12.89) from dual

The results displayed:

TRUNC (12.89 and 1)

-

12.8

For example, when the second parameter is negative

SQL > select trunc (12.89) from dual

The results displayed:

TRUNC (12.89 Mai Mui 1)

-

ten

C, MOD (parameter 1, parameter 2): find the remainder of parameter 1 divided by parameter 2

For example: 100 divided by the remainder of 30

SQL > select mod (100Pol 30) from dual

The results displayed:

MOD (100pi 30)

-

ten

D, ABS (parameter): find the absolute value of the parameter

For example: find the absolute value of-10

SQL > select abs (- 10) from dual

The results displayed:

ABS (- 10)

-

ten

E, CEIL (parameter): returns the smallest integer greater than or equal to the parameter

SQL > select ceil (- 10.23) from dual

The results displayed:

CEIL (- 10.23)

-

-10

F, FLOOR (parameter): returns the largest integer less than or equal to the parameter

For example: find the downward rounding of-10.23

SQL > select floor (- 10.23) from dual

The results displayed:

FLOOR (- 10.23)

-

-11

G, SQRT (parameter): the negative square root of the returned parameter is meaningless.

For example: find the square root of 4

SQL > select sqrt (4) from dual

The results displayed:

SQRT (4)

-

two

Date function: date data in Oracle actually contains two values: date and time. The default date format is DD-MON-RR. The date-time function is used to return the date and time of the current system, as well as to process data of the date and time type.

A, sysdate (); gets the current date of the system

For example:

SQL > select sysdate from dual

The result displayed is:

SYSDATE

-

2011-4-8 13

B, current_timestamp (); get the current time and date values

For example:

SQL > select current_timestamp from dual

The result displayed is:

CURRENT_TIMESTAMP

08-April-11 01.38.27.546000 p.m. + 08:00

C, add_months (date,count): add count months to the specified date

For example: output the current time plus the time after 3 months

SQL > select add_months (sysdate,3) from dual

Display the results:

ADD_MONTHS (SYSDATE,3)

-

2011-7-8 13:43:06

D, last_day (date); returns the last day of the month where the date date is located

For example:

SQL > select sysdate,last_day (sysdate) from dual

Display the results:

SYSDATE LAST_DAY (SYSDATE)

--

2011-4-8 13 2011-4-30 13:51:2

E, months_between (date1,dates); returns the number of months between date1 and date2

For example:

SQL > select sysdate,months_between (sysdate,add_months (sysdate,3)) from dual

Display the results:

SYSDATE MONTHS_BETWEEN (SYSDATE,ADD_MON

2011-4-8 13-3

F, new_time (date,'this','other'); convert time date from this time zone to other time zone

For example:

SQL > select sysdate,new_time (sysdate,'GMT','AST') from dual

Display the results:

SYSDATE NEW_TIME (SYSDATE,'GMT','AST')

2011-4-8 13 2011-4-8 9:

G, next_day (day,'week'); returns the specified date or the first day of the last week, where day is the day of the week

For example:

SQL > select sysdate,next_day (sysdate,' Friday') from dual

Display the results:

SYSDATE NEXT_DAY (SYSDATE,' Friday')

2011-4-8 13 2011-4-15 13:53:10

H, round (parameter, intercept type): rounding the date

For example: rounding the current time year

SQL > select sysdate,round (sysdate,'yyyy') from dual

Display the results:

SYSDATE ROUND (SYSDATE,'YYYY')

--

2011-4-9 22 2011-1-1

For example, the current time month is rounded

SQL > select sysdate,round (sysdate,'mm') from dual

Display the results:

SYSDATE ROUND (SYSDATE,'MM')

--

2011-4-9 22 2011-4-1

For example: round the current time day

SQL > select sysdate,round (sysdate,'dd') from dual

The results displayed:

SYSDATE ROUND (SYSDATE,'DD')

--

2011-4-9 22 2011-4-10

I, to_char (parameter, type of conversion): date interception

For example: intercept the year of the current time

SQL > select sysdate,to_char (sysdate,'yyyy') from dual

Display the results:

SYSDATE TO_CHAR (SYSDATE,'YYYY')

2011-4-9 22 2011

For example: intercept the month of the current time

SQL > select sysdate,to_char (sysdate,'mm') from dual

Display the results:

SYSDATE TO_CHAR (SYSDATE,'MM')

--

2011-4-9 22 04

For example: intercept the date of the current time

SQL > select sysdate,to_char (sysdate,'dd') from dual

Display the results:

SYSDATE TO_CHAR (SYSDATE,'DD')

--

2011-4-9 22 09

Mathematical operation of the date:

Add or subtract a number from a date and the result is still the date.

The number of days between the two dates subtracted from the return date.

You can divide the number by 24 to add or subtract hours from the date.

General function

These functions apply to any data type, as well as to null values:

A, NVL (expr1, expr2): displays expr2 if expr1 is empty

For example: check emp if bonus comm is empty, replace it with 0

SQL > select empno,ename,job,mgr,hiredate,sal,nvl (comm,0), deptno from emp

Display the results:

EMPNO ENAME JOB MGR HIREDATE SAL NVL (COMM,0) DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 800.00 0 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 0 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 0 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 0 10

7788 SCOTT ANALYST 7566 1982-12-9 3000.00 0 20

7839 KING PRESIDENT 1981-11-17 5000.00 0 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0 30

7876 ADAMS CLERK 7788 1983-1-12 1100.00 0 20

7900 JAMES CLERK 7698 1981-12-3 950.00 0 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 0 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 0 10

14 rows selected

B, NVL2 (expr1, expr2, expr3): show expr2 if expr1 is not empty, and expr3 if expr1 is empty

For example: find the salary of the employee whose employee number is 7521, and show the salary plus bonus if the comm is not empty.

SQL > select sal,nvl2 (comm,sal+comm,sal) from emp where empno=7521

Display the results:

SAL NVL2 (COMM,SAL+COMM,SAL)

1250.00 1750

C, NULLIF (expr1, expr2): expr1 equals expr2 returns NULL, unequal returns expr1

For example: check the salary and bonus of 7788 of the employee, return null if the salary and bonus are equal, otherwise return salary

SQL > select sal,comm,nullif (sal,comm) from emp where empno=7788

Display the results:

SAL COMM NULLIF (SAL,COMM)

3000.00 3000

D, COALESCE (expr1, expr2,..., exprn): if the first expression is empty, the next expression is returned. The advantage of COALESCE over NVL is that COALESCE can handle multiple alternating values at the same time.

For example: tested in the emp table

To test, add a record to the emp table:

SQL > insert into emp (empno,ename,deptno) values (7978)

Test statement:

SQL > select empno,ename,job,mgr,hiredate,sal,comm,deptno,coalesce (comm,sal,1000) as "Test results" from emp

Display the results:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO test results

7369 SMITH CLERK 7902 1980-12-17 800.00 20 800

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30 300

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30 500

7566 JONES MANAGER 7839 1981-4-2 2975.00 20 2975

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30 1400

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30 2850

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10 2450

7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20 3000

7839 KING PRESIDENT 1981-11-17 5000.00 10 5000

7844 TURNER SALESMAN 7698 1981-9-8 1500.00 300 0

7876 ADAMS CLERK 7788 1983-1-12 1100.00 20 1100

7900 JAMES CLERK 7698 1981-12-3 950.00 30 950

7902 FORD ANALYST 7566 1981-12-3 3000.00 20 3000

7934 MILLER CLERK 7782 1982-1-23 1300.00 10 1300

7978 guo 10 1000

15 rows selected

Combination function:

The grouping function acts on a set of data and returns a value for a set of data.

A, AVG (field name): find the average of this field

For example, find out the average salary in the emp table

SQL > select avg (nvl (sal,0)) from emp

Display the results:

AVG (NVL (SAL,0))

-

1935

B, COUNT (field name): find the total record in this field

For example: query several records in the emp table

SQL > select count (*) from emp

Display the results:

COUNT (*)

-

fifteen

C, MAX (field name): find the maximum value of this field

For example: the employee who finds the highest salary in the emp table

SQL > select max (nvl (sal,0)) from emp

Display the results:

MAX (NVL (SAL,0))

-

5000

D, MIN (field name): find the minimum value of this field

For example: employees who find the lowest wage in the emp table

SQL > select min (nvl (sal,0)) from emp

Display the results:

MIN (NVL (SAL,0))

-

0

E, SUM (field name): find the sum of the field

For example, how much salary does an emp need to pay?

SQL > select sum (sal) + sum (comm) as "Total salary" from emp

Display the results:

MIN (NVL (SAL,0))

-

0

Illegal use of group functions

Columns that are included in the SELECT list but not in the group function must be included in the GROUP BY clause.

For example:

SQL > select empno,count (job) from emp

The correct writing is as follows:

SQL > select empno,count (job) from emp group by empno

B. you cannot use group functions in the WHERE clause (note).

For example: SQL > select deptno from emp where count (job) > 0 group by deptno

Note: ORA-00934: grouping functions are not allowed here

Group by statement

If you need to group by the value of a column during the query to count the data within that group, use the group by clause. You can use the group by clause regardless of whether select uses the where clause or not.

Note: the group by clause must be used in conjunction with the grouping function, otherwise it doesn't make sense.

Find out the number of employees in each department

SQL > select count (*) from emp group by deptno

The results displayed:

COUNT (*)

-

six

five

four

Calculate the average salary of the middle employees in each department

SQL > select avg (nvl (sal,0)) from emp group by deptno

The results displayed:

AVG (NVL (SAL,0))

-

1566.6666666666

2175

2187.5

Having clause

The HAVING clause sets conditions on the GROUP BY clause in a similar way to the interaction between the WHERE clause and the SELECT statement. The WHERE clause search condition is applied before the grouping operation, while the HAVING search condition is applied after the grouping operation. The HAVING syntax is similar to the WHERE syntax, but HAVING can contain aggregate functions. The HAVING clause can refer to any item that appears in the selection list.

Note: having clause is usually used in conjunction with group by clause

Syntax:

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column]

A. the number of employees in the inquiry department is greater than the number of five departments.

SQL > select deptno,count (*) from emp group by deptno having count (*) > 5

Display the results:

DEPTNO COUNT (*)

--

30 6

Note: grouping functions can be nested

Order by statement

The ORDER BY clause is at the end of the SELECT statement. Sort using the ORDER BY clause: ASC (ascend): ascending; DESC (descend): descending. The default is ASC ascending order

Query employee information according to the department number in ascending order

SQL > select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by empno

Or:

SQL > select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by empno asc

The results displayed:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20

7839 KING PRESIDENT 1981-11-17 5000.00 10

7844 TURNER SALESMAN 7698 1981-9-8 1500.00

7876 ADAMS CLERK 7788 1983-1-12 1100.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

7978 guo 10

15 rows selected

Query employee information in descending order according to the department number

SQL > select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by empno desc

The results displayed:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7978 guo 10

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7876 ADAMS CLERK 7788 1983-1-12 1100.00 20

7844 TURNER SALESMAN 7698 1981-9-8 1500.00

7839 KING PRESIDENT 1981-11-17 5000.00 10

7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

7369 SMITH CLERK 7902 1980-12-17 800.00 20

15 rows selected

Query by department ascending order and employee number descending order

SQL > select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by deptno asc,empno desc

The results displayed:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7978 guo 10

7934 MILLER CLERK 7782 1982-1-23 1300.00 10

7839 KING PRESIDENT 1981-11-17 5000.00 10

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

7902 FORD ANALYST 7566 1981-12-3 3000.00 20

7876 ADAMS CLERK 7788 1983-1-12 1100.00 20

7788 SCOTT ANALYST 7566 1982-12-9 3000.00 20

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7900 JAMES CLERK 7698 1981-12-3 950.00 30

7844 TURNER SALESMAN 7698 1981-9-8 1500.00

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30

7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

15 rows selected

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.