1、Oracle數(shù)據(jù)庫技術(shù)與實驗指導(dǎo)2011.08第0章 實用Oracle數(shù)據(jù)庫技術(shù)Oracle的官方網(wǎng)站為；其次然后就是一些著名網(wǎng)站如：、，這里有很多經(jīng)驗之談。遇到問題了還可以第一時間找，這里會給你最詳細的解釋。Oracle 10g/11g數(shù)據(jù)庫都分為標準版（Standard Edition）、標準版1（Standard Edition One）以及企業(yè)版（Enterprise Edition）?？蓮娜缦戮W(wǎng)址下載、學(xué)習(xí)或試用Oracle：SQL Developer也可以單獨免費下載安裝的。下載地址為：SQL*Plus Instant Client（SQL*Plus即時客戶端）SQL*Plus I

2、nstant Client下載: 實驗1 數(shù)據(jù)庫系統(tǒng)基礎(chǔ)操作Oracle Database 11g 第 2 版(.0)的下載地址： 適用于 Microsoft Windows（32 位）的 Oracle Database 11g 第 2 版 (.0) 的下載地址：實驗2 數(shù)據(jù)庫基礎(chǔ)操作 手工建庫手工建庫須要經(jīng)過幾個步驟，每一個步驟都非常關(guān)鍵。它包括： 1、創(chuàng)建相關(guān)目錄（數(shù)據(jù)文件和跟蹤文件）（假設(shè)要創(chuàng)建KCGL數(shù)據(jù)庫，Oracle已安裝于“c:appqxz”目錄）在c:appqxzadmin這個目錄之下創(chuàng)建KCGL文件夾；在C:appqxzadminKCGL 這個

3、目錄之下創(chuàng)建adump文件夾；在C:appqxzadminKCGL 這個目錄之下創(chuàng)建dpdump文件夾；在C:appqxzadminKCGL 這個目錄之下創(chuàng)建pfile文件夾；在C:appqxzoradata 這個目錄之下創(chuàng)建KCGL文件夾； 2、創(chuàng)建初始化參數(shù)文件通過復(fù)制現(xiàn)有的初始化參數(shù)文件C:appqxzadminorclpfile這個目錄下的參數(shù)文件“init.ora.*”（*為數(shù)字擴展名）到C:appqxzproduct11.2.0dbhome_1database這個目錄，修改名為initKCGL.ora,最后用記事本打開這個參數(shù)文件，修改如下幾個參數(shù)的值：audit_file_des

4、t=C:appqxzadminKCGLadumpdb_name=KCGLcontrol_files=("C:appqxzoradataKCGLcontrol01.ctl","C:appqxzoradataKCGLcontrol02.ctl") 3、打開DOS窗口，設(shè)置環(huán)境變量：Set oracle_sid=KCGL 4、創(chuàng)建服務(wù)： Oradim -new -sid KCGL 5、創(chuàng)建口令文件 Orapwd file=C:appqxzproduct11.2.0dbhome_1databasepwdKCGL.ora Password=12345 6、啟動服務(wù)

5、器：Sqlplus /nolog Conn / as sysdba Startup nomount 7、執(zhí)行建庫腳本：CREATE DATABASE KCGLdatafile 'c:appqxzoradataKCGLsystem01.dbf' size 300mautoextend on next 10m extent management localSysaux datafile 'c:appqxzoradataKCGLsysaux01.dbf' size 120mundo tablespace undotbs1datafile 'c:appqxzor

6、adataKCGLundotbs01.dbf' size 100m default temporary tablespace temptbs1tempfile 'c:appqxzoradataKCGLtemp01.dbf' size 50mlogfile group 1 ('c:appqxzoradataKCGLredo01.log') size 50m, group 2 ('c:appqxzoradataKCGLredo02.log') size 50m, group 3 ('c:appqxzoradataKCGLredo03.

7、log') size 50m; 用記事本編輯以上內(nèi)容，假定保存為C:CREATEKCGL.sql文件，然后執(zhí)行這個腳本。 Start C:CREATEKCGL.sql 不管出現(xiàn)哪種錯誤，都要刪除C:appqxzoradataKCGL目錄下創(chuàng)建的所有文件，改正錯誤后，重新啟動實例，再執(zhí)行建庫腳本。 8、創(chuàng)建數(shù)據(jù)字典和包 Start C:appqxzproduct11.2.0dbhome_1RDBMSADMINcatalog Start C:appqxzproduct11.2.0dbhome_1RDBMSADMINcatproc 9、執(zhí)行pupbld.sql腳本文件 切換成system用戶

