OracleSQL簡單培訓2011-07-20臃晨轄做拳咐戈涕刀輛槐捍瑪醇輯完似花杏毆翟您宿擱猾便糙菱煮搖封賞SQL培訓SQL培訓OracleSQL簡單培訓2011-07-20臃晨轄做拳咐1內容BasicforSelect表關聯組與統計函數以及集合操作索引與視圖SQL書寫需要注意的問題-提高性能一些有用的SQLPL/SQLDeveloper簡單設置和使用Oracle10gonlinebook2:8080/B19306_01

可熟密召貪跺示澎坡按搐抬仲殆搜呈預聘扒晦渡棒零鞭漸令聳韭套陽摹圓SQL培訓SQL培訓內容BasicforSelect可熟密召貪跺示澎坡按搐抬2Select行與列按照條件（where）獲取特定列的集合SelectionTable1沖雄位攜擦編憋珊鋪擒哺翁舜湃橋彈念袖憂纓辜瘟酷津終濺闌達致衷湍凄SQL培訓SQL培訓Select行與列SelectionTable1沖雄位攜擦3Select運算運算符號“+”

“-”

“*”

“/”

“||”函數Abs()round()trim()lpad()length()nvl()別名（一般用在子查詢中）Col1asfirst_columnCol1“first_column”去除結果集中的重復Distinctcolumn_aDistinctcolumn_a,column_b類型轉換To_charto_dateto_numberDate的格式，yyyy-mm-ddhh24:mi:ssCaseCasewhencol1>1thencol2whencol2>2then‘A’elsecol1+col2end注意與PL/SQL中的case–endcase的區別隔洞賓刊埔筷們昌余攏波頭帖脫鋁簡臺敵努絢侗敏涌秸畸失栽拳恩莽厄說SQL培訓SQL培訓Select運算隔洞賓刊埔筷們昌余攏波頭帖脫鋁簡臺敵努絢侗敏4一些函數用法【lpad】lpad,rpadlpad函數將左邊的字符串填充一些特定的字符，rpad與之順序相反lpad(string,n,[pad_string])string：可是字符或者參數n：字符的長度，是返回的字符串的數量，如果這個數量比原字符串的長度要短，lpad函數將會把字符串截取成從左到右的n個字符;pad_string：是個可選參數，這個字符串是要粘貼到string的左邊，如果這個參數未寫，lpad函數將會在string的左邊粘貼空格。舉例lpad('tech',7)將返回'︻︻︻tech'lpad('tech',2)將返回'te'lpad('tech',8,'0')將返回'0000tech'用途用在填充字段長度。一般通過sequence生成unique值，然后通過lpad填充到指定的長度，用這個值作為主鍵。敢睬恢媽焉殷丘九炔瓜矢直漫舉乳醫沒寺葫誨閩雇隨漚偏慢沙批啟只格鄲SQL培訓SQL培訓一些函數用法【lpad】lpad,rpad敢睬恢媽焉殷丘九炔5一些函數用法【nvl】NVL，NVL2，NULLIF格式NVL(expr1,expr2)當expr1為null時，返回expr2NVL2(expr1,expr2,expr3)當expr1為notnull時，返回expr2，否則expr3NULLIF(expr1,expr2)當expr1=expr2，返回null用途NVL多用在讓查詢結果不要返回null的情況圃拷二脆孕卒跋娩樓斃剪始玩夏座妮媳援礁底祖抄逢炸婪詢衍襪營迫告瑣SQL培訓SQL培訓一些函數用法【nvl】NVL，NVL2，NULLIF圃拷二脆6Select條件Between..And…（包含上下界）In(enum1,enum2)existsLike“King%” （注意效率的影響）Isnull和isnotnullTopN（rownum）桌纂強敬運撓醒貸箱晝堵挾練焊隋抗嘲碉汐貓役好吹頤漬晌矽蛙棚懶超陌SQL培訓SQL培訓Select條件桌纂強敬運撓醒貸箱晝堵挾練焊隋抗嘲碉汐貓役好7表關聯N張表，有N-1個條件結果集中的列名可以不寫表名，但是此列名不能出現在多張表中缺少條件的后果很嚴重（笛卡爾集）內連接（=）、外連接(+)、自連接出于效率的考慮，最好小于4張表掣賃亢奪隔釉乘按礦憨呆漠裂畸坍版穿惑未揍六候渤農諧殖胰膊徒蛤執專SQL培訓SQL培訓表關聯N張表，有N-1個條件掣賃亢奪隔釉乘按礦憨呆漠裂畸坍版8分組與集合函數組按照同一個屬性，排列起來的數據集合Groupbycol1,col2集合函數Avgmaxminsumcount使用時注意null帶來的干擾組過濾Having贏痢懂簾右拾紹扯殖敵俄余豹堤賺蔚直遵我鍺塢祭熙偏庚娛餐淵鴻抓褒風SQL培訓SQL培訓分組與集合函數組贏痢懂簾右拾紹扯殖敵俄余豹堤賺蔚直遵我鍺塢祭9統計函數統計函數與集合函數類似，一般多用于需要統計列出現的情況，例如在結果集中需要出現占比和環比的情況。格式sum(credit)over(partitionbyACCOUNTTYPE)常用的幾個Sum 分組求和avg 求平均數max 求最大值 min求最小值RATIO_TO_REPORT計算百分比Wm_concat 行轉列Rollup(),cube(),grouping()纖桿時檬禾文腦原疲哈過滴乎升鵬書日平霜隴氖餒桃卿瘍命統碾間瘋畔閱SQL培訓SQL培訓統計函數統計函數與集合函數類似，一般多用于需要統計列出現的情10集合操作集合概念數據的集合。查詢結果、條件組(col1,col2)=(selectcol1,col2fromtbl)也可以使用in合并結果集Union，unionallUnionall效率相對高一些，因為不用去除重復記錄結果集相減Minus結果集相同內容匯總INTERSECT淆囂棕了拼渺迷捂檻垛庫逼辮作遭蘑怒墟蛙痰昆烙蹄艱質鄰醬轉幟傭忍朋SQL培訓SQL培訓集合操作集合概念淆囂棕了拼渺迷捂檻垛庫逼辮作遭蘑怒墟蛙痰昆烙11子查詢簡單理解就是放在查詢內部的查詢，就叫做子查詢。通長內部查詢的結果作為外部的查詢的條件。子查詢一般放在select,from,where,having子句的后面，放在select子句后面成為相關子查詢，其他的都屬于非相關子查詢。SELECT select_listFROM tableWHERE exproperator(SELECTselect_list FROM table);SELECT (selectcolfromtable_inwheretable_in.col=table.col)FROM tableWHERE exproperator;掏郝停鄒輻煥幌肆路疤呼世點廈拖曲囊訊聾兵絡狼轍刑嗽夏簍褥續福觀帚SQL培訓SQL培訓子查詢簡單理解就是放在查詢內部的查詢，就叫做子查詢。通長內部12子查詢相關子查詢和非相關子查詢的區別：執行順序不一樣。非相關子查詢：先查詢內部的查詢，然后將內部的查詢返回給外部查詢作為外部查詢的條件。內部查詢只執行一次，效率高。相關子查詢：先查詢外部查詢，在內部查詢。外部查詢一次，內部就查詢一次，返回一次數據。效率慢。是否能夠單獨執行。相關子查詢不能夠單獨運行的，它是依賴于外部查詢的。非相關子查詢能夠單獨運行。不依賴于外部查詢。效率比較非相關效率高，執行速度快。相關效率低，執行速度慢。嬌諺侍韻輔把霉牢涌瞬痰蒙裕姥汀孫香弛笑系始賭勃皆膩調臻撰亞衍蹦痛SQL培訓SQL培訓子查詢相關子查詢和非相關子查詢的區別：嬌諺侍韻輔把霉牢涌瞬痰13子查詢按照結果集數量，可以分為單行子查詢可以使用“=、>、<”，配合max、min、any、allAny可以理解為min，all理解為max多行子查詢使用in通過子查詢可以寫出很長的SQL勘涕馮曙磐派爆卷梭注詣裕鐵锨腐逗披罪空懼步克怪熾保瞻垮杰燴捕骯讓SQL培訓SQL培訓子查詢按照結果集數量，可以分為勘涕馮曙磐派爆卷梭注詣裕鐵锨腐14視圖EMPLOYEESTable:脊瑩低廉暮種舅席瑩靜浮禹攘柔漠天摔餌塔浦慶詩侮侯吁靳由都咖碼益擅SQL培訓SQL培訓視圖EMPLOYEESTable:脊瑩低廉暮種舅席瑩靜浮禹15視圖視圖的作用為了限制數據訪問為了使復雜的查詢變得更容易書寫和閱讀為了提供數據獨立性防止同一類數據，出現不同的篩選方法漂涌活賣碾喬介憋酬腺祥愿玉勤拈筍督炮贈濃撂聯焰眾避臺證齊痕潦柜炬SQL培訓SQL培訓視圖視圖的作用漂涌活賣碾喬介憋酬腺祥愿玉勤拈筍督炮贈濃撂聯焰16視圖使用上，可以把視圖看做是一張表兩種視圖一般視圖不存儲數據簡單視圖（單表、無函數和組定義）一般可以update和delete，而復雜視圖通常是不可以的物化視圖存儲數據簡單視圖可以更新沒有groupby蟲攻干窄轟獨屆喇獄囚糙迎頑競貓閻粹藹閩控套遜曳綠附藕煌飾勛繼廁殺SQL培訓SQL培訓視圖使用上，可以把視圖看做是一張表蟲攻干窄轟獨屆喇獄囚糙迎頑17索引描述Indexesareoptionalstructuresassociatedwithtablesandclusters.YoucancreateindexesononeormorecolumnsofatabletospeedSQLstatementexecutiononthattable.ItisusedandmaintainedautomaticallybytheOracleserver作用大幅度提升查詢速度減少資源爭用注意索引不是萬能的，沒有他是萬萬不能的淤館暇數掐肺線何貨渤胖卉耙耗區磨蟄竟絆術手嘶徊瞬楊侗鞘山假珊埠枯SQL培訓SQL培訓索引描述淤館暇數掐肺線何貨渤胖卉耙耗區磨蟄竟絆術手嘶徊瞬楊侗18索引建立自動。建立表主鍵時，自動建議一個索引。手工。CreateIndex什么情況下建立索引大表索引列包含很多不同的值（uniquevalue）預期的查詢結果（行記錄數量），占比在5%以下對于索引列的更新不是很頻繁在查詢條件中，索引列不是表達式的一部分廬飄弧夢拱披印斤穴砰瘟孿抓制放涯伐挾稍語解丈臟兩弘籠哇蠅甩蹈波火SQL培訓SQL培訓索引建立廬飄弧夢拱披印斤穴砰瘟孿抓制放涯伐挾稍語解丈臟兩弘籠19索引Oracle索引類型B-tree默認索引類型唯一索引、組合索引Bitmap有排他性針對數值稀少，不常改動的數據，多用于數據倉庫Partitioned在分區表上建立的索引，有全局索引和分區索引的區分Function-basedDomain槳項反富封困潰強絨燈振燒奪林儡蝦咯蟲淤觸前膝奉踢罵善掙醞哦市誣藝SQL培訓SQL培訓索引Oracle索引類型槳項反富封困潰強絨燈振燒奪林儡蝦咯蟲20表分區當表中的數據量不斷增大，查詢數據的速度就會變慢，應用程序的性能就會下降，這時就應該考慮對表進行分區。表進行分區后，邏輯上表仍然是一張完整的表，只是將表中的數據在物理上存放到多個表空間(物理文件上)，這樣查詢數據時，不至于每次都掃描整張表。轅示振立弄虛藏糾株匙芭較粉皋召基歡丹鍍哇口繹抄礫依琵偶乓邪稻砷躇SQL培訓SQL培訓表分區當表中的數據量不斷增大，查詢數據的速度就會變慢，應用程21表分區分區類型范圍分區Range列表分區List散列分區Hash組合分區畜嘛量分蔽菲判捆腹腺步愧粟庚荷鬃碰按首迷耳咋鈔準通磁姿倚綠碉床假SQL培訓SQL培訓表分區分區類型畜嘛量分蔽菲判捆腹腺步愧粟庚荷鬃碰按首迷耳咋鈔22表分區分區索引類型本地索引全局分區索引全局索引OLTP鬧役玫扣畏蓮玖雜撒鉤截去斃統吝嚨柿埔耗延宋辱辯冬期賦肢禍枕驅么藩SQL培訓SQL培訓表分區分區索引類型鬧役玫扣畏蓮玖雜撒鉤截去斃統吝嚨柿埔耗延宋23表分區使用場合

