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

This article mainly shows you the "what is the general function in the Oracle database", the content is easy to understand, clear, hope to help you solve your doubts, the following let Xiaobian lead you to study and learn "what is the common function in the Oracle database" this article.

1. Table structure under Scott users

SCOTT . Is the name of a sample user in the Oracle database. Its function is to provide some simple application examples for beginners, but it is locked by default. During installation, according to the needs of the user, after the completion of the "Database configuration Assistant" interface, the pop-up dialog box-password management, unlocked inside.

SCOTT is a sample user within ORACLE. The default password is tiger, and there are tables emp, dept and so on below. The relationships between these tables and tables demonstrate some basic principles of relational databases.

1. If you don't have a Scoot table, you can create one yourself.

(1) create DEPT table

CREATE TABLE DEPT (DEPTNO NUMBER (2) CONSTRAINT PK_DEPT PRIMARY KEY,DNAME VARCHAR2 (14), LOC VARCHAR2 (13))

(2) Table DEPT adds data

INSERT INTO DEPT VALUES (10, 'ACCOUNTING',' NEW YORK'); COMMIT;INSERT INTO DEPT VALUES (20, 'RESEARCH',' DALLAS'); COMMIT;INSERT INTO DEPT VALUES (30, 'SALES',' CHICAGO'); COMMIT;INSERT INTO DEPT VALUES (40, 'OPERATIONS',' BOSTON'); COMMIT

(3) create EMP table

CREATE TABLE EMP (EMPNO NUMBER (4) CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2 (10), JOB VARCHAR2 (9), MGR NUMBER (4), HIREDATE DATE, SAL NUMBER (7), COMM NUMBER (7), DEPTNO NUMBER (2) CONSTRAINT FK_DEPTNO REFERENCES DEPT)

(4) add data to table EMP

INSERT INTO EMP VALUES (7369 paraphrase pensionary CLERKYY'), COMMIT;INSERT INTO EMP VALUES (7499 partridge), COMMIT (1600). INSERT INTO EMP VALUES (7521 memorials WARDMADYY, Salesmans) 7698 (22-2-1981), 1250, 500 (30); COMMIT;INSERT INTO EMP VALUES (756, 756); COMMIT (7839) (2-4-1981) INSERT INTO EMP VALUES (7698 memorials); COMMIT;INSERT INTO EMP VALUES (7698 memorials); COMMIT;INSERT INTO EMP VALUES (7698 memorials); COMMIT INSERT INTO EMP VALUES (7788); COMMIT;INSERT INTO EMP VALUES (7788) (7788); COMMIT;INSERT INTO EMP VALUES (756); COMMIT (756). INSERT INTO EMP VALUES (7839); COMMIT;INSERT INTO EMP VALUES (784); COMMIT;INSERT INTO EMP VALUES (7698); COMMIT (8-9-1981); (1500) INSERT INTO EMP VALUES (7876 memoirs) 7788 memoirs tokyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy`511); COMMIT;INSERT INTO EMP VALUES (7900); COMMIT;INSERT INTO EMP VALUES (76988pencils); COMMIT INSERT INTO EMP VALUES (7902), 766 (766), 766, 766, 766, 766, 766, 766, 766, 766, 766, 766, 766, 766, 766, 766, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7902, 7906, 7902, 7902, 7902, 7904, 7904, 772, 772, 772, and COMMIT).

(5) create SALGRADE table

CREATE TABLE SALGRADE (GRADE NUMBER, LOSAL NUMBER, HISAL NUMBER)

(6) Table SALGRADE adds data

INSERT INTO SALGRADE VALUES (1); COMMIT;INSERT INTO SALGRADE VALUES (2); COMMIT;INSERT INTO SALGRADE VALUES (3) 1401); COMMIT;INSERT INTO SALGRADE VALUES (4); COMMIT;INSERT INTO SALGRADE VALUES (5); COMMIT

(7) create BONUS table

CREATE TABLE BONUS (ENAME VARCHAR2 (10), JOB VARCHAR2 (9), SAL NUMBER, COMM NUMBER)

Second, one-line function 1, character function

Receive character input returns a character or numeric value, dual is a pseudo table

(1) convert lowercase characters to uppercase characters

-- (1) convert lowercase characters to uppercase characters select upper ('smith') from dual

(2) change uppercase characters to lowercase characters

-- (2) change uppercase characters to lowercase characters select lower ('WHJ') from dual

2. Numerical function

(1) rounding function: round ()

By default, ROUND rounds up, and you can specify the number of bits to keep.

The first decimal place of the rounding function is less than 5

The first decimal place of the rounding function is less than 5select round (5.342345) from dual

The first decimal place of the rounding function is greater than 5

The first decimal place of the rounding function is less than 5select round (5.342345) from dual

Rounding function keeps two decimal places

-- rounding function decimal point retains two select round (5.12764) from dual

(2) date function

Oracle provides a lot of date-related functions, including date addition and subtraction, and there are some rules in date addition and subtraction.

Date-number = date

Date + number = date

Date-date = number

Example: query the number of weeks an employee has entered the company. (analysis: query the number of days an employee has entered the company (sysdate-entry date) / 7 is the number of weeks)

-- inquire about the number of weeks employees have entered the company. (analysis: query the number of days employees have entered the company (sysdate-entry date) / 7 is the number of weeks)-- 1. Employee table select * from emp;--2. Query the number of weeks ward enters the company select Ename,round ((sysdate-hiredate) / 7) from emp where Ename='WARD'

Get the number of months in two time periods: MONTHS_BETWEEN ()

Example: query the number of months all employees have entered the company

