数据库实验指导书2012

实验一数据库管理实验名称数据库管理实验日期使用设备硬件：电脑一台软件：Windows、SQLserver2000实验地点实验目的1、 主要使用SQL语言建立、修改与删除数据库。2、 会使用企业管理器对数据库进行建立、修改和删除。准备工作：每个学生首先在D盘建立一个以自己学号为名字的文件夹，今后所有操作均在自己的文件夹中进行。1、使用CREATEDATABASE创建一个Student数据库，所有参数均取默认值。createdatabasestudent实验内容2、 创建一个Studentl数据库，该数据库的主文件逻辑名称为student1_data，物理文件名为student1.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为student1_log，物理文件名为student1.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB实验内容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、 创建一个指定多个数据文件和日志文件的数据库。该数据库的名称为students，有1个10MB和20MB的数据和2个10MB的事务日志文件。数据文件逻辑名称为students1和students2，物理文件名为students1.mdf和students2.ndf，由primary指定，两个数据文件的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。事务日志文件逻辑名称为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、利用企业管理器向数据库student1可添加一个文件组，其中包括两个数据文件：它们的逻辑名称为student1_data1和student1_data2，物理文件名为student1_1.ndf和student1_2.ndf，初始大小为5MB，最大尺寸为50MB，增长速度为2MB。并将其设为默认文件组。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进行修改。将事务日志文件的大小增加到15MB，将数据文件studentsl和students2分别增加到15MB和30MB。同时增加两个文件组datal和data2，分别包含一个数据文件，逻辑文件名为students3和students4，物理文件名为students3.ndf和students4.ndf，它们的初始大小为20MB，最大尺寸无限大，增长速度15%；增加一个10MB事务日志文件，最大尺寸无限制，增长速度为10%。ALTERDATABASEstudentsMODIFYFILE(NAME='students2',SIZE=15MB)GOEXECUTEsp_helpdbstudentsALTERDATABASEstudentsMODIFYFILE(NAME='students2',SIZE=30MB)GOEXECUTEsp_helpdbstudentsALTERDATABASEstudents3addfilegroupdata1filegroupgoALTERDATABASEstudents3 --添加数据文件加入新文件组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、使用企业管理器查看student1的基本信息；件组和文件信息。使用SQL语言查看students中所有文EXECUTEsp_helpdbstudents!7、使用企业管理器删除student；使用SQL语言同时删除student1和students中所有文件组和文件信息。DROPDATABASEstudentlDROPDATABASEstudents实验二表的管理与使用实验名称表的管理与使用实验日期使用设备硬件：电脑一台软件：Windows、实验名称表的管理与使用实验日期使用设备硬件：电脑一台软件：Windows、SQLserver2000实验地点实验目的1、 主要使用SQL语言定义、删除与修改基本表。2、 会使用企业管理器对基本表进行建立、修改和删除。在自己的文件夹建立student数据库，并在此数据库中建立如下的表和插入数据:关系StudentsSnoSnameSsexSageSdeptS01王建平男21自动化S02刘华女19自动化S03范林军女18计算机S04李伟男19数学S05黄河男18数学S06长江男20数学CnoCnamePre_CnoCreditsC01英语4C02数据结构C052C03数据库C022C04DB_设计C033C05C++3C06网络原理C073C07操作系统C053关系CoursesSnoCnoGradeS01C0192S01C0384S02C0190S02C0294S02C0382S03C0172S03C0290S04C0375关系Reports1、建立学生表Students，每个属性名的意义为Sno-学号、Sname-姓名、Ssex-性别、Sage-年龄、Sdept-所在系。并用insert语句插入6条数据。createdatabasestudentUsestudentCREATETABLEstudents(snochar(5)notnull,实验内容snamevarchar(50)notnull,ssexChar(4)notnull,sagechar(50)notnull,sdeptvarchar(50)notnull,实验内容)insertstudents(sno,sname,ssex,sage,sdept)VALUES('s01','王建平','男','21','自动化')insertstudentsVALUES('s02'，刘华','女','19','自动化')insertstudentsVALUES('s03','范林军','女','18','计算机')insertstudentsVALUES('s04','李伟','男','19','数学')insertstudentsVALUES('s05','黄河','男','18','数学')insertstudentsVALUES('s06','长江','男','20','数学')2、 建立表3.4所示的课程表Courses,其属性名意义分别为Cno-课程号,Cname-课程名,PreCno-先修课程号,Credits-学分。并用insert语句插入7条数据。UsestudentCREATETABLECourses(cnochar(5)notnull,cnamevarchar(50)notnull,pro_cnoChar(4)notnull,creditschar(50)notnull,)insertCoursescnocnamepro_cno,creditsVALUES('c01','英语'，''，'4')insertCoursesVALUES('c02','数据结构'，'c05','2')insertCoursesVALUES('c03','数据库','c02','2')insertCoursesVALUES('c04','DB设计,，'c03','3')insertCoursesVALUES('c05','C++','','3')insertCoursesVALUES('c06','网络原理'，'c07','3')insertCoursesVALUES('c07','操作系统'，'c05','3')3、 建立成绩表Reports。其中的属性名意义分别为Sno-学号，Cno-课程号和Grade-考试成绩。并用insert语句插入8条数据。

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,数据类型为DATETIME型。USEstudentALTERTABLEstudentsaddSentrancedateDATETIME5、将Sage(年龄)的数据类型改为SMALLINT型。USEstudentALTERTABLEstudentsaltercolumnsageSMALLINT6、删除Students表。USEstudentdeletestudents7、将学习成绩的兀组(‘S01’，’C01’)添加到基本表Reports中。USEstudentinsertReports(sno_学号cno_课程号,Grade_考试成绩VALUES('s01','c01','')

实验三数据简单查询实验名称数据简单查询实验日期使用设备硬件：电脑一台软件：Windows、SQLserver2000实验地点实验目的1、 掌握查询语句的一般格式。2、 掌握无条件、有条件查询及查询结果排序与分组。实验内容1、查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。SELECT snamesnosdeptFROMstudentdbostudents2、查询全体学生的姓名(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、查询自动化系、数学和计算机系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。SELECTsnosnamessexfromstudentdbostudentswheresdept='自动化'orsdept='数学'orsdept='计算机'6、查询选修了C03号课程的学生的学号(Sno)和成绩(Grade)，并按成绩降序排列。SELECTsnoGradefromstudentdboReportswherecno='c03'orderbyGradedesc7、查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。SELECT*fromstudentdbo.studentsorderbysdeptasc,sagedesc8、查询选修了课程的学生人数SELECT选修课人数=count(cname)fromstudentdbo.Courses9、计算选修C01号课程的学生平均成绩，最高分数，最低分数。SELECTavgGrade)as学生平均成绩，maxGrade)as最高分min(Grade)as最低分fromstudent.dbo.Reportswherecnolike'c01%'10、求各个课程号(Cno)及相应的选课人数。SELECTcno选课人数=count(*)fromstudent.dboReportsgroupbycno11、查询选修了3门或3门以上课程的学生学号(Sno)。SELECTsnofromstudent.dboReportsgroupbysnohavingcount(*)>=3

