Oracle SQL語言應(yīng)用基礎(chǔ)

數(shù)據(jù)操縱

主要是對(duì)表或視圖進(jìn)行插入(insert),修改(update),刪除(delete)操作.

分組統(tǒng)計(jì)查詢

使用聚合函數(shù)需要注意的事項(xiàng):

1.count(*)統(tǒng)計(jì)所有的記錄數(shù),count(字段名)字段值為null時(shí)列不統(tǒng)計(jì)

2.聚合函數(shù)的出現(xiàn)順序

3.統(tǒng)計(jì)不重復(fù)的行信息distinct

[* 字符函數(shù) *]

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中每個(gè)單詞的首字母大寫,其他字母小寫

如: select initcap('hello,world') from dual ; //Hello,World 可以使用任何分隔符,進(jìn)行分隔

3.字符索引位置:instr(ch2,ch3,[m[,n]])

返回指定字符串ch3在字符串ch2中的位置,m起始搜索位置,n表示ch3在ch2出現(xiàn)的次數(shù)

如:

select * from emp where instr(ename,'C')>0 ; //從1開始找

select * from emp where instr(ename,'c')=2 ; //查詢出ename下標(biāo)為2為'C'的員工

4.計(jì)算字符串的長(zhǎng)度:length(char)

5.字符的大小寫轉(zhuǎn)換:

lower(char):把字符串char轉(zhuǎn)換為小寫

upper(char):把字符串char轉(zhuǎn)換為大寫

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

注：下標(biāo)是從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出現(xiàn)的次數(shù)

select instr('abcde','d') from dual ;//下標(biāo)從1開始查找,返回d所在字符串的位置,返回4

 instr(char,char,n)【在一個(gè)字符串中搜索另一個(gè)字符串，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的長(zhǎng)度大于n,那么返回char1左邊n個(gè)字符,如果n大于char1的長(zhǎng)度,使用

char2在char1左邊填充使其長(zhǎng)度達(dá)到n

select lpad('abc',2,'dd') from dual ;//ab--如果char1小于n的長(zhǎng)度,直接輸出n對(duì)應(yīng)char1中的字符

select lpad('abc',5,'dd') from dual ;//ddabc

12.RPad(char1,n[,char2]):使用char2補(bǔ)充在char1右側(cè),使char1的長(zhǎng)度達(dá)到n,如果n小于char1的長(zhǎng)度，截掉后面多的部分

select rpad('abc',5,'a') from dual;-->abcaa

select rpad('abc',2) from dual;-->ab

select length(rpad('abc',5)) from dual;-->5【右邊加多了2個(gè)空格】

13.Translate(char1,form,to):用to替換form,然后用form替換char1中匹配內(nèi)容

select translate('abc','ab','a') from dual;--->ac

[日期函數(shù)]

1.dbtimezone:返回?cái)?shù)據(jù)庫(kù)所在的時(shí)區(qū)

select dbtimezone from dual ;

2.extract(depart from date) :從日期date中獲取depart對(duì)應(yīng)部分的內(nèi)容,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個(gè)月所對(duì)應(yīng)的日期時(shí)間，n為正數(shù)表示d之后的日期，n為負(fù)數(shù)表示d之前的日期

select add_months(sysdate,2) from dual ; //今天是2016-7-31日，加兩個(gè)月，那么就是2016-9-30日

select add_months(sysdate,-1) from dual ; //今天是2016-8-1日,-1代表上一個(gè)月,即是2016-7-1日

4.next_day(日期,星期幾):

                 參數(shù)說明:

                          星期幾:可以使用,星期日-星期六

可以使用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 ; //獲取當(dāng)前月份的最后一天

6.trunc(d,[fmt]):返回截?cái)嗳掌跁r(shí)間數(shù)據(jù)

select trunc(sysdate,'yy') from dual ;//返回當(dāng)前年份的第一天

select trunc(sysdate,'mm') from dual ;//返回當(dāng)前月份的第一天

7.months_between(d1,d2):返回d1和d2兩個(gè)日期之間相差的月數(shù)

select abs(months_between(sysdate,add_months(sysdate,3))) from dual ;

假如今天是2016-8-1,加3個(gè)月,那么變成了2016-11-1,所以相差3個(gè)月

8.round(d[,fmt]):返回日期d的四舍五入結(jié)果

select round(sysdate) from dual ;//比如今天是2016-8-1日,今日已過半了,那么久是2016-8-2日了

9.to_date('字符串格式',date日期格式):把字符串格式的日期轉(zhuǎn)換為指定格式的date日期

select to_date('2016-8-1','yyyy-mm-dd') from dual ; 

10.to_char(date,'字符串格式'):把date格式的日期,轉(zhuǎn)換為指定格式的字符串日期

select to_char(sysdate,'yyyy-mm-dd') from dual ;

11.systimestamp:返回timesamp with time zone 類型的系統(tǒng)日期和時(shí)間

select to_char(systimestamp,'yyyy-mm-dd hh34:mi:ssxff6') from dual;

注:xff6中的6表示保留多少位

