關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL - POWERPOINT 演示文稿

第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL

SQL〔StructuredQueryLanguage〕是1974年由Boyce和Chamberlin提出的。在IBM公司SanJoseResearchLaboratory研制的SystemR上實(shí)現(xiàn)了這種語言。由于它功能豐富、使用方式靈活、語言簡潔易學(xué)等突出優(yōu)點(diǎn)，在計(jì)算機(jī)工業(yè)界和計(jì)算機(jī)用戶中倍受歡送并深深扎根。1986年10月，美國國家標(biāo)準(zhǔn)局〔ANSI〕的數(shù)據(jù)庫委員會(huì)X3H2批準(zhǔn)了SQL作為關(guān)系數(shù)據(jù)庫語言的美國標(biāo)準(zhǔn)。同年公布了標(biāo)準(zhǔn)SQL文本。此后不久，國際標(biāo)準(zhǔn)化組織(ISO)也作出了同樣的決定。本章介紹SQL語言,并進(jìn)一步討論關(guān)系數(shù)據(jù)庫的根本概念.SQL標(biāo)準(zhǔn)文本和實(shí)施的SQL語言相比,后者常常作了許多必要的擴(kuò)充,因此我們選用IBM公司在DB2上的SQL語言.它根本上和標(biāo)準(zhǔn)SQL一致,而在對(duì)關(guān)系模型的符合程度上比標(biāo)準(zhǔn)SQL更好.

3.1SQL概述SQL(StructuredQueryLanguage)的英文名稱是結(jié)構(gòu)查詢語言。實(shí)際上它的功能包括查詢(Query),操縱(Manipulation),定義(Definition)和控制(Control)四個(gè)方面,是一個(gè)綜合的、通用的、功能極強(qiáng)的關(guān)系數(shù)據(jù)庫語言。

3.1.1SQL的主要特點(diǎn)1.綜合統(tǒng)一2.高度非過程化3.面向集合的操作方式4.兩種使用方式,統(tǒng)一的語法結(jié)構(gòu)5.語言簡潔,易學(xué)易用(1).數(shù)據(jù)庫查詢SELECT(2).數(shù)據(jù)定義CREATE,DROP(3).數(shù)據(jù)操縱INSERT,UPDATE,DELETE(5).數(shù)據(jù)控制GRANT,REVOKE3.1.2SQL語言的根本概念圖3.1SQL對(duì)關(guān)系數(shù)據(jù)庫模式的支持SQLViewV1ViewV2BasetableB1BasetableB2BasetableB3BasetableB4StoredfileS1StoredfileS2外模式模式內(nèi)模式3.2SQL數(shù)據(jù)定義SQL的數(shù)據(jù)定義功能包括三局部:定義根本表,定義視圖和定義索引,他們是:CREATETABLECREATEVIEWCREATEINDEXDROPTABLEDROPVIEWDROPINDEXALTERTABLE定義、刪除和修改根本表一.定義根本表的語句1.格式:CREATETABLE<表名>(<列名1><類型>[列級(jí)完整性約束條件][,<列名2><類型>[列級(jí)完整性約束條件]]…[<表級(jí)完整性約束條件>];)

2.舉例:例1:P88CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,SnameCHAR(20)UNIQUE,SsexCHAR(1),SageINTSdeptCHAR(15));二.修改根本表1.格式:ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]];[DROP<完整性約束名>][MODIFY<列名><數(shù)據(jù)類型>];2.舉例例2:ALTERTABLEStudentADDScomedate;三.刪除根本表1.格式:DROPTABLE<表名>;2.舉例例5:刪除Student表ALTERTABLEStudent;例3:ALTERTABLEStudentMODIFYSageSMALLINT;例4:ALTERTABLEStudentDROPUNIQUE(Sname);

5.2.2建立與刪除索引通常索引的建立和刪除由DBA(DadaBaseAdministrator)或表的主人負(fù)責(zé).

一.建立索引

1.格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序.]]…);

2.舉例:例6:CREATEUNIQUEINDEXStusnoONStudent(Sno);CREATEUNIQUEINDEXCoucnoONCouse(Cno);CREATEUNIQUEINDEXSCnoONSC(SnoASC,CnoDESC);

二.刪除索引

