Oracle基礎(chǔ)知識(shí)專(zhuān)項(xiàng)培訓(xùn)

1、第1頁(yè)神州數(shù)碼思特奇信息技術(shù)股份有限公司Oracle 基礎(chǔ)培訓(xùn)胡繼鋼內(nèi)容介紹紹Oracle基基礎(chǔ)(*)使用DDL(*)使用DML(*)PL/SQL基基礎(chǔ)知識(shí)識(shí) (*)PROC*C基基礎(chǔ)知識(shí)識(shí) (*)Oracle基基礎(chǔ)配置.ora文件客戶(hù)端tnsnames.ora文件的位位置和作作用文件位置置UNIX:$ORACLE_HOME/network/adminWIN:ORACLE_HOMEnet80admin作用保存著連連接到服服務(wù)器監(jiān)監(jiān)聽(tīng)端口口需要的的地址和和端口號(hào)號(hào)信息可配置多多個(gè)服務(wù)務(wù)器監(jiān)聽(tīng)聽(tīng)端口配置.ora文件客戶(hù)端tnsnames.ora的基本內(nèi)內(nèi)容在UNIX下配置和和使用Oracle屬性文

2、件件(.profileetc.)我們關(guān)注注的ORACLE_SID=las;export ORACLE_SIDORACLE_OWNER=oracle;exportORACLE_OWNERORACLE_BASE=/oracle/oracle/app/oracle;exportORACLE_BASEORACLE_HOME=$ORACLE_BASE/product/8.0.6;export ORACLE_HOMEORACLE_DOC=$ORACLE_HOME/doc; exportORACLE_DOCORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; expo

3、rtORA_NLS33TMPDIR=/tmp;export TMPDIRLD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib;export LD_LIBRARY_PATHPATH=$PATH:$ORACLE_HOME/bin:/usr/lbin:.; exportPATHNLS_LANG=American_America.zhs16gbk;exportNLS_LANGSHLIB_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:/usr/lib;exportSHLIB_PATH用戶(hù)、安安全和模模式內(nèi)容介紹紹Oracle基

4、礎(chǔ)管理理(*)使用DML(*)使用DDL(*)PL/SQL基礎(chǔ)知識(shí)識(shí)(*)PROC*C基礎(chǔ)知識(shí)識(shí)(*)Oracle基礎(chǔ)使用DML內(nèi)容介紹紹常用SQL語(yǔ)語(yǔ)句常用內(nèi)部部函數(shù)的的使用構(gòu)造高效效率的SQL使用DML查查詢(xún)數(shù)據(jù)據(jù)基本的Select查查詢(xún)語(yǔ)法法SELECTcolumn_listsFROM table_listsWHEREconditionsGROUPBYcolumn_listsHAVINGconditionsORDERBYcolumn_lists;使用DML查查詢(xún)數(shù)據(jù)據(jù)選擇數(shù)據(jù)據(jù)SELECTlast_name,first_name,specialtyFROM artist;排列數(shù)據(jù)據(jù)使用O

5、RDERBY降序：DESC升序：ASC（默認(rèn)）SELECTlast_name,first_name,specialtyFROM artistORDERBYfirst_name DESC;使用DML查查詢(xún)數(shù)據(jù)據(jù)約束結(jié)果果使用WHERESELECTlast_name,first_name,specialtyFROM artistWHEREspecialty=DIGITAL;比較運(yùn)算算符，=，=LIKE%匹配字字符串，_匹配字字符BETWEEN，INISNULL，IS NOTNULL使用DML查查詢(xún)數(shù)據(jù)據(jù)使用WHERE的注注意事項(xiàng)項(xiàng)引用的字字符必須須在單引引號(hào)內(nèi)，而不是是雙引號(hào)號(hào)如果需要要在字符符串

6、內(nèi)放放單引號(hào)號(hào)，應(yīng)該該寫(xiě)兩次次，例如如想比較較wont，在WHERE中應(yīng)寫(xiě)寫(xiě)成wont注意AND和OR的使使用要考慮空空值的影影響使用DML查詢(xún)數(shù)據(jù)據(jù)關(guān)聯(lián)表Cartesian產(chǎn)品使用DML查查詢(xún)數(shù)據(jù)據(jù)使用相等等關(guān)聯(lián)來(lái)來(lái)比較列列SELECTbook_id,book_title,bl_book_id,bl_student_idFROM books,books_loanedWHEREbook_id=bl_book_id;使用表的的別名SELECTb.book_id,b.book_title,bl.bl_book_id,bl.bl_student_id,s.student_id, s.first_na

