Oracle Database elementary learning 2, oracle Database

Oracle Database elementary learning 2, oracle Database

Today we will introduce the remaining query methods in the Oracle database. Today's query methods will be more complex than yesterday's (PS: I am also a beginner, please forgive me ..).

I. Grouping Functions

The grouping function is created to distinguish different data in the same table. The keyword is group by, which can contain any number of columns.

Example:

Select deptno. MAX (SAL) from emp group by deptno;

Note that once a GROUP is used, the values before and after the GROUP must be consistent. That is to say, if group by is grouped based on DEPTNO, the SELECT statement must also have DEPTNO.

　　Besides, group functions are not supported. That is to say, SELECT can be followed by any group function.

　　Do you still remember what group functions are there?

• Avg () à returns the average value of a column • min () à returns the minimum value of a column • max () à returns the maximum value of a column • sum () à return the sum of values in a column • count () à return the number of rows in a column. Below are some specific instances. Select deptno. sal from emp group by deptno, SAL)

Select deptno, MIN (SAL) from emp group by deptno;

Select comm, COUNT (*) from emp group by comm order by comm;

Select deptno, AVG (SAL) from emp group by deptno order by avg (SAL );

　　

　　

　　

 

Note: The group by clause must appear after the where clause. Before the order by clause, if the group by clause still needs to be filtered, the keyword is used.HAVING.

That is to say, the SELECT statement can be written as SELECT * FROM * WHERE * group by * HAVING * order;

The specific execution sequence of the program isFROM WHERE GROUP BY HAVING SELECT ORDER

Considering program optimization, HAVING is definitely not required for data that can be filtered using WHERE.

 

--- Calculate the highest salary of each JOB in 30 departments, and sort the salary by JOB.

Select job, MAX (SAL) from emp where deptno = 30 group by job order by job;

--- Query the average salary of each department and job

Select deptno, JOB, AVG (SAL) from emp group by deptno, job order by deptno;

-- Execute WHERE before executing GROUP

--- Query the average salary of employees with A name in each department for each job, and the average salary is greater than 1500

Select deptno, JOB, AVG (SAL) from emp where ename like '% A %' group by deptno, job having avg (SAL)> 1500;

 

2. Multi-table join query

　　Currently, the mainstream SQL statements include SQL92 and SQL99. First, we will introduce SQL 92.

　　

/*
　　SQL92 multi-table join query
Equijoin
Non-equivalence Association
External Connection
Self-connection

*/

　　------- Equijoin

　　

SELECT * from emp;
SELECT * from dept;
SELECT * from emp, DEPT;
SELECT * from emp, dept where emp. DEPTNO = DEPT. DEPTNO;
Select emp. ENAME, EMP. DEPTNO, DEPT. dname from emp, dept where emp. DEPTNO = DEPT. DEPTNO;
Select e. ENAME, E. DEPTNO, D. DNAME, D. loc from emp e, dept d where e. DEPTNO = D. DEPTNO;

 

Select e. ENAME, D. loc from emp e, DEPT D
Where e. DEPTNO = D. DEPTNO
And e. ename like '% A %'
And d. deptno in (10, 30 );

 

------ Non-equivalent Association

　　

Select e. ENAME, E. SAL, S. grade from emp e, SALGRADE S
Where e. sal between s. losal and s. HISAL;

　　

　　At least N-1 conditions are required to connect N tables.

 

　　

Some data may be lost due to the absence of common conditions for the two tables during equivalent association. This requires external connections.
　--- External join is used to solve the problem of data in one table and no data in the other table.

In SQL 92, outer join is divided into left Outer Join and right outer join.

SELECT * from emp e, DEPT D
Where e. DEPTNO = D. DEPTNO (+ );

This is a left Outer Join, which can display the data in EMP but not in the DEPT table.

 

SELECT * from emp e, DEPT D
Where e. DEPTNO (+) = D. DEPTNO;

