計算機(jī)技術(shù)講義

計算機(jī)實用技術(shù)

PartI

OracleSQL

PartII

Oracle體系結(jié)構(gòu)

謝金國朱敏

南京航空航天大學(xué)計算中心

2005年9月

計算機(jī)實用新技術(shù)(PartI)

目錄

-2-

計算機(jī)實用技術(shù)(PartII)

PartIORACLESQL

1.什么是SQL?

SQL,翻譯成中文就是“結(jié)構(gòu)化查詢語言”。其發(fā)音是“S-Q-L”，也可以讀

作"sequel”。

SQL是一種計算機(jī)語言，它是為了從存儲在關(guān)系數(shù)據(jù)庫中的數(shù)據(jù)中獲得信息

而設(shè)計的，即通過SQL可以從大量收集的數(shù)據(jù)中查找想要的信息。

SQL不同于其他計算機(jī)語言，我們使用SQL描述想要的信息類型，然后計算機(jī)

會確定出獲得它的最好過程，并運行這個過程。這就是所謂的“說明性”計算機(jī)

語言，因為它注重的是結(jié)果：我們可以詳細(xì)說明結(jié)果的外表特征。計算機(jī)允許使

用任意數(shù)據(jù)處理方法，只要它獲得正確結(jié)果。

其他計算機(jī)語言大部分是“過程性”的，如匯編、Pascal,C、Java等。我

們使用這些語言描述應(yīng)用于數(shù)據(jù)的過程，無需描述結(jié)果。結(jié)果是將過程應(yīng)用到數(shù)

據(jù)后所得到的東西。

這好比我們清晨去一家咖啡店。如果使用SQL支持的說明性方法，你只要說:

“我想來一杯咖啡和一個炸面包圈?！倍褂眠^程性方法，你不能那樣說，你必

須說出如何得到結(jié)果，并且給它一個特定的過程。也就是說，你必須說出如何制

作咖啡以及炸面包圈。因此，對于咖啡，你必須說：“將一些烘烤好的咖啡豆磨

成粉，添加開水，沖泡一會兒，再將它倒入一個茶杯，然后給我?！睂τ谡姘?/p>

圈，你必須閱讀菜譜。顯然，說明性方法更貼近于我們通常說話的方式，并且它

更容易于為大多數(shù)人所使用。

相對于其他計算機(jī)語言而言，SQL是易于使用的，實際上這也正是SQL如此流

行和重要的主要原因。

信息自身并不強(qiáng)大，只有在需要的人使用它的時候，它才變得強(qiáng)大。SQL是

傳遞信息的工具。

計算機(jī)實用技術(shù)（PartII）

注釋：關(guān)于SQL

■SQL是從關(guān)系數(shù)據(jù)庫中獲得信息的說明性語言。

■SQL會告訴獲得什么信息，而不是告訴如何獲得信息。

■SQL的基本知識是易于掌握的。

■SQL使得人們能夠控制信息。

■SQL允許人們使用新的方式來處理信息。

■SQL通過向需要的人們提供信息來使信息變得強(qiáng)大。

2.什么是關(guān)系數(shù)據(jù)庫？為什么要使用它?

關(guān)系數(shù)據(jù)庫是一種在計算機(jī)中用于組織數(shù)據(jù)的方法。

SQL是使人們將數(shù)據(jù)組織到關(guān)系數(shù)據(jù)庫中的一個主要原因，使用SQL,你可以

毫不費勁地從數(shù)據(jù)中獲得信息，這是非常重要的。

另外一個原因是：許多人可以在同一時間使用關(guān)系數(shù)據(jù)庫中的數(shù)據(jù)。有時，

兒百或者兒千人可以一起共享數(shù)據(jù)庫中的數(shù)據(jù)。所有人都可以看見數(shù)據(jù)，并且所

有人都可以更改數(shù)據(jù)（如果他們有這樣的權(quán)限的話）。從商業(yè)觀點來看，這提供

了一個協(xié)調(diào)所有員工的方法，并且讓每一個人都使用相同的信息主體。

第三個原因是：關(guān)系數(shù)據(jù)庫是為希望信息可以隨時間進(jìn)行更改而設(shè)計的。信

息可能需要重新組織或者新的信息需要被添加，關(guān)系數(shù)據(jù)庫的設(shè)計目標(biāo)是為了使

這種類型的更改變得容易。大多數(shù)計算機(jī)系統(tǒng)很難更改。因為它們假設(shè)你在開始

