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

（0管理）0數(shù)據(jù)庫(kù)命令

操作

多年的企業(yè)咨詢顧問(wèn)經(jīng)驗(yàn)，經(jīng)過(guò)實(shí)戰(zhàn)驗(yàn)證可以落地執(zhí)行的卓越管理方案，值得您下載擁有

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

1,查看oracle版本：

select*fromv$version

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

CONNECTSCOTT/TIGER@MYDBSCOTT登陸名TIGER口令

MYDB稱為網(wǎng)絡(luò)服務(wù)名或稱為連接字符串

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

EXIT"或"QUIT"命令

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

通過(guò)SHOWALL命令可以查看

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

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

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

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

SETPAGESIZE100

SETLINESIZE120或

SETPAGESIZE100LINESIZE120

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

SELECT*FROMemp;

6,顯示當(dāng)前用戶，輸入并執(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é)果表頭的顯示，默認(rèn)為ON。

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

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

Setechoon/off:打開/關(guān)閉命令的回顯，默認(rèn)為ON。

Settimeon/off:打開/關(guān)閉時(shí)間顯示，默認(rèn)為OFF。

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

DESCRIBE（可簡(jiǎn)寫成DESC）來(lái)檢查表的結(jié)構(gòu)信息。輸入并執(zhí)行

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

10,注釋的方法是：

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

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

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

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

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

SELECT*FROMemp;

SELECT*FROMdept;

步驟2:選擇"文件"菜單下的"將輸入另存為"命令，彈出

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

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

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

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

CREATEUSERUSER1IDENTIFIEDBYABC123(createuseruserlidentifiedbyabcl23)

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

GRANTCONNECTTOUSERl;(grantconnecttouserl)

GRANTCREATETABLETOUSERl;(grantcreatetabletouserl)

GRANTCREATEPROCEDURETOUSERl;(grantcreateproceduretouserl)

GRANTUNLIMITEDTABLESAPCETOUSERl;(grantunlimitedtablesapcetouserl)

使用新賬戶登錄。

CONNECTUSER1/ABC123@MYDB

輸出結(jié)果：

已連接。

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

CREATETABLEEMPASSELECT*FROMSCOTT.EMP;（createtableempasselect*fromscott

.emp）

CREATETABLEDEPTASSELECT*FROMSCOTT.DEPT;

CREATETABLESALGRADESASSELECT*FROM

SCOTT.SALGRADE;

13,每個(gè)賬戶對(duì)應(yīng)一個(gè)模式（賬戶.表）

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

SELECT*FROMSCOTT.EMP;

14,顯示表的行號(hào)

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

SELECTrownum^nameFROMemp;

15,查詢語(yǔ)句中的加減乘除運(yùn)算

加（+1減（r乘（*\除（/）

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

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

SELECTename,sal,sal*(l+20/100)FROMemp;

顯示結(jié)果為：

ENAMESALSAL*(l+20/100)

SMITH800960

ALLEN16001920

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

輸入并執(zhí)行：

SELECTenameAS"名稱",sal"工資"FROMemp;

顯ZK結(jié)果為：

名稱工資

SMITH800

ALLEN1600

17,在直詢中使用字符串常量。

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

