精選優質文檔-----傾情為你奉上精選優質文檔-----傾情為你奉上專心---專注---專業專心---專注---專業精選優質文檔-----傾情為你奉上專心---專注---專業Oracle學習筆記Java相關課程系列筆記之二筆記內容說明Oracle數據庫、SQL（薛海璐老師主講，占筆記內容100%）；目錄TOC\o"1-4"\h\u數據庫介紹1.1表是數據庫中存儲數據的基本單位1.2數據庫標準語言結構化查詢語言SQL：StructureedQueryLanguage1）數據定義語言DDL：DataDefinitionLanguage createtable列表結構、altertable修改列、droptable刪除列2）數據操作語言DML：DataManipulationLanguage insert增加一行，某些列插入值、update修改一行，這一行的某些列、delete刪除一行，跟列無關3）事務控制語言TCL：TransactionConrtolLanguage commit確認，提交（入庫）、rollback取消，回滾，撤銷4）數據查詢語言DQL：DataQueryLanguage select語句5）數據控制語言DCL：DataControlLanguage 系統為多用戶系統因此有隱私權限問題：grant授權、revoke回收權限1.3數據庫（DB）DATABASE關系數據庫使用關系或二維表存儲信息。關系型數據庫管理系統（EDBMS）：RelationshipDatabaseManagementSystem是一套軟件，用于在數據庫中存儲數據、維護數據、查詢數據等。1.4數據庫種類Oracle10g（Oracle）、DB2（IBM）、SQLSERVER（MS）1.5數據庫中如何定義表先畫列即表頭（列名，數據類型及長度，約束）；數據類型有字符、數值number、日期date。1.6createdatabasedbname的含義創建數據庫即創建可用空間，創建出一堆數據文件datafile1.7安裝DBMS職位：DBA數據庫管理員（DataBaseAdministrator）1.8宏觀上是數據-->database開發流程：createtabaleDMLTCL->DQLselect1.9遠程登錄：telnetIP地址sqldeveloper在linux系統--->連接--->database在solaris系統1.10TCP/IP通信協議兩臺機器上的兩個應用程序要通信，必須依賴網絡，依賴TCP/IP通信協議。IP：IP協議包中提供要連接機器的IP地址，用于標識機器。TCP：TCP協議包中提供與機器上的哪個具體應用程序通信，通過端口號實現，oracle數據庫服務缺省端口為1521，用于標識Oracle此數據庫應用。1.11數據庫建連接必須提供以下信息ip地址（確認機器）、port號（確認進程（程序）確認Oracle）SID：一個端口可以為多個oracle數據庫提供監聽，因此還需要提供具體的數據庫名。（確認數據庫里的哪個數據庫）username、password：要想訪問數據庫，必須是該數據庫上一個有效的用戶。（確認身份）1.12一臺機器可跑幾個數據庫，主要受內存大小影響1.13源表和結果集源表：被查詢的表 結果集：select語句的查詢結果1.14幾個簡單命令showuser：查看當前用戶 desc表名：查看表結構droptable表名purge;刪除表，Oracle中刪除表不是真正的刪除，而是占空間的移動到別的地方，因為為了不占空間，真正的刪除需要用purge。deletefrom表名：刪除表中所有值；若加上where列名=value則刪除某列中的值1.15tarena給jsd1304授權connecttarena/tarenagrantselectonaccounttojsd1304; grantselectonservicetojsd1304;grantselectoncosttojsd1304; jsd1304selecttarena的表connectjsd1304/jsd1304createsynonym創建同義詞 createsynonymaccountfortarena.account;createsynonymservicefortarena.service; createsynonymcostfortarena.cost;1.16課程中使用的5個表

selectfrom語句2.1select語句功能1）投影操作：結果集是源表中的部分“列”2）選擇操作：結果集是源表中的部分“行”3）選擇操作+投影操作：結果集是源表中的部分“行”部分“列”4）連接操作join：多表查詢，結果集來自多張表，把多張的記錄按一定條件組合起來2.2select語句基本語法1）selectcolname(列名) fromtabname（表名）2）select中指定多個列名，則用“逗號”分隔：selectcolname1,colname2fromtabname3）*號表示所有列：select* fromtabname4）select語句：可有多個子句5）select子句：投影操作（列名）、列表達式、函數、from子句等2.3列別名1）給列起一個別名，能夠改變一個列、表達式的標識。2）不寫的話默認都是轉成大寫。 3）適合計算字段。4）在原名和別名之間可以使用as關鍵字。5）別名中包含空格、特數字符或希望大小寫敏感的，用“”雙引號將其括起來。2.4算術表達式在number類型上使用算術表達式（加減乘除）。eg：一個月使用了250小時,每種資費標準下應繳納的費用(首次實現)selectbase_cost+(250-base_duration)*unit_costfeefromcost;2.5空值null的處理未知的，沒寫數1）空值不等于02）空值不等于空格3）在算術表達式中包含空值導致結果為空4）在算術表達式中包含空值需要用空值轉換函數nvl處理2.6nvl(p1,p2)函數空值轉換函數1）兩個參數類型要一致！2）參數的數據類型可以是數值number、字符character、日期date3）但null轉成字符串，null也要用to_char()轉化。4）實現過程：ifp1isnullthen returnp2elase returnp1endif5）實現空值轉換：null－>非null值0eg：一個月使用了250小時,每種資費標準下應繳納的費用(再次實現)selectnvl(base_cost,0)+(250-nvl(base_duration,0))*nvl(unit_cost,0)feefromcost;2.7拼接運算符||表達字符（串）的拼接，可以將某幾列或某列與字符串拼接在一起。selectcolname1||colname2fromtabname2.8文字字符串select語句后面可以包含的文字值：字符、表達式、數字。1）字符常量（或字符串）必須用‘’單引號括起來，作為“定界符”使用。2）表達單引號本身，需要兩個單引號''''1，4定界2，3表單引號。3）對于文字值每行輸出一次。eg：顯示客戶姓名的身份證號是……selectreal_name||'''sIDCARDNOis'||idcard_no||'.'cilentfromaccount;4）函數轉換大小寫，盡量在進入數據時操作。2.9消除重復行distinct去重復行（對整條記錄返回的結果去重，不是對后面的某個列去重），若后面有多列，則所有列聯合起來唯一，即每列的值都可以重復，但組合不能重復。eg1：哪些unix服務器提供遠程登錄業務selectdistinctunix_hostfromservice;eg2：每一臺unix服務器在哪些天開通了遠程登錄業務selectdistinctunix_host,create_datefromservice;2.10其他注意事項1）調常量時用單行單列的dual表，系統提供的表。2）invalididentifier無效標識名，列名不。3）tableorviewdoesnotexist表名不對。

