oracle一些常用函数笔记和简单例子

转换函数 to_char、to_number。to_date
数字函数 round 、trunc、mod
日期函数 months_between 、add_months、last_day、next_day、
通用函数 nvl、coalesce、decde
字符串函数 upper、lower、initcap、length、trim、replace
select name,salary from emp_ck where salary >any(select salary from emp_ck where deptno =10 and salary is not null);
select deptno,dname from dept_ck where deptno in (10,20,30);
coalesce函数(奖金，薪水*0.5，100)
作用：返回第一个非空参数
建议最后一个参数设为常量
参数可以是很多个
select name,salary,bonus,coalesce(bonus,salary*0.5,100) sal from emp_ck;
本语句的意思就是，把没有奖金的人的工资的一半当做奖金。
select length(1234567890) from dual;
替换函数
replace(字符串，要替换的字母，要换成的字符)
select replace('aaaa','a','b') from dual;
去除字符串的空格的函数
trim(指定的字符串)(trim 有两种一个是左去除空格Ltrim一个是右去除空格Rtrim,也可以默认trim两边都去除空格)
每个部门每个职位有多少人
select deptno,job,count(*) People from emp_ck group by deptno,job order by deptno,job;
子查询
子查询的all用法
select name,salary from emp_ck where salary >all(select salary from emp_ck where deptno =10 and salary is not null);
这个的用法是，如果“要查询的字段”是NULL ，那么就把“指定的元素” 放在为NULL的上面
nvl(要查询的字段，指定的元素)
select nvl(salary,1) F,nvl(salary,0) E from emp_ck;
字符串变大写
upper(指定的字符串)
select upper('abc') from dual;
select deptno,avg(nvl(salary,0)) from emp_ck group by deptno;
select to_number('20111009') from dual;
把指定元素转换成字符串
to_char(指定元素，指定格式的字符串(要转换成数字用0表示，其他格式用其他形式))
select to_char(1234,'0000') from dual;
数字的四舍五入
round(要进行处理的数字，保留几位小数)
end new_salary
from emp_ck;
本语句的意思是给每个职位加薪水用
还可以用oracle的函数decode
decode(列名，匹配条件1，取值1，
匹配条件2，取值2，
。。。。。
默认值)
select name,job,salary,decode(job,'analyst',salary*1.15,
instr(字符串，指定在字符串中出现的字符或字符串，开始查找的起点，第几次出现的次数)
得到在string1中包含string2的位置. string1时从左边开始检查的,开始的位置为a,如果a是一个负数,那么string1是从右边开始进行扫描的.第b次出现的位置将被返回. a和b都缺省设置为1,这将会返回在string1中第一次出现string2的位置.如果string2在a和b的规定下没有找到,那么返回0.位置的计算是相对于string1的开始位置的,不管
select round(1029321.345,0) from dual;
trunc(要处理的数字，要截取小数点的长度)
他不进行四舍五入。只按要求指定截取长度
select trunc(1029321.345,0) from dual;
取模
mod(要取模的数字，对几取模)
select mod(100,3) from dual;
日期格式指定
to_date(日期的形式，指定的日期格式)
select to_date('20111009','yyyymmdd') from dual;
select to_date('20111009','yyyy-mm-dd') from dual;
把字符串转换成数字
to_number(字符串)
多列子查询in的用法
select deptno,dname from dept_ck where deptno in (select deptno from emp_ck where job ='Managerelect deptno,dname from dept_ck where deptno not in (select deptno from emp_ck where job ='Manager');
select deptno,avg(nvl(salary,0)) from emp_ck group by deptno;
哪个部门的平均薪水比部门20的平均薪水高
select deptno,avg(nvl(salary,0)) from emp_ck group by deptno having avg(nvl(salary,0))>(select avg(nvl(salary,0)) from emp_ck where deptno=20);
字符串变小写
lower(指定的字符串)
select lower('ABC') from dual;
指定的字符串首字母大写
initcap(指定的字符串)
select initcap('helloworld') from dual;
长度的显示
length(指定的元素)
select length('helloworld') from dual;
过滤分组结果：having短语平均薪水在5000以上的部门
select deptno,nvl(salary,0) from emp_ck;
select deptno,avg(nvl(salary,0)) from emp_ck group by deptno having avg(nvl(salary,0))>5000;
select instr('123123123123','2',1,1) from dual;
select count(*) from emp_ck where name=''
按指定的长度截取字符串
substr(指定字符串，指定字符的位置从1开始(如果为负责从右边开始)，指定截取长度)
select substr('abcabcabc',2,1) from dual;
日期函数
两个月份之间间隔数
months_between(日期1，日期2)
select months_between(to_date('20110910','yyyymmdd'),to_date('20111009','yyyymmdd')) from dual;
增加月数
add_months(指定的日期，指定的月数)
让日期按指定格式显示
select to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') now from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') now from dual;
select to_char(sysdate,' hh12:mi:ss') now from dual;
让日期按照全英文显示
select to_char(sysdate,' year month dd day dy hh:mi:ss pm') now from dual;
按照指定格式插入日期格式的数据，但还是和原来显示的效果是一样的
insert into emp_ck values(1012,’rose’,’Programmer’,5500,400,to_date(‘2011-10-09’,’yyyy-mm-dd’),1002,10);
在子查询中如果返回一条数据可以用=进行连接
如果返回多条数据用in进行连接
那个部门的人数大于3个
select deptno,count(*) from emp_ck group by deptno having count(*) >3;
用子查询做，