SELECTename||'IS'|[jobAS"雇員和職務(wù)表"FROMemp;

輸出結(jié)果為：

雇員和職務(wù)表

SMITHISCLERK

ALLENISSALESMAN

18,消除重復(fù)行顯示。

使用DISTINCT

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

SELECTDISTINCTjobFROMemp;

19,升序排序

【訓(xùn)練1】查詢雇員姓名和工資，并按工資從小到大排序。

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

SELECTename,salFROMempORDERBYsal;

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

ENAMESAL

SMITH800

JAMES950

注意：若省略ASC和DESC,則默認(rèn)為ASC,即升序排序。

降序排序？

【訓(xùn)練2】查詢雇員姓名和雇傭日期，并按雇傭日期排序，

后雇傭的先顯示。

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

SELECTename,hiredateFROMempORDERBYhiredateDESC;

結(jié)果如下：

ENAMEHIREDATE

ADAMS23-5月-87

SCOTT19-4月-87

多列排序

SELECTename,deptno,hiredateFROMempORDERBYdeptno,hiredate;

在排序中使用別名

SELECTempno,ename,sal*Months_between(sysdate,hiredate)AStotal

FROMemp

ORDERBYtotal;

20,條件查詢

同列表或查詢中的每一

個(gè)值進(jìn)行比較，測(cè)試是否所

Select*fromempWheresal<all(1000,1500,

ALL有的值都滿足，前面必須使

2000)

用的運(yùn)算符包括=、！=、＞=、

＜=＞＞、＜等

測(cè)試是否子查詢至少返Select'存在雇員SCOTT'fromdualwhere

[NOT]EXISTS

回一行exists(select*fromempwhereename='SCOTT');

SELECTename,job,salFROMempWHEREjob='SALESMAN';

SELECTename,job,salFROMempWHEREsal>=3000;

SELECTename,hiredateFROMempWHEREhiredate>='l-l月-82';

SELECTename,job,salFROMempWHEREsal>1000ANDsal<2000;

SELECT*FROMempWHEREjob='CLERK'ORjob='MANAGER';

SELECT*FROMempWHERENOTdeptno=10;

SELECT*FROMempWHERE(deptno=100Rdeptno=20)ANDsal<1500;

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

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

SELECT*FROMempWHEREsalBETWEEN1000AND2000;

21,IN條件查詢使用

顯示職務(wù)為"SALESMAN',"CLERK"和"MANAGER"的雇員信息。(in前可以力口not)

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

SELECT*FROMempWHEREjobIN('SALESMAN','CLERK','MANAGER');

22.LIKE模糊查詢

顯示姓名以"S"開頭的雇員信息如下（%:代表o個(gè)或多個(gè)任意字符。一：代表一個(gè)

任意字符。）

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

SELECT*FROMempWHEREenameLIKE'S%';

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

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

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

SELECTename,mgrFROMempWHEREmgrISNULL;

22,函數(shù)列表

使用求絕對(duì)值函數(shù)abs。

SELECTabs(-5)FR0Mdual;

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

SELECTround(45.923,2),round(45.923,0),round(45.923,-l)FROMdual;

使用求余數(shù)函數(shù)mod.

「冊(cè)例

d(1600,300)MQ實(shí)例結(jié)果

ascii獲得字符的ASCII碼Ascii('A')65

漕字彳室灌留聲ASCII碼相應(yīng)的字符Chr(65)A

J1寸嚕將串轉(zhuǎn)換成小寫

lowerlower('SQLCourse')sqlcourse

upper將字符串轉(zhuǎn)換成大寫upper('SQLCourse')SQLCOURSE

initcap將字符串轉(zhuǎn)換成每個(gè)單詞以大寫開頭initcap('SQLcourse')SqlCourse

concat連接兩個(gè)字符串concat('SQL','Course*)SQLCourse

substr給出起始位置和長(zhǎng)度，返回子字符串substr('String',l,3)Str

length求字符串的長(zhǎng)度length('Wellcom')7

給出起始位置和出現(xiàn)的次數(shù)，求子字符

instr('String',*r',l,l)

instr串在字符串中出現(xiàn)的位置3

Ipad用字符填充字符串左側(cè)到指定長(zhǎng)度IpadCHiUO；-')Hi

rpad用字符填充字符串右側(cè)到指定長(zhǎng)度rpadCHiUO,'-1)Hi

trim在一個(gè)字符串中去除另一個(gè)字符串trim('S'FROM'SSMITH')MITH

用一個(gè)字符串替換另一個(gè)字符串中的子

replacereplace('ABC,'B','D')ADC

字符串

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

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

SELECTempno,ename,deptnoFROMemp

WHEREIower(ename)='blake,;

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

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

SELECTconcat(rpad(ename,15,'.'),job)as職務(wù)列表FROMemp;

結(jié)果為：

職務(wù)列表

SMITHCLERK

ALLENSALESMAN

WARDSALESMAN

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

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

SELECTempno,initcap(ename),jobFROMemp

WHEREsubstr(ename,l,l)='W';

結(jié)果為：

EMPNOINITCAP(ENJOB

7521WardSALESMAN

顯示雇員名稱中包含"S"的雇員名稱及名稱長(zhǎng)度。

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

SELECTempno,ename,length(ename)FROMemp

WHEREinstr(ename,'S',l,l)>0;

24,日期函數(shù)

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

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

SELECTsysdateFROMdual

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

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

SELECTIast_day('08-2月-03')FROMdual;

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

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

SELECTsysdate+1000AS"NEWDATE"FROMdual;

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

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

SELECTename,round(sysdate-hiredate)DAYS

FROMemp

WHEREdeptno=10;

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

函數(shù)功能實(shí)例結(jié)果

To_char轉(zhuǎn)換成字符串類型To_char(1234.5,,$9999.9')$1234.5

To_date轉(zhuǎn)換成日期類型To_date('1980-01-0T,'yyyy-mm-dd')01-1月-80

To_number轉(zhuǎn)換成數(shù)值類型To_number('1234.5')1234.5

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

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

SELECT'12.5'+llFROMdual;

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

執(zhí)行以下查詢：

SELECri2.5'||llFROMdual;

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

MON簡(jiǎn)寫的月份名JAN,FEBJVIAR,...

MONTH全拼的月份名JANUARYFEBRUARY...

HH、HH1212小時(shí)制的小時(shí)(1?12)1,2,3,...,12

HH2424小時(shí)制的小時(shí)(0?23)0,1,2,...,23

MI分(0-59)0,1,2,…,59

SS秒(0?59)0,1,2,...,59

原樣顯示的標(biāo)點(diǎn)符號(hào)

TEXT'引號(hào)中的文本原樣顯示TEXT

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

執(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;

以全拼和序列顯示時(shí)間。

執(zhí)行以下查詢：

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

to_char(SYSDATE,'ddth')FROMdual;

時(shí)間顯示的大小寫。

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

SELECTSYSDATE,to_char(SYSDATE,'yyyysp')FROMdual;

結(jié)果為：

SYSDATETO_CHAR(SYSDATE,'YYYYSP')

07-2月-04twothousandfour

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

SELECTto_char(SYSDATE,'Yyyysp')FROMdual;

結(jié)果為：

SYSDATETO_CHAR(SYSDATE,'YYYYSP')

TwoThousandFour

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

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

執(zhí)行以下查詢：

SELECTTO_CHAR(123.45,,0000.00'),TO_CHAR(12345,'L9.9EEEE')FROMdual;

結(jié)果為：

TO_CHAR(TO_CHAR(12345,'L9.9

0123.45RMB1.2E+04

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

執(zhí)行以下查詢：

SELECTTO_CHAR(sal,'$99,999')SALARYFROMemp

WHEREename='SCOTT';

結(jié)果為：

SALARY

$4,000

函數(shù)功能實(shí)例結(jié)果

nvl空值轉(zhuǎn)換函數(shù)nvl(nuU,空)空

decode實(shí)現(xiàn)分支功能decoded」,男,2,女)男

userenv返回環(huán)境信息userenv('LANGUAGE')SIMPLIFIED

CHINESE_CHINA.ZHS16GBK

greatest返回參數(shù)的最大值greatest(20,35,18,9)35

least返回參數(shù)的最小值least(20,35,18,9)9

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

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

執(zhí)行以下查詢：

SELECTename,nvl(job,'^'),nvl(hiredate,'01-l月-97'),nvl(comm,0)FROMemp;

結(jié)果為：

ENAMENVL(JOB,'NNVL(HIREDANVL(COMM,0)

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:判斷會(huì)話用戶的角色是否為SYSDBA,是則返回TRUE。

"INSTANCE:返回會(huì)話連接的INSTANCE標(biāo)識(shí)符。

"LANGUAGE:返回語(yǔ)言、地區(qū)、數(shù)據(jù)庫(kù)字符集信息。

*LANG:返回會(huì)話語(yǔ)言的ISO簡(jiǎn)稱。

"TERMINAL:返回正在會(huì)話的終端或計(jì)算機(jī)的標(biāo)識(shí)符。

返回用戶終端或系統(tǒng)標(biāo)識(shí)信息。

執(zhí)行以下查詢：

SELECTuserenv('TERMINAL')FROMdual;

結(jié)果為：

ORASERVER

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

執(zhí)行以下查詢：

SELECTuserenv('LANGUAGE')FROMdual;

結(jié)果為：

SIMPLIFIEDCHINESE_CHINA.ZHS16GBK

28，相等連接

顯小雇員的名稱和所在的部門的編號(hào)和名稱。

執(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(+)=dept.deptno;

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

ENAMESALDNAME

SELECTename^aLdnameFROMemp

rightouterjoindeptonemp.deptno=dept.deptno;

SELECTename^aLdnameFROMemp

rightouterjoindeptonemp.deptno=dept.deptno;???

select*fromTBL_10200_FAC_ORDERBinnerjoinDTCXHDYAonB.MT_FAC_NAME=A.M

C

30,統(tǒng)計(jì)查詢

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

數(shù)值和日期類型的列。組函數(shù)忽略列的空值。

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

分成組，對(duì)組可以應(yīng)用組函數(shù)。

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

在組函數(shù)中可使用DISTINCT或ALL關(guān)鍵字。ALL表示對(duì)所有非NULL值（可重復(fù)）進(jìn)行運(yùn)

算（COUNT除外）。DISTINCT表示對(duì)每一個(gè)非NULL值，如果存在重復(fù)值，則組函數(shù)只運(yùn)

算一次。如果不指明上述關(guān)鍵字，默認(rèn)為ALL

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

執(zhí)行以下查詢：

SELECTCOUNT（*）FROMemp;

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

執(zhí)行以下查詢：

SELECTCOUNT(comm)FROMemp;

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

執(zhí)行以下查詢：

SELECTAVG(sal)FROMempWHEREdeptno=10;

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

執(zhí)行以下查詢：

SELECTMAX(hiredate),MIN(hiredate)FROMemp;

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

執(zhí)行以下查詢：

SELECTCOUNT(DISTINCTjob)FROMemp;

返回結(jié)果為：

COUNT(DISTINCTJOB)

5

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

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

SELECTSUM(sal)FROMempGROUPBYjob;

SELECTjob,SUM(sal)FROMempGROUPBYjob;

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

執(zhí)行以下查詢：

SELECTdeptno,job,sum(sal)FROMemp

GROUPBYdeptnojob;

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

執(zhí)行以下查詢：

SELECTdeptno,max(sal)FROMemp

GROUPBYdeptno

HAVINGmax(sal)>=3000;

注意:WHERE條件迂GROUPBY^^^,HAVING條件在

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

執(zhí)行以下直詢：

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

GROUPBYjob

ORDERBYSUM(sal);

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

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

執(zhí)行以下查詢：

SELECTmax(avg(sal))FROMempGROUPBYdeptno;

31,子查詢

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

子句中出現(xiàn)子查詢。子查詢比主查詢先螃,結(jié)果作為主查詢的條件,在書寫上要用圓括號(hào)

擴(kuò)起來(lái)，并放在比較運(yùn)算符的右側(cè)。子查詢可以嵌套使用，最里層的查詢最先執(zhí)行。子查詢

可以在SELECT、INSERT、UPDATE.DELETE等語(yǔ)句中使用。

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

執(zhí)行以下查詢：

SELECTename,salFROMemp

WHEREsal>(SELECTsalFROMempWHEREempno=7788);

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

執(zhí)行以下查詢：

SELECTename,salFROMemp

WHEREsal<(SELECTsalFROMempWHEREempno=7788)

ANDdeptno=(SELECTdeptnoFROMempWHEREempno=7788);

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

執(zhí)行以下查詢：

SELECTename,salFROMemp

WHEREsal>(SELECTAVG(sal)FROMemp);

32,多行子查詢

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

符號(hào)，它們是IN、ANY和ALL。

查詢工資低于任何一個(gè)"CLERK"的工資的雇員信息。

執(zhí)行以下查詢：

SELECTempno,ename,job,salFROMemp

WHEREsal<ANY(SELECTsalFROMempWHEREjob='CLERK')

ANDjobo'CLERK,;

查詢工資比所有的"SALESMAN"都高的雇員的編號(hào)、名字和工資。

執(zhí)行以下查詢：

SELECTempno,ename,salFROMemp

WHEREsal>ALL(SELECTsalFROMempWHEREjob='SALESMAN');

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

執(zhí)行以下查詢：

SELECTempno,ename,jobFROMemp

WHEREjobIN(SELECTjobFROMempWHEREdeptno=10)

ANDdeptno=20;

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

執(zhí)行以下查詢：

SELECTempno,ename,jobFROMemp

WHEREjob=(SELEC[jobFROMempWHEREempno=7788)

ANDhiredate<(SELECThiredateFROMempWHEREempno=7788);

33,多列子直詢

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

下是多列子查詢的訓(xùn)練實(shí)例。

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

執(zhí)行以下查詢：

SELECTempno,ename,salFROMemp

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

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

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

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

執(zhí)行以下查詢：

SELECTename,salFROM(SELECTrownumasnum,ename,salFROMempWHERErownum

<=9)

WHEREnum>=6;

35,集合運(yùn)算

操作描述

UNION并集，合并兩個(gè)操作的結(jié)果，去掉重復(fù)的部分

UNIONALL并集，合并兩個(gè)操作的結(jié)果，保留重復(fù)的部分

MINUS差集，從前面的操作結(jié)果中去掉與后面操作結(jié)果相同的部分

INTERSECT交集，取兩個(gè)操作結(jié)果中相同的部分

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

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

執(zhí)行以下查詢：

SELECTjobFROMempWHEREdeptno=10

UNION

SELECTjobFROMempWHEREdeptno=20;

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

執(zhí)行以下查詢：

SELECTjob,salFROMempWHEREdeptno=10

INTERSECT

SELECTjob,salFROMempWHEREdeptno=20;

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

執(zhí)行以下查詢：

SELECTdeptnoFROMdept

MINUS

SELECTdeptnoFROMemp;

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

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

SELECTDECODE(dname,'SALES','銷售部'「ACCOUNTING','財(cái)務(wù)部'/RESEARCH：研發(fā)部

'，沫知')

部門名

FROMemp,dept

WHEREemp.deptno=dept.deptno

GROUPBYdname

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

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

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

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

工資

FROMemp,dept

WHEREemp.deptno=dept.deptno

GROUPBYdname

ORDERBYAVG（sal）DESC;

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

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

INSERTINTO表名［（字段列表）］｛VALUES（表達(dá)式L表達(dá)式2,./QUERY語(yǔ)句｝;

插入字段的值的類型要和字段的類型一一對(duì)應(yīng)。字符串類型的字段值必須用單引號(hào)括起來(lái)

字符串類型的字段值超過(guò)定義的長(zhǎng)度會(huì)出錯(cuò)，最好在插入前進(jìn)行長(zhǎng)度校驗(yàn)。

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

將新雇員插入到emp表：

INSERTINTOemp（empno,ename,job）

VALUES（1000,'小李'CLERK'）;

其他沒有插入的字段，系統(tǒng)會(huì)填寫為表的默認(rèn)值。如果在表的創(chuàng)建時(shí)沒有說(shuō)明默認(rèn)值，則將

插入NULL值

日期型的數(shù)據(jù)默認(rèn)格式為DD-MON-YY,默認(rèn)的世紀(jì)為當(dāng)前的世紀(jì)，默認(rèn)的時(shí)間為午夜12

點(diǎn)。如果指定的世紀(jì)不是本世紀(jì)或時(shí)間不是午夜12點(diǎn)，則必須使用TODATE系統(tǒng)函數(shù)對(duì)

字符串進(jìn)行轉(zhuǎn)換。

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

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

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

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

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

步驟1:創(chuàng)建一個(gè)新表manager

CREATETABLEmanagerASSELECTempno,ename/salFROMempWHEREjob='MANAG

ER,；

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

表已創(chuàng)建。

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

INSERTINTOmanager

SELECTempno,ename,sal

FROMemp

WHEREjob='CLERK,;

38序列使用

使用INSERT語(yǔ)句時(shí)，可以通過(guò)序列來(lái)填寫某些數(shù)值型或字符型的列。序列是一個(gè)要預(yù)先定

義的有序的數(shù)值序列，應(yīng)該先建立一個(gè)序列，然后在插入語(yǔ)句中使用

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

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

CREATESEQUENCEabcINCREMENTBYlSTARTWITH2000

MAXVALUE99999CYCLENOCACHE;

在INSERT語(yǔ)句使用序列，序列的名稱為abc:

INSERTINTOmanagerVALUES(abc.nextval,'/J\S',2500);

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

修改數(shù)據(jù)的語(yǔ)句UPDATE對(duì)表中指定字段的數(shù)據(jù)進(jìn)行修改，一般需要通過(guò)添加WHERE條

件來(lái)限定要進(jìn)行修改的行，如果不添加WHERE條件，將對(duì)所有的行進(jìn)行修改。

⑴修改數(shù)據(jù)的語(yǔ)句UPDATE的基本語(yǔ)法如下：

UPDATE表名SET字段名1=表達(dá)式1,字段名2=表達(dá)式2,...WHERE條件；

修改小李(編號(hào)為1000)的工資為3000。

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

UPDATEempSETsal=3000WHEREempno=1000;

如果修改的值沒有賦值或定義，將把原來(lái)字段的內(nèi)容清為NULL。若修改值的長(zhǎng)度超過(guò)定義

的長(zhǎng)度，則會(huì)出錯(cuò)。

(2)UPDATE語(yǔ)句的另夕一種用法：

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

WHERE條件；

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

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

UPDATEmanager

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

WHEREempno=1000;

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

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

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

DELETEFROM表名WHERE條件;

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

據(jù)。

【訓(xùn)練1］刪除雇員編號(hào)為1000的新插入的雇員。

步驟1:刪除編號(hào)為1000的雇員：

DELETEFROMempWHEREempno=lOOO;

如果確實(shí)要?jiǎng)h除一個(gè)大表里的全部記錄，可以用TRUNCATE命令，它可以釋放占用的數(shù)據(jù)

塊表空間，語(yǔ)法為：

TRUNCATETABLE表名；

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

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

TRUNCATETABLEmanager;

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

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

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

之間的操作，稱為一個(gè)事務(wù)。在一個(gè)事務(wù)內(nèi)，數(shù)據(jù)的修改一起提交或撤銷，如果發(fā)生故障或

系統(tǒng)錯(cuò)誤，整個(gè)事務(wù)也會(huì)自動(dòng)撤銷。

我們可以這樣理解數(shù)據(jù)庫(kù)事物：對(duì)數(shù)據(jù)庫(kù)所做的一系列修改，在修改過(guò)程中，暫時(shí)不寫入數(shù)

據(jù)庫(kù)，而是緩存起來(lái),用戶在自己的終端可以預(yù)覽變化，直到全部修改完成，并經(jīng)過(guò)檢查確

認(rèn)無(wú)誤后，一次性提交并寫入數(shù)據(jù)庫(kù)，在提交之前，必要的話所做的修改都可以取消。提交

之后，就不能撤銷，提交成功后其他用戶才可以通過(guò)查詢?yōu)g覽數(shù)據(jù)的變化。

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

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

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

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

數(shù)據(jù)庫(kù)事務(wù)處理可分為隱式和顯式兩種。顯式事務(wù)操作通過(guò)命令實(shí)現(xiàn)，隱式事務(wù)由系統(tǒng)自動(dòng)

完成提交或撤銷（回退）工作，無(wú)需用戶的干預(yù)。

隱式提交的情況包括：當(dāng)用戶正常退出SQL*Plus或執(zhí)行CREATE、DRORGRANT、

理蛆生等命令時(shí)會(huì)發(fā)生事務(wù)的自動(dòng)提交。

還有一種情況，如果把系統(tǒng)的環(huán)境變量AUTOCOMMIT設(shè)置為ON（默認(rèn)狀態(tài)為OFF）,則

每當(dāng)執(zhí)行一條INSERT.DELETE或UPDATE命令對(duì)數(shù)據(jù)進(jìn)行修改后，就會(huì)馬上自動(dòng)提交。

設(shè)置命令格式如下：

SETAUTOCOMMITON/OFF

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

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

COMMIT操作把多個(gè)步驟對(duì)數(shù)據(jù)庫(kù)的修改，一次性地永久寫入數(shù)據(jù)庫(kù)，代表數(shù)據(jù)庫(kù)事務(wù)的

成功執(zhí)行。ROLLBACK操作在發(fā)生問(wèn)題時(shí)，把對(duì)數(shù)據(jù)庫(kù)已經(jīng)作出的修改撤消，回退到修改

前的狀態(tài)。在操作過(guò)程中，一旦發(fā)生問(wèn)題，如果還沒有提交操作，則隨時(shí)可以使用ROLLBACK

來(lái)撤消前面的操作。SAVEPOINT則用于在事務(wù)中間建立一些保存點(diǎn)，ROLLBACK可以使

操作回退到這些點(diǎn)上邊而不必撤銷全部的操作。一旦COMMIT"完成就不能用ROLLBACK

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

操作語(yǔ)句

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

語(yǔ)句將開始一個(gè)新的事務(wù)，或執(zhí)行COMMIT提交或ROLLBACK撤銷事務(wù)，也標(biāo)志新的事

務(wù)的開始。另外,執(zhí)行DDL（如CREATE）或DCL命令也將自動(dòng)提交前一個(gè)事務(wù)而開始一個(gè)

新的事務(wù)。

數(shù)據(jù)在修改的時(shí)候會(huì)對(duì)記錄進(jìn)行鎖定，其他會(huì)話不能對(duì)鎖定的記錄進(jìn)行修改或加鎖，只有當(dāng)

前會(huì)話提交或撤銷后，記錄的鎖定才會(huì)釋放。

我們通過(guò)以下的訓(xùn)練來(lái)為雇員SCOTT增加工資，SCOTT的雇員號(hào)為7788。

學(xué)習(xí)使用COMMIT和ROLLBACK。

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

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的工資恢復(fù)為3000:

SELECTename,salFROMempWHEREempno=7788;

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

ENAMESAL

SCOTT3000

步驟4:重新修改雇員SCOTT的工資，工資在原有基礎(chǔ)上增加1000:

UPDATEempSETsal=sal+1000WHEREempno=7788;

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

已更新1行。

顯示修改后SCOTT的工資：

SELECTename,salFROMempWHEREempno=7788;

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

ENAMESAL

SCOTT4000

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

COMMIT;

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

提交完成。

說(shuō)明：在執(zhí)行COMMIT后，工資的修改被永久寫入數(shù)據(jù)庫(kù)。本訓(xùn)練的第1步，先使用

COMMIT命令提交原來(lái)的操作，同時(shí)標(biāo)志一個(gè)新的事務(wù)的開始。

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

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

【訓(xùn)練2］學(xué)習(xí)使用SAVEPOINT命令。

步驟1:插入一個(gè)雇員：

INSERTINTOemp(empno,ename,job)

VALUES(3000,'小馬'「STUDENT');

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

已創(chuàng)建1行。

步驟2:插入保存點(diǎn)，檢查點(diǎn)的名稱為PA:

SAVEPOINTpa;

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

保存點(diǎn)已創(chuàng)建。

步驟3:插入另一個(gè)雇員：

INSERTINTOemp(empno,ename,job)

VALUES(3001,'/J\M','STUDENT');

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

已創(chuàng)建1行。

步驟4:回退到保存點(diǎn)PA，則后插入的小黃被取消，而小馬仍然保留。

ROLLBACKTOpa;

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

回退已完成。

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

COMMIT;

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

提交完成。

說(shuō)明：第4步的回退，將回退到保存點(diǎn)PA,即第3步被撤銷。所以最后的COMMIT只提

交了對(duì)小馬的插入。請(qǐng)自行檢查插入的雇員。

在Oracle數(shù)據(jù)庫(kù)中，有一個(gè)叫回滾段的特殊的存儲(chǔ)區(qū)域。在提交一個(gè)事物之前，如果用戶

進(jìn)行了數(shù)據(jù)的修改，在所謂的回滾段中將保存變化前的數(shù)據(jù)。有了回滾段才能在必要時(shí)使用

ROLLBACK命令或自動(dòng)地進(jìn)行數(shù)據(jù)撤銷。在提交事物之前，用戶自己可以看到修改的數(shù)據(jù),

但因?yàn)樾薷倪€沒有最終提交，其他用戶看到的應(yīng)該是原來(lái)的數(shù)據(jù)，也就是回滾段中的數(shù)據(jù),

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

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

的讀一致性。

當(dāng)?shù)?個(gè)SQL*Plus會(huì)話刪除一條后，第2個(gè)SQL*Plus會(huì)話仍然可以看到該條記錄，直到

第1個(gè)SQL*Plus會(huì)話提交(COMMIT)該刪除操作后，兩個(gè)會(huì)話看到的才是一致的數(shù)據(jù)。

42,表的鎖定

鎖出現(xiàn)在數(shù)據(jù)共享的場(chǎng)合，用來(lái)保證數(shù)據(jù)的一致性。當(dāng)多個(gè)會(huì)話同時(shí)修改一個(gè)表時(shí)，需要對(duì)

數(shù)據(jù)進(jìn)行相應(yīng)的鎖定。

鎖有"只讀鎖"、"排它鎖"，"共享排它鎖”等多種類型，而且每種類型又有“行級(jí)

鎖"(一次鎖住一條記錄)，"頁(yè)級(jí)鎖"(一次鎖住一頁(yè)，即數(shù)據(jù)庫(kù)中存儲(chǔ)記錄的最小可分配單

元)，"表級(jí)鎖"(鎖住整個(gè)表)。

若為"行級(jí)排它鎖”，則除被鎖住的行外，該表中其他行均可被其他的用戶進(jìn)行修改

(Update)或刪除(delete)。若為"表級(jí)排它鎖"，則所有其他用戶只能對(duì)該表進(jìn)行查詢

(select)操作，而無(wú)法對(duì)其中的任何記錄進(jìn)行修改或刪除。當(dāng)程序?qū)λ龅男薷倪M(jìn)行提交

(commit)或回滾(rollback)后，鎖住的資源便會(huì)得到釋放，從而允許其他用戶進(jìn)行操作。

如果兩個(gè)事務(wù)，分別鎖定一部分?jǐn)?shù)據(jù)，而都在等待對(duì)方釋放鎖才能完成事務(wù)操作，這種情況

下就會(huì)發(fā)生死鎖。

在Oracle數(shù)據(jù)庫(kù)中，修改數(shù)據(jù)操作時(shí)需要一個(gè)隱式的獨(dú)占鎖，以鎖定修改的行，直到修改

被提交或撤銷為止。如果一個(gè)會(huì)話鎖定了數(shù)據(jù)，那么第二個(gè)會(huì)話要想對(duì)數(shù)據(jù)進(jìn)行修改，只能

等到第一個(gè)會(huì)話對(duì)修改使用COMMIT命令進(jìn)行提交或使用ROLLBACK命令進(jìn)行回滾撤銷

后，才開始執(zhí)行。因此應(yīng)養(yǎng)成一個(gè)良好的習(xí)慣：執(zhí)行修改操作后，要盡早地提交或撤銷，以

免影響其他會(huì)話對(duì)數(shù)據(jù)的修改。

語(yǔ)句描述

SELECTFORUPDATE鎖定表行，防止其他會(huì)話對(duì)行的修改

LOCKTABLE鎖定表，防止其他會(huì)話對(duì)表的修改

鎖定行：

對(duì)emp表的部門10的雇員記錄加顯式鎖，并測(cè)試。

步驟1：對(duì)部門10加顯式鎖：

SELECTempno,enamejob,salFROMempWHEREdeptno=10FORUPDATE;

步驟2:啟動(dòng)第二個(gè)SQL*Plus（第二個(gè)會(huì)話），以SCOTT賬戶登錄數(shù)據(jù)庫(kù)，對(duì)部門10的雇

員CLARK進(jìn)行修改操作。

UPDATEempSETsal=sal+100whereempno=7782;

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

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

步驟3:在第一個(gè)會(huì)話進(jìn)行解鎖操作：

COMMIT;

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

已更新1行。

說(shuō)明：步驟1對(duì)選定的部門10的雇員加鎖，之后其他會(huì)話不能對(duì)部門10的雇員數(shù)據(jù)進(jìn)行

修改或刪除。如果此時(shí)要進(jìn)行修改或刪除，則會(huì)處于等待狀態(tài)。使用COMMIT語(yǔ)句進(jìn)行解

鎖之后，如果有掛起的修改或刪除操作，則等待的操作此時(shí)可以執(zhí)行。

鎖定表：

LOCK語(yǔ)句用于對(duì)整張表進(jìn)行鎖定。語(yǔ)法如下：

LOCKTABLE表名IN{SHARE|EXCLUSIVE}MODE

對(duì)表的鎖定可以是共享(SHARE)或獨(dú)占(EXCLUSIVE)模式。共享模式下，其他會(huì)話可以加

共享鎖，但不能加獨(dú)占鎖。在獨(dú)占模式下，其他會(huì)話不能加共享或獨(dú)占鎖。

【訓(xùn)練1】對(duì)emp表添加獨(dú)占鎖。

步驟1:對(duì)emp表加獨(dú)占鎖：

LOCKTABLEempINEXCLUSIVEMODE;

結(jié)果為：

表已鎖定。

步驟2:對(duì)表進(jìn)行解鎖操作：

COMMIT;

說(shuō)明：當(dāng)使用LOCK語(yǔ)句顯式鎖定一張表時(shí)，死鎖的概率就會(huì)增加。同樣地，使用COMMIT

或ROLLBACK命令可以釋放鎖。

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

43,表的創(chuàng)建

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

CREATETABLE表名

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

[TABLECONSTRAINT]

[TABLE_PARTITION_CLAUSE]

)；

創(chuàng)建表最主要的是要說(shuō)明表名、列名、列的數(shù)據(jù)類型和寬度，多列之間用"/分隔。可以

是用中文或英文作為表名和列名。表名最大長(zhǎng)度為30個(gè)字符。在同一個(gè)用戶下，表不能重

名，但不同用戶表的名稱可以相重。另外，表的名稱不能使用Oracle的保留字。在一張表

中最多可以包含2000歹山該語(yǔ)法中的其他部分根據(jù)需要添加，作用如下：

DEFAULT表達(dá)式：用來(lái)定義列的默認(rèn)值。

COLUMNCONSTRAINT:用來(lái)定義列級(jí)的約束條件。

TABLECONSTRAINT:用來(lái)定義表級(jí)的約束條件。

TABLE_PARTITION_CLAUSE:定義表的分區(qū)子句。

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

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

CREATETABLE出版社(

編號(hào)VARCHAR2⑵，

出版社名稱VARCHAR2(30),

地址VARCHAR2(30),

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

)；

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

CREATETABLE圖書（

圖書編號(hào)VARCHAR2⑸，

圖書名稱VARCHAR2(30),

出版社編號(hào)VARCHAR2(2),

作者VARCHAR2Q0),

出版日期DATE,

數(shù)量NUMBER⑶，

單價(jià)NUMBER。,2)

)；

2.通過(guò)子查詢創(chuàng)建表

如果要?jiǎng)?chuàng)建一個(gè)同已有的表結(jié)構(gòu)相同或部分相同的表，可以采用以下的語(yǔ)法：

