Oracle數(shù)據(jù)庫命令操作(一)

Oracle數(shù)據(jù)庫命令操作

1,查看oracle版本:

select*fromv$version

2,重新進行數(shù)據(jù)庫連接

CONNECTSCOTT/TIGER@MYDBSCOTT登陸名TIGER口令

MYDB稱為網(wǎng)絡(luò)效勞名或稱為連接字符串

3,關(guān)閉或退出SQL*Plus

EXIT"或"QUT命令

4,查看SQL*Plus的環(huán)境參數(shù)。

通過SHOWALL命令可以查看

設(shè)置命令的格式為：SET參數(shù)［ONOFF|值］

5,設(shè)置輸出頁面的大小。

步驟1：輸入并執(zhí)行以下命令，觀察顯示結(jié)果：SELECT*FROMemp；

步腺2：在輸入?yún)^(qū)輸入并執(zhí)行以下命令：

SETPAGESIZE100

SETLINESIZE120或

SETPAGESIZE100LINESIZE120

步驟3：重新輸入并執(zhí)行以下命令，觀察顯示結(jié)果：

SELECT*FROMemp；

6,顯示當前用戶，輸入并執(zhí)行命令：

SHOWUSER執(zhí)行結(jié)果是:USER為"SCOTT"

7,使用SPOOL命令記錄操作內(nèi)容。

步驟1：執(zhí)行命令：SPOOLC:\TEST

步驟2：執(zhí)行命令：SELECT*FROMemp；

步驟3:執(zhí)行命令：SELECT*FROMdept；

步驟4：執(zhí)行命令：SPOOLOFF

步驟5：用記事本翻開C:\TEST.LST并查看內(nèi)容:

8,翻開/關(guān)閉查詢結(jié)果表頭的顯示，默認為ON。

Setheadingon/off：翻開/關(guān)閉查詢結(jié)果表頭的顯示，默認為ON。

Setfeedbackon/off：翻開/關(guān)閉查詢結(jié)果中返回行數(shù)的顯示，默認為ON。

Setechoon/off：翻開/關(guān)閉命令的回顯，默認為ON。

Set.timeon/off：翻開/關(guān)閉葉間顯示.默認為OFF-

9,使用DESCRIBE命令查看表結(jié)構(gòu)

DESCRIBE(可簡寫成DESC)來檢查耒的結(jié)構(gòu)信息。輸入并執(zhí)行

以下命令(emp為要顯示結(jié)構(gòu)的表名)：DESCRIBEemp

10,注釋的方法是：

*在一行的開頭處書寫REM,將一行注釋掉。

*在一行中插入“一”，將其后的內(nèi)容注釋掉。

*使用/**/,可以用來注釋任何一段內(nèi)容。

11,保存輸入?yún)^(qū)的內(nèi)容。

步驟1：在輸入?yún)^(qū)重新輸入命令：

SELECT*FROMemp；

SELECT*FROMdept；

步驟2：選擇“文件”菜單下的”將輸入另存為〃命令，彈出

文件存盤對話框。選擇正確的磁盤位置，為存盤的文件起一個名字。如果輸入?yún)^(qū)

中的內(nèi)容是SQL命令或命令序列，則使用擴展名.SQL;否則可以省略或以.TXT做

擴展名。在本例中，選SELECT.SQL做文件名。

12,創(chuàng)立新用戶USER1,口令為ABC123

CREATEUSERUSERIIDENTIFIEDBYABC123(createuseruserlidentifiedbyabcl23)

授予連接數(shù)據(jù)庫權(quán)限、創(chuàng)立表權(quán)限、創(chuàng)立存儲過程和表空間使用權(quán)限。

GRANTCONNECTTOUSERI；(grantconnecttouserl)

GRANTCREATETABLETOUSERI；(grantcreatetabletouserl)

GRANTCREATEPROCEDURETOUSERI；(grantcreateproceduretouser!)

GRANTUNLIMITEDTABLESAPCETOUSERI；(grantunlimitedtablesapcetouserl)

使用新賬戶登錄。

CONNECTUSER1/ABC123@MYDB

輸出結(jié)果：

已連接。

步驟5：以創(chuàng)立表的方式復制數(shù)據(jù)到新賬戶。

CREATETABLEEMPASSELECT*FROMSCOTT.EMP；(createtableempasselect*fromscott.emp)

CREATETABLEDEPTASSELECT*FROMSCOTT.DEPT；

CREATETABLESALGRADESASSELECT?FROM

SCOTT.SALGRADH；

13,每個賬戶對應(yīng)一個模式(賬戶.表)

以不同的賬戶登錄數(shù)據(jù)庫時，就進入了不同的模式

SELECT*FROMSCOTT.EMP；

14,顯示表的行號

輸入并執(zhí)行查詢：

SELECTrownum,enameFROMemp；

15,查詢語句中的加減乘除運算

加(+)、減(-)、乘(*)、除(/)

顯示雇員工資上浮20%的結(jié)果。

輸入并執(zhí)行查詢：

SELECTename,sal,sal?(1+20/100)FROMemp；

顯示結(jié)果為：

ENAMESALSAL*(l+20/100)

SMITH800960

ALLEN16001920

16,在查詢中使用列別名。

輸入并執(zhí)行：

SELECTenameAS"名稱"，sal"工資"FROMemp；

顯示結(jié)果為：

名稱工資

SMITH800

ALLEN160()

20,條件查詢

SELECTename,job,salEROMempWHEREjob=rSALESMANr；

SELECTename,job,salFROMempWHEREsal>=3000；

SELECTename,hiredateFROMempWHEREhiredate”'17月-82'；

SELECTename,job,salFROMempWHEREsal>1000ANDsal<2000；

SELECT*FROMempWHEREjob='CLERK'ORjob='MANAGER,；

SELECT*FROMempWHERENOTdep:no=10;

SELECT*FROMempWHERE（deptno=10ORdeptno=20）ANDsal<1500；

顯示工資在1000?2000之間的雇員信息。

輸入并執(zhí)行查詢：

SELECT*FROMempWHEREsalBETWEEN1000AND2000；

21,IN條件查詢使用

