数据操纵
主要是对表或视图进行插入(insert),修改(update),删除(delete)操作.
分组统计查询
使用聚合函数需要注意的事项:
1.count(*)统计所有的记录数,count(字段名)字段值为null时列不统计
2.聚合函数的出现顺序
3.统计不重复的行信息distinct
[* 字符函数 *]
1.字符串连接:concat(ch1,ch2)
返回字符串ch1与字符串ch2连接的字符串
如:
select concat('hello ','world!') from dual; //hello world!
或
select 'hello '||'world!' from dual; //hello world!
2.字符串首字母大写
initcap(char) :将字符串char中每个单词的首字母大写,其他字母小写
如: select initcap('hello,world') from dual ; //Hello,World 可以使用任何分隔符,进行分隔
3.字符索引位置:instr(ch1,ch2,[m[,n]])
返回指定字符串ch2在字符串ch1中的位置,m起始搜索位置,n表示ch2在ch1出现的次数
如:
select * from emp where instr(ename,'C')>0 ; //从1开始找
select * from emp where instr(ename,'c')=2 ; //查询出ename下标为2为'C'的员工
4.计算字符串的长度:length(char)
5.字符的大小写转换:
lower(char):把字符串char转换为小写
upper(char):把字符串char转换为大写
6.替换字符串:replace(ch1,ch2,ch3)
把字符串ch1中的字符串ch2替换成字符串ch3
如:select replace(sal,sal,'*****') from emp ; //把emp表中的员工的工资替换为*****
7.截取字符串:substr(ch,起始位置,截取多少位)
如:select substr('hello world',6,5) from dual ; //world
注：下标是从1开始的
8.去掉字符串空格
trim(char):去除两边的空格
ltrim(char,[ch]):去掉字符串左空格或去掉左边包含ch的字符串
select ltrim('abcdef','abc') from dual ;-->def,去除char左边包含abc的字母
rtrim(char,[ch]):去掉字符串右空格或去掉右边包含ch的字符串
select rtrim('abcdef','abc') from dual ;-->abc,去除char左边包含def的字母
9.instr(char1,char2,[m[,n]]) : 返回char2在char1中的位置,m表示起始索引位置,n表示cha2在char1出现的次数
select instr('abcde','d') from dual ;//下标从1开始查找,返回d所在字符串的位置,返回4
instr(char,char,n)【在一个字符串中搜索另一个字符串，n>0从前向后，你<0从后向前】
select instr('abcabc','c') from dual;-->3
select instr('abcabc','c',-1) from dual;-->6
10.chr(n):返回ASCII码值为n的字符
select CHR('65') from dual ;//A
ASCII(char):返回制定字符的ASCII码
select ascii('A') from dual;-->65--返回A字符的ＡＳＣＩＩ码
11.lpad(char1,n[,char2]):如果char1的长度大于n,那么返回char1左边n个字符,如果n大于char1的长度,使用
char2在char1左边填充使其长度达到n
select lpad('abc',2,'dd') from dual ;//ab--如果char1小于n的长度,直接输出n对应char1中的字符
select lpad('abc',5,'dd') from dual ;//ddabc
12.RPad(char1,n[,char2]):使用char2补充在char1右侧,使char1的长度达到n,如果n小于char1的长度，截掉后面多的部分
select rpad('abc',5,'a') from dual;-->abcaa
select rpad('abc',2) from dual;-->ab
select length(rpad('abc',5)) from dual;-->5【右边加多了2个空格】
13.Translate(char1,form,to):用to替换form,然后用form替换char1中匹配内容
select translate('abc','ab','a') from dual;--->ac
[日期函数]
1.dbtimezone:返回数据库所在的时区
select dbtimezone from dual ;
2.extract(depart from date) :从日期date中获取depart对应部分的内容,depart的取值可以有:
year,month,day,hour,minute,second,timezone_hour,timezone_minute
timezone_region,timezone_abbr
select extract(year from sysdate) from dual ;//2016
3.add_months(d,n) :返回日期d添加n个月所对应的日期时间，n为正数表示d之后的日期，n为负数表示d之前的日期
select add_months(sysdate,2) from dual ; //今天是2016-7-31日，加两个月，那么就是2016-9-30日
select add_months(sysdate,-1) from dual ; //今天是2016-8-1日,-1代表上一个月,即是2016-7-1日
4.next_day(日期,星期几):
参数说明:
星期几:可以使用,星期日-星期六
可以使用1-7,1代表星期日
select next_day(sysdate,'星期一') from dual ;
select next_day(sysdate,'星期二') from dual ;
select next_day(sysdate,'星期三') from dual ;
select next_day(sysdate,'星期四') from dual ;
select next_day(sysdate,'星期五') from dual ;
select next_day(sysdate,'星期六') from dual ;
select next_day(sysdate,'星期日') from dual ;
select next_day(sysdate,1) from dual ;
select next_day(sysdate,2) from dual ;
select next_day(sysdate,3) from dual ;
select next_day(sysdate,4) from dual ;
select next_day(sysdate,5) from dual ;
select next_day(sysdate,6) from dual ;
select next_day(sysdate,7) from dual ;
5.last_day(d):返回d所在月份的最后一天
select last_day(sysdate) from dual ; //获取当前月份的最后一天
6.trunc(d,[fmt]):返回截断日期时间数据
select trunc(sysdate,'yy') from dual ;//返回当前年份的第一天
select trunc(sysdate,'mm') from dual ;//返回当前月份的第一天
7.months_between(d1,d2):返回d1和d2两个日期之间相差的月数
select abs(months_between(sysdate,add_months(sysdate,3))) from dual ;
假如今天是2016-8-1,加3个月,那么变成了2016-11-1,所以相差3个月
8.round(d[,fmt]):返回日期d的四舍五入结果
select round(sysdate) from dual ;//比如今天是2016-8-1日,今日已过半了,那么久是2016-8-2日了
9.to_date('字符串格式',date日期格式):把字符串格式的日期转换为指定格式的date日期
select to_date('2016-8-1','yyyy-mm-dd') from dual ;
10.to_char(date,'字符串格式'):把date格式的日期,转换为指定格式的字符串日期
select to_char(sysdate,'yyyy-mm-dd') from dual ;
11.systimestamp:返回timesamp with time zone 类型的系统日期和时间
select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ssxff6') from dual;
注:xff6中的6表示保留多少位
12.soundex(char):用来比较发音相同的字符串
[数值函数]
1.trunc(m[,n]):对m进行截取操作,不考虑四舍五入
select trunc(108.123) from dual ; //-->108,当省略时,表示截取数值的整数部分
select trunc(108.123,2) from dual ; //-->108.12,当n>0时,表示截取到小数点右边第n位
select trunc(1082.123,-1) from dual ; //-->1080,当n<0时,表示截取到小数点左边第n位,n位以0代替
2.abs(n):返回n的绝对值
3.sqrt(n):返回n的平方根
selec
t sqrt(4) from dual ; //2
4.mod(m,n):返回m除以n的余数
select mod(4,2) from dual ; //0
5.floor(n):返回小于等于n的最大整数
select floor(3.22) from dual ; //3
6.ceil(n) :返回大于等于n的最小整数
select ceil(3.22) from dual ; //4
7.power(m,n):返回m的n次方
select power(2,2) from dual ; //4
8.sign(n):判断n的正负(n>0返回1;n=0返回0;n<0返回-1)
[转换函数]
1.to_number(char[,fmt]):将特定的格式的字符串char转换为数值
[其他函数]
1.nvl(expr1,expr2):如果expr1位null,返回expr2,否则返回expr1
例:查询30号部门各个员工的编号,工资与奖金之后
select employee_id,salary+nvl(commission_pct,0)sal,department_id
from employees where department_id = 30 ;
例:查询员工编号,姓名,以及员工的经理号,如果没有经理则显示NO Manager字符串
select employee_id,first_name,last_name,
nvl(to_char(manager_id),'NO Manager') from employees
注:使用nvl函数进行空值转换处理时,一定要注意转换后的表达式的类型必须与原表达式的类型一样
2.nvl2(expr1,expr2,expr3):如果expr1位null,返回expr3,否则返回expr2
例:查询30号部门各个员工的编号,工资与奖金之后
select employee_id,salary+nvl2(commission_pct,commission_pct,0),department_id
from employees where department_id = 30 ;
例:查询员工编号,姓名,以及员工的经理号,如果没有经理则显示NO Manager字符串
select employee_id,first_name,last_name,nvl2(to_char(manager_id),
to_char(manager_id),'NO Manager') from employees
3.nullif(expr1,expr2):如果expr1与expr2相等,返回null,否则返回expr1
4.greatest(expr1,expr2,...):返回几个表达式中的最大值
5.least(expr1,expr2,...):返回几个表达式中的最小值
6.decode(expr,search1,result1[,search1,result1,...][,default]):
返回与expr相匹配的结果,如果search!=expr,则返回result1,
如果search2=expr,则返回result2,如果不匹配,使用默认值
例:查询员工的编号,部门编号及部门描述,如果部门号为10,则部门描述为'10号部门';
如果部门号为20,则部门描述为'20号部门';否则输出'其他部门'
select employee_id,department_id,decode(department_id,10,'10号部门',20,'20号部门',30,'30号部门','其他部门')
department from employees
7.为了在目标列中根据不同的条件进行不同的输出,可以使用CASE语句
case expr
when 条件 then return条件
when 条件 then return条件
else 其他 end
Oracle计算时间差表达式
有两个日期数据START_DATE，END_DATE，欲得到这两个日期的时间差
（以天，小时，分钟，秒，毫秒）：
天：ROUND(TO_NUMBER(END_DATE - START_DATE))
小时：ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)
www.2cto.com
分钟：ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)
秒：ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)
毫秒：ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000)
周一：Monday
周二：Tuesday
周三：Wednesday
周四：Thursday
周五：Friday
周六：Saturday
周日：Sunday
外连接：
a和b进行连接，如果要完全显示a，就在b条件上加一个（＋），也就是说不带＋的表完全显示
select * from emp e,dept d where e.deptno(+)=d.deptno;
因为我们给e.deptno带上一个(+),所以d表将会被完全显示也就是dept表会被完全显示
如果左侧的表完全显示我们就说是左外联接。
比如： Select * from tab1 t,tab2 r where t.aa=r.bb(+);
如果右侧的表完全显示我们就说是右外联接。
比如： Select * from tab1 t,tab2 r where t.aa(+)=r.bb;
例:查询所有部门的总人数，占总人数比例
select d.* ,nvl2(ed.cou,ed.cou,0)人数 from dept d,(select deptno,count(empno) cou
from emp group by deptno)ed where d.deptno=ed.deptno(+);
