SQL基本查詢與SQL Plus的使用

1、實(shí)驗(yàn)3 SQL基本查詢與SQL Plus的使用姓名：學(xué) 號(hào)：專業(yè)：同組人：無(wú)實(shí)驗(yàn)日期：2010-03-18【實(shí)驗(yàn)?zāi)康呐c要求】熟悉Oracle SQL Plus的使用掌握SQL基本查詢?！緦?shí)驗(yàn)內(nèi)容與步驟】3.1 啟動(dòng) SQL*Plus要調(diào)用SQL Plus，需依次完成如下步驟：?jiǎn)螕鬝tart （“開(kāi)始）按鈕。指向Programs （“程序）組圖標(biāo)。選擇 OracleraclelOg。My 匚omputerWinZipSqlplusv-.My 匚omputerWinZipSqlplusv-.1NetworkWiriA1 endM 已 ighb enh口 dNetworkWiriA1 endM 已

2、ighb enh口 dInternetExplorer凰PowerpritAccessoriesRecycle Bin SoniqueStartupCommand PromptWindows NT ExplorerAdministrative T ools (Common) Creative卜Microsoft Office ToolsWinZipNetscape SmartUpdateNew Office Document pen Office Document島 ProgramsFavorites Documents Settings FindHelpRun.於 Log Off Admin

3、istrator.Jbhut Down.Microsoft Visual Studio 6.0Netscape CommunicatorStartupWinZipMicrosoft AccessMicrosoft ExcelMicrosoft OutlookWinZipNetscape SmartUpdateNew Office Document pen Office Document島 ProgramsFavorites Documents Settings FindHelpRun.於 Log Off Administrator.Jbhut Down.Microsoft Visual Stu

4、dio 6.0Netscape CommunicatorStartupWinZipMicrosoft AccessMicrosoft ExcelMicrosoft OutlookMicrosoft PowerPointMicrosoft WordSoniqueAnna Volume 2 Dr Solomons AVTKS Accessories眉 Onrleoraclefii您 Application Development.-111岬k 洌ministration c3lion曙F；已加祇羋中旳Database Administration IN TYPE File AssistantOra

5、cle Objects for OLE Class Library Help裳 gQLPIus哲 Web Publishing Assistant9910:55 AM單擊 SOL Plus。啟動(dòng)SQL之后，將會(huì)出現(xiàn)圖3-2所示的屏幕。填寫(xiě)相應(yīng)的詳細(xì)資料。按Tab鍵 轉(zhuǎn)到下一個(gè)選項(xiàng)。安裝Oracle時(shí)創(chuàng)建的默認(rèn)用戶為Scott，密碼為tiger。主機(jī)字符串 是 oracle。Log OnUser Name：Password：Host String：OKCancel圖3-2將會(huì)出現(xiàn)如下圖所示的屏幕，顯示產(chǎn)品信息。單擊OK （“確定”）。OKCancel圖3-2將會(huì)出現(xiàn)如下圖所示的屏幕，顯示產(chǎn)品信息

6、。圖3-3SQL 是SQL提示符。在此提示符下提供要執(zhí)行的SQL命令和語(yǔ)句。下面給出可通過(guò)scott/tiger登錄訪問(wèn)的默認(rèn)表及其列名。在本實(shí)驗(yàn)中，我們將使 用這些表。EMPDEPTSALGRADEEMPNODEPTNOGRADEENAMEDNAMELOSALJOBLOCHISALMGRHIREDATE SAL COMM DEPTNO3.2從表中查看數(shù)據(jù)輸入表中的數(shù)據(jù)可通過(guò)使用SELECT語(yǔ)句進(jìn)行查看。3.2.1查看所有列語(yǔ)法如下：SELECT * FROM tablename;其中“*”表示將顯示所有列。實(shí)驗(yàn)3-1部門(mén)表中查看所有記錄和所有字段，請(qǐng)執(zhí)行如下步驟：令 在SQL提示符下鍵入Se

