Oracle SQL语言应用基础

数据操纵

主要是对表或视图进行插入(insert),修改(update),删除(delete)操作.

分组统计查询

使用聚合函数需要注意的事项:

1.count(*)统计所有的记录数,count(字段名)字段值为null时列不统计

2.聚合函数的出现顺序

3.统计不重复的行信息distinct

[* 字符函数 *]

1.字符串连接:concat(ch2,ch3)

返回字符串ch2与字符串ch3连接的字符串

如:

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(ch2,ch3,[m[,n]])

返回指定字符串ch3在字符串ch2中的位置,m起始搜索位置,n表示ch3在ch2出现的次数

如:

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(ch2,ch3,ch4)

把字符串ch2中的字符串ch3替换成字符串ch4

如: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 hh34: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,search2,result1[,search2,result1,...][,default]):

返回与expr相匹配的结果,如果search!=expr,则返回result1,

如果search3=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(+);