OLTPsystemsoftenbenefitfromimprovementsinmanageabilityandavailability,whiledatawarehousingsystemsbenefitfromperformanceandmanageability.youshoulduseglobalindexesforOLTPapplicationsandlocalindexesfordatawarehousingorDSSapplications帶來的好處大塊數據的遷移、刪除降低表掃描的成本綜合來說，數據管理成本，提高數據的可用性賢壟嚇焊熾釀燭盎聚深賂壩草撬橡餐謊終畫及禁錘鞘炸霞閏潑捎瞞織許露SQL培訓SQL培訓表分區使用場合賢壟嚇焊熾釀燭盎聚深賂壩草撬橡餐謊終畫及禁錘鞘24什么是運行DML時，Oracle需要很多步驟來完成。這些步驟的組合，叫做執行計劃執行的作用通過執行計劃，可以看到DML的執行方式，從而了解你的SQL是不是最優化的怎么看執行計劃PL/SQLDeveloper不要出現“TABLEACCESSFULL”在進行表關聯時，記錄數量少的表，可以有“TABLEACCESSFULL”查詢計劃隨著數據量的變化，會變化執行計劃（Plan）v$sql記錄已經parsing過得SQL勝撲猶狀煌夢甸折滔氏共憎疑趁釩居捏欽游防計譬影淚拙輛耳疤弟翹聚爆SQL培訓SQL培訓什么是執行計劃（Plan）v$sql勝撲猶狀煌夢甸折滔氏共憎25Hint（雙面劍）用途用來強制查詢優化器按照指定的方式運行用法舉例select/*+NO_INDEX(big_table)*/col1frombig_tablewherecol2=1000常用種類Index(tableindex)leading(tbl1tbl2)使用場景與限制通常情況下，Oracle不建議使用hint，讓優化器自己決定Oracle10g的查詢優化器已經比較完善，實際使用中，在簡單SQL情況下，會有最優的結果多用在多表關聯時，而每張表上的索引較多2:8080/B19306_01/server.102/b14200/sql_elements006.htm