SQL語句的處理過程3.1SQL語句處理過程用戶進程sqlplus→建立連接→服務進程ServerprocessoracleSID↑--創建會話--Oracleserver3.2處理一條select語句1）分析語句： ①搜索是否有相同語句 ②用hashvalue計算select語句是否長得一樣：大小寫，關鍵字，空格要都一樣，不一樣則為兩條語句，則服務進程會重新分析。若為統一語句，則直接從內存拿執行計劃，計算結果 ③檢查語法、表名、權限 ④在分析過程中給對象加鎖 ⑤生成執行計劃2）綁定變量：給變量賦值3）執行語句：4）獲取數據：將數據返回給用會進程

where子句用where子句對表里的記錄進行過濾，where子句跟在from子句后面。4.1where子句后面可以跟什么跟條件表達式：列名、常量、比較運算符（單、多值運算符）、文字值；不能跟組函數！不能跟列別名！注意事項：對列不經過運算的條件表達式效率會更高，建議在寫where子句時盡量不要對列進行運算。eg：一年的固定費用為70.8元，計算年包在線時長selectbase_duration*12ann_durationfromcostwherebase_cost*12=70.8;沒下面效率高selectbase_duration*12ann_durationfromcostwherebase_cost=70.8/12;4.2語法和執行順序語法順序：selectfromwhere 執行順序：fromwhereselect4.3字符串是大小寫敏感的，在比較時嚴格區分大小寫1）upper()：函數將字符串轉換成大寫。2）lower()：函數將字符串轉換成小寫。3）initcap()：函數將字符串轉換成首字符大寫（是將列中的值大小寫轉換然后去和等號后的字符串比，而不是把轉字符串轉換去和列比）。eg：哪些unix服務器上開通了os帳號huangrselectunix_host,os_usernamefromservicewhereos_username='huangr';(有結果) wherelower(os_username)='HUANGR';(無結果)wherelower(os_username)='huangr';(有結果)whereupper(os_username)='HUANGR';(有結果)4.4where子句后面可以跟多個條件表達式條件表達式之間用and、or連接，也可用（）改變順序。4.5betweenand運算符表示一個范圍，是閉區間，含義為大于等于并且小于等于。eg：哪些資費的月固定費用在5元到10元之間selectbase_duration,base_cost,unit_costfromcostwherebase_cost>=5andbase_cost<=10; wherebase_costbetween5and10;4.6in運算符（多值運算符）表示一個集合，是離散值，含義為等于其中任意一個值，等價于any。eg：哪些資費的月固定費用是5.9元,8.5元,10.5元selectbase_duration,base_cost,unit_costfromcostwherebase_cost=5.9orbase_cost=8.5orbase_cost=10.5;wherebase_costin(5.9,8.5,10.5); wherebase_cost=any(5.9,8.5,10.5);4.7like運算符在字符串比較中，可用like和通配符進行模糊查找。1）通配符：%表示0或多個字符；_表示任意“一個”字符（要占位的）。注意事項：若要查找%和_本身，則需要escape進行轉移。eg：哪些unix服務器上的os帳號名是以h開頭的selectos_usernamefromservicewhereos_usernamelike'h%'eg：哪些unix服務器上的os帳號名是以h_開頭的selectos_usernamefromservicewhereos_usernamelike'h\_%'escape'\';4.8isnull運算符測試null值需要用isnull。null不能用等于號“＝”和不等于號“<>”跟任何值比較，包括它自身。所以不能用“＝”和“<>”來測試是否有空值。即：null=null是不成立的；null不等于null也不成立；null和任何值比較都不成立。eg：列出月固定費用是5.9元，8.5元，10.5元或者沒有月固定費。selectbase_duration,base_cost,unit_costfromcostwherebase_costin(5.9,8.5,10.5,null);(錯誤)wherebase_costin(5.9,8.5,10.5)orbase_costisnull;(正確)4.9比較和邏輯運算符（單值運算符）1）比較運算符：= > >= < <=2）SQL比較運算符：betweenand、in、like、isnull3）邏輯運算符：and、or、not4.10多值運算符all、any1）>all：大于所有的，等價于>(selectmax()…)。2）>any：大于任意的，等價于>(selectmin()…)。4.11運算符的否定形式1）比較運算符：<> ！=^=2）SQL比較運算符：notbetweenand notin notlike isnotnull注意事項：in相當于=or=or=or等價于anynotin等價于<>and<>and<>and等價于<>allnotbetweenand小于下界or大于上界集合中有null，對in無影響；但對notin有影響，有一個就沒有返回值！eg：哪些資費信息的月固定費用不是5.9元,8.5元,10.5元selectbase_duration,base_cost,unit_costfromcostwherenvl(base_cost,0)<>5.9andnvl(base_cost,0)<>8.5andnvl(base_cost,0)<>10.5;wherenvl(base_cost,0)notin(5.9,8.5,10.5);

