Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to understand multi-table query, group query, sub-query in Oracle database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Multi-table query

FROM

The performance is not good, a large number of big data is not recommended to use

Multi-table query: set multiple data tables after the FROM clause

Cartesian product: multiplies two tables

Use an alias if the table name is long

Connection of tables

SELECT *

FROM emp e,dept d

WHERE e.des=d.des

Two forms of table joins:

Internal connection (equivalent connection): all data that meets the criteria will be displayed.

The outer connection (left connection, right connection, full connection) controls whether all the data of the left table and the right table are displayed. (+) tags are available only for Oracle

SELECT *

FROM emp e,dept d

WHERE e.des=d.des (+)

/ / left outer connection to display the contents of all emp tables

1999 Grammar

Left outer connection

SELECT * FROM emp LEFT OUTER JOIN dept USING (deptno)

Full external connection

SELECT * FROM emp FULL OUTER JOIN dept USING (deptno)

Grouping query

GROUB BY

Select d. Deptno. D. Dname. d. Loco trunc (avg (months_between (sysdate,e.hiredate) / 12)) years

From dept d,emp e

Group by d.deptno,d.dname,d.loc

Oracle executes statements and order

5 determine the data to use: SELECT

1 determine the data source to find: FROM

two。 Filtering for data rows: WHERE

3 for data grouping: GROUP BY

4 filter the grouped data: HAVING

6 sort the returned data: ORDER BY

The difference between WHERE and HAVING:

WHERE appears before the GROUP BY operation, that is, data filtering before grouping, and statistical functions are not allowed.

HAVING appears after GROUP BY and filters the grouped data. You can use statistical functions.

The difference between UNION and UNION ALL:

Union: merge two result sets, excluding duplicate rows, equivalent to distinct, and sort the default rules at the same time

Union all: joins two result sets, including duplicate rows, that is, all results are displayed, whether duplicated or not

Subquery

Multiple subqueries can be nested at the same time

No rows selected may be returned empty by subquery

Function: to solve the performance problem of Cartesian product caused by multi-table query.

SELECT clause

Return to a single row and column, using less

FROM clause

Returns multiple rows and columns (table structure)

SELECT d.dname,d.loc,count

FROM dept d, (SELECT deptno,COUNT (empno) count from emp GROUP BY deptno) temp

WHERE d.deptno=temp.deptno (+)

WHERE clause (most frequently used)

Returns single row, single column, single row, multiple columns, multiple rows, single column

Function: filtering of data rows, use of filter criteria

Single row, single column:

SELECT *

FROM emp

WHERE sal > (SELECT AVG (sal) FROM emp)

Multiple columns in a single row:

SELECT * Wuxi Gynecological expenses http://www.wxbhnkyy39.com/

FROM emp

WHERE (sal,job) = (SELECT sal,job FROM emp WHERE ename='SCOTT')

Multiple rows and single column:

Three operators IN,ANY,ALL

IN operation

This condition is the same as the sal in the subquery.

SELECT *

FROM emp

WHERE sal IN (SELECT sal FROM emp WHERE ename='SCOTT')

ANY

> ANY ()-if it is greater than the minimum value, it is satisfied.

= ANY ()-equals one of the values is satisfied

SELECT *

FROM emp

WHERE sal=ANY (SELECT sal FROM emp WHERE job='CLERK')

ALL

> ALL ()-larger than the maximum value obtained by the query

SELECT *

FROM emp

WHERE sal > ALL (SELECT sal FROM emp WHERE job='CLERK')

HAVING clause

Returns a single row and column, indicating that you want to use a statistical function

SELECT job,COUNT (ename), AVG (SAL)

FROM emp

GROUP BY job

HAVING AVG (sal) > (SELECT AVG (sal) FROM emp)

This is the end of the content of "how to understand multi-table query, group query and sub-query in Oracle database". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.