racle數(shù)據(jù)庫(kù)sql學(xué)習(xí)筆記

1、oracle-9i sql（stucturedqueylanguag 巳結(jié)構(gòu)化查詢語(yǔ)言）SQL語(yǔ)言按照功能可分為4大類PQL傲據(jù)查詢語(yǔ)言）:查詢iDDL（數(shù)據(jù)定義語(yǔ)言）:建立/刪除和修改麥對(duì)象PML（數(shù)據(jù)操縱語(yǔ)言）:完成數(shù)據(jù)操作的命令包括查詢PCL（數(shù)據(jù)控制語(yǔ)言）:控制對(duì)數(shù)據(jù)庫(kù)的訪問(wèn),服務(wù)器的關(guān)閉/啟動(dòng)等在Oracle9i中為使用SQL語(yǔ)言提供了 2個(gè)主要工具*SQLPIus*SQLPIusWorksheet select*fromscott.emp-"用戶名數(shù)據(jù)表11的形式selectdistinctjobfromscott.empdistinct保留字指在顯示時(shí)去除相同的記錄 s

2、electempno/enamerjobfromscottempwherejob=,MANAGER' selectempnofe nam 巳 salfromscott.empwheresalv 二 2500-等于select*fromscott.empwherejob=,MANAGER,select*fromscott.empwheresal=1100一不等于select*fromscott.empwherejob!=,MAN AGER1 select*fromscott.empwheresal! = 1100 select*fromscott.empwherejobA=,MANAGE

3、R' select*fromscott.empwheresalA = 1100 select*fromscott.empwherejob<>,MAN AGER'select*fromscott.empwheresal < >1100小于 select*fromscott.empwherejob<'MANAGER' select*fromscott.empwheresal<2000吠于select*fromscott.empwherejob>'MANAGER' select*fromscott.empwhe

4、resal>2000-int列表select*fromscott.empwheresalin(2000,1000/3000) select*fromscott.empwherejobin('MAN AGER','CLERK') select*fromscott.empwheresalnotin(2000,1000/3000) selecCfromscott.empwherejobnotinCMANAGER'JCLERK') betweenselect*fromscott.empwheresalnotbetween2000a nd3000 s

5、elecCfromscott.empwherejobnotbetween'MANAGER'and'CLERK' likeselect氣romscott.empwherejoblike'%M%'代表包含 M 的字符串select氣romscott.empwherejoblike'%M'-代表以M字符結(jié)尾的字符串 selecCfromscott.empwherejoblike'M%'以 M 字符開(kāi)頭的字符串 select*fromscott.empwherejoblike'M_'-代表 M 開(kāi)頭的長(zhǎng)度為

6、 2 的字符串 selectemp no ,ename,jobfromscottempwherejob> = 'CLERK'orsal< =2000 notjob 二'CLERK'"等價(jià)于"jobv>'CLERK'_邏輯比較符and(與)select*fromscott.empwherejob='MANAGER'andsal<>2000 or(S6)select*fromscott.empwherejob ! ='MANAGER'orsal< >2000

7、 not(菲)select*fromscott.empwherenotjob>='MANAGER'一排學(xué)查詢 selectempnorenamejobrsalfromscott.empwherejob< = ,CLERK,orderbyjobasc/saldesc-分組查詢 selectempno.enamej ob alfromscott.empgroupbyjob.empno nam 巳 salhavingsalv 二 2000 selectempno/enamejobrsalfromscott.empwheresal<=2000groupbyjobfem

8、pno/ename/sal /*where檢查每條記錄是否符合條件,having是檢查分組后的各組是否滿足條件having語(yǔ)句只能 配合 groupby語(yǔ)句使用，沒(méi)有g(shù)roupby時(shí)不能使用having,但可使用where*/ selectempno.enamesalmgcsal + mgrfromscott.emp-"常見(jiàn) selectempno 編號(hào),ename 姓名Job 工作,sal 薪水 fromscott.emp 無(wú)條件多表查詢"笛卡爾"積的方式組合起來(lái) selectemp.empno.emp.ename.emp.deptno.dept.dname.d

9、ept.locfromscott.emp.scott.dept -等值多表査詢 selectemp.empno.emp.enamemp.deptno.dept.dname.dept.locfromscott.emp,scott.deptwherescott.emp.deptno=scott.dept.deptno-具有相同的屬性(相同的數(shù)據(jù)類型/寬度和取值范 圍) 一非等值多表查詢selectemp.empno.emp.enameemp.deptnoept.dname.deptlocfromscott.emp,scott.deptwherescott.emp.deptno!=scott.dep

