Oracle數(shù)據(jù)庫技術(shù)與實驗指導(dǎo)

Oracle數(shù)據(jù)庫技術(shù)與實驗指導(dǎo)

2011.08

第0章實用Oracle數(shù)據(jù)庫技術(shù)

Oracle的官方網(wǎng)站為www.oraclo.com；

其次http://metalink.oracle,com/這里有很多權(quán)威的解決方案和補??；

然后就是一些著名網(wǎng)站如：>www.orafaq.net>,這

里有很多經(jīng)驗之談。

遇到問題了還可以第一時間找,這里會給你最詳細(xì)的解釋。

Oracle10g/llg數(shù)據(jù)庫都分為標(biāo)準(zhǔn)版(StandardEdition)>標(biāo)準(zhǔn)版1(Standard

EditionOne)以及企業(yè)版(EnterpriseEdition)?？蓮娜缦戮W(wǎng)址下載、學(xué)習(xí)或試用Oracle：

http://www.oracle,com/technology/global/cn/software/products/database/orac

lelOg/index.html

http://www.oracle,com/technology/global/cn/software/products/database/inde

x.html

http://www.oracle,com/technology/software/index.html

/technology/software/products/database/index.html

SQLDeveloper也可以蟬獨免費下載安爨的。下載地Ik址為：

/technology/global/cn/software/products/sql/index.html

SQL*PlusInstantClient(SQL*Plus即時客戶端)

SQL*PlusInstantClient下

載:http://www.oracle,com/technology/global/cn/software/tech/oci/instantclient/

index,html。

實驗1數(shù)據(jù)庫系統(tǒng)基礎(chǔ)操作

OracleDatabase11g第2版(.0)的下載地址:

/technology/global/cn/software/products/database/index.html

適用于MicrosoftWindows(32位)的OracleDatabase11g第2版(.0)的下載地

址：

/technology/global/cn/software/products/database/oraclellg/_win32s

oft.html

實驗2數(shù)據(jù)庫基礎(chǔ)操作

手工建庫

手工建庫須要經(jīng)過幾個步驟，每一個步驟都非常關(guān)鍵。它包括：

1、創(chuàng)建相關(guān)目錄(數(shù)據(jù)文件和跟蹤文件)(假設(shè)要創(chuàng)建KCGL數(shù)據(jù)庫，Oracle已安裝

于?。篭app\qxz”目錄)

在c:\app\qxz\admin這個目錄之下創(chuàng)建KCGL文件夾；

在C:\app\qxz\admin\KCGL這個目錄之下創(chuàng)建adump文件夾；

在C:\app\qxz\admin\KCGL這個目錄之下創(chuàng)建dpdump文件夾；

在C:\app\qxz\admin\KCGL這個目錄之下創(chuàng)建pfile文件夾；

在C:\app\qxz\oradata這個目錄之下創(chuàng)建KCGL文件夾；

2、創(chuàng)建初始化參數(shù)文件

通過復(fù)制現(xiàn)有的初始化參數(shù)文件C:\app\qxz\admin\orc1\pfi1e這個目錄下的參數(shù)文

件“3nit.ora.*"(*為數(shù)字?jǐn)U展名)到C:\app\qxz\product\ll.2.O\dbhome_l\database

這個目錄，修改名為initKCGL.ora,最后用記事本打開這個參數(shù)文件，修改如下幾個參數(shù)

的值：

audit_file_dest=C:\app\qxz\admin\KCGL\adump

db_name=KCGL

