一直在使用oracle，却没有系统学习过，like ggs，begin now！

1、DML：insert、update、delete、merge

2、DDL： create、alter、drop、truncate

3、DCL：grant、revoke

4、commit、rollback、savepoint

其中自己用的较少的：

*merge：

语法如下 
MERGE INTO [your table-name] [rename your table here] 
USING ( [write your query here] )[rename your query-sql and using just like a table] 
ON ([conditional expression here] AND [...]...) 
WHEN MATHED THEN [here you can execute some update sql or something else ] 
WHEN NOT MATHED THEN [execute something else here ! ] 
示例：

merge into products p using (select * from newproducts) np on (p.product_id = np.product_id) 
when matched then 
update set p.product_name = np.product_name delete where p.product_id = np.product_id where np.product_name like 'OL%' 
when not matched then 
insert values(np.product_id, np.product_name, np.category) ;

在Oracle 10g中MERGE有如下一些改进： 
1、UPDATE或INSERT子句是可选的 
2、UPDATE和INSERT子句可以加WHERE子句 
3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表 
4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行 

*revoke

Grant和revoke主要是给表、视图、列上分配或失去权限

grant 权限 on 表名/视图 to 用户;

revoke 权限 on 表名/视图 from 用户;

*DENY：
在安全系统中创建一项，以拒绝给当前数据库内的安全帐户授予权限并防止安全帐户通过其组或角色成员资格继承权限。

DENY CREATE DATABASE, CREATE TABLE TO Mary, John;

DENY INSERT, UPDATE, DELETE ON authors TO Mary, John, Tom;

 

*常用判断：

+-*/   ||   as   is null    distinct    order by (desc)    between and    < >  <> !=   in   not in   like     like '%*_%' excape '*'     or  and  not

 

*常用函数

字符函数：Upper、Lower、initcap、concat(‘a’,'b') ||、Substr  Select substr('abcde',2,2) from dual;--前数第2位开始取2位  、length、replace、instr、lpad、rpad、trim

 

*数值函数

round、mod取余、trunc

*日期函数

months_between()，add_months()，next_day() next_day( date, weekday ) 例next_day(sysdate,'星期二'),last_day

**转换函数

to_char，to_number，to_date

 

to_char(timestamp, text)	text	把 timestamp 转换成 string	to_char(timestamp 'now','HH12:MI:SS')
to_char(int, text)	text	把 int4/int8 转换成 string	to_char(125, '999')
to_char(float, text)	text	把 float4/float8 转换成 string	to_char(125.8, '999D9')
to_char(numeric, text)	text	把 numeric 转换成 string	to_char(numeric '-125.8', '999D99S')
to_date(text, text)	date	把 string 转换成 date	to_date('05 Dec 2000', 'DD Mon YYYY')
to_timestamp(text, text)	date	把 string 转换成 timestamp	to_timestamp('05 Dec 2000', 'DD Mon YYYY')
to_number(text, text)	numeric	把 string 转换成 numeric	to_number('12,454.8-', '99G999D9S')

**通用函数

NVL( string1, replace_with)

功能：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。

 

NULLIF()函数接受两个参数。如果它们相等，那么返回空值；否则，返回第一个参数。

NVL2(expr1,expr2,expr3)

功能：oracle中常用函数，如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。即：NVL2(表达式,不为空设值,为空设值)。

