數(shù)據(jù)庫實驗指導(dǎo)書2012

實驗一數(shù)據(jù)庫管理實驗名稱數(shù)據(jù)庫管理實驗日期使用設(shè)備硬件：電腦一臺軟件：Windows、SQLserver2000實驗地點實驗?zāi)康?、 主要使用SQL語言建立、修改與刪除數(shù)據(jù)庫。2、 會使用企業(yè)管理器對數(shù)據(jù)庫進行建立、修改和刪除。準備工作：每個學生首先在D盤建立一個以自己學號為名字的文件夾，今后所有操作均在自己的文件夾中進行。1、使用CREATEDATABASE創(chuàng)建一個Student數(shù)據(jù)庫，所有參數(shù)均取默認值。createdatabasestudent實驗內(nèi)容2、 創(chuàng)建一個Studentl數(shù)據(jù)庫，該數(shù)據(jù)庫的主文件邏輯名稱為student1_data，物理文件名為student1.mdf，初始大小為10MB，最大尺寸為無限大，增長速度為10%；數(shù)據(jù)庫的日志文件邏輯名稱為student1_log，物理文件名為student1.ldf，初始大小為1MB，最大尺寸為5MB，增長速度為1MB實驗內(nèi)容CREATEDATABASEstudent1ON(NAME=student1_data,FILENAME='C:\DATA\student1.mdf,SIZE=10MB,MAXSIZE=unlimited,FILEGROWTH=10%)LOGON(NAME=student1_log,FILENAME='D:\DATA\student1_log.LDF',SIZE=1MB,MAXSIZE=5MB,FILEGROWTH=1MB)3、 創(chuàng)建一個指定多個數(shù)據(jù)文件和日志文件的數(shù)據(jù)庫。該數(shù)據(jù)庫的名稱為students，有1個10MB和20MB的數(shù)據(jù)和2個10MB的事務(wù)日志文件。數(shù)據(jù)文件邏輯名稱為students1和students2，物理文件名為students1.mdf和students2.ndf，由primary指定，兩個數(shù)據(jù)文件的最大尺寸分別為無限大和100MB，增長速度分別為10%和1MB。事務(wù)日志文件邏輯名稱為studentslog1和studentslog2，物理文件名為studentslog1.ldf和studentslog2.ldf，最大尺寸為50MB，增長速度為1MB。

CREATEDATABASEstudentson(NAME=students!,FILENAME='C:\DATA\students1.mdf,SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%),(NAME=students2,FILENAME='D:\DATA\students2.ndf,SIZE=20MB,MAXSIZE=100MB,FILEGROWTH=1MB)LOGON(NAME=studentslog1,FILENAME='D:\DATA\studentslog1.LDF',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=1MB),(NAME=studentslog1,FILENAME='D:\DATA\studentslog2.LDF',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=1MB)4、利用企業(yè)管理器向數(shù)據(jù)庫student1可添加一個文件組，其中包括兩個數(shù)據(jù)文件：它們的邏輯名稱為student1_data1和student1_data2，物理文件名為student1_1.ndf和student1_2.ndf，初始大小為5MB，最大尺寸為50MB，增長速度為2MB。并將其設(shè)為默認文件組。ALTERDATABASEstudent1addfilegroupstudent1filegroupg。ALTERDATABASEstudent1addfile(name=student1_data1,Filename='D:\DATA\student1_1.ndf,SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=2MB)tofilegroupstudent1filegroupgoALTERDATABASEstudent1addfile(name=student1_data2,Filename='D:\DATA\student1_2.ndf,SIZE=5MB,MAXSIZE=50MB,FILEGROWTH=2MB)tofilegroupstudent1filegroupgoEXECUTEsphelpdbstudent15、利用SQL語言對students進行修改。將事務(wù)日志文件的大小增加到15MB，將數(shù)據(jù)文件studentsl和students2分別增加到15MB和30MB。同時增加兩個文件組datal和data2，分別包含一個數(shù)據(jù)文件，邏輯文件名為students3和students4，物理文件名為students3.ndf和students4.ndf，它們的初始大小為20MB，最大尺寸無限大，增長速度15%；增加一個10MB事務(wù)日志文件，最大尺寸無限制，增長速度為10%。ALTERDATABASEstudentsMODIFYFILE(NAME='students2',SIZE=15MB)GOEXECUTEsp_helpdbstudentsALTERDATABASEstudentsMODIFYFILE(NAME='students2',SIZE=30MB)GOEXECUTEsp_helpdbstudentsALTERDATABASEstudents3addfilegroupdata1filegroupgoALTERDATABASEstudents3 --添加數(shù)據(jù)文件加入新文件組addfile(name=students3,Filename='D:\DATA\students3.ndf,SIZE=20MB,MAXSIZE=UNLIMITED,FILEGROWTH=15%)tofilegroupstudents3filegroupALTERDATABASEstudentsaddfilegroupdata2filegroupg。ALTERDATABASEstudentsaddfile(name=students4,Filename='D:\DATA\students4.ndf,SIZE=20MB,MAXSIZE^UNLIMITED,FILEGROWTH=15%)tofilegroupstudents4filegroupALTERDATABASEstudentsaddfilegroupstudentslog05filegroupgoALTERDATABASEstudentsaddfile(name=studentslog05,Filename='D:\DATA\studentslog05.ldf,SIZE=10MB,MAXSIZE^UNLIMITED,FILEGROWTH=10%)tofilegroupstudentslog05filegroup6、使用企業(yè)管理器查看student1的基本信息；件組和文件信息。使用SQL語言查看students中所有文EXECUTEsp_helpdbstudents!7、使用企業(yè)管理器刪除student；使用SQL語言同時刪除student1和students中所有文件組和文件信息。DROPDATABASEstudentlDROPDATABASEstudents實驗二表的管理與使用實驗名稱表的管理與使用實驗日期使用設(shè)備硬件：電腦一臺軟件：Windows、實驗名稱表的管理與使用實驗日期使用設(shè)備硬件：電腦一臺軟件：Windows、SQLserver2000實驗地點實驗?zāi)康?、 主要使用SQL語言定義、刪除與修改基本表。2、 會使用企業(yè)管理器對基本表進行建立、修改和刪除。在自己的文件夾建立student數(shù)據(jù)庫，并在此數(shù)據(jù)庫中建立如下的表和插入數(shù)據(jù):關(guān)系StudentsSnoSnameSsexSageSdeptS01王建平男21自動化S02劉華女19自動化S03范林軍女18計算機S04李偉男19數(shù)學S05黃河男18數(shù)學S06長江男20數(shù)學CnoCnamePre_CnoCreditsC01英語4C02數(shù)據(jù)結(jié)構(gòu)C052C03數(shù)據(jù)庫C022C04DB_設(shè)計C033C05C++3C06網(wǎng)絡(luò)原理C073C07操作系統(tǒng)C053關(guān)系CoursesSnoCnoGradeS01C0192S01C0384S02C0190S02C0294S02C0382S03C0172S03C0290S04C0375關(guān)系Reports1、建立學生表Students，每個屬性名的意義為Sno-學號、Sname-姓名、Ssex-性別、Sage-年齡、Sdept-所在系。并用insert語句插入6條數(shù)據(jù)。createdatabasestudentUsestudentCREATETABLEstudents(snochar(5)notnull,實驗內(nèi)容snamevarchar(50)notnull,ssexChar(4)notnull,sagechar(50)notnull,sdeptvarchar(50)notnull,實驗內(nèi)容)insertstudents(sno,sname,ssex,sage,sdept)VALUES('s01','王建平','男','21','自動化')insertstudentsVALUES('s02'，劉華','女','19','自動化')insertstudentsVALUES('s03','范林軍','女','18','計算機')insertstudentsVALUES('s04','李偉','男','19','數(shù)學')insertstudentsVALUES('s05','黃河','男','18','數(shù)學')insertstudentsVALUES('s06','長江','男','20','數(shù)學')2、 建立表3.4所示的課程表Courses,其屬性名意義分別為Cno-課程號,Cname-課程名,PreCno-先修課程號,Credits-學分。并用insert語句插入7條數(shù)據(jù)。UsestudentCREATETABLECourses(cnochar(5)notnull,cnamevarchar(50)notnull,pro_cnoChar(4)notnull,creditschar(50)notnull,)insertCoursescnocnamepro_cno,creditsVALUES('c01','英語'，''，'4')insertCoursesVALUES('c02','數(shù)據(jù)結(jié)構(gòu)'，'c05','2')insertCoursesVALUES('c03','數(shù)據(jù)庫','c02','2')insertCoursesVALUES('c04','DB設(shè)計,，'c03','3')insertCoursesVALUES('c05','C++','','3')insertCoursesVALUES('c06','網(wǎng)絡(luò)原理'，'c07','3')insertCoursesVALUES('c07','操作系統(tǒng)'，'c05','3')3、 建立成績表Reports。其中的屬性名意義分別為Sno-學號，Cno-課程號和Grade-考試成績。并用insert語句插入8條數(shù)據(jù)。

UsestudentCREATETABLEReports(snochar10)notnull,cnochar(5)notnull,Gradeintnotnull,)insertReports(snocno,Grade)VALUES('s01','c01','92,)insertReportsVALUES('s01','c03','84,)insertReportsVALUES('s02','c01','90,)insertReportsVALUES('s02','c02','94,)insertReportsVALUES('s02','c03','82')insertReportsVALUES('s03','c01','72,)insertReportsVALUES('s03','c02','90')insertReportsVALUES('s04','c03','75')4、向基本表Students中增加“入學時間”屬性列，其屬性名為Sentrancedate,數(shù)據(jù)類型為DATETIME型。USEstudentALTERTABLEstudentsaddSentrancedateDATETIME5、將Sage(年齡)的數(shù)據(jù)類型改為SMALLINT型。USEstudentALTERTABLEstudentsaltercolumnsageSMALLINT6、刪除Students表。USEstudentdeletestudents7、將學習成績的兀組(‘S01’，’C01’)添加到基本表Reports中。USEstudentinsertReports(sno_學號cno_課程號,Grade_考試成績VALUES('s01','c01','')

實驗三數(shù)據(jù)簡單查詢實驗名稱數(shù)據(jù)簡單查詢實驗日期使用設(shè)備硬件：電腦一臺軟件：Windows、SQLserver2000實驗地點實驗?zāi)康?、 掌握查詢語句的一般格式。2、 掌握無條件、有條件查詢及查詢結(jié)果排序與分組。實驗內(nèi)容1、查詢?nèi)w學生的姓名(Sname)、學號(Sno)、所在系(Sdept)。SELECT snamesnosdeptFROMstudentdbostudents2、查詢?nèi)w學生的姓名(Sname)、出生年份及學號(Sno)。SELECT sname,出生年份=year(getdate())-sageFROMstudent.dbo.students3、查詢所有年齡在18~22歲(包括18歲和22歲)之間的學生姓名(Sname)及年齡(Sage)。SELECTsname,sagefromstudentdbo.studentswheresage>=18andsage<=224、查詢所有不姓劉的學生姓名(Sname)和年齡(Sage)。SELECTsname,sagefromstudentdbostudentswheresnamenotlike'劉％，5、查詢自動化系、數(shù)學和計算機系學生的學號(Sno)、姓名(Sname)和性別(Ssex)。SELECTsnosnamessexfromstudentdbostudentswheresdept='自動化'orsdept='數(shù)學'orsdept='計算機'6、查詢選修了C03號課程的學生的學號(Sno)和成績(Grade)，并按成績降序排列。SELECTsnoGradefromstudentdboReportswherecno='c03'orderbyGradedesc7、查詢?nèi)w學生情況，查詢結(jié)果按所在系的系名(Sdpet)升序排列，同一系中的學生按年齡(Sage)降序排列。SELECT*fromstudentdbo.studentsorderbysdeptasc,sagedesc8、查詢選修了課程的學生人數(shù)SELECT選修課人數(shù)=count(cname)fromstudentdbo.Courses9、計算選修C01號課程的學生平均成績，最高分數(shù)，最低分數(shù)。SELECTavgGrade)as學生平均成績，maxGrade)as最高分min(Grade)as最低分fromstudent.dbo.Reportswherecnolike'c01%'10、求各個課程號(Cno)及相應(yīng)的選課人數(shù)。SELECTcno選課人數(shù)=count(*)fromstudent.dboReportsgroupbycno11、查詢選修了3門或3門以上課程的學生學號(Sno)。SELECTsnofromstudent.dboReportsgroupbysnohavingcount(*)>=3