7、lect * from dept”（從部門(mén)選擇*）。令 按Enter。您將得到圖3-4中顯示的結(jié)果。圖3-43.2.2查看選擇列要查看選擇列，請(qǐng)輸入列名，并用逗點(diǎn)而不是“*”隔開(kāi)。語(yǔ)法如下:SELECT columnnamel, columnname2, ?, columnnameN FROM tablename;實(shí)驗(yàn)3-2只顯示部門(mén)表中的部門(mén)名和位置，執(zhí)行如下所示的語(yǔ)句:圖3-5請(qǐng)給出運(yùn)行結(jié)果:SQL select dname,loc 2 from scott.dept;DNAMELOCACCOUNTING RESEARCH SALES OPERATIONSNEW VORK DALLAS C

8、HICAGO BOSTONSQL |Q某些列標(biāo)題由于其列大小限制而使用縮寫(xiě)詞。要將字符列的顯示寬度設(shè)置為12,請(qǐng)給出如下語(yǔ)句：COLUMN columnname FORMAT A12aCOLUMN語(yǔ)句不能以分號(hào)結(jié)尾，因?yàn)樗歉袷交睢Ｍㄟ^(guò)COLUMN命令可以使用的選項(xiàng)包括:選項(xiàng)功能FORMAT指定數(shù)據(jù)格式HEADING texttext為列標(biāo)題JUSTIFY L R C將標(biāo)題對(duì)齊為L(zhǎng)EFT RIGHT CENTERWRAP TRUNC在列中回行或截?cái)鄶?shù)據(jù)通過(guò)FORMAT選項(xiàng)可用的掩碼包括:掩碼功能Ann個(gè)字母數(shù)字字符999.不含前導(dǎo)零的數(shù)字00.含前導(dǎo)零的數(shù)字顯示的小數(shù)點(diǎn)V隱藏的小數(shù)點(diǎn)$先導(dǎo)

9、$MI數(shù)字后面是減號(hào)PR負(fù)值包括在“”之內(nèi)B顯示空白而不是零要顯示包含前導(dǎo)零的薪金，請(qǐng)?jiān)赟QL提示符下給出如下命令。SQL column sal format 009999亠 O125J實(shí)驗(yàn)3-3亠 O125J玄柿舊 Wilt- 網(wǎng)劃S4E叨 fllftifi網(wǎng)I* ?tLFCf tHFHp.Emwh5*l ,cnwii FROM Ltfi圖 3-5(a)請(qǐng)給出運(yùn)行結(jié)果:這樣，所有雇員的薪金就以包含前導(dǎo)零的格式顯示。要?jiǎng)h除此格式，請(qǐng)?jiān)谔崾痉?下輸入如下給定命令。SQL column sal forma 9999993.2.3查看符合條件的行可以使用WHERE子句完成對(duì)某些相關(guān)數(shù)據(jù)行的條件檢索。

10、WHERE子句中指定 的條件稱為謂詞。謂詞可以是任意數(shù)據(jù)類(lèi)型、字符、數(shù)字或日期。像AND、OR和NOT 這樣的邏輯運(yùn)算符也可以用于合并謂詞。語(yǔ)法如下：SELECT columnnamel, columnname2, ?, columnnameN FROM tablenameWHERE condition;實(shí)驗(yàn)3-4要從雇員表中只顯示那些在部門(mén)實(shí)驗(yàn)3-4要從雇員表中只顯示那些在部門(mén)20工作的雇員，執(zhí)行如下所示的語(yǔ)句:圖3-6請(qǐng)給出運(yùn)行結(jié)果:SQL select empno ename,job,hiredate,sal,deptno 2 From scott.emp where deptno=2Q

