NULL
・order by
null is the highest in rows;
null Last is default for ASC
null first is default for desc
●Defining a Null Value
・NULL is a value that is unavailable,unassigned,unknown,or inapplicable;
・NULL is not the same as zero or a blank space;
●Null values in Arithmetic Expressions
※Arithmatic expressions containing a null value evaluate to null
1,Groupo By clause can’t contain functions ,like AVG(),MIN(),MAX() and so on;2,Function AVG(),SUM() can’t be used with DATE data type colunn;
3,NVL2(expr1, expr2, expr3)
The arguments
expr2 and expr3 can have any datatypes except LONG.4,Alternative Quote(q) Opreator
・Specify your own quotation mark delimiter
・Select any delimiter
・Increase readability and usability
eg:
SQL> select 'this'||q'['s mine]' as sample from dual;
SAMPLE
-----------
this's mine
SQL>
SQL> select 'this'||q'('s mine)' as sample from dual;
SAMPLE
-----------
this's mine
SQL>
SQL> select 'this'||q'['s 'mine]' as sample from dual;
SAMPLE
------------
this's 'mine
SQL> select 'this'||q'{'s '||mine}' as sample from dual;
SAMPLE
--------------
this's '||mine
5,Like operater
% 0 or more characters
_ a character
・You can use the ESCAPE identifier to search for the actual % and _symbols;
eg:
SQL> select * from employees where last_name like '%SA\_%' ESCAPE '\';
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- -------------------- ----------
34 fadf faSA_FADF
6,Rules of Precedence
1,Arithmetic operators;
2,Concatenation opreator;||
3,Comparison conditions;
4,IS[NOT] NULL,LIKE,[NOT] IN
5,[NOT]BETWEEN
6,Not equal to
7,NOT logical condition
8,AND logical condition
9,OR logical condition
you can user parentheses()to override the Precedence
7,Substitution Variables
& 遇到一次要求输入一次
&&存储session 中
DEFINE variable
UNDEFINE variable
set verify ON/OFF