《OracleSQL實用基礎教程.ppt》由會員分享，可在線閱讀，更多相關《OracleSQL實用基礎教程.ppt（144頁珍藏版）》請在裝配圖網(wǎng)上搜索。

王忠海2020/4/25,OracleSQL實用基礎教程,SQL概述,歷史SQL:StruceuredQueryLanguage1974年，由Boyce和Chamber提出1975-1979年，在SystemR上實現(xiàn)，由IBM的SanJose研究室研制，稱為Sequel,SQL概述,標準化有關組織ANSI(AmericanNaturalStandardInstitute)ISO(InternationalOrganizationforStandardization)有關標準SQL-86“數(shù)據(jù)庫語言SQL”SQL-89“具有完整性增強的數(shù)據(jù)庫語言SQL”，增加了對完整性約束的支持SQL-92“數(shù)據(jù)庫語言SQL”，是SQL-89的超集，增加了許多新特性，如新的數(shù)據(jù)類型，更豐富的數(shù)據(jù)操作，更強的完整性、安全性支持等。SQL-99正在討論中的新的標準，將增加對面向對象模型的支持,SQL概述,特點一體化集DDL，DML，DCL于一體單一的結構----關系，帶來了數(shù)據(jù)操作符的統(tǒng)一面向集合的操作方式一次一集合高度非過程化用戶只需提出“做什么”，無須告訴“怎么做”，不必了解存取路徑兩種使用方式，統(tǒng)一的語法結構SQL既是自含式語言（用戶使用），又是嵌入式語言（程序員使用）語言簡潔，易學易用,SQL概述,1SQL命令基礎,準備工作,用SQLPLUS來學習SQL連接到SQLPLUS，創(chuàng)建學習用的用戶和數(shù)據(jù)SCOTT用戶在ORACLE805中默認已經創(chuàng)建，在8i和9i中需要手動運行\(zhòng)rdbms\admin\scott.sql。是ORACLE安裝的主目錄，在SQLPLUS中可以用?來代替。例如：SQL>connect/assysdbaConnected.SQL>@?\rdbms\admin\scott.sqlSQL>connectscott/tigerConnected.,基本的SELECT命令,SELECT命令用于從數(shù)據(jù)庫中獲得想要的信息。語法：SELECT{*,column[alias],……}FROMtable;一個最簡單的查詢語句至少要包括SELECT子句和FROM子句：SELECT后面指定要選擇的列FROM后面指定從哪些表或視圖中獲取數(shù)據(jù),SQL語句基本規(guī)則,SQL命令是大小寫不敏感SQL命令可寫成一行或多行一個關鍵字不能跨多行或縮寫子句通常位于獨立行，以便編輯，并易讀空格和縮進使程序易讀關鍵字大寫，其他小寫,使用SELECT*可顯示所有的列,SQL>SELECT*FROMdept;DEPTNODNAMELOC---------------------------------------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTON,選擇指定的列,我們可以在SELECT后面指定要選擇的列。,SQL>SELECTdeptno,locFROMdept;DEPTNOLOC-------------------------------------10NEWYORK20DALLAS30CHICAGO40BOSTON,在SQL*PLUS中查看表具有哪些列,在SQLPLUS中，用DESCTABLENAME命令可以查看表具有的列以及類型等,SQL>descdept名稱是否為空?類型--------------------------------------------------------------DEPTNONUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13),從數(shù)據(jù)字典中獲取表的列信息,SQL>SELECTTABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLEFROMUSER_TAB_COLUMNSWHERETABLE_NAME=DEPT;TABLE_NAMECOLUMN_NAMEDATA_TYPEDATA_LENGTHNULLABLE--------------------------------------------------------------DEPTDEPTNONUMBER22YDEPTDNAMEVARCHAR214YDEPTLOCVARCHAR213Y,使用算術運算,可以對數(shù)字類型的字段進行算術運算。運算的先后順序是先乘除，后加減，括號優(yōu)先。上面例子中，計算的是一年的報酬，月工資乘以12個月，再加上100,SQL>SELECTename,sal,12*sal+100FROMemp;ENAMESAL12*SAL+100-----------------------------------------------------------KING500060100BLAKE285034300CLERK245029500JONES297535800……14rowsselected。,關于空值(NULL),空值是指不可用，不知道，不適用的值空值不等于零也不等于空格對空值進行的任何運算仍然為空值,SQL>SELECTenameNAME,12*sal＋commFROMempWHEREename＝‘KING’;NAME12*SAL+COMM--------------------------------------------KING,定義列的別名,當顯示查詢結果時，SQL*PLUS通常使列名作為列頭。在很多情況下，列名并非是此列的清晰描述。因此我們就可使用列的別名作為列頭，。缺省情況下，列的別名是大寫的。如果區(qū)別大小寫，可加雙引號，如有特殊字符如‘$’、‘#’也必須使用雙引號將其括起來。上面的例子中的AS可以省略,SQL>SELECTenameASname,salsalaryFROMemp;NAMESALARY----------------------------------------------……,SQL>SELECTename"Name",sal*12"AnnualSalary"FROMemp;NAMEAnnualSalary----------------------------------------------……,列連接操作,使用雙豎條”||”操作符，可將列和運算表達式常量連起來顯示，形成一個輸出顯示,SQL>SELECTENAME||ssalaryis||sal"EmployeesSalary"fromemp;EmployeesSalary-----------------------------------------------------------------------SMITHssalaryis800ALLENssalaryis1600WARDssalaryis1250JONESssalaryis2975………………………..已選擇14行。,去除重復記錄,默認情況下，顯示所有行，包括重復記錄。如果想去掉重復記錄，可以在DISTINCT關鍵字。如果在DISTINCT后面指定了多個列，則DISTINCT將對所有被選擇的列有效，其結果是不同的列的組合。,SQL>SELECTdeptnoFROMemp;DEPTNO------------------103010……14rowsselected。,SQL>SELECTDISTINCTdeptnoFROMemp;DEPTNO------------------102030,SQL*PLUS程序介紹,SQL*PLUS是是Oracle自帶的與Oracle交互的一個工具。你可在SQL*PLUS中做以下操作：執(zhí)行SQL命令來修改、查詢、增加、刪除數(shù)據(jù)庫中的數(shù)據(jù)格式化、計算、存儲、數(shù)據(jù)于一定格式的報告中產生用于存儲SQL命令的腳本，以便以后執(zhí)行SQL*PLUS專用命令可被分為如下類別：環(huán)境：影響通常的SQL命令格式化：格式化查詢結果文件處理：存儲、調用、運行腳本文件編輯：修改SQL緩存中的SQL命令顯示列的定義,登陸到SQL*PLUS,在命令行提示符下輸入SQLPLUS/NOLOG然后在SQL>提示符下輸入CONNECTUSERNAME/PASSWORD@DATABASE,C:\DocumentsandSettings\Administrator>SQLPLUS/NOLOGSQL*Plus:Release9.2.0.4.0-Productionon星期四5月2409:51:422007Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.SQL>connectsystem/manager@tlgaxz已連接。,SQL*PLUS的編輯命令,SQL*PLUS的命令每次只能鍵入一行，且不能存于SQL緩存中A[PPEND]text將text加到當前行的后面C[HANGE]/old/new將當前行的old改為newC[HANGE]/text/從當前行中刪除textC[LEAR]BUFF[ER]從緩存中刪除所有的行DEL刪除當前行L[IST]：列出SQL緩存中的所有行L[IST]n：列出一行(由n指出列出的行)R[UN]：顯示并運行SQL緩存中的SQL語句,SQL*PLUS的編輯命令(續(xù)),GETfilename[.ext]：把文件的內容寫到SQL緩存@filename[.ext]：運行文件ED[IT]：調用編輯器編輯當前緩存中的內容ED[IT]filename.[ext]：調用編輯器編輯存的文件SPOOL[filename[.ext]：將查詢結果存于文件中SPOOLOFF結束結果內容輸出EXIT：退出SQL*PLUS,2限定和排序數(shù)據(jù),目的,限制某一查詢所取記錄排序查詢結果,使用選擇限定記錄,,在上面的例子中，假定你想要顯示部門10的所有員工，這種方式是基于WHERE子句的SQL命令。,使用where子句限定返回的記錄,WHERE子句在FROM子句后面Condition:由列名表達式，常量和比較操作符組成。,SELECT〔DISTINCT〕{*,column[alias],……}FROMtable〔WHEREcondition(s)];,使用WHERE語句,上面例子中返回job=‘CLERK’的所有員工的name,job和deptno注意：字符的大小寫是敏感的。,SQL>SELECTename,job,deptnoFROMempWHEREjob=CLERK;ENAMEJOBDEPTNO-----------------------------------------------------------KINGCLERK30BLAKECLERK20TURNERCLERK10……14rowsselected。,字符串和日期,在WHERE子句中的字符串和日期必須用單引號括起來，所有的字符是大小寫敏感的。Oracle存儲日期是以內定的格式存放，它們代表世紀、年、月、日、小時、分鐘和秒，缺省顯示的日期格式是DD-MON-YY，也可能是其他格式。為了避免日期字段查詢條件不同格式下可能造成的錯誤，通常用TO_DATE函數(shù)來進行轉換。,日期類型查詢條件舉例,上面第二個例子使用了TO_DATE函數(shù)，就不再受日期格式的影響了。否則同樣的查詢，同樣的表數(shù)據(jù)，不同的系統(tǒng)日期格式會導致結果不正確。,SQL>SELECTENAME,HIREDATEFROMEMPWHEREHIREDATE=03-DEC-81;ENAMEHIREDATE-------------------JAMES03-DEC-81FORD03-DEC-81SQL>SELECTENAME,HIREDATEFROMEMPWHEREHIREDATE=TO_DATE(19811203,YYYYMMDD);ENAMEHIREDATE-------------------JAMES03-DEC-81FORD03-DEC-81,常用比較運算符,使用BETWEEN,SQL>SELECTename,salFROMempWHEREsalBETWEEN1000AND1500;ENAMESAL-------------------------KING1250BLAKE1500CLERK1250JONES1300,注意：BETWEEN后面要先寫低值，后寫高值,使用IN運算符,SQL>SELECTempno,ename,sal,mgrFROMempWHEREmgrIN(7902,7566,7788);EMPNOENAMESALMGR-------------------------―――――－――――――7902KING125075667369BLAKE150079027788CLARK125075667876JONES13007788,使用LIKE運算符,使用LIKE運算符執(zhí)行通配查詢查詢條件可包含文字字符或數(shù)字%可表示零或多個字符_可表示一個字符,SQL>SELECTenameFROMempWHEREenameLIKES%;ENAME----------SMITHSCOTT,用LIKE和ESCAPE來查找包含特殊字符的數(shù)據(jù),例如如果想查找表EMP中ENAME包含下劃線_的數(shù)據(jù)，就需要用到ESCAPE選項，否則查詢結果不準確。ESCAPE后面單引號內只能有一個字符，表示前面的LIKE條件中這個字符后面的第一個字符當作普通字符處理,SQL>SELECTEMPNO,ENAMEFROMEMPWHEREENAMELIKE%\_%ESCAPE\;EMPNOENAME--------------------9999FOR_TEST,使用ISNULL,查詢包含空值的記錄,SQL>SELECTename,mgrFROMempWHEREmgrISNULL;ENAMEMGR―――――――――――KING,邏輯運算符,優(yōu)先級次序：1所有的比較運算2NOT3AND4OR括號將跨越所有優(yōu)先級,使用AND運算符,AND需要條件都滿足,SQL>SELECTempno,ename,job,salFROMempWHEREsal>=1100ANDjob=CLERK；EMPNOENAMEJOBSAL-------------------------―――――－――――――7369BLAKECLERK13007788CLARKCLERK1250,使用OR運算符,OR需要滿足條件之一即可,SQL>SELECTempno,ename,job,salFROMempWHEREsal>=1100ORjob=CLERK；EMPNOENAMEJOBSAL-------------------------―――――－――――――7369BLAKECLERK13007788CLARKCLERK12507839KINGPERSIDENT50007645MARTINMANAGER1050,使用NOT運算符,,SQL>SELECTename,jobFROMempWHEREjobNOTIN(CLERK,MANAGER,ANALYST);ENAMEJOB--------------------------------KINGPERSIDENTMARTINSALESMANWARDSALESMAN,ORDERBY語句,在缺省情況下，查詢返回的結果是沒被排序的。使用ORDERBY子可將記錄排序。ORDERBY子句放在最后。ASC表示升序排序，DESC表示降序排序，缺省為ASC,SELECTexprFROMtable[WHEREcondition]ORDERBY{column,expr}[ASC|DESC],降序排列,SQL>selectename,hiredatefromemporderbyhiredatedesc;ENAMEHIREDATE-------------------FOR_TESTADAMS12-JAN-83SCOTT09-DEC-82MILLER23-JAN-82JAMES03-DEC-81SMITH17-DEC-80……15rowsselected.,使用列別名排序,SQL>selectempno,ename,sal*12annualfromemporderbyannual;EMPNOENAMEANNUAL------------------------------7369SMITH96007900JAMES114007876ADAMS132007521WARD150007654MARTIN150007934MILLER156007844TURNER18000……15rowsselected.,按照多個列排序,SQL>SELECTename,sal,deptnoFROMEMPORDERBYdeptno,salDESC;ENAMESALDEPTNO------------------------------KING500010CLARK245010MILLER130010SCOTT300020FORD300020JONES297520ADAMS110020......15rowsselected.,3單行函數(shù),SQL函數(shù),SQL函數(shù)有兩種不同的SQL函數(shù)單行函數(shù)多行函數(shù)單行函數(shù)這些函數(shù)僅作用于單行記錄，并對每行記錄返回一個值，有許多不同類型的單行函數(shù)，常用的類型有：字符函數(shù)數(shù)字函數(shù)日期函數(shù)轉換函數(shù)多行函數(shù)這些函數(shù)作用于記錄組，每組記錄返回一個結果。,單行函數(shù),單行函數(shù)單行函數(shù)操作數(shù)據(jù)項，它們接收一個或多個參數(shù)，并對查詢出的每一條記錄返回一個值。參數(shù)可以是：用戶提供的常量一個列名一個表達式單行函數(shù)的特性它們作用于查詢的每一條記錄每條記錄返回一個結果它們可返回一個不同于它所參照的數(shù)據(jù)類型它們可嵌入到SELECT，WHERE和ORDERBY子句。,字符串函數(shù),字符函數(shù)被分為：大小寫轉換函數(shù)字符處理函數(shù)LOWER(column|expression):將字符轉換為小寫UPPER(column|expression):將字符轉換不大寫INITCAP(column|expression):將每一個單詞的第一個字母大寫其它小寫CONCAT(column｜expression):返回第一個串接上第二個串,它的作用和||運算是相同的SUBSTR(column｜expression,ｍ［,ｎ］):返回從字母m開始,有n個字符長的字符串。LENGTH(column｜expression):返回字符串長度INSTR(column1expression.m[n]):返回字符串中字符的位置LPAD(column1cxpression,n,string):在字符串前填補字符,使其長度達到n。RPAD(column1cxpression,n,‘string’):在字符串后填補字符,使其長度達到n。,字符串函數(shù)舉例,上面的例子將ename列的首字母大寫，其余字母小寫,SQL>SELECTINITCAP(ename)FROMemp;INITCAP(EN----------SmithAllenWard……,字符串處理函數(shù)舉例,,SQL>SELECTENAME,SUBSTR(ENAME,1,3)"SUBSTR",RPAD(ENAME,10,*)"RPAD",LENGTH(ENAME)"LENGTH"FROMEMP;ENAMESUBSTRRPADLENGTH-------------------------------------------------------SMITHSMISMITH*****5ALLENALLALLEN*****5WARDWARWARD******4JONESJONJONES*****5MARTINMARMARTIN****6……,數(shù)字函數(shù),數(shù)字函數(shù)接收數(shù)字輸入返回數(shù)字值ROUND(column|expression,n):返回舍入到小數(shù)點右邊n位的值TRUNC(column|expression,n):返回截斷到n位的值MOD(m,n):返回m和n相除后的余數(shù),使用ROUND函數(shù),上面的例子分別顯示45.923到小數(shù)點后兩位，個位，十位,SQL>SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMDUAL;ROUND(45.923,2)ROUND(45.923,0)ROUND(45.923,-1)----------------------------------------------45.924650,使用TRUNC函數(shù),顯示45.923到小數(shù)點后兩位，個位，十位,SQL>SELECTTRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1)FROMDUAL;TRUNC(45.923,2)TRUNC(45.923,0)TRUNC(45.923,-1)----------------------------------------------45.924540,使用MOD函數(shù),這個例子計算工資除以獎金后的余數(shù),SQL>SELECTename,sal,comm,MOD(sal,comm)FROMempWHEREjob=SALESMAN;ENAMESALCOMMMOD(SAL,COMM)-------------------------------------------ALLEN1600300100WARD1250500250MARTIN125014001250TURNER150001500,使用日期函數(shù),Oracle的日期函數(shù)Oracle使用內部的數(shù)字化格式存儲日期，它們代表世紀、年、月、日、小時、分鐘和秒.缺省顯示的日期格式為DD-MON-YY，有效的日期在公元前4712年1月1日到公元后9999年12月31日SYSDATESYSDATE是一個返回當前日期和時間的日期函數(shù)DUALDUAL是一個SYS用戶所擁有的表，所有的用戶都可以訪問。它包括一個列DUMMY和一條記錄值為X。例子：顯示當前的日期SQL>SELECTSYSDATEFROMSYS.DUAL;SYSDATE---------24-MAY-07,日期運算,日期+數(shù)字=日期加天數(shù)日期-數(shù)字=日期減天數(shù)日期-日期=數(shù)字兩日期間的天數(shù)日期+number/24=日期加小時注意：兩個日期類型字段不能相加,日期運算舉例,SQL>SELECTENAME,SYSDATE-HIREDATEFROMEMP;ENAMESYSDATE-HIREDATE--------------------------SMITH9654.54954ALLEN9589.54954WARD9587.54954JONES9548.54954MARTIN9369.54954……,常見的日期函數(shù),,日期函數(shù)使用舉例,SQL>SELECTSYSDATE,ADD_MONTHS(SYSDATE,12),LAST_DAY(SYSDATE),NEXT_DAY(SYSDATE,FRIDAY)FROMDUAL;SYSDATEADD_MONTHSLAST_DAY(SNEXT_DAY(S----------------------------------------2007/05/242008/05/242007/05/312007/05/25,轉換函數(shù),轉換函數(shù)用于數(shù)據(jù)類型之間的轉換。SQL盡可能地自動進行轉換，它會隱含地調用轉換函數(shù)。但是你無法對隱含調用中使用的格式指定符進行控制，并且這會使得你的代碼很難理解。因此使用顯式轉換函數(shù)而不依賴于隱式轉換是一個很好的程序設計風格。,轉換函數(shù),Oracle提供了3個轉換函數(shù)?TO_CHAR(number|date,[fmt]):將數(shù)字或日期按格式轉換成字符?TO_NUMBER(char):將字符串轉換成數(shù)字，此字符串必須是數(shù)字?TO_DATE(CHAR,[fmt])：將字符串按指定的格式轉換成日期,使用TO_CHAR操作日期函數(shù)舉例,上面的例子中：YYYY代表4位的年份，MM代表月份，HH24代表24小時，MI代表分鐘，SS代表秒。,SQL>SELECTSYSDATE,TO_CHAR(SYSDATE,YYYY/MM/DDHH24:MI:SS)DETAILDATEFROMDUAL;SYSDATEDETAILDATE---------------------------------------24-MAY-072007/05/2413:47:32,使用TO_DATE函數(shù)舉例,上面的例子，如果不用TO_DATE進行轉換，會怎么樣呢？,SQL>SELECTENAME,HIREDATEFROMEMPWHEREHIREDATE>=TO_DATE(19820101,YYYYMMDD);ENAMEHIREDATE-------------------SCOTT09-DEC-82ADAMS12-JAN-83MILLER23-JAN-82,SQL>SELECTENAME,HIREDATEFROMEMPWHEREHIREDATE>=19820101;SELECTENAME,HIREDATEFROMEMPWHEREHIREDATE>=19820101*ERRORatline1:ORA-01861:literaldoesnotmatchformatstring,因為19820101不是采用默認日期格式寫的，Oracle無法進行隱含轉換，導致報錯。因此可以看出，對于可能發(fā)生轉換的語句，應該顯式指定轉換,NVL函數(shù),將空值轉換為實際的值數(shù)據(jù)格式可以是日期，字符，數(shù)字數(shù)據(jù)類型必須匹配,NVL(comm,0)：如果comm為空，則轉換為0NVL(hiredate,01-JAN-97)：如果hiredate為空，則轉換為01-JAN-97NVL(job,‘NoJobYet)：如果job為空，則轉換為‘NoJobYet,使用NVL,SQL>SELECTename,comm,sal,sal+300,(sal*12)+NVL(comm,0)FROMemp;ENAMECOMMSALSAL+300(SAL*12)+NVL(COMM,0)------------------------------------------------------------SMITH80011009600ALLEN3001600190019500WARD5001250155015500JONES2975327535700MARTIN14001250155016400BLAKE2850315034200CLARK2450275029400SCOTT3000330036000……,4從多個表中選擇數(shù)據(jù),從多個表中獲取數(shù)據(jù),有時候你需要從多個表中獲得數(shù)據(jù)。在上面的例子中，報告顯示的數(shù)據(jù)取自兩個表。EMPNO存在于EMP表中，DEPTNO在EMP和DEPT表中都有，LOC存在于DEPT表中為了生成上面的報告，你需要將表EMP和DEPT連起來，從兩個中獲取數(shù)據(jù)。,定義連接,當從兩個以上的表中獲取數(shù)據(jù)時，就要使用連接條件。一個表中的記錄可以根據(jù)兩個表的相同列和另一個表的記錄相連接。兩表中的相同列一般是主鍵和外鍵列。為了能顯示兩個或多個表中的數(shù)據(jù)，在WHERE子句中需要設簡單的連接條件。語法如下：table.column指定取數(shù)據(jù)的表和它的列table.column1=table2.column2將表連接起來的條件當寫一個有連接的SELECT命令時，為了避免同樣的不同表具有同樣的列名，應該在列前加表的名字或者表的別名。當在表中有相同的列名時，并且這個列作為顯示內容或者查詢條件，必須在列名前加表名或表的別名作為前綴。如果要將n個表連起來，你必須指定n-1個連接條件。因此連接4個表需要有3個連接條件。如果你的表有組合主鍵，此規(guī)則可能不適用，此時多一條記錄需要多個列唯一標識。,笛卡爾結果,笛卡爾結果笛卡爾結果形成于：-連接條件被省略-連接條件無效-第一個表的所有記錄連接到第二個表的所有記錄一個笛卡爾結果趨于產生一個巨大的記錄數(shù)，通常沒有意義。為了避免笛卡爾結果我們要在WHERE子句中使用有效連接,笛卡爾結果舉例,SELECT*FROMEMP,DEPT;,連接類型,有兩種主要的連接條件等值連接非等值連接其它的連接方式包括多連接自連接定置運算符,什么是等值連接,就是兩個表連接的WHERE條件是一個表的列等于另外一個表的列。通常情況下，這種連接是主鍵和外鍵的連接。,使用等值連接獲取記錄舉例,因為DEPTNO列在EMP和DEPT中都存在，因此需要在這個列前面加上表名，否則Oracle認為有歧義，語句無法執(zhí)行,SQL>SELECTENAME,EMP.DEPTNO,DNAMEFROMEMP,DEPTWHEREEMP.DEPTNO=DEPT.DEPTNO;ENAMEDEPTNODNAME----------------------------------MILLER10ACCOUNTINGKING10ACCOUNTINGCLARK10ACCOUNTINGFORD20RESEARCHADAMS20RESEARCHSCOTT20RESEARCH……,額外的條件,除了連接條件，可能還有額外的查詢條件。例如，顯示員工King的員工號、各字、部門號和部門位置，這時在WHERE子句中需要設定一個額外的條件。,SQL>SELECTempno,ename,emp.deptno,LocFROMemp,deptWHEREemp.deptno=dept.deptnoANDINITCAP(ename)=King;EMPNOENAMEDEPTNOLOC-------------------------------------------7839KING10NEWYORK,使用表的別名,表的別名使用表名限定列名可能會很浪費時間，尤其是當表名特別長，這時你可以使用表的別名。使用表的別名會減少程序代碼，因此占用較少的內存。注意，表的別名是在FROM子句中指定的。表別名規(guī)則：表的別名最長為30個字符，但通常以短字符為佳表的別名最好有一定的含義表的別名只在當前的SELECT語句有效如果在FROM子句定義了表的別名，在SELECT子句中必須用它來替代表名。,SQL>SELECTempno,ename,e.deptno,LocFROMempe,deptdWHEREe.deptno=d.deptnoANDINITCAP(ename)=King;EMPNOENAMEDEPTNOLOC-------------------------------------------7839KING10NEWYORK,非等值連接,在EMP表和SALGRADE表中，沒有直接的對應列，它們之間的關系是EMP的SAL列的值在SALGRADE表的LOSAL和HISAL列之間，它們是不等值連接。,非等值連接舉例,SQL>SELECTe.sal,e.ename,s.gradeFROMempe,salgradesWHEREe.salBETWEENs.losalANDs.hisal;SALENAMEGRADE------------------------------5000KING53000SCOTT43000FORD42975JONES42850BLAKE42450CLARK41600ALLEN31500TURNER31300MILLER2......,外連接,使用外部連接，返回連接兩邊有一邊為NULL的記錄外連接運算符是加號(+)外連接運算符(+)可以加在左邊,也可以加在右邊，但不能兩邊同時加外連接有(+)的一邊表示這邊的值要么等于另外一邊，要么為NULL從9i開始，SQL支持ANSISQL，也就是支持LEFTOUTERJOIN、RIGHTOUTERJOIN和FULLOUTERJOIN,SQL>SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column(+)=table2.column;,SQL>SELECTtable.column,table.columnFROMtable1,table2WHEREtable1.column=table2.column(+);,外連接舉例1：,這個例子(+)在e.deptno這邊，意味著e.deptno可以是NULL,SQL>SELECTename,e.deptno"E.DEPTNO",d.deptno"D.DEPTNO",d.dnameFROMEMPE,DEPTDWHEREE.DEPTNO(+)=D.DEPTNO;ENAMEE.DEPTNOD.DEPTNODNAME--------------------------------------------SMITH2020RESEARCHALLEN3030SALES......FORD2020RESEARCHMILLER1010ACCOUNTING40OPERATIONS15rowsselected.,外連接舉例2,這個例子(+)在d.deptno這邊，意味著d.deptno可以是NULL,SQL>SELECTename,e.deptno"E.DEPTNO",d.deptno"D.DEPTNO",d.dnameFROMEMPE,DEPTDWHEREE.DEPTNO=D.DEPTNO(+);ENAMEE.DEPTNOD.DEPTNODNAME--------------------------------------------MILLER1010ACCOUNTINGKING1010ACCOUNTING......ALLEN3030SALESwzh90FOR_TEST9016rowsselected.,外連接舉例3,從9i開始，可以用ANSISQL語法來寫外連接，這樣也提供了一個以前的(+)不能實現(xiàn)的功能：全外連接,SQL>SELECTE.ENAME,E.DEPTNO"E.DEPTNO",D.DEPTNO"D.DEPTNO",D.DNAMEFROMEMPEFULLOUTERJOINDEPTDON(E.DEPTNO=D.DEPTNO);ENAMEE.DEPTNOD.DEPTNODNAME--------------------------------------------MILLER1010ACCOUNTINGKING1010ACCOUNTING......WARD3030SALESALLEN3030SALESwzh90FOR_TEST9040OPERATIONS17rowsselected.,表的自連接,有的時候，需要對表進行自連接。例如上圖所示，EMP表中的MGR列的代表員工的經理的員工編號，所以要想顯示出每個員工的經理就要對EMP進行自連接，用MGR=EMPNO。實際上，自連接只是等連接（等外連接）的一個特例，同一張表用不同的別名，區(qū)別成了不同的表。,表自連接舉例,上面這個例子相當一個等外連接。如果員工有經理，則顯示出員工為誰工作（打工者），否則顯示員工為自己干活（老板）,SQL>SELECTE.ENAME||worksfor||NVL(M.ENAME,himself)RELATIONSFROMEMPE,EMPMWHEREE.MGR=M.EMPNO(+);RELATIONS--------------------------------------------------------------------------FORDworksforJONES......JONESworksforKINGSMITHworksforFORDKINGworksforhimself14rowsselected.,5使用分組函數(shù),什么是分組函數(shù),和單行函數(shù)不同，分組函數(shù)作用于一組記錄，每一組返回一個結果。這些組可能是整個表，也可能是由GROUPBY子句將表分成的多個組。,主要的分組函數(shù),COUNT({*/[DISTINCE/ALL]expr})返回記錄數(shù)，這里expr賦非空值，*表示所有被選擇的記錄，包括重復記錄和空值。MAX([DISTNCT/ALL]expr)：表達式的最大值，忽略空值MIN([DISTNCT/ALL]expr)：表達式的最小值，忽略空值AVG([DISTNCT/ALL]expr)：平均值，忽略空值STDDEV[DISTINCT/ALL]X)：返回標準差，忽略空值SUM([DIXNTICT/ALL]n)：求和，忽略空值VARIANCE([DISTINCT/ALL]X)：返回統(tǒng)計方差這些分組函數(shù)中,COUNT是不計算NULL值的，其它函數(shù)忽略NULL值。,使用分組函數(shù)舉例1：,,SQL>SELECTSUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL)FROMEMP;SUM(SAL)MAX(SAL)MIN(SAL)AVG(SAL)----------------------------------------2902550008002073.21429,使用分組函數(shù)舉例2：,SQL>SELECTCOUNT(*)FROMEMP;COUNT(*)----------16SQL>SELECTCOUNT(EMPNO)FROMEMP;COUNT(EMPNO)------------16SQL>SELECTCOUNT(MGR)FROMEMP;COUNT(MGR)----------13,產生數(shù)據(jù)組：GROUPBY子句,GROUPBY子句使用GROUPBY子句將一個表分成許多小組，并對每一個小組返回一個計算值。Group_by_expression：指定按什么列分組規(guī)則：在SELECT子句中，如果使用分組函數(shù)，不能對GROUPBY子句中指定的列使用分組函數(shù)。使用WHERE子句，可預先排除某些記錄在GROUPBY子句中必須有表中的列在GROUPBY子句中不能使用列的別名缺省情況下在GROUPBY子句中的列以升序排，你可以使用orderby子句改變它。,SELECTcolumn,group_functionFROMtable[WHEREcondition][GROUPBYgroup_by_expression][ORDERBYcolumn];,使用GROUPBY舉例,注意，不是分組的列不能出現(xiàn)在SELECT后面。否則會提示：ORA-00979:notaGROUPBYexpression。組函數(shù)也不能出現(xiàn)在WHERE子句中，否則會提示ORA-00934:groupfunctionisnotallowedhere,SQL>SELECTDEPTNO,SUM(SAL),MAX(SAL),MIN(SAL),AVG(SAL)FROMEMPGROUPBYDEPTNO;DEPTNOSUM(SAL)MAX(SAL)MIN(SAL)AVG(SAL)--------------------------------------------------108750500013002916.6666720108753000800217530940028509501566.66667,多列分組舉例,有時你可能需要在組中再分組，上面例子中顯示每個部門中不同頭銜的工資和與平均工資。此時EMP表首先以部門分組，然后按頭銜分組,SQL>SELECTDEPTNO,JOB,SUM(SAL),AVG(SAL)FROMEMPGROUPBYDEPTNO,JOB;DEPTNOJOBSUM(SAL)AVG(SAL)---------------------------------------10CLERK1300130010MANAGER2450245010PRESIDENT5000500020CLERK190095020ANALYST6000300020MANAGER2975297530CLERK95095030MANAGER2850285030SALESMAN56001400,使用HAVING子句限定分組函數(shù)結果值,前面我們講過，分組函數(shù)不能寫在WHERE子句中，如果要對分組函數(shù)結果值進行限定，可以用HAVING子句,SQL>SELECTDEPTNO,JOB,SUM(SAL),AVG(SAL)FROMEMPGROUPBYDEPTNO,JOBHAVINGAVG(SAL)>2000;DEPTNOJOBSUM(SAL)AVG(SAL)---------------------------------------10MANAGER2450245010PRESIDENT5000500020ANALYST6000300020MANAGER2975297530MANAGER28502850,練習,練習分組函數(shù)的使用練習GROUPBY子句練習HAVING子句掌握分組函數(shù)中易犯的錯誤,6子查詢,使用子查詢解決問題,假設想知道誰的工資高于Jones。為了解決這個問題，必須執(zhí)行兩個查詢：第一個查詢查到了Jones的工資，第二個查詢查找高于這個工資的人。可以將這兩個查詢組合起來，將一個查詢放在另一個中來解決此問題。一個內部的查詢或子查詢返回一個值，此值被外部查詢或主查詢使用。使用子查詢等價于執(zhí)行兩個順序查詢。第一個查詢的結果作為第二個查詢檢索的值。,子查詢語法,子查詢在主查詢前執(zhí)行一次主查詢使用子查詢的結果,SELECTselect_listFROMtablenameWHEREexpr_operator(SELECTselect_listFROMtable);,子查詢舉例,,SQL>selectename,salfromempwheresal>(selectsalfromempwhereename=JONES);ENAMESAL--------------------SCOTT3000KING5000FORD3000,子查詢類別,單行子查詢：內部SELECT命令返回一條記錄多行子查詢：內部SELECT命令返回多條記錄多列子查詢：內部SELECT命令返回多個數(shù)據(jù)列,子查詢使用規(guī)則,子查詢要用括號括起來將子查詢放在比較運算符的右邊子查詢中不要加ORDERBY子句對單行子查詢使用單行運算符（如=,>,selectename,salfromempwheresal=(selectsalfromemp);selectename,salfromempwheresal=(selectsalfromemp)*ERRORatline1:ORA-01427:single-rowsubqueryreturnsmorethanonerow,子查詢容易犯的錯誤2：,另一個常見的錯誤是內層查詢沒有返回記錄,SQL>selectename,salfromempwheresal=(selectsalfromempwhereename=NOTHING);norowsselected,多行子查詢舉例,查找個部門工資最高的員工,SQL>SELECTENAME,DEPTNO,SALFROMEMPWHERESALIN(SELECTMAX(SAL)FROMEMPGROUPBYDEPTNO);ENAMEDEPTNOSAL------------------------------KING105000FORD203000SCOTT203000BLAKE302850,在多行子查詢中使用ANY,ANY運算子查詢返回每個值。上面例子中返回工資低于任何CLERK的員工，并且他不是CLERK。CLERK工資最高值為$1300，上面的例子返回工資低于$1300，且非CLERK的員工，”ANY”意味著大于最小值，”=ANY”等于IN,SQL>SELECTename,deptno,job,sal2FROMempWHEREsalCLERK;ENAMEDEPTNOJOBSAL---------------------------------------WARD30SALESMAN1250MARTIN30SALESMAN1250,7操作數(shù)據(jù),課程目標,在這一節(jié)課里，你將學會如何往表中插入記錄，如何修改和刪除表中的記錄。另外，你還將學會如何用COMMIT和ROLLBACK語句控制事務。,數(shù)據(jù)操作語言(DML),當你想在數(shù)據(jù)庫中增加、修改或刪除數(shù)據(jù)時，你就要執(zhí)行DML語句。由一組DML語句組成的邏輯工作單元叫做一個事務。DML語句可以SQL*PLUS或SQL*DBA中直接執(zhí)行。也可以在象Developer/2000、TOAD這樣的工具中執(zhí)行，還可以在SQL預編譯程序中執(zhí)行。數(shù)據(jù)操作語言主要有INSERT,UPDATE和DELETE,INSERT語句,table：表名column：列名value：列的值通過INSERT語句在表中增加一條新記錄。如果省略表后面的列，那么要在VALUES后面按照表的順序指定所有的列的值,INSERTINTOtable[(column[,column…])]VALUES(value〔，value…〕)；,INSERT舉例:,如果表中的列沒有在VALUES中列出來，則系統(tǒng)分配給這些列默認值。沒有默認值則為空。如果該列不能為空，則插入語句不成功。如果違反約束條件，插入語句同樣不能成功。,SQL>INSERTINTOdept(deptno,dname,loc)VALUES(50,DEVELOPMENT,BEIJING);1rowcreated.,從其它表中拷貝數(shù)據(jù),SQL>INSERTINTOMANAGERS(empno,ename,sal,hiredate)SELECTempno,ename,sal,hiredateFROMempWHEREjob=MANAGER;3rowscreated.,UPDATE語句,用UPDATE語句可以一次修改一條或者多條記錄。如果沒有WHERE條件，則所有的SET后面的列的值都會被更新。,UPDATEtableSETcolumn=value[,column=value][WHEREcondition];,UPDATE舉例,給EMP表中部門編號是30的員工工資增加20%,SQL>UPDATEEMPSETSAL=SAL*1.2WHEREDEPTNO=30;6rowsupdated.,DELETE語句,用DELETE語句從表中刪除數(shù)據(jù)。如果不加WHERE條件，那么所有的記錄都將被刪除。,DELETE[FROM]table[WHEREcondition];,從表中刪除指定的記錄,,SQL>DELETEFROMDEPTWHEREDNAME=DEVELOPMENT;1rowdeleted.,提交所作的修改,用INSERT、UPDATE和DELETE語句對數(shù)據(jù)進行改動后，數(shù)據(jù)并沒有真正的保存。這些改動的數(shù)據(jù)別的會話中看不到。為了真的保存，需要執(zhí)行COMMIT命令。,一些隱式的COMMIT,如果在SQL*PLUS中執(zhí)行了DML語句，然后雖然沒有執(zhí)行COMMIT，但是如果存在如下情形之一，Oralce會執(zhí)行一個隱式的COMMIT，一定要注意：1.正常退出SQL*PLUS2.執(zhí)行了一條DDL語句，例如創(chuàng)建了一個表,取消所做的修改,可以用ROLLBACK命令取消所做的修改,隱式的ROLLBACK,如果修改數(shù)據(jù)后，沒有COMMIT，也沒有提交，那么下列情況下Oracle會取消所做的修改：1.系統(tǒng)崩潰2.程序異常退出,DML語句應該注意的問題,一定要加合適的WHERE條件，避免錯誤修改數(shù)據(jù)執(zhí)行DML后因該盡快的顯式執(zhí)行COMMIT或者ROLLBACK，因為被改動的數(shù)據(jù)別的用戶是不能修改的，長時間不結束事務會增加死鎖的可能性,COMMIT舉例,,SQL>UPDATEempSETdeptno=10WHEREdeptno=7782;1rowupdated.,SQL>COMMIT;Commitcomplete.,ROLLBACK舉例,SQL>SELECTCOUNT(*)FROMEMP;COUNT(*)----------16SQL>DELETEFROMEMP;已刪除