CREATETABLE表名(列名...)ASSQL查詢語(yǔ)句；

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

在表名后的括號(hào)中給出，如果省略將采用原來(lái)表的列名。復(fù)制的內(nèi)容由查詢語(yǔ)句的WHERE

條件決定。

通過(guò)子查詢創(chuàng)建新的圖書表。

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

CREATETABLE圖書1ASSELECT*FROM圖書；

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

表已創(chuàng)建。

步驟2:創(chuàng)建新的圖書表"圖書2”,只包含書名和單價(jià)，輸入并執(zhí)行以下命令：

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

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

令：

CREATETABLE圖書3(書名,單價(jià))ASSELECT圖書名稱，單價(jià)FROM圖書WHERE1=2;

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

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

默認(rèn)值，默認(rèn)值由DEFAULT部分說(shuō)明。

］創(chuàng)建表時(shí)設(shè)置默認(rèn)值。

步驟1:創(chuàng)建表時(shí)，設(shè)置表的默認(rèn)值。

CREATETABLE圖書4(

圖書編號(hào)VARCHAR2(5)DEFAULTNULL,

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

出版社編號(hào)VARCHAR2(2)DEFAULTNULL,

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

作者VARCHAR2(10)DEFAULTNULL,

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

單價(jià)NUMBER。,2)DEFAULTNULL,

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

)；

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

