2022年SQL培訓

1、Oracle SQL簡單培訓2011-07-20內(nèi)容Basic for Select表關(guān)聯(lián)組與統(tǒng)計函數(shù)以及集合操作索引與視圖SQL書寫需要注意的問題-提高性能一些有用的SQLPL/SQL Developer簡單設置和使用Oracle 10g online book2:8080/B19306_01 Select行與列按照條件（where）獲取特定列的集合SelectionTable 1Select運算運算符號“+” “-” “*” “/” “|”函數(shù)Abs() round() trim() lpad() length() nvl()別名（一般用在子查詢中）Col1 as first_column

2、Col1 “first_column”去除結(jié)果集中的重復Distinct column_aDistinct column_a, column_b類型轉(zhuǎn)換To_char to_date to_numberDate的格式，yyyy-mm-dd hh24:mi:ssCaseCase when col11 then col2 when col22 then A else col1+col2 end注意與PL/SQL中的case end case 的區(qū)別一些函數(shù)用法【lpad】lpad,rpadlpad函數(shù)將左邊的字符串填充一些特定的字符，rpad與之順序相反lpad(string,n,pad_stri

3、ng)string：可是字符或者參數(shù)n：字符的長度，是返回的字符串的數(shù)量，如果這個數(shù)量比原字符串的長度要短，lpad函數(shù)將會把字符串截取成從左到右的n個字符;pad_string：是個可選參數(shù)，這個字符串是要粘貼到string的左邊，如果這個參數(shù)未寫，lpad函數(shù)將會在string的左邊粘貼空格。舉例lpad(tech, 7) 將返回tech lpad(tech, 2) 將返回te lpad(tech, 8, 0) 將返回0000tech 用途用在填充字段長度。一般通過sequence生成unique值，然后通過lpad填充到指定的長度，用這個值作為主鍵。一些函數(shù)用法【nvl】NVL，NVL2

4、，NULLIF格式NVL(expr1, expr2)當expr1為null時，返回expr2NVL2(expr1, expr2, expr3)當expr1為not null時，返回expr2，否則expr3NULLIF(expr1, expr2)當expr1=expr2，返回null用途NVL多用在讓查詢結(jié)果不要返回null的情況Select條件Between . And （包含上下界）In (enum1, enum2)existsLike “King%” （注意效率的影響）Is null 和 is not nullTop N（rownum）表關(guān)聯(lián)N張表，有N-1個條件結(jié)果集中的列名可以不寫表

5、名，但是此列名不能出現(xiàn)在多張表中缺少條件的后果很嚴重（笛卡爾集）內(nèi)連接（=）、外連接(+)、自連接出于效率的考慮，最好小于4張表分組與集合函數(shù)組按照同一個屬性，排列起來的數(shù)據(jù)集合Group by col1,col2集合函數(shù)Avg max min sum count 使用時注意null帶來的干擾組過濾Having 統(tǒng)計函數(shù)統(tǒng)計函數(shù)與集合函數(shù)類似，一般多用于需要統(tǒng)計列出現(xiàn)的情況，例如在結(jié)果集中需要出現(xiàn)占比和環(huán)比的情況。格式sum(credit) over (partition by ACCOUNTTYPE)常用的幾個Sum 分組求和avg 求平均數(shù)max 求最大值 min 求最小值RATIO_TO

6、_REPORT 計算百分比Wm_concat行轉(zhuǎn)列Rollup() ,cube(), grouping()集合操作集合概念數(shù)據(jù)的集合。查詢結(jié)果、條件組(col1,col2) = (select col1,col2 from tbl)也可以使用in合并結(jié)果集Union，union allUnion all 效率相對高一些，因為不用去除重復記錄結(jié)果集相減Minus結(jié)果集相同內(nèi)容匯總INTERSECT子查詢簡單理解就是放在查詢內(nèi)部的查詢，就叫做子查詢。通長內(nèi)部查詢的結(jié)果作為外部的查詢的條件。子查詢一般放在 select , from , where , having子句的后面，放在select子句后

