■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 FunctionsAVG,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;