SQL數(shù)據(jù)庫基礎(chǔ)培訓(xùn).ppt

第1章安裝,1.1ORACLE數(shù)據(jù)庫安裝1.2PL/SQL工具安裝,默認(rèn)下一步即可.1.3配置本機(jī)tnsname.ora文件1.4測(cè)試tnsname.ora配置是否正確1.5PL/SQL登錄,第2章SQL基礎(chǔ),2.1數(shù)據(jù)庫查詢語言SQL2.2基本查詢和排序2.3條件查詢2.4函數(shù)2.5高級(jí)查詢2.6階段訓(xùn)練2.7練習(xí),2.1數(shù)據(jù)庫查詢語言SQL,2.1.1SQL語言的特點(diǎn)和分類SQL語言有以下的主要特點(diǎn)：*SQL語言可以在Oracle數(shù)據(jù)庫中創(chuàng)建、存儲(chǔ)、更新、檢索和維護(hù)數(shù)據(jù)，其中主要的功能是實(shí)現(xiàn)數(shù)據(jù)的查詢和數(shù)據(jù)的插入、刪除、修改等操作。*SQL語言在書寫上類似于英文，簡潔清晰，易于理解。它由關(guān)鍵字、表名、字段名，表達(dá)式等部分構(gòu)成。,*SQL語言屬于非過程化的4GL(第四代語言)。*SQL語言按功能可分為DDL語言、DML語言、DCL語言和數(shù)據(jù)庫事務(wù)處理語言四個(gè)類別。*SQL語言的主要關(guān)鍵字有：ALTER、DROP、REVOKE、AUDIT、GRANT、ROLLBACK、COMMIT、INSERT、SELECT、COMMENT、LOCK、UPDATE、CREATE、NOAUDIT、VALIDATE、DELETE、RENAME等。按照SQL語言的不同功用，可以進(jìn)一步對(duì)SQL語言進(jìn)行劃分。下表給出了SQL語言的分類和功能簡介。,表2-1SQL語言的分類,2.1.2SQL的基本語法SQL語言的語法比較簡單，類似于書寫英文的語句。其語句一般由主句和若干個(gè)從句組成，主句和從句都由關(guān)鍵字引導(dǎo)。主句表示該語句的主要功能，從句表示一些條件或限定，有些從句是可以省略的。在語句中會(huì)引用到列名、表名或表達(dá)式。另外還有如下一些說明：*關(guān)鍵字、字段名、表名等之間都要用空格或逗號(hào)等進(jìn)行必要的分隔。*語句的大小寫不敏感(查詢的內(nèi)容除外)。*語句可以寫在一行或多行。*語句中的關(guān)鍵字不能略寫和分開寫在兩行。,*要在每條SQL語句的結(jié)束處添加“；”號(hào)。*為了提高可讀性，可以使用縮進(jìn)。*從句一般寫在另一行的開始處。查詢語句是最常見的SQL語句，它從給定的表中，把滿足條件的內(nèi)容檢索出來。以下是最基本的SELECT語句語法。SELECT字段名列表FROM表名WHERE條件;SELECT為查詢語句的關(guān)鍵字，后跟要查詢的字段名列表，字段名列表用來指定檢索特定的字段，該關(guān)鍵字不能省略。,字段名列表代表要查詢的字段。FROM也是查詢語句關(guān)鍵字，后面跟要查詢的表名，該關(guān)鍵字不能省略。WHERE條件限定檢索特定的記錄，滿足“條件”的記錄被顯示出來，不滿足條件的被過濾掉。語句查詢的結(jié)果往往是表的一部分行和列。如果字段名列表使用*，將檢索全部的字段。如果省略WHERE條件，將檢索全部的記錄。,【訓(xùn)練1】查詢部門10的雇員。輸入并執(zhí)行查詢：SELECT*FROMempWHEREdeptno=10;結(jié)果略。說明：該查詢語句從emp表中檢索出部門10的雇員，條件由WHEREdeptno=10子句指定。,2.2基本查詢和排序,2.2.1查詢的基本用法在Oracle數(shù)據(jù)庫中，對(duì)象是屬于模式的，每個(gè)賬戶對(duì)應(yīng)一個(gè)模式，模式的名稱就是賬戶名稱。在表名前面要添加模式的名字，在表的模式名和表名之間用“.”分隔。我們以不同的賬戶登錄數(shù)據(jù)庫時(shí)，就進(jìn)入了不同的模式，比如登錄到STUDENT賬戶，就進(jìn)入了STUDENT模式。而在STUDENT模式要查詢屬于SCOTT模式的表，就需要寫成：SELECT*FROMSCOTT.EMP;,但如果登錄用戶訪問屬于用戶模式本身的表，那么可以省略表名前面的模式名稱。SELECT*FROMemp;1指定檢索字段下面的練習(xí)，只顯示表的指定字段?！居?xùn)練1】顯示DEPT表的指定字段的查詢。輸入并執(zhí)行查詢：SELECTdeptno,dnameFROMdept;,顯示結(jié)果如下：DEPTNODNAME-10ACCOUNTING20RESEARCH30SALES40OPERATIONS說明：結(jié)果只包含2列deptno和dname。在語句中給出要顯示的列名，列名之間用“，”分隔。表頭的顯示默認(rèn)為全部大寫。對(duì)于日期和數(shù)值型數(shù)據(jù)，右對(duì)齊顯示，如deptno列。對(duì)于字符型數(shù)據(jù)，左對(duì)齊顯示，如dname列。,【練習(xí)1】顯示emp表的雇員名稱和工資。2顯示行號(hào)每個(gè)表都有一個(gè)虛列ROWNUM，它用來顯示結(jié)果中記錄的行號(hào)。我們?cè)诓樵冎幸部梢燥@示這個(gè)列?！居?xùn)練2】顯示EMP表的行號(hào)。輸入并執(zhí)行查詢：SELECTrownum,enameFROMemp;,結(jié)果如下：ROWNUMENAME-1SMITH2ALLEN3WARD4JONES注意：顯示的行號(hào)是查詢結(jié)果的行號(hào)，數(shù)據(jù)在數(shù)據(jù)庫中是沒有行號(hào)的。,3顯示計(jì)算列在查詢語句中可以有算術(shù)表達(dá)式，它將形成一個(gè)新列，用于顯示計(jì)算的結(jié)果，通常稱為計(jì)算列。表達(dá)式中可以包含列名、算術(shù)運(yùn)算符和括號(hào)。括號(hào)用來改變運(yùn)算的優(yōu)先次序。常用的算術(shù)運(yùn)算符包括：*+：加法運(yùn)算符。*-：減法運(yùn)算符。*：乘法運(yùn)算符。*/：除法運(yùn)算符。以下訓(xùn)練在查詢中使用了計(jì)算列。,【訓(xùn)練3】顯示雇員工資上浮20%的結(jié)果。輸入并執(zhí)行查詢:SELECTename,sal,sal*(1+20/100)FROMemp;顯示結(jié)果為：ENAMESALSAL*(1+20/100)-SMITH800960ALLEN16001920說明：結(jié)果中共顯示了3列，第3列顯示工資上浮20%的結(jié)果，它不是表中存在的列，而是計(jì)算產(chǎn)生的結(jié)果，稱為計(jì)算列。,【練習(xí)2】顯示EMP表的雇員名稱以及工資和津貼的和。4使用別名我們可以為表的列起一個(gè)別名，它的好處是，可以改變表頭的顯示。特別是對(duì)于計(jì)算列，可以為它起一個(gè)簡單的列別名以代替計(jì)算表達(dá)式在表頭的顯示。【訓(xùn)練4】在查詢中使用列別名。輸入并執(zhí)行：SELECTenameAS名稱,sal工資FROMemp;顯示結(jié)果為：名稱工資-SMITH800ALLEN1600,說明：表頭顯示的是列別名，轉(zhuǎn)換為漢字顯示。在列名和別名之間要用AS分隔，如ename和它的別名“名稱”之間用AS隔開。AS也可以省略，如sal和它的別名“工資”之間用空格分割。注意：如果用空格分割，要區(qū)別好列名和別名，前面為列名，后面是別名。別名如果含有空格或特殊字符或大小寫敏感，需要使用雙引號(hào)將它引起來。,【訓(xùn)練5】在列別名上使用雙引號(hào)。輸入并執(zhí)行查詢：SELECTenameASName,sal*12+5000AS年度工資(加年終獎(jiǎng))FROMemp;顯示結(jié)果為：Name年度工資(加年終獎(jiǎng))-SMITH14600ALLEN24200,說明：其中別名“Name”有大小寫的區(qū)別，別名“年度工資(加年終獎(jiǎng))”中出現(xiàn)括號(hào)，屬于特殊符號(hào)，所以都需要使用雙引號(hào)將別名引起?！揪毩?xí)3】顯示DEPT表的內(nèi)容，使用別名將表頭轉(zhuǎn)換成中文顯示。5連接運(yùn)算符在前面，我們使用到了包含數(shù)值運(yùn)算的計(jì)算列，顯示結(jié)果也是數(shù)值型的。我們也可以使用字符型的計(jì)算列，方法是在查詢中使用連接運(yùn)算。連接運(yùn)算符是雙豎線“|”。通過連接運(yùn)算可以將兩個(gè)字符串連接在一起。,【訓(xùn)練6】在查詢中使用連接運(yùn)算。輸入并執(zhí)行查詢：SELECTename|jobAS雇員和職務(wù)表FROMemp;輸出結(jié)果為：雇員和職務(wù)表-SMITHCLERKALLENSALESMAN,說明：在本例中，雇員名稱和職務(wù)列被連接成為一個(gè)列顯示。在查詢中可以使用字符和日期的常量，表示固定的字符串或固定日期。字符和日期的常量需要用單引號(hào)引起。下一個(gè)訓(xùn)練是作為上一個(gè)訓(xùn)練的改進(jìn)。,【訓(xùn)練7】在查詢中使用字符串常量。輸入并執(zhí)行查詢：SELECTename|IS|jobAS雇員和職務(wù)表FROMemp;輸出結(jié)果為：雇員和職務(wù)表-SMITHISCLERKALLENISSALESMAN說明：本練習(xí)中將雇員名稱、字符串常量“IS”和雇員職務(wù)3個(gè)部分連接在一起。,【練習(xí)4】顯示DEPT表的內(nèi)容，按以下的形式:部門ACCOUNTING所在的城市為NEWYORK6消除重復(fù)行如果在顯示結(jié)果中存在重復(fù)行，可以使用的關(guān)鍵字DISTINCT消除重復(fù)顯示。,【訓(xùn)練8】使用DISTINCT消除重復(fù)行顯示。輸入并執(zhí)行查詢：SELECTDISTINCTjobFROMemp;結(jié)果為：JOB-ANALYSTCLERKMANAGERPRESIDENTSALESMAN,說明：在本例中，如果不使用DISTINCT關(guān)鍵字，將重復(fù)顯示雇員職務(wù)，DISTINCT關(guān)鍵字要緊跟在SELECT之后。請(qǐng)去掉DISTINCT關(guān)鍵字，重新執(zhí)行，并觀察顯示結(jié)果的不同。【練習(xí)5】顯示EMP表中不同的部門編號(hào)。2.2.2查詢結(jié)果的排序如果要在查詢的同時(shí)排序顯示結(jié)果，可以使用如下的語句：SELECT字段列表FROM表名WHERE條件ORDERBY字段名1ASC|DESC,字段名2ASC|DESC.;,ORDERBY從句后跟要排序的列。ORDERBY從句出現(xiàn)在SELECT語句的最后。排序有升序和降序之分，ASC表示升序排序，DESC表示降序排序。如果不指明排序順序，默認(rèn)的排序順序?yàn)樯?。如果要降序，必須書寫DESC關(guān)鍵字。,1升序排序【訓(xùn)練1】查詢雇員姓名和工資，并按工資從小到大排序。輸入并執(zhí)行查詢：SELECTename,salFROMempORDERBYsal;執(zhí)行結(jié)果為：ENAMESAL-SMITH800JAMES950注意：若省略ASC和DESC，則默認(rèn)為ASC，即升序排序。,2降序排序【訓(xùn)練2】查詢雇員姓名和雇傭日期，并按雇傭日期排序，后雇傭的先顯示。輸入并執(zhí)行查詢：SELECTename,hiredateFROMempORDERBYhiredateDESC;結(jié)果如下：ENAMEHIREDATE-ADAMS23-5月-87SCOTT19-4月-87,MILLER23-1月-82JAMES03-12月-81FORD03-12月-81注意：DESC表示降序排序，不能省略。3多列排序可以按多列進(jìn)行排序，先按第一列，然后按第二列、第三列.?！居?xùn)練3】查詢雇員信息，先按部門從小到大排序，再按雇傭時(shí)間的先后排序。輸入并執(zhí)行查詢：SELECTename,deptno,hiredateFROMempORDERBYdeptno,hiredate;,結(jié)果如下：ENAMEDEPTNOHIREDATE-CLARK1009-6月-81KING1017-11月-81MILLER1023-1月-82SMITH2017-12月-80JONES2002-4月-81FORD2003-12月-81SCOTT2019-4月-87說明：該排序是先按部門升序排序，部門相同的情況下，再按雇傭時(shí)間升序排序。,4在排序中使用別名如果要對(duì)計(jì)算列排序，可以為計(jì)算列指定別名，然后按別名排序?！居?xùn)練4】按工資和工作月份的乘積排序。輸入并執(zhí)行查詢：SELECTempno,ename,sal*Months_between(sysdate,hiredate)AStotalFROMempORDERBYtotal;,執(zhí)行結(jié)果為：EMPNOENAMETOTAL-7876ADAMS221526.0067369SMITH222864.6617900JAMES253680.8177654MARTIN336532.484說明：求得雇員工作月份的函數(shù)Months_between將在后面介紹。sysdate表示當(dāng)前日期?！揪毩?xí)1】將部門表中的部門名稱按字母順序顯示。,2.3條件查詢,2.3.1簡單條件查詢要對(duì)顯示的行進(jìn)行限定，可在FROM從句后使用WHERE從句，在WHERE從句中給出限定的條件，因?yàn)橄薅l件是一個(gè)表達(dá)式，所以稱為條件表達(dá)式。條件表達(dá)式中可以包含比較運(yùn)算，表達(dá)式的值為真的記錄將被顯示。常用的比較運(yùn)算符列于表2-2中。,表2-2比較運(yùn)算符,【訓(xùn)練1】顯示職務(wù)為“SALESMAN”的雇員的姓名、職務(wù)和工資。輸入并執(zhí)行查詢：SELECTename,job,salFROMempWHEREjob=SALESMAN;執(zhí)行結(jié)果為：ENAMEJOBSAL-ALLENSALESMAN1600WARDSALESMAN1250MARTINSALESMAN1250TURNERSALESMAN1500,說明：結(jié)果只顯示職務(wù)為“SALESMAN”的雇員。字符串和日期型數(shù)據(jù)的值是包含在單引號(hào)中的，如SALESMAN，需要用單引號(hào)引起。字符的值對(duì)大小寫敏感，在emp表中存放的職務(wù)字符串全部是大寫。注意：在本練習(xí)中，如果SALESMAN寫成小寫或大小寫混合，將不會(huì)有查詢結(jié)果輸出。,【訓(xùn)練2】顯示工資大于等于3000的雇員姓名、職務(wù)和工資。輸入并執(zhí)行查詢：SELECTename,job,salFROMempWHEREsal=3000;執(zhí)行結(jié)果為：ENAMEJOBSAL-SCOTTANALYST3000KINGPRESIDENT5000FORDANALYST3000,說明：結(jié)果只顯示工資大于等于3000的雇員。缺省中文日期格式為DD-MM月-YY，如2003年1月10日應(yīng)該表示為“10-1月-03”?！居?xùn)練3】顯示1982年以后雇傭的雇員姓名和雇傭時(shí)間。輸入并執(zhí)行查詢：SELECTename,hiredateFROMempWHEREhiredate=1-1月-82;執(zhí)行結(jié)果為：ENAMEHIREDATE-,SCOTT19-4月-87ADAMS23-5月-87MILLER23-1月-82說明：檢查hiredate字段的內(nèi)容，都在82年以后?！揪毩?xí)1】顯示部門編號(hào)為10的雇員姓名和雇傭時(shí)間。,2.3.2復(fù)合條件查詢可以用邏輯運(yùn)算符構(gòu)成復(fù)合的條件查詢，即把兩個(gè)或多個(gè)條件，用邏輯運(yùn)算符連接成一個(gè)條件。有3個(gè)邏輯運(yùn)算符，如表2-3所示。,表2-3邏輯運(yùn)算符,運(yùn)算的優(yōu)先順序是NOT，AND，OR。如果要改變優(yōu)先順序，可以使用括號(hào)。下面是使用邏輯與運(yùn)算的練習(xí)。1使用邏輯與【訓(xùn)練1】顯示工資在10002000之間(不包括1000和2000)的雇員信息。,輸入并執(zhí)行查詢：SELECTename,job,salFROMempWHEREsal1000ANDsal1000ANDsal1000AND0;執(zhí)行結(jié)果為：EMPNOENAMELENGTH(ENAME)-7369SMITH57566JONES5,說明：本例在字段列表和查詢條件中分別應(yīng)用了函數(shù)length和instr。Length函數(shù)返回ename的長度。instr(ename,S1,1)函數(shù)返回ename中從第一個(gè)字符位置開始，字符串“S”第一次出現(xiàn)的位置。如果函數(shù)返回0，則說明ename中不包含字符串“S”；如果函數(shù)返回值大于0，則說明ename中包含字符串“S”。,【練習(xí)1】顯示部門表中部門和所在城市列表，中間以下劃線“_”連接，城市名轉(zhuǎn)換成以大寫字母開頭。2.4.3日期型函數(shù)Oracle使用內(nèi)部數(shù)字格式來保存時(shí)間和日期，包括世紀(jì)、年、月、日、小時(shí)、分、秒。缺省日期格式為DD-MON-YY，如“08-05月-03”代表2003年5月8日。SYSDATE是返回系統(tǒng)日期和時(shí)間的虛列函數(shù)。使用日期的加減運(yùn)算，可以實(shí)現(xiàn)如下功能：*對(duì)日期的值加減一個(gè)天數(shù)，得到新的日期。,*對(duì)兩個(gè)日期相減，得到相隔天數(shù)。*通過加小時(shí)來增加天數(shù)，24小時(shí)為一天，如12小時(shí)可以寫成12/24(或0.5)。還有如表2-7所示的日期函數(shù)可以使用。,表2-7日期函數(shù),【訓(xùn)練1】返回系統(tǒng)的當(dāng)前日期。輸入并執(zhí)行查詢：SELECTsysdateFROMdual;返回結(jié)果為：SYSDATE-06-2月-03說明：該查詢返回執(zhí)行該查詢時(shí)的數(shù)據(jù)庫服務(wù)器的系統(tǒng)當(dāng)前時(shí)間，日期顯示格式為默認(rèn)格式，如“06-2月-03”表示03年2月6日。,【訓(xùn)練2】返回2003年2月的最后一天。輸入并執(zhí)行查詢：SELECTlast_day(08-2月-03)FROMdual;返回結(jié)果為：LAST_DAY(-28-2月-03說明：該函數(shù)給定參數(shù)為某月份的任意一天，返回時(shí)間為該月份的最后一天。本例中，參數(shù)為03年2月8號(hào)，返回日期為03年2月28日，是該月的最后一天。,【訓(xùn)練3】假定當(dāng)前的系統(tǒng)日期是2003年2月6日，求再過1000天的日期。輸入并執(zhí)行查詢：SELECTsysdate+1000ASNEWDATEFROMdual;返回結(jié)果為：NEWDATE-04-11月-05說明：該查詢使用到了日期的加法運(yùn)算，求經(jīng)過一定天數(shù)后的新日期。,【訓(xùn)練4】假定當(dāng)前的系統(tǒng)日期是2003年2月6日，顯示部門10雇員的雇傭天數(shù)。輸入并執(zhí)行查詢：SELECTename,round(sysdate-hiredate)DAYSFROMempWHEREdeptno=10;返回結(jié)果為：ENAMEDAYS-CLARK7913KING7752MILLER7685說明：該查詢使用日期的減法運(yùn)算求兩個(gè)日期的相差天數(shù)。用round函數(shù)對(duì)天數(shù)進(jìn)行四舍五入。,【練習(xí)1】顯示雇員名稱和雇傭的星期數(shù)?！揪毩?xí)2】顯示從本年1月1日開始到現(xiàn)在經(jīng)過的天數(shù)(當(dāng)前時(shí)間取SYSDATE的值)。2.4.4轉(zhuǎn)換函數(shù)Oracle的類型轉(zhuǎn)換分為自動(dòng)類型轉(zhuǎn)換和強(qiáng)制類型轉(zhuǎn)換。常用的類型轉(zhuǎn)換函數(shù)有TO_CHAR、TO_DATE或TO_NUMBER，如表2-8所示。,表2-8類型轉(zhuǎn)換函數(shù),1自動(dòng)類型轉(zhuǎn)換Oracle可以自動(dòng)根據(jù)具體情況進(jìn)行如下的轉(zhuǎn)換：*字符串到數(shù)值。*字符串到日期。*數(shù)值到字符串。*日期到字符串。以下是自動(dòng)轉(zhuǎn)換的訓(xùn)練。,【訓(xùn)練1】自動(dòng)轉(zhuǎn)換字符型數(shù)據(jù)到數(shù)值型。輸入并執(zhí)行查詢：SELECT12.5+11FROMdual;執(zhí)行結(jié)果為：12.5+11-23.5說明：在本訓(xùn)練中，因?yàn)槌霈F(xiàn)+運(yùn)算符，說明進(jìn)行的是算術(shù)運(yùn)算，所以字符串12.5被自動(dòng)轉(zhuǎn)換成數(shù)值12.5，然后參加運(yùn)算。,【訓(xùn)練2】自動(dòng)轉(zhuǎn)換數(shù)值型數(shù)據(jù)到字符型。執(zhí)行以下查詢：SELECT12.5|11FROMdual;結(jié)果為：12.5-12.511說明：在本訓(xùn)練中，因?yàn)槌霈F(xiàn)|運(yùn)算符，說明進(jìn)行的是字符串連接運(yùn)算，數(shù)值11被自動(dòng)轉(zhuǎn)換成字符串11，然后參加運(yùn)算。,2日期類型轉(zhuǎn)換將日期型轉(zhuǎn)換成字符串時(shí)，可以按新的格式顯示。如格式Y(jié)YYY-MM-DDHH24:MI:SS表示“年-月-日小時(shí):分鐘:秒”。Oracle的日期類型是包含時(shí)間在內(nèi)的。主要的日期格式字符的含義如表2-9所示。,表2-9日期轉(zhuǎn)換格式字符,表2-9日期轉(zhuǎn)換格式字符,【訓(xùn)練3】將日期轉(zhuǎn)換成帶時(shí)間和星期的字符串并顯示。執(zhí)行以下查詢：SELECTTO_CHAR(sysdate,YYYY-MM-DDHH24:MI:SSAMDY)FROMdual;結(jié)果為：TO_CHAR(SYSDATE,YYYY-MM-DDHH24-2004-02-0715:44:48下午星期六,說明：該語句中的第一個(gè)參數(shù)表示要轉(zhuǎn)換的日期，第二個(gè)參數(shù)是格式字符串，表示轉(zhuǎn)換后的格式，結(jié)果類型為字符串?！癥YYY”為4位的年份，“MM”為兩位的月份，“DD”為兩位的日期，“HH24”表示顯示24小時(shí)制的小時(shí),“MI”表示顯示分鐘，“SS”表示顯示秒，“AM”表示顯示上午或下午(本例中為下午)，“DY”表示顯示星期?！?”、“:”和空格原樣顯示，用于分割日期和時(shí)間。轉(zhuǎn)換出來的系統(tǒng)時(shí)間為：2004年2月7日(星期六)下午15點(diǎn)44分48秒。還可以按其他的格式顯示。以下查詢中插入中文的年月日，其中原樣顯示部分區(qū)別于外層的單引號(hào)，需要用雙引號(hào)引起。,【訓(xùn)練4】將日期顯示轉(zhuǎn)換成中文的年月日。輸入并執(zhí)行查詢：SELECTTO_CHAR(sysdate,YYYY年MM月DD日)FROMdual;執(zhí)行結(jié)果為：TO_CHAR(SYSDAT-2003年11月18日說明：雙引號(hào)中的中文字“年”、“月”、“日”原樣顯示，單引號(hào)為字符串的界定標(biāo)記，區(qū)別于雙引號(hào)，不能混淆。,【訓(xùn)練5】將雇傭日期轉(zhuǎn)換成字符串并按新格式顯示。輸入并執(zhí)行查詢：SELECTename,to_char(hiredate,DDMonthYYYY)HIREDATEFROMemp;執(zhí)行結(jié)果為：ENAMEHIREDATE-SMITH1712月1980ALLEN202月1981,說明：Month表示月份的特殊格式，如“12月”。年度用4位顯示。對(duì)于數(shù)字型的日期格式，可以用數(shù)字或全拼格式顯示，即在格式字符后面添加TH或SP。TH代表序列，SP代表全拼。,【訓(xùn)練6】以全拼和序列顯示時(shí)間。執(zhí)行以下查詢：SELECTSYSDATE,to_char(SYSDATE,yyyysp),to_char(SYSDATE,mmspth),to_char(SYSDATE,ddth)FROMdual;執(zhí)行結(jié)果為：SYSDATETO_CHAR(SYSDATE,YYYYSP)TO_CHAR(TO_C-,07-2月-04twothousandfoursecond07th說明：“twothousandfour”為全拼表示的2004年；“second”為全拼序列表示的2月；“07th”為用序列表示的7號(hào)。在格式字符中，前兩個(gè)字符代表顯示結(jié)果的大小寫。如果格式中的前兩個(gè)字符是大寫，則輸出結(jié)果的全拼也為大寫。如果格式中的前兩個(gè)字符是小寫，則輸出結(jié)果的全拼也為小寫。如果格式中的前兩個(gè)字符的第一個(gè)字符是大寫，第二個(gè)字符是小寫，則輸出結(jié)果的全拼也為大寫開頭，后面為字符小寫。,【訓(xùn)練7】時(shí)間顯示的大小寫。步驟1：執(zhí)行以下查詢：SELECTSYSDATE,to_char(SYSDATE,yyyysp)FROMdual;結(jié)果為：SYSDATETO_CHAR(SYSDATE,YYYYSP)-07-2月-04twothousandfour步驟2：執(zhí)行以下查詢：SELECTto_char(SYSDATE,Yyyysp)FROMdual;結(jié)果為：SYSDATETO_CHAR(SYSDATE,YYYYSP)-TwoThousandFour,步驟3：執(zhí)行以下查詢：SELECTSYSDATE,to_char(SYSDATE,YYyysp)FROMdual;結(jié)果為：SYSDATETO_CHAR(SYSDATE,YYYYSP)-TWOTHOUSANDFOUR說明：步驟1輸出全拼小寫的年度，步驟2輸出全拼的以大寫開頭的年度，步驟3輸出全拼大寫的年度?！揪毩?xí)1】顯示2008年的8月8日為星期幾。,3數(shù)字類型轉(zhuǎn)換將數(shù)字型轉(zhuǎn)換成字符串時(shí)，也可以按新的格式顯示。格式字符含義如表2-10所示。,表2-10數(shù)值轉(zhuǎn)換符,【訓(xùn)練8】將數(shù)值轉(zhuǎn)換成字符串并按新格式顯示。執(zhí)行以下查詢：SELECTTO_CHAR(123.45,0000.00),TO_CHAR(12345,L9.9EEEE)FROMdual;結(jié)果為：TO_CHAR(TO_CHAR(12345,L9.9-0123.45RMB1.2E+04說明：格式字符串中“0”表示一位數(shù)字，轉(zhuǎn)換結(jié)果中相應(yīng)的位置上沒有數(shù)字則添加0。“.”表示在相應(yīng)的位置上顯示小數(shù)點(diǎn)?！癓”將以本地貨幣符號(hào)顯示于數(shù)字前，在本例中本地貨幣符號(hào)為“RMB”?！癊EEE”將顯示轉(zhuǎn)換為科學(xué)計(jì)數(shù)法。,【訓(xùn)練9】將數(shù)值轉(zhuǎn)換成字符串并按新格式顯示。執(zhí)行以下查詢：SELECTTO_CHAR(sal,$99,999)SALARYFROMempWHEREename=SCOTT;結(jié)果為：SALARY-$4,000說明：格式字符串中“$”表示轉(zhuǎn)換結(jié)果前面添加$?！?”表示一位數(shù)字，“99，999”表示結(jié)果可以顯示為5位的數(shù)字?！?”表示在相應(yīng)的位置上添加逗號(hào)。如果實(shí)際數(shù)值位數(shù)不足5位，則只顯示實(shí)際位數(shù)，如4000實(shí)際位數(shù)為4位，則只顯示4位。如果實(shí)際位數(shù)超過5位，則會(huì)填充為#號(hào)。,2.4.5其他函數(shù)Oracle還有一些函數(shù)，如decode和nvl，這些函數(shù)也很有用，歸納如表2-11所示。,表2-11其他常用函數(shù),1空值的轉(zhuǎn)換如果對(duì)空值NULL不能很好的處理，就會(huì)在查詢中出現(xiàn)一些問題。在一個(gè)空值上進(jìn)行算術(shù)運(yùn)算的結(jié)果都是NULL。最典型的例子是，在查詢雇員表時(shí)，將工資sal字段和津貼字段comm進(jìn)行相加，如果津貼為空，則相加結(jié)果也為空，這樣容易引起誤解。使用nvl函數(shù)，可以轉(zhuǎn)換NULL為實(shí)際值。該函數(shù)判斷字段的內(nèi)容，如果不為空，返回原值；為空，則返回給定的值。,如下3個(gè)函數(shù)，分別用新內(nèi)容代替字段的空值：nvl(comm,0)：用0代替空的Comm值。nvl(hiredate,01-1月-97)：用1997年1月1日代替空的雇傭日期。nvl(job,無)：用“無”代替空的職務(wù)。,【訓(xùn)練1】使用nvl函數(shù)轉(zhuǎn)換空值。執(zhí)行以下查詢：SELECTename,nvl(job,無),nvl(hiredate,01-1月-97),nvl(comm,0)FROMemp;結(jié)果為：ENAMENVL(JOB,NNVL(HIREDANVL(COMM,0)-SMITHCLERK17-12月-800ALLENSALESMAN20-2月-81300說明：本例中，空日期將顯示為“01-1月-97”，空職務(wù)顯示為“無”，空津貼將顯示為0。,2decode函數(shù)decode函數(shù)可以通過比較進(jìn)行內(nèi)容的轉(zhuǎn)換，完成的功能相當(dāng)于分支語句。該函數(shù)的第一個(gè)參數(shù)為要進(jìn)行轉(zhuǎn)換的表達(dá)式，以后的參數(shù)成對(duì)出現(xiàn)，最后一個(gè)參數(shù)可以單獨(dú)出現(xiàn)。如果第一個(gè)參數(shù)的值與第二個(gè)表達(dá)式的值相等，則返回第三個(gè)表達(dá)式的值；如果不等則繼續(xù)比較，如果它的值與第四個(gè)表達(dá)式的值相等，則返回第五個(gè)表達(dá)式的值，以此類推。在參數(shù)的最后位置上可以存在單獨(dú)的參數(shù)，如果以上比較過程沒有找到匹配值，則返回該參數(shù)的值，如果不存在該參數(shù)，則返回NULL。,【訓(xùn)練2】將職務(wù)轉(zhuǎn)換成中文顯示。執(zhí)行以下查詢：SELECTename,decode(job,MANAGER,經(jīng)理,CLERK,職員,SALESMAN,推銷員,ANALYST,系統(tǒng)分析員,未知)FROMemp;結(jié)果為：ENAMEDECODE(JOB-SMITH職員ALLEN推銷員WARD推銷員JONES經(jīng)理,MARTIN推銷員BLAKE經(jīng)理CLARK經(jīng)理SCOTT系統(tǒng)分析員KING未知TURNER推銷員ADAMS職員JAMES職員FORD系統(tǒng)分析員MILLER職員已選擇14行。,說明：在以上訓(xùn)練中，如果job字段的內(nèi)容為“MANAGER”則返回“經(jīng)理”，如果是“CLERK”則返回“職員”，以此類推。如果不是“MANAGER”、“CLERK”、“SALESMAN”和“ANALYST”之一，則返回“未知”，如KING的職務(wù)“PRESIDENT”不在上述范圍，返回“未知”。【練習(xí)1】對(duì)部門表的部門名稱和城市名進(jìn)行轉(zhuǎn)換。,3userenv函數(shù)函數(shù)userenv返回用戶環(huán)境信息字符串，該函數(shù)只有一個(gè)字符串類型的參數(shù)，參數(shù)的內(nèi)容為如下之一的字符串，可以不區(qū)分大小寫：*ISDBA：判斷會(huì)話用戶的角色是否為SYSDBA，是則返回TRUE。*INSTANCE：返回會(huì)話連接的INSTANCE標(biāo)識(shí)符。*LANGUAGE：返回語言、地區(qū)、數(shù)據(jù)庫字符集信息。*LANG：返回會(huì)話語言的ISO簡稱。*TERMINAL：返回正在會(huì)話的終端或計(jì)算機(jī)的標(biāo)識(shí)符。,【訓(xùn)練3】返回用戶終端或系統(tǒng)標(biāo)識(shí)信息。執(zhí)行以下查詢：SELECTuserenv(TERMINAL)FROMdual;結(jié)果為：ORASERVER說明：根據(jù)用戶使用的機(jī)器不同返回的信息不同，在本例中機(jī)器標(biāo)識(shí)符ORASERVER為主機(jī)的名稱。,【訓(xùn)練4】返回語言、地區(qū)、數(shù)據(jù)庫字符集信息。執(zhí)行以下查詢：SELECTuserenv(LANGUAGE)FROMdual;結(jié)果為：SIMPLIFIEDCHINESE_CHINA.ZHS16GBK說明：顯示當(dāng)前用戶的語言為簡體中文(SIMPLIFIEDCHINESE)，地區(qū)為中國(CHINA)，字符集為ZHS16GBK?！揪毩?xí)2】判斷用戶的角色是否為SYSDBA。,4最大、最小值函數(shù)greatest返回參數(shù)列表中的最大值，least返回參數(shù)列表中的最小值。這兩個(gè)函數(shù)的參數(shù)是一個(gè)表達(dá)式列表，按表達(dá)式列表中的第一個(gè)表達(dá)式的類型對(duì)求值后的表達(dá)式求得最大或最小值。對(duì)字符的比較按ASCII碼的順序進(jìn)行。如果表達(dá)式中有NULL，則返回NULL。,【訓(xùn)練5】比較字符串的大小，返回最大值。執(zhí)行以下查詢：SELECTgreatest(ABC,ABD,abc,abd)FROMdual;執(zhí)行結(jié)果為：GRE-abd說明：在上述四個(gè)字符串中，大小關(guān)系為abdabcABDABC。在ASCII碼表中，排在后邊的字符大，小寫字母排在大寫字母之后。字符串的比較原則是，先比較第一位，如果相同，則繼續(xù)比較第二位，依此類推，直到出現(xiàn)大小關(guān)系。,2.5高級(jí)查詢,2.5.1多表聯(lián)合查詢通過連接可以建立多表查詢，多表查詢的數(shù)據(jù)可以來自多個(gè)表，但是表之間必須有適當(dāng)?shù)倪B接條件。為了從多張表中查詢，必須識(shí)別連接多張表的公共列。一般是在WHERE子句中用比較運(yùn)算符指明連接的條件。,忘記說明表的連接條件是常見的一種錯(cuò)誤，這時(shí)查詢將會(huì)產(chǎn)生表連接的笛卡爾積(即一個(gè)表中的每條記錄與另一個(gè)表中的每條記錄作連接產(chǎn)生的結(jié)果)。一般N個(gè)表進(jìn)行連接，需要至少N-1個(gè)連接條件，才能夠正確連接。兩個(gè)表連接是最常見的情況，只需要說明一個(gè)連接條件。兩個(gè)以上的表也可以進(jìn)行連接，在這里不做專門介紹。,兩個(gè)表的連接有四種連接方式：*相等連接。*不等連接。*外連接。*自連接。1相等連接通過兩個(gè)表具有相同意義的列，可以建立相等連接條件。使用相等連接進(jìn)行兩個(gè)表的查詢時(shí)，只有連接列上在兩個(gè)表中都出現(xiàn)且值相等的行才會(huì)出現(xiàn)在查詢結(jié)果中。,在下面的練習(xí)中，將顯示雇員姓名、所在的部門編號(hào)和名稱，在雇員表emp中是沒有雇員的部門名稱信息的，只有雇員所在部門的編號(hào)，部門的信息在另外的部門表dept中，兩個(gè)表具有相同的部門編號(hào)列deptno，可以通過該列建立相等連接。,【訓(xùn)練1】顯示雇員的名稱和所在的部門的編號(hào)和名稱。執(zhí)行以下查詢：SELECTemp.ename,emp.deptno,dept.dnameFROMemp,deptWHEREemp.deptno=dept.deptno;執(zhí)行結(jié)果如下：ENAMEDEPTNODNAME-SMITH20RESEARCHALLEN30SALES,說明：相等連接語句的格式要求是，在FROM從句中依次列出兩個(gè)表的名稱，在表的每個(gè)列前需要添加表名，用“.”分隔，表示列屬于不同的表。在WHERE條件中要指明進(jìn)行相等連接的列。以上訓(xùn)練中，不在兩個(gè)表中同時(shí)出現(xiàn)的列，前面的表名前綴可以省略。所以以上例子可以簡化為如下的表示：SELECTename,emp.deptno,dnameFROMemp,deptWHEREemp.deptno=dept.deptno;,【練習(xí)1】省略表前綴，察看執(zhí)行結(jié)果?！揪毩?xí)2】執(zhí)行以下查詢(省略表連接條件)并察看執(zhí)行結(jié)果中共有多少記錄產(chǎn)生。SELECTename,emp.deptno,dnameFROMemp,dept如果表名很長，可以為表起一個(gè)別名，進(jìn)行簡化，別名跟在表名之后，用空格分隔。【訓(xùn)練2】使用表別名。執(zhí)行以下查詢：SELECTename,e.deptno,dnameFROMempe,deptdWHEREe.deptno=d.deptno;執(zhí)行結(jié)果同上。,說明：emp表的別名為e，dept表的別名為d。相等連接還可以附加其他的限定條件?！居?xùn)練3】顯示工資大于3000的雇員的名稱、工資和所在的部門名稱。執(zhí)行以下查詢：SELECTename,sal,dnameFROMemp,deptWHEREemp.deptno=dept.deptnoANDsal3000;顯示結(jié)果為：ENAMESALDNAME-KING5000ACCOUNTING說明：只顯示工資大于3000的雇員的名稱、工資和部門名稱。在相等連接的條件下增加了工資大于3000的條件。增加的條件用AND連接。,2外連接在以上的例子中，相等連接有一個(gè)問題：如果某個(gè)雇員的部門還沒有填寫，即保留為空，那么該雇員在查詢中就不會(huì)出現(xiàn)；或者某個(gè)部門還沒有雇員，該部門在查詢中也不會(huì)出現(xiàn)。為了解決這個(gè)問題可以用外連，即除了顯示滿足相等連接條件的記錄外，還顯示那些不滿足連接條件的行，不滿足連接條件的行將顯示在最后。外連操作符為(+)，它可以出現(xiàn)在相等連接條件的左側(cè)或右側(cè)。出現(xiàn)在左側(cè)或右側(cè)的含義不同，這里用如下的例子予以說明。,【訓(xùn)練4】使用外連顯示不滿足相等條件的記錄。步驟1：顯示雇員的名稱、工資和所在的部門名稱及沒有任何雇員的部門。執(zhí)行以下查詢：SELECTename,sal,dnameFROMemp,deptWHEREemp.deptno(+)=dept.deptno;執(zhí)行結(jié)果為：ENAMESALDNAME-,CLARK2450ACCOUNTINGKING5000ACCOUNTINGMILLER1300ACCOUNTINGSMITH800RESEARCHADAMS1100RESEARCHFORD3000RESEARCHSCOTT3000RESEARCHJONES2975RESEARCHALLEN1600SALES,BLAKE2850SALESMARTIN1250SALESJAMES950SALESTURNER1500SALESWARD1250SALESOPERATIONS,步驟2：顯示雇員的名稱、工資和所在的部門名稱及沒有屬于任何部門的雇員。執(zhí)行以下查詢：SELECTename,sal,dnameFROMemp,deptWHEREemp.deptno=dept.deptno(+);結(jié)果從略，請(qǐng)自行執(zhí)行觀察結(jié)果。說明：部門OPERATION沒有任何雇員。查詢結(jié)果通過外連顯示出該部門。,3不等連接還可以進(jìn)行不等的連接。以下是一個(gè)訓(xùn)練實(shí)例，其中用到的salgrade表的結(jié)構(gòu)如下：DESCsalgrade名稱是否為空類型-GRADENUMBERLOSALNUMBERHISALNUMBERGrade表示工資等級(jí)，losal和hisal分別表示某等級(jí)工資的下限和上限。表的內(nèi)容為：SELECT*FROMsalgrade;,GRADELOSALHISAL-17001200212011400314012000420013000530019999,【訓(xùn)練5】顯示雇員名稱，工資和所屬工資等級(jí)。執(zhí)行以下查詢：SELECTe.ename,e.sal,s.gradeFROMempe,salgradesWHEREe.salBETWEENs.losalANDs.hisal；執(zhí)行結(jié)果為：ENAMESALGRADE-,SMITH8001ADAMS11001JAMES9501WARD12502MARTIN12502MILLER13002ALLEN16003TURNER15003,JONES29754BLAKE28504CLARK24504SCOTT30004FORD30004KING50005說明：通過將雇員工資與不同的工資上下限范圍相比較，取得工資的等級(jí)，并在查詢結(jié)果中顯示出雇員的工資等級(jí)。,4自連接最后是一個(gè)自連接的訓(xùn)練實(shí)例，自連接就是一個(gè)表，同本身進(jìn)行連接。對(duì)于自連接可以想像存在兩個(gè)相同的表(表和表的副本)，可以通過不同的別名區(qū)別兩個(gè)相同的表?！居?xùn)練6】顯示雇員名稱和雇員的經(jīng)理名稱。執(zhí)行以下查詢：SELECTworker.ename|的經(jīng)理是|manager.enameAS雇員經(jīng)理FROMempworker,empmanagerWHEREworker.mgr=manager.empno;執(zhí)行結(jié)果為：雇員經(jīng)理,-SMITH的經(jīng)理是FORDALLEN的經(jīng)理是BLAKEWARD的經(jīng)理是BLAKE說明：為EMP表分別起了兩個(gè)別名worker和manager，可以想像，第一個(gè)表是雇員表，第二個(gè)表是經(jīng)理表，因?yàn)榻?jīng)理也是雇員。然后通過worker表的mgr(經(jīng)理編號(hào))字段同manager表的empno(雇員編號(hào))字段建立連接，這樣就可以顯示雇員的經(jīng)理名稱了。注意：經(jīng)理編號(hào)mgr是雇員編號(hào)empno之一，所以經(jīng)理編號(hào)可以同雇員編號(hào)建立連接。,2.5.2統(tǒng)計(jì)查詢通常需要對(duì)數(shù)據(jù)進(jìn)行統(tǒng)計(jì)，匯總出數(shù)據(jù)庫的統(tǒng)計(jì)信息。比如，我們可能想了解公司的總?cè)藬?shù)和總工資額，或各個(gè)部門的人數(shù)和工資額，這個(gè)功能可以由統(tǒng)計(jì)查詢完成。Oracle提供了一些函數(shù)來完成統(tǒng)計(jì)工作，這些函數(shù)稱為組函數(shù)，組函數(shù)不同于前面介紹和使用的函數(shù)(單行函數(shù))。組函數(shù)可以對(duì)分組的數(shù)據(jù)進(jìn)行求和、求平均值等運(yùn)算。組函數(shù)只能應(yīng)用于SELECT子句、HAVING子句或ORDERBY子句中。組函數(shù)也可以稱為統(tǒng)計(jì)函數(shù)。,查詢公司的總?cè)藬?shù)需要對(duì)整個(gè)表應(yīng)用組函數(shù)；查詢各個(gè)部門的人數(shù)，需要對(duì)數(shù)據(jù)進(jìn)行分組，然后應(yīng)用組函數(shù)進(jìn)行運(yùn)算。常用的組函數(shù)如表2-12所示。,表2-12常用的組函數(shù),分組函數(shù)中SUM和AVG只應(yīng)用于數(shù)值型的列，MAX、MIN和COUNT可以應(yīng)用于字符、數(shù)值和日期類型的列。組函數(shù)忽略列的空值。使用GROUPBY從句可以對(duì)數(shù)據(jù)進(jìn)行分組。所謂分組，就是按照列的相同內(nèi)容，將記錄劃分成組，對(duì)組可以應(yīng)用組函數(shù)。如果不使用分組，將對(duì)整個(gè)表或滿足條件的記錄應(yīng)用組函數(shù)。在組函數(shù)中可使用DISTINCT或ALL關(guān)鍵字。ALL表示對(duì)所有非NULL值(可重復(fù))進(jìn)行運(yùn)算(COUNT除外)。DISTINCT表示對(duì)每一個(gè)非NULL值，如果存在重復(fù)值，則組函數(shù)只運(yùn)算一次。如果不指明上述關(guān)鍵字，默認(rèn)為ALL。,1統(tǒng)計(jì)查詢【訓(xùn)練1】求雇員總?cè)藬?shù)。執(zhí)行以下查詢：SELECTCOUNT(*)FROMemp;返回結(jié)果為：COUNT(*)-14說明：該實(shí)例中，因?yàn)闆]有WHERE條件，所以對(duì)emp表的全部記錄應(yīng)用組函數(shù)。使用組函數(shù)COUNT統(tǒng)計(jì)記錄個(gè)數(shù)，即雇員人數(shù)，返回結(jié)果為14，代表有14個(gè)記錄。注意：*代表返回所有行數(shù)，否則返回非NULL行數(shù)。,【訓(xùn)練2】求有傭金的雇員人數(shù)。執(zhí)行以下查詢：SELE