Oracle Knowledge Point Consolidation

1.NVL function, Usage: NVL (A, B) when the first argument is empty, the second argument is displayed
2. Recursive query: Connect by: General usage: Start With...connect by ...
Example: SELECT * from emp connect by Mgr =prior empno start with empno=7839
Represents an upward recursive query from empno to 7839, the previous record empno= the Mgr,prior of this record represents the previous record (parent record)
Note: Connect by Mgr =prior empno and connect by prior MGR = Empno recursion in the opposite direction
3.select 1 from Table ... Indicates whether the view table has data
4.select Column_value A from table: query pseudo-column, data to be queried as column A (self-specified)
5.exits: Indicates that a subquery result is not empty, the Where condition is established
6. View CNC Library version: SELECT * FROM V$version
The difference between 7.in and exits
Example: SELECT * from B where cc on (select cc from A);
This is the first to query the CC in a table and B in the match, for a table is much smaller than the B table
SELECT * FROM B exits (select cc from A where cc=b.cc)
This is the first to find out the CC in B and match the A table.
8.splitstr function: Splits a string by a specified character
such as: SELECT * FROM table (splitstr (' 1,2,3,4 ', ', '))
9.union: Merge result sets of two or more queries, default does not contain duplicate values, if you want to protect duplicate values, use UnionAll
10.dual: Commonly used in queries that do not have a target table
The 11.INSTR function returns the position of the string to intercept in the source string,
Example: InStr (string1,string2,start_position,nth_appearanece): String1 source String, String2 the string to find in the source string, the location to begin the lookup, Find the string2 of the first few occurrences
12.| | Representing string connectors in Oracle
13.Oracle Database Export command exp name/[email PROTECTED]:1521/ORCL file=f:\daochu.dmp full=y

Oracle Knowledge Point Consolidation