control_files=("C:\app\qxz\oradata\KCGL\control01.ctl","C:\app\qxz\oradata

\KCGL\control02.ctl")

3、打開DOS窗口，設(shè)置環(huán)境變量：Setoracle_sid=KCGL

4、創(chuàng)建服務(wù)：Oradim-new-sidKCGL

5、創(chuàng)建口令文件

Orapwdfile=C:\app\qxz\product\ll.2.0\dbhome_l\database\pwdKCGL.ora

Password=12345

6^啟動服務(wù)器：Sqlplus/nolog

Conn/assysdba

Startupnomount

7、執(zhí)行建庫腳本：

CREATEDATABASEKCGL

datafile*c:\app\qxz\oradata\KCGL\system01.dbf*size300m

autoextendonnext10mextentmanagementlocal

SysauxdatafileJc:\app\qxz\oradata\KCGL\sysaux01.dbf,size120m

undotablespaceundotbsl

datafile?c:\app\qxz\oradata\KCGL\undotbs01.dbfJsize100m

defaulttemporarytablespacetemptbsl

tempfile*c:\app\qxz\oradata\KCGL\temp01,dbf*size50m

logfilegroup1(Jc:\app\qxz\oradata\KCGL\redo01.log*)size50m,

group2(，c:\app\qxz\oradata\KCGL\redo02.log，)size50m,

group3(Jc:\app\qxz\oradata\KCGL\redo03.log，)size50m;

用記事本編輯以上內(nèi)容，假定保存為C:\CREATEKCGL.sql文件，然后執(zhí)行這個腳本。

StartC:\CREATEKCGL.sql

不管出現(xiàn)哪種錯誤，都要刪除C:\app\qxz\oradata\KCGL目錄下創(chuàng)建的所有文件，改

正錯誤后，重新啟動實例，再執(zhí)行建庫腳本。

8、創(chuàng)建數(shù)據(jù)字典和包

StartC:\app\qxz\product\ll.2.0\dbhomel\RDBMS\ADMIN\catalog

StartC:\app\qxz\product\ll.2.0\dbhome_l\RDBMS\ADMIN\catproc

9^執(zhí)行pupbld.sql腳本文件

切換成system用戶執(zhí)行如下命令：Connsystem/manager

StartC:\app\qxz\product\l1.2.O\dbhome_l\sqlplus\admin\pupbld

10、執(zhí)行scott腳本創(chuàng)建scott方案

StartC:\app\qxz\product\l1.2.0\dbhome_l\RDBMS\ADMIN\scott.sql

這時需要修改密碼：Conn/assysdba

Alteruserscottidentifiedbytiger;

再連接scott：Connscott/tiger

11、select*fromdept;

能顯示出dept表的結(jié)果，表示新數(shù)據(jù)庫KCGL已安裝成功了。

2.2查看數(shù)據(jù)庫

1、查看表空間的名稱及大小

selecttab1espace_name,min_extents,max_extents,pctincrease,statusfrom

dbatablespaces;

selecttablespacename,initialextent,nextextent,contents,logging,

extent_management,allocationtypefromdbatablespacesorderbytablespacename;

selectt.tablespacename,round(sum(bytes/(1024*1024)),0)tssizefrom

dbci_tablespacest,dba_data_fil.esdwheret.tablespace_ncime=d.tablespace_name

groupbyt.tablespace_name;

2、查看表空間物理文件的名稱及大小

columndb_block_sizenew_valueblksznoprint

selectvaluedb_block_sizefromv$parameterwherename='db_block_size';

columntablespace_nameformatal6;

columnfile_nameformata60;

setlinesize160;--為sqlplus命令

selectfile_name,round(bytes/(1024*1024),0)

total_space,autoextensible,increment_by*&blksz/(1024*1024)as

incement,maxbytes/(1024*1024)asmaxsizefromdbadatafilesorderby

tablespace_name;-blksz一般為8192

selecttablespacename,fileid,filename,round(bytes/(1024*1024),0)

total_space

fromdba_data_filesorderbytablespace_name;

3、查看回滾段名稱及大小

selecta.owner||'.||a.segmentnameroll_name,a.tablespace_name

tablespace,to_char(a.initial_extent)||'/'||to_char(a.next_extent)

in_extents,to_char(a.min_extents)I|'/'||to_char(a.max_extents)m_extents,

a.statusstatus,b.bytesbytes,b.extentsextents,d.shrinksshrinks,d.wraps

wraps,d.optsizeoptfromdbarollback_segsa,dbasegmentsb,v$rollnamec,

v$rollstatdwherea.segmentname=b.segment_nameanda.segmentname=c.name(+)

andc.usn=d.usn(+)orderbya.segment_name;

selectsegment_name,tablespace_name,r.status,(initial_extent/1024)

InitialExtent,(next_extent/1024)NextExtent,max_extents,v.curext

CurExtentFromdba_rollback_segsr,v$rollstatvWherer.segment_id=v.usn(+)order

bysegment_name;

4、查看控制文件

selectnamefromv$controlfile;

5、查看日志文件

selectmemberfromv$logfile;

6、查看表空間的使用情況

select*from(selectsum(bytes)/(1024*1024)as

^freespace(m)tablespacenamefromdbafreespacegroupbytablespacename)

orderby〃free_space(m)〃；

SELECTA.TABLESPACE_NAME,A.BYTESTOTAL,B.BYTESUSED,C.BYTES

FREE,(B.BYTES*100)/A.BYTES〃%USED",(C.BYTES*100)/A.BYTESFREE〃FROM

SYS.SM$TS_AVAILA,SYS.SM$TS_USEDB,SYS.SM$TS_FREE

CWHEREA.TABLESPACE_NAME=B.TABLESPACE_NAMEAND

A.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、查看數(shù)據(jù)庫庫對象

selectowner,object_type,status,count(*)count#fromallobjectsgroupby

owner,object_type,status;

8、查看數(shù)據(jù)庫的版本

select*fromv$version;

SelectversionFROMProduct_component_versionWhere

SUBSTR(PRODUCT,1,6)=Oracle，;

9、查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式

selectcreated,logmode,log_modefromv$database;

10、查看臨時數(shù)據(jù)庫文件

selectstatus,enabled,namefromv$tempfile;

常用數(shù)據(jù)庫信息查看命令

(1)oracle中怎樣查看總共有哪些用戶

select*fromallusers;

(2)查看oracle當(dāng)前連接數(shù)

怎樣查看oracle當(dāng)前的連接數(shù)呢？只需要用下面的SQL語句查詢一下就可以了。

select*fromv$sessionwhereusernameisnotnullselect

username,count(username)fromv$sessionwhereusernameisnotnullgroupby

username并查看不同用戶的連接數(shù)

selectcount(*)fromv$session#連接數(shù)

Selectcount(*)fromv$sessionwherestatus='ACTIVE'#并發(fā)連接數(shù)

(3)列出當(dāng)前數(shù)據(jù)庫建立的會話情況

selectsid,serial#,username,program,machine,statusfromv$session;

實驗3表與視圖的基礎(chǔ)操作

3.1創(chuàng)建基本表

例3-1創(chuàng)建學(xué)生、課程、選課三個表，在SQLPLUS的啟動界面輸入以下代碼：

SQL>CreateTableS(SnoVarchar2(10)PrimaryKey,SnameVarchar2(10)Not

Null,SsexChar(2),SageNumber,SdeptVarchar2(40));

SQL>CreateTableCourse(CnoVarchar2(10),CnameVarchar2(50),Ccredit

Number,ConstraintPk_CPrimaryKey(Cno));

SQL>CreateTableSC(SnoVarchar2(10),CnoVarchar2(10),ScoreNumber

Default0Check(ScoreBetween0And100),ConstraintPk_SPrimaryKey

(Sno,Cno))TABLESPACE"Testspace”;一使用Testspace表空間

3.2修改表

i、修改表空間的相關(guān)操作

1)增加表空間中的數(shù)據(jù)文件