構(gòu)造之前就知道所有的請求。

從計算機(jī)語言的觀點來看，關(guān)系數(shù)據(jù)庫的靈活性和SQL的可用性使得開發(fā)新

的計算機(jī)應(yīng)用程序要比傳統(tǒng)技術(shù)更為迅速。

開發(fā)關(guān)系數(shù)據(jù)庫的想法出現(xiàn)于20世紀(jì)70年代早期，用來處理大量數(shù)據(jù)和數(shù)以

百萬計的紀(jì)錄。最初，關(guān)系數(shù)據(jù)庫被想象成后端處理器，它向使用過程性語言（如

C語言或COBOL語言）編寫的計算機(jī)應(yīng)用程序提供信息。即使到現(xiàn)在，關(guān)系數(shù)據(jù)庫

還遺留著這些痕跡。

不過今天，這種想法變得如此成功，以致于整個信息系統(tǒng)常常被構(gòu)造為關(guān)系

數(shù)據(jù)庫，并且無需很多過程代碼（除了支持輸入格式）。最初被開發(fā)為過程性代

碼做配角的關(guān)系數(shù)據(jù)庫現(xiàn)在已經(jīng)唱了主角，許多過程性代碼已經(jīng)不再需要。

-4-

計算機(jī)實用技術(shù)(PartII)

在關(guān)系數(shù)據(jù)庫中，所有數(shù)據(jù)都保存在表里，表是由列和行組成的二維結(jié)構(gòu)。

在使用了一段時間的表以后，你會發(fā)現(xiàn)表為處理數(shù)據(jù)提供了一個非常好的結(jié)構(gòu)。

它們易于更改，可以在同一時間與所有用戶共享數(shù)據(jù)，并且可以在表數(shù)據(jù)上運行

SQLo許多人開始從表的角度考慮他們的數(shù)據(jù)，表已經(jīng)成為處理數(shù)據(jù)時的主要手

段。

今天，人們使用小的個人數(shù)據(jù)庫來保護(hù)地址簿、為音樂磁帶做目錄、組織藏

書或者紀(jì)錄他們的財務(wù)情況。商業(yè)上應(yīng)用的數(shù)據(jù)庫也被建立為關(guān)系數(shù)據(jù)庫。許多

人更愿意將他們的數(shù)據(jù)存入一個數(shù)據(jù)庫中，即使其中只有少量的記錄。

關(guān)系數(shù)據(jù)庫的起源

■關(guān)系數(shù)據(jù)庫最初是在20世紀(jì)70年代開發(fā)的，開發(fā)目的是為了以一致并且

相關(guān)的方式來組織大量信息。

■關(guān)系數(shù)據(jù)庫允許兒千人在相同時間使用相同信息。

■關(guān)系數(shù)據(jù)庫總是保持信息的實時性和一致性。

■關(guān)系數(shù)據(jù)庫使單位中所有級別的人(從秘書到副總裁)都可以容易地獲

得信息。它們使用SQL、表單、標(biāo)準(zhǔn)化的報告和臨時報告，及時地將信

息傳遞給人們。

■關(guān)系數(shù)據(jù)庫是作為信息服務(wù)器后端設(shè)計的。這意味著大部分人將不會直

接使用數(shù)據(jù)庫，而用另一層的軟件。這個軟件從數(shù)據(jù)庫中獲得信息，然

后傳遞給需要的人們。

■關(guān)系數(shù)據(jù)庫使人們能夠在需要信息的時候獲得當(dāng)前信息。

今天——關(guān)系數(shù)據(jù)庫任何改變

■除了已經(jīng)描述的大型數(shù)據(jù)庫之外，現(xiàn)在還有許多處理較少信息量的小型

數(shù)據(jù)庫，它們可以被個人使用或兒個人分享。

■關(guān)系數(shù)據(jù)庫非常成功，易于使用，使用它的應(yīng)用程序要比原先設(shè)想的應(yīng)

用范圍大許多。

■現(xiàn)在許多人直接使用數(shù)據(jù)庫，而不是通過另一個軟件層來使用。

■許多人更愿意將他們的數(shù)據(jù)保存在數(shù)據(jù)庫中。他們覺得關(guān)系數(shù)據(jù)庫為處

理各種類型的數(shù)據(jù)提供了一個實用且高效的框架。

計算機(jī)實用技術(shù)(PartII)

3.為什么學(xué)習(xí)SQL?