11、;ENAMEJOBHIREDATESALDEPTNO7369CLERK17-12 -80800207566MANAGER02-812975207788ANALVST何曬、-873000207876CLERK23-5-871100207902ANALVST03-12 -81300020SQL |在謂詞中可以有效使用的其他運(yùn)算符還有:1. IN運(yùn)算符：此運(yùn)算符用于顯式規(guī)定可以接受的值組。實(shí)驗(yàn)3-5要只顯示屬于“CLERK”（辦事員）或“MANAGER”（經(jīng)理）的雇 員，執(zhí)行如下命令。圖3-7請(qǐng)給出運(yùn)行結(jié)果:SQL select empno,ename,job.hiredate,salFrom em

12、pwhere job in(clerk,manager);未選定行SQL另一方面，NOT IN運(yùn)算符將返回與條件不匹配的行。2. BETWEEN運(yùn)算符：此運(yùn)算符與IN運(yùn)算符相似，不過(guò)可以指定值的范圍而不是匹 配的值組。實(shí)驗(yàn) 3-6 要只顯示在 “03-JAN-82”（1982 年 1 月 1 日）和“ 33-DEC-87”（1987 年 12月31日）之間加入的雇員，請(qǐng)給出如下命令。土缶NeSQT恤兇Itt W現(xiàn)咖SQL SELH2 FUN EIF力 UHERL KMWiTE BEIUtEK 詢-1 月-誑 AID 割-曲戈-豺；if圖3-8請(qǐng)給出運(yùn)行結(jié)果:SQLselect empno,en

13、ame,job,hiredate,salFrom empwhere hiredate between 81-1 -82 and 31-12 -87;EMPNOENAMEJOBHIREDATESAL7788SCOTTANALVST何一即月-8730007876ADAMSCLERK23-5-8711007934MILLERCLERK23-1-821300NOT BETWEEN運(yùn)算符將獲取與條件不匹配的行。SQL select empno,ename,job.hiredate,salfrom empwhere hiredate not betueen 01-1 -82 and 31-12 -07;E

14、MPN0ENAMEJOBHIREDATESAL7369SMITHCLERK17-12 -808007499ALLENSALESMAN20-2-8116007521UARDSALESMAN22-2-8112507566JONESMANAGER02-8129757654MARTINSALESMAN28-9-8112507698BLAKEMANAGER-8128507782CLARKMANAGER09-fiE-8124507839KINGPRESIDENT17-11月-8150007844TURNERSALESMAN朋-9月-8115007900JAMESCLERK03-12-819507902FO

15、RDANALVST03-12-813000LIKE運(yùn)算符：該運(yùn)算符用于匹配字符串或字符串的一部分(稱為子字符串)。該 運(yùn)算符使用通配符擴(kuò)展其字符串匹配功能。有兩種通配符與LIKE運(yùn)算符一起使 用：下劃線(_):這表示任意單字符。百分號(hào)(%)：這表示多字符的序列。實(shí)驗(yàn)3-7要顯示姓名以“J”開(kāi)頭的雇員，請(qǐng)給出如下命令。圖3-9請(qǐng)給出運(yùn)行結(jié)果：SQL select empno,ename,job,hiredate,sal2Fromemp3whereenamelike J1;EMPNOENAMEJOBHIREDATESAL7566JONESMANAGER02-4月一附29757900JAMESCLE

16、RK03-12月-81950SQL3.2.4查看匯總數(shù)據(jù)(使用統(tǒng)計(jì)函數(shù))GROUP BY子句根據(jù)字段值對(duì)行進(jìn)行分組。該子句在應(yīng)用時(shí)將與聚合函數(shù)聯(lián)合。 有時(shí)，也要求對(duì)已分組的查詢進(jìn)行條件檢索。可以使用“HAVING”子句對(duì)已分組查詢 進(jìn)行條件檢索?！癏AVING”子句與“WHERE”子句相似，只不過(guò)“WHERE”子句用 于行，而“HAVING”子句用于已分組的結(jié)果。語(yǔ)法如下：SELECT columnnamel, aggregate_function(columnname2)FROM tablenameGROUP BY columnnamel;實(shí)驗(yàn)3-8要從雇員表中顯示付給經(jīng)理的最高薪金，執(zhí)行如