AlterTablespaceTestspaceAddDatafile'c:\app\qxz\file_3.dbf'size

100m;

2)刪除表空間中的數(shù)據(jù)文件

AlterTablespaceTestspaceDropDatafile'c:\app\qxz\file_3.dbf';

3)修改表空間文件的數(shù)據(jù)文件大小

AlterDatabaseDatafile'c:\app\qxz\file_2.dbf'Resize50m;

4)修改表空間數(shù)據(jù)文件的自動增長屬性。

AlterDatabaseDatafile,c:\app\qxz\file_l.dbf1AutoextendOff;--Off不能自

動增長

2、修改表結(jié)構(gòu)的相關(guān)操作：

1)插入屬性

例3-2在S表插入新屬性地址。

SQL>AlterTableSAdd(AddressVarchar(100));

2)修改屬性

例3-3對上述性別屬性的數(shù)據(jù)類型進(jìn)行修改，并且默認(rèn)值為男”。

SQL>AlterTableSModify(SsexVarchar2(2)Default'男')；

3)刪除表屬性

例3-4刪除上述表中的地址屬性。命令為:SQL>AlterTableSDrop(Address);

注意：通常在系統(tǒng)不忙的時候刪除不使用的字段，可以先設(shè)置字段為unused；

AlterTableSSetUnusedColumnAddress;

系統(tǒng)不忙時再執(zhí)行刪除:AlterTableSDropUnusedColumn;

4)表重命名

例3-5把表SC改名為Learn。命令為：SQL>RenameScToLearn;

5)清空表中的數(shù)據(jù)

例3-6清空學(xué)生表的信息。命令為：SQL>TruncateTableS;

6)給表增加注釋

例3-7對表S添加注釋為'thisIsATestTable'

SQL>CommentOnTableSIs'ThisIsATestTable,;

7)給列添加注釋

例3-8對表S的Sno屬性添加'學(xué)號’的注釋。

SQL>CommentOnColumnS.SnoIs'學(xué)號';

3.3刪除表