實驗名稱數(shù)據(jù)簡單查詢實驗日期使用設(shè)備硬件：電腦一臺軟件：Windows、SQLserver2000實驗地點實驗?zāi)康?、 掌握sql連接、嵌套和集合查詢語句的一般格式和各種使用方法。2、 掌握sql數(shù)據(jù)插入、修改和刪除語句的一般格式和使用方法。1、查詢每個學生及其選修課程的情況。實驗四數(shù)據(jù)查詢與更新實驗四數(shù)據(jù)查詢與更新SELECTstudents.sname,選修的課程=Camefromstudent.dbo.studentsjoinstudent.dbo.Reportsonstudents.sno=Reports.snojoinstudent.dbo.CoursesonCo=Ro2、查詢每個學生的學號(Sno)、姓名(Sname)、選修的課程名(Cname)及成績(Grade)。實SELECT學號=studentssno姓名students.sname,課程成績=ReportsGrade選修的課程Camefromstudent.dbostudentsjoinstudent.dbo.Reportsonstudents.sno=Reportssnojoinstudentdbo.CoursesonCo=Reportscno3、查詢選修了編號為“C02”的課程的學生姓名(Sname)和所在系(Sdept)。驗selectsname,sdeptfromstudentdbo.studentsjoinstudentdbo.Reportsonstudents.snoReports.snowhereReportscno=內(nèi)4、查詢與“李偉”在同一個系學習的學生學號(Sno)、姓名(Sname)和系名(Sdept)。selectsname,sdept,snofromstudentdbostudentswheresdept=(selectsdeptfromstudentdbo.studentswheresname:'李偉')容5、查詢選修了課程名為“數(shù)據(jù)結(jié)構(gòu)”的學生學號(Sno)和姓名(Sname)。selectsname,studentssnofromstudentdbo.studentsjoinstudentdbo.Reportsonstudents.snoReports.snojoinstudent.dbo.CoursesonRoCowherecname='數(shù)據(jù)結(jié)構(gòu)'6、查詢所有選修了編號為“C01”課程的學生姓名(Sname)和所在系(Sdept)。