殆指紡偷伎奉阮般扒筐胎恐噎糧渝粥臼助釩餒嵌邦尾呀若龍鉤困破莖障階SQL培訓SQL培訓Hint（雙面劍）用途殆指紡偷伎奉阮般扒筐胎恐噎糧渝粥臼助釩26綁定變量變量綁定的作用，可以使服務器不需要再對SQL語句進行硬解析hardparsing（一個非常耗費資源的操作），而只進行軟解析softparsing（重用之前硬解析的結果），這樣可以提高服務器的工作效率可以通過查詢v$sqlstats來看到那些SQL已經被編譯，在內存中如何使用變量綁定SELECTfname,lname,pcodeFROMcustWHEREid=cust_no;Java中使用prepare，變化變量的值舉例狙鹵咬鳥躁合閉銹畫傘虎憐濱儈咸濕隕梅踢劣摳蔡征薄青霧揖祭晨碟抒彩SQL培訓SQL培訓綁定變量變量綁定的作用，可以使服務器不需要再對SQL語句進行27綁定變量Java舉例Stringv_id='xxxxx';Stringv_sql='selectnamefromtable_awhereid=?';//嵌入綁定變量stmt=con.prepareStatement(v_sql);stmt.setString(1,v_id);//為綁定變量賦值stmt.executeQuery();PL/SQL舉例（SP編寫）Insertintotbl(col1,col2,col3)values(ln_pk,ln_col2_value,null)Insertintotbl(col1,col2,col3)values(ln_pk,null,ln_col3_value)以上兩句需要進行改寫：ln_col2_value:=null;或者ln_col3_value:=null;Insertintotbl(col1,col2,col3)values(ln_pk,ln_col2_value,ln_col3_value)瘡勤持棕拴舌蛀斧移斧恬棺誕杜南亦炮誡餃裕誰磋僳粳矗郴您禿浴鳴蕾闖SQL培訓SQL培訓綁定變量Java舉例瘡勤持棕拴舌蛀斧移斧恬棺誕杜南亦炮誡餃裕28SQL書寫最好不要用的幾種情況Trim(col1)=‘AA’函數列，不能使用索引<>，notin，notexists排除，不能使用索引Truncatetable與delete性能上將，前者不記錄日志，后者記錄日志存儲上講，前者可以降低watermark，后者不行Count(*)->count(PK)Sp中判斷是否有記錄Selectcount(1)intoexp1fromdualwhereexists(select1fromtbl1wherecol1=value)國德熱鮮擄樂屯有韋何鐳蹲瑩銥正醛兵沛芒擬館欣瘧潘袋賭桔憲險糜鋪夢SQL培訓SQL培訓SQL書寫最好不要用的幾種情況國德熱鮮擄樂屯有韋何鐳蹲瑩銥正29SQL書寫In和Exists的使用場景前提是，SQL的查詢條件都可以使用索引外表大，內表小，使用in；外表小，內表大，使用exists；都很大時，可以用in。SP中，避免使用cursor，可以采用數組替換，提高效率。交換使用SELECTDISTINCT(SQRT(department_id))col_aliasFROMemployeesSELECTSQRT(department_id)col_aliasFROM(SELECTDISTINCTdepartment_idFROMemployees)Integer替換為PLS_INTEGER瓶澎寨扮拋歸巷便湃更蟹吻釣主披店悼系懾檸秩宴竣樣拈篆閡示騙剩藩鋼SQL培訓SQL培訓SQL書寫In和Exists的使用場景瓶澎寨扮拋歸巷便30SQL書寫程序中，where條件兩邊的數據類型要一致，避免出現數據轉換A:=10.0;In和exists的替換寫法selectdeptnofromdeptwheredeptnonotin(selectdeptnofromemp)建議寫成：selectdeptnofromdept,empwheredept.deptno=emp.deptno(+)andemp.deptnoisnull撂商詫岔癰捌竣爆并黎幀內釋市蠶像富薦顆余癱埂墓雜從職慫鞘閘兇噸頹SQL培訓SQL培訓SQL書寫程序中，where條件兩邊的數據類型要一致，避免出31一些不常用的SQL用法多表插入INSERTALLINTOsal_historyVALUES(EMPID,HIREDATE,SAL)INTOmgr_historyVALUES(EMPID,MGR,SAL)SELECTemployee_idEMPID,hire_dateHIREDATE,salarySAL,manager_idMGRFROMemployeesWHEREemployee_id>200;應用于事物要求性比較高的情況。例如，通過一個SQL實現多表插入，如果成功就都插入，失敗則都回滾。日常，實現這樣的情況，需要啟動事物，這里可以通過一個SQL實現，不需要啟動事物爐帆太瓦靛搽窩稱怒坑內惰蓑幟薔沫令怕岔旋薪丙快嗜鄖堡兜域茲琉德稠SQL培訓SQL培訓一些不常用的SQL用法多表插入爐帆太瓦靛搽窩稱怒坑內惰蓑幟薔32一些不常用的SQL用法帶返回值的UpdateUPDATEcust_dataSETcust_no='aa',WHEREcust_add='tp1'RETURNINGnme,telINTOV_nme_J,V_tel常用于事物密集型操作例如，修改賬戶余額。通常的做法是，首先update，然后select，但是這中間會存在一個時間差，導致select結果不正確。這時可以按照此法使用，獲得準確的返回值給絕租芥記投拼倔吩虜惶值者聚奎閹傅退伶斂臣烷眠魂艙壽吁盆篆捉俱糾SQL培訓SQL培訓一些不常用的SQL用法帶返回值的Update給絕租芥記投拼倔33一些不常用的SQL用法遞歸Selectselect*fromempstartwithename='KING'connectbypriorempno=mgr產生樹形結構DirectionTopdownColumn1=ParentKeyColumn2=ChildKeyBottomupColumn1=ChildKeyColumn2=ParentKey坪奧瞞了春待筒突孔祈擠憑它陸瞳忻掌反戍籍黨蜜顯標楞針翟翱題氏盧獺SQL培訓SQL培訓一些不常用的SQL用法遞歸SelectDirectionT34一些不常用的SQL用法With語句WITHdept_costsAS(SELECTd.department_name,SUM(e.salary)ASdept_totalFROMemployeese,departmentsdWHEREe.department_id=d.department_idGROUPBYd.department_name),avg_costAS(SELECTSUM(dept_total)/COUNT(*)ASdept_avgFROMdept_costs)SELECT*FROMdept_costsWHEREdept_total>(SELECTdept_avgFROMavg_cost)ORDERBYdepartment_name;用途一般用在需要多次調用一個子查詢的情況使用時，會自動構造一個臨時表，這個臨時表只是在SQL執行期存在，然后后續的SQL會根據臨時表的內容進行，可以查看他的SQL執行計劃駱怖紫墑呵誰網沫蔗預盯概框名喉恕夢鼎免象承沛音請隊盆膏閹泥宅編端SQL培訓SQL培訓一些不常用的SQL用法With語句駱怖紫墑呵誰網沫蔗預盯概框35一些不常用的SQL用法使用“數組”代替游標Record+table+bulkcollectMyccc.firstmyccc.countmy.last銻曬咸篩英洲夢判燴襄壺響抒蓬襪啞建氨箍般蛹掖灘鄭凳牙由馮語顴灶牡SQL培訓SQL培訓一些不常用的SQL用法使用“數組”代替游標銻曬咸篩英洲夢判燴36PL/SQLDeveloper使用OracleInstantClient設置OCI編寫批處理文件，設置字符集不使用TNSNAMES.ora登錄數據庫氛記鋁狐拴腰滄懲脫私研嚙擁黃貼惜限卑以齊夫孰塹途彤記蕩苦逼母豺虹SQL培訓SQL培訓PL/SQLDeveloper使用OracleInsta37OracleSQL簡單培訓2011-07-20臃晨轄做拳咐戈涕刀輛槐捍瑪醇輯完似花杏毆翟您宿擱猾便糙菱煮搖封賞SQL培訓SQL培訓OracleSQL簡單培訓2011-07-20臃晨轄做拳咐38內容BasicforSelect表關聯組與統計函數以及集合操作索引與視圖SQL書寫需要注意的問題-提高性能一些有用的SQLPL/SQLDeveloper簡單設置和使用Oracle10gonlinebook2:8080/B19306_01