顯示職務(wù)為“SALESMAN',"CLERK'和"MANAGER”的雇員信息。（in前可以加not）

輸入并執(zhí)行查詢：

SELECT*FROMempWHEREjobIN（rSALESMAN1,'CLERK','MANAGER1）；

22,LIKE模糊查詢

顯示姓名以“S〃開頭的雇員信息如下（%代表0個或多個任意字符。一：代表一個任意字符。）

輸入并執(zhí)行查詢：

SELECT*FROMempWHEREenameLIKE'S%'；

顯示經(jīng)理編號沒有填寫的雇員

（判斷空值NULL）（也可ISnotNULL）

輸入并執(zhí)行查詢：

SELECTename,mgrFROMempWHEREmgrISNULL；

22,函數(shù)列表

使用求絕對值函數(shù)abso

SELECTabs(-5)FROMdual；

使用四舍五入函數(shù)roundo

SELECTround(45.923,2),round(45.923,0),round(45.923,-1)FROMdual；

使用求余數(shù)函數(shù)modo

SELECTmod(1600,300)FROMdual；

23,字符函數(shù)

如果不知道表的字段內(nèi)容是大寫還是小寫，可以轉(zhuǎn)換后比較。

輸入并執(zhí)行查詢：

SELECTempno,cname,doptnoFROMomp

WHERElower(ename)=，blake';

顯示雇員名稱和職務(wù)列表。

輸入并執(zhí)行查詢：

SELECTconcat(rpad(ename,15/.1),job)as職務(wù)列表FROMemp；

結(jié)果為：

職務(wù)列表

SMITHCLERK

ALLENSALESMAN

WARDSALESMAN

顯示名稱以“曠開頭的雇員，并將名稱轉(zhuǎn)換成以大寫開頭。

輸入并執(zhí)行查詢：

SELECTcmpno,initcap(ename),jobFROMemp

WHEREsubstr(ename,1,1)='W'；

結(jié)果為：

