Functions

■Character functions

LOWER,UPPER,INITCAP

CONCAT,||,SUBSTR,LENGTH,INSTR,LPAD,RPAD,TRIM('x' FROM 'xxx'),REPLACE

■Number Functions

ROUND,TRUNC,MOD

■Working with Dates

RR Date Format & YY Date Format

MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC

■Convertion Functions

To_char(1,'format_model','nls_parameters'),To_date(1,2,3),To_number(1,2,3)

SQL> desc nls_session_parameters

 名称                                      是否为空? 类型

 ----------------------------------------- -------- -------------

 PARAMETER                                          VARCHAR2(60)

 VALUE                                              VARCHAR2(80)

■General functions

NVL(1,2),NVL2(1,2,3),NULLIF(1,2),COALESCE(1,2,3,...)

■Conditional expressions

CASE,DECODE(col,ex1,res1,..,default)

case expr when expr1 then return_expr1

          when expr2 then return_expr2

          when expr3 then return_expr3

          ..

          else return_expr

end;

           

■Impicit Data Type Conversion

From:varchar2 or char To number or date

From:number or date To varchar2 or char 

Reporting Aggregated Data Using the Group Function

■Types of Group Functions

AVG,COUNT,MAX,MIN,SUM,STDDEV(标准方差),VARIANCE

■Syntax

Group functions ignore null values in the column

组函数不考虑null值。。。

The NVL function force group functions to include null values

select GroupFunction(NVL(XX,XX))from XX

■Count（） returns the number of rows with non-null values

count(*) = cont(1)

count(distinct expr)

■Group by 

All columns in the SELECT list that are not in group functions must be in the GROUP BY clause;

■HAVING 

to restict groups

●Execute follows:

 1,Rows are grouped; 

 2,The group function is applied;

 3,Groups matching the HAVING clause are displayed;