orderby子句select語句輸出的結果安記錄在表中的存儲順序顯示，orderby子句能夠改變記錄的輸出順序。orderby子句對查詢出來的結果集進行排序，即對select子句的計算結果排序。5.1語法和執行順序語法順序：selectfromwhereorderby 執行順序：fromwhereselectorderby5.2升降序ASC－升序，可以省略，默認值 DESC－降序orderbynvl(base_cost,0); orderbyunix_host,create_datedesc;注意事項：orderby是select語句中最后一個子句5.3null值在排序中顯示1）被排序的列如果包含null值，用ASC方式null值的在最后；2）用DESC方式null在最前面；5.4orderby后面可以跟什么可以跟列名、列別名、列位置（數字）、表達式、函數。orderby1：表示列位置為1的列select1from：表示常量1eg：按年固定費用從大到小的順序顯示資費信息方式一：selectid,base_cost*12ann_cost,base_durationann_durationfromcost orderbybase_costdesc;方式二：selectid,base_costann_cost,base_durationann_durationfromcostorderbybase_cost*12desc;排序的效果和上面是一樣的，但前一個效率高。5.5多列排序orderby子句后面可以跟多列，而orderby后面的列可以不出現在select后面。結果集先按第一列升序排列，若列值一樣，再按第二列降序排列。eg：按unix服務器ip地址升序，開通時間降序顯示業務帳號信息selectid,unix_host,os_username,create_datefromserviceorderbyunix_host,create_datedesc;