-- query the number of months that all employees have entered the company select ename,round (months_between (sysdate,hiredate)) as has entered the company from emp

(3) conversion function

TO_CHAR: string conversion function

Example: querying all employees will separate the year, month and day, at this time you can use the TO_CHAR function to split

Wildcard characters are required when splitting

Year: y, year is four people using yyyy

Month: M, month is the two people who use mm

Day: d, day is the two of you using dd

Query all employees will be separated from the year, month and day.

-- query all employees will separate the year, month and day into select empno,ename, to_char (hiredate,'yyyy') as year, to_char (hiredate,'mm') as month, to_char (hiredate,'dd') as day from emp

Date change date format to yyyy-mm-dd string format

-- initial format select * from emp;-- date change date format to yyyy-mm-dd string format select empno,ename,to_char (hiredate,'yyyy-mm-dd') from emp

In the result, 0 is added before the month below 10, and the leading 0 can be removed by using fm.

-if 0 is added before the month below 10 in the result, you can use fm to remove the front 0select empno,ename,to_char (hiredate,'fmyyyy-mm-dd') from emp.

TO_DATE: date conversion function

TO_DATE can convert the data of a string to a date type

-- TO_DATE can convert the data of a string to a date type select to_date ('2022-03-10 as as current date from dual

(4) General function

Null value handling nvl

Example: query the annual salaries of all employees

-- query the annual salary of all employees comm year-end bonus select ename,sal*12+comm from emp

We find that the annual salary of many employees is empty, because the bonus of many employees is null,null and any numerical calculation.

Null, at this point we can use nvl to deal with

-- query the annual salary of all employees comm year-end bonus select ename,nvl (comm,0) year-end bonus, sal*12+nvl (comm,0) annual salary from emp

Decode function

-- this function is similar to if....else if...esle-- syntax: DECODE (col/expression, [search2,result1], [search3, result2].... [default]) Col/expression: column name or expression 1. Search2,search3...: condition for comparison 2. Result1, result2...: returns value 3. Return resulti if col/expression and Searchi match, otherwise return the default value of default-- decode function-- 1. I'm 1select decode. I'm 1pm. I'm 2pm. I'm nameless.) from dual;--2. I'm 2select decode. (2) I'm 2, I'm 2, I'm 2) from dual;--3. I am a nameless select decode. (3) I am a nameless from dual.

Example: find out the Chinese names of all employees' positions

Select ename,decode (job, 'clerk',' salesman', 'SALESMAN',' sales', 'PRESIDENT',' president', 'MANAGER',' manager', 'NALYST',' analyst', 'employee') from emp

Case when

CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END

Example: find out the Chinese names of all employees' positions

-- example: Case when queries out the Chinese names of all employees' positions, select t.empnoret.ename Case when T. jobless clerk 'then' salesman 'when T. jobless' Salesan 'then' sales' when t.jobless' PRESIDENT' then 'president' when T. jobless' Manager` then 'manager' when t.jobless' NALYST'then 'analyst' else 'employee' end from emp t

3. Multiline function (aggregate function) 1. Number of statistical records

Example: query the number of records of all employees

Example: query the number of records of all employees select count (*) from emp

Count (*) is not recommended and a specific column can be used so as not to affect performance.

-- count (*) is not recommended. You can use a specific column so as not to affect performance. Select count (ename) from emp;2, minimum query min ()

Example: find out the minimum wage for employees

-- example: query the employee minimum wage select min (sal) from emp;3, maximum query max ()

Example: find out the maximum wage of an employee

-- example: query the employee's maximum wage select max (sal) from emp;4, average query avg ()

Example: find out the average salary of the employee

Example: query the average salary of employees select avg (sal) from emp;5, summation function

Example: find out the total salary of the employees in department 20

-- example: find out the total salary of employees in department 20 select sum (sal) from emp where deptno=20; 4. Group statistics

GROUP BY is required for grouping statistics.

-- Syntax: SELECT * | column name FROM table name {WEHRE query condition} {GROUP BY grouping field} ORDER BY column name 1 ASC | DESC, column name 2...ASC | DESC

Example: query the number of people in each department

Example: query the number of people in each department select deptno,count (ename) from emp group by deptno

Example: find out the average salary of each department

-- example: query the average salary of each department select deptno,avg (sal) from emp group by deptno

If we want to find out the department number and the number of people under the department

If we want to find out the department number, and the number of people under the department select deptno,count (ename) from emp

We found out that we reported a mistake in ORA-00937.

Note:

1. If you use grouping functions, SQL can only query GOURP BY grouping condition fields and grouping functions, not its

His field.

two。 If you use a grouping function, you can only query the value of the grouping function without using GROUP BY

Example: group by department to find out the name of the department and the number of employees in the department

-- example: grouped by department, query the department name and the number of employees in the department select d.deptnored.dnamememcount (e.ename) from emp eDet dwhere e.deptno=d.deptno group by d.deptnorecod.dname.

Example: find out the departments with more than 5 people

-- example: query departments with more than 5 people. (analysis: count (ename) needs to be conditioned. Where cannot be used in this query at this time, but HAVING can be used.) select d.deptno having count d.dnamejery count (e.ename) from emp dept d where e.deptno=d.deptno group by d.deptnod dname having count (e.ename) > 5

Analysis: count (ename) needs to be conditioned. Where cannot be used in this query, but HAVING can be used.

Example: find out the departments whose average salary is more than 2000

-- example: find out the departments whose average salary is greater than 2000. Select d.deptno having avg d.dnameno from dept d, emp e where d.deptno=e.deptno group by d.deptnored.dname having avg (e.sal) > 2000

These are all the contents of the article "what are the common functions in the Oracle database?" Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.