实验名称数据简单查询实验日期使用设备硬件：电脑一台软件：Windows、SQLserver2000实验地点实验目的1、 掌握sql连接、嵌套和集合查询语句的一般格式和各种使用方法。2、 掌握sql数据插入、修改和删除语句的一般格式和使用方法。1、查询每个学生及其选修课程的情况。实验四数据查询与更新实验四数据查询与更新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=内4、查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。selectsname,sdept,snofromstudentdbostudentswheresdept=(selectsdeptfromstudentdbo.studentswheresname:'李伟')容5、查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。selectsname,studentssnofromstudentdbo.studentsjoinstudentdbo.Reportsonstudents.snoReports.snojoinstudent.dbo.CoursesonRoCowherecname='数据结构'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、将数学系所有学生的成绩置零。updateReportssetGrade0fromstudentdbostudentswhere Reportssno=students.snoandsdept='数学'11、删除学号为“S04”的学生选修的课号为“C02”的记录。usestudentdeleteReportswheresno='s04'andcno='c02'12、删除所有学生的选课记录。usestudentdeleteReports13、删除数学系所有学生的选课记录。usestudentdeleteReportsfromstudentdbo.Reportsjoinstudentdbo.studentsonsdept='数学'whereReports.snostudents.sno

实验五索引与视图实验名称索引与视图实验日期使用设备硬件：电脑一台软件：Windows、SQLserver2000实验地点实验目的1、 掌握sql建立索引的一种方法，即在基本表中建立和用命令方式建立；掌握删除索引的方法。2、 掌握sql视图建立、修改和删除。实验内容1、为student数据库中的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、建立数学系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为Sno,Sname，Sage，Sdept。usestudentgocreateviewc_studentasselectsno,sname,sage,sdeptfrom student,dbo.studentswheresdept='数学'withcheckoption5、建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图。本视图由三个基本表的连接操作导出。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、 在数学系的学生视图C_Student中找出年龄(Sage)小于20岁的学生姓名(Sname)和年龄(Sage)。usestudentselectsname,sagefromc_studentwheresage209、在Student_CR视图中查询成绩在85分以上的学生学号(Sno)、姓名(Sname)和课程名称(Cname)。