1.格式DROPINDEX<索引名>;

2.舉例例7:刪除Student表的Stusname索引.DROPINDEXStusname小結(jié)在關(guān)系數(shù)據(jù)庫中允許DBA隨時(shí)使用SQL數(shù)據(jù)定義語句來定義根本表、索引和視圖，系統(tǒng)十分方便靈活。在非關(guān)系系統(tǒng)中，數(shù)據(jù)庫的模式一經(jīng)建立再要參加新的記錄型或修改老的記錄型是十分費(fèi)事的工作。而象DB2這樣的SQL關(guān)系數(shù)據(jù)庫系統(tǒng)，增刪改根本表、索引和視圖都不必停止系統(tǒng)的其它處理，不會(huì)影響其它用戶對(duì)數(shù)據(jù)庫的正常使用。

3.3查詢

數(shù)據(jù)庫查詢是數(shù)據(jù)庫的核心操作。SQL語句提供了SELECT語句進(jìn)行數(shù)據(jù)庫的查詢。

查詢語句的一般格式

SELECT

[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[,<目標(biāo)列表達(dá)式>]…FROM<表名或視圖名>[,<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]];單表查詢一、選擇表中的假設(shè)干列1.查詢指定列例1.查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名。SELECTSno,SnameFROMStudent;例2.查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系。SELECTSname,Sno,SdeptFROMStudent；

2.查詢?nèi)苛欣?.查詢?nèi)w學(xué)生的詳細(xì)信息。SELECT*FROMStudent;

等價(jià)于:SELECTSno,Sname,Ssex,Sage,SdeptFROMStudent;

3.查詢經(jīng)過計(jì)算的值。例4.查詢?nèi)w學(xué)生學(xué)號(hào)和出生年份.SELECTSno,2003-SageFROMStudent;二、選擇表中的假設(shè)干元組1.消除取值重復(fù)的行例6.查詢選修了課程的學(xué)生的學(xué)號(hào).SELECTSnoFROMSC;指定DISTINCT短語,可去掉結(jié)果表中的重復(fù)行.SELECTDISTINCTSnoFROMSC;

2.查詢滿足條件的元組

查詢滿足指定條件的元組可以通過WHERE子句實(shí)現(xiàn).(1)比較大小例7.查詢計(jì)算機(jī)系全體學(xué)生的名單.SELECTSnameFROMStudentWHERESdept=‘CS’;例8.查詢所有年齡在20歲以下的學(xué)生的姓名及年齡.SELECTSname,SageFROMStudentWHERESage<20;SELECTSname,Sage或FROMStudentWHERENOTSage>=20;

(2)確定范圍例10.查詢年齡在20~23歲之間(包括20歲和23歲)的學(xué)生的姓名、系別和年齡。SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23;例11.查詢年齡不在20~23歲之間的學(xué)生的姓名、系別和年齡。SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23;

(3)確定集合謂詞IN可以用來查找屬性值屬于指定集合的元組.

例12.查詢信息系(IS)、數(shù)學(xué)系(MA)、計(jì)算機(jī)科學(xué)系(CS)學(xué)生的姓名和性別.SELECTSname,SsexFROMStudent

WHERESdeptIN(‘IS’,‘MA’,’CS’);謂詞IN實(shí)際上是一系列謂詞‘OR’的縮寫.等價(jià)的WHERE子句為:

WHERESdept=‘IS’ORSdept=‘MA’ORSdept=‘CS’;例13.查詢既不是信息系(IS)、數(shù)學(xué)系(MA),也不是計(jì)算機(jī)科學(xué)系(CS)的學(xué)生的姓名和性別.SELECTSname,SsexFROMStudentWHERESdeptNOTIN(‘IS’,‘MA’,’CS’);(4)字符匹配LIKE謂詞的一般形式為:<列名>[NOT]LIKE‘<匹配串>’[ESCAPE’<換碼字符>‘]字符串中通配符的含義:⑴.字符“_〞,表示任意的單個(gè)字符;⑵.字符“%〞,表示任意字符串(長度可以為零);例14.查詢學(xué)號(hào)為95001的學(xué)生的詳細(xì)情況。SELECT*FROMStudentWHERESnoLIKE‘95001’;等價(jià)的WHERE子句為:WHERESno=‘95001’;例15.查詢所有姓劉的學(xué)生的姓名、學(xué)號(hào)和性別。SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE‘劉%’;例16.查詢姓“歐陽〞且全名為三個(gè)漢字的學(xué)生的姓名。SELECTSnameFROMStudentWHERESnameLIKE‘歐陽__’;例19.查詢DB_Design課程的課程號(hào)和學(xué)分。SELECTCno,CcreditFROMCourseWHERECnameLIKE‘DB\_Design’ESCAPE’\’;