例3-9刪除Course表。命令為：SQL>DropTableCourse;

3.5創(chuàng)建和管理視圖

1、創(chuàng)建視圖

例3T0在S表中創(chuàng)建以學(xué)號、姓名、系別的新視圖。

SQL>CreateOrReplaceViewV_S(Num,Name,Sdept)AsSelectSno,Sname,

SdeptFromS;

例3-11在SC上定義新視圖，當(dāng)用update修改數(shù)據(jù)時，必須滿足視圖score>60的條

件，不滿足則不能被改變。

SQL>CreateOrReplaceViewVSCAsSelect*FromSCWhereScore>60WithCheck

Option;

例3T2創(chuàng)建新視圖，按照學(xué)號分組顯示學(xué)生的最高、最低分和平均成績。

SQL>CreateViewV_S_SC(Num,Smin,Smax,Savg)AsSelect

D.Sno,Min(E.Score),Max(E.Score),Avg(E.Score)FromSCE,SDWhereE.Sno=D.Sno

GroupByD.Sno;

2、查詢視圖

例3-13查詢上述建立的視圖。命令為:SQDSelect*FromV_S_SC;

3、更新視圖

例3-14把所有學(xué)號為08開頭的學(xué)生的相關(guān)系別信息改為管理系。

SQL>UpdateVSSetSdept=，Management'WhereNum1ike'08%";

3.6表或視圖的導(dǎo)入與導(dǎo)出操作

1、Oracle數(shù)據(jù)間的導(dǎo)入導(dǎo)出imp/exp

下面是導(dǎo)入導(dǎo)出的實例，導(dǎo)入導(dǎo)出的其它例子或方法請參閱實驗13。(1)數(shù)據(jù)導(dǎo)出

1)將數(shù)據(jù)庫orcl完全導(dǎo)出，用戶名system密碼orcl,導(dǎo)出到c:\orcl.dmp中。exp

system/orcl@orcl2file=c:\orcl.dmpfull=y

2)將數(shù)據(jù)庫噸jxgl用戶與scott用戶的表導(dǎo)出。expsystem/orcl@orcl2

file=c:\orcl_jxglscott.dmpowner=(jxgl,scott)

3)將數(shù)據(jù)庫和jxgl用臚的表student,sc導(dǎo)出。expjxgl/jxgl@orcl2

file=c:\orcl_jxgl_studentsc.dmptables=(student,sc)

4)將數(shù)據(jù)庫中jxgl用戶的表student中年齡大于等于19的學(xué)生記錄導(dǎo)出。exp

jxgl/jxgl@orcl2file=c:\orcl_jxgl_student_agegel9.dmptables=(student)

query='〃wheresage>=19\，/

上面是常用的導(dǎo)出，對于壓縮導(dǎo)出，只要在上面命令后面加上compress=y就可以了。

(2)數(shù)據(jù)的導(dǎo)入

1)將c:\orcl.dmp中的獺(據(jù)導(dǎo)入orcl數(shù)據(jù)中。imp

system/orcl@orcl2file=c:\orcl.dmp

上面可能有點問題，因為有的表己經(jīng)存在，然后它就報錯，對該表就不進(jìn)行導(dǎo)入。在

后面加上ignore=y就可以了。

2)將c:\orcl_jxgl_studentsc.dmp中的表sc導(dǎo)入。impjxgl/jxgl@orcl2file=

c:\orcl_jxgl_studentsc.dmptables=(sc)ignore=y

實驗4SQL語言——SELECT查詢操作

創(chuàng)建Student、SC^Course三表及添加表記錄命令如下：

CreateTableStudent

(SnoCHAR(5)NOTNULL,

SnameVARCHAR(20),

SageSMALLINTCHECK(Sage>=15ANDSage<=45),

SsexCHAR(2)DEFAULT'男'CHECK(Ssex='男'ORSsex='女')，

SdeptCHAR(2),constraintprsnoprimarykey(sno));

CreateTableCourse(CnoCHAR(2)NOTNULL,CnameVARCHAR(20),Cpno

CHAR(2),CcreditSMALLINT,constraintprCnoprimarykey(Cno));

CreateTableSC(SnoCHAR(5)NOTNULL,CnoCHAR(2)NOTNULL,GradeSMALLINT

CHECK((GradeISNULL)OR(GradeBETWEEN0AND100)),PRIMARY

KEY(Sno,Cno),CONSTRAINTCFFOREIGNKEY(Cno)REFERENCESCourse(Cno),CONSTRAINT

S_FFOREIGNKEY(Sno)REFERENCESStudent(Sno));