刪除表的語(yǔ)法如下：

DROPTABLE表名［CASCADECONSTRAINTS］;

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

表示當(dāng)要?jiǎng)h除的表被其他表參照時(shí)，刪除參照此表的約束條件。

44,表的操作

1.表的重命名

語(yǔ)法如下：

RENAME舊表名TO新表名；

2.清空表

清空表的語(yǔ)法為：

TRUNCATETABLE表名;

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

3.添加注釋

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

COMMENTONTABLE表名IS'...';

該語(yǔ)法為表添加注釋字符串。如IS后的字符串為空，則清除表注釋。

4為列添加注釋的語(yǔ)法為：

COMMENTONCOLUMN表名.列名IS'...'

該語(yǔ)法為列添加注釋字符串。如IS后的字符串為空，則清除列注釋。

為emp表的deptno列添加注釋："部門編號(hào)"。

COMMENTONCOLUMNemp.deptnoS部門編號(hào)'；

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

DESCRIBE表名;

DESCRIBE可以簡(jiǎn)寫為DESC。

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

顯示當(dāng)前用戶的所有表。

SELECTobject_nameFROMuser_objectsWHEREobject_type='TABLE';

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

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

修改表時(shí)，可通過(guò)定義約束條件來(lái)保證數(shù)據(jù)的完整性和一致性。約束條件是一些規(guī)則，在對(duì)