(5).涉及空值的查詢.

例21查詢?nèi)鄙賹W(xué)習(xí)成績的學(xué)生的學(xué)號(hào)和課程號(hào)。SELECTSno,CnoFROMSCWHEREGradeISNULL;

例22查詢所有有成績的學(xué)生的學(xué)號(hào)和課程號(hào)。SELECTSno,CnoFROMSCWHEREGradeISNOTNULL;

(6).多重條件查詢用邏輯運(yùn)算符AND和OR來聯(lián)結(jié)多個(gè)查詢條件.例23查詢計(jì)算機(jī)科學(xué)系年齡在20歲以下的學(xué)生的姓名及年齡.SELECTSname,SageFROMStudentWHERESdept=‘CS’ANDSage<20;

三、對(duì)查詢結(jié)果排序可以用ORDERBY字句對(duì)查詢結(jié)果按照一個(gè)或多個(gè)屬性列的升序(ASC)或降序(DESC)排列,缺省值為升序.例24查詢選修了3號(hào)課程的學(xué)生的學(xué)號(hào)和成績,查詢結(jié)果按分?jǐn)?shù)的降序排序。SELECTSno,GradeFROMSCWHERECno=‘3’ORDERBYSdept,SageDESC;例25查詢?nèi)w學(xué)生的詳細(xì)信息,查詢結(jié)果按所在系的系號(hào)升序排列,同一系中的學(xué)生按年齡降序排序。SELECT*FROMStudent;ORDERBYSdept,SageDESC;

四、使用集函數(shù)(庫函數(shù))SQL提供了6種庫函數(shù).其功能如下:COUNT([DISTINCT|ALL]*)統(tǒng)計(jì)元組個(gè)數(shù)COUNT([DISTINCT|ALL]<列名>)統(tǒng)計(jì)一列中值的個(gè)數(shù)SUM([DISTINCT|ALL]<列名>)計(jì)算某一列值的總和AVG([DISTINCT|ALL]<列名>)計(jì)算某一列值的平均值MAX([DISTINCT|ALL]<列名>)求某一列值中的最大值MIN([DISTINCT|ALL]<列名>)求某一列值中的最小值例26.查詢學(xué)生總?cè)藬?shù).SELECTCOUNT(*)FROMStudent;例27查詢選修了課程的學(xué)生人數(shù).SELECTCOUNT(DISTINCTSno)FROMSC;例28計(jì)算1號(hào)課程的學(xué)生的平均成績.SELECTAVG(Grade)FROMSCWHERECno=‘1’;

五、對(duì)查詢結(jié)果分組

GROUPBY子句將查詢結(jié)果表按某列或多列值分組，值相等的為一組。例30求各課程號(hào)及選修該課程的學(xué)生人數(shù).SELECTCno,COUNT(Sno)FROMSC

GROUPBYCno;可以使用HAVING短語指定篩選條件。例31查詢選修了3門以上課程的學(xué)生學(xué)號(hào).SELECTSnoFROMSCGROUPBYSno

HAVINGCOUNT(*)>3;連接查詢定義:假設(shè)查詢涉及兩個(gè)以上的表,那么稱之為連接查詢.

一、等值與非等值連接查詢

例32.查詢每個(gè)學(xué)生以及其選修課程的情況。SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=SC.Sno;例33.用自然連接完成例32的查詢.SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno;

二、自身連接例34查詢每一門課程的間接先行課(即先行課的先行課).此例中的FIRST和SECOND為引入的別名;SELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=SECOND.Cno;

三、外連接如果我們想以Student表為主體列出每個(gè)學(xué)生的根本情況及其選課情況，假設(shè)某個(gè)學(xué)生沒有選課，只輸出其根本情況信息，其選課信息為空值即可，這時(shí)就需要外連接。SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno(*);