selectsname,sdeptfromstudentdbo.studentsjoinstudentdbo.Reportsonstudents.snoReports.snowhereReportscno='c01'7、查詢計算機科學系的學生或年齡不大于20歲的學生信息。select*fromstudentdbo.studentswheresdept='計算機科學系'orsage<=208、將學號為“S03”的學生年齡改為22歲。updatestudentssetsage22wheresno='s03'9、將所有學生的年齡增加1歲。updatestudentssetsagesage110、將數(shù)學系所有學生的成績置零。updateReportssetGrade0fromstudentdbostudentswhere Reportssno=students.snoandsdept='數(shù)學'11、刪除學號為“S04”的學生選修的課號為“C02”的記錄。usestudentdeleteReportswheresno='s04'andcno='c02'12、刪除所有學生的選課記錄。usestudentdeleteReports13、刪除數(shù)學系所有學生的選課記錄。usestudentdeleteReportsfromstudentdbo.Reportsjoinstudentdbo.studentsonsdept='數(shù)學'whereReports.snostudents.sno

實驗五索引與視圖實驗名稱索引與視圖實驗日期使用設(shè)備硬件：電腦一臺軟件：Windows、SQLserver2000實驗地點實驗?zāi)康?、 掌握sql建立索引的一種方法，即在基本表中建立和用命令方式建立；掌握刪除索引的方法。2、 掌握sql視圖建立、修改和刪除。實驗內(nèi)容1、為student數(shù)據(jù)庫中的Students,Courses,Reports二個表建立索引。其中Students表按Sno（學號）升序建立唯一索引，Courses表按Cno（課程號）升序建立唯一索引，Reports表按Sno（學號）升序和Cno（課程號）號降序建立唯一索引。usestudentcreateuniqueclusteredindexpk_snoonstudentdbostudents(snoasc)usestudentcreateuniqueclusteredindexc_cnoonstudentdboCourses(cnoasc)usestudentcreateuniqueclusteredindexRep_SCnoonstudentdboReports(snocnodesc)2、在基本表Students的Sname（姓名）和Sno（學號）列上建立一個聚簇索引，而且Students中的物理記錄將按照Sname值和Sno值的升序存放。usestudentcreateuniqueclusteredindexpk_studentsonstudentdbostudents(snoasc,snameasc)usestudent3、刪除基本表Reports上的Rep_SCno索引。usestudentdropindexReports.Rep_SCno4、建立數(shù)學系學生的視圖，并要求進行修改和插入操作時仍需保證該視圖只有數(shù)學系的學生，視圖的屬性名為Sno,Sname，Sage，Sdept。usestudentgocreateviewc_studentasselectsno,sname,sage,sdeptfrom student,dbo.studentswheresdept='數(shù)學'withcheckoption5、建立學生的學號(Sno)、姓名(Sname)、選修課程名(Cname)及成績(Grade)的視圖。本視圖由三個基本表的連接操作導(dǎo)出。usestudentgocreateviewstudent_CRasselectstudents.sno,sname,cname,Gradefromstudent.dbo.students,student.dbo.Courses,student.dbo.Reportswherestudents.sno=Reports.snoandRo=Co6、 定義一個反映學生出生年份的視圖。usestudentgocreateviews_sage(sno,sname,sageasselectsno,sname,year(getdate())-sagefromstudent,dbo.students7、 刪除視圖StudentCR。usestudentdropviewstudent_CR8、 在數(shù)學系的學生視圖C_Student中找出年齡(Sage)小于20歲的學生姓名(Sname)和年齡(Sage)。usestudentselectsname,sagefromc_studentwheresage209、在Student_CR視圖中查詢成績在85分以上的學生學號(Sno)、姓名(Sname)和課程名稱(Cname)。

usestudentselectsname,sno,cnamefromstudent_CRwhereGrade>8510、將數(shù)學系學生視圖C_Student中學號為S05的學生姓名改為“黃?！?。updatestudentdbo.c_studentsetsname='黃海'wheresno='s05'11、向數(shù)學系學生視圖C_Student中插入一個新的學生記錄，其中學號為“S09”，姓名為“王?！保挲g為20歲。insertstudentdbo.c_studentvalues（'s03','王海'，'20','數(shù)學'）12、刪除數(shù)學系學生視圖C_Student中學號為“S09”的記錄。deletestudentdbo.c_studentwheresno='s09'

實驗六存儲過程和觸發(fā)器、安全管理實驗名稱存儲過程和觸發(fā)器、安全管理實驗日期使用設(shè)備硬件：電腦一臺軟件：Windows、SQLserver2000實驗地點實驗?zāi)康?、 主要使用SQL語言對存儲過程和觸發(fā)器進仃創(chuàng)建、顯示與刪除。2、 會使用企業(yè)管理器對存儲過程和觸發(fā)器進行創(chuàng)建、顯示與刪除。3、 主要使用SQL語言對SQLserver中數(shù)據(jù)庫進仃安全管理。實驗內(nèi)容1、根據(jù)Sno創(chuàng)建一個存儲過程Sno_prog，用于顯示學生學號和姓名。并執(zhí)仃此過程。usestudentIFexists(SELECTnameFROMsysobjectsWHEREname='sno_prog'andtype='p')DROPproceduresno_progGOCREATEproceduresno_progASSELECTsnosnameFROMstudentdbostudentsGOEXECUTEsno_prog2、創(chuàng)建一個存儲過程add_prog,用于添加學生表Students的學生記錄。IFexists(SELECTnameFROMsysobjectsWHEREname='add_prog'andtype='p')DROPprocedureadd_progGOCREATEprocedureadd_progASinsertstudent.dbo.studentsvalues('','','','','')3、創(chuàng)建一個存儲過程dele_prog,刪除Courses中指定Cno的記錄。IFexists(SELECTnameFROMsysobjectsWHEREname='dele_prog'andtype='p')DROPproceduredele_progGOCREATEproceduredele_prog@學號char(5)ASdeleteFROMstudent.dbo.Courseswherecno=@學號4、顯示存儲過程add_prog。usestudentEXECUTEsp_helptextadd_prog5、 刪除存儲過程Snoprog。usestudentdropproceduresno_prog6、 在表Reports上創(chuàng)建一個觸發(fā)器Sno_update,當Stuents表更改Sno時同步更改Reports中的Sno。usestudentIFexists(SELECTnameFROMsysobjectsWHEREname='sno_update'andtype='tr')DROPtriggersno_updateGOCREATETRIGGERsno_updateONstudentsafterupdateASDECLARE@xh1char(5),@xh2char(5)SELECT@xh1=snoFROMdeletedSELECT@xh2=snoFROMinsertedUPDATEReportsSETsno=@xh2WHEREsno=@xh1GO7、 在表Reports上創(chuàng)建一個觸發(fā)器Cno_update，當Courses表更改Cno時同步更改Reports中的Cno。usestudentIFexists(SELECTnameFROMsysobjectsWHEREname='cno_update'andtype='tr')DROPtriggercno_updateGOCREATETRIGGERcno_updateONCoursesafterupdateASDECLARE@xh1char(5),@xh2char(5)SELECT@xh1=cnoFROMdeletedSELECT@xh2=cnoFROMinsertedUPDATEReportsSETcno=@xh2WHEREcno=@xh1GO8、在Reports表上創(chuàng)建一個觸發(fā)器“成績插入”、“成績刪除”、“成績更新”觸發(fā)器，當用戶插入、刪除、更新記錄時觸發(fā)。usestudentIFexists(SELECTnameFROMsysobjectsWHEREname='成績插入'andtype='tr')DROPtrigger成績插入GOCREATEtrigger成績插入?？赗eportsafterupdateASinsertstudent.dbo.Reportsvalues('','','')usestudentIFexists(SELECTnameFROMsysobjectsWHEREname='成績刪除'andtype='tr')DROPtrigger成績刪除GOCREATEtrigger成績刪除onReportsafterupdateASdelete student,dbo.Reportswheresno=''usestudentIFexists(SELECTnameFROMsysobjectsWHEREname='成績更新'andtype='tr')DROPtrigger成績更新GOCREATEtrigger成績更新onReportsafterupdateASDECLARE@xh1char(5),@xh2char(5)SELECT@xh1=snoFROMdeletedSELECT@xh2=snoFROMinsertedUPDATEReportsSETsno=@xh2WHEREsno=@xh1GO9、刪除觸發(fā)器“成績刪除”。