SQL被運用于100多種軟件產(chǎn)品中。一旦學(xué)會了SQL,你將能夠使用所有這些

產(chǎn)品。當(dāng)然，你需要稍微了解一下每種產(chǎn)品的特性，很快你就會感覺到它們很熟

悉并且知道如何使用。你可重復(fù)使用這些技巧。

主要SQL產(chǎn)品其他SQL產(chǎn)品(和基于SQL的產(chǎn)品)

OracleMYSQL

MicrosoftAccessSQLbase

IBMDB2ColdFusion

MicrosoftSQLServerSAP

InformixBusinessObjects

SQLWindowsODBC

SybaseIngres

SASsqlprocedureOcelotSQL

FoxproOsloData

dBasePostgreSQL

TandemSQLRapidSQL

XDB

SQL/DS

MiniSQL

Empress

Interbase

Progress

Supra

SQLReportWriter

Paradox

Delphi

VAXSQL

Essbase

BeagleSQL

GNUSQLServer

JustLogic/SQL

PrimeBase

AlteraSQLServer

DataScope

PowerBuilder

SQL被廣泛使用的原因之一是：相對于其他許多計算機(jī)語言來說，SQL易于學(xué)

習(xí)。另一個原因是：它打開了關(guān)系數(shù)據(jù)庫的大門，而關(guān)系數(shù)據(jù)庫提供了許多便利。

一些人說：SQL是關(guān)系數(shù)據(jù)庫最好的特性，并且SQL是關(guān)系數(shù)據(jù)庫獲得成功的原因。

-6-

計算機(jī)實用技術(shù)(PartII)

而另一些人說：是關(guān)系數(shù)據(jù)庫使得SQL成功。大部分人同意SQL和關(guān)系數(shù)據(jù)庫是一

個成功組合的觀點。

SQL是最成功的說明性計算機(jī)語言種你可以對它說你想要什么，而不

是告訴它如何得到你想要東西的語言。雖然也有其他一些說明性語言和報告生成

工具，但它們大部分在功能上有更多的限制。SQL更強(qiáng)大，可以應(yīng)用于更多的場

合。

在SQL基礎(chǔ)上易于構(gòu)建最終用戶程序，幫助不懂SQL語言的用戶從數(shù)據(jù)庫中獲

得信息。

4.SQL語句的分類

SQL使用一種很簡單的語法，易于學(xué)習(xí)和使用。SQL語句可分為5類，簡要概

括如下：

?查詢語句用于檢索數(shù)據(jù)庫表中存儲的行?？梢允褂肧QL的SELECT語句

編寫查詢語句。

?數(shù)據(jù)操縱語言(DataManipulationLanguage,DML)語句用于修改表

的內(nèi)容。DML語句有3種：

?INSERT向表中添加行。

?UPDATE修改行的內(nèi)容。

?DELETE刪除行。

?數(shù)據(jù)定義語言(DataDefinitionLanguage,DDL)語句用于定義構(gòu)成

數(shù)據(jù)庫的數(shù)據(jù)結(jié)構(gòu)，例如表。DDL語句有5種基本類型：

?CREATE創(chuàng)建數(shù)據(jù)庫結(jié)構(gòu)。例如，CREATETABLE語句用于創(chuàng)建一個

表；CREATEUSER用于創(chuàng)建一個數(shù)據(jù)庫用戶。

?ALTER修改數(shù)據(jù)庫結(jié)構(gòu)。例如，ALTERTABLE語句用于修改一個表。

?DROP刪除數(shù)據(jù)庫結(jié)構(gòu)。例如，DROPTABLE語句用于刪除表。

?RENAME更改表名。

?TRUNCATE刪除表的全部內(nèi)容。

?事務(wù)控制(TransactionControl,TC)語句用于將對行所作的修改永

久性地存儲到表中，或者取消這些修改操作。TC語句有3種：

-7-

計算機(jī)實用技術(shù)（Partll）

?COMMIT水久性地保存對行所作的修改。

?ROLLBACK取消對行所作的修改。

?SAVEBACK設(shè)置一個“保存點”，可以將對行所作的修改回滾到此處。

?數(shù)據(jù)控制語言（DataControlLanguage,DCL）語句用于修改數(shù)據(jù)庫結(jié)

構(gòu)的操作權(quán)限。DCL語句有2種：

?GRANT授予用戶對數(shù)據(jù)庫結(jié)構(gòu)（例如表）的訪問權(quán)限。

?REVOKE收回用戶對數(shù)據(jù)庫結(jié)構(gòu)（例如表）的訪問權(quán)限。