7、面成為相關(guān)子查詢，其他的都屬于非相關(guān)子查詢。 SELECTselect_listFROMtableWHEREexpr operator (SELECT select_list FROM table);SELECT(select col from table_in where table_in.col = table.col)FROMtableWHEREexpr operator;子查詢相關(guān)子查詢和非相關(guān)子查詢的區(qū)別：執(zhí)行順序不一樣。非相關(guān)子查詢：先查詢內(nèi)部的查詢，然后將內(nèi)部的查詢返回給外部查詢作為外部查詢的條件。內(nèi)部查詢只執(zhí)行一次，效率高。相關(guān)子查詢：先查詢外部查詢，在內(nèi)部查詢。外部查詢一次，

8、內(nèi)部就查詢一次，返回一次數(shù)據(jù)。效率慢。是否能夠單獨執(zhí)行。相關(guān)子查詢不能夠單獨運行的，它是依賴于外部查詢的。非相關(guān)子查詢能夠單獨運行。不依賴于外部查詢。效率比較非相關(guān)效率高，執(zhí)行速度快。相關(guān)效率低，執(zhí)行速度慢。子查詢按照結(jié)果集數(shù)量，可以分為單行子查詢可以使用“=、”，配合max、min、any、allAny可以理解為min，all理解為max多行子查詢使用in通過子查詢可以寫出很長的SQL視圖EMPLOYEES Table:視圖視圖的作用為了限制數(shù)據(jù)訪問為了使復雜的查詢變得更容易書寫和閱讀為了提供數(shù)據(jù)獨立性防止同一類數(shù)據(jù)，出現(xiàn)不同的篩選方法視圖使用上，可以把視圖看做是一張表兩種視圖一般視圖不存儲

9、數(shù)據(jù)簡單視圖（單表、無函數(shù)和組定義）一般可以update和delete，而復雜視圖通常是不可以的物化視圖存儲數(shù)據(jù)簡單視圖可以更新沒有g(shù)roup by索引描述Indexes are optional structures associated with tables and clusters. You can create indexes on one or more columns of a table to speed SQL statement execution on that table.It is used and maintained automatically by the Or

10、acle server作用大幅度提升查詢速度減少資源爭用注意索引不是萬能的，沒有他是萬萬不能的索引建立自動。建立表主鍵時，自動建議一個索引。手工。Create Index 什么情況下建立索引大表索引列包含很多不同的值（unique value）預期的查詢結(jié)果（行記錄數(shù)量），占比在5%以下對于索引列的更新不是很頻繁在查詢條件中，索引列不是表達式的一部分索引Oracle索引類型B-tree默認索引類型唯一索引、組合索引Bitmap有排他性針對數(shù)值稀少，不常改動的數(shù)據(jù)，多用于數(shù)據(jù)倉庫Partitioned 在分區(qū)表上建立的索引，有全局索引和分區(qū)索引的區(qū)分Function-basedDomain 表分

11、區(qū)當表中的數(shù)據(jù)量不斷增大，查詢數(shù)據(jù)的速度就會變慢，應用程序的性能就會下降，這時就應該考慮對表進行分區(qū)。表進行分區(qū)后，邏輯上表仍然是一張完整的表，只是將表中的數(shù)據(jù)在物理上存放到多個表空間(物理文件上)，這樣查詢數(shù)據(jù)時，不至于每次都掃描整張表。表分區(qū)分區(qū)類型范圍分區(qū) Range列表分區(qū) List散列分區(qū) Hash組合分區(qū)表分區(qū)分區(qū)索引類型本地索引全局分區(qū)索引全局索引OLTP表分區(qū)使用場合OLTP systems often benefit from improvements in manageability and availability, while data warehousing syst

12、ems benefit from performance and manageability. you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications 帶來的好處大塊數(shù)據(jù)的遷移、刪除降低表掃描的成本綜合來說，數(shù)據(jù)管理成本，提高數(shù)據(jù)的可用性什么是運行DML時，Oracle需要很多步驟來完成。這些步驟的組合，叫做執(zhí)行計劃執(zhí)行的作用通過執(zhí)行計劃，可以看到DML的執(zhí)行方式，從而了解你的SQL是不是最優(yōu)化的怎么看執(zhí)行計劃PL/SQL Dev

