1.decode
select decode (columnname, 值1,翻译1,值2,翻译2,。。。值n,翻译n,缺省值)
from talbename
where ...
2,nvl
nvl(string1,replace_with)
功能:如果string1 为null, 则nvl函数返回replace——with的值,否则返回string1 的值
注意事项:数据类型必须统一数据类型
3. greatest
greatest(expr1,expr2,.....expr_n)
找出最大数返回
例:select greatest(2,5,12,3,16,8) A from dual
A
16
4, coalesce 返回其参数中第一个非空表达式
coalesce(expression[,...n])
5,truncate 删除表数据--不可恢复
delete 删除表数据--可恢复
drop 删除表结构
6,translate 替换 replace
translate(expr, from_string, to_string)
7,merge
MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句,,也可以是删除语句
1---merge into fzq1 aa --fzq1表是需要更新的表
using(select fzq.id , fzq.chengji
from fzq join fzq2
on fzq.id = fzq2,id) bb --数据集也可以是单个表
on (aa.id = bb.id) -- 关联条件
when matched then --匹配关联条件,作更新处理
update set
aa.chengji=bb.chengji+1,
aa.name=bb.name --可以同时修改多个字段
when not matched then
insert values(bb.id, bb.name, bb.sex, bb.kecheng, bb.chengji);
2----merge into bonuses d
using ( select employee_id,salary, department_id
from hr.employees
where department_id = 80) s
on (d.employee_id = s.employee_id)
when matched then
update set d.bonus = d.bonus + s.salary * 0.01
where----只能出现一次,如果这里出现where ,delete后面where 无效
delete where (s.salary >8000)
when not matched then
insert (d.employee_id , d.bonus) values (s.employee_id, s.salary * 0.01)
where (s.salary <=8000)
8, instr
可以使用instr函数对某个字符串进行判断,判断其是否含有制定的字符。
在一个字符串中查找制定的字符,返回被查找到的制定的字符的位置。
instr(源字符串,目标字符串,开始位置(可选,默认1),第几次出现)
select instr(’abcdefgh','de') position from dual
select instr('avcdefghbc','bc',3) position from dual; --从第3个起开始往后查找bc
9, regexp_replace----多次 replayce
用正则表达式将字母与数字分开
select regexp_replace(data ,'[0-9]','') dname,
regexp_replace(data, '[^0-9],'') deptno
from dept
^--在方括号内代表否,否则代表开始
regexp_like ----模糊查询
regexp_like(data, '[abc]') 相当于 (like '%a%' or '%b%' or '%c%')
regexp_count
regexp_substr(v.emps,'[^,]', 1, level) ---把非,的分割----regexp ---可以使用正则表达式
10, with clause
with x as
(select rownum as seq, a.* from (select 编号,项目, 金额 from detail order by 编号) a)
-----with clause 以with 开头的查询,相当于创建一个临时表
select 编号,项目, 金额,
(case when seq = 1 then 金额 else -金额 end) as 转换后的值
from x
11, over (分析函数)
sum(sal) over (partition by deptno order by ename) new_alias
sum 就是函数名
(sal) 是分析函数的参数,每个函数有0~3个参数,参数看可以使表达式,例如: sum(sal+comm)
over 是一个关键字,可用于表示分析函数,否则查询分析器不能区别sum() 聚集函数 和 sum(0分析函数
partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集客看做一个单一的大区
order by ename 是可选的order by 子句
12, 与over()函数常见的函数介绍(必须和over() 函数一起使用)
---1,row_number() 有两个第一只显示一个
select * from
(
select name , class, s , row_number() over(partition by class order by s desc) row_number from t2
)
where row_number=1
---2, rank() 和 dense_rank() 可以将所有的都查找出来
区别 rank() 是跳跃排序,有两个第二名时接下来就是第四名
dense_rank() 是连续排序,有两个第二名时仍然跟着第三名
---3, sum() over()
select name,class, sum(s) over (partition by class order by s desc) sum from t2
有两个第一时,第一个sum就是两个第一的和
----4 first_value() over() 和 last_value() over()
select opr_id, res_type,
first_value(res_type) over(partition by opr_id order by res_type) low,
last_value(res_type) over(partition by opr_id order by res_type) high
from rm_circuit_route
where opr_id in ('001',.......)
order by opr_ID
-----5 lag() over() 取出前n行的值
lead() over() 取出后n行的值
with a as
(select 1 id, 'a' name from dual
union
select 2 id, 'b' name from dual
union
select 3 id, 'c' name from dual
union
select 4 id, 'd' name from dual
union
select 5 id, 'e' name from dual
)
14, ntile 对数据分组
select ntile(3) over (order by empno) as 组,empno as 编码,ename as 姓名 from emp
where job in ('clerk' , ' manager');
组 编码 姓名
1 7369 aaa
1 3453 bbb
1 2311 ccc
2 4454 ddd
2 5555 eee
3
15 rollup 合计
select deptno , sum(sal) s_sal from emp group by rollup(deptno)
select deptno 部门编码, job 工作, sum(sal) 工资小计 from emp
group by rollup( (deptno,job))
16,ceil(n) 和floor(n)
ceil(n) 取大于等于数值n的最小整数
floor(n)取小于等于数值n的最大整数
特殊列子:
where (ceil((ta.repairTempDate-ta.repairDate))*24) >= 12