《數據庫系統概論》實驗報告書專業班級學號姓名指導教師安徽工業大學計算機學院實驗一：數據定義/數據操縱語言實驗目的熟悉Oracle上機環境及Oracle客戶端的配置；熟練掌握和使用DDL語言，建立、修改和刪除數據庫表、主鍵、外鍵約束關系和索引；熟練掌握和使用DML語言，對數據進行增加、修改和刪除操作。實驗內容1．SQL數據定義語句：例1-1：(建立數據庫表)建立教學數據庫的四個數據庫表，其中Student表中不包含SSEX(C,2)字段，Sname字段為Sname(C,8)且可為空。例1-2：(修改數據庫表)在Student表中增加SEX(C,2)字段。例1-3：(修改列名)將Student表中列名SEX修改為SSEX。例1-4：(修改數據庫表)將Student表中把Sname字段修改為Sname(C,10)且為非空。例1-5：(建立索引)為Score表按課程號升序、分數降序建立索引，索引名為SC_GRADE。例1-6：(刪除索引)刪除索引SC_GRADE。例1-7：(建立數據庫表)建立數據庫表S1(SNO,SNAME,SD,SA)，其字段類型定義與Student表中的相應字段(SNO,SNAME,SDEPT,SAGE)的數據類型定義相同。例1-8：(修改數據庫表)刪除成績表Score的參照完整性約束關系。例1-9：(修改數據庫表)添加成績表Score的參照完整性約束關系。例1-10：(修改數據庫表名)將數據庫表S1改名為Student_Temp。2．SQL數據操縱語句：例2-1：(插入數據)按前面各表中的數據分別插入到教學數據庫的四個數據庫表中。例2-2：(多行插入)將表Student中在計算機系(‘CS’)的學生數據插入到表Student_Temp中。例2-3：(利用查詢來實現表的定義與數據插入)求每一個學生的平均成績，把結果存入數據庫表Student_Gr中。例2-4：(修改數據)將Student_Temp表中所有學生的年齡加2。例2-5：(修改數據)將Course表中‘程序設計’課時數修改成與‘數據結構’的課時數相同。例2-6：(插入數據)向Score表中插入數據（‘98001’,‘001’,95），根據返回信息解釋其原因。例2-7：(插入數據)向Score表中插入數據（‘97001’,‘010’,80），根據返回信息解釋其原因。例2-8：(刪除數據)刪除Student表中學號為‘96001’的學生信息，根據返回信息解釋其原因。例2-9：(刪除數據)刪除Course表中課程號為‘003’的課程信息，根據返回信息解釋其原因。例2-10：(刪除數據)刪除學生表Student_Temp中學號以96打頭的學生信息。例2-11：(刪除數據)刪除數據庫表Student_Temp中所有學生的數據。例2-12：(刪除表)刪除數據庫表Student_Temp和Student_Gr。實驗程序createtablestudent(snochar(5)primarykey,snamechar(8),sdeptchar(2)notnull,sclasschar(2)notnull,sagenumeric(2))createtablecourse(cnochar(3)primarykey,cnamechar(16)unique,ctimenumeric(3))createtableteach(tnamechar(8),tsexchar(2),cnochar(3),tdatedatetime,tdeptchar(2),primarykey(tname,cno,tdept),foreignkey(cno)referencescourse(cno))createtablescore(snochar(5),cnochar(3),scorenumeric(5,2)primarykey(sno,cno),foreignkey(sno)referencesstudent(sno),foreignkey(cno)referencescourse(cno))altertablestudentaddsexchar(2)sp_rename'student.sex','ssex','column'altertablestudentaltercolumnsnamechar(10)notnullcreateuniqueindexsc_gradeonscore(sno,scoredesc)dropindexscore.sc_gradecreatetables1(snochar(5)primarykey,snamechar(8),sdchar(2),sanumeric(2))altertablescoredropconstraintfk_score_studentaltertablescoreaddconstraintfk_score_studentforeignkey(sno)referencesstudent(sno)onupdatecascadeondeletecascadesp_rename's1','student_temp'insertintostudentvalues('96001','馬小燕','CS','01',21,'女');insertintostudentvalues('96002','黎明','CS','01',18,'男');insertintostudentvalues('96003','劉東明','MA','01',18,'男');insertintostudentvalues('96004','趙志勇','IS','02',20,'男');insertintostudentvalues('97001','馬蓉','MA','02',19,'女');insertintostudentvalues('97002','李成功','CS','01',20,'男');insertintostudentvalues('97003','黎明','IS','03',19,'女');insertintostudentvalues('97004','李麗','CS','02',19,'女');insertintostudentvalues('96005','司馬志明','CS','02',18,'男');insertintoCoursevalues('001','數學分析',144);insertintoCoursevalues('002','普通物理',144);insertintoCoursevalues('003','微機原理',80);insertintocoursevalues('004','數據結構',72);insertintocoursevalues('005','操作系統',80);insertintocoursevalues('006','數據庫原理',80);insertintocoursevalues('007','編譯原理',60);insertintocoursevalues('008','程序設計',40);insertintoteachvalues('王成剛','男','004','1999.9.5','CS');insertintoteachvalues('李正科','男','003','1999.9.5','CS');insertintoteachvalues('嚴敏','女','001','1999.9.5','MA');insertintoteachvalues('趙高','男','004','1999.9.5','MA');insertintoteachvalues('劉玉蘭','女','006','2000.2.23','CS');insertintoteachvalues('王成剛','男','004','2000.2.23','IS');insertintoteachvalues('馬悅','女','008','2000.9.6','CS');insertintoscorevalues('96001','001',77);insertintoscorevalues('96001','003',89);insertintoscorevalues('96001','004',86);insertintoscorevalues('96001','005',82);insertintoscorevalues('96002','001',88);insertintoscorevalues('96002','003',92);insertintoscorevalues('96002','006',90);insertintoscorevalues('96005','004',92);insertintoscorevalues('96005','005',90);insertintoscorevalues('96005','006',89);insertintoscorevalues('96005','007',76);insertintoscorevalues('96003','001',69);insertintoscorevalues('97001','001',96);insertintoscorevalues('97001','008',95);insertintoscorevalues('96004','001',87);insertintoscorevalues('96003','003',91);insertintoscorevalues('97002','003',91);insertintoscorevalues('97002','004',NULL);insertintoscorevalues('97002','006',92);insertintoscorevalues('97004','005',90);insertintoscorevalues('97004','006',85);insertintostudent_tempvalues('96001','馬小燕','CS',21)insertintostudent_tempvalues('96002','黎明','CS',18)insertintostudent_tempvalues('97002','李成功','CS',20)insertintostudent_tempvalues('97004','李麗','CS',19)insertintostudent_tempvalues('96005','司馬志明','CS',18)selectavg(score)avg1intostudent_grfromscoregroupbysnoupdatestudent_tempsetsa=sa+2;select*fromstudent_grupdatecoursesetctime=(selectctimefromcoursec1whereame='數據結構')wherecname='程序設計'插入的數據（‘98001’,‘001’,95）中，student中沒有98001這個學號插入的數據（‘97001’,‘010’,80）中，course中沒有010這個課程號刪除的數據中，96001這個學號在score中有記錄刪除的數據中，003這個課程號在score中有記錄deletefromstudent_tempwheresnolike'96%'deletefromstudent_tempdroptablestudent_temp,student_gr實驗總結本次試驗讓我初步認識了Oracle上機環境及Oracle客戶端的配置；熟習，建立、修改和刪除數據庫表、主鍵、外鍵約束關系和索引；熟練掌握怎么對數據進行增加、修改和刪除操作。實驗二：數據查詢語言實驗目的：體會SQL語言數據查詢功能的豐富和復雜。實驗內容：SQL數據查詢語句。例3-1：(選擇表中的若干列)求全體學生的學號、姓名、性別和年齡。例3-2：(不選擇重復行)求選修了課程的學生學號。例3-3：(選擇表中的所有列)求全體學生的詳細信息。例3-4：(使用表達式)求全體學生的學號、姓名和出生年份。例3-5：(使用列的別名)求學生的學號和出生年份，顯示時使用別名“學號”和“出生年份”。例3-6：(比較大小條件)求年齡大于19歲的學生的姓名和年齡。例3-7：(比較大小條件)求計算機系或信息系年齡大于18歲的學生的姓名、系和年齡。例3-8：(確定范圍條件)求年齡在19歲與22歲(含20歲和22歲)之間的學生的學號和年齡。例3-9：(確定范圍條件)求年齡不在19歲與22歲之間的學生的學號和年齡。例3-10：(確定集合條件)求在下列各系的學生信息：數學系、計算機系。例3-11：(確定集合條件)求不是數學系、計算機系的學生信息。例3-12：(匹配查詢)求姓名是以“李”打頭的學生。例3-13：(匹配查詢)求姓名中含有“志”的學生。例3-14：(匹配查詢)求姓名長度至少是三個漢字且倒數第三個漢字必須是“馬”的學生。例3-15：(匹配查詢)求選修課程001或003，成績在80至90之間，學號為96xxx的學生的學號、課程號和成績。例3-16：(匹配查詢)求課程名中包含’_’字符的課程號、課程名和學時數。例3-17：(涉及空值查詢)求缺少學習成績的學生的學號和課程號。例3-18：(控制行的顯示順序)求選修003課程或004課程的學生的學號、課程號和分數，要求按課程號升序、分數降序的順序顯示結果。例3-19：(組函數)求學生總人數。例3-20：(組函數)求選修了課程的學生人數。例3-21：(組函數)求計算機系學生的平均年齡。例3-22：(組函數)求選修了課程001的最高、最低與平均成績。例3-23：(分組查詢)求各門課程的平均成績與總成績。例3-24：(分組查詢)求各系、各班級的人數和平均年齡。例3-25：(分組查詢)輸入以下查詢語句并執行，觀察出現的其結果并分析其原因。SELECTSNAME，SDEPT，COUNT（*）FROMSTUDENTWHERESDEPT=’CS’GROUPBYSDEPT;例3-26：(分組查詢)分析以下語句為什么會出現錯誤。并給出正確的查詢語句。SELECTSAGEFROMSTUDENTGROUPBYSNO;例3-27：(分組查詢)求學生人數不足3人的系及其相應的學生數。例3-28：(分組查詢)求各系中除01班之外的各班的學生人數。例3-29(涉及空值的查詢)分別觀察各組函數、行的顯示順序以及分組查詢與空值的關系。例3-30：(連接查詢)求選修了課程001且成績在70分以下或成績在90分以上的學生的姓名、課程名稱和成績。例3-31：(連接查詢與表的別名)求選修了課程的學生的學生姓名、課程號和成績。例3-32：(自身連接查詢)求年齡大于’李麗’的所有學生的姓名、系和年齡。例3-33：(外部連接查詢)求選修了課程002或003的學生的學號、課程號、課程名和成績，要求必須將002和003課程的相關信息顯示出來。例3-34：(子查詢)求與‘黎明’年齡相同的學生的姓名和系。例3-35：(子查詢)求選修了課程名為’數據結構’的學生的學號和姓名。例3-36：(子查詢ANY)求比數學系中某一學生年齡大的學生的姓名和系。例3-37：(子查詢ALL)求比數學系中全體學生年齡大的學生的姓名和系。例3-38：(子查詢EXISTS)求選修了課程004的學生的姓名和系。例3-39：(返回多列的子查詢)求與‘李麗’同系且同齡的學生的姓名和系。例3-40：(多個子查詢)求與‘‘黎明’同系，且年齡大于‘李麗’的學生的信息。例3-41：(子查詢中使用表連接)求數學系中年齡相同的學生的姓名和年齡。例3-42：(連接或嵌套查詢)檢索至少選修王成剛老師所授課程中一門課程的女學生姓名。例3-43：(嵌套與分組查詢)檢索選修某課程的學生人數多于3人的教師姓名。例3-44：(集合查詢)列出所有教師和同學的姓名和性別。例3-45：(相關子查詢)求未選修課程004的學生的姓名。例3-46：(相關子查詢)求選修了全部課程的學生的姓名。例3-47：(相關子查詢)求至少選修了學生‘96002’所選修的全部課程的學生的學號。例3-48：(相關子查詢)求成績比所選修課程平均成績高的學生的學號、課程號、和成績。例3-49：(相關子查詢)查詢被一個以上的學生選修的課程號。例3-50：(相關子查詢)查詢所有未選課程的學生姓名和所在系。實驗程序selectsno,sname,ssex,sagefromsselectsnofromscgroupbysnoselects.sno,sname,sdept,sclass,ssex,sage,sc.score,o,ame,c.ctimefroms,sc,cwheres.sno=sc.snoando=o;selectsno,sname,2009-sagebirthdayfroms;selectsno學號,2009-sage出生年份froms;selectsname,sagefromswheresage>19;selectsname,sdept,sagefromswheresdeptin('CS','IS')andsage>18;selectsno,sagefromswheresagebetween19and22;selectsno,sagefromswheresagenotbetween19and22;select*fromswheresdeptin('CS','MA');select*fromswheresdeptnotin('CS','MA');selectsno,snamefromswheresnamelike'李%';selectsno,snamefromswheresnamelike'%志%';select*fromswheresnamelike'%馬__';selectsno,cno,scorefromscwherecnoin('001','003')andscorebetween80and90andsnolike'96%'select*fromcwherecnolike'%\_&'escape'\'selectsno,cnofromscwherescoreisnull;select*fromscwherecnoin('003','004')orderbycno,scoredescselectcount(sno)fromsselectcount(distinctsno)fromscselectavg(sage)fromswheresdept='cs'selectmax(score),min(score),avg(score)fromscwherecno='001'selectcno,avg(score),sum(score)fromscgroupbycnoselectsdept,sclass,count(sclass),avg(sage)fromsgroupbysdept,sclassorderbysdeptselect*fromsselectsname,sdept,count(*)fromswheresdept='cs'groupbysdept,sname在結果的查詢中，只有一行，而sname有好幾個，故不能顯示出selectsagefromsgroupbysno,sageselectsdept,count(*)fromsgroupbysdepthavingcount(*)<3selectsdept,sclass,count(*)fromswheresclass!='01'groupbysdept,sclassorderbysdeptselectsname,cname,scorefromsc,s,cwheresc.sno=s.snoando=oando='001'andscorenotbetween70and90selectsname,cno,scorefromscsc1,ss1wheres1.sno=sc1.snoandoisnotnullselects1.sname,s1.sdept,s1.sagefromss1,ss2wheres1.sage>s2.sageands2.sname='李麗'selectsc.sno,o,ame,scorefromsc,cwhereoin('002','003')ando=oselectsname,sdeptfromswheresagein(selectsagefromswheresname='黎明')selectsno,snamefromswheresnoin(selectsnofromscwherecno=(selectcnofromcwherecname='數據結構'))selectsname,sdeptfromswheresage>any(selectsagefromswheresdept='ma')selectsname,sdeptfromswheresage>all(selectsagefromswheresdept='ma')selectsname,sdeptfromswhereexists(select*fromscwheresc.sno=s.snoando='004')selectsname,sdeptfromss1whereexists(select*fromss2wheres2.sname='李麗'ands2.sdept=s1.sdeptands2.sage=s1.sageands2.sname!=s1.sname)select*fromss1wheresdeptin(selectsdeptfromswheresname='黎明')andsage>(selectsagefromswheresname='李麗')selectdistincts3.sname,s3.sagefromss3,ss2wheres3.sdept='cs'ands3.sage=s2.sageands3.sdept=s2.sdeptands3.sno!=s2.snoorderbys3.sageselectdistinctsnamefroms,scwheressex='女'ands.sno=sc.snoandcnoin(selectcnofromtwheretname='王成剛')select*fromswheresdept='cs'selecttnamefromtwhereexists(select*fromscwhereo=ohavingcount(*)>3)selectsname,ssexfromsunionselecttname,tsexfromtselectsnamefromswherenotexists(select*fromscwheresc.sno=s.snoandcno='004')selectsnamefromswherenotexists(select*fromcwherenotexists(select*fromscwheresc.sno=s.snoando=o))selectdistinctsnofromscsc1wherenotexists(select*fromscsc2wheresc2.sno='96002'andnotexists(select*fromscsc3wheresc3.sno=sc1.snoando=o))andsc1.sno!='96002'select*fromscsc1wherescore>(selectavg(score)fromscsc2wheresc2.sno=sc1.sno)selectcnofromscgroupbycnohavingcount(*)>1selectsname,sdeptfromss1wherenotexists(select*fromscwheresc.sno=s1.sno)實驗總結本次試驗使我認識到了體會SQL語言數據查詢功能的豐富和復雜性。也更加加深了對了解和運用SQL語言的興趣，感覺語言并不是想象中那樣的有難度，反而極大方便了編程的一些功能實驗三：視圖、授權控制與事務處理實驗目的：通過實驗進一步理解視圖的建立和更新、數據庫的權限管理和事務處理功能。實驗內容：1.視圖的定義與操縱；例4-1：(建立視圖)建立計算機系的學生的視圖STUDENT_CS。例4-2：(建立視圖)建立由學號和平均成績兩個字段的視圖STUDENT_GR。例4-3：(視圖查詢)利用視圖STUDENT_CS，求年齡大于19歲的學生的全部信息。例4-4：(視圖查詢)利用視圖STUDENT_GR求平均成績為88分以上的學生學號和平均成績。例4-5：(視圖更新)利視圖STUDENT_CS，增加學生(‘96006’,‘張然’,‘CS’,‘02’,‘男’,19)。例4-6：視圖更新)利用視圖STUDENT_CS，將學生年齡增加1歲。觀察其結果并分析原因。例4-7：(視圖更新)利用視圖STUDENT_GR，將平均成績增加2分。觀察其運行結果并分析原因。例4-8：(視圖更新)刪除視圖STUDENT_CS中學號為‘96006’的學生的全部數據。例4-9：(視圖更新)刪除視圖STUDENT_GR的全部數據。例4-10：(刪除視圖)刪除視圖STUDENT_CS和STUDENT_GR。2.SQL數據控制語句；例5-1：(授權)給左右鄰近同學（用戶）授予在表Student上的SELECT權限，并使這兩個用戶具有給其他用戶授予相同權限的權限。例5-2：(授權)給鄰近同學（用戶）授予Teach表上的所有權限。例5-3：(授權)給所有用戶授予Score表上的SELECT權限。例5-4：(授權驗證)觀察左右鄰近同學查詢你所授權的表中的內容。例5-5：(收回授權)收回上面例子中的所有授予的權限。3.SQL事務處理。例6-1：(事務回退)將課程名稱表中的‘程序設計’課程學時數修改為80、‘微機原理’課程學時數修改為70學時，查詢全部課程的總學時數后,取消所有修改(ROLLBACK)。再次查詢全部課程的總學時數。注意比較分析兩次查詢的結果。例6-2：(事務提交)將課程名稱表中的‘程序設計’課程學時數修改為80、‘微機原理’課程學時數修改為70學時，查詢全部課程的總學時數后,確認所有修改(COMMIT)。再次查詢全部課程的總學時數。注意比較分析兩次查詢的結果。實驗程序1----createviewstudent_csasselect*fromswheresdept='cs'createviewstudent_gr(sno,avg_score)asselectsno,avg(score)fromscgroupbysnoselect*fromstudent_cswheresage>19select*fromstudent_grwhereavg_score>88insertintostudent_csvalues('96006','張然','CS','02','男',19)updatestudent_cssetsage=sage+1;updatestudent_grsetavg_score=avg_score+2;原因是student_gr表的avg_score由avg函數生成，不能更新不能刪除數據dropviewstudent_cs,student_gr2-----execsp_addlogin@loginame='zhang',@passwd='123'execsp_grantdbaccess'zhang'execsp_addlogin@loginame='wang',@passwd='123'execsp_grantdbaccess'wang'grantselectonstowang,zhangwithgrantoptiongrantallprivilegesonttowang,zhanggrantselectonsctopublic3-----revokeselectonscfrompublicrevokeselectonsfromwang,zhangcascaderevokeallprivilegesontfromwang,zhang實驗總結本次試驗使我進一步理解視圖的建立和更新、數據庫的權限管理和事務處理功能實驗四：存儲過程與觸發器實驗目的：通過實驗進一步理解視圖的建立和更新、數據庫的權限管理和事務處理功能。實驗內容：1.存儲過程；例6-1：(事務回退)將課程名稱表中的‘程序設計’課程學時數修改為80、‘微機原理’課程學時數修改為70學時，查詢全部課程的總學時數后,取消所有修改(ROLLBACK)。再次查詢全部課程的總學時數。注意比較分析兩次查詢的結果。例6-2：(事務提交)將課程名稱表中的‘程序設計’課程學時數修改為80、‘微機原理’課程學時數修改為70學時，查詢全部課程的總學時數后,確認所有修改(COMMIT)。再次查詢全部課程的總學時數。注意比較分析兩次查詢的結果。

2.觸發器。例7-1：(存儲過程)創建一個顯示學生總人數的存儲過程。例7-2：(存儲過程)創建顯示學生信息的存儲過程STUDENT_LIST，并引用STU_COUNT存儲過程。例7-3：(存儲過程)創建一個顯示學生平均成績的存儲過程。例7-4：(存儲過程)創建顯示所有學生平均成績的存儲過程。例7-5：(修改數據庫表)在Student表中增加SAVG(N,6,2)字段。例7-6：(存儲過程)創建存儲過程，計算每個學生的平均成績保存到學生表SAVG字段中。例7-7：(觸發器)創建包含插入、刪除、修改多種觸發事件的觸發器DML_LOG，對SCORE表的操作進行記錄。用INSERTING、DELETING、UPDATING謂詞來區別不同的DML操作。實驗程序1----createprocedurestu_countasselectcount(*)fromscreateprocedurestudent_li