INSERTINTOStudentVALUES98001','錢橫'，18,'男'，'CS，)

INSERTINTOStudentVALUESC98002','王林'，19,'女'，'CS')

INSERTINTOStudentVALUES('98003','李民'，20,'男'，'IS')

INSERTINTOStudentVALUES('98004','趙三'，16,'女'，'MA')

INSERTINTOCourseVALUESCT，'數(shù)據(jù)庫系統(tǒng)‘，'5',4);

INSERTINTOCourseVALUES('2'，'數(shù)學(xué)分析'，null,2);

INSERTINTOCourseVALUES('3'，'信息系統(tǒng)導(dǎo)論‘，'1',3);

INSERTINTOCourseVALUESC4'，'操作系統(tǒng)原理‘，'6',3);

INSERTINTOCourseVALUES('5'，‘?dāng)?shù)據(jù)結(jié)構(gòu)‘，’7',4);

INSERTINTOCourseVALUESC6'，'數(shù)據(jù)處理基礎(chǔ)'，null,4);

INSERTINTOCourseVALUES('7','C語言‘，’6',3);

INSERTINTOSCVALUESC98001'.'l',87);INSERTINTOSC

VALUES('98001，,，2,,67);INSERTINTOSCVALUES('98001','3',90);INSERTINTOSC

VALUES('98002','2',95);INSERTINTOSCVALUES('98002,,'3',88);

例4-1

SELECTDISTINCTSno

FROMSC

WHEREGrade>=90;

例4-2

SELECTSname,Ssex

FROMStudent

WHERESage>18ANDSdeptNOTIN('IS','MA');

例4-3

SELECT*FROMCourseWHERECnameLIKE導(dǎo)，ESCAPE，#，；

例4-4

SELECTCOUNT(DISTINCTSno)/*加DISTINCT去掉重復(fù)值后計數(shù)*/

FROMSC;

例4-5

SELECTStudent.SnoFROMStudent,SC

WHERESdept=,CS，ANDStudent.Sno=SC.Sno

GROUPBYStudent.SnoHAVINGCOUNT(*)>=2;

例4-6

SelectStudent.*,SC.*FromStudent,SC;

或SelectStudent.*,SC.*FromStudentCrossJoinSC;

例4-7

Select*FromStudent,SCWHEREStudent.Sno=SC.Sno;

例4-8

SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

FROMStudent,SCWIIEREStudent.Sno=SC.Sno;

或SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

FROMStudentINNERJOINSCONStudent.Sno=SC.Sno;

例4-9

SELECTFIRST.Cno,SECOND.cpnoFKOMCourseFIRST,CourseSECOND

WHEREFIRST.cpno=SEC0ND.Cno;

我們?yōu)镃ourse表取兩個別名FIRST與SECOND,這樣就可以在SELECT子句和WHERE子

句中的屬性名前分別用這兩個別名加以區(qū)分。

例4-10

SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

FROMStudentLeftOuterJOINSCONStudent.Sno=SC.Sno;

例4-11

SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,

Course.Cno,Grade,cname,cpno,ccredit

FROMStudentLeftOuterJOINSCONStudent.Sno=SC.SnoFullOuterjoinCourse

onSC.eno=Course,eno;

例4-12

SELECTStudent.*,Cno,GradeFROMSTUDENTINNERJOINSCONStudent.Sno=SC.Sno

WHERESSEX='男'ANDGRADE>=60

例4-13

SELECT*FROMStudent

WHERESdeptIN(SELECTSdeptFROMStudentWHERESname='錢橫')；

或SELECT*FROMStudent

WHERESdept=(SELECTSdeptFROMStudent

WHERESname='錢橫')；一當(dāng)子查詢?yōu)閱瘟袉涡兄禃r可以用“二”

或SELECTSL*FROMStudentSI,StudentS2

WHERESl.Sdept=S2.SdeptANDS2.Sname='錢橫'；

一般來說，連接查詢可以替換大多數(shù)的嵌套子查詢。

SQL-92支持“多列成員”的屬于(IN)條件表達(dá)，例：

例4-14

Select*fromStudentT

Where(T.sdept,T.sage,T.ssex)IN(Selectsdept,sage,ssexFromstudentS

WhereS.snoOT.sno);-Oracle支持的

它等價于逐個成員IN的方式表達(dá)，如下：

Select*fromStudentTWhereT.sdeptIN