有很多方法都可以運行SQL語句，并從數(shù)據(jù)庫中返回結(jié)果，其中包括使用

OracleForms>Delphi>PowerBuiIder等設(shè)計的程序；還可以通過JDBC在Java

程序中加入SQL語句。

Oracle還有一個名為SQL*Plus的工具，可以使用這個工具從鍵盤輸入SQL語

句，或者提供一個包含SQL語句的文件，并在SQL*Plus中運行這些語句。通過

SQWlus,可以與數(shù)據(jù)庫進(jìn)行“對話”，因為可以輸入SQL語句，并查看數(shù)據(jù)庫

所返回的結(jié)果。

5.使用SQL*Plus

SQL*Plus有兩個版本：一個是Windows版本，另外一個是命令行版本?？梢?/p>

在任何Oracle數(shù)據(jù)庫的操作系統(tǒng)上使用命令行版本的SQL*Plus。

?啟動Windows版本的SQL*Plus

如果使用Windows操作系統(tǒng)，雙擊桌面的SQL*Plus圖標(biāo)，或單

SQLPlus

擊開始菜單，并選擇程序IOracleIApplication

Development|SQL*Plus來啟動SQL*Plus（如圖1）。

冠

ApplicationDevelopmentINTYPEFileAssistant

國

aConfigurationandMigrationToolsOracleObjectsforOLEClassLibraryHelp

..*與f

dEnterpriseManagementPacksOracleObjectsforOLEHelp

dEnterpriseManagerQuickTours?OracleObjectsforOLEReadme

f守

lIntegratedManagementToolsOracleODBCHelp

&OracleHTTPServer蜀OraOLEDBDocumentation

EnterpriseManagerConsoleOraOLEDBReadme

，

囪

ReleaseDocumentation

SQLPlusWorksheet

程序菜單啟動SQL*Plus

-8-

計算機(jī)實用技術(shù)(PartII)

圖2顯示了在Windows操作系統(tǒng)上運行SQL*Plus時出現(xiàn)的LogOn對話框。在用戶

名稱(UserName)中輸入：s30101,在口令(Password)中輸入:student,主機(jī)字

符串(HostString)輸入:oracle9i用來告訴SQL*Plus數(shù)據(jù)庫在哪里運行，然

后單擊確定。

圖2SQL*Plus登錄窗口

成功登錄到數(shù)據(jù)庫中之后，就會看到SQL*Plus窗口，可以通過這個窗口與數(shù)據(jù)庫

進(jìn)行交互。SQL*Plus窗口如圖3所示。

圖3SQL*Plus

-9-

計算機(jī)實用技術(shù)(PartII)

?啟動命令行版本的SQL*Plus

要啟動命令行的SQL*Plus,可以使用sqlplus命令。sqlplus命令的完整語法

如下：

Sqlplus[user_name[/password[@host_sting]]]

其中：

,usejname指定數(shù)據(jù)庫的用戶名。

,password指定該數(shù)據(jù)庫用戶的密碼。

,host_string指定要連接的數(shù)據(jù)庫。

下面是執(zhí)行sqlplus命令的例子：

sqlpluss30101/student@oracle9i

?使用SQL*Plus執(zhí)行SELECT語句

使用SQL*Plus登錄到數(shù)據(jù)庫之后，輸入下面的SELECT語句，會返回數(shù)據(jù)庫中

的當(dāng)前時間：

SELECTSYSDATEFROMdual;

SYSDATA是一個內(nèi)置Oracle函數(shù)，它返回當(dāng)前日期，dual表是Oracle的一

個內(nèi)置表，該表只包含一行可以使用dual表來執(zhí)行一些簡單的查詢，這些查詢的

結(jié)果并非從特定表中獲得。

SQL>SELECTsysdate

2FROMdual;

SYSDATE

27-SEP-05

SQL>

通過輸入EDIT命令，可以編輯SQL*Plus中的最后一條SQL語句。在輸錯SQL

語句或想修改SQL語句時，這種功能非常有用。在Windows系統(tǒng)中輸入EDIT命令后，

就會啟動記事本，然后就可以使用記事本來編輯SQL語句。在退出記事本并保存

SQL語句時，SQL語句就會被傳遞到SQL*Plus中，可以用/重新執(zhí)行該條SQL語句。

-10-

計算機(jī)實用技術(shù)（PartII）

6.用于示例的表（EMP和DEPT）

