SQL基本查詢與SQL Plus的使用

1、實驗3 SQL基本查詢與SQL Plus的使用姓名：學 號：專業(yè)：同組人：無實驗日期：2010-03-18【實驗目的與要求】熟悉Oracle SQL Plus的使用掌握SQL基本查詢?！緦嶒瀮热菖c步驟】3.1 啟動 SQL*Plus要調用SQL Plus，需依次完成如下步驟：單擊Start （“開始）按鈕。指向Programs （“程序）組圖標。選擇 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。啟動SQL之后，將會出現(xiàn)圖3-2所示的屏幕。填寫相應的詳細資料。按Tab鍵 轉到下一個選項。安裝Oracle時創(chuàng)建的默認用戶為Scott，密碼為tiger。主機字符串 是 oracle。Log OnUser Name：Password：Host String：OKCancel圖3-2將會出現(xiàn)如下圖所示的屏幕，顯示產品信息。單擊OK （“確定”）。OKCancel圖3-2將會出現(xiàn)如下圖所示的屏幕，顯示產品信息

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

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

8、HICAGO BOSTONSQL |Q某些列標題由于其列大小限制而使用縮寫詞。要將字符列的顯示寬度設置為12,請給出如下語句：COLUMN columnname FORMAT A12aCOLUMN語句不能以分號結尾，因為它是格式化命令。通過COLUMN命令可以使用的選項包括:選項功能FORMAT指定數(shù)據(jù)格式HEADING texttext為列標題JUSTIFY L R C將標題對齊為LEFT RIGHT CENTERWRAP TRUNC在列中回行或截斷數(shù)據(jù)通過FORMAT選項可用的掩碼包括:掩碼功能Ann個字母數(shù)字字符999.不含前導零的數(shù)字00.含前導零的數(shù)字顯示的小數(shù)點V隱藏的小數(shù)點$先導

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

10、WHERE子句中指定 的條件稱為謂詞。謂詞可以是任意數(shù)據(jù)類型、字符、數(shù)字或日期。像AND、OR和NOT 這樣的邏輯運算符也可以用于合并謂詞。語法如下：SELECT columnnamel, columnname2, ?, columnnameN FROM tablenameWHERE condition;實驗3-4要從雇員表中只顯示那些在部門實驗3-4要從雇員表中只顯示那些在部門20工作的雇員，執(zhí)行如下所示的語句:圖3-6請給出運行結果: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 |在謂詞中可以有效使用的其他運算符還有:1. IN運算符：此運算符用于顯式規(guī)定可以接受的值組。實驗3-5要只顯示屬于“CLERK”（辦事員）或“MANAGER”（經理）的雇 員，執(zhí)行如下命令。圖3-7請給出運行結果:SQL select empno,ename,job.hiredate,salFrom em

12、pwhere job in(clerk,manager);未選定行SQL另一方面，NOT IN運算符將返回與條件不匹配的行。2. BETWEEN運算符：此運算符與IN運算符相似，不過可以指定值的范圍而不是匹 配的值組。實驗 3-6 要只顯示在 “03-JAN-82”（1982 年 1 月 1 日）和“ 33-DEC-87”（1987 年 12月31日）之間加入的雇員，請給出如下命令。土缶NeSQT恤兇Itt W現(xiàn)咖SQL SELH2 FUN EIF力 UHERL KMWiTE BEIUtEK 詢-1 月-誑 AID 割-曲戈-豺；if圖3-8請給出運行結果: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運算符將獲取與條件不匹配的行。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運算符：該運算符用于匹配字符串或字符串的一部分(稱為子字符串)。該 運算符使用通配符擴展其字符串匹配功能。有兩種通配符與LIKE運算符一起使 用：下劃線(_):這表示任意單字符。百分號(%)：這表示多字符的序列。實驗3-7要顯示姓名以“J”開頭的雇員，請給出如下命令。圖3-9請給出運行結果：SQL select empno,ename,job,hiredate,sal2Fromemp3whereenamelike J1;EMPNOENAMEJOBHIREDATESAL7566JONESMANAGER02-4月一附29757900JAMESCLE

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

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

18、查詢返回的值中返回最大值。Selelct job,count(job)From empGroup by jobHavingMin：從查詢返回的值中返回最小值。Avg：用來確定某個列中出現(xiàn)的值的平均數(shù)。Sum：用來獲取查詢輸出的行的算術和。實驗3-10實驗3-10要從雇員表中顯示付給經理的最高、最低、平均和總薪金，執(zhí)行如下所 示的語句：圖 3-12請給出運行結果：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)的順序檢索查詢?？梢酝ㄟ^指定DESC參 數(shù)或ASC參數(shù)來按升序順序或降序順序進行排序。如果沒有指定任何參數(shù)，則默認為 升序排序。還可對多列進行排序。語法如下:SELECT columnnamel, columnname2, ?, columnnameNFROM tablenameWHERE condition;ORDER BY columnnamel, columnname2,columnnameN;實驗3-11要以雇員的工作的順序顯示收入大于或等于3000的雇員，執(zhí)行如下所 示的語句：圖 3-13請給出運行結果:SQL select job,empno,ename,s

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

21、示雇員總薪金，執(zhí)行如下語句:圖 3-14請給出運行結果: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ù)用來將空值轉換為數(shù)字值，我們在此將其轉 換為“0”。我們必須按任意表達式執(zhí)行此操作，表達式中包含空值，因為操作數(shù)返回 NULL。因此，為避免在任何操作中出現(xiàn)這種錯誤，我們使用NVL函數(shù)。3.3退出SQL在SQL提示符下鍵入Exit （“退出”）或執(zhí)行如下步驟：令 從Main Menu （“主菜單”）中選擇Fi2e （“文件”）選項。 令 單擊Exit （退出）。令您也可

23、以在命令提示符下輸入“xit。3.4實驗練習：在scott用戶模式下，完成下查詢需求，給出相關查詢語句和運行結果:列出各種類別 SALES工作的雇員的姓名，假定不知道銷售部的部門編號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ù)量的所有部門的詳細信息,即使是分配有0個雇員.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 |列出各種類別工作的最低工資.Select min(sal) from emp group by job;SQL Select min(sal) from emp group by job;MIN(SAL)3000800245850001258SQL |列出各個部門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 |列出按計算的字段排序的所有雇員的年薪.SQL select (sal+nul(comm,O)*12 as aunfrom emporder by aun;AUN9600 11400 13200 15600 18000 21000 22800 29400 31800 34200 35700AUN360003600060000已選擇行。選擇部門30中的雇員。select * from empwhere deptno=30;SQL select * F

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

29、69803-12 -81950己選擇百行。(9)列出所有辦事員的姓名、編號和部門。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找出部門10中所有經理和部門20中所有辦事員的詳細資料。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找出部門10中所有經理、部門20中所有辦事員以及既不是經理又不是辦事員但 其薪金大于或等于2000的所有雇員的詳細資料。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顯示只有首字母大寫的所有雇員的姓名。select enamefrom empwhere ename=initcap(ename);SQL select enameFrom empwhere ename=initcap(ename);未選定行SQL |顯示雇員的詳細資料，按姓名排序。select * from emp order by ename;SQL select *Fron emporder by ename;EMPNO ENfiMEJOBMGR HIREDf