7、me,s_last_nameFROM books b, books_loanedbl,studentssWHEREb.book_id=bl.bl_book_idandbl.bl_student_id=s.student.id;使用DML查查詢(xún)數(shù)據(jù)據(jù)內(nèi)部關(guān)聯(lián)聯(lián)兩個(gè)表之之間的內(nèi)內(nèi)部關(guān)聯(lián)聯(lián)返回兩兩個(gè)表中中都符合合的記錄錄的行。使用DML查查詢(xún)數(shù)據(jù)據(jù)外部關(guān)聯(lián)聯(lián)外部關(guān)聯(lián)聯(lián)關(guān)聯(lián)了了兩個(gè)表表并使得得即使第第二個(gè)表表沒(méi)有與與第一個(gè)個(gè)表符合合的記錄錄時(shí)也能能返回結(jié)結(jié)果。使用DML查查詢(xún)數(shù)據(jù)據(jù)使用GROUPBY子句句原理Oracle對(duì)對(duì)查詢(xún)檢檢索數(shù)據(jù)據(jù)并在臨臨時(shí)存儲(chǔ)儲(chǔ)區(qū)中保保存；Oracle對(duì)對(duì)數(shù)據(jù)排排序，并并和

8、GROUP BY值一起起對(duì)所有有數(shù)據(jù)分分組；Oracle把把指定的的聚集函函數(shù)應(yīng)用用到每個(gè)個(gè)記錄組組并對(duì)每每組返回回一行。注意由于求和和查詢(xún)對(duì)對(duì)每組返返回一行行，所以以在選擇擇列中不不可能有有GROUPBY子子句中沒(méi)沒(méi)有列出出的列，除非它它們應(yīng)用用了一個(gè)個(gè)聚集函函數(shù)。使用DML查查詢(xún)數(shù)據(jù)據(jù)例子正確SELECTspecialty,COUNT(*)FROM artistGROUPBYspecialty;錯(cuò)誤SELECTspecialty,last_name, first_name, COUNT(*)FROM artistGROUPBYspecialty;使用DML查查詢(xún)數(shù)據(jù)據(jù)使用HAVING子子句

9、HAVING和和WHERE的的區(qū)別在在于HAVING子句句中的條條件是被被求和后后應(yīng)用到到數(shù)據(jù)，它只對(duì)對(duì)指定組組有興趣趣。SELECTspecialtyFROM artistGROUPBYspecialtyHAVINGCOUNT(*)=1ANDspecialtyOTHER;SELECTspecialtyFROM artistWHEREspecialtyOTHERGROUPBYspecialtyHAVINGCOUNT(*)=1;使用DML查查詢(xún)數(shù)據(jù)據(jù)使用DISTINCT和ALL關(guān)關(guān)鍵字默認(rèn)為ALLSELECTCOUNT(DISTINCTspcialty)FROM artist;SELECTCOU

10、NT(spcialty)FROM artist;使用DML修修改數(shù)據(jù)據(jù)插入數(shù)據(jù)據(jù)基本形式式INSERTINTOtablename(columnname1, columnname2,)VALUES(value1,value2, );子查詢(xún)形形式INSERTINTOtablename(columnname1, columnname2,)subquery;使用DML修修改數(shù)據(jù)據(jù)更新數(shù)據(jù)據(jù)基本形式式UPDATEtablenameSETcolumnname1=expression1,columnname2=expression2,WHEREconditions;子查詢(xún)形形式UPDATEtablename

11、SET(columnname1,columnname2,)=(SELECT columnname3,columnname4,FROM tablename2WHEREconditions)WHEREconditions;使用DML修修改數(shù)據(jù)據(jù)刪除數(shù)據(jù)據(jù)基本形式式DELETEFROMtablenameWHEREconditions;子查詢(xún)形形式DELETEFROMtablenameWHEREsubquery;使用DML常用內(nèi)部部函數(shù)常用內(nèi)部部函數(shù)(1)通用函函數(shù)LOWER()函數(shù)UPPER()函數(shù)DECODE()函數(shù)INSTR()函數(shù)SUBSTR()函數(shù)NVL()函數(shù)LENGTH()函數(shù)常用內(nèi)部部

12、函數(shù)(2)數(shù)字函函數(shù)ROUND()函數(shù)TRUNC()函數(shù)MOD()函數(shù)連接運(yùn)算算符：|使用DML常常用內(nèi)部部函數(shù)常用內(nèi)部部函數(shù)(3)日期期函數(shù)SYSDATEMONTHS_BETWEEN()函數(shù)NEXT_DAY()函數(shù)LAST_DAY()函數(shù)ADD_MONTHS()函函數(shù)ROUND()函數(shù)TRUNC()函數(shù)常用內(nèi)部部函數(shù)(4)格式式轉(zhuǎn)換函函數(shù)TO_CHAR()函函數(shù)TO_DATE()函函數(shù)TO_NUMBER()函數(shù)數(shù)常用的格格式轉(zhuǎn)化化使用DML常常用內(nèi)部部函數(shù)常用內(nèi)部部函數(shù)(5)聚合函函數(shù)AVG()函數(shù)數(shù)COUNT()函數(shù)MAX()函數(shù)數(shù)MIN()函數(shù)數(shù)SUM()函數(shù)數(shù)使用DML構(gòu)造高高效率的的

