《OracleSQL實(shí)用基礎(chǔ)教程.ppt》由會員分享，可在線閱讀，更多相關(guān)《OracleSQL實(shí)用基礎(chǔ)教程.ppt（144頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

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