In oracle-day02

Second, connection query
(a) Multi-table connection query
(1) Requirements: The query shows the owner number, owner name, owner type name, such as:

Query statement:

select o.id 业主编号,o.name 业主名称,ot.name 业主类型from T_OWNERS o,T_OWNERTYPE otwhere o.ownertypeid=ot.id

(2) Requirements: The query shows the owner number, owner name, address and owner type, such as

Analysis: This query requires a three-table association query. The owners table, the owner's classification table and the Address table, respectively.
Statement:

select o.id 业主编号,o.name 业主名称,ad.name 地址,ot.name 业主类型from T_OWNERS o,T_OWNERTYPE ot,T_ADDRESS adwhere o.ownertypeid=ot.id and o.addressid=ad.id

(3) Demand: The query shows the owner number, owner's name, address, area, owner classification, such as:

Analysis: Four tables are required to correlate the query, more than one area table (T_area)
Query statement:

select o.id 业主编号,o.name 业主名称,ar.name 区域, ad.name 地址, ot.name 业主类型

from T_OWNERS o ,T_OWNERTYPE ot,T_ADDRESS ad,T_AREA arwhere o.ownertypeid=ot.id and o.addressid=ad.idad.areaid=ar.idand

(4) Demand: The query shows the owner number, owner's name, address, area, charge, owner classification, such as:

Analysis: This query has one more table than the top t_operator
Statement:

select ow.id 业主编号,ow.name 业主名称,ad.name 地址,ar.name 所属区域,op.name 收费员, ot.name 业主类型from T_OWNERS ow,T_OWNERTYPE ot,T_ADDRESS ad ,T_AREA ar,T_OPERATOR opwhere ow.ownertypeid=ot.id and ow.addressid=ad.idand ad.areaid=ar.id and ad.operatorid=op.id

(b) Left outer connection query
Requirements: Check the owner's account records, showing the owner number, name, year, month, amount. If the owner
Names are also listed without a record of the accounts.

Analysis: We want to query this result, need to use T_owners (owner table), T_account
(Table of Accounts) according to the results of the query, the owners table for the left table, accounting table for the right table.
In accordance with the SQL1999 standard syntax, the query statement is as follows:

SELECT ow.id,ow.name,ac.year ,ac.month,ac.moneyFROM T_OWNERS ow left join T_ACCOUNT acon ow.id=ac.owneruuid

Following the syntax provided by ORACLE, it is simple:

SELECT ow.id,ow.name,ac.year ,ac.month,ac.money FROMT_OWNERS ow,T_ACCOUNT acWHERE ow.id=ac.owneruuid(+)

If it is a left outer join, fill in the condition end of the right table (+)
(c) Right outer connection query
Requirements: Check the owner's account records, showing the owner number, name, year, month, amount. If the accounting
No corresponding owner information, but also to list the records. Such as:

SQL1999 Standard Statements

select ow.id,ow.name,ac.year,ac.month,ac.money fromT_OWNERS ow right join T_ACCOUNT acon ow.id=ac.owneruuidORACLE 的语法select ow.id,ow.name,ac.year,ac.month,ac.money fromT_OWNERS ow , T_ACCOUNT acwhere ow.id(+) =ac.owneruuid

Third, sub-query
(i) subqueries in the WHERE clause

1. Single-line subquery
   L return only one record
   L Single-line operator
   
   Requirements: Check the January 2012 Account record for more than average water usage
   Statement:

select * from T_ACCOUNTwhere year=‘2012‘ and month=‘01‘ and usenum>( select avg(usenum) from T_ACCOUNT where year=‘2012‘ and

month=‘01‘ )

Query Result:

The average is:

2. Multi-row subqueries
   L returned multiple records.
   L Multiline operator
   
   In operator
   (1) Requirements: Check the owner record of the address number 1, 3, 4
   Analysis: If we write with the OR operator, SQL is cumbersome, so we use in to query
   The statements are as follows:

select * from T_OWNERSwhere addressid in ( 1,3,4 )

The query results are as follows:

(2) Requirements: The information of the owner of the "garden" in the enquiry address
Statement:

select * from T_OWNERSwhere addressid in( select id from t_address where name like ‘%花园%‘ )

Query Result:

(3) Requirement: The information statement of the owner of the "garden" is not included in the enquiry address:

select * from T_OWNERSwhere addressid not in( select id from t_address where name like ‘%花园%‘ )

Query Result:

(ii) Sub-query in the FROM clause
Subqueries for a FROM clause are multiline subqueries
Requirements: The query displays the owner number, the owner name, the owner type name, the condition for the owner type is "resident",
Implemented using subqueries.
Statement:

SELECT * FROM

(select o.id 业主编号,o.name 业主名称,ot.name 业主类型from T_OWNERS o,T_OWNERTYPE otwhere o.ownertypeid=ot.id)where 业主类型=‘居民‘

The query results are as follows:

(iii) Subqueries in the SELECT clause
The subquery for the SELECT clause must be a single-row subquery
(1) Requirements: List owner information, including ID, name, address.
Statement:

select id,name,(select name from t_address where id=addressid) addressnamefrom t_owners

The query results are as follows:

(2) Requirements: List The owner's information, including ID, name, address, and area of ownership.
Statement:

select id,name,( select name from t_address where id=addressid )addressname,( select (select name from t_area where id=areaid ) fromt_address where id=addressid )adrenamefrom t_owners;

The query results are as follows:

Four, paging query
(a) Simple paging
Requirements: Paging query table T_account, 10 records per page
Analysis: We use the pseudo-column ROWNUM and nested queries in ORACLE for paging queries
We first show the top 10 records, with the following statements:

select rownum,t.* from T_ACCOUNT t where rownum<=10

The results appear as follows:

So we're showing the 11th to 20th record? To write a statement:

select rownum,t.* from T_ACCOUNT twhere rownum>10 and rownum<=20

Query Result:
Well? How come there's no result?
This is because rownum is generated when a query statement scans each record, so you cannot use the "greater than"
Symbol, can only use "less than" or "less than equals", only "equal" or not.
What about that? We can use sub-queries to implement

select * from(select rownum r,t.* from T_ACCOUNT t where rownum<=20)where r>10

The query results are as follows:

(ii) sorting-based paging
Requirements: Paged query table T_account, 10 records per page, in descending order by the number of words used.
We query the 2nd page of data, and if you add a sort based on the statement above, the statement is as follows:

select * from(select rownum r,t.* from T_ACCOUNT t where rownum<=20 orderby usenum desc)where r>10

The query results are as follows:

After verification, we see that the result on page 2nd should be the following record
So it's wrong to infer the statement just now! Then why is it wrong?
We can execute the same sentence in the nested query first.

select rownum r,t.* from T_ACCOUNT twhere rownum<=20 order by usenum desc

You will see the following query results:

You will find that the sorted R is messy. This is because the ROWNUM pseudo-column is generated in the table record scan
is generated, and the sort is done later, when the sort R has been produced, so after sorting R is messy.
So how do you write it?
Very simple, we just have to nest a layer of loops (altogether three layers), let the results sorted first, and then after sorting
Result of R, so it won't be messy.
The statements are as follows:

select * from(select rownum r,t.* from(select * from T_ACCOUNT order by usenum desc) twhere rownum<=20 )where r>10

The results are as follows:

In oracle-day02