實(shí)驗(yàn) SQL基本查詢(xún)與SQL Plus的使用

1、實(shí)驗(yàn)3 SQL基本查詢(xún)與SQL Plus的使用【實(shí)驗(yàn)?zāi)康呐c要求】1 熟悉Oracle SQL Plus的使用2 掌握SQL基本查詢(xún)?！緦?shí)驗(yàn)內(nèi)容與步驟】3.1 啟動(dòng) SQL*Plus 要調(diào)用 SQL Plus，需依次完成如下步驟：² 單擊 Start（“開(kāi)始”）按鈕。² 指向 Programs（“程序”）組圖標(biāo)。² 選擇 Oracleoracle10g。圖 3-1² 選擇 Application Development（“應(yīng)用程序開(kāi)發(fā)”）選項(xiàng)。² 單擊 SQL Plus。啟動(dòng) SQL 之后，將會(huì)出現(xiàn)圖3-2 所示的屏幕。填寫(xiě)相應(yīng)的詳細(xì)資料。按 T

2、ab 鍵轉(zhuǎn)到下一個(gè)選項(xiàng)。安裝 Oracle 時(shí)創(chuàng)建的默認(rèn)用戶(hù)為 Scott，密碼為 tiger。主機(jī)字符串是數(shù)據(jù)庫(kù)名稱(chēng)(TestDB)，對(duì)本機(jī)而言，也可空著；若只是希望啟動(dòng)SQL Plus不想以任何用戶(hù)身份登錄，則可在用戶(hù)名處輸入”/nolog”登錄。圖 3-2(a)² 單擊 OK（“確定”），完成登錄將會(huì)出現(xiàn)如下圖所示的屏幕，顯示產(chǎn)品信息。圖 3-3SQL > 是 SQL 提示符。在此提示符下提供要執(zhí)行的 SQL 命令和語(yǔ)句。下面給出可通過(guò) scott/tiger 登錄訪(fǎng)問(wèn)的默認(rèn)表及其列名。在本實(shí)驗(yàn)中，我們將使用這些表。EMPDEPTSALGRADEEMPNOENAMEJOB

3、MGRHIREDATESALCOMMDEPTNODEPTNODNAMELOCGRADELOSALHISAL3.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 提示符下鍵入“Select * from dept”（從部門(mén)選擇 *）；² 按 Enter。 將得到圖3-4 中顯示的結(jié)果。圖 3-43.2.2查看選擇列要查看選擇列，請(qǐng)輸入列名，并用逗點(diǎn)而不是 “*” 隔開(kāi)。語(yǔ)法

4、如下：SELECT columnname1, columnname2, ?, columnnameNFROM tablename;實(shí)驗(yàn)3-2只顯示部門(mén)表中的部門(mén)名和位置，執(zhí)行如下所示的語(yǔ)句：圖 3-5請(qǐng)給出運(yùn)行結(jié)果：$某些列標(biāo)題由于其列大小限制而使用縮寫(xiě)詞。要將字符列的顯示寬度設(shè)置為 12，請(qǐng)給出如下語(yǔ)句： COLUMN columnname FORMAT A12$COLUMN 語(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ì)齊為 LEFT RIGHT

5、 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) $MI數(shù)字后面是減號(hào)PR負(fù)值包括在“<>”之內(nèi)B顯示空白而不是零要顯示包含前導(dǎo)零的薪金，請(qǐng)?jiān)?SQL 提示符下給出如下命令。SQL> column sal format 009999實(shí)驗(yàn)3-3設(shè)置格式之后，執(zhí)行如下所示的命令。圖 3-5請(qǐng)給出運(yùn)行結(jié)果：這樣，所有雇員的薪金就以包含前導(dǎo)零的格式顯示。要?jiǎng)h除此格式，請(qǐng)?jiān)谔崾痉螺斎肴缦陆o定命令。SQL> column sal f

6、ormat 9999993.2.3查看符合條件的行可以使用 WHERE 子句完成對(duì)某些相關(guān)數(shù)據(jù)行的條件檢索。WHERE 子句中指定的條件稱(chēng)為謂詞。謂詞可以是任意數(shù)據(jù)類(lèi)型、字符、數(shù)字或日期。像 AND、OR 和 NOT 這樣的邏輯運(yùn)算符也可以用于合并謂詞。語(yǔ)法如下：SELECT columnname1, columnname2, ?, columnnameNFROM tablenameWHERE <condition>實(shí)驗(yàn)3-4要從雇員表中只顯示那些在部門(mén) 20 工作的雇員，執(zhí)行如下所示的語(yǔ)句：圖 3-6請(qǐng)給出運(yùn)行結(jié)果：在謂詞中可以有效使用的其他運(yùn)算符還有：1. IN 運(yùn)算符：此運(yùn)算

7、符用于顯式規(guī)定可以接受的值組。實(shí)驗(yàn)3-5 要只顯示屬于“CLERK”（辦事員）或“MANAGER”（經(jīng)理）的雇員，執(zhí)行如下命令。圖 3-7請(qǐng)給出運(yùn)行結(jié)果：另一方面，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)給出如下命令。圖 3-8請(qǐng)給出運(yùn)行結(jié)果：NOT BETWEEN 運(yùn)算符將獲取與條件不匹配的行。3. LIKE 運(yùn)算符：該運(yùn)算符用于匹配字符串或