(SelectsdeptFromstudentS

WhereS.snoOT.snoandT.sageIN

(SelectsageFromstudentXWhereS.sno=X.snoandX.snoOT.sno

andT.ssexIN

(SelectssexFromstudentYWhereX.sno=Y.snoandY.snoOT.sno)));

例4-15

SELECTSno,Sname,SdeptFROMStudent--IN嵌套查詢方法

WHERESnoIN

(SELECTSnoFROMSC

WHERECnoIN(SELECTCnoFROMCourseWHERECname='數(shù)據(jù)庫系統(tǒng)'))；

或SELECTSno,Sname,SdeptFROMStudent-IN、=嵌套查詢方法

WHERESnoIN

(SELECTSnoFROMSC

WHERECno=(SELECTCnoFROMCourseWHERECname='數(shù)據(jù)庫系統(tǒng)'))；

或SELECTStudent.Sno,Sname,Sdept一連接查詢方法

FROMStudent,SC,Course

WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname='數(shù)據(jù)庫系統(tǒng)

或SelectSno,Sname,SdeptFromStudent-Exists嵌套查詢方法

WhereExists(Select*FromSCWhereSC.Sno=Student.SnoAnd

Exists(Select*FromCourse

WhereSC.Cno=Course.CnoAndCname=，數(shù)據(jù)庫系統(tǒng)'))；

或SelectSno,Sname,SdeptFromStudent-Exists嵌套查詢方法

WhereExists(Select*FromcourseWhereCname='數(shù)據(jù)庫系統(tǒng)'and

Exists(Select*FromSCWheresc.sno=student.snoandSC.Cno=

Course.Cno));

例4-16

SELECTSno,SnameFROMStudent

WHERESnoNOTIN(SELECTSnoFROMSCWHERECnoIN('2','4'));

例4-17

SELECTSnameFROMStudent

WHERESage>A11(SELECTSageFROMStudent

WHERESdept='IS')ANDSdept<>'IS'

ORDERBYSname;

本查詢實際上也可以用集函數(shù)實現(xiàn)：

SELECTSnameFROMStudent

WHERESage>(SELECTMAX(Sage)FROMStudent

WHERESdept=TS')ANDSdeptO'IS"

ORDERBYSname;

例4-18

SELECTDISTINCTCNAMEFROMCOURSEC

WHERE'女'=ALL(SELECTSSEXFROMSC,STUDENT

WHERESC.SNO=STUDENT.SNOANDSC.CNO=C.CNO);

或SELECTDISTINCTCNAMEFROMCOURSEC

WHERENOTEXISTS

(SELECT*FROMSC,STUDENT

WHERESC.SNO=STUDENT.SNOANDSC.CNO=C.CNOANDSTUDENT.SSEX='男')；

例4T9

SELECTSnameFROMStudent

WHERENOTEXISTS

(SELECT*FROMSCWHERESno=Student.SnoANDCno=T');

或SELECTSnameFROMStudent

WHERESnoNOTIN(SELECTSnoFROMSCWHERECno='T);

但如下是錯的：

SELECTSnameFROMStudent,SCWHERESC.Sno=Student.SnoANDCnoO'l’;

例4-20

SELECTSnameFROMStudent

WHERENOTEXISTS

(SELECT*FROMCourseWHERENOTEXISTS

(SELECT*FR0MSCWHERESno=SC.SnoANDCno=Course.Cno));

由于沒有全稱量詞，我們將題目的意思轉(zhuǎn)換成等價的存在量詞的形式：查詢這樣的學(xué)

生姓名沒有一門課程是他不選的。

本題的另一操作方法是：

SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.Sno

GroupbyStudent.Sno,Snamehavingcount(*)>=(SELECTcount(*)FROMCourse);

例4-21

SELECTSnoFROMStudentSX

WHERENOTEXISTS

(SELECT*FROMSCSCY

WHERESCY.Sno=98001'ANDNOTEXISTS

(SELECT*FROMSCSCZWHERESCZ.Sno=SX.SnoAND

SCZ.Cno=SCY.Cno));

例4-22

SELECTSnoFROMSCWHERECno=T'

UNION

SELECTSnoFROMSCWHERECno='2';

SELECTSnoFROMSCWHERECno=T'

INTERSECT

SELECTSnoFROMSCWHERECno='2';"查詢既選課程1又選課程2的學(xué)生學(xué)號集

例4-23

SELECT*FROMStudentWHERESdept=CS，

INTERSECT

SELECT*FROMStudentWHERESage<=19;

本查詢等價于“鱉詢計算機科學(xué)系中年齡不大于19歲的學(xué)生J,為此變通法為：

SELECT*FROMStudentWHERESdept=CS,ANDSage<=19;

例4-24

SELECTSnoFROMSCWHERECno='2'

MINUS

SELECTSnoFROMSCWHERECno=T;

本例實際上是查詢選修了課程2但沒有選修課程1的學(xué)生。為此變通法為：

SELECTSnoFROMSC

WHERECno=’2'ANDSnoNOTIN(SELECTSnoFROMSCWHERECno=f;

例4-25

Selectstuno,sname,avgr

FromStudent,(Selectsnostuno,avg(grade)avgrFromSCGroupBysno)SG

WhereStudent.sno=SG.stu_noAndavgr>85;

SQL-92允許在From中使用看詢表達(dá)式，并必須為查詢表達(dá)式取名。它等價于如下未

使用查詢表達(dá)式的形式：

SelectStudent.Sno,Sname,AVG(Grade)

FromStudent,SCWhereStudent.Sno=SC.Sno

GroupByStudent.Sno,SnameHAVINGAVG(Grade)>85;

例4-26

SELECTSNAME,CNAME,GRADE

FROM(SELECTSNAME,CNAME,GRADEFROMSTUDENT,SC,COURSE

WHERESSEX='女'ANDSTUDENT.SNO=SC.SNOANDSC.CNO=COURSE.CNO)

TEMPWHEREGRADE>90;一特意用查詢表達(dá)式實現(xiàn)，完全可用其它方式實現(xiàn)

但如下使用查詢表達(dá)式的查詢，則不易改寫為其它形式。

例4-27

Selectavgr,COUNT(*)

From(Selectsno,avg(grade)avgrFromSCGroupBysno)SG

GroupByavgr;

例4-28

GRANTCREATEVIEWTOJXGL—賦予用戶JXGLCREATEVIEW的權(quán)力

CREATEVIEWIS_Student

ASSELECTSno,Sname,Sage,Ssex

FROMStudentWHERESdept='IS'WITHCHECKOPTION

GO

SELECT*FROMIS_StudentWHERESage>=18ANDSsex=女'；

實驗5SQL語言一一數(shù)據(jù)更新操作

5.1INSERT命令

例5-1

InsertIntoStudentValues('98011'張靜'，27,'女CS');Commit;

Insert語句后可跟returning子句來獲取插入記錄的某字段值。程序代碼如下：

Setserveroutputon

Declare

bndlstudent.sno%TYPE;

bnd2student.sname%TYPE;

Begin

InsertIntoStudent(sno,sname,sage,ssex,sdept)Values('98011'張靜

',27,‘女'，'CS')RETURNINGsno,Student,snameINTObndl,bnd2;

dbmsoutput.put_line(bndl||’||bnd2);

End;

例5-2

InsertIntoStudent(Sno,Sname,Sage)Values98012,,'李四16);Commit;

例5-3

CreateSequencettincrementby1minvalue101maxvaluecycle;

Createtabletestable(idint,rqdate);

InsertintotestableValues(tt.nextval,sysdate);

若要刪除序列命令為：DropSequencett;若刪除測試表testable命令為：Droptable

testable;

例5-4

InsertIntoSC

Selectsno,eno,nullFromStudent,CourseWhereSdept='CS'andeno='5’;

Commit;

例5-5

InsertIntoStudent

Selectcast(cast(snoasinteger)+1as

char(5)),CONCAT(sname,*2*),sage,ssex,sdeptFromStudentWhereSname=，趙三'；

Commit;

SELECT*FROMStudent;一查看結(jié)果(執(zhí)行結(jié)果略)

5.2UPDATE命令

例5-6

UPDATEStudentSETSage=23WHERESno='98003,;

例5-7

UPDATEStudentSETStudent,Sage=Student.Sage+1

wheresnoin

(SELECTsnoFROM(SELECT*FROMStudentORDERBYsno)

WHEREROWNUM<=3);

例5-8

UpdateSC

SetGrade=(SelectAVG(Grade)FromSCWhereCno=，3?)WhereSno='98001'and

Cno=,3';

Update語句后也可跟上Returning子句來獲取更新后記錄的字段值。程序代碼如下：

Setserveroutputon

Declare

bndlsc.sno%TYPE;bnd2sc.grade%TYPE;

Begin

UpdateSCSetGrade13(SelectAVG(Grade)FromSCWhereCno=,3')WhereSno

二'98001'andCn。='3'RETURNINGsc.sno,sc.gradeINTObndl,bnd2;

dbmsoutput.putline(bndl||J|tochar(bnd2));

End;

例5-9

UPDATESCSETGRADE=0

WHERECN0='2'AND

‘王林'=(SELECTSNAMEFROMSTUDENTWHERESTUDENT.SNO=SC.SNO);

例5-10

Updatestudent

Setsage=(selectavg(sage)fromstudentwheresdept='CS'),

(ssex,sdept)=(selectssex,sdeptfromstudentwheresno='98003')

Wheresno='98002'

5.3DELETE命令

先備份選修表SC到TSC中,命令為：

CreateTableTSCasSelect*FromSC一備份到表TSC中

例5-11

SELECT*FROMSC一刪除前

DELETEFROMSC一刪除中

WHERE'CS'=(SELECTSdeptFROMStudent

WHEREStudent.Sno=SC.Sno)

SELECT*FROMSC一刪除后

DELETE語句后也可跟上Returning子句來獲取剛刪除記錄相應(yīng)的字段值。程序代碼如

下：

Setserveroutputon

Declare

bndlsc.sno%TYPE;bnd2sc.grade%TYPE;

Begin

DELETEFROMSC—刪除中

WHERESNO=，98001'ANDcno='2'RETURNINGsc.sno,sc.gradeINTObndl,bnd2;

dbmsoutput.putline(bndl|,|；tochar(bnd2));

End;

例5-12

DELETEFROMjxgl.SC@remoteWHERESNO="980011;

例5-13DELETEFR0MSC;

如TRUNCATETABLESC,清空SC表。

從表TSC恢復(fù)數(shù)據(jù)到表SC,命令為：

INSERTINTOSCSELECT*FROMTSC―這是一種方便、簡易地恢復(fù)數(shù)據(jù)的方法。

實驗6嵌入式SQL應(yīng)用

Pro*C程序舉例

例如：“example.pc”程序能完成輸入雇員號、雇員名、職務(wù)名和薪金等信息，并插

入到雇員表emp(Oracle缺省安裝后SCOTT用戶連接能存取到該表)中的功能。

#defineUSERNAME"SCOTT"〃連接Oracle的用戶名

#definePASSWORD〃scott〃〃連接Oracle的用戶口令

#defineSERVER'localhost:1521/orcl"〃連接Oracle的用戶口令

tfinclude<stdio.h>

ttinclude<string.h>

#include<stdlib.h>

#include<sqlda.h>

^include<sqlcpr.h>

EXECSQLINCLUDEsqlca;

EXECSQLBEGINDECLARESECTION;

char*username=USERNAME;char*password=PASSWORD;

char*server=SERVER;varcharsqlstmt[80];

intempnum;varcharemp_name[15];

varcharjob[50];floatsalary;

EXECSQLENDDECLARESECTION;

voidsqlerror();

main()

{EXECSQLWHENEVERSQLERRORDOsqlerror();〃錯誤處理

EXECSQLCONNECT:usernameIDENTIFIEDBY:passwordUSING:server;〃連接

oracle

sqlstmt.len=sprintf(sqlstmt.arr,“INSERTINTOEMP(EMPNO,ENAME,JOB,SAL)

VALUES(:VI,:V2,:V3,:V4)");

EXECSQLPREPARESFROM:sqlstmt;//SQL命令區(qū)S動態(tài)準(zhǔn)備

for(;;)

{printf(^Xnenteremployeenumber:");scanf(〃％d〃，&empnum);

if(empnum二二0)break;

printf(z/\nenteremployeename:");scanf(〃%s〃，emp_name.arr);

emp_name.1en=str1en(emp_name.arr);

printf(，z\nenteremployeejob:");scanf(〃％s〃，job.arr);

job.len=strlen(job.arr);

printf(^Xnenteremployeesalary:，z);scanf&salary);

printf(，，%d--%s--%s--%f〃，empnum,emp_name.arr,job.arr,salary);

//以下通過命令區(qū)S參數(shù)化動態(tài)執(zhí)行SQL命令

EXECSQLEXECUTESUSING:empnum,:empname,:job,:salary;

}

EXECSQLCOMMITWORKRELEASE;

exit(0);

)

voidsqlerror(){〃錯誤處理程序

EXECSQLWHENEVERSQLERRORCONTINUE;

printfCAnOracleerrordetected:\n，z);

printf(，z\n%.70s\n〃，sqlca.sqlerrm.sqlerrmc);

EXECSQLROLLBACKWORKRELEASE;//出錯回滾,取消操作。

exit(1);

)