可熟密召貪跺示澎坡按搐抬仲殆搜呈預聘扒晦渡棒零鞭漸令聳韭套陽摹圓SQL培訓SQL培訓內容BasicforSelect可熟密召貪跺示澎坡按搐抬39Select行與列按照條件（where）獲取特定列的集合SelectionTable1沖雄位攜擦編憋珊鋪擒哺翁舜湃橋彈念袖憂纓辜瘟酷津終濺闌達致衷湍凄SQL培訓SQL培訓Select行與列SelectionTable1沖雄位攜擦40Select運算運算符號“+”

“-”

“*”

“/”

“||”函數Abs()round()trim()lpad()length()nvl()別名（一般用在子查詢中）Col1asfirst_columnCol1“first_column”去除結果集中的重復Distinctcolumn_aDistinctcolumn_a,column_b類型轉換To_charto_dateto_numberDate的格式，yyyy-mm-ddhh24:mi:ssCaseCasewhencol1>1thencol2whencol2>2then‘A’elsecol1+col2end注意與PL/SQL中的case–endcase的區別隔洞賓刊埔筷們昌余攏波頭帖脫鋁簡臺敵努絢侗敏涌秸畸失栽拳恩莽厄說SQL培訓SQL培訓Select運算隔洞賓刊埔筷們昌余攏波頭帖脫鋁簡臺敵努絢侗敏41一些函數用法【lpad】lpad,rpadlpad函數將左邊的字符串填充一些特定的字符，rpad與之順序相反lpad(string,n,[pad_string])string：可是字符或者參數n：字符的長度，是返回的字符串的數量，如果這個數量比原字符串的長度要短，lpad函數將會把字符串截取成從左到右的n個字符;pad_string：是個可選參數，這個字符串是要粘貼到string的左邊，如果這個參數未寫，lpad函數將會在string的左邊粘貼空格。舉例lpad('tech',7)將返回'︻︻︻tech'lpad('tech',2)將返回'te'lpad('tech',8,'0')將返回'0000tech'用途用在填充字段長度。一般通過sequence生成unique值，然后通過lpad填充到指定的長度，用這個值作為主鍵。敢睬恢媽焉殷丘九炔瓜矢直漫舉乳醫沒寺葫誨閩雇隨漚偏慢沙批啟只格鄲SQL培訓SQL培訓一些函數用法【lpad】lpad,rpad敢睬恢媽焉殷丘九炔42一些函數用法【nvl】NVL，NVL2，NULLIF格式NVL(expr1,expr2)當expr1為null時，返回expr2NVL2(expr1,expr2,expr3)當expr1為notnull時，返回expr2，否則expr3NULLIF(expr1,expr2)當expr1=expr2，返回null用途NVL多用在讓查詢結果不要返回null的情況圃拷二脆孕卒跋娩樓斃剪始玩夏座妮媳援礁底祖抄逢炸婪詢衍襪營迫告瑣SQL培訓SQL培訓一些函數用法【nvl】NVL，NVL2，NULLIF圃拷二脆43Select條件Between..And…（包含上下界）In(enum1,enum2)existsLike“King%” （注意效率的影響）Isnull和isnotnullTopN（rownum）桌纂強敬運撓醒貸箱晝堵挾練焊隋抗嘲碉汐貓役好吹頤漬晌矽蛙棚懶超陌SQL培訓SQL培訓Select條件桌纂強敬運撓醒貸箱晝堵挾練焊隋抗嘲碉汐貓役好44表關聯N張表，有N-1個條件結果集中的列名可以不寫表名，但是此列名不能出現在多張表中缺少條件的后果很嚴重（笛卡爾集）內連接（=）、外連接(+)、自連接出于效率的考慮，最好小于4張表掣賃亢奪隔釉乘按礦憨呆漠裂畸坍版穿惑未揍六候渤農諧殖胰膊徒蛤執專SQL培訓SQL培訓表關聯N張表，有N-1個條件掣賃亢奪隔釉乘按礦憨呆漠裂畸坍版45分組與集合函數組按照同一個屬性，排列起來的數據集合Groupbycol1,col2集合函數Avgmaxminsumcount使用時注意null帶來的干擾組過濾Having贏痢懂簾右拾紹扯殖敵俄余豹堤賺蔚直遵我鍺塢祭熙偏庚娛餐淵鴻抓褒風SQL培訓SQL培訓分組與集合函數組贏痢懂簾右拾紹扯殖敵俄余豹堤賺蔚直遵我鍺塢祭46統計函數統計函數與集合函數類似，一般多用于需要統計列出現的情況，例如在結果集中需要出現占比和環比的情況。格式sum(credit)over(partitionbyACCOUNTTYPE)常用的幾個Sum 分組求和avg 求平均數max 求最大值 min求最小值RATIO_TO_REPORT計算百分比Wm_concat 行轉列Rollup(),cube(),grouping()纖桿時檬禾文腦原疲哈過滴乎升鵬書日平霜隴氖餒桃卿瘍命統碾間瘋畔閱SQL培訓SQL培訓統計函數統計函數與集合函數類似，一般多用于需要統計列出現的情47集合操作集合概念數據的集合。查詢結果、條件組(col1,col2)=(selectcol1,col2fromtbl)也可以使用in合并結果集Union，unionallUnionall效率相對高一些，因為不用去除重復記錄結果集相減Minus結果集相同內容匯總INTERSECT淆囂棕了拼渺迷捂檻垛庫逼辮作遭蘑怒墟蛙痰昆烙蹄艱質鄰醬轉幟傭忍朋SQL培訓SQL培訓集合操作集合概念淆囂棕了拼渺迷捂檻垛庫逼辮作遭蘑怒墟蛙痰昆烙48子查詢簡單理解就是放在查詢內部的查詢，就叫做子查詢。通長內部查詢的結果作為外部的查詢的條件。子查詢一般放在select,from,where,having子句的后面，放在select子句后面成為相關子查詢，其他的都屬于非相關子查詢。SELECT select_listFROM tableWHERE exproperator(SELECTselect_list FROM table);SELECT (selectcolfromtable_inwheretable_in.col=table.col)FROM tableWHERE exproperator;掏郝停鄒輻煥幌肆路疤呼世點廈拖曲囊訊聾兵絡狼轍刑嗽夏簍褥續福觀帚SQL培訓SQL培訓子查詢簡單理解就是放在查詢內部的查詢，就叫做子查詢。通長內部49子查詢相關子查詢和非相關子查詢的區別：執行順序不一樣。非相關子查詢：先查詢內部的查詢，然后將內部的查詢返回給外部查詢作為外部查詢的條件。內部查詢只執行一次，效率高。相關子查詢：先查詢外部查詢，在內部查詢。外部查詢一次，內部就查詢一次，返回一次數據。效率慢。是否能夠單獨執行。相關子查詢不能夠單獨運行的，它是依賴于外部查詢的。非相關子查詢能夠單獨運行。不依賴于外部查詢。效率比較非相關效率高，執行速度快。相關效率低，執行速度慢。嬌諺侍韻輔把霉牢涌瞬痰蒙裕姥汀孫香弛笑系始賭勃皆膩調臻撰亞衍蹦痛SQL培訓SQL培訓子查詢相關子查詢和非相關子查詢的區別：嬌諺侍韻輔把霉牢涌瞬痰50子查詢按照結果集數量，可以分為單行子查詢可以使用“=、>、<”，配合max、min、any、allAny可以理解為min，all理解為max多行子查詢使用in通過子查詢可以寫出很長的SQL勘涕馮曙磐派爆卷梭注詣裕鐵锨腐逗披罪空懼步克怪熾保瞻垮杰燴捕骯讓SQL培訓SQL培訓子查詢按照結果集數量，可以分為勘涕馮曙磐派爆卷梭注詣裕鐵锨腐51視圖EMPLOYEESTable:脊瑩低廉暮種舅席瑩靜浮禹攘柔漠天摔餌塔浦慶詩侮侯吁靳由都咖碼益擅SQL培訓SQL培訓視圖EMPLOYEESTable:脊瑩低廉暮種舅席瑩靜浮禹52視圖視圖的作用為了限制數據訪問為了使復雜的查詢變得更容易書寫和閱讀為了提供數據獨立性防止同一類數據，出現不同的篩選方法漂涌活賣碾喬介憋酬腺祥愿玉勤拈筍督炮贈濃撂聯焰眾避臺證齊痕潦柜炬SQL培訓SQL培訓視圖視圖的作用漂涌活賣碾喬介憋酬腺祥愿玉勤拈筍督炮贈濃撂聯焰53視圖使用上，可以把視圖看做是一張表兩種視圖一般視圖不存儲數據簡單視圖（單表、無函數和組定義）一般可以update和delete，而復雜視圖通常是不可以的物化視圖存儲數據簡單視圖可以更新沒有groupby蟲攻干窄轟獨屆喇獄囚糙迎頑競貓閻粹藹閩控套遜曳綠附藕煌飾勛繼廁殺SQL培訓SQL培訓視圖使用上，可以把視圖看做是一張表蟲攻干窄轟獨屆喇獄囚糙迎頑54索引描述Indexesareoptionalstructuresassociatedwithtablesandclusters.YoucancreateindexesononeormorecolumnsofatabletospeedSQLstatementexecutiononthattable.ItisusedandmaintainedautomaticallybytheOracleserver作用大幅度提升查詢速度減少資源爭用注意索引不是萬能的，沒有他是萬萬不能的淤館暇數掐肺線何貨渤胖卉耙耗區磨蟄竟絆術手嘶徊瞬楊侗鞘山假珊埠枯SQL培訓SQL培訓索引描述淤館暇數掐肺線何貨渤胖卉耙耗區磨蟄竟絆術手嘶徊瞬楊侗55索引建立自動。建立表主鍵時，自動建議一個索引。手工。CreateIndex什么情況下建立索引大表索引列包含很多不同的值（uniquevalue）預期的查詢結果（行記錄數量），占比在5%以下對于索引列的更新不是很頻繁在查詢條件中，索引列不是表達式的一部分廬飄弧夢拱披印斤穴砰瘟孿抓制放涯伐挾稍語解丈臟兩弘籠哇蠅甩蹈波火SQL培訓SQL培訓索引建立廬飄弧夢拱披印斤穴砰瘟孿抓制放涯伐挾稍語解丈臟兩弘籠56索引Oracle索引類型B-tree默認索引類型唯一索引、組合索引Bitmap有排他性針對數值稀少，不常改動的數據，多用于數據倉庫Partitioned在分區表上建立的索引，有全局索引和分區索引的區分Function-basedDomain槳項反富封困潰強絨燈振燒奪林儡蝦咯蟲淤觸前膝奉踢罵善掙醞哦市誣藝SQL培訓SQL培訓索引Oracle索引類型槳項反富封困潰強絨燈振燒奪林儡蝦咯蟲57表分區當表中的數據量不斷增大，查詢數據的速度就會變慢，應用程序的性能就會下降，這時就應該考慮對表進行分區。表進行分區后，邏輯上表仍然是一張完整的表，只是將表中的數據在物理上存放到多個表空間(物理文件上)，這樣查詢數據時，不至于每次都掃描整張表。轅示振立弄虛藏糾株匙芭較粉皋召基歡丹鍍哇口繹抄礫依琵偶乓邪稻砷躇SQL培訓SQL培訓表分區當表中的數據量不斷增大，查詢數據的速度就會變慢，應用程58表分區分區類型范圍分區Range列表分區List散列分區Hash組合分區畜嘛量分蔽菲判捆腹腺步愧粟庚荷鬃碰按首迷耳咋鈔準通磁姿倚綠碉床假SQL培訓SQL培訓表分區分區類型畜嘛量分蔽菲判捆腹腺步愧粟庚荷鬃碰按首迷耳咋鈔59表分區分區索引類型本地索引全局分區索引全局索引OLTP鬧役玫扣畏蓮玖雜撒鉤截去斃統吝嚨柿埔耗延宋辱辯冬期賦肢禍枕驅么藩SQL培訓SQL培訓表分區分區索引類型鬧役玫扣畏蓮玖雜撒鉤截去斃統吝嚨柿埔耗延宋60表分區使用場合