后面課程中在介紹SQL語句時所引用的都是以下兩個表。

EMP表（14條記錄），存放員工的基本信息。

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO

7369SMITHCLERK790217-Dec-80100020

7499ALLENSALESMAN769820-Feb-81180030030

7521WARDSALESMAN769822-Feb-81145050030

7566JONESMANAGER783902-Apr-81317520

7654MARTINSALESMAN769828-Sep—811450140030

7698BLAKEMANAGER7839Ol-May-81305030

7782CLARKMANAGER783909-Jun-81265010

7788SCOTTANALYST756609-Dec-82320020

7839KINGPRESIDENT17-Nov-81520010

7844TURNERSALESMAN769808-Sep-811700030

7876ADAMSCLERK778812-Jan-83130020

7900JAMESCLERK769803-Dec-81115030

7902FORDANALYST756603-Dec-81320020

7934MILLERCLERK778223-Jan-82150010

DEPT表（4條記錄），存放部門的信息

DEPTNODNAMELOC

10ACCOUNTINGNEWYORK

20RESEARCHDALLAS

30SALESCHICAGO

40OPERATIONSBOSTON

7.退出SQL*Plus

可以使用EXIT命令退出SQL*Plus。

SQL>EXIT

-11-

計算機(jī)實用技術(shù)(PartII)

8.基本的SQL語句

SQL>SELECTjob,SUM(sal)PAYROLL

2FROMemp

3WHEREjobNOTLIKE1SALES%'

4GROUPBYjob

5HAVINGSUM(sal)>5000

6ORDERBYSUM(sal);

SQL語句的簡單規(guī)則

?除非特別說明，SQL語句不區(qū)分大小寫

?SQL語句可以在一行或多行輸入

?關(guān)鍵詞不能跨行分開或縮寫

?為了方便閱讀或編輯，語句通常被分為若干行

?Tab和縮排用來增加代碼的可讀性

?通常關(guān)鍵詞用大寫；其他的詞，如表名和列名用小寫

?在SQL*Plus里，在SQL提示后輸入第一條SQL語句，而隨后的行被編號。這叫做SQLbuffer。

無論何時在buffer里當(dāng)前的語句只有一條

?在語句的結(jié)尾用分號(；)。

排除重復(fù)的行

SQL>SELECTDISTINCTdeptno,job

2FROMemp;

DEPTNOJOB

10CLERK

10MANAGER

10PRESIDENT

20ANALYST

9rowsselected.

SQL和SQL*Plus的比較

SQLSQL*Plus

是一種語言，用來和服務(wù)器通信存取數(shù)據(jù)的識別SQL語句，并把它們發(fā)送給服務(wù)器

是建立在美國國家標(biāo)準(zhǔn)協(xié)會(ANSI)的標(biāo)準(zhǔn)SQL是Oracle為了執(zhí)行SQL語句提供的接口

之上的

可以操縱定義在數(shù)據(jù)庫里的數(shù)據(jù)和基表不允許操縱存儲在數(shù)據(jù)庫里的數(shù)據(jù)的值

可以輸入一行或多行存儲在SQLbuffer里一次只輸入一行；不存儲在SQLbuffer里

不能有連接符如果命令超過一行可以用破折號(-)作為連接符

不能縮寫可以縮寫

在使用終止符后將立刻執(zhí)行不需要終止符；命令會立刻執(zhí)行

可以用函數(shù)來處理數(shù)據(jù)格式的工作用命令來改變數(shù)據(jù)的格式

-12-

計算機(jī)實用技術(shù)(PartII)

9.Where,Orderby子句

ISNULL操作

SQL>SELECTename,mgr

2FROMemp

3WHEREmgrISNULL;

ENAMEMGR

KING

BETWEEN操作

SQL>SELECTename,sal

2FROMemp

3WHEREsalBETWEEN1000AND1500;

ENAMESAL

MARTIN1250

TURNER1500

WARD1250

ADAMS1100

MILLER1300

IN操作

SQL>SELECTempno,enamezsal,mgr

2FROMemp

3WHEREmgrIN(7902,7566,7788);

EMPNOENAMESALMGR

7902FORD30007566

7369SMITH8007902

7788SCOTT30007566

7876ADAMS11007788

LIKE操作

SQL>SELECTename

2FROMemp

3WHEREenameLIKE

ENAME

JAMES

WARD

NOT操作

SQL>SELECTename,job

2FROMemp

111

3WHEREjobNOTIN(CLERKzMANAGER*,'ANALYST');

