Oracle query-related note points

Single-Table query:

　　1. mix with OR and and

Requirements: Query owner name contains ' Liu ' or house number containing ' 5 ', and Address No. 3 record

SELECT *  from WHERE  like ' % LIU% ' or  like ' %5% '  and = 3

and permissions take precedence over or so you need to add () on either side of or

　　2. Scope Query

In addition to the traditional where usenum >=100 and Usenum <=200, you can also query with between and

SELECT *  from WHERE between  -  and -   -only for >= or <=

　　3. go to Empty query

SELECT *  from WHERE  is NULL ; SELECT *  from WHERE  is  not null;

4. Remove duplicate records

SELECT  from GROUP  by Addressid; SELECT DISTINCT  from -High execution efficiency

5. pseudo-column-based queries

　　　　ROWID: Each row in the table has a physical address in the data file, and rowID returns the physical address of the row

SELECT ROWID, T.* from T_area t;

RowNum: You can limit the number of rows returned in a query result set by RowNum pseudo-columns, which can be used for paging

SELECT ROWNUM, T.* from T_owners t;

　　6. Aggregation Functions

　　　　SUM (), AVG (), Max (), Min (), count () within parentheses pass conditions, such as:

SELECT "COUNT" (*fromWHERE=1;

Grouping aggregation GROUP BY if there is a having condition followed by filtering the data after grouping

SELECT Areaid, "sum" (money from theGROUP by has a "sum" (  Money>169000

Multi-Table query:

connection rules can be used within the connection can not use external links to the left side no right outside .

1　　 . Internal Connection

Requirements: Query showing owner number, owner name, owner type name

SELECT     t1. " ID ",    T1." NAME ",    T2." NAME "from    t_owners t1,    t_ownertype T2where    = T2. " ID "

Requirements: Query showing owner number, owner name, address, owner type name

SELECT     T1. " ID ",    T1." NAME ",    T2." NAME ",    T3." NAME "from    t_owners t1,    t_ownertype T2,    t_address T3WHERE      = T2. " ID "and= T3." ID ";

Note: Multiple tables within the connection, you can write from xxx, xxx, xxx and where xx=yy,aa=bb,xx=zz, and finally write select * need to display the content

　　2. Left outer connection

　　　　Requirements: Check the owner's account records, show the owner's number, name, year, month, amount, no account information also to be listed

Select                   -This is the SQL1999 standard syntax     A1. " ID ",    A1." NAME ",    B1."  Year ",    B1. " MONTH ",    B1.  Money  from     t_owners A1leftJOINon= B1. Owneruuid

SELECT-This is the syntax provided by Oracle     A1. " ID ",    A1." NAME ",    B1."  Year ",    B1. " MONTH ",    B1.  Money  from     t_owners A1,    t_account B1where    = B1. Owneruuid (+);

Note: The LEFT join is the main table, the right side is the auxiliary table, on the following is the condition; Oracle provides, who is the auxiliary table on who

Plus (+).

　　3. Sub-query

　　　　Single-line subquery:

Requirements: Query data for January 2012 more than the average value of the ledger Records. Analysis: First check the average, then incoming

SELECT    * fromT_accountWHERE    " Year"= ' -' and"MONTH"= ' on' andUsenum> (    SELECT-Check average        "AVG"(usenum) fromT_accountWHERE        " Year"= ' -'     and"MONTH"= ' on')

Multiline subquery:

Requirement: Inquire the owner record of the address number 1,3,4. Analysis: If writing with or is too cumbersome, use in

SELECT *  from WHERE inch (1,3,4);

　　　　　　　Requirements: The query address contains "garden" owner information. Analysis: First fuzzy query, then incoming

 select  *   From   t_owners  where   Addressid  in   (        Span style= "COLOR: #0000ff" >select   "id"--External query needs ADDRESSID, so internal query to check ID  from   t_address  
   
    where  
     "NAME"  
     Like  
     " 
    % Garden%  
     " 
     
   

Note: The not-in does not contain the IS-not NULL representation is not NULL; The result of a subquery query is passed to another SQL statement as a condition

Paging query:

Simple paging query:

Requirements: Paging query table, 10 records per page. Analysis: RowNum can not use > symbols, only < or <= or = 1, how to do?

SELECT Rownum,a1. *  from WHERE <= ten;

Using subqueries:

 select  *   From   ( select ROWNUM R, A1.         *  from   T_account A1  where   ROWNUM  <=  20--how do you write the code if you want to nest the sort check?  )  where   R 
     >,  10 ; 

Sort-based paging:

SELECT    * from    (        SELECTA.*, ROWNUM RN from            (                SELECT                    *                 fromT_accountWHERE                     Year = ' -'                ORDER  byUsenumDESCA--a table represents the sort, also the data layer in layer three, the first step ) B--b table is based on pseudo-column query, is also a pseudo-column layer in the three layer, the second step 
      where--the last is paged query, is also the three layer of the paging layer, the third step B.rnbetween(3 - 1)* 5 + 1--the index in Oracle starts from 1  and 3 * 5; --condition to know how to calculate

Not to be continued ...

　　　　

Oracle query-related note points