I know the database 10-DQL language Select (cont.)

Yesterday wrote a single-table query, today write down nested bar, multi-table view time ...

Let's just say fake.

ROWID

ROWID is a data type that uniquely identifies the physical location of a record and consists of 18 characters based on 64-bit encoding.

Composition

000000 | FFF | bbbbbb | RRR

Where 1-6 bits are the data object number, 7-9 bits is the file number, 10-15 bits is the block number, and 16-18 bits is the line number.

ROWNUM

RowNum is the line number of a record in the query result.

There is a big difference between the two. ROWID is a real existence, equivalent to a hidden column. RowNum is virtual, is the RDBMS automatically set, if separate screening, in addition to Rownum=1, rownum can not equal any value, otherwise will error, rownum can only use < screening, can not use; Because RowNum is for the entire query result, no matter what the condition, the first record of the rownum absolute = 1. Examples are as follows:

SELECTROWNUM,*  fromEMP;--query All information about the employee table. SELECTROWNUM,*  fromEmpWHEREDEPTNO=2 0;--Query the employee information for department number 20. SELECTROWNUM,*  fromEmpWHEREDEPTNO=  -  andROWNUM= 1;--Query the information for an employee who has a department number of 20. SELECTROWNUM,*  fromEmpWHEREDEPTNO=  -  andROWNUM<Ten;--Query the information for 9 employees who have department number 20. SELECTROWNUM,*  fromEmpWHEREDEPTNO=  -  andROWNUM>3;--Error! 

SELECT rowid,* from EMP WHERE rowid= ' xxx ';--query ROWID information for employees of XXX.

As shown above, when the rownum>3 is an error, can not be executed, then how to query it? Don't worry, we can use a nested query, also called a subquery, to use the query result as a query object or an object in a conditional expression. RowNum the query to a single column, and then filter on it, the SQL statement is as follows:

SELECT RN,* from (SELECT ROWNUM RN,*fromWHERE WHERE  3; -- The Query department number is 20 and excludes the first 2 employee information. 

nested queries

Nested queries are divided into 2 types, one of which is to query the query results as a table and query the object.

SELECT *  from (SELECTfromWHERE'SMITH'; -- The nesting does not make any sense, and is shown here only as an example. 

The second is to filter the data in the Where condition by using the query result as a value or a range.

SELECT  from WHERE = (SELECTfrom WHERE=' Zhang San '); -- query for Zhang San's results.

I know the database 10-DQL language Select (cont.)