四、復(fù)合條件連接例35查詢選修2號(hào)課程且成績?cè)?0分以上的學(xué)生.SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cn=‘2’ANDSC.Grade>90;例如：SELECTSnameFROMStudentWHERESnoINSELECTSnoFROMSCWHERECno=‘2’；

3.3.3嵌套查詢嵌套查詢亦稱為子查詢.嵌套查詢是指一個(gè)SELECT-FROM-WHERE查詢塊嵌入另一個(gè)查詢塊之中.SQL中允許多層嵌套.

一、帶有IN謂詞的查詢例37查詢與“劉晨〞在同一個(gè)系學(xué)習(xí)的學(xué)生。①確定“劉晨〞所在的系名。SELECTSdeptFROMStudentWHERESname=‘劉晨’；②查詢所有在IS系學(xué)習(xí)的學(xué)生.SELECTSno,Sname,SdeptFROMStudentWHERESdept=‘IS’；

將第1步查詢嵌入到第2步查詢的條件中,構(gòu)造嵌套查詢,SQL語句如下:SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=‘劉晨’)；SELECTSno,SnameFROMStudentWHERESnoIN

(SELECTSnoFROMSCWHERECnoIN

(SELECTCnoFROMCourseWHERECname=‘信息系統(tǒng)’));例38.查詢選修了課程名為‘信息系統(tǒng)’的學(xué)生的學(xué)號(hào)和姓名.

上述查詢可用連接查詢實(shí)現(xiàn):SELECTSno,SnameFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=‘信息系統(tǒng)’二.帶有比較運(yùn)算符的子查詢當(dāng)確切知道內(nèi)層查詢返回的是單值時(shí),可以用比較運(yùn)算符.用比較運(yùn)算符‘=’代替IN改寫例37SELECTSno,Sname,SdeptFROMStudentWHERESdept=(SELECTSdeptFROMStudentWHERESname=‘劉晨’)；三.帶有ANY或ALL謂詞的子查詢子查詢返回的是單值時(shí),可以用比較運(yùn)算符,而使用ANY或ALL謂詞時(shí)那么必須同時(shí)使用比較運(yùn)算符。例39查詢其他系中比信息系某一學(xué)生年齡小的學(xué)生的姓名和年齡。SELECTSname,SageFROMStudentWHERESage<ANY(SELECTSageFROMStudentWHERESdept=‘IS’)ANDSdept<>‘IS’；

四.代有EXISTS的嵌套查詢.例41查詢所有選修了1號(hào)課程的學(xué)生姓名.SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=‘1’);例42查詢沒有選修1號(hào)課程的學(xué)生姓名.SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=‘1’);例43.查詢選修了全部課程的學(xué)生姓名.

SELECTSnameFROMStudentWHERENOTEXISTS

(SELECT*FROMCourseWHERENOTEXISTS

(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno));例45查詢計(jì)算機(jī)科學(xué)系的學(xué)生以及年不大于19歲的學(xué)生.SELECT*FROMStudentWHERESdept=‘CS’UNIONSELECT*FROMStudentWHERESage<=19;

3.3.4集合查詢例46查詢選修了1號(hào)課程或選修了2號(hào)課程的學(xué)生.SELECTSnoFROMSCWHERECno=‘1’UNIONSELECTSnoFROMSCWHERECno=‘2’;例48查詢選修了1號(hào)課程的學(xué)生集合與選修了2號(hào)課程的學(xué)生集合的交集..SELECT*FROMStudentWHERECno=‘1’ANDSnoIN(SELECTSnoFROMSCWHERECno=‘2’);

3.4.1插入數(shù)據(jù)

一、插入單個(gè)元組語句的一般格式:INSERTINTO表名[(<屬性列1>[,<屬性列2>]…)]VALUES(<常量1>[,<常量2>]…);例1將一個(gè)新學(xué)生:95020(學(xué)號(hào)),陳東(姓名),男(性別),IS(信息系),18(年齡),插入到Student表中.INSERTINTOStudentVALUES(‘95020’,’陳東’,‘男’,‘IS’,18);

3.4數(shù)據(jù)更新SQL更新語句包括修改、刪除和插入三類語句。