17、下所示的語(yǔ)句:圖 3-10請(qǐng)給出運(yùn)行結(jié)果:SQL select job,max(sal)From empgroup 纓 jobhauing job=manager;未選定行SQL聚合函數(shù)通常與Group by和Having子句一起使用。要討論的聚合函數(shù)是:1. Count:此函數(shù)對(duì)包括Null值的查詢所返回的行數(shù)進(jìn)行記數(shù)。實(shí)驗(yàn)3-9要查找每個(gè)工作類(lèi)別的人數(shù)，執(zhí)行如下所示的語(yǔ)句:圖 3-11請(qǐng)給出運(yùn)行結(jié)果：SQL2SQL23JOBCOUNT(JOB)group by job;JOBCOUNT(JOB)CLERK SALESMAN PRESIDENT MANAGER ANALYSTSQLMax：從

18、查詢返回的值中返回最大值。Selelct job,count(job)From empGroup by jobHavingMin：從查詢返回的值中返回最小值。Avg：用來(lái)確定某個(gè)列中出現(xiàn)的值的平均數(shù)。Sum：用來(lái)獲取查詢輸出的行的算術(shù)和。實(shí)驗(yàn)3-10實(shí)驗(yàn)3-10要從雇員表中顯示付給經(jīng)理的最高、最低、平均和總薪金，執(zhí)行如下所 示的語(yǔ)句：圖 3-12請(qǐng)給出運(yùn)行結(jié)果：SQL select job,max(sal),min(sal),auq(sal),sum(sal)From empgroup 纓 jobhauing job=manager;未選定行SQL |3.2.5以指定順序查看數(shù)據(jù)ORDER B

19、Y子句可幫助按照希望出現(xiàn)的順序檢索查詢?？梢酝ㄟ^(guò)指定DESC參 數(shù)或ASC參數(shù)來(lái)按升序順序或降序順序進(jìn)行排序。如果沒(méi)有指定任何參數(shù)，則默認(rèn)為 升序排序。還可對(duì)多列進(jìn)行排序。語(yǔ)法如下:SELECT columnnamel, columnname2, ?, columnnameNFROM tablenameWHERE condition;ORDER BY columnnamel, columnname2,columnnameN;實(shí)驗(yàn)3-11要以雇員的工作的順序顯示收入大于或等于3000的雇員，執(zhí)行如下所 示的語(yǔ)句：圖 3-13請(qǐng)給出運(yùn)行結(jié)果:SQL select job,empno,ename,s

20、alFrom empwhere sal=3000order by job;JOBEMPNOENAMESALNALVST7788SCOTT3000NALVST7902FORD3000RESIDENT7839KING5000SQL |3.2.6使用計(jì)算列查看數(shù)據(jù)除了從數(shù)據(jù)庫(kù)表中直接提取的列外，SQL還可以用于計(jì)算多個(gè)列中的數(shù)據(jù)的值。 可在查詢中使用SQL表達(dá)式執(zhí)行所有類(lèi)型的算術(shù)操作。唯一必須注意的是，這些表達(dá) 式涉及的列應(yīng)該只屬于數(shù)字?jǐn)?shù)據(jù)類(lèi)型。語(yǔ)法如下：SELECT columnnamel, (columnname2 columnname3) FROM tablename實(shí)驗(yàn)3-12要從雇員表中顯

21、示雇員總薪金，執(zhí)行如下語(yǔ)句:圖 3-14請(qǐng)給出運(yùn)行結(jié)果:SQL select job,empno,ename,sal*nul(comm,O) 2 From emp;JOBEMPNOENAMESAL*NUL(COMM,0)CLERK7369SMITH0SALESMAN7499ALLEN480000SALESMAN7521UARD625000MANAGER7566JONES0SALESMAN7654MARTIN1750000MANAGER7698BLAKE0MANAGER7782CLARK0HNfiLVST7788SCOTT0PRESIDENT7839KING0SALESMAN7844TURNER