OLTPsystemsoftenbenefitfromimprovementsinmanageabilityandavailability,whiledatawarehousingsystemsbenefitfromperformanceandmanageability.youshoulduseglobalindexesforOLTPapplicationsandlocalindexesfordatawarehousingorDSSapplications帶來的好處大塊數據的遷移、刪除降低表掃描的成本綜合來說，數據管理成本，提高數據的可用性賢壟嚇焊熾釀燭盎聚深賂壩草撬橡餐謊終畫及禁錘鞘炸霞閏潑捎瞞織許露SQL培訓SQL培訓表分區使用場合賢壟嚇焊熾釀燭盎聚深賂壩草撬橡餐謊終畫及禁錘鞘61什么是運行DML時，Oracle需要很多步驟來完成。這些步驟的組合，叫做執行計劃執行的作用通過執行計劃，可以看到DML的執行方式，從而了解你的SQL是不是最優化的怎么看執行計劃PL/SQLDeveloper不要出現“TABLEACCESSFULL”在進行表關聯時，記錄數量少的表，可以有“TABLEACCESSFULL”查詢計劃隨著數據量的變化，會變化執行計劃（Plan）v$sql記錄已經parsing過得SQL勝撲猶狀煌夢甸折滔氏共憎疑趁釩居捏欽游防計譬影淚拙輛耳疤弟翹聚爆SQL培訓SQL培訓什么是執行計劃（Plan）v$sql勝撲猶狀煌夢甸折滔氏共憎62Hint（雙面劍）用途用來強制查詢優化器按照指定的方式運行用法舉例select/*+NO_INDEX(big_table)*/col1frombig_tablewherecol2=1000常用種類Index(tableindex)leading(tbl1tbl2)使用場景與限制通常情況下，Oracle不建議使用hint，讓優化器自己決定Oracle10g的查詢優化器已經比較完善，實際使用中，在簡單SQL情況下，會有最優的結果多用在多表關聯時，而每張表上的索引較多2:8080/B19306_01/server.102/b14200/sql_elements006.htm