COALESCE (expression_1, expression_2, ...,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。使用COALESCE在于大部分包含空值的表达式最终将返回空值。

SELECT COALESCE(NULL,NULL,3,4,5) FROM dual

其 返回结果为：3

 

CASE表达式

select empno, ename, sal,
case deptno 
when 10 then '财务部'
when 20 then '研发部'
when 30 then '销售部'
else '未知部门'      
end  部门
from emp;

DECODE()函数

select empno, ename, sal,
decode(deptno, 10, '财务部',
20, '研发部',
30, '销售部',
'未知部门')      
部门
from emp; 
 

decode只能做等值的，case when可以做区间的，使用范围来进行条件区分，decode能做到的，case when也能做到； 效率方面，个人觉得两者效率差不多； 但decode的语法要简单些，但它只能做等值的比较； case when end 能做条件的判断。

 

-- 简单Case函数 

CASE sex 
WHEN ' 1 ' THEN ' 男 ' 
WHEN ' 2 ' THEN ' 女 ' 
ELSE ' 其他 ' END 

-- Case搜索函数 

CASE
WHEN sex = ' 1 ' THEN ' 男 ' 
WHEN sex = ' 2 ' THEN ' 女 ' 
ELSE ' 其他 ' END

 

*分组函数

 

count()、avg、max、min、sum、分组函数省略列中空值

 

GROUP BY：在带有group by 子句的查询语句中，在select 列表中指定的列要么是group by 子句中指定的列，要么包含聚组函数

查询语句的select 和group by ,having 子句是聚组函数唯一出现的地方，在where 子句中不能使用聚组函数。

Select [filed1,fild2,]聚合函数（filed）,
 [Grouping(filed),]
 [Grouping_id(filed1,filed2,…)]
From tablename
 Where condition
 [Group by {rollup|cube}(filed,filed2)]
 [having condition]
 [order by filed1]

select deptno,sum(sal) from emp where sal>1200 group by deptno having sum(sal)>8500 order by deptno;  当在gropu by 子句中使用having 子句时，查询结果中只返回满足having条件的组。在一个sql语句中可以有where子句和having子句。having 与where 子句类似，均用于设置限定条件  where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉， 即在分组之前过滤数据，条件中 不能包含聚合函数，使用where条件显示特定的行。 

having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。 

使用order by排序时order by子句置于group by 之后 并且 order by 子句的排序标准不能出现在select查询之外的列。

在使用group by 时，有一个规则需要遵守，即出现在select列表中的字段，如果没有在组函数中，那么必须出现在group by 子句中。

Rollup：意思有”卷起,汇总”的意思,他可以在使得在其括号中的字段,按从右到左的顺序分别group后显示,类似我们用多个group by 语句,然后union all起来

select a,b,c,sum(n) total from testgroup group by rollup(a,b,c);

Cube意思是立方,使用该操作符可以对操作符内的字段,进行遍历组合汇总,例如cube(a,b,c),那么就会产生8种组合结果,分别如下”a-b-c”,”a-b”,”a”,”a-c”,” b-c”,”b”,”c”,”空”,

select a,b,c,sum(n) total from testgroup group by cube(a,b,c);

 

grouping（filed）函数

使用grouping 函数必须先理解rollup 和cube操作符,那么grouping函数有什么用呢?在日常应用中,我们通过rollup或者cube对汇总进行了汇总,汇总后的结果往往要传送到应用程序端,在应用程序端我们必须要有一个依据来判断某行数据是不是按照rollup或cube进行汇总,grouping函数可以用来产生这个依据,他可以接收一个参数,判断该参数是否为null,是则返回1,否则返回0,我样可以据此来判断该是否按某列进行汇总统计的,当然在实验应用中,0和1看起来不那么直观,我们可以使用decode或者case函数进行转议,让查看结果看起来更直观,请看以下例子：select grouping(a) ca,grouping(b) cb,grouping(c) cc, a,b,c,sum(n) from testgroup group by rollup(a,b,c);

 

使用grouping_id（filed1,file2,…）函数

使用grouping函数有时候感觉不是那么灵活的，他只能接收一个字段，而grouping_id()函数则可以接收多个字段，GROUPING_ID()函数可以接受一列或多列，返回按GROUPING位向量进行计算的十进制值。下面我们通过一个例子来研究grouping_id函数是如何按照grouping函数的位向量进行计算的。

select grouping(a) ca,grouping(b) cb,grouping_id(a,b) caandb, a,b,sum(n) from testgroup group by rollup(a,b);