10、t.deptnoandscott.emp.deptno=10 嵌套查詢 子查詢可以嵌套多層，子查詢操作的數(shù)據(jù)表可以是父查詢不操作的數(shù)據(jù)表，子查詢中不能有orderby 分組語(yǔ)句 selectemp.empnomp.enameemp.job.emp.salfromscott.emp wheresal>=(selectsalfromscott.empwhereename=,WARD')查詢薪水二WARD 薪水的員工 selectemp.empno.emp.ename.emp.job.emp.salfromscott.empwheresa-in(se-e2sa-fr-omscottem

11、pwhereenameH-WARD)：IIWzksWARDffi4lff3別 ZE3iwefse-ectempempnobmpenamebmpjoba>mpsa-fromscoti.empwheresavany(se-ectsa-fromscottempwherejobHMANAGER)se_ectsa_fromscoitempwherejobHMANAGERrlms®3 皿禦決瞞 2975、2850、2450se-ec?mpempnoa>mpenameQmpjobDmpsa-fromscort.empWheresav29750rsav28500rsa-V2450:卓ql

12、any 昭色淞®曲 someIMase-ectempempnoa>mpenameCDmpjoba>mpsa-fromscott.emp wheresa-nsome(se-ecisa-fromscottempwherejobHMANAGER) se-ecisa-fromsco 才.empwherejobH-MANAGERrwisffi3 B2975、2850、2450se-ectempempnoa>mpenameempjob(Dmpsa-frornscoft.empwheresa-"2975orsaT2850orsaT2450:夏qlsome 瞅色亠卄

13、74;©3i蘭 sse-ectempempnoompenameCDmpjobCDmpsa-fromscoft.empwhere® va-(se-ectsa-fromscottempwherQobHMANAGER) se_ectsa_fromscott.empwherejobHMANAGERmi® 世 3 §>決»2975、2850、2450se-ectempempnoa>mpenameempjobempsa-frornscotLempWheresa-V2975andsa-V285oandsav245o;牙qla=m& 淞棄M

14、凹3JexistsImi®se-ecrempempnofDmpenameempjobCDmpsa-fromscott.empscott.deprwhereexists(se-eci4;romscottempwherescottempdeptno"scottdepLdepino)斗痂命Imi®fse-ectdeotnofromscoftemDcnionfse-ecfdeofnofromscottdeoH空痂命Im色 (se-ecideprnofomscottemp)mtersect(se-ecidepinofromscottdepr)AB (se-ecidepino

15、fromscotLemp)minus(se-esdepinofromscottdepD :a SQLKm®薯瞅色 772.=因薯2=5冋汁軸屮磐®n s?t se-ecrmgr;mgt、10pcei-(mgr、100)fromscoitemp 三。®薯±。弓)冋、彳軸嬉廊n3馴汁h(huán)e磐 se-ectmgr;mgulopf-oor(mg、loo)fromscottemp 3od®mod(ma、a3t®弼Dns獨(dú)磐 se-ectmgr;mod(mgr;1000)mod(mgr;100)mod(mgr;10)fromscottemp 【po

16、wer®勞 power(mb)冋 msn 為常 se-ecimgpower(mgr;2)power(mgl;3)fromscottempround函數(shù):round（m,rO.四舍五入，保留n位 selectmgrjoundCmgr/lOOXroundCmgr/lOOOJfromscott.empsign函數(shù):sign(n)小>0.取 l;n=0#ER 0;n<0#取-1 selectmgr;mgr7800,sign(mg7800)fromscottemp avg函數(shù):avg（字段名）,求平均值要求字段為 selectavg(mgr)平均薪水 fromscott.emp c

17、ount函數(shù):count（字段名）或county）統(tǒng)計(jì)總數(shù) selectcount（*）記錄總數(shù) fromscott.emp selectcount（distinctjob）!作類別總數(shù) fromscott.emp min函數(shù):min（字段名），計(jì)算數(shù)值型字段最小數(shù) selectmin（sal）最少薪水 fromscott.empmax函數(shù):max（字段名），計(jì)算數(shù)值型字段最大數(shù) selectmax（sal）最高薪水 fromscott.empsum函數(shù):sum（字段名），計(jì)算數(shù)值型字段總和 selectsum(sal)薪水總和 fromscott.emp一記錄的錄入 1 單行記錄的錄入ins

18、ertinto 數(shù)據(jù)表 valuesinsertintoscott.emp(empno,ename,hiredate)values(8000/JONE'/25-ll 月-2003');2.多行記錄的錄入 insertinto 數(shù)據(jù)表(selectfrom 數(shù)據(jù)表 where 條件)insertintoscott.empfempnoename.hiredate)(selectemp no+100,ename, hiredatefromscott.empwhereempon> =6999);一表間數(shù)據(jù)復(fù)制createtable語(yǔ)句的功能就是創(chuàng)建新的createtablescott.testselectdistinctempno.ename.hiredatefromscott.empwhereempno> =7000);一刪除數(shù)據(jù) 使用delete命令可以刪除數(shù)據(jù)，數(shù)據(jù)將存儲(chǔ)在系統(tǒng)回滾段中,需要的時(shí)候可以回滾恢復(fù);使用truncate命令可以刪除整表數(shù)據(jù)但保留結(jié)構(gòu)數(shù)據(jù)不可恢復(fù).deletefrom數(shù)據(jù)表where條件 deletefrom