22、0CLERK7876ADAMS0JOBEMPNOENAMESAL*NUL(COMM,0)CLERK7900JAMES0HNfiLVST7982FORD0CLERK7934MILLER0已選擇4帝。注意：在上面的命令中，NVL函數(shù)用來(lái)將空值轉(zhuǎn)換為數(shù)字值，我們?cè)诖藢⑵滢D(zhuǎn) 換為“0”。我們必須按任意表達(dá)式執(zhí)行此操作，表達(dá)式中包含空值，因?yàn)椴僮鲾?shù)返回 NULL。因此，為避免在任何操作中出現(xiàn)這種錯(cuò)誤，我們使用NVL函數(shù)。3.3退出SQL在SQL提示符下鍵入Exit （“退出”）或執(zhí)行如下步驟：令 從Main Menu （“主菜單”）中選擇Fi2e （“文件”）選項(xiàng)。 令 單擊Exit （退出）。令您也可

23、以在命令提示符下輸入“xit。3.4實(shí)驗(yàn)練習(xí)：在scott用戶模式下，完成下查詢需求，給出相關(guān)查詢語(yǔ)句和運(yùn)行結(jié)果:列出各種類(lèi)別 SALES工作的雇員的姓名，假定不知道銷(xiāo)售部的部門(mén)編號(hào)SQL select ename2 From emp3 where deptno=4(select deptno5From dept6where dname=upper(SALES);ENAMEALLENHARDMARTINBLAKETURNERJAMES已選擇百行。SQL(2)列出薪金高于公司平均水平的所有雇員.SQL select enameFrom empwhere sal(select aug(sal) F

24、rom emp);ENAMEJONESBLAKECLARKSCOTTKINGFORD已選擇宜行。SQL(3)列出與SCOTT 從事相同工作的所有雇員.select ename from empwhere job二(select jobfrom empwhere ename=upper(sc ott);SQLSQL select enamefrom empwhere job=456ENAME(select jobfrom emp where ename=upper(scott);SCOTT FORDSQL |(4)列出分配有雇員數(shù)量的所有部門(mén)的詳細(xì)信息,即使是分配有0個(gè)雇員.select dep

25、t.deptno,dname,loc,count(empno)from dep t,empwhere dep t.dep tn o二emp.dep tn o(+)group by dep t.dep tn o,dname,locSQL select dept.deptno,dname,loc,count(empno)from dept,empwhere dept.deptno=emp.deptno(+)group by dept.deptno,dname,locDEPTNO DNAMELOCCOUNT(EMPNO)10 ACCOUNTINGNEU VORK320 RESEARCHDALLAS5

26、30 SALESCHICAGO640 OPERATIONSBOSTON0SQL |列出各種類(lèi)別工作的最低工資.Select min(sal) from emp group by job;SQL Select min(sal) from emp group by job;MIN(SAL)3000800245850001258SQL |列出各個(gè)部門(mén)MANAGER的最低薪金. selec t dep tn o,min(sal) from empwhere job二upper(manager)group by deptno;SQL select deptno,min(sal)234from emp w

27、here job=upper(manager)groupDEPTNOby deptno;MIN(SAL)102450202975302850SQL |列出按計(jì)算的字段排序的所有雇員的年薪.SQL select (sal+nul(comm,O)*12 as aunfrom emporder by aun;AUN9600 11400 13200 15600 18000 21000 22800 29400 31800 34200 35700AUN360003600060000已選擇行。選擇部門(mén)30中的雇員。select * from empwhere deptno=30;SQL select * F

28、rom emp2 where deptno=30;EMPNO ENAMEJOBMGR HIREDATESfiLCOMMDEPTNO749930ALLENSALESMAN769820-2月-811600300752130HARDSALESMAN769822-2月-811250500765430MARTINSALESMAN769828-9月-8112501400EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO769830BLAKEMANAGER7839時(shí)-5月-812850784430TURNERSALESMAN7698089AMESCLERK7

29、69803-12 -81950己選擇百行。(9)列出所有辦事員的姓名、編號(hào)和部門(mén)。selec t ename,empno,dnamefrom emp einner join dep t d on e.dep tno 二 d.dep tnowhere job二upper(clerk);SQL select ename,empno,dnameFrom emp einner join dept d on e.deptno = d.deptnowhere job=upper(clerk);ENAMEEMPNODNAMESMITH7369RESEARCHWAMS7876RESEARCHJAMES7900

30、SALES1ILLER7934ACCOUNTINGSQL找出傭金高于薪金的雇員。 select * from emp where commsal;SQL select * From emp where commsal;SALCOMMEMP NOENAMEJOBMGRHIREDATEDEPTNO765430SQL |MARTINSALESMAN769828-9月-站12501400找出傭金高于薪金60%的雇員。select * from empwhere commsal*0.6;SQL select *from empwhere commsal*0.6;EMPNO ENAMEJOBSQL sel

31、ect *from empwhere commsal*0.6;EMPNO ENAMEJOBMGR HIREDATESALCOMMDEPTNO7654 MARTIN30SALESMAN7654 MARTIN30SALESMAN7698 28-9月-81SQL找出部門(mén)10中所有經(jīng)理和部門(mén)20中所有辦事員的詳細(xì)資料。select * from empwhere (deptno=10 and job二upper(manager)or (dep tn o=20 and job二upper(clerk);SQL select * From emp2 where (deptno=1Q and job=upp

32、er(manager)3EMPNOr (deptno=20 and job=upper(clerk);ENAMEJOBMGR HIREDATESALCOMMDEPTNO736920SMITHCLERK790217-12 -80SQQ778210CLARKMANAGER783909月2450787620ADAMSCLERK778823-5月 -8了1100找出部門(mén)10中所有經(jīng)理、部門(mén)20中所有辦事員以及既不是經(jīng)理又不是辦事員但 其薪金大于或等于2000的所有雇員的詳細(xì)資料。select * from emp where (deptno=10 and job二upper(manager)or (d

33、ep tn o=20 and job二upper(clerk)or (jobupper(manager)and jobupper(clerk) and sal=2000);SQL select * From emp2345whereEMPNO(deptno=10 and job=upper(manager) or (deptno=2B and job=upper(clerk) or (jobupper(manager)and jobupper(clerk) and sal=2000);ENA MEJOBMGR HIREDATESALCOMMDEPTNO736920SMITHCLERK79021

34、7-12| -80800778210CLARKMANAGER7839卿-右月245 0778820SCOTTANALYST756619-即月-873000EMPNOENA MEJOBMGRHIREDATESALCOMMDEPTNO783910KINGPRESIDENT17-11月-815000787620ADAMSCLERK778823-5月 -8了1100找出不收取傭金或收取的傭金低于100的雇員。select * from emp where nvl(comm,0)100;SQL2select * from empwhereEMPNOnul(comm,0) select *From emp

35、where months_between(sysdate.hiredate)/1212;EMP NOENA MEJOBMGRHIREDATESALCOMMDEPTNO736920SMITHCLERK79 0217-12| -80800749930ALLENSALESMAN76982 0-2月-站1600300752130UARDSALESMAN769822-2月-811258500EMP NOENA MEJOBMGRHIREDATESALCOMMDEPTNO7566JONESMANAGER783902-4月-站2975EMP NOENAMEJOBMGRHIREDATESALCOMMDEPTNO

36、79 0220FORDANALYST756603-12月-813000793410MILLERCLERK778223-1 -821300顯示只有首字母大寫(xiě)的所有雇員的姓名。select enamefrom empwhere ename=initcap(ename);SQL select enameFrom empwhere ename=initcap(ename);未選定行SQL |顯示雇員的詳細(xì)資料，按姓名排序。select * from emp order by ename;SQL select *Fron emporder by ename;EMPNO ENfiMEJOBMGR HIREDf