數(shù)據(jù)進(jìn)行插入、刪除和修改時(shí)要對(duì)這些規(guī)則進(jìn)行驗(yàn)證，從而起到約束作用。

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

數(shù)據(jù)之間的約束條件。數(shù)據(jù)完整性由主鍵(PRIMARYKEY)、非空(NOTNULL)、惟一(UNIQUE)

和檢查(CHECK)約束條件定義，參照完整性由外鍵(FOREIGNKEY)約束條件定義。

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

1.主鍵(PRIMARYKEY)

主鍵是表的主要完整性約束條件，主鍵惟一地標(biāo)識(shí)表的每一行。一般情況下表都要定義主鍵，

而且一個(gè)表只能定義一個(gè)主鍵。主鍵可以包含表的一列或多列，如果包含表的多列，則需要

在表級(jí)定義屋主鍵包含了主犍每一列的非空約束和主鍵所有列的惟一約束。主鍵一旦成功定

義，系統(tǒng)將自動(dòng)生成一個(gè)B*樹惟一索引(？？),用于快速訪問(wèn)主鍵列。比如圖書表中用"圖

書編號(hào)"列作主鍵，“圖書編號(hào)"可以惟一地標(biāo)識(shí)圖書表的每一行。

主鍵約束的語(yǔ)法如下：