單行函數的使用SQL函數的兩種類型：單行函數、多行函數（組函數）。單行函數：數值類型、日期類型、字符類型、轉換函數。處理一列數據，返回一個結果。6.1數值類型1）定義：createtabletabname(c1number,c2number(6),c3number(4,3),c4number(3,-3),c5number(2,4));2）數值類型說明①number：不寫數值，表可寫38位數②number(6)：6位整數 .1 ③number(4,3)：數字4位，小數點占3位，四舍五入 1. 1.235④number(3,-3)：小數點前三位不寫數，四舍五入，然后有效位3位 12341000⑤number(2,4)：小數點后4位，有效位2位 0.00991 0.00993）數值函數：參數類型為number①round()：四舍五入函數，“缺省轉成數字”；也可對日期 ②trunc()：截取函數（不管多大值直接舍去）；也可對日期eg：round和truncround(45.923，2)：45.92 round(45.923，0)：46 round(45.923，-1)：50trunc(45.923，2)：45.92 trunc(45.923)：45 trunc(45.923，-1)：406.2日期類型1）Oracle用7個字節來存儲日期和時間：世紀、年、月、日、時、分、秒。Date不存在定寬度，就是7個字節。2）缺省（默認）日期格式為DD-MON-RR，格式敏感。3）sysdate是一個系統函數，返回當前系統時間和日期。4）改變session（會話）中的日期格式：session和connection是同時建立的，兩者是對同一件事情的不同層次的描述。connection是物理上的客戶機同服務器端的通信鏈路；session是邏輯上的用戶同服務器的通信交互，SQL語句的運行環境。eg：顯示的日期包含世紀、年、月、日、時、分、秒altersessionsetnls_date_format='yyyymmddhh24:mi:ss';日期格式yyyy用數字表達的四位年（2013年）mm用數字表達的兩位月（01月）dd用數字表達的兩月日（01日）hh24用數字表達的24進制的小時（20點）h12用數字表達的12進制的小時（8點）mi用數字表達的分鐘（30分）ss用數字表達的小時（30秒）D用數字表達的一周內的第幾天（周日：1）day用全拼表達的星期幾（sunday）month用全拼表達的月（march）mon用簡拼表達的月（mar）eg：案例selectto_char(sysdate,'DDD')fromdual;年中的第幾天selectto_char(sysdate,'DD')fromdual;月中的第幾天selectto_char(sysdate,'D')fromdual;星期中的第幾天6）在數據庫中如何處理日期類型 createtabletest(c1date);insertintotestvalues('01-JAN-08');insertintotestvalues('2008-08-08');（報錯）insertintotestvalues(to_date('2008-08-08','yyyy-mm-dd'));selectc1fromtest;selectto_char(c1,'yyyy-mm-dd')fromtest;在createtable中定義日期類型date時一定不能指定寬度。日期在數據庫中用固定的7個字節存儲，表示世紀、年、月、日、時、分、秒。缺省的日期格式為'DD-MON-RR'，'01-JAN-08'符合缺省日期格式可以插入表中，因為系統會自動調用to_date函數將它轉成日期。 '2008-08-08'插入時報錯，原因是不符合缺省格式，需要手工使用函數to_date對字符串的格式進行說明，如'2008-08-08'的格式說明串為'yyyy-mm-dd'。select時日期按缺省日期格式顯示，若用指定日期格式，需要使用to_char函數。7）日期與字符串相互轉換：to_date(char,date)函數：將字符串轉換成一個日期值。對應java中parse。to_char(date,char)函數：第一個參數為要處理的日期，第二個參數為格式；可獲取一個日期的任意一部分信息；對應java中format。eg：創建一張表，包含date類型的列，插入2008年8月8日8點8分8秒并顯示。insertintotestvalues(to_date('2008-08-0808:08:08','yyyy-mm-ddhh24:mi:ss'));selectto_char(c1,'yyyy-mm-ddhh24:mi:ss')fromtest;注意事項：格式必須用單引號括起來，并且大小寫敏感。必須是有效的日期格式。fm能去掉前導0和兩端的空格。對日期去重復問題，to_char獲取當天日期即可，時分秒忽略，加上distinct即可做到eg：案例whereto_char(create_date,'mm')='03';若等式右邊寫成‘3’，‘03’=‘3’不成立，需要在‘mm’前增加‘fm’。whereto_char(create_date,'fmmm')='3'whereto_number(to_char(create_date,'mm'))=3;若等式右邊寫成3，‘03’=3成立，‘03’是字符類型，3是數字類型，等式兩邊相等，說明系統做了隱式數據轉換，缺省做法將字符轉化為number。8）日期函數：參數類型為date①add_months()：一個日期加、減一個月。②months_between()：兩個日期之間相差多少個月。③last_day()：同一個月的最后一天 ④next_day()：根據參數，出現下一個的日期。eg1：昨天，今天，明天altersessionsetnls_date_format='yyyymmddhh24:mi:ss';selectsysdate-1,sysdate,sysdate+1fromdual;eg2：十分鐘之后altersessionsetnls_date_format='yyyymmddhh24:mi:ss';selectsysdate,sysdate+1/144fromdual;eg3：每臺unix服務器上的os帳號開通了多長時間（以天為單位）selectunix_host,os_username,create_date,round(sysdate-create_date)daysfromservice;eg4：上個月的今天，今天，下個月的今天altersessionsetnls_date_format='yyyymmddhh24:mi:ss';selectadd_months(sysdate,-1),sysdate,add_months(sysdate,1)fromdual;eg5：當前月的最后一天altersessionsetnls_date_format='yyyymmddhh24:mi:ss';selectlast_day(sysdate)fromdual;eg6：用戶注冊多長時間了selecttrunc(months_between(sysdate,create_date))fromservice;6.3字符類型1）定義：createtabletabname(c1 char(10),c2varchar2(10));2）char和varchar2區別：①varchar2必須定義長度，按字符串的實際長度存，最大長度4000字節，更省空間。②char可以不定義長度，默認為1，按定義長度存，最大長度2000字節，操作更快。③列的取值是定長，定義成char類型。④列的取值長度不固定，定義成varchar2。注意事項：在字符串比較中，varchar2按實際字符串比，對空格是敏感的，對大小些敏感。char會將短字符串補齊后，再與字符串比，對空格不敏感。varchar類型是ANSI定義的，varchar2類型是Oracle定義的，目前是等價的。但如果ANSI對varchar類型定義有變化，則Oraclevarchar2類型不變。eg：案例 varchar2(10)：'abc'='abc'yes;'abc'='abc'no char(10)：'abc'='abc'yes; 'abc'='abc'yes3）字符函數：參數類型為字符①upper()：函數將字符串轉換成大寫。②lower()：函數將字符串轉換成小寫。③initcap()：函數將字符串轉換成首字符大寫（是將列中的值大小寫轉換然后去和等號后的字符串比，而不是把轉字符串轉換去和列比）。④length()：字符串的長度。⑤rpad()、lpad()：將字符補成同樣長度，l和r表左右。⑥rtrim()、ltrim()：壓縮字符，l和r表左右，與fm相同效果。⑦concat()：拼接函數與“||”相似。⑧substr()：求子串函數。eg：相關操作selectrpad('FEBRARY',9,'*')fromdual;whereto_char(create_date,'fmMONTH')='MARCH';wherertrim(to_char(create_date,'MONTH'))='MARCH';selectconcat('ab','c')fromdual;↓從左往右 ↓從右往左selectos_username,substr(os_username,1,2),substr(os_username,-2,2)fromservice; 6.4轉換函數1）to_number()函數：將字符（串）轉換成number數值類型，這也是系統的缺省做法，即to_number('03')=3。注意事項：若to_number函數處理的字符串為'ab'，則系統報錯，若轉換后的值是十進制的，則要求字符串必須是數字字符。selectto_number('ab')fromdual;（報錯invalidnumber）2）to_char(date,char)函數：第一個參數為要處理的日期，第二個參數為格式；可獲取一個日期的任意一部分信息；對應java中format。 3）函數格式說明：9代表數字位0定義寬度大于實際寬度時，0會被強制顯示在前面，以補齊位數$美元符號L本地貨幣符號．小數點，每千位顯示一個逗號注意事項：如果顯示位數不足（定義寬度小于實際寬度），用#代替。eg1：相關操作selectto_char(base_cost,'L99.99')fromcost;selectto_char(base_cost,'L00.00')fromcost;selectto_char(base_cost,'$00.00')fromcost;eg2：顯示月固定費用，單位費用，單位費用為null，顯示nounitcostselectbase_cost,nvl(to_char(unit_cost),'nounitcost')unit_cost3）to_date(char,date)函數：將字符串轉換成一個日期值。對應java中parse。4）number、字符、data間的轉化①to_char()：number->字符date->字符②to_number()：字符->number③to_date()：字符->date5）顯式隱式轉換①隱式數據類型轉換，系統調用轉換函數wherecreate_datelike'%3%';隱式②顯式數據類型轉換，用戶調用轉換函數whereto_char(create_date,'mm')='03';顯式6.5其他注意事項1）insertinto表名values(1，2，3，4，null)有多列時，插入值必須都寫，沒值的也要寫null2）insertinto表名(C5)values(1234)表名最多30個字符且不能有特殊字符3）altersessionsetnls_language='AMERICAN';28-MAY-13altersessionsetnls_language='SIMPLIFIEDCHINESE';28-5月-134）altersessionsetnls_territory='AMERICA';altersessionsetnls_territory='CHINA';