8、字符串的一部分（稱(chēng)為子字符串）。該運(yùn)算符使用通配符擴(kuò)展其字符串匹配功能。有兩種通配符與 LIKE 運(yùn)算符一起使用：Ø 下劃線(xiàn) (_)：這表示任意單字符。Ø 百分號(hào) (%)：這表示多字符的序列。實(shí)驗(yàn)3-7 要顯示姓名以“J”開(kāi)頭的雇員，請(qǐng)給出如下命令。圖3-9請(qǐng)給出運(yùn)行結(jié)果：3.2.4查看匯總數(shù)據(jù)（使用統(tǒng)計(jì)函數(shù)）GROUP BY 子句根據(jù)字段值對(duì)行進(jìn)行分組。該子句在應(yīng)用時(shí)將與聚合函數(shù)聯(lián)合。有時(shí)，也要求對(duì)已分組的查詢(xún)進(jìn)行條件檢索?？梢允褂谩癏AVING”子句對(duì)已分組查詢(xún)進(jìn)行條件檢索?！癏AVING”子句與“WHERE”子句相似，只不過(guò)“WHERE”子句用于行，而“HAVING”

9、子句用于已分組的結(jié)果。語(yǔ)法如下：SELECT columnname1, aggregate_function(columnname2)FROM tablenameGROUP BY columnname1;實(shí)驗(yàn)3-8要從雇員表中顯示付給經(jīng)理的最高薪金，執(zhí)行如下所示的語(yǔ)句：圖3-10請(qǐng)給出運(yùn)行結(jié)果：聚合函數(shù)通常與 Group by 和 Having 子句一起使用。要討論的聚合函數(shù)是：1.Count：此函數(shù)對(duì)包括 Null 值的查詢(xún)所返回的行數(shù)進(jìn)行記數(shù)。實(shí)驗(yàn)3-9要查找每個(gè)工作類(lèi)別的人數(shù)，執(zhí)行如下所示的語(yǔ)句：圖 3-11請(qǐng)給出運(yùn)行結(jié)果：2.Max：從查詢(xún)返回的值中返回最大值。3.Min：從查詢(xún)返回的

10、值中返回最小值。4.Avg：用來(lái)確定某個(gè)列中出現(xiàn)的值的平均數(shù)。5.Sum：用來(lái)獲取查詢(xún)輸出的行的算術(shù)和。實(shí)驗(yàn)3-10要從雇員表中顯示付給經(jīng)理的最高、最低、平均和總薪金，執(zhí)行如下所示的語(yǔ)句：圖3-12請(qǐng)給出運(yùn)行結(jié)果：3.2.5以指定順序查看數(shù)據(jù)ORDER BY子句可幫助按照希望出現(xiàn)的順序檢索查詢(xún)?？梢酝ㄟ^(guò)指定 DESC 參數(shù)或 ASC 參數(shù)來(lái)按升序順序或降序順序進(jìn)行排序。如果沒(méi)有指定任何參數(shù)，則默認(rèn)為升序排序。還可對(duì)多列進(jìn)行排序。語(yǔ)法如下：SELECT columnname1, columnname2, ?, columnnameNFROM tablenameWHERE <conditio

11、n>ORDER BY columnname1, columnname2, , columnnameN;實(shí)驗(yàn)3-11要以雇員的工作的順序顯示收入大于或等于 3000 的雇員，執(zhí)行如下所示的語(yǔ)句：圖 3-13請(qǐng)給出運(yùn)行結(jié)果：3.2.6使用計(jì)算列查看數(shù)據(jù)除了從數(shù)據(jù)庫(kù)表中直接提取的列外，SQL 還可以用于計(jì)算多個(gè)列中的數(shù)據(jù)的值?？稍诓樵?xún)中使用 SQL 表達(dá)式執(zhí)行所有類(lèi)型的算術(shù)操作。唯一必須注意的是，這些表達(dá)式涉及的列應(yīng)該只屬于數(shù)字?jǐn)?shù)據(jù)類(lèi)型。語(yǔ)法如下：SELECT columnname1, (columnname2 <arithmetic operator> columnname3)F

12、ROM tablename實(shí)驗(yàn)3-12要從雇員表中顯示雇員總薪金，執(zhí)行如下語(yǔ)句：圖 3-14請(qǐng)給出運(yùn)行結(jié)果：注意： 在上面的命令中，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（“主菜單”）中選擇 File（“文件”）選項(xiàng)。² 單擊 Exit（“退出”）。² 您也可以在命令提示符下輸入“Exit”。3.

13、4 實(shí)驗(yàn)練習(xí)：在scott用戶(hù)模式下，完成下查詢(xún)需求，給出相關(guān)查詢(xún)語(yǔ)句和運(yùn)行結(jié)果：(1) 列出各種類(lèi)別"SALES"工作的雇員的姓名，假定不知道銷(xiāo)售部的部門(mén)編號(hào).(2) 列出薪金高于公司平均水平的所有雇員.(3) 列出與"SCOTT"從事相同工作的所有雇員.(4) 列出分配有雇員數(shù)量的所有部門(mén)的詳細(xì)信息,即使是分配有0個(gè)雇員. (5) 列出各種類(lèi)別工作的最低工資.(6) 列出各個(gè)部門(mén)MANAGER的最低薪金.(7) 列出按計(jì)算的字段排序的所有雇員的年薪.(8)  選擇部門(mén) 30 中的雇員。(9) 列出所有辦事員的姓名、編號(hào)和部門(mén)。(10) 找出傭金高于薪金的雇員。(11) 找出傭金高于薪金 60% 的雇員。(12) 找出部門(mén) 10 中所有經(jīng)理和部門(mén) 20 中所有辦事員的詳細(xì)資料。(13) 找出部門(mén) 10 中所有經(jīng)理、部門(mén) 20 中所有辦事員以及既不是經(jīng)理又不是辦事員但其薪金大于或等于 2000 的