[CONSTRANT約束名]PRIMARYKEY--列級(jí)

[CONSTRANT約束名]PRIMARYKEY例名L列名2,表級(jí)

2.非空(NOTNULL)

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

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

非空約束語(yǔ)法如下：

[CONSTRANT約束名]NOTNULL-洌級(jí)

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

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

表級(jí)i速1約束表的一列或多列，如果涉及到多列，則必須在表級(jí)定義。表級(jí)約束出現(xiàn)在所

有列定義之后。

3.惟一(UNIQUE)

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

但如果惟一約束包含表的多列，則必須在表級(jí)定義。比如出版社表的“聯(lián)系電話"不應(yīng)該重

復(fù)，可以為其定義惟一約束。

惟一約束的語(yǔ)法如下：

[CONSTRANT約束名]UNIQUE—歹U級(jí)

[CONSTRANT約束名]UNIQUE(列名1,列名2,...)—表級(jí)

4.檢查(CHECK)

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

條件例的內(nèi)容為空除外)。在CHECK條件中，可以調(diào)用SYSDATE、USER等系統(tǒng)函數(shù)。二

個(gè)列上可以定義多個(gè)CHECK約束條件，一個(gè)CHECK約束可以包含一列或多列。如果CHECK

約束包含表的多列，則必須在表級(jí)定義。比如圖書表的"單價(jià)"的值必須大于零，就可以設(shè)

置成CHECK約束條件。

檢查約束的語(yǔ)法如下：

[CONSTRAINT約束名]CHECK（約束條件）一歹I」級(jí)，約束條件中只包含本列

[CONSTRAINT約束名]CHECK（約束條件）一表級(jí)，約束條件中包含多列

5.外鍵（FOREIGNKEY）

指定表的二H或多列的組合作為外鍵，外鍵參照指定的主鍵或惟一鍵。外鍵的值可以為

NULL,如果不為NULL,就必須是指定主鍵或惟一鍵的值之一。外鍵通常用來(lái)約束兩個(gè)表

之間的數(shù)據(jù)關(guān)系，這兩個(gè)表含有主鍵或惟一鍵的稱為主表，定義外鍵的那張表稱為子表。如

果外鍵只包含一列，則可以在列級(jí)定義；如果包含多列，則必須在表級(jí)定義。

外鍵的列的個(gè)數(shù)、列的數(shù)據(jù)類型和長(zhǎng)度，應(yīng)該和參照的主鍵或惟一鍵一致。比