SQL語句中的分支7.1分支表達式1）casewhen(then)，用于解決不同記錄需要不同處理方式的問題。when后面跟條件表達式，當所有when條件都不滿足時，若有else，表達式的返回結果為其后的值，否則返回null值。2）尋找when的優先級：從上到下再多的when，也只有一個出口，即其中有一個滿足了表達式expr就馬上退出case。3）elseexpr和returnexpr的數據類型必須相同。eg：當月包在線時長為20小時，單位費用漲5分，為40小時漲3分，其他不變（用CASEWHEN實現）selectbase_duration,unit_cost,casewhenbase_duration=20thenunit_cost+0.05whenbase_duration=40thenunit_cost+0.03elseunit_costendnew_nuit_costfromcost;7.2分支函數decode，是簡版的casewhen。1）decode(value,if1,then1,if2,then2,……,else)標識如果value等于if1時，返回then1。如果不等于任何一個if值，則返回else。eg：當月包在線時長為20小時，單位費用漲5分，為40小時漲3分，其他不變（用decode實現）selectbase_duration,unit_cost,decode(base_duration,20,unit_cost+0.05,40,unit_cost+0.03,unit_cost)n_base_costfromcost;

組函數操作在一組行（記錄）上，每組返回一個結果。8.1報表統計常用1）avg(distinct|all|n)：平均值，參數類型只能為number。2）sum(distinct|all|n)：求和，參數類型只能為number。3）count(distinct|all|expr|*)：計數，參數類型為number、字符、date。4）max(distinct|all|expr)：最大值，參數類型為number、字符、date。5）min(distinct|all|expr)：最小值，參數類型為number、字符、date。注意事項：distinct去重復時，會保留一個。 selectcount(distinctbase_duration)fromcost;//4，distinct保留一個空，但count統計時不算count（*）不管null，統計“記錄”數。count（列名）返回的是列中非null值的數量。8.2缺省情況組函數處理什么值所有的非空值。8.3當組函數要處理的所有值都為null時count函數返回0，其他函數返回null。8.4行級信息和組級信息返回的結果集包含多條記錄，是行級信息；返回的結果集包含一條記錄，是統計匯總信息，是組級別的信息；兩者不能同時顯示出來！處理方式：將行級信息變成組標識或進行組函數處理。eg1：單位費用的總和、平均值、最大值、最小值個數selectsum(unit_cost)sum1,avg(unit_cost)avg1,max(unit_cost)max1,min(unit_cost)min1,count(unit_cost)cntfromcost;eg2：若null值參與運算，必須將null值轉換成非null值selectavg(nvl(unit_cost,0)),sum(unit_cost)/count(*)fromcost;eg3：若unit_cost列中參與運算的數據都為null，avg(unit_cost)的函數值為null，count(unit_cost)的函數值為0。selectavg(unit_cost),count(unit_cost)fromcostwhereunit_costisnull;eg4：每臺unix服務器上開通的os帳號數即開戶數？selectunix_host,count(os_username)fromservicegroupbyunix_host;eg5：tarena26（6）上開通的os帳號數即開戶數？selectmax(unix_host),count(os_username)cntfromservicewhereunix_host='6'; 用min(unix_host)也可

groupby子句將表中的記錄進行分組9.1語法和執行順序語法順序：selectfromwheregroupbyorderby 執行順序：fromwheregroupbyselectorderby9.2分組過程根據groupby子句指定的表達式，將要處理的數據分成若干組（若有where子句即為通過條件過濾后的數據）。每組有唯一的組標識，組內有若干條記錄，根據select后面的組函數對每組的記錄進行計算，每組對應一個返回值。9.3常見錯誤若沒有groupby子句，select后面有一個是組函數，則其他都必須是組函數（記錄（行）信息和組信息不能放一起，要么都是組函數，要么都是單行函數）。若有groupby子句，select后面跟groupby后面跟的表達式以及組函數，其他會報錯。9.4多列分組包含多列用“，”分開，分組的個數多了，每組的記錄少了。eg：根據unix服務器ip地址、開通時間統計開通的os帳號數即開戶數selectunix_host,to_char(create_date,'yyyymmdd')create_date,count(os_username)cntfromservicegroupbyunix_host,to_char(create_date,'yyyymmdd');

having子句對分組過濾。10.1語法和執行順序語法順序：selectfromwheregroupbyhavingorderby執行順序：fromwheregroupbyhavingselectorderby10.2執行過程行被分組，將having子句的條件應用在每個分組上，只有符合having條件的組被保留，再應用select后面的組函數對每組的數據進行處理。10.3where和having區別1）where：過濾的是行（記錄），后面可跟任意列名，單行函數，不能跟組函數（無法對應到具體記錄），先執行，不允許用列別名。2）having：過濾的是分組（組標識、每組數據的聚合結果），后面只能包含groupby后面的表達式和組函數（能表達組信息的），后執行，不允許用列別名。eg1：哪些unix服務器開通的os帳號數即開戶數多于2個selectunix_host,count(os_username)cntfromservicegroupbyunix_hosthavingcount(os_username)>2;eg2：哪些unix服務器在哪幾天的開戶數多于1個selectunix_host,to_char(create_date,'yyyymmdd')create_date,count(os_username)cntfromservicegroupbyunix_host,to_char(create_date,'yyyymmdd')havingcount(os_username)>1;

