SQL Tips

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