例2.插入一個(gè)選課記錄(‘95020’,’1’)INSERTINTOSC(Sno,Cno)VALUES(‘95020’,’1’);

二.插入子查詢結(jié)果語句的一般格式:INSERTINTO表名[(<屬性列1>[,<屬性列2>]…)]子查詢;CREATETABELDeptage(SdeptCHAR(15),AvgageSMALLINT);INSERTINTODeptage(Sdept,Avgage)SELECTSdept,AVG(SA)FROMStudentGROUPBYSdept;例3.對(duì)每個(gè)系,求學(xué)生的平均年齡,并把結(jié)果插入數(shù)據(jù)庫中.一、修改某一個(gè)元組的值例4.把學(xué)生95001的姓名改為‘王平’.UPDATEStudentSETSname=‘王平’WHERESno=‘95001’;

3.4.2修改數(shù)據(jù)語句的一般格式:UPDATE<表名>SET<列名>=<表達(dá)式>[,<列名>=<表達(dá)式>]…[WHERE<條件>];

二、修改多個(gè)元組的值例5.將所有學(xué)生的年齡增加1歲.

UPDATEStudentSETSage=Sage+1;

三、帶子查詢的修改語句例6.將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績置零.

UPDATESCSETGrade=0WHERE‘CS’=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno);

3.4.3刪除數(shù)據(jù)刪除語句的一般格式:DELETEFROM<表名>[WHERE<條件>];

一.刪除某一個(gè)元組的值例7.刪除學(xué)號(hào)為95019的學(xué)生記錄.DELETEFROMStudentWHERESno=‘95019’;執(zhí)行刪除操作也可能產(chǎn)生破壞完整性的情況.

二、刪除多個(gè)元組的值例8刪除所有的學(xué)生選課記錄.DELETEFROMSC;

三、帶有子查詢的刪除語句例9刪除計(jì)算機(jī)科學(xué)系全體學(xué)生的選課記錄.DELETEFROMSCWHERE‘CS’=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno);

四、更新操作與數(shù)據(jù)庫的一致性增刪改操作一次只能對(duì)一個(gè)表操作，例如，刪除學(xué)號(hào)為95019的學(xué)生記錄后，相應(yīng)該生的選課記錄也應(yīng)同時(shí)刪除，而這只能通過兩條刪除語句進(jìn)行。DELETEFROMStudentWHERESno=‘95019’;

DELETEFROMSCWHERESno=‘95019‘；3.5視圖視圖(View)是從一個(gè)或幾個(gè)根本表(或視圖)導(dǎo)出的表.

3.5.1視圖的定義

一、建立視圖

SQL建立視圖的語句格式:CREATEVIEW<視圖名>[(<列名>[,<列名>]…)]AS子查詢[WITHCHECKOPTION];例1.建立信息系的學(xué)生視圖.CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=‘IS’;例2.建立信息系的學(xué)生視圖,并要求進(jìn)行修改和插入操作時(shí)仍需保證該視圖只有信息系的學(xué)生.CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept=‘IS’WITHCHECKOPTION;例3.建立信息系選修了1號(hào)課程的學(xué)生視圖.CREATEVIEWIS_S1(Sno,Sname,Grade)ASSELECTStudent.Sno,Sname,GradeFROMStudent,SCWHERESdept=‘IS’ANDStudent.Sno=SC.SnoANDSC.Cno=‘1’;例4建立信息系選修了1號(hào)課程且成績?cè)?0分及以上的學(xué)生視圖.CREATEVIEWIS_S2ASSELECTSno,Sname,GradeFROMIS_S1WHEREGrade>=90;例5.定義一個(gè)反映學(xué)生出生年份的視圖.CREATEVIEWBT_S(Sno,Sname,Sbirth)ASSELECTSno,Sname,2003-SageFROMStudent;例6.把學(xué)生的學(xué)號(hào)及它的平均成績定義為一個(gè)視圖.CREATEVIEWS_G(Sno,Gavg)ASSELECTSno,AVG(Grade)FROMSCGROUPBYSno;

二、.刪除視圖

