管理信息化ORACLEOracle自學(xué)自學(xué)筆記

學(xué)筆記OracleInstallationProducts、集成管理工具、配置和移植工具（管理員用得比較多）當(dāng)啟動(dòng)sqlplus時(shí)會(huì)提示輸入用戶名和口令，所謂的sqlplus是Oracle的一個(gè)客戶端。當(dāng)然也可以安裝特定的客戶端軟件實(shí)現(xiàn)。說(shuō)實(shí)在的Oracle的這個(gè)客戶端太難看了，并且要記好多好多的命令，所以專門有一類公司聲卡的話，你啟動(dòng)一次它就叫一次。對(duì)我們來(lái)說(shuō)sqlplus以超級(jí)管理員身份鏈接數(shù)據(jù)庫(kù)的方法是再命令行下敲入命令：sqlplussys/口令assysdba。的用到一個(gè)學(xué)一個(gè)，沒必要從頭到尾的去學(xué)它。么叫第四代語(yǔ)言，因?yàn)槲覀冎赖谌Z(yǔ)言也得有ifelsefor等這些流程控制語(yǔ)句，也就是事務(wù)控制語(yǔ)句[transactioncontrolstatement]。語(yǔ)句肯定會(huì)考這條select語(yǔ)句不是別的。你跟別人交流的時(shí)候，別說(shuō)我寫過(guò)幾條d而因詳細(xì)的說(shuō)到底是什么語(yǔ)句，比如insert語(yǔ)句、upda數(shù)據(jù)拿出來(lái)，展現(xiàn)出來(lái)。聽起來(lái)似乎挺簡(jiǎn)單的，但你要從表里拿數(shù)據(jù)得首先知道有幾張表，每張表里有些什么內(nèi)容。第一步要做的就是熟悉一下實(shí)驗(yàn)用的數(shù)據(jù)，這些數(shù)據(jù)Oracle都給自帶了，直接用就行了。以上簡(jiǎn)要的介紹了三張表，如果想更詳細(xì)的了解里面的數(shù)據(jù)，教大家第一個(gè)select語(yǔ)句：各張表之間的關(guān)系，以及表內(nèi)部各字段之間的關(guān)系，越熟悉越好。如果要算純數(shù)據(jù)，可以采用dual這張表，它就一條記錄，一個(gè)字段。你如果用其他表計(jì)算最后的結(jié)果都是空值。就可以正確顯示了。關(guān)鍵字distinct是指選擇不重復(fù)的記錄，如果修飾多個(gè)字段時(shí)表示選擇該多個(gè)字段的組合來(lái)。處理。模糊查詢是這樣的，比如找出名字里含有al的，有點(diǎn)像正則表達(dá)式。使用關(guān)鍵字like，可串。這幾個(gè)函數(shù)都是對(duì)字符串進(jìn)行操作的，傳遞進(jìn)去的參數(shù)就是字段名。如selectchr(86)fromdual;將86代表的字符顯示出來(lái)，與之對(duì)應(yīng)的是將一個(gè)字符轉(zhuǎn)為對(duì)應(yīng)的ASCII碼，叫ascii，如：selectascii(‘A’)fromdual;就是將A的碼值顯示出來(lái)。函數(shù)最好記住它。to_char是將字段內(nèi)容顯示為字符，可以對(duì)顯如selectto_char(sal,'$99,999,999.9999')fromemp;就是以后面給定字符形式顯示出來(lái)，進(jìn)行格式控制。格式的。實(shí)際上對(duì)日期的格式控制是利用Java來(lái)實(shí)現(xiàn)的，到時(shí)會(huì)有一個(gè)關(guān)于日期處理的專題。組函數(shù)一個(gè)都不能忘，很重要。selectcount(distinctdeptno)fromemp;找出各個(gè)單獨(dú)的部門一共有多少個(gè)，distinct可以修飾字段。當(dāng)要將各個(gè)組中的記錄分別取出然后再計(jì)算比如平均值等要采用groupby+字段。比如：不唯一匹配導(dǎo)致。select語(yǔ)句一定要非常熟悉，如果這都要查書的話對(duì)企業(yè)就沒法交代了，哈哈！子查詢因?yàn)樵僖粋€(gè)select語(yǔ)句里套了另外一個(gè)select語(yǔ)句。理解子查詢的關(guān)鍵是把它當(dāng)成一張表，可以對(duì)它做表連接最后就可以求出想要的結(jié)果來(lái)。單獨(dú)的select語(yǔ)句相當(dāng)簡(jiǎn)單，(=t.max_salandt.deptno=);這個(gè)語(yǔ)句的作用是求出每個(gè)的經(jīng)理人。為同一張表起不同的別名然后當(dāng)成兩張表來(lái)用。鏈接的時(shí)候有一個(gè)簡(jiǎn)便的寫法using(deptno)代替on(=)但不推薦使用，因它的限制條件很多，可能報(bào)出來(lái)的錯(cuò)誤很難找并且看起來(lái)也不是很清楚。的拿出來(lái)Join前加上right即可；如果兩邊多余的都想拿出來(lái)Join前加上full叫全外連接。接<,這樣表中的最大值就會(huì)連接不上，然后再?gòu)膃mp表中找出未連接上的最大值，如下：selectsalfromempwheresalnotin(select);。使得程序簡(jiǎn)潔了，但要注意最多只能兩層嵌套，第一層可能有多個(gè)輸出（分組時(shí)第二層一定只能有一個(gè)輸出，接下來(lái)就不能再嵌套了，一個(gè)輸入不能作為組函數(shù)的參數(shù)。join()));鍵入命令grantcreatetable,createviewtoscott;然后以scott登錄就可以了。創(chuàng)建視圖時(shí)，名。;用再取去比較了，但實(shí)際中不好說(shuō)因?yàn)橛锌赡躉racle在執(zhí)行的時(shí)候做了優(yōu)化，自動(dòng)將數(shù)字現(xiàn)在已經(jīng)講完了select語(yǔ)句，接下來(lái)講dml語(yǔ)句，常見的有三條，他們和select語(yǔ)句合稱接下來(lái)簡(jiǎn)單介紹一下Oracle的邏輯結(jié)構(gòu)。安裝的時(shí)候創(chuàng)建了一個(gè)全局?jǐn)?shù)據(jù)庫(kù)，同時(shí)為這個(gè)\test進(jìn)入該目錄后執(zhí)行del*.*刪除所有，然后鍵入命令exp后提示輸入用戶名和口令，就個(gè)小區(qū)域填滿了再統(tǒng)一拿到硬盤上來(lái)）一路回車就行，等著它導(dǎo)完就可以了；3.創(chuàng)建新用戶相應(yīng)權(quán)限grantcreateses里完成。insertinto+表名+values（各字段值在對(duì)這些數(shù)據(jù)進(jìn)行操作時(shí)最好有一個(gè)備份，方法很簡(jiǎn)單rollback回退操作，createtable表名asselect*from+欲備份表名;insert語(yǔ)句還有一種插入方法，insertintodept2(deptno,loc)values(50,'beijing');只是插入部分字段的值，其他字段的值為null，還有一種方法是insertintodept2select*fromdept;將select子句選出的記錄挨個(gè)插入前提是選出的記錄與該表的記錄格式相同。selectename,sal,rfrom);）；1.找出沒選過(guò)“黎明”老師課程的所有學(xué)生姓名；‘liming’;nt(*)>=2);語(yǔ)句提交結(jié)束事務(wù)；當(dāng)正常退出exit時(shí)提交結(jié)束事務(wù)，不正常退出時(shí)（如斷電）自動(dòng)回滾結(jié)束事務(wù)。庫(kù)里。創(chuàng)建一張表存放學(xué)生數(shù)據(jù)如：createtablestu();sex取數(shù)字來(lái)表示更節(jié)省空間，入學(xué)時(shí)日期加個(gè)s避免和關(guān)鍵字沖突，可以為某些字段指定默認(rèn)值當(dāng)插入記錄時(shí)沒有對(duì)該字段賦新值就以該默認(rèn)值，其他沒有默認(rèn)值為null。復(fù)，比如學(xué)生學(xué)號(hào)。E-mail做主鍵不好因?yàn)樗梢詾閚ull，同時(shí)索引的時(shí)候數(shù)字比字符串constraintstu_id_pkprimarykey(id),主鍵也可以是多個(gè)字段組合。外鍵約束建立在一張表的兩個(gè)字段上或者是兩張表的兩個(gè)字段上，是比較麻煩的一種約束。classnumber(2)referencesclass（id）,參考class這張表的id字段，當(dāng)然也可以加在表級(jí)上：constraintforeignkey(class)referencesclass(id),。注意在寫外鍵約束的時(shí)候，stu束條件。還可以修改字段的數(shù)據(jù)類型，如果里面已經(jīng)有數(shù)據(jù)修改的后果應(yīng)不影響原來(lái)存的記錄值。在Oracle里面看當(dāng)前用戶下有哪些表，哪些約束，哪些視圖等等這些信息被單獨(dú)地裝在一張表里面，稱作數(shù)據(jù)字典表。user_tables里面裝的是當(dāng)前用戶共有多少?gòu)埍恚锩孀侄慰煽偸潜持鴤€(gè)小本本的原因，你要背是不可能的。索引就相當(dāng)于字典里的索引一樣，它在Oracle里相當(dāng)于一種新的數(shù)據(jù)庫(kù)對(duì)象，建立索引的方法createindexidx_stu_emilonstu(email);也可以在字段組合上建立索引，以后在查詢組合時(shí)效率就高，刪除索引同刪除表方法類似。忘了自己有哪些索引可以采用一點(diǎn)不要輕易建立索引，除非覺得訪問量特別大時(shí)。少等等。我可以建立一個(gè)視圖里面只包含我想要給他看的內(nèi)容。序列是Oracle數(shù)據(jù)庫(kù)獨(dú)有的東西，它產(chǎn)生一個(gè)唯一、不間斷的序列，作用是一般做主鍵。createtablearticle(titlevarchar2(1024),);如果要加入一篇文章，采用這種做法selectmax(id)fromarticle;然后知道了id往里插，但若有另外一個(gè)客戶端在做數(shù)據(jù)插入同時(shí)也在執(zhí)行完上面一條語(yǔ)句插入了一條記錄就會(huì)導(dǎo)致數(shù)據(jù)不一致的現(xiàn)象。為此我們可以創(chuàng)建一個(gè)sequence，每次插的時(shí)候訪問它的一個(gè)屬性silverbullet所以有時(shí)要具體問題具體分析，該打破三范式的時(shí)候還是要敢于打破。除了冗余性外還有一些邊緣性的要求，不是很重要，講到的時(shí)候再說(shuō)。第一范式的第一個(gè)要求-要有主鍵；第二個(gè)要求-列不可分，不能說(shuō)姓名我還要分成姓和名。多對(duì)多關(guān)系的表應(yīng)分割成三張表，如前一個(gè)面試題。個(gè)了解知道有這么回事。dbmsline('hi,pl_sql!');/相當(dāng)于而put_line相當(dāng)于println。/dbmsline('Error!');/異常捕獲到。字，這樣不易沖突如select、from等；第一個(gè)字符必須是字母；變量名最多包含30個(gè)種:binary_integer整數(shù)主要是計(jì)數(shù)而不是用來(lái)表示字段類型（用來(lái)for循環(huán)，數(shù)組下標(biāo)，候一定要給一個(gè)初值，否則是null。v_countbinary_integer:=0;v_validboolean:=false;dbmsline('v_tempvalue:'||v_date);/行限制。行的時(shí)候用/**/。dbmsline(‘Test’);的方式如：();v_:='aaaa';v_:='bj';dbmsline(v_||''||v_);/v_:='aaaa';v_:='bj';dbmsline(v_||''||v_);/PL_SQL語(yǔ)句里的select語(yǔ)句只能返回一條記錄，且select語(yǔ)句里面必須樣做：executeimmediate'createtableT(nnnvarchar2(20)default''aaa'')';由于外面已經(jīng)有單引號(hào)了所以里面的一個(gè)單引號(hào)得用兩個(gè)代表一個(gè)。default是給一個(gè)默認(rèn)值。PL_SQL里條件語(yǔ)句是形如if(條件)then…elsif(條件)then…else…endif;注意最后一個(gè)ibinary_integer:=0;dbmsline(i);i:=i+1;jbinary_integer:=1;dbmsline(j);j:=j+1;dbmsline(k);這類似于Java里的for循環(huán)，reverse表示逆序循環(huán)，1..10指定循環(huán)范圍。大家看一下，本來(lái)錯(cuò)誤處理也不是我們的重點(diǎn)。dbmsline('選定行數(shù)過(guò)多！');dbmsline('error');則我們寫的都是跨數(shù)據(jù)庫(kù)平臺(tái)的程序。下面學(xué)習(xí)一下作為dba記錄錯(cuò)誤常用的一種方法：首先創(chuàng)建一張表叫errorlog記錄錯(cuò)誤信creattableerrorlog();rollback;insertintoerrorlogvalues(seq_errorlog_,v_errcode,v_errmsg,sysdate);mit;cursorcisclosec;印兩次，而實(shí)際上最后是沒找到的，結(jié)果明cursorcis不易出錯(cuò)，平時(shí)常用。這是帶參數(shù)的游標(biāo)使用方法。cursorcisif(v_<200)thenelsif(v_=5000)thenendif;可更新的游標(biāo)，用得不多。is存儲(chǔ)過(guò)程沒有返回值，它就得借助于傳出參數(shù)，isif(v_a>v_b)thenv_ret:=v_a;elsev_ret:=v_b;endif;調(diào)用時(shí)采取這種方式：p(v_a,v_b,v_ret,v_temp);dbmsline(v_ret);/注意即便是你這過(guò)程有錯(cuò)誤同樣會(huì)被創(chuàng)建，為了展現(xiàn)出錯(cuò)的地方可以敲入命令：createorreplacefunctionsal_taxisif(v_sal<2000)thenreturn0.1;elsif(v_sal<2750)thenreturn0.15;elsereturn0.2;endif;調(diào)用方法如：selectlower(ename),sal_tax(sal)fromemp;actionvarchar2(10),);createorreplacetriggertrigifinsertingtheninsertintoemp2_logvalues(USER,‘insert’,sysdate);elsifupdatingthenelsifdeletingtheninsertintoemp2_logvalues(USER,’delete’,sysdate);endif;新：createorreplacetriggertrigcreatetablearticle(isleafnumber(1),--0代表非葉子節(jié)點(diǎn)，1代表葉子節(jié)點(diǎn));createorreplaceprocedurep(v_pid%type,v_levelbinary_integer)iscursorcisselect*fromarticlewherepid=v_pid;v_preStrvarchar2(1024):='';foriin1..v_levelloopv_preStr:=v_preStr||'****';forv_articleincloopif(v_=0)thenp(v_,);endif;2.學(xué)生表如下:刪除除了自動(dòng)編號(hào)不同,其他都相同的學(xué)生冗余信息號(hào),姓名,課程編號(hào),課程名稱,分?jǐn)?shù))四個(gè)球?qū)?，現(xiàn)在四個(gè)球?qū)M(jìn)行比賽，用一條sql語(yǔ)句顯示所有可能的比賽組合.答：selecta.*************************************************************************************selectyear,select*from(selectname,yearb1,lead(year)over()over(************************************************************************************說(shuō)明：復(fù)制表(只復(fù)制結(jié)構(gòu),源表名：a新表名：b)SQL:insertintob(a,b,c)selectd,e,ffromb;tablewhere=a.title)bSQL:selecta.a,a.b,a.c,b.c,b.d,b.ffromaLEFTOUTJOINbONSQL:select*from日程安排wheredatediff('minute',f開始時(shí)間,getdate())>5說(shuō)明：--說(shuō)明：--說(shuō)明：從數(shù)據(jù)庫(kù)中去一年的各單位電話費(fèi)統(tǒng)計(jì)(電話費(fèi)定額賀電化肥清單兩個(gè)表來(lái)源）SELECTa.userper,a.tel,a.standfee,TO_CHAR(a.telfeedate,'yyyy')AStelyear,SUM(decode(TO_CHAR(a.telfeedate,'mm'),'02',a.factration))ASFRI,SUM(decode(TO_CHAR(a.telfeedate,'mm'),'06',a.factratioSUM(decode(TO_CHAR(a.telfeedate,'mm'),'07',a.factration))ASJUL,SUM(decode(TO_CHAR(a.telfeedate,'mm'),'09',a.faFROM(SELECTa.userper,a.tel,a.standfee,b.telfeedate,b.factrationGROUPBYa.userper,a.tel,a.standfee,TO_CHAR(a.telfeedate,'yyyy說(shuō)明：四表聯(lián)查問題：SQL:select*fromaleftinnerjoinbona.a=b.brightinnerjoincona.a=c.cinnerjoindona.a=d.dwhere.....*******************************************************************************updatebsetb.value=(selecta.valuefromawherea.key=b***************************************************************************原表:5servlet80為了便于閱讀,查詢此表后的結(jié)果顯式如下(及格分?jǐn)?shù)為60):3xml40fail4jsp30fail---------------------------------------------------沒有裝ORACLE,沒試過(guò)（sign(score-60),-1,'fail','pass')asmarkfromcourse------------------------------5servlet80SQL>selectcourseid,coursename,score,decode(sign(score-60),-1,'fail','pass')asma3xml40fail4jsp30fail*******************************************************************************原表:使用pl/sql代碼實(shí)現(xiàn)，但要求你組合后的長(zhǎng)度不能超出oraclevarchar2長(zhǎng)度的限制。createorreplacetypestrings_tableistableofvarchar2(20);/createorreplacefunctionmerge(pvinstrings_table)returnvarchar2islsvarchar2(4000);foriin1..loopls:=ls||pv(i);returnls;/insertintotvalues(1,'Joan');insertintotvalues(1,'Jack');insertintotvalues(1,'Tom');insertintotvalues(2,'Rose');insertintotvalues(2,'Jenny');selectt0.id,merge(cast(multiset(selectnamefromtwheret.id=t0.id)asstrings_table)from(selectdistinctidfromt)t0;droptypestrings_table;droptablet;3selectd.deptno,d.dname,rtrim(e.ename||','||5orderbye.ename)||','||7orderbye.ename)||','||9orderbye.ename)||','||11orderbye.ename)||','||also先createfunctionget_a2;isCol_a2:='';Col_a2=Col_a2||cur.a2;returnCol_a2;selectdistincta1,get_a2(a1)fromunite_a*******************************************************************************去年應(yīng)聘一個(gè)職位未果,其間被考了一個(gè)看似簡(jiǎn)單的題,但我沒有找到好的大案.題為:有兩個(gè)表,t1,t2,Tablet1:Tablet2:-----------------------.......且用的方法不要增加數(shù)據(jù)庫(kù)負(fù)擔(dān),如用臨時(shí)表等.inserttable2values(convert(varchar(50),@i),'2001-10-1',100)inserttable2values(convert(varchar(50),@i),