非關聯子查詢子查詢就是在一條SQL（DDL、DML、TCL、DQL、DCL）語句中嵌入select語句。11.1語法selectcolname,…fromtabnamewhereexproperator(selectcolname2fromsubtabname);11.2子查詢的執行過程先執行子查詢，子查詢的返回結果作為主查詢的條件，再執行主查詢。子查詢只執行一遍。若子查詢的返回結果為多個值，Oracle會自動去掉重復值后，再將結果返回給主查詢。注意事項：不需要distinct，會自動去重的。eg1：哪些os帳號的開通時間是最早的selectunix_host,os_username,create_datefromservicewherecreate_date=(selectmin(create_date)fromservice);eg2：哪些os帳號的開通時間比unix服務器6上的huangr晚selectunix_host,create_date,os_usernamefromservicewherecreate_date>(selectcreate_datefromservicewhereos_username='huangr'andunix_host='6');eg3：哪些os帳號的開通時間比huangr晚？（多臺unix服務器上都有名為huangr的os帳號）selectunix_host,create_date,os_usernamefromservicewherecreate_date>all(selectcreate_datefromservice whereos_username='huangr');大于所有的wherecreate_date>(selectmax(create_date)fromservice whereos_username='huangr');大于最大的wherecreate_date>any(selectcreate_datefromservice whereos_username='huangr');大于任意一個wherecreate_date>(selectmin(create_date)fromservice whereos_username='huangr');大于最小的11.3常見錯誤單行子查詢返回多條記錄！此時要注意運算符的選擇：1）若子查詢的返回結果僅為一個值，可用單值運算符，如“=”號。2）若子查詢的返回結果可能為多個值，必須用多值運算符，如in等。eg：哪些客戶是推薦人selectreal_namefromaccountwhereidin(selectrecommender_idfromaccount);11.4子查詢與空值若子查詢的返回結果中包含空值null，并且運算為notin，那么整個查詢不會返回任何行。notin等價于<>all，任何值跟null比（包括null本身），結果都不為true。eg：哪些客戶不是推薦人selectreal_namefromaccountwhereidnotin(selectrecommender_idfromaccountwhererecommender_idisnotnull);11.5多列子查詢where子句后面可以跟多列條件表達式。eg1：哪些os帳號的開通時間是所在unix服務器上最早的？（每臺unix服務器上最早開通的os帳號）selectunix_host,os_username,create_datefromservicewhere(unix_host,create_date)in(selectunix_host,min(create_date)fromservice groupbyunix_host);eg2：哪些os帳號的開通時間比所在unix服務器上最早開通時間晚九天selectunix_host,os_username,create_datefromservicewhere(unix_host,to_char(create_date,'yyyymmdd'))in(selectunix_host,to_char(min(create_date)+9,'yyyymmdd')fromservice groupbyunix_host);

關聯子查詢關聯子查詢采用的是循環（loop）的方式。12.1語法selectcolumn1,…fromtable1owherecolumn1operator(selectcolumn1,column2fromtable2iwherei.expr1=o.expr2);12.2執行過程1）外部查詢得到一條記錄（查詢先從outer表中讀取數據），并將其傳入到內部的表查詢。2）內部查詢基于傳入的值執行。3）內部查詢從其結果中把值傳回到外部查詢，外部查詢使用這些值來完成處理，若符合條件，outer表中得到的那條記錄就放入結果集中，否則放棄。4）重復執行1－3，直到把outer表中的所有記錄判斷一遍。子查詢執行n遍。eg:哪些os帳號的開通天數比同一臺unix服務器上的平均開通天數長。selectunix_host,os_username,create_date,round(sysdate-create_date)open_agefromserviceowhereround(sysdate-create_date)>(selectavg(round(sysdate-create_date))fromserviceiwhereo.unix_host=i.unix_host);12.3existsexists采用的是循環（loop）的方式，判斷outer表中是否存在在inner表中找到的一條匹配的記錄。12.4exists執行過程1）外部查詢得到一條記錄（查詢先從outer表中讀取數據），并將其傳入到內部的表進行查詢。2）對inner表中的的記錄依次掃描，若根據條件，存在一條記錄與outer表中的記錄匹配，則立即停止掃描，返回true，將outer表中的記錄放入結果集中；若掃描了全部記錄，沒有任何一條記錄符合匹配條件，則返回false，outer表中的該記錄被過濾掉，不能出現在結果集中。3）重復執行1－2，直到把outer表中的所有記錄判斷一遍。eg1：哪些客戶是推薦人selectreal_namefromaccountowhereexists(select1fromaccountiwhereo.id=i.recommender_id);//1可隨便寫，不關心結果什么樣，只關心是否有滿足的條件返回eg2：哪些客戶申請了遠程登錄業務非關聯子查詢：selectreal_namefromaccountwhereidin(selectaccount_idfromservice);關聯子查詢：selectreal_namefromaccountowhereexists(select1fromservicei whereo.id=i.account_id);12.5notexists采用的是循環（loop）的方式，判斷outer表中是否不存在記錄（它能在inner表中找到匹配的記錄）。12.6notexists執行過程1）外部查詢得到一條記錄（查詢先從outer表中讀取數據），并將其傳入到內部的表進行查詢。2）對inner表中的的記錄依次掃描，若根據條件，存在一條記錄與outer表中的記錄匹配，則立即停止掃描，返回false，將outer表中的記錄過濾掉，不能出現在結果集中；若掃描了全部記錄，沒有任何一條記錄符合匹配條件，則返回true，outer表中的該記錄放入結果集中。3）重復執行1－2，直到把outer表中的所有記錄判斷一遍。eg1：哪些客戶不是推薦人selectreal_namefromaccountowherenotexists(select1fromaccounti whereo.id=i.recommender_id);eg2：哪些客戶沒有申請遠程登錄業務非關聯子查詢：selectreal_namefromaccountwhereidnotin(selectaccount_idfromservice);關聯子查詢：selectreal_namefromaccountowherenotexists(select1fromservicei whereo.id=i.account_id);12.7in和exists比較1）exists是用循環（loop）的方式，有outer表的記錄數決定循環次數，對于exists影響最大，所以，外表的記錄數要少。2）in先執行子查詢，子查詢的結果去重之后，再執行主查詢，所以，子查詢的返回結果越少，越適合用該方式。