語句格式為：DROPVIEW<視圖名>;例8刪除視圖IS_S1DROPVIEWIS_S1;3.5.2查詢視圖視圖定義后,用戶可以如同根本表那樣對(duì)視圖查詢.例1在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生.SELECTSno,SageFROMIS_StudentWHERESage<20;SELECTSno,SageFROMStudentWHERESdept=‘IS’ANDSage<20;系統(tǒng)執(zhí)行此查詢時(shí)首先把它轉(zhuǎn)換成等價(jià)的對(duì)根本表的查詢.然后執(zhí)行修改了的查詢.本例的修正后的查詢語句為:例2查詢信息系選修了1號(hào)課程的學(xué)生.SELECTSno,SnameFROMIS_Student,SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno=‘1’;例3在S_G視圖中查詢平均成績?cè)?0分以上的學(xué)生的學(xué)號(hào)和平均成績.相應(yīng)的語句為:SELECT*FROMS_GWHEREGave>=90;S_G視圖定義為:SELECTSno,AVG(Grade)FROMSCGROUPBYSno;將上面查詢語句與子查詢結(jié)合后,形成以下查詢語句:SELECTSno,AVG(Grade)FROMSCWHEREAVG(Grade)>=90GROUPBYSno;

正確的查詢語句為:SELECTSno,AVG(Grade)FROMSCGROUPBYSno

HAVINGAVG(Grade)>=90;3.5.3視圖的更新對(duì)視圖的更新最終要轉(zhuǎn)換成對(duì)根本表的更新.例1將信息系學(xué)生視圖IS_Student中學(xué)號(hào)為95002的學(xué)生姓名改為“劉辰〞。UPDATEIS_StudentSETSname=‘劉辰’WHERESno=‘95002’;將轉(zhuǎn)換成對(duì)根本表Student的更新:UPDATEStudentSETSname=‘劉辰’WHERESno=‘95002’ANDSdept=‘IS’;

例2向信息系學(xué)生視圖IS_Student中插入一個(gè)新的學(xué)生記錄，其中學(xué)號(hào)為95029，姓名為趙新，年齡為20歲。INSERTINTOIS_StudentVALUES(‘95029’,‘趙新’,20);轉(zhuǎn)換成對(duì)根本表的插入INSERTINTOStudent(Sno,Sname,Sage,Sdept)VALUES(‘95029’,‘趙新’,20,‘IS’);

例3刪除信息系學(xué)生視圖IS_Student中學(xué)號(hào)為95029的記錄。DELETEFROMIS_StudentWHERESno=‘95029’;

視圖分類:(1).行列子集視圖(可更新的視圖).(2).理論上可更新的視圖.(3).不可更新的視圖在BD2中,只有從單個(gè)根本表導(dǎo)出的視圖才允許對(duì)它進(jìn)行更新操作,并且具有以下限制:P136-137共7條.不可更新的視圖與不允許更新的視圖的概念在關(guān)系數(shù)據(jù)庫中,并非所有的視圖都是可更新的.如:UPDATES_GSETGavge=90WHERESno=‘95001’;

3.5.4視圖的作用1.視圖能夠簡化用戶的操作.關(guān)系數(shù)據(jù)庫中，數(shù)據(jù)庫的重構(gòu)造是不可防止的。最常見的情況是把一個(gè)表‘垂直’地分為兩個(gè)以上的表。例如把學(xué)生關(guān)系Student(Sno,Sname,Ssex,Sage,Sdept)分為：SX(Sno,Sname,Sage)SY(Sno,Ssex,Sdept)這時(shí)原表Student是SX,SY的自然連接的結(jié)果,因此可以建立一個(gè)視圖Student:CREATEVIEWStudent(Sno,Sname,Ssex,Sage,Sdept)ASSELECTSX.Sno,SX.Sname,SY.Ssex,SX.Sage,SY.SdeptFROMSX,SYWHERESX.Sno=SY.Sno;4.視圖能對(duì)機(jī)密數(shù)據(jù)提供自動(dòng)的平安保護(hù)2.視圖使用戶能以多種角度看待同一數(shù)據(jù).3.視圖對(duì)重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性.

3.6數(shù)據(jù)控制授權(quán)語句的一般格式為:

GRANT<權(quán)限>[,<權(quán)限>]…[ON<對(duì)象類型><對(duì)象名>]TO<用戶>[,<用戶>]…[WITHGRANTOPTION];對(duì)不同類型的操作對(duì)象可有不同的操作權(quán)力,見表3.4任選項(xiàng)WITHGRANTOPTION的作用是使獲得某種權(quán)力的用戶可以把權(quán)力再授予別的用戶.

3.6.1授權(quán)例1.把查詢Student表的權(quán)XJ6授予用戶U1.GRANTSELECTONTABLEStudentTOU1;例4.把修改學(xué)生學(xué)號(hào)和查詢Student表的權(quán)限授給用戶U4.

GRANTUPDATE(Sno),SELECT

ONTABLEStudentTOU4;例2.把對(duì)表Student和Course的全部操作權(quán)限授予U2和U3。

GRANTALLPRIVILIGES

ONTABLEStudent,CourseTOU2,U3;例3.把對(duì)表SC的查詢權(quán)限授予所有用戶.GRANTSELECT

ONTABLESCTOPUBLIC;例6.DBA把在數(shù)據(jù)庫S_C中建立表的權(quán)限授予用戶U8.GRANTCREATETABONDATABASES_CTOU8;例5把對(duì)表SC的INSERT權(quán)限授予U5用戶,并允許將此權(quán)限再授予其他用戶.

GRANTINSERT

ONTABLESCTOU5WITHGRANTOPTION;

3.6.2回收權(quán)限

授予的權(quán)力可以用REVOKE語句收回，格式為：

REVOKE<權(quán)限>[，<權(quán)限>]…[ON<對(duì)象類型><對(duì)象名>]FROM<用戶>[,<用戶>]…;例7.把用戶U4修改學(xué)生學(xué)號(hào)的權(quán)力收回.REVOKEUPDATE(Sno)ONTABLEStudenyFROMU4;例8.收回所有用戶對(duì)表SC的查詢權(quán)限.REVOKESELECTONTABLEStudentFROMPUBLIC;例9.把用戶U5對(duì)表SC的INSERT權(quán)限收回.REVOKEINSERTONTABLESCFROMU5;

3.7嵌入式SQL3.7.1嵌入式SQL的一般形式在嵌入式SQL中,為了能夠區(qū)分SQL語句與主語言語句,所有SQL語句都必須加前綴EXECSQL。SQL語句的結(jié)束標(biāo)志那么隨主語言的不同而不同.例如,在PL/1和C中以分號(hào)“;〞結(jié)束:EXECSQL<SQL語句>;在COBOL中以END-EXEC結(jié)束:EXECSQL<SQL語句>END-EXEC例如一條交互式的SQL語句:DROPTABLEStudent;嵌入到C程序中,應(yīng)寫作:EXECSQLDROPTABLEStudent;課間休息

注意時(shí)間3.7.2嵌入式SQL語句與主語言之間的通信

一、SQL通信區(qū)

SQL通信區(qū)SQLCA

二、主變量1.輸入主變量2.輸出主變量3.指示變量所有主變量和指示變量必須在SQL語句BEGINDECLARESECYION與ENDDECLARESECYION之間進(jìn)行說明.

三、游標(biāo)

四、程序?qū)嵗璄XECSQLINCLUDESQLCA;EXECSQLBEGINDECLARESECTION;CHARSno(5);CHARCno(3);INTGrade;EXECSQLENDDECLARESECTION;main(){EXECSQLDECLAREC1CURSORFORSELECTSno,Cno,GradeFROMSC;EXECSQLOPENC1;FOR(;;){EXECSQLFETCHC1INTO:Sno,:Cno,:Grade;if(sqlca.sqlcode<>SUCCESS)break;printf(“Sno:%s,Cno:%s,Grade:%d〞,:Sno,:Cno,:Grade);}EXECAQLCLOSEC1;}

3.7.3不用游標(biāo)的SQL語句

一.說明性語句

EXECSQLBEGINDECLARESECYIONEXECSQLENDDECLARESECYION二.數(shù)據(jù)定義語句例1建立一個(gè)“學(xué)生〞表Student.EXEXSQLCREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,SnameCHAR(20),SsexCHAR(1),SageINT,SdeptCHAR(15));

三.數(shù)據(jù)控制語句例2把查詢Student表的權(quán)限授予用戶U1.

EXEXSQLGRANTSELECTONTABLEStudentTOU1;