13、eloper不要出現(xiàn)“TABLE ACCESS FULL”在進行表關(guān)聯(lián)時，記錄數(shù)量少的表，可以有“TABLE ACCESS FULL”查詢計劃隨著數(shù)據(jù)量的變化，會變化執(zhí)行計劃（Plan）v$sql記錄已經(jīng)parsing過得SQLHint（雙面劍）用途用來強制查詢優(yōu)化器按照指定的方式運行用法舉例select /*+ NO_INDEX(big_table) */ col1 from big_table where col2 = 1000常用種類Index(table index)leading(tbl1 tbl2)使用場景與限制通常情況下，Oracle不建議使用hint，讓優(yōu)化器自己決定Oracl

14、e10g的查詢優(yōu)化器已經(jīng)比較完善，實際使用中，在簡單SQL情況下，會有最優(yōu)的結(jié)果多用在多表關(guān)聯(lián)時，而每張表上的索引較多2:8080/B19306_01/server.102/b14200/sql_elements006.htm 綁定變量變量綁定的作用，可以使服務器不需要再對SQL語句進行硬解析hard parsing（一個非常耗費資源的操作），而只進行軟解析soft parsing（重用之前硬解析的結(jié)果），這樣可以提高服務器的工作效率可以通過查詢v$sqlstats來看到那些SQL已經(jīng)被編譯，在內(nèi)存中如何使用變量綁定SELECT fname, lname, pcode FROM cust WH

15、ERE id = cust_no; Java中使用prepare，變化變量的值舉例綁定變量Java舉例String v_id = xxxxx;String v_sql = select name from table_a where id = ? ; /嵌入綁定變量stmt = con.prepareStatement( v_sql );stmt.setString(1, v_id ); /為綁定變量賦值stmt.executeQuery();PL/SQL舉例（SP編寫）Insert into tbl (col1,col2,col3) values (ln_pk,ln_col2_value,n

16、ull)Insert into tbl (col1,col2,col3) values (ln_pk,null,ln_col3_value)以上兩句需要進行改寫：ln_col2_value := null; 或者 ln_col3_value := null; Insert into tbl (col1,col2,col3) values (ln_pk, ln_col2_value,ln_col3_value)SQL書寫最好不要用的幾種情況Trim(col1)=AA函數(shù)列，不能使用索引，not in，not exists排除，不能使用索引Truncate table與 delete性能上將，前者

17、不記錄日志，后者記錄日志存儲上講，前者可以降低water mark，后者不行Count(*) - count(PK)Sp中判斷是否有記錄Select count(1) into exp1 from dual where exists(select 1 from tbl1 where col1=value)SQL書寫In 和 Exists 的使用場景前提是，SQL的查詢條件都可以使用索引外表大，內(nèi)表小，使用in；外表小，內(nèi)表大，使用exists；都很大時，可以用in。SP中，避免使用cursor，可以采用數(shù)組替換，提高效率。交換使用SELECT DISTINCT(SQRT(department_

18、id) col_alias FROM employees SELECT SQRT(department_id) col_alias FROM ( SELECT DISTINCT department_id FROM employees) Integer替換為PLS_INTEGERSQL書寫程序中，where條件兩邊的數(shù)據(jù)類型要一致，避免出現(xiàn)數(shù)據(jù)轉(zhuǎn)換A:=10.0;In 和 exists的替換寫法select deptno from deptwhere deptno not in(select deptno from emp)建議寫成：select deptno from dept, empwh

19、ere dept.deptno = emp.deptno(+)and emp.deptno is null一些不常用的SQL用法多表插入INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL)SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id 200;應用于事物要求性比較高的情況。例如，通過一個SQL實現(xiàn)多表

20、插入，如果成功就都插入，失敗則都回滾。日常，實現(xiàn)這樣的情況，需要啟動事物，這里可以通過一個SQL實現(xiàn)，不需要啟動事物一些不常用的SQL用法帶返回值的UpdateUPDATE cust_data SET cust_no = aa,WHERE cust_add = tp1RETURNING nme,tel INTO V_nme_J,V_tel 常用于事物密集型操作例如，修改賬戶余額。通常的做法是，首先update，然后select，但是這中間會存在一個時間差，導致select結(jié)果不正確。這時可以按照此法使用，獲得準確的返回值一些不常用的SQL用法遞歸Select select * from emp start with ename=KING connect by prior empno=mgr產(chǎn)生樹形結(jié)構(gòu)DirectionTop down Column1 = Parent Ke