l-MPNOINITCAP(ENJOB

7521WardSALESMAN

顯示雇員名稱中包含“S”的雇員名稱及名稱長度。

輸入并執(zhí)行查詢；

SELECTempno,ename,length(ename)FROMemp

WHEREinstr(ename.S',1,1)>0；

24,日期函數(shù)

返回系統(tǒng)的當前日期。

輸入并執(zhí)行查詢：

SELECTsysdateFROMdual

返回2003年2月的最后一天。

輸入并執(zhí)行查詢：

SELECTlasl_day('08-2月-03')FROMdual；

假定當前的系統(tǒng)日期是2003年2月6日，求再過1000天的日期。

輸入并執(zhí)行查詢：

SELECTsysdate+1000AS"NEWDATE"FROMdual；

假定當前的系統(tǒng)日期是2003年2月6日，顯示部門10雇員的雇傭天數(shù)。

輸入并執(zhí)行查詢：

SELECTenamc,round(sysdatc-hircdatc)DAYS

FROMemp

WHEREdeptno=10；

25,類型轉(zhuǎn)換函數(shù)

自動轉(zhuǎn)換字符型數(shù)據(jù)到數(shù)值型。

輸入并執(zhí)行查詢：

SELECT'12.5r+llFROMdual；

自動轉(zhuǎn)換數(shù)值型數(shù)據(jù)到字符型。

執(zhí)行以下查洵：

SELECT'12.5'|111FROMdual；

26,日期類型轉(zhuǎn)換

將日期轉(zhuǎn)換成帶時間和星期的字符串并顯示。

執(zhí)行以下查詢：

SELECTTO_CHAR(sysdate,'YYYY-MM-DDHH24：MI：SSAMDY')FROMdual；

將日期顯示轉(zhuǎn)換成中文的年月日。

輸入并執(zhí)行查詢：

SELECTTO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"')FROMdual;

將雇傭日期轉(zhuǎn)換成字符串并按新格式顯示。

輸入并執(zhí)行查詢：

SELECTename,to.char(hiredate,'DDMonthYYYY')HIREDATE

FROMemp；

以全拼和序列顯示時間。

執(zhí)行以下查詢：

SELECTSYSDATE,to_char(SYSDATE,'yyyysp'),to_char(SYSDATE,'mmspth'),

to_char(SYSDATE,'ddth1)FROMdual；

時間顯示的大小寫。

步驟1:執(zhí)行以下查詢：

SELECTSYSDATE,to.char(SYSDATE,'yyyysp1)FROMdual；

結(jié)果為：

SYSDATETO_CHAR(SYSDATE,'YYYYSP')

07-2月-04twothousandfour

步驟2：執(zhí)行以下查詢：

SELECTto_char(SYSDATE,，Yyyysp,)FROMdual；

結(jié)果為：

SYSDATETOCHAR(SYSDATE,'YYYYSP')

TwoThousandFour

27,數(shù)字類型轉(zhuǎn)換

將數(shù)值轉(zhuǎn)換成字符串并按新格式顯示。

執(zhí)行以下查詢：

SELECTTO_CHAR(123.45/0000,00'),TO_CHAR(12345,rL9.9EEEE1)FROMdual；

結(jié)果為：

TO_CHAR(TO_CHAR(12345,1L9.9

0123.45RMB1.2E+04

將數(shù)值轉(zhuǎn)換成字符串并按新格式顯示。

執(zhí)行以下查詢：

SELECTTO_CHAR(sal,'$99.999')SALARYFROMemp

WHEREename=rSCOTT'；

結(jié)果為：

SALARY

$4,000

NVL：如果不為空，返回原值；為空，則返回給定的值。

使用nvl函數(shù)轉(zhuǎn)換空值。

執(zhí)行以下查詢：

SELECTename,nvl(job,'無')，nvl(hiredale,'01T月-97'),nvl(comm,0)FROMemp；

結(jié)果為:

ENAMENVL(JOB,'NNVL(HIREDANVL(COMM.O)

SMITHCLERK17-12月-800

ALLENSALESMAN20-2月-81300

Decode：將職務(wù)轉(zhuǎn)換成中文顯示。

執(zhí)行以下查詢：

SELECTename,decode(job,'MANAGER','經(jīng)理'，'CLERK'職員，'SALESMAN'推銷員'

'ANALYST','系統(tǒng)分析員‘，'未知')FROMemp；

userenv函數(shù)

*ISDBA：判斷會話用戶的角色是否為SYSDBA,是則返回TRUE。

*INSTANCE：返回會話連接的INSTANCE標識符。

*LANGUAGE：返回語言、地區(qū)、數(shù)據(jù)庫字符集信息。

*LANG：返回會話語言的ISO簡稱。

*TERMINAL：返回正在會話的終端或計算機的標識符,

返回用戶終端或系統(tǒng)標識信息。

執(zhí)行以下查詢：

SELECTuserenv('TERMINAL')FROMdual；

結(jié)果為：

ORASERVER

返回語言、地區(qū)、數(shù)據(jù)庫字符集信息。

執(zhí)行以1、查詢：

SELECTuserenv('LANGUAGE')FROMdual；

結(jié)果為：

SIMPLIFIEDCHINESECHINA.ZIIS16GBK

28,相等連接

顯示雇員的名稱和所在的部門的編號和名稱。

執(zhí)行以卜查詢：

SELECTemp.ename,emp.deptno,dept.dnameFROMemp,dept

WHEREemp.deptno=dept.deptno；

使用表別名。

執(zhí)行以下查詢：

SELECTename.e.deptno,dnameFROMempe,deptd

WHEREe.deptno=d.deptno：

29,外連接

外連操作符為(+)

使用外連顯示不滿足相等條件的記錄。

步驟1：顯示雇員的名稱、工資和所在的部門名稱及沒有任何雇員的部門。

執(zhí)行以下查詢：

SELECTename,sal,dnameFROMemp,dept

WHEREemp.deptno(+)=dep..deptno；

執(zhí)行結(jié)果為：

ENAMESALDNAME

SELECTename,sal,dnameFROMemp

rightouterjoindeptonemp.deptno=dept.deptno；

SELECTename,sal,dnameFROMemp

rightouterjoindeptonemp.deptno=dept,deptno；???

select*fromTBL_10200_FAC_0RDERBinnerjoinDTCXHDYAonB.MT_FAC_NAME=A.MC

30,統(tǒng)計查詢

分組函數(shù)中SUM和AVG只應(yīng)用于數(shù)值型的列,MAX,MI5I和COUNT可以應(yīng)用于字符、數(shù)值和日期類型的列。組函

數(shù)忽略列的空值。

使用GROUPBY從句可以對數(shù)據(jù)進行分組。所謂分組，就是按照列的相同內(nèi)容，將記錄劃分成組，對組

可以應(yīng)用組函數(shù)。

如果不使用分組，將對整個表或滿足條件的記錄應(yīng)用組函數(shù)。

在組函數(shù)中可使用DISTINCT或ALL關(guān)鍵字。ALL表示對所有非NULL值(可重復)進行運算(COUNT除外)。

DISTINCT表示對每一個非NULL值，如果存在重復值，則組函數(shù)只運算一次。如果不指明上述關(guān)鍵字，默認為

ALLo

求雇員總?cè)藬?shù)。

執(zhí)行以下查詢：

SELECTCOUNT(*)FROMemp；

求有傭金的雇員人數(shù)。

執(zhí)行以下查洵：

SELECTCOUNT(comm)FROMemp；

求部門10的雇員的平均工資。

執(zhí)行以下查詢：

SELECTAVG(sal)FROMempWHEREdeptno=10；

求最晚和最早雇傭的雇員的雇傭日期。

執(zhí)行以下查詢：

SELECTMAX(hiredate).MIN(hiredate)FROMemp；

求雇員表中不同職務(wù)的個數(shù)。

執(zhí)行以下查詢：

SELECTCOUNT(DISTINCTjob)FROMemp；

返回結(jié)果為：

COUNT(DISTINCTJOB)

5

按職務(wù)統(tǒng)計工資總和。

步驟1：執(zhí)行以下查詢：

SELECTSUM(sal)FROMempGROUPBYjob；

SELECTjob,SUM(sal)FROMempGROUPBYjob；

按部門和職務(wù)分組統(tǒng)計工資總和。

執(zhí)行以下查詢：

SELECTdeptno,job,sum(sal)FROMemp

GROUPBYdeptno,job；

統(tǒng)計各部門的最高工資，排除最高工資小于3000的部門。

執(zhí)行以下查詢：

SELECTdeptno,max(sal)FROMemp

GROUPBYdeptno

HAVINGmax(sal)>=3000；

注意：WHERE條件是1GROUPBY之前執(zhí)行,HAVING條件在分組后執(zhí)行。

按職務(wù)統(tǒng)計工資總和并排序。

執(zhí)行以下查詢：

SELECTjob職務(wù)，SUM(sal)工資總和FROMemp

GROUPBYjob

ORDERBYSUM(sal)；

注意：使用ORDERBY從句對統(tǒng)計的結(jié)果進行排序，ORDERBY從句要出現(xiàn)在語句的最后。

求各部門平均工資的最高值。

執(zhí)行以下查詢：

SELECTmax(avg(sal))FROMempGROUPBYdeptno；

31,子查詢

子查詢一般出現(xiàn)在SELECT語句的WHERE子句中,Oracle也支持在FROM或HAVING子句中出現(xiàn)子查詢。子

查詢比主查詢先執(zhí)行,結(jié)果作為主查詢的條件,在書寫上要用圓括號擴起來，并放在比較運算符的右側(cè)。子查詢

可以嵌套使用，最里層的查詢最先執(zhí)行。子查詢可以在SELECT、I'SERT、UPDATE、DELETE等語句中使用。

查詢比SCOTT工資高的雇員名字和工資。

執(zhí)行以下查詢：

SELECTename,salFROMemp

WHEREsal>(SELECTsalFROMempWHEREempno=7788)；

查詢和SCOTT同一部門且比他工資低的雇員名字和工資。

執(zhí)行以下查詢：

SELECTename,salFROMemp

WIIF.RF(SELECTsalFROMnmpWHEREnmpnn=77RR)

ANDdeptno=(SELECTdeptnoFROMempWHEREempno=7788)；

查詢工資高于平均工資的雇員名字和工資。

執(zhí)行以下查詢：

SELECTename,salFROMemp

WHEREsal>(SELECT/WG(sal)FROMemp)；

32,多行子查詢

如果子查詢返回多行的結(jié)果，則我們稱它為多行子查詢。多行子查詢要使用不同的比較運算符號，它們是IN、

ANY和ALL。

查詢工資低于任何一個"CLERK”的工資的雇員信息。

執(zhí)行以下查詢：

SELECTempno,ename,job,salFROMemp

WHEREsal<ANY(SELECTsalFROMempWHEREjob=1CLERK')

ANDjob<>'CLERK'；

查詢工資比所有的“SALESMAN”都高的雇員的編號、名字和工資。

執(zhí)行以下查詢：

SELECTempno,ename,salFROMemp

WHEREsal>/\LL(SELECTsalFROMempWHEREjob=1SALESMAN')；

查詢部門20中職務(wù)同部門10的雇員一樣的雇員信息。

執(zhí)行以下查詢：

SELECTempno,ename,jobFROMemp

WHEREjobIN(SELECTjobFROMempWHEREdeptno=10)

ANDdeptno=20；

查詢職務(wù)和SCOTT相同，比SCOTT雇傭時間早的雇員信息。

執(zhí)行以下查詢：

SELECTempno,ename,jobFROMemp

WHEREjob=(SELECTjobFROMempWHEREempno=7788)

ANDhiredate<(SELECThiredateFROMempWHEREempno=7788)；

33,多列子查詢

如果干查詢返回多列，則對應(yīng)的比較條件中也應(yīng)該出現(xiàn)多列，這種查詢稱為多列子查詢。以下是多列子查詢的訓

練實例。

【訓練8】查詢職務(wù)和部門與SCOTT相同的雇員的信息。

執(zhí)行以下查詢：

SELECTempno,cnamo,salFROMemp

WHERE(job,deptno)=(SELECTjob,deptnoFROMempWHEREempno=7788)；

34,在FROM從句中使用子查詢

在FROM從句中也可以使用子直詢，在原理上這與在WHERE條件中使用子查詢類似。

查詢雇員表中排在第6?9位置上的雇員。

執(zhí)行以下查詢：

SELECTename,salFROM(SELECTrownumasnum,ename,sellFROMempWHERErownum<=9)

WHEREnum>=6；

35,集合運算

多個查詢語句的結(jié)果可以做集合運算，結(jié)果集的字段類型、數(shù)量向順序應(yīng)該一樣。

查詢部門10和部門20的所有職務(wù)。

執(zhí)行以下查詢：

SELECTjobFROMempWHEREdeptno=10

UNION

SELECTjobFROMempWHEREdcptno=20；

查詢部門10和20中是否有相同的職務(wù)和工資。

執(zhí)行以下查詢：

SELECTjob,salFROMempWHEREdeptno=10

INTERSECT

SELECTjob,salFROMempWHEREdeptno=20；

查詢只在部門表中出現(xiàn)，但沒有在雇員表中出現(xiàn)的部門編號。

執(zhí)行以下查洵：

SELECTdeptnoFROMdept

MINUS

SELECTdeplnoEROMemp;

顯示人數(shù)最多的部門名稱。

輸入并執(zhí)行以下查詢：

SELECTDECODE(dnameSALES','銷售部'，'ACCOUNTING財務(wù)部，'RESEARCH研發(fā)部'，'未知’)

部門名

FROMemp,dept

WHEREemp.dcptno=dcpt.deptno

GROUPBYdname

HAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMempGROUPBYdeptno)；

顯示各部門的平均工資、最高工資、最低工資和總工資列表，并按平均工資上卜順序排序。

輸入并執(zhí)行以下查詢：

SELECTdname部門，AVG(sal)平均工資,MAX(sal)最高工資,MIN(sal)最低工資，SUM(sal)總工資

FROMemp,dept

WHEREemp.dcptno=dept.deptno

GROUPBYdname

ORDERBYAVG(sal)DESC；

36,插入數(shù)據(jù)

可以使用INSERT命令，向已經(jīng)存在的表插入數(shù)據(jù)，語法格式如下：

INSERTINTO表名［（字段列表）］｛VALUES（表達式1,表達式2,...）IQUERY語句｝;

插入字段的值的類型要和字段的類型一一對應(yīng)。字符串類型的字段值必須用單引號括起來

字符串類型的字段值超過定義的長度會出錯，最好在插入前進行長度校臉。

字段列表如果省略則代表全部字段。

將新雇員插入到emp表：

INSERTINTOemp（empno,ename,job）

VALUES（1000,'小李'，'CLERK'）；

其他沒有插入的字段，系統(tǒng)會填寫為表的默認值。如果在表的創(chuàng)立時沒有說明默認值，則將插入NULL值

日期型的數(shù)據(jù)默認格式為DD-MONTY,默認的世紀為當前的世紀，默認的時間為午夜12點。如果指定的世紀不

是本世紀或時間不是午夜12點，則必須使用TODATE系統(tǒng)函數(shù)對字符串進行轉(zhuǎn)換。

如果要插入表的全部字段，則表名后的字段列表可以省略

37,復制數(shù)據(jù)

另一種插入數(shù)據(jù)（相當于復制）方法的語法格式是：

INSERTINTO表名（字段列表）SELECT（字段名1,字段名2,...）FROM另外的表名；

該形式一次可以插入多行數(shù)據(jù)。

步驟1:創(chuàng)立一個新表manager

CREATETABLEmanagerASSELECTempno,ename,salFROMempWHEREjob="MANAGER'；

執(zhí)行結(jié)果：

表已創(chuàng)立。

步驟2：從emp表拷貝數(shù)據(jù)到manager：

INSERTINTOmanager

SELECTempno,ename,sal

FROMemp

WHEREjob='CLERK'；

38序列使用

使用INSERT語句時，可以通過序列來填寫某些數(shù)值型或字符型的列。序列是一個要預先定義的有序的數(shù)值序

列，應(yīng)該先建立一個序列，然后在插入語句中使用

插入數(shù)據(jù)中使用序列的練習。

步驟1：創(chuàng)立從2000起始，增量為1的序列abc：

CREATESEQUENCEabcINCREMENTBY1STARTWITH2000

MAXVALUE99999CYCLENOCACHE；

在INSERT語句使用序列，序列的名稱為abc：

INSERTINTOmanagerVALUES（abc.nextval,'小王'，2500）;

39,修改數(shù)據(jù)

修改數(shù)據(jù)的語句UPDATE對表中指定字段的數(shù)據(jù)進行修改，一般需要通過添加WHERE條件來限定要進行修改的

行，加果不添加WHERE條件，將對所有的行進行修改。

(1)修改數(shù)據(jù)的語句UPDATE的根本語法如下：

UPDATE表名SET字段名1二表達式1,字段名2二表達式2,...WHERE條件；

修改小李(編號為1000)的工資為3000<,

執(zhí)行以下的查詢：

UPDATEempSETsal=3000WHEREempno=1000；

如果修改的值沒有賦值或定義，將把原來字段的內(nèi)容清為NULL,若修改值的長度超過定義的長度，則會出錯。

(2)UPDATE語句的另外一種用法：

UPDATE表名SET(字段名1,字段名2,...)=SELECT(字段名1,字段名2,...)FROM另外的表名

WHERE條件；

【訓練4】根據(jù)其他表修改數(shù)據(jù)。

執(zhí)行以下的查詢：

UPDATEmanager

SET(ename,sal)=(SELECTename,salFROMempWHEREempno=7788)

WHEREempno=1000；

執(zhí)行結(jié)果：

40,刪除數(shù)據(jù)

刪除數(shù)據(jù)的根本語法如下：

DELETEFROM表名WHERE條件；

要從表中刪除滿足條件的記錄，WHERE條件一般不能省略，如果省略就會刪除表的全部數(shù)據(jù)。

【訓練1】刪除雇員編號為1000的新插入的雇員。

步驟1：刪除編號為1000的雇員；

DELETEFROMempWHEREempno=1000；

如果確實要刪除一個大表里的全部記錄，可以用TRUNCATE命令，它可以釋放占用的數(shù)據(jù)塊表空間，語法為：

TRUNCATETABLE表名；

徹底刪除manager表的內(nèi)容。

執(zhí)行以下的命令：

TRUNCATETABLEmanager；

TRUNCATETABLE命令用來刪除表的全部數(shù)據(jù)而不是刪除表，表依舊存在。

41,數(shù)據(jù)庫事務(wù)

事務(wù)是由相關(guān)操作構(gòu)成的一個完整的操作單元。兩次連續(xù)成功的COMMIT或ROLLBACK之間的操作，稱為一個

事務(wù)。在一個事務(wù)內(nèi)，數(shù)據(jù)的修改一起提交或撤銷，如果發(fā)生故障或系統(tǒng)錯誤，整個事務(wù)也會自動撤銷。

我們可以這樣理解數(shù)據(jù)庫事物：對數(shù)據(jù)庫所做的一系列修改，在修改正程中，暫時不寫入數(shù)據(jù)庫，而是緩存起來,

用戶在自己的終端可以預覽變化，直到全部修改完成，并經(jīng)過檢查確認無誤后，一次性提交并寫入數(shù)據(jù)庫，在提

交之前，必要的話所做的修改都可以取消。提交之后，就不能撤銷，提交成功后其他用戶才可以通過查詢?yōu)g覽數(shù)

據(jù)的變化。

以事務(wù)的方式對數(shù)據(jù)庫進行訪問，有如下的優(yōu)點：

*把邏輯相關(guān)的操作分成了一個組。

*在數(shù)據(jù)永久改變前，可以預覽數(shù)據(jù)變化。

*能夠保證數(shù)據(jù)的讀一致性。

數(shù)據(jù)庫事務(wù)處理可分為隱式和顯式兩種。顯式事務(wù)操作通過命令實現(xiàn)，隱式事務(wù)由系統(tǒng)自動完成提交或撤銷（回

退）工作，無需用戶的干預。

隱式提交的情況包括：當用戶正常退出SQL*Plus或執(zhí)行CREATE、DROP、GRANT、REV0K3等命令時會發(fā)

生事務(wù)的自動提交。

還有一種情況，如果把系統(tǒng)的環(huán)境變量AUTOCOMMIT設(shè)置為ON（默認狀態(tài)為OFF）,則每當執(zhí)行一條INSERT、DELETE

或UPDATE命令對數(shù)據(jù)進行修改后，就會馬上自動提交。設(shè)置命令格式如下：

SETAUTOCOMMITON/OFF

隱式回退的情況包括；當異常結(jié)束SQL+Plus或系統(tǒng)故障發(fā)生時，會發(fā)生事務(wù)的自動回退。

顯式事務(wù)處理的數(shù)據(jù)庫事務(wù)操作語句有3條，如表3-2所示。

COMMIT操作把多個步驟對數(shù)據(jù)庫的修改，一次性地永久寫入數(shù)據(jù)庫，代表數(shù)據(jù)庫事務(wù)的成功執(zhí)行。ROLLBACK操

作在發(fā)生問題時，把對數(shù)據(jù)庫已經(jīng)作出的修改撤消，回退到修改前的狀態(tài)。在操作過程中，一旦發(fā)生問題，如果

還沒有提交操作，則隨時可以使用ROLLBACK來撤消前面的操作。SAVEPOINT則用于在事務(wù)中間建立一些保存點，

ROLLBACK可以使操作回退到這些點上邊，而不必撤銷全部的操作。一旦COMMIT完成，就不能用ROLLBACK來取

消已經(jīng)提交的操作。一旦ROLLBACK完成，被撤消的操作要重做，必須重新執(zhí)行相關(guān)操作語句

如何開始一個新的事務(wù)呢？一般情況下，開始一個會話（即連接數(shù)據(jù)庫），執(zhí)行第一條SQL語句將開始一個新的事

務(wù)，或執(zhí)行COMMIT提交或ROLLBACK撤銷事務(wù),也標志新的事務(wù)的開始。另外，執(zhí)行DDL（如CREATE）或DCL命令

也將自動提交前一個事務(wù)而開始一個新的事務(wù)。

數(shù)據(jù)在修改的時候會對記錄進行鎖定，其他會話不能對鎖定的記錄進行修改或加鎖，只有當前會話提交或撤銷后,

記錄的鎖定才會釋放。

我們通過以下的訓練來為雇員SCCTT增加工資,SCOTT的雇員號為7788o

學習使用COMMIT和ROLLBACKo

步驟1：執(zhí)行以1、命令，提交尚未提交的操作：

COMMIT；

執(zhí)行結(jié)果：

提交完成。

顯示SCOTT的現(xiàn)有工資：

SELECTename,salFROMempWHEREempno=7788；

執(zhí)行結(jié)果：

ENAMESAL

SCOTT3000

步驟2：修改雇員SCOTT的工資：

UPDATEempSETsal=sal+100WHEREempno=7788；

執(zhí)行結(jié)果：

已更新1行。

顯示修改后的SCOTT的工資：

SELECTename,salFROMempWHEREempno=7788;

執(zhí)行結(jié)果：

ENAMESAL

SCOTT3100

步驟3：假定修改操作后發(fā)現(xiàn)增加的工資應(yīng)該為1000而不是100,為了取消剛做的操作，可以執(zhí)行以下命令:

ROLLBACK；

執(zhí)行結(jié)果：

回退已完成。

顯示回退后SCOTT的工資恢復為3000：

SELECTename,salFROMempWHEREempno=7788；

執(zhí)行結(jié)果：

ENAMESAL

SCOTT3000

步驟4：重新修改雇員scon的工資，工資在原有基礎(chǔ)上增加ioo。：

UPDATEempSETsal=sal+1000WHEREempno=7788；

執(zhí)行結(jié)果：

已更新1行。

顯示修改后SCOTT的工資：

SELECTename,salFROMempWHEREempno=7788；

執(zhí)行結(jié)果：

ENAMESAL

SCOTT4000

步躲5：經(jīng)查看修改結(jié)果正確，提交所做的修改：

COMMIT；

執(zhí)行結(jié)果：

提交完成。

說明：在執(zhí)行COMMIT后，工資的修改被永久寫入數(shù)據(jù)庫。本訓練的第1步，先使用COMMIT命令提交原

來的操作，同時標志一個新的事務(wù)的開始。

注意：在事務(wù)執(zhí)行過程中，隨時可以預覽數(shù)據(jù)的變化。

對于比較大的事務(wù)，可以使用SAVEPOINT命令在事務(wù)中間劃分一些斷點，用來作為回退點。

【訓練2】學習使用SAVEPOINT命令。

步驟1：插入一個雇員：

INSERTINTOemp(empno,ename,job)

VALUES(3000,'小馬'/STUDENT)；

執(zhí)行結(jié)果：

已創(chuàng)立1行。

步驟2：插入保存點，檢查點的名稱為PA：

SAVEPOINTpa；

執(zhí)行結(jié)果：

保存點已創(chuàng)立。

步驟3：插入另一個雇員：

INSERTINTOemp(empno,ename,job)

VALUES(3001,'小黃'，'STUDENT')；

執(zhí)行結(jié)果：

已創(chuàng)立1行。

步躲4：回退到保存點PA,則后插入的小黃被取消，而小馬仍然保存。

ROLLBACKTOpa；

執(zhí)行結(jié)果：

回退已完成。

步驟5：提交所做的修改：

COMMIT；

執(zhí)行結(jié)果：

提交完成。

說明：第4步的回退，將回退到保存點PA,即第3步被撤銷。所以最后的COMMIT只提交了對小馬的插

入。請自行檢查插入的雇員。

在Oracle數(shù)據(jù)庫中，有一個叫回滾段的特殊的行儲區(qū)域。在提交一個事物之前，如果用戶進行了數(shù)據(jù)的修改，

在所謂的回滾段中將保存變化前的教據(jù)。有了回滾段才能在必要時使用ROLLBACK命令或自動地進行數(shù)據(jù)撤銷。

在提交事物之前，用戶自己可以看到修改的數(shù)據(jù)，但因為修改還沒有最終提交，其他用戶看到的應(yīng)該是原來的數(shù)

據(jù)，也就是回滾段中的數(shù)據(jù)，這時用戶自己看到的數(shù)據(jù)和其他用戶看到的數(shù)據(jù)是不同的，只有提交發(fā)生后，變化

的數(shù)據(jù)才會被寫入數(shù)據(jù)庫，此時用戶自己看到的數(shù)據(jù)和其他用戶看到的數(shù)據(jù)才是一致的，這叫做數(shù)據(jù)的讀一致性。

當?shù)?個SQL*Plus會話刪除一條后，第2個SQL*Plus會話仍然可以看到該條記錄，直到第1個SQL*Plus會話

提交（COMMIT）該刪除操作后，兩個會話看到的才是一致的數(shù)據(jù)。

42,表的鎖定

鎖出現(xiàn)在數(shù)據(jù)共享的場合，用來保證數(shù)據(jù)的一致性。當多個會話同時修改一個表時，需要對數(shù)據(jù)進行相應(yīng)的鎖

定。

鎖有“只讀鎖”、"排它鎮(zhèn)”，"共享排它鎖〃等多種類型，而且每種類型又有“行級鎖〃（一次鎖住

一條記錄），“頁級鎖〃（一次鎖住一頁，即數(shù)據(jù)庫中存儲記錄的最小可分配單元），“表級鎖”（鎖住整個表）。

若為“行級排它鎖"，則除被鎖住的行外，該表中其他行均可被其他的用戶進行修改（Update）或刪除（delete）。

若為“表級排它鎖"，則所有其他用戶只能對該表進行查詢（select）操作，而無法對其中的任何記錄進行修改或

刪除。當程序?qū)λ龅男薷牡男刑峤唬╟ommit）或回?；?。llback）后,鎖住的資源便會得到釋放，從而允許其他用

戶進行操作。

如果兩個事務(wù)，分別鎖定一局部數(shù)據(jù)，而都在等待對方釋放鎖才能完成事務(wù)操作，這種情況下就會發(fā)生死鎖。

在Oracle數(shù)據(jù)庫中，修改數(shù)據(jù)操作時需要一個隱式的獨占鎖，以鎖定修改的行，直到修改被提交或撤銷為止。

如果一個會話鎖定了數(shù)據(jù)，那么第二個會話要想對數(shù)據(jù)進行修改，只能等到第一個會話對修改使用COMMIT命令

進行提交或使用ROLLBACK命令進行回滾撤銷后，才開始執(zhí)行。因比應(yīng)養(yǎng)成一個良好的習慣：執(zhí)行修改操作后，

要盡早地提交或撤銷，以免影響其他會話對數(shù)據(jù)的修改。

主動鎖表：

鎖定行：

對emp表的部門10的雇員記錄加顯式鎖，并測試。

步驟1：對部門10加顯式鎖：

SELECTempno,ename,job,salFROMempWHEREdeptno=10FORUPDATE；

步驟2：啟動第二個SQL*Plus（第二個會話），以SCOTT賬戶登錄數(shù)據(jù)庫，對部門10的雇員CLARK進行修改操作。

UPDATEempSETsal=sal+l（）0whereempno=7782；

執(zhí)行結(jié)果：

沒有任何輸出(處于等待解鎖狀態(tài))。

步驟3：在第一個會話詵行解鎖操作：

COMMIT；

步驟4：查看第二個會話，有輸出結(jié)果：

已更新1行。

說明：步驟1對選定的部門10的雇員加鎖，之后其他會話不能對部門10的雇員數(shù)據(jù)進行修改或刪除。如果此時

要進行修改或刪除，則會處于等待狀態(tài)。使用COMMIT語句進行解鎖之后，如果有掛起的修改或刪除操作，則等

待的操作此時可以執(zhí)行。

鎖定表：

LOCK語句用于對整張表進行鎖定。語法如下：

LOCKTABLE表名IN{SHARE|EXCLUSIVE}MODE

對表的鎖定可以是共享(SHARE)或獨占(EXCLUSIVE)模式。共享模式下，其他會話可以加共享鎖，但不

能加獨占鎖。在獨占模式下，其他會話不能加共享或獨占鎖。

【訓練】】對enip表添加獨占鎖。

步驟1：對emp表加獨占鎖：

LOCKTABLEempINEXCLUSIVEMODE；

結(jié)果為：

表已鎖定。

步驟2：對表進行解鎖操作：

COMMIT；

說明：當使用LOCK語句顯式鎖定一張表時，死鎖的概率就會增加。同樣地，使用COMMIT或ROLLBACK

命令可以釋放鎖。

注意：必須沒有其他會話對該表的任何記錄加鎖，此操作才能成功。

43,表的創(chuàng)立

表的創(chuàng)立需要CREATETABLE系統(tǒng)權(quán)限，表的根本創(chuàng)立語法如下：

CREATETABLE表名

(列名數(shù)據(jù)類型(寬度)[DEFAULT表達式][COLUMNCONSTRAINT],

[TABLECONSTRAINT]

[TABLEPARTITIONCLAUSE]

);

創(chuàng)立表最主要的是要說明表名、列名、列的數(shù)據(jù)類型和寬度，多列之間用“，”分隔?？梢允怯弥形幕蛴⑽淖鳛?/p>

表名和列名。表名最大長度為30個字符。在同一個用戶下，表不能重名，但不同用戶表的名稱可以相重。另外,

表的名稱不能使用Oracle的保存字，在一張表中最多可以包含2000列。該語法中的其他局部根據(jù)需要添加，作

用如卜：

DEFAULT表達式：用來定義列的默認值。

COLUMNCONSTRAINT：用來定義列級的約束條件。

TABLECONSTRAINT：用來定義表級的約束條件。

TABLEPARTITIONCLAUSE：定義表的分區(qū)子句。

創(chuàng)立圖書和出版社表。

步驟1：創(chuàng)立出版社表，輸入并執(zhí)行以下命令：

CREATETABLE出版社(

編號VARCHAR2(2),

出版社名稱VARCHAR2(30).

地址VARCHAR2地0),

聯(lián)系電話VARCHAR2(20)

)；

步鞭2：創(chuàng)立圖書表，輸入并執(zhí)行以下命令：

CREATETABLE圖書(

圖書編號VARCHAR2(5),

圖書名稱VARCHAR2(30),

出版社編號VARCHAR2(2),

作者VARCHAR2(10),

出版日期DATE,

數(shù)量NUMBER(3),

單價NUMBER(7,2)

);

2.通過子查詢創(chuàng)立表

如果要創(chuàng)立一個同已有的表結(jié)構(gòu)相同或局部相同的表，可以采用以下的語法：

CREATETABLE表名(列名...)ASSQL查詢語句;

該語法既可以復制表的結(jié)構(gòu)，也可以復制表的內(nèi)容，并可以為新表命名新的列名。新的列名在表名后的

括號中給出，如果省略將采用原來表的列名。復制的內(nèi)容由查詢語句的WHERE條件決定。

通過干查詢創(chuàng)立新的圖書表。

步驟1：完全復制圖書表到“圖書1”,輸入并執(zhí)行以下命令：

CREATETABLE圖書1ASSELECT*FROM圖書；

執(zhí)行結(jié)果：

表已創(chuàng)立。

步驟2：創(chuàng)立新的圖書表“圖書2〃，只包含書名和單價，輸入并執(zhí)行以下命令：

CREATETABLE圖書2(書名，單價)ASSELECT圖書名稱，單價FROM圖書；

步驟3：創(chuàng)立新的圖書表“圖書3”,只包含書名和單價，不復制內(nèi)容，輸入并執(zhí)行以下命令：

CREATETABLE圖書3(書名，單價)ASSELECT圖書名稱，單價FROM圖書WHERE1=2；

3.設(shè)置列的默認值

可以在創(chuàng)立表的同時指定列的默認值，這樣在插入數(shù)據(jù)時，如果不插入相應(yīng)的列，則該列取默認值，默

認值由DEFAULT局部說明。

1創(chuàng)立表時設(shè)置默認值。

步驟1：創(chuàng)立表時，設(shè)置表的默認值。

CREATETABLE圖書4(

圖書編號VARCHAR2(5)DEFAULTNULL,

圖書名稱VARCHAR2(30)DEFAULT'未知，

出版社編號VARCHAR2(2)DEFAULTNULL,

出版日期DATEDEFAULT'01-1月-1900',

作者VARCHAR2(10)DEFAULTNULL,

數(shù)量NUMBER(3)DEFAULT0,

單價NUMBER(7,2)DEFAULTNULL,

借出數(shù)量NUMBER(3)DEFAULT0

)；

4.刪除已創(chuàng)立的表

刪除表的語法如下：

DROPTABLE表名［CASCADECONSTRAINTS］；

表的刪除者必須是表的創(chuàng)立者或具有DROPANYTABLE權(quán)限。CASCADECONSTRAINTS表示當要刪除的表

被其他表參照時，刪除參照此表的約束條件。

44,表的操作

1.表的重命名

語法如下：

RENAME舊表名TO新表名:

2.清空表

清空表的語法為：

TRUNCATETABLE表名；

清空表可刪除表的全部數(shù)據(jù)并釋放占用的存儲空間。

3.添加注釋

(1)為表添加注釋的語法為：

COMMENTONTABLE表名IS

該語法為表添加注釋字符串。如IS后的字符串為空，則去除表注釋。

4為列添加注釋的語法為：

COMMENTONCOLUMN表名.列名IS'

該語法為列添加注釋字符串。如IS后的字符串為空，則去除列注釋。

為emp表的deptno列添加注釋："部門編號"。

COMMENTONCOLUMNemp.deptnoIS'部門編號

5查看表的結(jié)構(gòu)：

DESCRIBE表名；

DESCRIBE可以簡寫為DESC,

可以通過對數(shù)據(jù)字典USER_OBJECTS的查詢，顯示當前模式用戶的所有表。

顯示當前用戶的所有表。

SELECTobjectnameFROMuserobjectsWHEREobjecttypc="TABLE1；

45,數(shù)據(jù)完整性約束

表的數(shù)據(jù)有一定的取值范圍和聯(lián)系，多表之間的數(shù)據(jù)有時也有一定的參照關(guān)系。在創(chuàng)立表和修改表時，可通

過定義約束條件來保證數(shù)據(jù)的完整性和一致性。約束條件是一些規(guī)則，在對數(shù)據(jù)進行插入、刪除知修改時要對這

些規(guī)則進行驗證，從而起到約束作用。

完整性包括數(shù)據(jù)完整性和箜照完整性,數(shù)據(jù)完整性定義表數(shù)據(jù)的約束條件，參照完整性定義數(shù)據(jù)之間的約束條件。

數(shù)據(jù)完整性由主鍵(PRIMARYKEY)、非空(NOTNULL)、惟一(UNIQUE)和檢查(CHECK)約束條件定義,參照完整性由

外鍵(FOREIGNKEY)約束條件定義。

表共有五種約束，它們是主鍵、非空、惟一、檢查和外鍵。

1.主鍵(PRIMARYKEY)

主鍵是表的主要完整性約束條件，主鍵惟一地標識表的每一行。一般情況下表都要定義主鍵，而且一個

表只能定義一個主鍵。主鍵可以包含表的一列或多列，如果包含表的多列，則需要在表級定義。主鍵包含了主鍵

每一列的非空約束和主鍵所有列的惟一約束。主鍵一旦成功定義，系統(tǒng)將自動生成一個B*樹惟一索引(？？),

用于快速訪問主鍵列。比方圖書表中用“圖書編號〃列作主鍵，“圖書編號"可以惟一地標識圖書表的每一行。

主鍵約束的語法如下：

[CONSTRANT約束名]PRIMARYKEY一列級

[CONSTRANT約束名]PRIMARYKRY（列名1,列名2,...）―表級

2.非空（NOTNULL）

非空約束指定某列不能為空，它只能在列級定義。在默認情況下，Oracle允許列的內(nèi)容為空值。比方

”圖書名稱〃列要求必須填寫，可以為該列設(shè)置非空約束條件。

非空約束語法如下：

[CONSTRANT約束名]NOTNULL一列級

約束分為兩級，一個約束條件根據(jù)具體情況，可以在列級或表級定義。

列級約束：約束表的某一列,出現(xiàn)在表的某列定義之后，約束條件只對該列起作用。

表級約束：約束表的一列或多列,如果涉及到多列，則必須在表級定義。表級約束出現(xiàn)在所有列定義之

后。

3.惟一（UNIQUE）

惟一約束條件要求表的一列或多列的組合內(nèi)容必須惟一，即不相重，可以在列級或表級定義。但如果惟

一約束包含表的多列，則必須在表級定義。比方出版社表的“聯(lián)系電話”不應(yīng)該重復，可以為其定義惟一約束。

惟一約束的語法如下：

[CONSTRANT約束名]UNIQUE一列級

[CONSTRANT約束名]UNIQUE（列名1.列名2,...）—表級

4.檢查（CHECK）

檢查約束條件是用來定義表的一列或多列的一個約束條件，使表的每一列的內(nèi)容必須滿足該條件（列的

內(nèi)容為空除外）。在CHECK條件中，可以調(diào)用SYSDATE、USER等系疏函數(shù)。一個列上可以定義多個CHECK約束條

件，一個CHECK約束可以包含一列或多列。如果CHECK約束包含表的多列,則必須在表級定義。比方圖書表的“單

價"的值必須大于零，就可以設(shè)置成CHECK約束條件。

檢查約束的語法如下：

[CONSTRANT約束名]CHECK（約束條件）一列級，約束條件中只包含本列

[CONSTRANT約束名]CHECK（約束條件）一表級，約束條件中包含多列

5.外鍵（FOREIGNKEY）

指定表的一列或多列的組合作為外鍵,外鍵參照指定的主鍵或惟一鍵。外鍵的值可以為NULL,如果不

為NULL,就必須是指定主鍵或惟一鍵的值之一。外鍵通常用來約束兩個表之間的數(shù)據(jù)關(guān)系，這兩