數(shù)據(jù)庫系統(tǒng)概論實驗指導書2013上課用

i《數(shù)據(jù)庫系統(tǒng)概論》實驗指導書目錄TOC\o"1-1"\h\z實驗一：數(shù)據(jù)庫管理系統(tǒng)軟件的使用 1實驗二：數(shù)據(jù)庫的建立和維護 8實驗三：數(shù)據(jù)庫的簡單查詢和連接查詢 12實驗四：數(shù)據(jù)庫的嵌套查詢實驗 14實驗五：數(shù)據(jù)庫的分組查詢和統(tǒng)計查詢 16實驗六：數(shù)據(jù)庫視圖的定義及使用 18實驗七：數(shù)據(jù)完整性實驗 20實驗八*：數(shù)據(jù)庫備份和恢復實驗 27PAGE43說明：1.帶*號的為選做；2.實驗環(huán)境為SqlServer。但除實驗七有較大差別外，其余的所有實驗在Access、Oracle、MySql甚至VFP中都類似。除在SqlServer中進行實驗外，建議也在Access中操作一下。

實驗一：數(shù)據(jù)庫管理系統(tǒng)軟件的使用一、實驗目的(1)認識幾種常見的數(shù)據(jù)庫管理系統(tǒng)，熟悉它們的使用界面;(2)熟練掌握建立數(shù)據(jù)庫和表，向數(shù)據(jù)庫輸入數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)的操作。二、實驗內容分別在Access和SQLSERVER2000中建立數(shù)據(jù)庫并設計各表，輸入多條實際數(shù)據(jù)，并實現(xiàn)數(shù)據(jù)的增、刪、改操作。三、實驗步驟：創(chuàng)建用于學生管理數(shù)據(jù)庫，數(shù)據(jù)庫名為XSGL，包含學生的基本信息，課程信息和選課信息。數(shù)據(jù)庫XSGL包含下列3個表：(l）student：學生基本信息。(2）course：課程信息表。(3）sc：學生選課表。各表的結構分別如表1、表2和表3所示。表1學生信息表：student列名數(shù)據(jù)類型長度是否允許為空值sno字符（文本）型10否sname字符（文本）型10否ssex字符（文本）型2否sage整數(shù)（數(shù)值）型是sdept字符型4否表2課程信息表：course列名數(shù)據(jù)類型長度是否允許為空值cno字符（文本）型3否cname字符（文本）型30否credit整數(shù)（數(shù)值）型是pcno字符（文本）型3是表3學生選課表：sc列名數(shù)據(jù)類型長度是否允許為空值sno字符（文本）型10否cno字符（文本）型30否grade整數(shù)（數(shù)值）型是用可視化界面在Access中建立數(shù)據(jù)庫和表：（a）在Access中創(chuàng)建xsgl.mdb數(shù)據(jù)庫，使用表設計視圖創(chuàng)建如表1、表2、表3所示結構的3個表。提示：(1)啟動Access2000或Access2003，選擇文件->新建->空數(shù)據(jù)庫，輸入數(shù)據(jù)庫文件名xsgl.mdb，進入（2）；圖1Access數(shù)據(jù)庫設計界面（2）啟動如圖1的設計界面后，選擇表對象和使用使用設計器創(chuàng)建表，選擇新建，進入（3）圖1Access數(shù)據(jù)庫設計界面圖2表設計器（3）生成如圖2界面，選擇設計視圖,點擊確定按鈕，進入（4）；圖2表設計器圖3表結構設計界面（4）在圖3表設計界面下，分別創(chuàng)建student表，course表和sc表圖3表結構設計界面（5）輸入表中的記錄：分別在student表、course表和sc表中輸入如下表中的記錄：在數(shù)據(jù)庫視圖下，選擇表對象中的相應表選擇打開，在瀏覽視圖下輸入如下表記錄：snosnamessexsagesdept95001李勇男20CS95002劉晨女19IS95003王敏女18MA95004張立男19IS95005劉云女18CScnocnamecreditpcno1數(shù)據(jù)庫452數(shù)學63信息系統(tǒng)314操作系統(tǒng)465數(shù)據(jù)結構476數(shù)據(jù)處理37PASCAL語言46snocnograde9500119295001285950013889500229095002380950032859500415895004285（b）對表中的記錄進行瀏覽、修改、刪除操作。2．在SQLSERVER中用企業(yè)管理器新建數(shù)據(jù)庫和表：（1）建立xsgl數(shù)據(jù)庫：=1\*GB3①啟動SQLServer2000企業(yè)管理器，界面如下：圖4SQLServer2000企業(yè)管理器界面=2\*GB3②選擇樹形菜單數(shù)據(jù)庫，點擊鼠標右鍵，出現(xiàn)如下彈出式菜單，選擇新建數(shù)據(jù)庫=3\*GB3③在圖中，按圖分別設置數(shù)據(jù)庫xsgl的屬性，點擊確定按鈕，完成了數(shù)據(jù)庫的創(chuàng)建。圖5數(shù)據(jù)庫名稱屬性設置圖6數(shù)據(jù)庫數(shù)據(jù)文件屬性設置圖7數(shù)據(jù)庫日志文件屬性設置選擇確定按鈕，則創(chuàng)建xsgl數(shù)據(jù)庫。（2）建立student、course、sc表：=1\*GB3①選擇xsgl數(shù)據(jù)庫樹形菜單，選擇表，在圖8表邏輯對象中點擊鼠標的右鍵，在彈出式菜單中選擇新建，啟動表設計器。圖8表邏輯對象界面=2\*GB3②選擇新建，啟動如圖9的表設計器，建立表結構，保存為student。圖9student的表結構=3\*GB3③分別按表2、3建立course表和sc表。（3）在表中添加記錄：選擇student表，在快捷菜單下選擇打開表->返回所有行，進入圖11，輸入學生表中的記錄。圖10圖11student表記錄錄入依次按相同方法建立course表和sc表，并輸入其中的記錄。(4)修改表中的數(shù)據(jù):(5）刪除表中的記錄思考：Access數(shù)據(jù)庫主要有哪些對象組成？SQLSERVER2000數(shù)據(jù)庫主要由哪些邏輯對象組成？物理數(shù)據(jù)庫文件包括哪些文件？

實驗二：數(shù)據(jù)庫的建立和維護實驗目的熟練掌握建立數(shù)據(jù)庫和表，向數(shù)據(jù)庫輸入數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)的操作。實驗內容建立數(shù)據(jù)庫并設計各表，輸入多條實際數(shù)據(jù)，并實現(xiàn)數(shù)據(jù)的增、刪、改操作。實驗步驟：創(chuàng)建用于學生管理數(shù)據(jù)庫，數(shù)據(jù)庫名為XSGL，包含學生的基本信息，課程信息和選課信息。數(shù)據(jù)庫XSGL包含下列3個表：(l）student：學生基本信息。(2）course：課程信息表。(3）sc：學生選課表。各表的結構分別如表1、表2和表3所示。表1學生信息表：student列名數(shù)據(jù)類型長度是否允許為空值sno字符型10否sname字符型10否ssex字符型2否sage整數(shù)型是sdept字符型4否表2課程信息表：course列名數(shù)據(jù)類型長度是否允許為空值cno字符型3否cname字符型30否credit整數(shù)型是pcno字符型3是表3學生選課表：sc列名數(shù)據(jù)類型長度是否允許為空值sno字符型10否cno字符型30否grade整數(shù)型是一.數(shù)據(jù)庫的建立：用可視化界面建立：在SQLSERVER中用企業(yè)管理器-新建數(shù)據(jù)庫；命令方式建立：在SQLSERVER中，在查詢分析器中使用T-SQL語句：CREATEDATABASEXSGLON(NAME='XSGL_DATA',FILENAME='E:\XSGL.MDF',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5%)LOGON(NAME='XSGL_Log',FILENAME='e:\XSGL_Log.ldf',SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)二.表的建立：用可視化界面建立：在SQLSERVER中用企業(yè)管理器—數(shù)據(jù)庫—XSCJ—表—右鍵—新建表;命令方式建立：在SQLSERVER中查詢分析器的編輯窗口中用下列SQL語句:useXSGLCreatetablestudent(snoCHAR(5),snameCHAR(10),ssexCHAR(2),sageint,sdeptCHAR(4))goCreatetablecourse(cno CHAR(2),cnameCHAR(30),credit INT,pcno CHAR(2)NULL)goCreatetablesc(snoCHAR(5),cnoCHAR(2),grade INTNULL)go三.表數(shù)據(jù)的添加:1.用可視化方法:I:在SQLSERVER中用企業(yè)管理器—數(shù)據(jù)庫—XSCJ—表－表名—右鍵－打開表－返回所有行；輸入下列數(shù)據(jù)：snosnamessexsagesdept95001李勇男20CS95002劉晨女19IS95003王敏女18MA95004張立男19IS95005劉云女18CScnocnamecreditpcno1數(shù)據(jù)庫452數(shù)學63信息系統(tǒng)314操作系統(tǒng)465數(shù)據(jù)結構476數(shù)據(jù)處理37PASCAL語言46snocnograde95001192950012859500138895002290950023809500328595004158950042852.在SQLSERVER查詢分析器的編輯窗口中使用下列SQL語句插入數(shù)據(jù):insertintostudent(sno,sname, ssex,sage,sdept)values('95001', '李勇', '男', 20, 'CS')insertintostudent(sno,sname, ssex,sage,sdept)values('95002', '劉晨', '女', 19,'IS')insertintostudent(sno,sname, ssex,sage,sdept)values('95003', '王敏', '女', 18, 'MA')insertintostudent(sno,sname, ssex,sage,sdept)values('95004', '張立', '男', 19, 'IS')insertintostudent(sno,sname, ssex,sage,sdept)values('95005', '劉云', '女', 18, 'CS')insertintocourse(cno, cname,credit,pcno)values('1', '數(shù)據(jù)庫', 4,'5')insertintocourse(cno, cname,credit,pcno)values('2', '數(shù)學', 6, null) insertintocourse(cno, cname,credit,pcno)values('3', '信息系統(tǒng)',3, '1')insertintocourse(cno, cname,credit,pcno)values('4', '操作系統(tǒng)',4, '6')insertintocourse(cno, cname,credit,pcno)values('5', '數(shù)據(jù)結構',4, '7')insertintocourse(cno, cname,credit,pcno)values('6', '數(shù)據(jù)處理',3, null) insertintocourse(cno, cname,credit,pcno)values('7', 'PASCAL語言',4, '6')insertintosc(sno,cno,grade)values('95001', '1' ,92)insertintosc(sno,cno,grade)values('95001', '2' ,85)insertintosc(sno,cno,grade)values('95001', '3' ,88)insertintosc(sno,cno,grade)values('95002', '2' ,90)insertintosc(sno,cno,grade)values('95002', '3' ,80)insertintosc(sno,cno,grade)values('95003', '2' ,85)insertintosc(sno,cno,grade)values('95004', '1' ,58)insertintosc(sno,cno,grade)values('95004', '2' ,85)四.表數(shù)據(jù)的修改:1.用可視化方法:在SQLSERVER中用企業(yè)管理器—數(shù)據(jù)庫—XSCJ—表－表名—右鍵－打開表－返回所有行；在表格中將相應的數(shù)據(jù)修改即可。2．命令方法：將所有學生的年齡增加一歲：updatestudentsetsage=sage+1將4號課程的學分改為4:updatecoursesetcredit=4wherecno=4設置7號課程沒有先行課:updatecoursesetpcno=nullwherecno=7將95001號學生的1號課程的成績增加3分:updatescsetgrade=grade+3wheresno=’95001’andcno=’五.表數(shù)據(jù)的刪除:1.用可視化方法:在SQLSERVER中用企業(yè)管理器—數(shù)據(jù)庫—XSCJ—表－表名—右鍵－打開表－返回所有行；單擊左邊的行標記,選定某一行,或單擊后拖動選擇相鄰的多行,再右擊鼠標選擇彈出式菜單中的刪除。2．命令方法：刪除學號為95005的學生的記錄：deletefromstudentwheresno＝’95005刪除所有的課程記錄：deletefromcourse刪除成績?yōu)椴患案瘢ㄉ儆?0分）的學生的選課記錄：deletefromscwheregrade<60思考：比較用可視化界面與命令方式在數(shù)據(jù)的插入、修改、刪除方面的優(yōu)缺點。

實驗三：數(shù)據(jù)庫的簡單查詢和連接查詢實驗目的：掌握簡單表的數(shù)據(jù)查詢、數(shù)據(jù)排序和數(shù)據(jù)聯(lián)結查詢的操作方法。實驗內容：簡單查詢操作和連接查詢操作。實驗步驟：一.單表查詢：1.查詢全體學生的學號和姓名:selectsno,snamefromstudent2.查詢全體學生的所有信息:select*fromstudent或者selectsno,sname,ssex,sage,sdeptfromstudent3.查詢全體學生的姓名,出生年份,和所在系,并用小寫字母表示所有系名:selectsname,'出生年份為:',year(getdate())-sage,lower(sdept)fromstudent4.給上例的結果集指定列名:selectsname,'出生年份為:'出生,year(getdate())-sage年份,lower(sdept)系名fromstudent5.查詢選修了課程的學生的學號:selectdistinctsnofromsc比較:selectsnofromsc6.查詢年齡在20歲以下的學生的姓名及其年齡:selectsname,sagefromstudentwheresage<207.查詢考試成績有不及格的學生的學號:selectdistinctsnofromscwheregrade<60比較:selectsnofromscwheregrade<608.查詢年齡在20-30歲直接的學生的姓名,姓名,所在系:selectsname,ssex,sdeptfromstudentwheresagebetween20and309.查詢IS,CS,MA系的所有學生的姓名和性別:selectsname,ssexfromstudentwheresdeptin('IS','MA','CS')10.查找所有姓’李’的學生的姓名,學號和性別:selectsname,sno,ssexfromstudentwheresnamelike'李%'比較:將學生表中的’95001’號學生的姓名’李勇’改為’李勇勇’selectsname,sno,ssexfromstudentwheresnamelike'李_'11.查詢沒有先行課的課程的課程號cno和課程名cname:selectcno,cnamefromcoursewherepcnoisnull二.查詢結果排序12.查詢選修了3號課程的學生的學號和成績,并按分數(shù)降序排列:selectsno,gradefromscwherecno='3'orderbygradeDESC23.查詢全體學生的情況,查詢結果按所在系號升序排列,同一系中的學生按年齡降序排列:select*fromstudentorderbysdeptASC,sageDESC三.連接查詢:14.查詢每個學生及其選修課程的情況:selectstudent.*,sc.*fromstudent,scwherestudent.sno=sc.sno比較:笛卡爾集:selectstudent.*,sc.*fromstudent,sc自然連接:selectstudent.sno,sname,ssex,sdept,cno,gradefromstudent,scwherestudent.sno=sc.sno15.查詢每一門課程的間接先行課(只求兩層即先行課的先行課):selectFo,Second.pcno間接先行課fromcourseFirst,courseSecondwhereFirst.pcno=So比較:selectFo,Second.pcno間接先行課fromcourseFirst,courseSecondwhereFirst.pcno=SoandSecond.pcnoisnotnull16.列出所有學生的基本情況和選課情況,若沒有選課,則只列出基本情況信息:SQLServer中:selects.sno,sname,ssex,sdept,cno,gradefromstudents,scscwheres.sno*=sc.sno17.查詢每個學生的學號,姓名,選修的課程名和成績:selectS.sno,sname,cname,gradefromstudentS,courseC,scSCwhereS.sno=SC.snoandC.cno=SC.cno思考:如何求出不及格學生的學號,姓名,不及格的課程名以及成績。

實驗四：數(shù)據(jù)庫的嵌套查詢實驗實驗目的：加深對嵌套查詢語句的理解。實驗內容：使用IN、比較符、ANY或ALL和EXISTS操作符進行嵌套查詢操作。實驗步驟：一.使用帶IN謂詞的子查詢查詢與’劉晨’在同一個系學習的學生的信息:select*fromstudentwheresdeptin(selectsdeptfromstudentwheresname='劉晨')比較:select*fromstudentwheresdept=(selectsdeptfromstudentwheresname='劉晨')的異同比較:select*fromstudentwheresdept=(selectsdeptfromstudentwheresname='劉晨')andsname<>'劉晨V比較:selectS1.*fromstudentS1,studentS2whereS1.sdept=S2.sdeptandS2.sname='劉晨'查詢選修了課程名為’信息系統(tǒng)’的學生的學號和姓名:SQLServer中:selectsno,snamefromstudentwheresnoin(selectsnofromscwherecnoin(selectcnofromcoursewherecname='信息系統(tǒng)'))查詢選修了課程’1’和課程’2selectsnofromstudentwheresnoin(selectsnofromscwherecno='1')andsnoin(selectsnofromscwherecno='2')比較:查詢選修了課程’1’或課程’2selectsnofromscwherecno='1'orcno='2'比較連接查詢:selectA.snofromscA,scBwhereA.sno=B.snoandA.cno='1'andB.cno='2'二.使用帶比較運算的子查詢查詢比’劉晨’年齡小的所有學生的信息:select*fromstudentwheresage<(selectsagefromstudentwheresname='劉晨')三.使用帶Any,All謂詞的子查詢查詢其他系中比信息系(IS)某一學生年齡小的學生姓名和年齡;selectsname,sagefromstudentwheresage<Any(selectsagefromstudentwheresdept='IS')andsdept<>'IS'查詢其他系中比信息系(IS)學生年齡都小的學生姓名和年齡:selectsname,sagefromstudentwheresage<ALL(selectsagefromstudentwheresdept='IS')andsdept<>'IS'查詢與計算機系(CS)系所有學生的年齡均不同的學生學號,姓名和年齡:selectsno,sname,sagefromstudentwheresage<>all(selectsagefromstudentwheresdept='CS')四.使用帶Exists謂詞的子查詢和相關子查詢查詢與其他所有學生年齡均不同的學生學號,姓名和年齡:selectsno,sname,sagefromstudentAwherenotexists(select*fromstudentBwhereA.sage=B.sageandA.sno<>B.sno)查詢所有選修了1號課程的學生姓名:selectsnamefromstudentwhereexists(select*fromscwheresno=student.snoandcno='1')查詢沒有選修了1號課程的學生姓名:selectsnamefromstudentwherenotexists(select*fromscwheresno=student.snoandcno='1')查詢選修了全部課程的學生姓名:selectsnamefromstudentwherenotexists(select*fromcoursewherenotexists(select*fromscwheresno=student.snoandcno=o))11.查詢至少選修了學生95002選修的全部課程的學生的學號:selectdistinctsnofromscAwherenotexists(select*fromscBwheresno='95002'andnotexists(select*fromscCwheresno=A.snoandcno=B.cno))12.求沒有人選修的課程號cno和cnamecname:selectcno,cnamefromcourseCwherenotexists(select*fromscwhereo=C.cno)13*.查詢滿足條件的(sno,cno)對,其中該學號的學生沒有選修該課程號cno的課程selectsno,cnofromstudent,coursewherenotexists(select*fromscwherecno=oandsno=student.sno)14*.查詢每個學生的課程成績最高的成績信息(sno,cno,grade):select*fromscAwheregrade=(selectmax(grade)fromscwheresno=A.sno)思考:如何查詢所有學生都選修了的課程的課程號cno?

實驗五：數(shù)據(jù)庫的分組查詢和統(tǒng)計查詢實驗目的：熟練掌握數(shù)據(jù)查詢中的分組、統(tǒng)計、計算和集合的操作方法。實驗內容：使用聚集函數(shù)查詢、分組計算查詢、集合查詢。實驗步驟：一.使用聚集函數(shù)：查詢學生總人數(shù)：SelectCount(*)as學生總數(shù)fromstudent2.查詢選修了課程的學生總數(shù)：selectcount(distinctsno)as選課學生總數(shù)fromsc3.查詢所有課程的總學分數(shù)和平均學分數(shù),以及最高學分和最低學分：selectsum(credit)as總credit,avg(credit)as課程平均學分,max(credit)as最高學分,min(credit)as最低學分fromcourse4.計算1號課程的學生的平均成績,最高分和最低分:selectavg(grade)as平均成績,max(grade)as最高分,min(grade)as最低分fromscwherecno='1'5.查詢’信息系’(IS)學生”數(shù)據(jù)結構”課程的平均成績:selectavg(grade)fromstudent,course,scwherestudent.sno=sc.snoando=oandsdept='IS'andcname='數(shù)據(jù)結構'6*.查詢每個學生的課程成績最高的成績信息(sno,cno,grade):select*fromgradeAwheregrade=(selectmax(grade)fromscwheresno=A.sno)7*.求成績低于該門課程平均成績的學生的成績信息(sno,cno,grade)select*fromgradeAwheregrade=(selectavg(grade)fromscwherecno=A.cno)二.分組查詢8.查詢各系的學生的人數(shù)并按人數(shù)從多到少排序:selectsdept,Count(*)as人數(shù)fromstudentgroupbysdeptorderby人數(shù)desc9.查詢各系的男女生學生總數(shù),并按系別,升序排列,女生排在前:selectsdept,ssex,Count(*)as人數(shù)fromstudentgroupbysdept,ssexorderbysdept,ssexdesc10.查詢選修了3門課程已上的學生的學號和姓名:selectsno,snamefromstudentwheresnoin(selectsnofromscgroupby(sno)havingcount(*)>3)11.查詢每個學生所選課程的平均成績,最高分,最低分,和選課門數(shù):selectsno,avg(grade)as平均成績,max(grade)as最高分,min(grade)as最低分,count(*)as選課門數(shù)fromscgroupbysno12.查詢至少選修了2門課程的學生的平均成績:selectsno,avg(grade)as平均成績,fromscgroupbysnohavingcount(*)>=213.查詢平均分超過80分的學生的學號和平均分:Selectsno,avg(grade)as平均成績fromscgroupbysnohavingavg(*)>=80比較:求各學生的60分以上課程的平均分:selectsno,avg(grade)as平均成績fromscwheregrade>=60groupbysno14.查詢”信息系”(IS)中選修了5門課程以上的學生的學號:selectsnofromscwheresnoin(selectsnofromstudentwheresdept='IS')groupbysnohavingcount(*)>=2三.集合查詢15.查詢數(shù)學系和信息系的學生的信息;select*fromstudentwheresdept=’MA’unionselect*fromstudentwheresdept='IS'16.查詢選修了1號課程或2號課程的學生的學號:selectsnofromscwherecno='1'Unionselectsnofromscwherecno='2'比較實驗三之3.思考:用兩種方法查詢平均成績少于70分的學生的學號。Selectsnofromscgroupbysnohavingavg(grade)<80Selectdistinctsnofromscawhere(selectavg(grade)fromscwheresno=a.sno)<802*.求各系的”大學英語”課程的成績最高的學生的姓名和成績。selectsname,gradefromstudentA,scBwhereA.sno=B.snoandB.gradein(selectmax(grade)fromsc,course,studentwherestudent.sno=sc.snoando=oandcname='大學英語'groupbysdept)

實驗六：數(shù)據(jù)庫視圖的定義及使用實驗目的：掌握視圖的用法，加深對視圖作用的理解。實驗內容：1、創(chuàng)建、修改和刪除視圖。2、利用視圖進行查詢。實驗步驟：說明：對視圖的創(chuàng)建、修改和刪除均可在可視化界面下操作。在SQLServer企業(yè)管理器中，依次選擇數(shù)據(jù)庫――XSGL――視圖――鼠標右擊――新建視圖。然后通過單擊按鈕或右擊鼠標第一格的空白區(qū)域，選擇彈出菜單中的“添加表”。然后拖拽主鍵到外鍵，建立關聯(lián)，點擊字段左邊的方框選擇輸出字段。圖5-1僅以命令方式來操作：一、視圖的創(chuàng)建：1．創(chuàng)建信息系學生信息的視圖：createviewIS_Studentasselectsno,sname,sagefromstudentwheresdept='IS'2.創(chuàng)建信息系選修了1號課程的學生的視圖:createviewIS_S1asselectstudent.sno,cno,gradefromstudent,scwherestudent.sno=sc.snoandsdept='IS'andcno='1'3.建立信息系選修了1號課程且成績在90分以上的學生的視圖:createviewasIS_S2asselect*fromIS_S1wheregrade>=904.創(chuàng)建一個反映學生出生年份的視圖:createviewBT_S(sno,sname,出生年份)asselectsno,sname,year(date())-sagefromstudent5.將所有女生的記錄定義為一個視圖:createviewF_studentasselect*fromstudentwheressex='女'6.將所有學生的學號和他的平均成績定義為一個視圖:createviewS_G(sno,avg_grade)asselectsno,avg(grade)fromscgroupbysno二、視圖結構的修改：6.將視圖F_student修改為信息系的所有女士的視圖alterviewF_studentasselect*fromstudentwheressex='女'andsdept='IS'說明:視圖結構的修改的AS后的Select語句與創(chuàng)建視圖的完全一致,引入結構修改的目的是為了避免與視圖相關的數(shù)據(jù)庫對象的變換,如觸發(fā)器,關聯(lián)等三、查詢視圖7.在信息系的學生視圖中查詢年齡小于20歲的學生:select*fromIS_Studentwheresage<208.查詢信息系選修了1號課程的學生:selectsc.sno,snamefromIS_Student,scwhereIS_Student.sno=sc.snoandcno='1'9.在視圖S_G中查詢平均成績在90分以上的學生的學號和平均成績:Select*fromS_Gwhereavg_grade>=90四、更新視圖：10．將信息系學生視圖IS_Student中學號為”95002”的學生姓名改為”劉辰”updateIS_Studentsetsname='劉辰'wheresno='95002'比較:updateIS_Studentsetsname=’劉辰’wheresno=’95003’11.向信息系學生視圖IS_Student中插入一個新的學生記錄,學號為95029,姓名為”趙新”,年齡為20歲:insertintoIS_Studentvalues('95029','趙新',20)12.刪除信息系學生視圖IS_Student中學號為95004的學生的記錄:deletefromIS_Studentwheresno='95029'五、刪除視圖：13.刪除視圖IS_S1:dropviewIS_S1思考:創(chuàng)建所有學生的基本信息和選課信息的視圖基于上述視圖查詢各系學生各門功課的平均成績.

實驗七：數(shù)據(jù)完整性實驗實驗目的：加深對數(shù)據(jù)完整性的理解。實驗內容：數(shù)據(jù)庫的完整性設置。實驗步驟：可視化界面的操作方法：一、實體完整性1．將student表的“sno”字段設為主鍵：在表設計界面中，單擊左邊的行選定塊，選定“sno”字段，單擊工具按鈕設置主鍵。如圖6-1所示：圖6-1將“sc”表的“sno”和“cno”設置為主鍵：在表設計界面中，單擊并拖動左邊的行選定塊，選定sno和cno字段，單擊工具按鈕設置主鍵。如圖6-2所示：圖6-2二、域完整性3.將“ssex”字段設置為只能取“男”，“女”兩值：在表設計界面，點擊圖4－3箭頭所指按鈕，出現(xiàn)屬性(Property)對話框，選擇新建（New）按鈕，然后在約束表達式（Constraintexpression）框中輸入“ssexin('男','女')”。如圖6－3所示。圖6－3三、參照完整性4.將“student”表和“sc”表中的“sno”字段設為參照:打開“sc”表的設計界面,點擊工具欄按鈕,在彈出的屬性(properties)對話框中點擊“新建”按鈕，在“主鍵表(Primarykeytable)”下拉框中選擇“student”表，在其下的字段選擇框中選擇“sno”，在“外鍵表(Foreignkeytable)”下拉框中選擇“sc”表，在其下的字段選擇框中選擇“sno”，單擊關閉即可。見圖6.4。圖6－4命令方式操作方法：一、實體完整性1．將“student”表的“sno”字段設為主鍵：當“student”表已存在則執(zhí)行：altertablestudentaddconstraintpk_snoprimarykey(sno)當“student”表不存在則執(zhí)行:Createtablestudent(snoCHAR(5)primarykey,snameCHAR(10),ssexCHAR(2),sageint,sdeptCHAR(4))注:可用命令“droptablestudent”刪除“student”表2.添加一身份證號字段,設置其惟一性.(注:操作前應刪除表中的所有記錄)Altertablestudentaddidchar(18)unique(id)3.將“sc”表的“sno”和“cno”設置為主鍵：當“sc”表已存在則執(zhí)行：altertablescaddconstraintPK_SnoCnoprimarykey(sno,cno)當“sc”表不存在則執(zhí)行：Createtablesc(snoCHAR(5),cnoCHAR(2),grade INTNULL,constraintPK_SnoCnoprimarykey(sno,cno))二、域完整性4.將“ssex”字段設置為只能取“男”，“女”兩值：當“student”表已存在則執(zhí)行：altertablestudentaddconstraintCK_Sexcheck(ssexin('男','女'))當“student”表不存在則執(zhí)行：Createtablestudent(snoCHAR(5)primarykey,snameCHAR(10),ssexCHAR(2)check(ssexin('男','女')),sageint,sdeptCHAR(4))5.設置學號字段只能輸入數(shù)字:altertablestudentaddconstraintCK_Sno_Formatcheck(snolike'[0-9][0-9][0-9][0-9][0-9]')6.設置身份證號的輸入格式:altertablestudentaddconstraintCK_ID_Formatcheck((idlike'[0-9][0-9][0-9][0-9][0-9][0-9][1-2][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]_')OR(idlike'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-1][0-9][0-3][0-9][0-9][0-9][0-9]'))7.設置18位身份證號的第7位到第10位為合法的年份(1900-2050)altertablestudentaddconstraintCK_ID_Format2check(notlen(id)=18or((convert(smallint,substring(id,7,4))>=1900)and(convert(smallint,substring(id,7,4))<=2050)))三、參照完整性9.設置男生的年齡必須大于22,女生的年齡必須大于20.AltertablestudentaddconstraintCK_agecheck(sex='男'andsage>=22orsex='女'andsage>=20)10.將“student”表和“sc”表中的“sno”字段設為參照:當“sc”表已存在則執(zhí)行：altertablescaddconstraintFP_snoforeignkey(sno)referencesstudent(sno)當“sc”表不存在則執(zhí)行：Createtablesc(snoCHAR(5)constraintFP_snoforeignkeyreferencesstudent(sno),cnoCHAR(2),grade INTNULL,constraintPK_SnoCnoprimarykey(sno,cno))四、完整性驗證1．實體完整性：在“student”表數(shù)據(jù)瀏覽可視化界面中輸入學號相同的兩條記錄將會出現(xiàn)錯誤如下圖所示：圖6－5或者在命令窗口輸入下面兩條命令也會出現(xiàn)錯誤提示：insertintostudentvalues('95001','張三','男',20,'CS')insertintostudentvalues('95001','李四','女',18,'CS')圖6－6下面的語句用來驗證“sc”表中的實體完整性：insertintoscvalues('95002','10',65)insertintoscvalues('95002','10',90)圖6－72.域完整性:使用下面的語句驗證“ssex”字段的域完整性：insertintostudentvalues('95009','張勻','大',20,'CS')3．參照完整性：使用下面的語句“驗證”sc表中的“sno”字段的域完整性(假設student表中沒有學號為“95998”的學生記錄)：insertintoscvalues('98998','10',98)思考:1．建立課程的實體完整性,和課程號cno的參照完整性;2．建立年齡的域完整性,約束條件為“年齡在15到30歲之間”3*.在學生表中添加“出生日期”和“身份證號”字段，設置一完整性規(guī)則，確保身份證號中的關于出生日期的數(shù)字與“出生日期”字段的值相匹配。altertablestudentaddconstraintCK_ID_Format3check(len(id)=18andconvert(char(8),birthdate,112)=substring(id,7,8)orlen(id)=15andconvert(char(8),birthdate,12)=substring(id,7,6))

實驗八*：數(shù)據(jù)庫備份和恢復實驗實驗目的：了解數(shù)據(jù)備份和恢復機制，掌握其方法。實驗內容：1、用企業(yè)管理器創(chuàng)建一個備份設備，并利用該備份設備對數(shù)據(jù)庫執(zhí)行備份。2、為數(shù)據(jù)庫設置一個備份計劃，要求每當CPU空閑時進行數(shù)據(jù)庫備份，并且要求每月1號進行數(shù)據(jù)庫備份。3、在企業(yè)管理器中恢復數(shù)據(jù)庫。實驗步驟：1、（1）用企業(yè)管理器創(chuàng)建一個備份設備。如下圖7-1所示，展開左側目錄樹至“管理工具”－“備份(backup)”,然后在右側空白區(qū)域單擊右鍵，從彈出菜單中選擇“新建備份設備”(NewBackupDevice)。圖7-1然后在備份設備屬性對話框的名稱中輸入“XSCJBakOnDiskD”,在“文件名(Filename)”編輯框中輸入“D:\XSCJ_Bak_on_Disk_D.BAK”。單擊確定即建立了備份設備。如圖7－2所示。圖7-2(2)將數(shù)據(jù)庫XSCJ備份于剛才所建立的備份設備上。在“管理工具－備份”所對應的右側的空白區(qū)域中單擊右鍵選擇“備份數(shù)據(jù)庫(BackupaDatabase)”，打開如圖7－3所示界面：在“常規(guī)(gerneral)”選項卡的“數(shù)據(jù)庫”下拉列表中選擇XSCJ數(shù)據(jù)庫?！皞浞?Backup)”選項選擇“數(shù)據(jù)庫－完全”（Database-complete）。在“目的”(Destination)選項中單擊“添加”(Add…)按鈕，彈出圖7－3下面的對話框。選擇“備份設備”(Backupdevice),并選擇剛才所建立的備份設備。單擊OK。再單擊“確定”按鈕即完成數(shù)據(jù)庫的備份操作。圖7－32、為數(shù)據(jù)庫設置一個備份計劃，要求每當CPU空閑時進行數(shù)據(jù)庫備份，并且要求每月1號進行數(shù)據(jù)庫備份。展開目錄樹至“管理工具－SQLSERVER代理－作業(yè)(jobs)”，在右側的空白區(qū)域單擊右鍵選擇“新建作業(yè)”(Newjob...),如下圖7－4所示。圖7－4在彈出的窗口中的“常規(guī)”(general)選項卡中，“名稱”(Name)框填寫“XSCJ數(shù)據(jù)庫備份計劃”，如圖7－5所示。圖7－5切換到第二個選項卡“步驟”(Steps),點擊“新建”(new)按鈕，在彈出的窗口的“常規(guī)”選項卡的“步驟名稱”(stepname)中輸入“執(zhí)行備份操作”，“類型”選擇“Transact-SQLScript(TSQL)”，在“命令”(command)輸入框中輸入“backupdatabaseXSCJto[XSCJBakOnDiskD]”，點擊“分析”(parse)按鈕檢查命令是否正確。然后先后點擊“應用”(Apply)和“OK”按鈕關閉本窗口。圖7－6在“新作業(yè)屬性”窗口，選擇“調度”(schedules)選項卡。點擊“新建調度”按鈕，在彈出的“新建作業(yè)調度”(NewJobSchedule)窗口中的“名稱”(Name)框輸入“CPU空閑時執(zhí)行備份”，“調度類型”（ScheduleType）設為“每當CPU空閑時啟動”(StartwhenevertheCPU(s)becomeidle)。如圖7－7所示。單擊OK按鈕返回“新作業(yè)屬性”窗口。圖7－7在“新作業(yè)屬性”窗口，再點擊“新建調度”按鈕，在彈出的“新建作業(yè)調度”(NewJobSchedule)窗口中的“名稱”(Name)框輸入“每月1日執(zhí)行備份”，“調度類型”（ScheduleType）設為“反復出現(xiàn)”(Recurring)。如圖7－8所示。圖7－8然后點擊“更改”（Change）按鈕，打開“編輯反復出現(xiàn)的作業(yè)調度”(EditRecurringJobSchedule)窗口，按圖7－9所示設置為每月的一號圖7－9點擊OK返回“新作業(yè)調度”(NewJobSchedule)窗口，再單擊OK按鈕返回“新作業(yè)屬性”窗口。再先后點擊“應用”，“確定”按鈕關閉“新作業(yè)屬性”窗口。3、在企業(yè)管理器中恢復數(shù)據(jù)庫。在“工具”(tools)菜單下選擇“還原數(shù)據(jù)庫”(RestoreDatabase…)，出現(xiàn)“還原數(shù)據(jù)庫”（RestoreDatabase）窗口，在“常規(guī)”(General)選項卡的“還原為數(shù)據(jù)庫”(Restoreasdatabase)選擇或輸入“XSCJ”，“還原”(Restore)項設為“數(shù)據(jù)庫”（Database）在“顯示數(shù)據(jù)庫備份”(Showbackupsofdatabase)項選擇“XSCJ”，在“要還原的第一個備份”項下選擇最近的一個。如圖7－10所示。然后單擊“確定”，即完成數(shù)據(jù)庫的恢復。圖7－10

實驗九：使用ASP訪問SQLServer數(shù)據(jù)庫實驗目的：學會使用ADO訪問SQLSERVER學會ASP操作和使用SQLSERVER數(shù)據(jù)庫實驗內容：使用ADO訪問xscj數(shù)據(jù)庫；使用ASP技術實現(xiàn)xscj數(shù)據(jù)庫中記錄集的分頁顯示和查詢使用ASP技術實現(xiàn)數(shù)據(jù)庫表記錄的添加、修改和刪除功能實驗步驟使用ADO訪問xscj數(shù)據(jù)庫：在D:盤創(chuàng)建一個xscj文件夾，使用iis將該文件夾配置為默認站點下的虛擬目錄啟動DREAMWEAVER8.0，使用站點管理工具管理定義站點xscj,將網(wǎng)頁文件存儲在d:\xscj文件夾下；建立一個conn.inc文件，建立與數(shù)據(jù)庫XSCJ的連接，具體代碼如下：<%dimconnsetconn=Server.CreateObject("adodb.connection")vider="sqloledb;"conn.connectionstring="server=(local);database=xscj;uid=sa;pwd=;"conn.open%>（4）建立與數(shù)據(jù)庫xscj的連接，返回xs表中所有的記錄，在瀏覽器中以表格形式進行分頁顯示。設計兩個ASP文件。文件showstudent.asp用于執(zhí)行對XS表的查詢，將得到的結果集進行分頁顯示。文件showstudent.asp代碼如下：<!--#includefile="showonepage.asp"--><!--#includefile="inc\adovbs.inc"--><!--#includefile="inc\conn.inc"--><linkhref="main.css"type="text/css"rel="stylesheet"><scriptsrc="control.js"language="JavaScript"></script><%dimcmd,rssetrs=Server.CreateObject("adodb.recordset")str="selectxs.xh學號,xm姓名,性別=casewhenxb=1then'男'whenxb=0then'女'end,cssj出生時間,zy專業(yè),zxf總學分,bz備注fromxs"rs.cursortype=adOpenStaticrs.locktype=1rs.pagesize=10rs.openstr,connifrs.eof=falsethenpage=cint(request("page"))ifpage<1thenpage=1ifpage>rs.pagecountthenpage=rs.pagecountShowOnePage"學生基本信息",rs,pageendif%><html><body><formname="form1"method="post"action="showStudent.asp"><palign="center"><%ifpage<>1thenresponse.Write("<ahref=showStudent.asp?page=1>第一頁</a>")response.Write("   ")response.Write("<ahref=showStudent.asp?page="&(1)&">上一個</a>")endififpage<>rs.pagecountthenresponse.Write("   ")response.Write("<ahref=showStudent.asp?page="&(page+1)&">下一個</a>")response.Write("   ")response.Write("<ahref=showStudent.asp?page="&(rs.pagecount)&">最后一個</a>")endif%>請輸入頁碼：<label><inputname="page"type="text"id="page"size="6"></label>頁碼：<fontcolor="#FF0000"><%=page%>/<%=rs.pagecount%></font></p></form><%rs.closeconn.closesetrs=nothingsetconn=nothing%></body></html>文件showonepage實現(xiàn)對記錄集的分頁顯示，代碼如下：<ScriptLanguage="JavaScript">functioncheck(sno,cno){if(confirm("你確定要刪除這條記錄嗎?")){location.href="deletescore.asp?sno="+sno+"&cno="+cno}}functioncheck1(sno){if(confirm("你確定要刪除這條記錄嗎?")){location.href="deleteStudent.asp?sno="+sno}}</Script><%subShowOnePage(header,rs,page)dimtitle,tbheadertbheader=cstr(header)title="<center><fontsize=5color=#3300cc>"&tbheader&"</font></center>"response.writetitlestopresponse.write"<center><tableborder='1'cellpadding='0'cellspacing='0'style='border-collapse:collapse'bordercolor='#000000'>"response.Write"<tr>"fori=0tors.fields.count-1response.Write"<tdbgcolor=##2266dd>"&rs.fields(i).name&"</td>"nextresponse.Write"<td>查看</td>"response.Write"<td>編輯</td>"response.Write"<td>添加</td>"response.Write"<td>刪除</td>"response.Write"</tr>"rs.absolutepage=pageforthepage=1tors.pagesizeresponse.write"<tronMouseOver='moveRow(this)'onClick='clickRow(this)'>"fori=0tors.fields.count-1response.write"<td>"&rs.fields(i).value&"</td>"nextresponse.Write"<td><ahref=showStuDetails.asp?xh="&rs("學號")&">"&_"查看</a></td>"response.Write"<td><ahref=stuEdit.asp?xh="&rs("學號")&">"&_"編輯</a></td>"response.Write"<td><ahref='stuRegister.html'>添加</a></td>"%><td><ahref="javascript:check1('<%=rs("學號")%>');"><fontcolor=darkblue>刪除</font></a></td><%response.write"</tr>"rs.movenextifrs.eofthenexitfornextresponse.Write"</table></center>"endsubsubShowScoreOnePage(header,rs,page)dimtitle,tbheadertbheader=cstr(header)title="<center><fontsize=5color=#3300cc>"&tbheader&"</font></center>"response.writetitleresponse.write"<center><tableborder='1'cellpadding='0'cellspacing='0'style='border-collapse:collapse'bordercolor='#000000'>"fori=0tors.fields.count-2response.Write"<td>"&rs.fields(i).name&"</td>"nextrs.absolutepage=1forthepage=1tors.pagesizeresponse.write"<tronMouseOver='moveRow(this)'onClick='clickRow(this)'>"fori=0tors.fields.count-2response.write"<td>"&rs.fields(i).value&"</td>"nextresponse.Write"<td><ahref='#'>查看</a></td>"response.Write"<td><ahref=updatescore.asp?xh="&rs("學號")&"&kch="&rs("kch")&">編輯</a></td>"response.Write"<td><ahref=insertscore.asp?xh="&rs("學號")&">添加</a></td>"%><td><ahref="javascript:check('<%=rs("學號")%>','<%=rs("kch")%>')"><fontcolor=darkblue>刪除</font></a></td><%response.write"</tr>"rs.movenextifrs.eofthenexitfornextresponse.Write"</table></center>"endsub%>建立如下的表單，實現(xiàn)對學生信息的錄入：建立學生信息錄入表單的處理程序DealStuRegister.asp，代碼如下：<!--#includefile="inc/conn.inc"--><!--#includefile="inc/adovbs.inc"--><%dimxh,xm,xb,sscj,zy,zxf,bzdimcmdsetcmd=Server.CreateObject("mand")xh=request.Form("xh")xm=request.Form("xm")xb=cint(request.Form("xb"))cssj=request.Form("cssj")zy=request.Form("zy")zxf=cint(request.Form("zxf"))bz=request.Form("bz")mandtype=adCmdTmandtext="insertintoxs(xh,xm,xb,cssj,zy,zxf,bz)values('"&xh&"','"&xm&"',"&xb&",'"&cssj&"',"&"'"&zy&"',"&zxf&",'"&bz&"')"response.Writemandtextcmd.activeconnection=conncmd.executeresponse.Redirect("showStudent.asp")%>建立學生信息編輯的處理程序stuEdit.asp和dealStuEdit.asptuEdit.asp代碼如下：<htmlxmlns="/1999/xhtml"><linkhref="main.css"type="text/css"rel="stylesheet"><head><!--#includefile="inc/adovbs.inc"--><!--#includefile="inc/conn.inc"--><%dimcmd,rs,xhxh=request.QueryString("xh")setcmd=Server.CreateObject("mand")setrs=Server.CreateObject("adodb.recordset")mandtype=adCmdTmandtext="select*fromxswherexh='"&xh&"'"cmd.activeconnection=connsetrs=cmd.executeif(rs.eof)thenresponse.Endendif%><metahttp-equiv="Content-Type"content="text/html;charset=gb2312"/><title>無標題文檔</title></head><body><scriptlanguage="JavaScript"type="text/javascript">functioncheckForm(){varxh,xm;varmsg;xh=document.form1.xh.value;xm=document.form1.xm.value;msg="";if(xh=="")msg+="學號不能為空\n";if(xm=="") msg+="姓名不能為空\n"; if(msg=="") returntrue; else { window.alert(msg); returnfalse; }}</script><formid="form1"name="form1"method="post"action="dealStuEdit.asp"><tablewidth="364"border="0"align="center"><captionalign="top">學生信息編輯</caption><tr><td>學號</td><td><label><inputname="xh"type="text"readonly="true"value=<%=rs("xh")%>class="newInput"id="xh"size="12"maxlength="6"/>*</label></td></tr><tr><td>姓名</td><td><inputname="xm"type="text"value=<%=rs("xm")%>class="newInput"id="xm"size="12"maxlength="8"/>*</td