多表查詢結果集中的記錄保存在多張表中。13.1按范式要求設計表結構第二范式：每個非主屬性必須完全依賴于主屬性（主鍵pk列）（避免多對多合表造成數據冗余）。第三范式：每個非主屬性不能依賴于另一個非主屬性（避免一對多合表造成數據冗余，不一致）。13.2多表連接的種類交叉連接（crossjoin）、內連接（innerjoin）、外連接（outerjoin）。13.3交叉連接數學中的組合問題。1）假設table1表中有m條記錄，table2表中有n條記錄，交叉連接產生的結果集為m*n。該結果產生的結果集為笛卡爾積。2）語法：selecttabname1.colname1,tabname2.colname2fromtabname1crossjointabname2;eg：案例selecta.real_name,a.id,s.account_id,s.unix_host,s.os_usernamefromaccountacrossjoinservices;13.4內連接核心解決匹配問題，建議用on and and多條件組合，不用where。語法：selecttabname1.colname1,tabname2.colname2fromtabname1jointabname2ontabname1.colname1=tabname2.colname2and其他條件;2）如果有多個條件表達式，on關鍵字后面跟一個，其余用and條件連接。eg：客戶huangrong在哪些unix服務器上申請了遠程登錄業務selecta.real_name,s.unix_host,s.os_username,s.create_datefromaccountajoinservicesona.id=s.account_idanda.real_name='huangrong';3）內連接原理一：t1和t2表作內連接，連接條件為ont1.c1=t2.c2，假設t1表作驅動表，t2表作匹配表，記錄過程如下：①從t1表中讀取一條記r1，若它的列c1值為1②根據該值到t2表中查找匹配的記錄，即需要遍歷t2表，從t2表中的第一條記錄開始，若查找的記錄的c2列的值為1，我們就說這兩條記錄能夠匹配上，那么t1的r1和t2中剛剛匹配的該條記錄組合起來，作為結果集里的一條記錄，否則檢測t2表中的下一條記錄。③按照步驟2依次將t2表中所有的記錄檢測一遍，只要匹配就放入結果集中。④從t1表中讀取第二條記錄，依次重復步驟2和3，產生最終的結果集。eg：列出申請了遠程登錄業務的客戶姓名以及在unix服務器上的開通信息selecta.real_name,s.unix_host,s.os_username,s.create_datefromaccountajoinservicesona.id=s.account_id;4）內連接原理二：t1和t2表作內連接，連接條件為ont1.c1=t2.c2，假設t1表作驅動表，t2表作匹配表，記錄的匹配有如下三種情況：①t1表中的某條記錄在t2表中找不到任何一條匹配的記錄，那么t1表中的該條記錄不會出現在結果集中。②t1表中的某條記錄在t2表中只有一條匹配的記錄，那么t1表中的該記錄和t2表中匹配的記錄組合成新的記錄出現在結果集中。③t1表中的某條記錄在t2表中有多條匹配的記錄，那么t1表中的該記錄會和t2表中每一條匹配的記錄組合成新的記錄出現在結果集中。注意事項：內連接的核心為：任何一張表里的記錄一定要在另一張表中找到匹配的結果，否則不能出現在結果集中。5）內連接原理三：t1和t2表作內連接，連接條件為ont1.c1=t2.c2，以下兩種方式都可以得到相同的結果集：①一種t1作驅動表，t2作匹配表②一種t2作驅動表，t1作匹配表 ③無論那種方式，最終得到的結果集都一樣，所不同的是效率。6）內連接的結果集結構：t1.c1t1.c2t1.c3……t2.c1t2.c2t2.c37）內連接的語句執行順序：先根據on和and條件對要連接的表進行過濾，將過濾后的結果集進行內連接操作（joinon），再根據select語句的定義生成最終的結果集。注意事項：內連接中使用on和where都可以。8）from后面可跟子查詢eg1：列出客戶姓名以及開通的遠程登錄業務的數量方式一：先連接再統計selecta.id,max(a.real_name),count(a.id)fromaccountajoinservicesona.id=s.account_idgroupbya.id;方式二：先統計再連接，效率更高。selecta.real_name,count(a.id)fromaccountajoin(selectaccount_idcount(id)cntfromservicegroupbyaccount_id)c ona.id=c.account_ideg2：列出客戶姓名以及他的推薦人（考查了內連接、空值轉換、decode）selecta1.real_namerecommended,decode(a2.id,a1.id,'NoRecommender',a2.real_name)recommenderfromaccounta1joinaccounta2onnvl(a1.recommender_id,a1.id)=a2.id;9）自連接：①同一張表的行（記錄）之間的匹配關系可以用同一張表的列之間的條件表達式描述。②通過給表起別名，將同一張表的列之間的關系轉換成不同表的列之間的條件表達式。eg：哪些客戶是推薦人selectdistincta2.id,a2.real_namefromaccounta1joinaccounta2ona1.recommender_id=a2.id;10）其他案例eg1：顯示客戶姓名，開通的遠程登錄業務的數量。(結果集中只包含開通了遠程登錄業務的客戶)selectt1.real_name,tfromaccountt1join (selectaccount_id,count(*)cntfromservicegroupbyaccount_id)t2 ont2.account_id=t1.id;注意事項：count(*)已經到了不得不起別名的地步，組函數不可作與單行函數在一起顯示的。eg2：顯示客戶姓名，開通的遠程登錄業務的數量。(結果集中只包含開通了遠程登錄業務的客戶)selectmin(a.real_name),count(s.account_id)fromaccountajoinservicesona.id=s.account_idgroupbya.id;注意事項：eg1比eg2的效率高！兩個表出統計結果時，一個表就能出來結果的就先單表統計再連接。否則，就先連接再統計！！！！in（非關聯子查詢）exists（關聯子查詢）join（表查詢）都是在解決匹配問題。匹配是記錄和記錄的匹配，是邏輯上的匹配，不一定非要是物理上獨立個體的匹配，也可在一個表的記錄間相互匹配。13.5外連接作用：①把匹配和不匹配的都找出來。②只找不匹配的，匹配的交給內連接作。1）語法：leftrightfull定驅動表的fromt1left（outer）joint2ont1.c1=t2.c2outer可省左表為驅動表fromt1right（outer）joint2ont1.c1=t2.c2 右表為驅動表fromt1full（outer）joint2ont1.c1=t2.c2 左右表都為驅動表2）外連接原理一：t1和t2表作外連接，連接條件為fromt1leftouterjoint2ont1.c1=t2.c2，t1表必須作驅動表，t2表作匹配表，記錄的匹配過程如下：①從t1表中讀取一條記r1，若它的列c1值為1②根據該值到t2表中查找匹配的記錄，即需要遍歷t2表，從t2表中的第一條記錄開始，若查找的記錄的c2列的值為1，我們就說這兩條記錄能夠匹配上，那么t1的r1和t2中剛剛匹配的該條記錄組合起來，作為結果集里的一條記錄，否則檢測t2表中的下一條記錄。③按照步驟2依次將t2表中所有的記錄檢測一遍，只要匹配就放入結果集中。若掃描完后，t1的r1記錄在t2表中找不到任何匹配的記錄，t2表中模擬一條null記錄與t1表中的r1組合起來，放入結果集中。④從t1表中讀取第二條記錄，依次重復步驟2和3，產生最終的結果集。3）外連接原理二：t1和t2表作外連接，連接條件為fromt1leftouterjoint2ont1.c1=t2.c2，t1表必須作驅動表，t2表作匹配表：①外連接的結果集＝內連接結果集+t1表中匹配不上的記錄和一條null記錄（按t2表的結構）組成的記錄的組合。②外連接的核心可以將t1中匹配不上的記錄（按on條件在t2中找不到對應的匹配記錄）也顯示出來，而不像內連接直接過濾掉，即t1中的記錄一個都不少的出現在結果集中。③外連接結果集的記錄數不一定是驅動表的記錄數（結果集記錄數>=驅動表記錄數）。4）外連接原理三：t1和t2表作外連接，連接條件為fromt1rightouterjoint2ont1.c1=t2.c2，t2表必須作驅動表，t1表作匹配表：①外連接的結果集＝內連接結果集+t2表中匹配不上的記錄和一條null記錄（按t1表的結構）組成的記錄的組合。②外連接的核心可以將t2中匹配不上的記錄（按on條件在t1中找不到對應的匹配記錄）也顯示出來，而不像內連接直接過濾掉，即t2中的記錄一個都不少的出現在結果集中。③外連接結果集的記錄數不一定是驅動表的記錄數（結果集記錄數>=驅動表記錄數）。5）外連接原理四：t1和t2表作外連接，連接條件為fromt1fullouterjoint2ont1.c1=t2.c2，t1表必須作驅動表，t2表作匹配表：①外連接的結果集＝內連接結果集+t1表中匹配不上的記錄和一條null記錄（按t2表的結構）組成的記錄+t2表中匹配不上的記錄和一條null記錄（按t1表的結構）組成的記錄的組合。②外連接結果集的記錄數不一定是t1表和t2表的記錄數之和。eg1：列出客戶姓名以及他的推薦人selecta1.real_namecustomer,nvl(a2.real_name,'NoRecommender')recommenderfromaccounta1leftjoinaccounta2ona1.recommender_id=a2.id;eg2：列出客戶姓名以及所開通的遠程登錄業務的信息(沒有申請遠程登錄業務的客戶也要出現在結果集中)selecta.id,a.real_name,s.unix_host,s.os_usernamefromaccountaleftjoinservices ona.id=s.account_id;eg3：哪些客戶不是推薦人selecta1.real_namerecommenderfromaccounta1leftjoinaccounta2ona1.id=a2.recommender_idwherea2.idisnull;6）外連接語句的執行順序若on子句后面有and條件，則現對匹配表進行過濾，然后再進行外連接（joinon），再對外連接的結果集用where子句進行過濾，最后用select語句生成最終的結果集。on和where后面都可以跟多個條件表達式，表達式之間用and連接eg：哪些UNIX服務器上沒有os帳號weixbselecth.id,,h.locationfromhosthleftjoinservicesonh.id=s.unix_hostands.os_username='weixb'wheres.idisnull;①先過濾service表，用s.os_username='weixb'②過濾后的結果集作匹配表，host表作驅動表，進行外連接，用where對外連接的結果集進行過濾，產生最終結果。注意事項：驅動表和匹配表的關系，也就是指驅動表中的記錄和匹配表中的記錄的關系，通過on聯系；要想統計出正確的數量count，必須統計匹配表的“非空列”！對內連接and、where用誰都行，但外連接則有嚴格的使用位置。過濾驅動表一定用where子句。13.6非等值連接不同表沒有共同屬性的列，但兩張表的列可以寫成一個SQL條件表達式。eg1：顯示客戶的年齡段selectt1.real_name,round((sysdate-t1.birthdate)/365)age,fromaccountt1joinage_segmentt2onround((sysdate-t1.birthdate)/365)betweent2.lowageandt2.hiage;eg2：顯示客戶huangrong的年齡段selectt1.real_name,round((sysdate-t1.birthdate)/365)age,fromaccountt1joinage_segmentt2onround((sysdate-t1.birthdate)/365)betweent2.lowageandt2.hiage andreal_name='huangrong';eg3：顯示青年年齡段中的客戶數selectt1.real_name,round((sysdate-t1.birthdate)/365)age,fromaccountt1joinage_segmentt2onround((sysdate-t1.birthdate)/365)like'青年%';eg4：顯示各個年齡段的客戶數（沒有客戶的年齡段的客戶數為0）selectmax(),count(t1.id)fromaccountt1rightjoinage_segmentt2onround((sysdate-t1.birthdate)/365)betweent2.lowageandt2.hiagegroupbyt2.id;搞