殆指紡偷伎奉阮般扒筐胎恐噎糧渝粥臼助釩餒嵌邦尾呀若龍鉤困破莖障階SQL培訓SQL培訓Hint（雙面劍）用途殆指紡偷伎奉阮般扒筐胎恐噎糧渝粥臼助釩63綁定變量變量綁定的作用，可以使服務器不需要再對SQL語句進行硬解析hardparsing（一個非常耗費資源的操作），而只進行軟解析softparsing（重用之前硬解析的結果），這樣可以提高服務器的工作效率可以通過查詢v$sqlstats來看到那些SQL已經被編譯，在內存中如何使用變量綁定SELECTfname,lname,pcodeFROMcustWHEREid=cust_no;Java中使用prepare，變化變量的值舉例狙鹵咬鳥躁合閉銹畫傘虎憐濱儈咸濕隕梅踢劣摳蔡征薄青霧揖祭晨碟抒彩SQL培訓SQL培訓綁定變量變量綁定的作用，可以使服務器不需要再對SQL語句進行64綁定變量Java舉例Stringv_id='xxxxx';Stringv_sql='selectnamefromtable_awhereid=?';//嵌入綁定變量stmt=con.prepareStatement(v_sql);stmt.setString(1,v_id);//為綁定變量賦值stmt.executeQuery();PL/SQL舉例（SP編寫）Insertintotbl(col1,col2,col3)values(ln_pk,ln_col2_value,null)Insertintotbl(col1,col2,col3)values(ln_pk,null,ln_col3_value)以上兩句需要進行改寫：ln_col2_value:=null;或者ln_col3_value:=null;Insertintotbl(col1,col2,col3)values(ln_pk,ln_col2_value,ln_col3_value)瘡勤持棕拴舌蛀斧移斧恬棺誕杜南亦炮誡餃裕誰磋僳粳矗郴您禿浴鳴蕾闖SQL培訓SQL培訓綁定變量Java舉例瘡勤持棕拴舌蛀斧移斧恬棺誕杜南亦炮誡餃裕65SQL書寫最好不要用的幾種情況Trim(col1)=‘AA’函數列，不能使用索引<>，notin，notexists排除，不能使用索引Truncatetable與delete性能上將，前者不記錄日志，后者記錄日志存儲上講，前者可以降低watermark，后者不行Count(*)->count(PK)Sp中判斷是否有記錄Selectcount(1)intoexp1fromdualwhereexists(select1fromtbl1wherecol1=value)國德熱鮮擄樂屯有韋何鐳蹲瑩銥正醛兵沛芒擬館欣瘧潘袋賭桔憲險糜鋪夢SQL培訓SQL培訓SQL書寫最好不要用的幾種情況國德熱鮮擄樂屯有韋何鐳蹲瑩銥正66SQL書寫In和Exists的使用場景前提是，SQL的查詢條件都可以使用索引外表大，內表小，使用in；外表小，內表大，使用exists；都很大時，可以用in。SP中，避免使用cursor，可以采用數組替換，提高效率。交換使用SELECTDISTINCT(SQRT(department_id))col_aliasFROMemployeesSELECTSQRT(department_id)col_aliasFROM(SELECTDISTINCTdepartment_idFROMemployees)Integer替換為PLS_INTEGER瓶澎寨扮拋歸巷便湃更蟹吻釣主披店悼系懾檸秩宴竣樣拈篆閡示騙剩藩鋼SQL培訓SQL培訓SQL書寫In和Exists的使用場景瓶澎寨扮拋歸巷便67SQL書寫程序中，where條件兩邊的數據類型要一致，避免出現數據轉換A:=10.0;In和exists的替換寫法selectdeptnofromdeptwheredeptnonotin(selectdeptnofromemp)建議寫成：selectdeptnofromdept,empwheredept.deptno=emp.deptno(+)andemp.dept