Advanced Query method for Oracle Database Multi-Table federated query

　　Oracle queries

　　　　One, simple query

ORDER BY

Select T.sno, T.sname, t.ssex from STUDENT t ORDER by

Group BY Enhanced Edition

SELECT P.toma, P.ptype, SUM (P.lastcou) from product P GROUP by Rollup (P.toma, P.ptype)

　　　　Second, advanced query (multi-table connection query)

The concept of Cartesian product:

The so-called Cartesian product, the popular point is refers to contains two sets of arbitrary two elements of the composition of a set. If R has a tuple of M and S has a tuple n, then R and S's Cartesian card

The number of tuples contained in the product is m*n. This rule can be extended to multiple relationships.

Classification:

Internal connection: Select S.sname, r.cno,r.degree from student S, score R where S.sno=r.sno

Connection of equivalent connection------with =

No equivalent connection

External connection (Nothing to write is the inner connection, directly write two table name)---Left outer connection, right outer connection

SELECT * FROM student s left outer joins course C on S.sno=c.cno

Use of (+) modifiers

Select S.sname, r.cno,r.degree from student S, score R where S.sno=r.sno (+)----results equivalent to outer joins

Use of data dictionaries

Self-connect (cheat yourself): select * from Z_course C1, Z_course C2 where C1.cour_code=c2.p_cour_code

Hierarchical query
Connect by PRIOR Connection condition start with start condition

　　　　　　　　　　　　SELECT * FROM Z_course t connect by prior T.cour_code=t.p_cour_code start with t.cour_name like '% International '

Pseudo-column:

Level: plus a column of display levels

　　　　　　　　　　　　　　Select t.*, level from Z_course t connect by prior T.cour_code=t.p_cour_code start with t.cour_name like '% International '

RowNum: Add a column line number

　　　　　　　　　　　　　　　　Select S.*, rownum from student s

Use RowNum to split pages

　　　　　　　　　　　　　　　　　　SELECT * FROM (select s.*, rownum rn from student s where rownum<=10) T where t.rn>5

Subqueries (the order in which SQL statements are executed)

Single-line subquery: can use = number

Multi-row subquery: Can not use the = number, can be used in

Use of the EXISTS keyword: whether it exists

　　　　　　　　　　　　　　SELECT * FROM student t where exists (select 1 from student t1 where t1.sno=105)

Advanced Query method for Oracle Database Multi-Table federated query