Droptrigger成績刪除10、創(chuàng)建一個名為“計算機”密碼為“1234”，默認數(shù)據(jù)庫為“student”的登錄帳戶。sp_addlogin'計算機'，'1234','student','us_english'11、將登錄帳戶“計算機”的密碼由“1234”改為“OK”。默認數(shù)據(jù)庫由“student”改為“Pubs”數(shù)據(jù)庫。executesp_password'1234','ok','計算機'executesp_defaultdb'計算機','pubs'12、刪除登錄帳戶“計算機”。executesp_droplogin'計算機'13、創(chuàng)建一個名字和密碼都為自己學號，默認數(shù)據(jù)庫為“student”登錄帳戶。而后將該登錄帳戶設(shè)置為加入到sysadmin角色中。executesp_addlogin'201002520119','201002520119','student','us_english'executesp_addsrvrolemember'201002520119','sysadmin'14、在“student”數(shù)據(jù)庫中建立新的角色yyc和managers0executesp_addrole'yyc'executesp_addrole'managers'15、給public角色授予SELECT權(quán)限，并將UPDATA、DELETE權(quán)限授予用戶yyc和managers,使這些用戶擁有對數(shù)據(jù)庫“student”下的“Students”、“Courses”、“Reports”表的相應(yīng)權(quán)限。grantSELECTtopublicgrantupdate,deletetoyycmanagers

實驗七數(shù)據(jù)完整性實驗名稱數(shù)據(jù)完整性（4課時）實驗日期使用設(shè)備硬件：電腦一臺軟件：Windows、SQLserver2000實驗地點實驗?zāi)康?、 主要使用SQL語言對約束、規(guī)則和默認值進行定義、刪除與修改。2、 會使用企業(yè)管理器對約束、規(guī)則和默認值進行建立、修改和刪除。在實驗—的基礎(chǔ)上對student數(shù)據(jù)庫的表Students、Course