13、SQL內(nèi)容提要要使用索引引應(yīng)該避免免的問(wèn)題題使用DML構(gòu)造高高效率的的SQL使用索引引索引可以以成百上上千倍的的提高SQL語(yǔ)語(yǔ)句效率率查詢(xún)系統(tǒng)統(tǒng)中的索索引按照索引引的順序序訪(fǎng)問(wèn)表表使用效率率最高的的索引使用DML構(gòu)造高高效率的的SQL應(yīng)該避免免的問(wèn)題題盡量不要要在檢索索條件中中對(duì)列使使用函數(shù)數(shù)，因?yàn)闉闀?huì)阻止止索引的的使用如果可以以使用where條件件，盡量量不要在在having中限制制數(shù)據(jù)盡量不要要使數(shù)據(jù)據(jù)排序引起排序序的條件件OrderbyGroupbyUnion,intersect,minusDistinct謹(jǐn)慎使用用in,exists內(nèi)容介紹紹Oracle基基礎(chǔ)管理理(*)使用DML(*

14、)使用DDL(*)PL/SQL基基礎(chǔ)知識(shí)識(shí) (*)PROC*C基基礎(chǔ)知識(shí)識(shí) (*)Oracle基基礎(chǔ)使用DDL管管理表了解表表是關(guān)系系數(shù)據(jù)庫(kù)庫(kù)的基礎(chǔ)礎(chǔ)，它們們典型的的由下面面幾個(gè)主主要元素素組成：列定定義存存儲(chǔ)在表表中的數(shù)數(shù)據(jù)約束 限制制存儲(chǔ)在在表中的的數(shù)據(jù)索引 允許許根據(jù)一一列或多多列中的的值快速速訪(fǎng)問(wèn)一一個(gè)表中中的數(shù)據(jù)據(jù)使用DDL 管理理表列名列名必須須以字母母開(kāi)頭列名最多多可包括括30個(gè)個(gè)字符列名的第第一個(gè)字字母后，可由字字母、數(shù)數(shù)字、（#）、（$）、（_）任意意組合而而成除非在括括號(hào)中引引用，否否則Oracle自自動(dòng)會(huì)將將所有列列名轉(zhuǎn)化化為大寫(xiě)寫(xiě)字母約束主鍵 唯一一且非空空唯一鍵唯唯一