12.soundex(char):用來比較發(fā)音相同的字符串

[數(shù)值函數(shù)]

1.trunc(m[,n]):對(duì)m進(jìn)行截取操作,不考慮四舍五入

select trunc(108.123) from dual ; //-->108,當(dāng)省略時(shí),表示截取數(shù)值的整數(shù)部分

select trunc(108.123,2) from dual ; //-->108.12,當(dāng)n>0時(shí),表示截取到小數(shù)點(diǎn)右邊第n位

select trunc(1082.123,-1) from dual ; //-->1080,當(dāng)n<0時(shí),表示截取到小數(shù)點(diǎn)左邊第n位,n位以0代替

2.abs(n):返回n的絕對(duì)值

3.sqrt(n):返回n的平方根

selec

t sqrt(4) from dual ;   //2

4.mod(m,n):返回m除以n的余數(shù)

select mod(4,2) from dual ;  //0

5.floor(n):返回小于等于n的最大整數(shù)

select floor(3.22) from dual ; //3

6.ceil(n) :返回大于等于n的最小整數(shù)

select ceil(3.22) from dual ; //4

7.power(m,n):返回m的n次方

select power(2,2) from dual ; //4

8.sign(n):判斷n的正負(fù)(n>0返回1;n=0返回0;n<0返回-1)

[轉(zhuǎn)換函數(shù)]

1.to_number(char[,fmt]):將特定的格式的字符串char轉(zhuǎn)換為數(shù)值

[其他函數(shù)]

1.nvl(expr1,expr2):如果expr1位null,返回expr2,否則返回expr1

例:查詢30號(hào)部門各個(gè)員工的編號(hào),工資與獎(jiǎng)金之后

select employee_id,salary+nvl(commission_pct,0)sal,department_id

from employees where department_id = 30 ;

例:查詢員工編號(hào),姓名,以及員工的經(jīng)理號(hào),如果沒有經(jīng)理則顯示NO Manager字符串

select employee_id,first_name,last_name,

nvl(to_char(manager_id),'NO Manager') from employees

注:使用nvl函數(shù)進(jìn)行空值轉(zhuǎn)換處理時(shí),一定要注意轉(zhuǎn)換后的表達(dá)式的類型必須與原表達(dá)式的類型一樣

2.nvl2(expr1,expr2,expr3):如果expr1位null,返回expr3,否則返回expr2

例:查詢30號(hào)部門各個(gè)員工的編號(hào),工資與獎(jiǎng)金之后

select employee_id,salary+nvl2(commission_pct,commission_pct,0),department_id 

from employees where department_id = 30 ;

例:查詢員工編號(hào),姓名,以及員工的經(jīng)理號(hào),如果沒有經(jīng)理則顯示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,...):返回幾個(gè)表達(dá)式中的最大值

5.least(expr1,expr2,...):返回幾個(gè)表達(dá)式中的最小值

6.decode(expr,search2,result1[,search2,result1,...][,default]):

返回與expr相匹配的結(jié)果,如果search!=expr,則返回result1,

如果search3=expr,則返回result2,如果不匹配,使用默認(rèn)值

例:查詢員工的編號(hào),部門編號(hào)及部門描述,如果部門號(hào)為10,則部門描述為'10號(hào)部門';

如果部門號(hào)為20,則部門描述為'20號(hào)部門';否則輸出'其他部門'

 select employee_id,department_id,decode(department_id,10,'10號(hào)部門',20,'20號(hào)部門',30,'30號(hào)部門','其他部門')

department from employees 

7.為了在目標(biāo)列中根據(jù)不同的條件進(jìn)行不同的輸出,可以使用CASE語句

case expr 

when 條件 then return條件

when 條件 then return條件

else 其他 end

Oracle計(jì)算時(shí)間差表達(dá)式

有兩個(gè)日期數(shù)據(jù)START_DATE，END_DATE，欲得到這兩個(gè)日期的時(shí)間差

（以天，小時(shí)，分鐘，秒，毫秒）：  

天：ROUND(TO_NUMBER(END_DATE - START_DATE))  

小時(shí)：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進(jìn)行連接，如果要完全顯示a，就在b條件上加一個(gè)（＋），也就是說不帶＋的表完全顯示

select * from emp e,dept d where e.deptno(+)=d.deptno; 

因?yàn)槲覀兘oe.deptno帶上一個(gè)(+),所以d表將會(huì)被完全顯示也就是dept表會(huì)被完全顯示

如果左側(cè)的表完全顯示我們就說是左外聯(lián)接。

比如： Select * from tab1 t,tab2 r where t.aa=r.bb(+);

如果右側(cè)的表完全顯示我們就說是右外聯(lián)接。

比如： Select * from tab1 t,tab2 r where t.aa(+)=r.bb;

例:查詢所有部門的總?cè)藬?shù)，占總?cè)藬?shù)比例

select d.* ,nvl2(ed.cou,ed.cou,0)人數(shù) from dept d,(select deptno,count(empno) cou

from emp group by deptno)ed where d.deptno=ed.deptno(+);