8、執(zhí)行如下命令：Conn system/manager Start C:appqxzproduct11.2.0dbhome_1sqlplusadminpupbld 10、執(zhí)行scott腳本創(chuàng)建scott方案 Start C:appqxzproduct11.2.0dbhome_1RDBMSADMINscott.sql 這時需要修改密碼：Conn / as sysdba Alter user scott identified by tiger; 再連接scott：Conn scott/tiger 11、select * from dept;能顯示出dept表的結(jié)果，表示新數(shù)據(jù)庫KCGL已安裝成功了。

9、2.2 查看數(shù)據(jù)庫1、查看表空間的名稱及大小select tablespace_name,min_extents,max_extents,pct_increase,status from dba_tablespaces;select tablespace_name,initial_extent,next_extent,contents,logging,extent_management,allocation_type from dba_tablespaces order by tablespace_name;select t.tablespace_name, round(sum(bytes/(

10、1024*1024),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_namegroup by t.tablespace_name;2、查看表空間物理文件的名稱及大小column db_block_size new_value blksz noprintselect value db_block_size from v$parameter where name='db_block_size'column tablespace_name forma

11、t a16;column file_name format a60;set linesize 160; - 為sqlplus 命令select file_name,round(bytes/(1024*1024),0) total_space,autoextensible,increment_by*&blksz/(1024*1024) as incement,maxbytes/(1024*1024) as maxsize from dba_data_files order by tablespace_name; - blksz一般為8192select tablespace_name,

12、file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_files order by tablespace_name;3、查看回滾段名稱及大小select a.owner |' . '| a.segment_name roll_name , a.tablespace_name tablespace , to_char(a.initial_extent) | '/' | to_char(a.next_extent) in_extents , to_char(a.min_exten

13、ts) | '/' | to_char(a.max_extents) m_extents , a.status status , b.bytes bytes , b.extents extents , d.shrinks shrinks , d.wraps wraps , d.optsize opt from dba_rollback_segs a , dba_segments b , v$rollname c , v$rollstat d where a.segment_name = b.segment_name and a.segment_name = (+)

14、 and c.usn = d.usn (+) order by a.segment_name;select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ;4、查看控制文件 selec

15、t name from v$controlfile;5、查看日志文件 select member from v$logfile;6、查看表空間的使用情況select * from (select sum(bytes)/(1024*1024) as "free_space(m)",tablespace_name from dba_free_space group by tablespace_name) order by "free_space(m)"SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C

16、.BYTES FREE,(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE" FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 7、查看數(shù)據(jù)庫庫對象 select owner, object_type, status, count(*) count# from

17、 all_objects group by owner,object_type,status;8、查看數(shù)據(jù)庫的版本select * from v$version;Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle'9、查看數(shù)據(jù)庫的創(chuàng)建日期和歸檔方式 select created,log_mode,log_mode from v$database;10、查看臨時數(shù)據(jù)庫文件select status, enabled, name from v$tempfile;常用數(shù)據(jù)庫信息

18、查看命令（1）oracle中怎樣查看總共有哪些用戶select * from all_users;（2）查看oracle當(dāng)前連接數(shù)怎樣查看oracle當(dāng)前的連接數(shù)呢？只需要用下面的SQL語句查詢一下就可以了。select * from v$session where username is not null select username,count(username) from v$session where username is not null group by username #查看不同用戶的連接數(shù) select count(*) from v$session #連接數(shù)Select

19、 count(*) from v$session where status='ACTIVE'#并發(fā)連接數(shù)（3）列出當(dāng)前數(shù)據(jù)庫建立的會話情況select sid,serial#,username,program,machine,status from v$session;實驗3 表與視圖的基礎(chǔ)操作3.1創(chuàng)建基本表 例3-1 創(chuàng)建學(xué)生、課程、選課三個表，在SQL PLUS 的啟動界面輸入以下代碼： SQL>Create Table S(Sno Varchar2(10) Primary Key, Sname Varchar2(10) Not Null,Ssex Char(2),

20、Sage Number,Sdept Varchar2(40); SQL>Create Table Course(Cno Varchar2(10),Cname Varchar2(50),Ccredit Number,Constraint Pk_C Primary Key (Cno); SQL>Create Table SC(Sno Varchar2(10), Cno Varchar2(10),Score Number Default 0 Check (Score Between 0 And 100),Constraint Pk_S Primary Key (Sno

21、,Cno) TABLESPACE "Testspace"  -使用Testspace表空間3.2修改表 1、修改表空間的相關(guān)操作 1）增加表空間中的數(shù)據(jù)文件Alter Tablespace Testspace Add Datafile 'c:appqxzfile_3.dbf' size 100m; 2）刪除表空間中的數(shù)據(jù)文件 Alter Tablespace Testspace Drop Datafile 'c:appqxzfile_3.dbf' 3）修

22、改表空間文件的數(shù)據(jù)文件大小Alter Database Datafile 'c:appqxzfile_2.dbf' Resize 50m; 4）修改表空間數(shù)據(jù)文件的自動增長屬性。Alter Database Datafile 'c:appqxzfile_1.dbf' Autoextend Off; -Off不能自動增長 2、修改表結(jié)構(gòu)的相關(guān)操作：1）插入屬性例3-2 在S表插入新屬性地址。SQL>Alter Table S Add( Address Varchar(100); 2）修改屬性 例3-

23、3 對上述性別屬性的數(shù)據(jù)類型進行修改，并且默認值為“男”。SQL>Alter Table S Modify( Ssex Varchar2(2) Default '男'); 3）刪除表屬性 例3-4 刪除上述表中的地址屬性。命令為：SQL>Alter Table S Drop(Address); 注意：通常在系統(tǒng)不忙的時候刪除不使用的字段，可以先設(shè)置字段為unused；Alter Table S Set Unused Column Address; 系統(tǒng)不忙時再執(zhí)行刪除：Alter Table S Drop Unused Column; 4）表重命名 例3-5 把表S

24、C改名為Learn。命令為：SQL>Rename Sc To Learn; 5）清空表中的數(shù)據(jù) 例3-6 清空學(xué)生表的信息。命令為： SQL>Truncate Table S; 6）給表增加注釋 例3-7 對表S添加注釋為'this Is A Test Table'SQL>Comment On Table S Is 'This Is A Test Table' 7）給列添加注釋 例3-8 對表S的Sno屬性添加學(xué)號的注釋。SQL>Comment On Column S.Sno Is '學(xué)號'3.3刪除表 例3-9 刪除Co

25、urse表。命令為： SQL>Drop Table Course;3.5 創(chuàng)建和管理視圖 1、創(chuàng)建視圖 例3-10 在S表中創(chuàng)建以學(xué)號、姓名、系別的新視圖。 SQL>Create Or Replace View V_S(Num,Name,Sdept) As Select Sno, Sname, Sdept From S; 例3-11 在SC上定義新視圖，當(dāng)用update修改數(shù)據(jù)時，必須滿足視圖score>60的條件，不滿足則不能被改變。 SQL>Create Or Replace View V_SC As Select * From SC Where Score>

26、60 With Check Option; 例3-12 創(chuàng)建新視圖，按照學(xué)號分組顯示學(xué)生的最高、最低分和平均成績。 SQL>Create View V_S_SC (Num,Smin,Smax,Savg) As Select D.Sno,Min(E.Score),Max(E.Score),Avg(E.Score) From SC E,S D Where E.Sno=D.Sno Group By D.Sno; 2、查詢視圖 例3-13 查詢上述建立的視圖。命令為：SQL>Select * From V_S_SC; 3、更新視圖 例3-14 把所有學(xué)號為08開頭的學(xué)生的相關(guān)系別信息改為管

27、理系。 SQL>Update V_S Set Sdept='Management' Where Num like '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/orclorcl2 file=c:orcl.dmp full=y 2）將數(shù)據(jù)庫中jxgl用戶與scott用戶的表導(dǎo)出。exp system/orclorcl2 file

28、=c:orcl_jxglscott.dmp owner=(jxgl,scott)3）將數(shù)據(jù)庫中jxgl用戶的表student,sc導(dǎo)出。exp jxgl/jxglorcl2 file=c:orcl_jxgl_studentsc.dmp tables=(student,sc) 4）將數(shù)據(jù)庫中jxgl用戶的表student中年齡大于等于19的學(xué)生記錄導(dǎo)出。exp jxgl/jxglorcl2 file=c:orcl_jxgl_student_agege19.dmp tables=(student) query=" where sage>=19"上面是常用的導(dǎo)出，對于壓縮導(dǎo)

29、出，只要在上面命令后面加上 compress=y就可以了。（2）數(shù)據(jù)的導(dǎo)入1）將c:orcl.dmp中的數(shù)據(jù)導(dǎo)入orcl數(shù)據(jù)庫中。imp system/orclorcl2 file=c:orcl.dmp上面可能有點問題，因為有的表已經(jīng)存在，然后它就報錯，對該表就不進行導(dǎo)入。在后面加上 ignore=y 就可以了。2）將c:orcl_jxgl_studentsc.dmp中的表sc 導(dǎo)入。imp jxgl/jxglorcl2 file= c:orcl_jxgl_studentsc.dmp tables=(sc) ignore=y實驗4 SQL語言SELECT查詢操作創(chuàng)建Stud

30、ent、SC、Course三表及添加表記錄命令如下：Create Table Student( Sno CHAR(5) NOT NULL,Sname VARCHAR(20),Sage SMALLINT CHECK(Sage>=15 AND Sage<=45),Ssex CHAR(2) DEFAULT '男' CHECK (Ssex='男' OR Ssex='女'),Sdept CHAR(2),constraint pr_sno primary key(sno);Create Table Course( Cno CHAR(2) NOT

31、NULL,Cname VARCHAR(20),Cpno CHAR(2),Ccredit SMALLINT,constraint pr_Cno primary key(Cno);Create Table SC( Sno CHAR(5) NOT NULL,Cno CHAR(2) NOT NULL,Grade SMALLINT CHECK (Grade IS NULL) OR (Grade BETWEEN 0 AND 100),PRIMARY KEY(Sno,Cno),CONSTRAINT C_F FOREIGN KEY(Cno) REFERENCES Course(Cno), CONSTRAINT

32、 S_F FOREIGN KEY(Sno) REFERENCES Student(Sno);INSERT INTO Student VALUES('98001','錢橫',18,'男','CS');INSERT INTO Student VALUES('98002','王林',19,'女','CS');INSERT INTO Student VALUES('98003','李民',20,'男','IS')

33、;INSERT INTO Student VALUES('98004','趙三',16,'女','MA');INSERT INTO Course VALUES('1','數(shù)據(jù)庫系統(tǒng)', '5',4);INSERT INTO Course VALUES('2','數(shù)學(xué)分析',null ,2);INSERT INTO Course VALUES('3','信息系統(tǒng)導(dǎo)論','1',3);INSERT INTO

34、Course VALUES('4','操作系統(tǒng)原理','6',3);INSERT INTO Course VALUES('5','數(shù)據(jù)結(jié)構(gòu)','7',4);INSERT INTO Course VALUES('6','數(shù)據(jù)處理基礎(chǔ)',null,4);INSERT INTO Course VALUES('7','C語言','6',3);INSERT INTO SC VALUES('98001','1

35、',87);INSERT INTO SC VALUES('98001','2',67);INSERT INTO SC VALUES('98001','3',90); INSERT INTO SC VALUES('98002','2',95);INSERT INTO SC VALUES('98002','3',88);例4-1 SELECT DISTINCT Sno FROM SCWHERE Grade>=90;例4-2 SELECT Sname,Ssex

36、 FROM StudentWHERE Sage>18 AND Sdept NOT IN ('IS', 'MA');例4-3 SELECT * FROM Course WHERE Cname LIKE MIS#_%導(dǎo)_ ESCAPE #;例4-4 SELECT COUNT(DISTINCT Sno) /* 加DISTINCT 去掉重復(fù)值后計數(shù) */FROM SC;例4-5 SELECT Student.Sno FROM Student,SCWHERE Sdept='CS' AND Student.Sno=SC.SnoGROUP BY Stud

37、ent.Sno HAVING COUNT(*)>=2;例4-6 Select Student.*,SC.* From Student,SC;或 Select Student.*,SC.* From Student Cross Join SC;例4-7 Select * From Student,SC WHERE Student.Sno=SC.Sno;例4-8 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student, SC WHERE Student.Sno=SC.Sno;或 SELECT Student.S

38、no, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student INNER JOIN SC ON Student.Sno=SC.Sno;例4-9 SELECT FIRST.Cno, SECOND.cpno FROM Course FIRST, Course SECONDWHERE FIRST.cpno=SECOND.Cno;我們?yōu)镃ourse表取兩個別名FIRST與SECOND，這樣就可以在SELECT子句和WHERE子句中的屬性名前分別用這兩個別名加以區(qū)分。例4-10 SELECT Student.Sno, Sname, Ssex, Sage, Sd

39、ept, Cno, GradeFROM Student Left Outer JOIN SC ON Student.Sno=SC.Sno;例4-11 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Course.Cno, Grade, cname, cpno, ccreditFROM Student Left Outer JOIN SC ON Student.Sno=SC.Sno Full Outer join Course on SC.cno=Co;例4-12 SELECT Student.*,Cno,Grade FROM STUDENT INNE

40、R JOIN SC ON Student.Sno=SC.SnoWHERE SSEX=男 AND GRADE >=60例4-13 SELECT * FROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname='錢橫');或 SELECT * FROM StudentWHERE Sdept=(SELECT Sdept FROM StudentWHERE Sname='錢橫'); -當(dāng)子查詢?yōu)閱瘟袉涡兄禃r可以用“=”或 SELECT S1.* FROM Student S1,Student

41、S2WHERE S1.Sdept=S2.Sdept AND S2.Sname='錢橫'一般來說，連接查詢可以替換大多數(shù)的嵌套子查詢。SQL-92支持“多列成員”的屬于(IN)條件表達，例：例4-14 Select * from Student TWhere (T.sdept,T.sage,T.ssex) IN (Select sdept,sage,ssex From student SWhere S.sno<>T.sno); -Oracle支持的它等價于逐個成員IN的方式表達，如下：Select * from Student T Where T.sdept IN(

42、Select sdept From student SWhere S.sno<>T.sno and T.sage IN (Select sage From student X Where S.sno=X.sno and X.sno<>T.sno and T.ssex IN (Select ssex From student Y Where X.sno=Y.sno and Y.sno<>T.sno);例4-15 SELECT Sno,Sname,Sdept FROM Student - IN嵌套查詢方法WHERE Sno IN( SELECT Sno FROM

43、 SCWHERE Cno IN (SELECT Cno FROM Course WHERE Cname='數(shù)據(jù)庫系統(tǒng)');或 SELECT Sno,Sname,Sdept FROM Student - IN、= 嵌套查詢方法WHERE Sno IN( SELECT Sno FROM SCWHERE Cno=(SELECT Cno FROM Course WHERE Cname='數(shù)據(jù)庫系統(tǒng)');或 SELECT Student.Sno,Sname,Sdept -連接查詢方法FROM Student,SC,CourseWHERE Student.Sno=SC.Sn

44、o AND SC.Cno=Course.Cno AND Course.Cname='數(shù)據(jù)庫系統(tǒng)'或 Select Sno,Sname,Sdept From Student - Exists嵌套查詢方法Where Exists( Select * From SC Where SC.Sno=Student.Sno AndExists( Select * From CourseWhere SC.Cno = Course.Cno And Cname='數(shù)據(jù)庫系統(tǒng)');或 Select Sno,Sname,Sdept From Student - Exists嵌套查詢方法

45、Where Exists( Select * From course Where Cname='數(shù)據(jù)庫系統(tǒng)' and Exists( Select * From SC Where sc.sno=student.sno and SC.Cno = Course.Cno);例4-16 SELECT Sno,Sname FROM StudentWHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno IN ('2','4');例4-17 SELECT Sname FROM StudentWHERE Sage>Al

46、l(SELECT Sage FROM StudentWHERE Sdept='IS') AND Sdept <> 'IS'ORDER BY Sname;本查詢實際上也可以用集函數(shù)實現(xiàn)：SELECT Sname FROM Student WHERE Sage>(SELECT MAX(Sage) FROM StudentWHERE Sdept='IS') AND Sdept<>'IS'ORDER BY Sname;例4-18 SELECT DISTINCT CNAME FROM COURSE CWHERE

47、 '女'=ALL( SELECT SSEX FROM SC,STUDENTWHERE SC.SNO=STUDENT.SNO AND SC.CNO=C.CNO);或 SELECT DISTINCT CNAME FROM COURSE CWHERE NOT EXISTS( SELECT * FROM SC,STUDENTWHERE SC.SNO=STUDENT.SNO AND SC.CNO=C.CNO AND STUDENT.SSEX='男');例4-19 SELECT Sname FROM StudentWHERE NOT EXISTS( SELECT * FRO

48、M SC WHERE Sno=Student.Sno AND Cno='1');或 SELECT Sname FROM StudentWHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno='1');但如下是錯的：SELECT Sname FROM Student,SC WHERE SC.Sno=Student.Sno AND Cno<>'1'例4-20 SELECT Sname FROM StudentWHERE NOT EXISTS( SELECT * FROM Course WHERE NO

49、T EXISTS(SELECT * FROM SC WHERE Sno=SC.Sno AND Cno=Course.Cno); 由于沒有全稱量詞，我們將題目的意思轉(zhuǎn)換成等價的存在量詞的形式：查詢這樣的學(xué)生姓名沒有一門課程是他不選的。本題的另一操作方法是：SELECT Sname FROM Student,SC WHERE Student.Sno=SC.SnoGroup by Student.Sno,Sname having count(*)>=(SELECT count(*) FROM Course);例4-21 SELECT Sno FROM Student SXWHERE NOT E

50、XISTS( SELECT * FROM SC SCYWHERE SCY.Sno='98001' AND NOT EXISTS(SELECT * FROM SC SCZ WHERE SCZ.Sno=SX.Sno AND SCZ.Cno=SCY.Cno);例4-22 SELECT Sno FROM SC WHERE Cno='1'UNIONSELECT Sno FROM SC WHERE Cno='2'SELECT Sno FROM SC WHERE Cno='1'INTERSECTSELECT Sno FROM SC WHERE

51、Cno='2' - 查詢既選課程1又選課程2的學(xué)生學(xué)號集例4-23 SELECT * FROM Student WHERE Sdept='CS'INTERSECTSELECT * FROM Student WHERE Sage<=19; 本查詢等價于“查詢計算機科學(xué)系中年齡不大于19歲的學(xué)生?！?，為此變通法為：SELECT * FROM Student WHERE Sdept='CS' AND Sage<=19;例4-24 SELECT Sno FROM SC WHERE Cno='2'MINUSSELECT Sno

52、FROM SC WHERE Cno='1'本例實際上是查詢選修了課程2但沒有選修課程1的學(xué)生。為此變通法為：SELECT Sno FROM SCWHERE Cno='2' AND Sno NOT IN (SELECT Sno FROM SC WHERE Cno='1');例4-25 Select stu_no,sname,avgrFrom Student,( Select sno stu_no,avg(grade) avgr From SC Group By sno) SGWhere Student.sno=SG.stu_no And avgr&

53、gt;85;SQL-92允許在 From中使用查詢表達式，并必須為查詢表達式取名。它等價于如下未使用查詢表達式的形式：Select Student.Sno,Sname,AVG(Grade)From Student,SC Where Student.Sno = SC.SnoGroup By Student.Sno,Sname HAVING AVG(Grade)>85;例4-26 SELECT SNAME,CNAME,GRADEFROM (SELECT SNAME,CNAME,GRADE FROM STUDENT,SC,COURSEWHERE SSEX='女' AND STU

54、DENT.SNO=SC.SNO AND SC.CNO=COURSE.CNO) TEMP WHERE GRADE>90; -特意用查詢表達式實現(xiàn)，完全可用其它方式實現(xiàn)但如下使用查詢表達式的查詢，則不易改寫為其它形式。例4-27 Select avgr,COUNT(*)From (Select sno,avg(grade) avgr From SC Group By sno) SGGroup By avgr;例4-28 GRANT CREATE VIEW TO JXGL - 賦予用戶JXGL CREATE VIEW 的權(quán)力CREATE VIEW IS_StudentAS SELECT Sno

55、,Sname,Sage,SsexFROM Student WHERE Sdept='IS' WITH CHECK OPTIONGOSELECT * FROM IS_Student WHERE Sage>=18 AND Ssex='女'實驗5 SQL語言數(shù)據(jù)更新操作5.1 INSERT命令例5-1 Insert Into Student Values('98011','張靜',27,'女','CS'); Commit;Insert語句后可跟returning 子句來獲取插入記錄的某字段值。程序代

56、碼如下：Set serveroutput onDeclare bnd1 student.sno%TYPE; bnd2 student.sname%TYPE;Begin Insert Into Student(sno,sname,sage, ssex, sdept) Values('98011','張靜',27,'女','CS') RETURNING sno,Student.sname INTO bnd1,bnd2; dbms_output.put_line(bnd1|' '|bnd2);End;例5-2 Inser

57、t Into Student(Sno,Sname, Sage) Values('98012', '李四',16); Commit;例5-3 Create Sequence tt increment by 1 minvalue 101 maxvalue 9999999 cycle;Create table testable(id int,rq date);Insert into testable Values(tt.nextval,sysdate);若要刪除序列命令為：Drop Sequence tt; 若刪除測試表testable命令為：Drop table testable;例5-4 Insert Into SCSelect sno,cno,null From Student,Course Where Sdept='CS' and cno='5'Commit;例5-5 Insert Into StudentSelect c