ENAMEJOB

KINGPRESIDENT

MARTINSALESMAN

-13-

計算機(jī)實用技術(shù)(Partll)

ALLENSALESMAN

TURNERSALESMAN

WARDSALESMAN

按多列進(jìn)行排序

SQL>SELECTename,deptno,sal

2FROMemp

3ORDERBYdeptno,salDESC;

ENAMEDEPTNOSAL

KING105000

CLARK102450

MILLER101300

FORD203000

14rowsselected.

10.SQL函數(shù)

單行函數(shù)

?操縱數(shù)據(jù)項

?接受變量并返回一個值

?對每一行的返回值起作用

?每行返回一個結(jié)果

?改變數(shù)據(jù)類型

?能夠被嵌套

函數(shù)結(jié)果

LOWER('OracleEducation')oracleeducation

UPPERCOracleEducation')ORACLEEDUCATION

INITCAPCORACLEeducation')OracleEducation

CONCATCCertifkdJDBA，)CertifiedDBA

SUBSTRCAdministrator\1,5)Admin

LENGTH(4Administratof)13

INSTR，Oracle'「c')4

LPAD(sal,10,'*')******5000

ROUND(78.926,2)78.93

TRUNC(78.926,2)78.92

MOD(2100,500)100

SQL>SELECT*Thejobtitlefor1||INITCAP(ename)||*is

2||LOWER(job)AS"EMPLOYEEDETAILS"

3FROMemp;

EMPLOYEEDETAILS

ThejobtitleforKingispresident

ThejobtitleforBlakeismanager

ThejobtitleforClarkismanager

14rowsselected.

-14-

計算機(jī)實用技術(shù)(PartII)

Date函數(shù)

函數(shù)結(jié)果說明

MONTHS_BETWEEN('01-SEP-95:11-JAN-94')19.6774194計算兩個日期之間的月數(shù)

ADD.MONTHS('11-JAN-94',6)'ll-JUL-94'計算指定日期后若干月的日期

NEXT_DAY('01-SEP-951,'FRIDAY')'08-SEP?95'計算指定日期后下一天的日期

LAST_DAY('01-SEP-95')'30-SEP-95'計算指定月份最后一天的日期

ROUND('25-JUL-95','MONTH')01-AUG-95舍入日期

ROUNDC25-JUL-95?YEAR)01-JAN-96

TRUNCC25-JUL-951,'MONTH1)01-JUL-95截取日期

TRUNC(125-JUL-95'；YEAR')01-JAN-95

SQL>SELECTempno,hiredatez

2MONTHS_BETWEEN(SYSDATE,hiredate)TENURE,

3ADD_MONTHS(hiredate,6)REVIEW,

4NEXT_DAY(hiredate,'FRIDAY'),LAST_DAY(hiredate)

5FROMemp

6WHEREMONTHS_BETWEEN(SYSDATE,hiredate)<200;

EMPNOHIREDATETENUREREVIEWNEXT_DAYLAST_DAY

783917-NOV-81192.2479417-MAY-8220-NOV-8130-NOV-81

769801-MAY-81198.7640701-NOV-8108-MAY-8131-MAY-81

11rowsselected.

帶日期的TO—CHAR函數(shù)

要素說明

SCCorCC世紀(jì)；以s為前綴，用-表示公元前的日期

YearsindatesYYYYorSYYYY年；以S為前綴，用-表示公元前的口期

YYYorYYorY年的后3、2或1位數(shù)字

Y,YYY在這個位置加逗號的年

IYYY,IYY,IY,I以ISO標(biāo)準(zhǔn)為基礎(chǔ)的4,3,2,或1位數(shù)字的年

SYEARorYEAR完整拼寫的年；以S為前綴，用-表示公元前的口期

BCorAD加上BC/AD指示

B.C.orA.D.加上帶句點的BC/AD指示

Q四分之一年

MM2位數(shù)字的月

MONTH用9個字符長度(不夠用空格填充)拼寫的月

MON以三個字母的縮寫表示的月

RM羅馬數(shù)字表示的月

WWorW年或月的星期

DDDorDDorD年、月或星期的某日

DAY用9個字符長度(不夠用空格填充)拼寫的日

DY以三個字母的縮寫表示的日

J侏略日；從公元前4713年12月31日開始計算

AMorPM加上上下午指示

A.M.orP.M.加上帶句點的上下午指示

HHorHH12orHH24時、時(1-12)或時(0-23)

