【文章內(nèi)容簡介】 sts( Select * From SC Where = and = ))。例416 SELECT Sno,Sname FROM StudentWHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno IN (39。239。,39。439。))。例417 SELECT Sname FROM StudentWHERE SageAll(SELECT Sage FROM StudentWHERE Sdept=39。IS39。) AND Sdept 39。IS39。ORDER BY Sname。本查詢實際上也可以用集函數(shù)實現(xiàn)：SELECT Sname FROM Student WHERE Sage(SELECT MAX(Sage) FROM StudentWHERE Sdept=39。IS39。) AND Sdept39。IS39。ORDER BY Sname。例418 SELECT DISTINCT CNAME FROM COURSE CWHERE 39。女39。=ALL( SELECT SSEX FROM SC,STUDENTWHERE = AND =)?；? SELECT DISTINCT CNAME FROM COURSE CWHERE NOT EXISTS( SELECT * FROM SC,STUDENTWHERE = AND = AND =39。男39。)。例419 SELECT Sname FROM StudentWHERE NOT EXISTS( SELECT * FROM SC WHERE Sno= AND Cno=39。139。)?；? SELECT Sname FROM StudentWHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno=39。139。)。但如下是錯的：SELECT Sname FROM Student,SC WHERE = AND Cno39。139。例420 SELECT Sname FROM StudentWHERE NOT EXISTS( SELECT * FROM Course WHERE NOT EXISTS(SELECT * FROM SC WHERE Sno= AND Cno=))。 由于沒有全稱量詞，我們將題目的意思轉換成等價的存在量詞的形式：查詢這樣的學生姓名沒有一門課程是他不選的。本題的另一操作方法是：SELECT Sname FROM Student,SC WHERE =Group by ,Sname having count(*)=(SELECT count(*) FROM Course)。例421 SELECT Sno FROM Student SXWHERE NOT EXISTS( SELECT * FROM SC SCYWHERE =39。9800139。 AND NOT EXISTS(SELECT * FROM SC SCZ WHERE = AND =))。例422 SELECT Sno FROM SC WHERE Cno=39。139。UNIONSELECT Sno FROM SC WHERE Cno=39。239。SELECT Sno FROM SC WHERE Cno=39。139。INTERSECTSELECT Sno FROM SC WHERE Cno=39。239。 查詢既選課程1又選課程2的學生學號集例423 SELECT * FROM Student WHERE Sdept=39。CS39。INTERSECTSELECT * FROM Student WHERE Sage=19。 本查詢等價于“查詢計算機科學系中年齡不大于19歲的學生?！?，為此變通法為：SELECT * FROM Student WHERE Sdept=39。CS39。 AND Sage=19。例424 SELECT Sno FROM SC WHERE Cno=39。239。MINUSSELECT Sno FROM SC WHERE Cno=39。139。本例實際上是查詢選修了課程2但沒有選修課程1的學生。為此變通法為：SELECT Sno FROM SCWHERE Cno=39。239。 AND Sno NOT IN (SELECT Sno FROM SC WHERE Cno=39。139。)。例425 Select stu_no,sname,avgrFrom Student,( Select sno stu_no,avg(grade) avgr From SC Group By sno) SGWhere = And avgr85。SQL92允許在 From中使用查詢表達式，并必須為查詢表達式取名。它等價于如下未使用查詢表達式的形式：Select ,Sname,AVG(Grade)From Student,SC Where = Group By ,Sname HAVING AVG(Grade)85。例426 SELECT SNAME,CNAME,GRADEFROM (SELECT SNAME,CNAME,GRADE FROM STUDENT,SC,COURSEWHERE SSEX=39。女39。 AND = AND =) TEMP WHERE GRADE90。 特意用查詢表達式實現(xiàn)，完全可用其它方式實現(xiàn)但如下使用查詢表達式的查詢，則不易改寫為其它形式。例427 Select avgr,COUNT(*)From (Select sno,avg(grade) avgr From SC Group By sno) SGGroup By avgr。例428 GRANT CREATE VIEW TO JXGL 賦予用戶JXGL CREATE VIEW 的權力CREATE VIEW IS_StudentAS SELECT Sno,Sname,Sage,SsexFROM Student WHERE Sdept=39。IS39。 WITH CHECK OPTIONGOSELECT * FROM IS_Student WHERE Sage=18 AND Ssex=39。女39。實驗5 SQL語言——數(shù)據(jù)更新操作 INSERT命令例51 Insert Into Student Values(39。9801139。,39。張靜39。,27,39。女39。,39。CS39。)。 Commit。Insert語句后可跟returning 子句來獲取插入記錄的某字段值。程序代碼如下：Set serveroutput onDeclare bnd1 %TYPE。 bnd2 %TYPE。Begin Insert Into Student(sno,sname,sage, ssex, sdept) Values(39。9801139。,39。張靜39。,27,39。女39。,39。CS39。) RETURNING sno, INTO bnd1,bnd2。 (bnd1||39。 39。||bnd2)。End。例52 Insert Into Student(Sno,Sname, Sage) Values(39。9801239。, 39。李四39。,16)。 Commit。例53 Create Sequence tt increment by 1 minvalue 101 maxvalue 9999999 cycle。Create table testable(id int,rq date)。Insert into testable Values(,sysdate)。若要刪除序列命令為：Drop Sequence tt。 若刪除測試表testable命令為：Drop table testable。例54 Insert Into SCSelect sno,o,null From Student,Course Where Sdept=39。CS39。 and o=39。539。Commit。例55 Insert Into StudentSelect cast(cast(sno as integer)+1 as char(5)),CONCAT(sname,39。239。),sage,ssex,sdept From Student Where Sname=39。趙三39。Commit。SELECT * FROM Student。 查看結果（執(zhí)行結果略） UPDATE命令例56 UPDATE Student SET Sage=23 WHERE Sno=39。9800339。例57 UPDATE Student SET = +1where sno in (SELECT sno FROM (SELECT * FROM Student ORDER BY sno)　　 WHERE ROWNUM =3)。 例58 Update SCSet Grade=(Select AVG(Grade) From SC Where Cno=39。339。) Where Sno =39。9800139。 and Cno=39。339。Update語句后也可跟上Returning子句來獲取更新后記錄的字段值。程序代碼如下：Set serveroutput onDeclare bnd1 %TYPE。bnd2 %TYPE。Begin Update SC Set Grade=(Select AVG(Grade) From SC Where Cno=39。339。) Where Sno =39。9800139。 and Cno=39。339。 RETURNING , INTO bnd1, bnd2。 (bnd1||39。 39。||to_char(bnd2))。End。例59 UPDATE SC SET GRADE＝0WHERE CNO＝39。239。 AND39。王林39。＝(SELECT SNAME FROM STUDENT WHERE =)。例510 Update studentSet sage=(select avg(sage) from student where sdept=39。CS39。),(ssex,sdept)=(select ssex,sdept from student where sno=39。9800339。)Where sno=39。9800239。 DELETE命令先備份選修表SC到TSC中，命令為：Create Table TSC as Select * From SC 備份到表TSC中例511 SELECT * FROM SC 刪除前DELETE FROM SC 刪除中WHERE 39。CS39。=(SELECT Sdept FROM StudentWHERE =)SELECT * FROM SC 刪除后DELETE語句后也可跟上Returning子句來獲取剛刪除記錄相應的字段值。程序代碼如下：Set serveroutput onDeclare bnd1 %TYPE。 bnd2 %TYPE。Begin DELETE FROM SC 刪除中 WHERE SNO=39。9800139。 AND o=39。239。 RETURNING , INTO bnd1, bnd2。 (bnd1||39。 39。||to_char(bnd2))。End。例512 DELETE FROM @remote WHERE SNO=39。9800139。 例513 DELETE FROM SC。如TRUNCATE TABLE SC,清空SC表。從表TSC恢復數(shù)據(jù)到表SC，命令為：INSERT INTO SC SELECT * FROM TSC 這是一種方便、簡易地恢復數(shù)據(jù)的方法。實驗6 嵌入式SQL應用Pro*C程序舉例例如：“”程序能完成輸入雇員號、雇員名、職務名和薪金等信息，并插入到雇員表emp（Oracle缺省安裝后SCOTT用戶連接能存取到該表）中的功能。define USERNAME SCOTT //連接Oracle的用戶名define PASSWORD scott //連接Oracle的用戶口令define SERVER localhost:1521/orcl //連接Oracle的用戶口令include include include include include EXEC SQL INCLUDE sqlca。EXEC SQL BEGIN DECLARE SECTION。 char* username=USERNAME。 char* password=PASSWORD。 char* server=SERVER。 varchar sqlstmt[80]。 int empnum。 varchar emp_name[15]。 varchar job[50]。 float salary。EXEC SQL END DECLARE SECTION。void sqlerror()。main(){ EXEC SQL WHENEVER SQLERROR DO sqlerror()。//錯誤處理 EXEC SQL CONNECT :username IDENTIFIED BY :password USING :server。 //連接oracle =sprintf(,INSERT INTO EMP(EMPNO,ENA