15、外部鍵連連接包括括相關(guān)信信息的兩兩個(gè)表檢查 允許許定義一一個(gè)強(qiáng)制制性條件件，在一一行被存存入表之之前它它必須為為真，比比如NOTNULL使用DDL 管理理表建立表CREATETABLEaquatic_animal(id_nonumber(10),tank_nonumber(10),animal_namevarchar2(30),birth_datedate,death_datedate,markings_descvarchar2(30),CONSTRAINT aquatic_animal_pkPRIMARYKEY(id_no)USINGINDEXTABLESPACEindx,CONSTRAIN

16、T aquatic_name_tankUNIQUE(animal_name, tank_no)USINGINDEXTABLESPACE indxSTORAGE(INITIAL 50KNEXT 10K)TABLESPACEusers;使用DDL 管理理表修改表增加列和和約束ALTERTABLEtablenameADD(column_or_constraint,column_or_constraint);例如：ALTERTABLEaquatic_animalADD(animal_sexchar,CONSTRAINT animal_sex_mfCHECK(animal_sexIN(M, F);使用D

17、DL 管理理表刪除約束束ALTERTABLEtablenameDROP CONSTRAINTconstraintname;如果打算算刪除一一個(gè)主鍵鍵約束或或者一個(gè)個(gè)唯一鍵鍵約束而而它有外外部鍵約約束，必必須指定定cascade選項(xiàng)項(xiàng)，比如如：ALTERTABLEtankDROP CONSTRAINTtank_pkcascade;使用DDL 管理理表刪除列ALTERTABLEtablenameDROP COLUMNcolumnname;在Oracle8i以以前的版版本，刪刪除一個(gè)個(gè)列的唯唯一方法法是先刪刪除整個(gè)個(gè)表然后后從臨時(shí)時(shí)本中重重建一個(gè)個(gè)。臨時(shí)時(shí)本是一一個(gè)為了了滿(mǎn)足保保存和還還原數(shù)據(jù)據(jù)而建

18、立立的進(jìn)程程。在大表中中刪除列列時(shí)要小小心。Oracle實(shí)實(shí)際上讀讀取表中中的每一一行，并并從每一一行中刪刪除列?？焖賱h除除一列的的方法ALTERTABLEtablenameSET UNUSED(columnname);ALTERTABLEtablenameDROPUNUSEDCOLUMNS;使用DDL 管理理表修改列ALTERTABLEtablenameMODIFY(columnname1typedefine,);比如：ALTERTABLEaquatic_animalMODIFY(animal_namevarchar2(60);使用DDL 管理理表刪除表DROP TABLE tablenam

19、e;當(dāng)其它表表有外部部鍵指向向被刪除除的表時(shí)時(shí)，需要要加選項(xiàng)項(xiàng)CASCADECONSTRAINS。DROP TABLE aquatic_animal CASCADECONSTRAINS;使用DDL 管理理索引建立索引引CREATEUNIQUEINDEXindexnameONtablename(columnname1,columnname2,)TABLESPACEtablespacename;使用DDL 管理理索引修改索引引修改索引引名稱(chēng)ALTERINDEXoldindexnameRENAMETOnewindexname;修改索引引屬性ALTERINDEXindexnameREBUILD ;如果

20、想向向索引中中增加列列，必須須先刪除除索引然然后再重重建。刪除索引引DROP INDEX indexname;Oracle編編程 PL/SQLSQL和和PL/SQLSQL是是一種ANSI標(biāo)準(zhǔn)類(lèi)類(lèi)型的描描述語(yǔ)言言；PL/SQL是Oracle公公司開(kāi)發(fā)發(fā)的一種種對(duì)SQL語(yǔ)言言進(jìn)行了了擴(kuò)充的的過(guò)程化化語(yǔ)言。二者都可可以在數(shù)數(shù)據(jù)庫(kù)的的服務(wù)器器上運(yùn)行行。二者之間間可以相相互調(diào)用用。PL/SQL支支持?jǐn)?shù)據(jù)據(jù)庫(kù)使用用的多種種標(biāo)準(zhǔn)數(shù)數(shù)據(jù)類(lèi)型型。Oracle編編程 PL/SQLPL/SQL塊塊的結(jié)構(gòu)構(gòu)變量聲明明（可選選部分）程序代碼碼異常處理理（可選選部分）DECLAREBEGINEXCEPTIONEND;Or

21、acle編程PL/SQL變量聲明明包含內(nèi)容容賦予變量量適當(dāng)?shù)牡拿Q(chēng)賦予變量量正確的的數(shù)據(jù)類(lèi)類(lèi)型定義標(biāo)量量變量和和記錄類(lèi)類(lèi)型變量量（記錄錄由標(biāo)量量組成）控制變量量的范圍圍命名規(guī)則則變量名必必須由字字符開(kāi)頭頭在首字符符之后，變量名名應(yīng)包括括：字母母、數(shù)字字、_、$、#變量的長(zhǎng)長(zhǎng)度范圍圍：130變量名大大小寫(xiě)不不敏感變量名不不能和語(yǔ)語(yǔ)言中的的關(guān)鍵子子重復(fù)數(shù)據(jù)類(lèi)型型Oracle編編程 PL/SQLDBMS_OUTPUT包使用前定定義：SETSERVEROUTPUT ONDBMS_OUTPUT包允允許使用用SQL*Plus從從PL/SQL中顯示示輸出信信息輸出過(guò)程程DBMS_OUTPUT.PUT_LIN

22、E允許顯示示一行文文本DBMS_OUTPUT.PUT允許顯示示一段文文本，但但不能生生成換行行符DBMS_NEW_LINE生成換行行符Oracle編程PL/SQL語(yǔ)句類(lèi)型型分支控制制語(yǔ)句IFexpression THENstatementsELSIFexpressionstatementsELSEstatementsENDIF;Oracle編編程 PL/SQL循環(huán)語(yǔ)句句PL/SQL支支持三種種不同類(lèi)類(lèi)型的循循環(huán)結(jié)構(gòu)構(gòu)：WHILE循環(huán)環(huán)FOR循循環(huán)基本循環(huán)環(huán)WHILE循環(huán)環(huán)WHILEexpression LOOPstatementsENDLOOP;FOR循循環(huán)FORcounterINREVERSEstartvalueendvalueLOOPstatementsENDLOOP;Oracle編編程 PL/SQL基本循環(huán)環(huán)沒(méi)有內(nèi)部部控制的的循環(huán)結(jié)結(jié)構(gòu)，在在程序代代碼中編編寫(xiě)代碼碼來(lái)結(jié)束束該循環(huán)環(huán)。LOOPstatements