四.查詢結(jié)果為單記錄的SELECT語句一般格式為:

EXECSQL

SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[,<目標(biāo)列表達(dá)式>]…

INTO<主變量>[<指示變量>][,<主變量>[<指示變量>]]…FROM<表名或視圖名>[,<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]];使用該語句需要注意以下四點(diǎn):例3.根據(jù)學(xué)生號(hào)碼查詢學(xué)生信息,假設(shè)已將要查詢的學(xué)生的學(xué)號(hào)賦給了主變量Givensno.

EXECSQLSELECTSno,Sname,Ssex,Sage,Sdept

INTO:Hno,:Hname,:Hsex,:Hage,:HdeptFROMStudentWHERESno=:Givensno;五.非CURRENT形式的UPDATE語句例5將全體學(xué)生1號(hào)課程的考試成績?cè)黾蛹僭O(shè)干分.假設(shè)增加的分?jǐn)?shù)已賦給了主變量Raise.EXECSQLUPDATESCSETGrade=Grade+:RaiseWHERECno=‘1’;例7.把計(jì)算機(jī)科學(xué)系全體學(xué)生的年齡置NULL值.SAID=-1;EXECSQLUPDATEStudentSETSage=:Raise:SageidWHERESdpt=‘CS’;它等價(jià)于:EXECSQLUPDATEStudentSETSage=NULLWHERESdpt=‘CS’;

六.非CURRENT形式的DELETE語句例8.某個(gè)學(xué)生退學(xué)了,現(xiàn)要將有關(guān)他的所有選課記錄刪除掉.假設(shè)該學(xué)生的姓名已賦給主變量Stdname.EXECSQLDELETEFROMSCWHERESno=(SELECTSnoFROMStudentWHERESname=:Stdname); 另一種等價(jià)實(shí)現(xiàn)方法為:EXECSQLDELETEFROMSCWHERE:Stdname=(SELECTSnameFROMStudentWHEREStudent.Sno=SC.Sno);

七.INSERT語句例9.某個(gè)學(xué)生選修了某門課程,將有關(guān)記錄插入SC表中,假設(shè)學(xué)生學(xué)號(hào)已賦給主變量stdno,課程號(hào)已賦主變量couno.gradeid=-1;EXECSQLINSERTINTOSC(Sno,Cno,Grade)VALUES(:stdno,:couno,:gr:gradeid);

3.7.4使用游標(biāo)的SQL語句

必須使用游標(biāo)SQL語句有:

●查詢結(jié)果為多條記錄的SELECT語句

●CURRENT形式的UPDATE語句

●CURRENT形式的DELETE語句

一.查詢結(jié)果為多條記錄的SELECT語句使用游標(biāo)的步驟:

(1).說明游標(biāo)

EXECSQLDECLARE<游標(biāo)名>CURSORFOR<SELECT語句>;(2).翻開游標(biāo)EXECSQLOPEN<游標(biāo)名>;(3).推進(jìn)游標(biāo)指針并取當(dāng)前記錄

EXECSQLFETCH<游標(biāo)名>INTO<主變量>[<指示變量>[;<主變量>[<指示變量>]]…;(4).關(guān)閉游標(biāo)

EXECSQLCLOSE<游標(biāo)名>;例1.查詢某個(gè)系全體學(xué)生的信息.要查的系名由用戶在程序運(yùn)行過程中指定,存放在主變量deptname中.……

EXECSQLBEGINDECLARESECTION;…../*說明主變量deptname，Hsno，Hsname，Shsex，Hsage等*/EXECSQLENDDECLARESECTION

……gets(deptname);……EXCESQLDECLARESXCURSORFORSELECTSno,Sname,Ssex,SageFROMStudentWHERESDept=:deptname;EXECSQLOPENSX;WHILE(1)

{EXECSQLFETCHSXINTO:HSno,:HSname,:Hssex,:HSage;if(sqlca.sqlcode<>SUCCES)break;……};EXECSQLCLOSESX;…….例2.查詢某些系全體學(xué)生的選課信息.……

EXECSQLBEGINDECLARESECTION;…../*說明主變量deptname，Hsno，Hsname，Shsex，Hsage等*/EXECSQLENDDECLARESECTION

……