MI分(0-59)

SS秒(0-59)

SSSSS午夜之后的秒數(shù)(0-86399)

-15-

計算機(jī)實用技術(shù)（PartII）

TH序數(shù)(例如，DDTHfor4TH)

SP完整拼寫的數(shù)字（例如，DDSPforFOUR）

SPTHorTHSP完整拼寫的序數(shù)（例如,DDSPTHforFOURTH）

fm清除空格或打頭的零

SQL>SELECTename,

2TO_CHAR(hiredate,'fmDDMonthYYYY1)HIREDATE

3FROMemp;

ENAMEHIREDATE

KING17November1981

BLAKE1May1981

CLARK9June1981

JONES2April1981

MARTIN28September1981

ALLEN20February1981

14rowsselected.

SQL>SELECTename,

n

2TO_CHAR(hiredatez'fmDdspth"ofMonthYYYY:SSAM*)

3HIREDATE

4FROMemp;

ENAMEHIREDATE

KINGSeventeenthofNovember198112:00:00AM

BLAKEFirstofMay198112:00:00AM

14rowsselected.

帶數(shù)字的TO_CHAR函數(shù)

要素說明例子結(jié)果

9數(shù)字的位數(shù)（9的數(shù)目決定顯示的寬度）9999991234

0首位加零顯示099999001234

$浮動的美元符號$999999$1234

L浮動的當(dāng)?shù)刎泿欧朙999999FF1234

小數(shù)點的位置999999.991234.00

>逗號的位置999,9991,234

MI減號置于右邊（負(fù)數(shù)999999MI1234-

PR作為插入成分插入的負(fù)數(shù)999999PR<1234>

EEEE科學(xué)計數(shù)法（格式必須是4個E）99.999EEEE1.234E+03

V10的n次塞（n=V后9的個數(shù)）9999V99123400

B顯示零值為空格，而不是0B9999.991234.00

SQL>SELECTTO_CHAR(sal,,$99,999，)SALARY

2FROMemp

3WHEREename='SCOTT*;

SALARY

$3,000

-16-

計算機(jī)實用技術(shù)(Partll)

NVL函數(shù)

?將空值轉(zhuǎn)換為一個既定的值

?數(shù)據(jù)類型可以是口期、字符和數(shù)字

?數(shù)據(jù)類型一定要匹配

?NVL(comm,0)

?NVL(hiredate；01-JAN-97')

?NVL(job；NoJobYet')

SQL>SELECTename,sal,comm,(sal*12)+comm

2FROMemp;

ENAMEJOB(SAL*12)+COMM

KINGPRESIDENT

BLAKEMANAGER

CLARKMANAGER

JONESMANAGER

MARTINSALESMAN16400

14rowsselected.

SQL>SELECTename,sal,comm,(sal*12)+NVL(comm,0)

2FROMemp;

ENAMESALCOMM(SAL*12)+NVL(COMM,0)

KING500060000

BLAKE285034200

CLARK245029400

JONES297535700

MARTIN1250140016400

ALLEN160030019500

14rowsselected.

DECODE函數(shù)

SQL>SELECTjob,salz

2DECODE(job,1ANALYST1,SAL*1.1,

1

3CLERKISAL*1.15Z

41MANAGER',SAL*1.20,

5SAL)

6REVISED_SALARY

7FROMemp;

JOBSALREVISED.SALARY

PRESIDENT50005000

MANAGER28503420

MANAGER24502940

14rowsselected.

Nesting函數(shù)

SQL>SELECTenamez

2NVL(TO_CHAR(mgr),1NoManager1)

3FROMemp

4WHEREmgrISNULL;

-17-

計算機(jī)實用技術(shù)(PartII)

ENAMENVL(TO_CHAR(MGR),'NOMANAGER')

KINGNoManager

11.表的聯(lián)接

等值聯(lián)接

SQL>SELECTemp.empnoAemp.ename,emp.deptno,

2dept.deptno,dept.loc

3FROMemp,dept

4WHEREemp.deptno=dept.deptno;

EMPNOENAMEDEPTNODEPTNOLOC

7839KING1010NEWYORK

7698BLAKE3030CHICAGO

7782CLARK1010NEWYORK

7566JONES2020DALLAS

14rowsselected.

非等值聯(lián)接

SALGRADE表

GRADELOSALHISAL

17001200

212011400

314012000

420013000

530019999

SQL>SELECTe.enameze.salzs.grade

2FROMempe,salgrades

3WHEREe.sal

4BETWEENs.losalANDs.hisal;

ENAMESALGRADE

JAMES9501

SMITH8001

ADAMS11001

14rowsselected.

外聯(lián)接

SQL>SELECTe.ename,d.deptno,d.dname

2FROMempe,deptd

3WHEREe.deptno(+)=d.deptno

4ORDERBYe.deptno;

ENAMEDEPTNODNAME

KING10ACCOUNTING

CLARK10ACCOUNTING

40OPERATIONS

-18-

計算機(jī)實用技術(shù)(PartII)

15rowsselected.

自聯(lián)接

SQL>SELECTworker.ename||*worksfor'||manager.ename

2FROMempworker,empmanager

3WHEREworker.mgr=manager.empno;

WORKER.ENAME||'WORKSFOR1||MANAG

BLAKEworksforKING

CLARKworksforKING

JONESworksforKING

MARTINworksforBLAKE

13rowsselected.

使用表的別名

SQL>SELECTe.empno,e.ename,e.deptno,

2d.deptno,d.loc

3FROMempe,deptd

4WHEREe.deptno=d.deptno;

12.分組函數(shù)

分組函數(shù)的類型

函數(shù)說明

AVG(|DISTINCTIALL]n)計算n的平均值，忽略空值

統(tǒng)計行數(shù)，如果有表達(dá)式，則統(tǒng)計空值以外的行

COUNT({*1[DISTINCTIALL]表達(dá)式})數(shù)。用*將選擇統(tǒng)計所有的行，包括重復(fù)的和空

的行。

MAX([DISTINCTIALL]表達(dá)式)取表達(dá)式的最大值，忽略空值

MIN([DISTINCTIALL]表達(dá)式)取表達(dá)式的最小值，忽略空值

STDDEV(lDISTINCTIALLJx)計算n的均方差，忽略空值

SUM([DISTINCTIALL]n)計算n的和，忽略空值

VARIANCE([DISTINCTIALL]x)計算n的方差，忽略空值

SQL>SELECTAVG(sal)zMAX(sal),

2MIN(sal)zSUM(sal)

3FROMemp

4WHEREjobLIKE1SALES%';

AVG(SAL)MAX(SAL)MIN(SAL)SUM(SAL)

1400160012505600

SQL>SELECTCOUNT(*)

2FROMemp

3WHEREdeptno=30;

COUNT(*)

6

-19-

計算機(jī)實用技術(shù)(PartII)

GROUPBY子句

SQL>SELECTdeptno,AVG(sal)

2FROMemp

3GROUPBYdeptno;

DEPTNOAVG(SAL)

102916.6667

202175

301566.6667

SQL>SELECTdeptno,job,sum(sal)

2FROMemp

3GROUPBYdeptno,job;

DEPTNOJOBSUM(SAL)

10CLERK1300

10MANAGER2450

10PRESIDENT5000

20ANALYST6000

20CLERK1900

9rowsselected.

使用分組功能而導(dǎo)致的非法查詢

SQL>SELECTdeptno,COUNT(ename)

2FROMemp;

SELECTdeptno,COUNT(ename)

★

ERRORatline1:

ORA-00937:notasingle-groupgroupfunction

SQL>SELECTdeptno,AVG(sal)

2FROMemp

3WHEREAVG(sal)>2000

4GROUPBYdeptno;

WHEREAVG(sal)>2000

ERRORatline3:

ORA-00934:groupfunctionisnotallowedhere

HAVING子句

SQL>SELECTdeptno,max(sal)

2FROMemp

3GROUPBYdeptno

4HAVINGmax(sal)>2900;

DEPTNOMAX(SAL)

105000

203000

-20-

計算機(jī)實用技術(shù)（Partll）

13.子查詢

使用子查詢指南

SQL>SELECTename

2FROMemp

3WHEREsal>

4(SELECTsal

5FROMemp

6WHEREempno=7566);

ENAME

KING

FORD

SCOTT

?子查詢（內(nèi)部查詢）在主查詢前只執(zhí)行一次。

?子查詢的結(jié)果被用于主查詢（外部查詢）。

?子查詢要用圓括號括起。

?子查詢要放在比較運算符的右邊。

?子查詢里不要加ORDERBY子句。

?單行的F查詢用于單行操作。

?多行的子查詢用于多行操作。

單行比較運算

運算符含義

=等于

>大于

>=大于等于

<小于

<=小于等于

<>不等于

SQL>SELECT