usestudentselectsname,sno,cnamefromstudent_CRwhereGrade>8510、将数学系学生视图C_Student中学号为S05的学生姓名改为“黄海”。updatestudentdbo.c_studentsetsname='黄海'wheresno='s05'11、向数学系学生视图C_Student中插入一个新的学生记录，其中学号为“S09”，姓名为“王海”，年龄为20岁。insertstudentdbo.c_studentvalues（'s03','王海'，'20','数学'）12、删除数学系学生视图C_Student中学号为“S09”的记录。deletestudentdbo.c_studentwheresno='s09'

实验六存储过程和触发器、安全管理实验名称存储过程和触发器、安全管理实验日期使用设备硬件：电脑一台软件：Windows、SQLserver2000实验地点实验目的1、 主要使用SQL语言对存储过程和触发器进仃创建、显示与删除。2、 会使用企业管理器对存储过程和触发器进行创建、显示与删除。3、 主要使用SQL语言对SQLserver中数据库进仃安全管理。实验内容1、根据Sno创建一个存储过程Sno_prog，用于显示学生学号和姓名。并执仃此过程。usestudentIFexists(SELECTnameFROMsysobjectsWHEREname='sno_prog'andtype='p')DROPproceduresno_progGOCREATEproceduresno_progASSELECTsnosnameFROMstudentdbostudentsGOEXECUTEsno_prog2、创建一个存储过程add_prog,用于添加学生表Students的学生记录。IFexists(SELECTnameFROMsysobjectsWHEREname='add_prog'andtype='p')DROPprocedureadd_progGOCREATEprocedureadd_progASinsertstudent.dbo.studentsvalues('','','','','')3、创建一个存储过程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上创建一个触发器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上创建一个触发器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表上创建一个触发器“成绩插入”、“成绩删除”、“成绩更新”触发器，当用户插入、删除、更新记录时触发。usestudentIFexists(SELECTnameFROMsysobjectsWHEREname='成绩插入'andtype='tr')DROPtrigger成绩插入GOCREATEtrigger成绩插入。口ReportsafterupdateASinsertstudent.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、删除触发器“成绩删除”。

Droptrigger成绩删除10、创建一个名为“计算机”密码为“1234”，默认数据库为“student”的登录帐户。sp_addlogin'计算机'，'1234','student','us_english'11、将登录帐户“计算机”的密码由“1234”改为“OK”。默认数据库由“student”改为“Pubs”数据库。executesp_password'1234','ok','计算机'executesp_defaultdb'计算机','pubs'12、删除登录帐户“计算机”。executesp_droplogin'计算机'13、创建一个名字和密码都为自己学号，默认数据库为“student”登录帐户。而后将该登录帐户设置为加入到sysadmin角色中。executesp_addlogin'201002520119','201002520119','student','us_english'executesp_addsrvrolemember'201002520119','sysadmin'14、在“student”数据库中建立新的角色yyc和managers0executesp_addrole'yyc'executesp_addrole'managers'15、给public角色授予SELECT权限，并将UPDATA、DELETE权限授予用户yyc和managers,使这些用户拥有对数据库“student”下的“Students”、“Courses”、“Reports”表的相应权限。grantSELECTtopublicgrantupdate,deletetoyycmanagers

实验七数据完整性实验名称数据完整性（4课时）实验日期使用设备硬件：电脑一台软件：Windows、SQLserver2000实验地点实验目的1、 主要使用SQL语言对约束、规则和默认值进行定义、删除与修改。2、 会使用企业管理器对约束、规则和默认值进行建立、修改和删除。在实验—的基础上对student数据库的表Students、Course