This is the right outer connection, which can display data in EMP, DEPT

If you do not have good memory, you can write down the following sentence.

　Add a plus sign next to the other party to display the person who wants to display the information.

What's the difference?

 

　

---- Self-join is used to solve the problem that two columns to be matched are on the same table.

Select e. ENAME, M. ename from emp e, EMP M
Where e. MGR = M. EMPNO;

 

　99 syntax
Cartesian Product
Equijoin
Naturally: classes of the same name and type are automatically connected to the same type for equality.
Easy to use
Potential risks (the table structure may be modified)
Field
USING
Non-equivalent join
ON
External Connection
Left outer
Outer right
All

　　

SELECET * from emp e, dept d; (92 type)

SELECT * from emp e cross join dept d; (99 type)

Use cross join instead,

 

SELECT * from emp e, dept d where e. DEPTNO = D. DEPTNO; (92 type)

SELECT * from emp e natural join dept d; (99 type)

Natural join becomes a natural join and automatically performs equivalent JOIN for classes of the same name and type. The disadvantage is that there may be risks (the table structure may be modified)

Therefore, we can often use the following method:

 

Select deptno e. ENAME, D. dname from emp e join dept d using (DEPTNO); (99 type) (automatically associate with DEPTNO, and cannot add a qualified word, that is, cannot add E. or D)

SLEECT * from emp e, dept d where e. DEPTNO = D. D. DEPTNO; (92 type)

 

 

SELECT * from emp e, salgrade s where e. sal between s. losal and s. HISAL;

SELECT * from emp e join salgrade s on (E. sal between s. losal and s. HISAL );
(The keyword join on replaces)

 

------ External connection

--- Left Outer
SELECT * from emp e, DEPT D
Where e. DEPTNO = D. DEPTNO (+ );

SELECT * from emp e left outer join dept d using (DEPTNO );
(Keyword outjoin using)

--- Outer right
SELECT * from emp e, DEPT D
Where e. DEPTNO (+) = D. DEPTNO;

SELECT * from emp e right outer join dept d using (DEPTNO );
(Keyword outer join using)

--- 99 features, 92 none

SELECT * from emp e full outer join dept d using (DEPTNO );

 

--- Self-connection

Select e. ENAME, M. ename from emp e join emp m on (E. MGR = M. EMPNO); (99 type)
(Keyword: join on) instead, where

Select e. ENAME, M. ename from emp e, EMP M
Where e. MGR = M. EMPNO; (92 type)

 

/*

Subquery
Returns a row from a single row subquery.
Multi-row subquery

Only three operators can be used IN SOME ALL

Abstracts the query results into a table.
*/

Select max (SAL) from emp;
Select ename from emp where sal = (select max (SAL) from emp );
The key to understanding subqueries is to treat subqueries as a table. The outer statement can use the result returned by the nested subquery as a table.
The subquery should be enclosed in parentheses.
Place the subquery on the right of the comparison operator to enhance readability)

Select ename from emp where sal >=( select avg (SAL) from emp );

SELECT * from emp where deptno = 30;

SELECT * from emp where deptno <> 30 and sal in (select sal from emp where deptno = 30 );

SELECT * from emp where deptno <> 30 AND> = sal some (select sal from emp where deptno = 30 );

SELECT * from emp where deptno <> 30 and sal> = ALL (select sal from emp where deptno = 30 );

Some only needs to be greater than the minimum option
ALL is greater than the maximum

SELECT * from emp where sal> = ALL (select sal from emp where deptno = 20) and deptno = 20; (max)

SELECT * from emp where sal> = some (select sal from emp where deptno = 20) and deptno = 20; (minimum)

Select deptno, AVG (SAL) from emp where deptno is not null group by deptno;

Select da. DEPTNO, S. grade from salgrade s (
Select deptno, AVG (SAL) from emp where deptno is not null group by deptno) DA
Where da. avgsal between s. losal and s. HISAL;