数据库原理实验报告

实验一实验项目名称：SQLServer2000系统的配置及基本操作实验主要内容及方法熟悉实验环境实验主要内容及方法熟悉实验环境实验要求：1、了解SQLServer2000的启动，熟悉如何在SQLServer2000图形环境下建立数据库和表。2、了解SQLServer2000系统的相关服务及常用工具。实验设备：装有SQLSERVER2000的电脑实验步骤：启动和停止SQLServera.用服务管理器启动和停止SQLServer选择菜单“程序|MicrosoftSQLServer|服务管理器”。b.自动启动SQLServer使用手动启动弹出“SQLServer服务管理器”后，选择复选框“当启动OS时自动启动服务”后，下次开机时系统即可自动启动SQLServer。c.用操作系统控制面板中的服务管理程序启动和停止SQLServer择菜单“程序|管理工具|服务”，弹出“服务”对话框。鼠标右击要启动的服务器名称，即：MSSQLSERVER，弹出菜单后，选择“启动”菜单项，就可启动SQLServer；类似的方法，可以停止SQLServer。2、使用企业管理器a.启动企业管理器选择菜单“程序|MicrosoftSQLServer|服务管理器”。b.启动和停止SQLServer在企业管理器中所要启动的服务器上点鼠标右击，从快捷菜单中选择“连接”，即可启动并连接；类似的方法，可以停止SQLServer。c.浏览master和pubs数据库的结构使用查询分析器a.启动查询分析器选择“程序|MicrosoftSQLServer|查询分析器”菜单后，弹出“连接到SQLServer服务器”对话框。用户输入SQLServer服务器的名称和身份验证后，即可进入查询分析器。注：身份验证可以使用“Windows身份验证”或“SQLServer身份验证”。b.从表中查询数据：首先选择当前使用的数据库为pubs，然后在查询分析器的查询窗口中输入SQL语句Select*fromtitles点击按钮，执行该SQL语句，观察执行结果。执行结果：（服务器:消息208，级别16，状态1，行1；对象名'pubs'无效。）c.练习T-SQL中的函数如求200的平方根在查询分析器的查询窗口中输入selectsqrt(200)go观察执行结果。执行结果：（14.142135623730951）实验总结：1、了解SQLServer2000的启动。2、了解SQLServer2000系统的相关服务及常用工具。实验二实验项目名称sql数据定义语言的应用实验主要内容及方法数据库、数据表、视图、索引的定义、删除及修改实验要求：解并掌握SQL查询分析器及企业管理器的使用。握数据库、基本表、索引的定义、删除与修改。实验目的：熟练掌握库、表的定义、删除与修改，为后继学习作准备。实验设备：装有SQLSERVER2000的电脑实验步骤：表scSnoCnoGrade表scSnoCnoGradeS01S01S02S02S02S03S03S04…C01C03C01C02C03C01C02C03…9284909482729075…表CoursesCnoCnamePre_CnoCreditsC01C02C03C04C05C06C07英语数据结构数据库DB_设计C++网络原理操作系统C05C02C03C07C054223333表StudentsSnoSnameSsexSageSdeptS01S02S03S04S05S06S07S08S09S10王平刘华范君李伟黄河长江文涛杨丽欧浩刘洪男女女男男男男女男男21191819182017182119信息信息计算机计算机数学数学信息历史中文历史表中数据可以自行给定。一、定义数据库1.用企业管理器创建数据库创建一个名称为Test的数据库，数据文件的初始大小设为5MB，文件增长增量设为2MB，文件增长方式设为自动增长，文件的增长上限设为50MB；日志文件的初始大小为10MB，文件增长增量为1MB，文件的增长限制设为100MB。具体创建步骤如下：1）进入SQLServer企业管理器2）展开目录树，在“数据库”选项上单击右键，选择“新建数据库”。该窗口有3个选项卡：常规、数据文件和事务日志。输入数据库名称：Test，点击数据文件选项卡，在数据文件中，你可以设定数据文件的名称，所在目录以及文件的大小等信息。完成后点击事务日志选项卡,事务日志中，你可以设定日志文件的名称，所在目录以及文件的大小等信息。接受系统的缺省值，点击确定按钮，完成数据库的新建。2.用T-SQL语言创建数据库CREATEDATABASEdatabase_name其他配置可以采用默认设置。[练习]：按以上要求，在查询分析器中用T-SQL语言创建数据库“Test”，写出SQL语句。Createdatabasetest执行结果：CREATEDATABASE进程正在磁盘'test'上分配0.75MB的空间。CREATEDATABASE进程正在磁盘'test_log'上分配0.49MB的空间。3.查看并修改数据库的属性（1）进入企业管理器，鼠标右键单击数据库“Test”，选择“属性”，查看选项。将数据库Test数据文件的初始大小改为20MB，最大值改为80MB，数据增长改为5％。二、定义基本表例1建立表1.1所示的学生表Students，每个属性名的意义为Sno-学号、Sname-姓名、Ssex-性别、Sage-年龄、Sdept-所在系。这里要求Sno和Sname不能为空值，且取值唯一。CREATETABLEStudents/*列级完整性约束条件*/(SnoCHAR(5)NOTNULL,/*Sno不能为空值*/SnameCHAR(20)NOTNULL,/*Sname不能为空值*/SsexCHAR(2),SageINT,SdeptCHAR(15),CONSTRAINTun_SnoUNIQUE(Sno),/*Sno取值唯一的约束*/CONSTRAINTun_SnameUNIQUE(Sname));/*Sname取值唯一的约束*/ 在查询分析器中输入以上代码，点击执行按扭，即可得到空表Students.说明：在MicrosoftSQLServer2000的查询分析器(QueryAnalyzer)中使用单条SQL语句，其末尾不需要分号“；”作为命令结尾标记。通常，SQLServer2000对大多数末尾带有分号的SQL命令都能顺利执行，但对少数的SQL命令，末尾若带分号，则SQLServer2000会给出错误信息提示。例2建立表1.2所示的课程表Courses，其属性名意义分别为Cno-课程号,Cname-课程名,Pre_Cno-先修课程号,Credits-学分。CREATETABLECourses(CnoCHAR(5)NOTNULL,/*Cno不能为空值*/CnameCHAR(20)NOTNULL,/*Cname不能为空值*/Pre_CnoCHAR(5),CreditsINT,CONSTRAINTun_CnoUNIQUE(Cno));/*Cno取值唯一的约束*/例3建立表1.3所示的成绩表Sc。其中的属性名意义分别为Sno-学号，Cno-课程号和Grade-考试成绩。CREATETABLESc(SnoCHAR(5)NOTNULL,/*Sno不能为空值*/CnoCHAR(5)NOTNULL,/*Cno不能为空值*/GradeINT,CONSTRAINTSno_CnoUNIQUE(Sno,Cno));/*Sno+Cno取值唯一的约束*/三、修改基本表例4向基本表Students中增加“入学时间”属性列，其属性名为Sentrancedate，数据类型为DATETIME型。ALTERTABLEStudentsADDSentrancedateDATETIME例5将Sage(年龄)的数据类型改为SMALLINT型。ALTERTABLEStudentsALTERCOLUMNSageSMALLINT例6删除Sname(姓名)必须取唯一值的约束。ALTERTABLEStudentsDROPCONSTRAINTun_SnameA注意：SQLServer2000增加了删除属性的命令。比如，删除属性列Sentrancedate的命令为：LTERTABLEStudentsDROPCOLUMNSentrancedate;说明：=1\*GB2⑴为了保证后面例子能够顺利运行，请大家一定将属性列Sentrancedate从Students表中删除。=2\*GB2⑵为了调试SQL语句方便，这里没有在表Sc中增加参照完整性约束，甚至没有定义主键。四、删除基本表例7删除Students表。DROPTABLEStudents;说明：此表删除后，请立即用例1将其建立起来，以便后面的例子使用。五、建立索引例8为学生选课数据库中的Students，Courses，Sc三个表建立索引。其中Students表按Sno(学号)升序建唯一索引，Courses表按Cno(课程号)升序建唯一索引，Sc表按Sno(学号)升序和Cno(课程号)号降序建唯一索引。其语句为：CREATEUNIQUEINDEXStu_SnoONStudents(Sno);CREATEUNIQUEINDEXCou_CnoONCourses(Cno);CREATEUNIQUEINDEXRep_ScnoONSc(SnoASC,CnoDESC);例9在基本表Students的Sname(姓名)和Sno(学号)列上建立一个聚簇索引，而且Students中的物理记录将按照Sname值和Sno值的升序存放。其语句为：CREATECLUSTEREDINDEXStu_Sname_SnoONStudents(Sname,Sno);六、删除索引例10删除基本表Sc上的Rep_SCno索引。DROPINDEXSc.Rep_Scno;七、建立视图例11建立数学系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有数学系的学生，视图的属性名为Sno，Sname，Sage，Sdept。CREATEVIEWC_StudentASSELECTSno,Sname,Sage,SdeptFROMStudentsWHERESdept=’数学’WITHCHECKOPTION例12建立学生的学号(Sno)、姓名(Sname)、选修课程名(Cname)及成绩(Grade)的视图。本视图由三个基本表的连接操作导出，其SQL语句如下：CREATEVIEWStudent_CRASSELECTStudents.Sno,Sname,Cname,GradeFROMStudents,Sc,CoursesWHEREStudents.Sno=Sc.SnoANDSc.Cno=Courses.Cno例13定义一个反映学生出生年份的视图。CREATEVIEWStudent_birth(Sno,Sname,Sbirth)ASSELECTSno,Sname,1996-SageFROMStudents八、删除视图例14删除视图Student_CR。DROPVIEWStudent_CR;

实验三实验项目名称sql数据操纵语言的应用(6课时，综合)实验主要内容及方法数据的各种操纵方法实验要求：掌握sql数据插入、修改、删除和查询语句的一般格式。(2)掌握sql数据插入、修改、删除和查询使用方法。实验目的：熟练掌握四大语句的使用。实验设备：装有SQLSERVER2000的电脑实验步骤：一、插入数据1）采用insert语句将实验二给定的表格中数据插入数据库。表一：INSERTINTOStudentsVALUES('S01','王平','男','21','信息')INSERTINTOStudentsVALUES('S02','刘华','女','19','信息')INSERTINTOStudentsVALUES('S03','范君','女','18','计算机')INSERTINTOStudentsVALUES('S04','李伟','男','19','计算机')INSERTINTOStudentsVALUES('S05','黄河','男','18','数学')INSERTINTOStudentsVALUES('S06','长江','男','20','数学')INSERTINTOStudentsVALUES('S07','文涛','男','17','信息')INSERTINTOStudentsVALUES('S08','杨丽','女','18','历史')INSERTINTOStudentsVALUES('S09','欧浩','男','21','中文')INSERTINTOStudentsVALUES('S10','刘洪','男','19','历史')表二：insertintoCourses(Cno,Cname,Credits)VALUES('C01','英语',4)insertintoCoursesVALUES('C02','数据结构','C05',2)insertintoCoursesVALUES('C03','数据库','C02',2)insertintoCoursesVALUES('C04','DB_设计','C03',3)insertintoCourses(Cno,Cname,Credits)VALUES('C05','C++',3)insertintoCoursesVALUES('C06','网络原理','C07',3)insertintoCoursesVALUES('C07','操作系统','C05',3)表三：INSERTINTOScVALUES('S01','C01',92)INSERTINTOScVALUES('S01','C03',84)INSERTINTOScVALUES('S02','C01',90)INSERTINTOScVALUES('S02','C02',94)INSERTINTOScVALUES('S02','C03',82)INSERTINTOScVALUES('S03','C01',72)INSERTINTOScVALUES('S03','C02',90)INSERTINTOScVALUES('S04','C03',75) 在数据库中定义一个关系History_Student，其关系模式与Students完全一样，试将关系Students中的所有元组插入到关系History_Student中去。建立History_Student表createtableHistory_Student(SnoCHAR(5)NOTNULL,/*Sno不能为空值*/SnameCHAR(20)NOTNULL,/*Sname不能为空值*/SsexCHAR(2),SageINT,SdeptCHAR(15),）;查询Students,并将查询结果插入History_Student表。insertintoHistory_Student(Sno,Sname,Ssex,Sage,Sdept)selectSno,Sname,Ssex,Sage,SdeptfromStudents二、修改数据1）将学号为“S03”的学生年龄改为22岁，即要修改满足条件的一个元组的属性值。updateStudentssetSage=22whereSno='S03'2）将所有学生的年龄增加1岁。即要修改多个元组的值。updateStudentssetSage=Sage+13）将数学系所有学生的成绩置零。注意：由于学生所在系的信息在Students表中，而学习成绩在Sc表中，因此，可以将SELECT子查询作为WHERE子句的条件表达式。updateScsetgrade=0where'数学'=(selectSdeptfromStudentswhereStudents.Sno=Sc.Sno)三、删除数据1）删除学号为“S04”的学生选修的课号为“C02”的记录。deletefromscwheresno='s04'andcno='c02'2）删除所有学生的选课记录。deletefromsc3）删除数学系所有学生的选课记录。deletefromscwhere'数学'=(selectsdeptfromstudentswherestudents.sno=sc.sno)四、查询数据1、无条件查询1）查询全体学生的详细记录select*fromstudents2）查询全体学生的姓名(Sname)、学号(Sno)、所在系(Sdept)。selectsname,sno,sdeptfromstudents3）查询全体学生的姓名(Sname)、出生年份及学号(Sno)。提示：由于SELECT子句的<目标列表达式>不仅可以是表中的属性列，也可以是表达式，故可以查询经过计算的值。selectsname,2010-sagefromstudents4）查询全体学生的姓名、出生年份和学号，要求用小写字母表示学号中的字母。selectsname,2010-sage,lower(sno)fromstudents2、条件查询1)查询数学系全体学生的学号(Sno)和姓名(Sname)。selectsno,snamefromstudentswheresdept='数学'2）查询所有年龄在18-22岁(包括18岁和22岁)之间的学生姓名(Sname)及年龄(Sage)。selectsname,sagefromstudentswhere18<=sageandsage<=223)查询年龄不在18-22岁之间的学生姓名(Sname)及年龄(Sage)。selectsname,sagefromstudentswheresagenotbetween18and224)查询自动化系、数学和计算机系学生的学号(Sno)、姓名(Sname)和性别(Ssex)。selectsno,sname,ssexfromstudentswheresdeptin('自动化系','数学','计算机')5)查询既不是信息系、数学系、也不是计算机系的学生的姓名(Sname)和性别(Ssex)。selectsname,ssexfromstudentswheresdeptnotin('信息','数学','计算机')6)查询所有姓刘的学生的姓名(Sname)、学号(Sno)和性别(Ssex)。selectsname,sno,ssexfromstudentswheresnamelike'刘%'7)查询课程名为“DB_设计”的课程号(Cno)和学分(Credits)。selectcno,creditsfromcourseswherecname='DB_设计'8)假设某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。试查询缺少成绩的学生的学号(Sno)和相应的课程号(Cno)。selectsnocnofromscwheregradeisnull9)查询所有有成绩的学生学号(Sno)和课程号(Cno)。selectsno,cnofromscwheregradeisnotnull3、查询结果排序1)查询选修了C03号课程的学生的学号(Sno)和成绩(Grade)，并按成绩降序排列。selectsno,gradefromscwherecno='c03'orderbygradedesc2)查询全体学生情况，查询结果按所在系的系名(Sdpet)升序排列，同一系中的学生按年龄(Sage)降序排列。select*fromstudentsorderbysdept,sagedesc4、集函数的使用1)查询学生总人数。selectcount(*)fromstudents2)查询选修了课程的学生人数。selectcount(distinctsno)fromsc3)计算选修C01号课程的学生平均成绩。selectavg(grade)fromscwherecno='c01'4)查询选修C01号课程的学生最高分数。selectmax(grade)fromscwherecno='c01'5、查询结果分组1)求各个课程号(Cno)及相应的选课人数。SELECTCno,COUNT(Sno)FROMScGROUPBYCno;2)查询选修了3门或3门以上课程的学生学号(Sno)。selectsnofromscgroupbysnohavingcount(*)>=36、不同表之间的连接查询1)查询每个学生及其选修课程的情况。提示：本查询实际上是涉及Students与Sc两个表的连接操作。这两个表之间的联系是通过公共属性Sno实现的。selectstudents.*,sc.*fromstudents,scwherestudents.sno=sc.sno2）查询每个学生的学号(Sno)、姓名(Sname)、选修的课程名(Cname)及成绩(Grade)。selectstudents.sno,sname,cname,gradefromstudents,sc,courseswherestudents.sno=sc.snoando=o7、自身连接1）查询每一门课的间接先修课(即先修课的先修课)。在Courses表关系中，只有每门课的直接先修课信息，而没有先修课的先修课。要得到这个信息，必须先对一门课找到其先修课，再按此先修课的课程号，查找它的先修课程。这就需要要将Courses表与其自身连接。为方便连接运算，这里为Courses表取两个别名分别为A，B。SELECTA.cno,A.cname,B.Pre_CnoFROMCoursesA,CoursesBWHEREA.Pre_Cno=B.Cno;8、带谓词IN的嵌套查询1）查询选修了编号为“C02”的课程的学生姓名(Sname)和所在系(Sdept)。selectsname,sdeptfromstudentswheresnoin(selectsnofromscwherecno='c02')2）查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。selectsno,sname,sdeptfromstudentswheresdeptin(selectsdeptfromstudentswheresname='李伟')3）查询选修了课程名为“数据结构”的学生学号(Sno)和姓名(Sname)。selectsno,snamefromstudentswheresnoin(selectsnofromscwherecnoin(selectcnofromcourseswherecname='数据结构'))9、带有比较运算符的嵌套查询1）将查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。改为带有比较运算符的嵌套查询。提示：由于一个学生只可能在一个系学习，因此子查询的结果是一个值，因此可以用=代替IN。selectsno,sname,sdeptfromstudentswheresdept=(selectsdeptfromstudentswheresname='李伟')10、带谓词ANY或ALL的嵌套查询1）查询非自动化系的不超过自动化系所有学生的年龄的学生姓名和年龄。selectsname,sagefromstudentswheresage<all(selectsagefromstudentswheresdept='自动化')andsdept!='自动化'11、带谓词EXISTS的嵌套查询1）查询所有选修了编号为“C01”课程的学生姓名(Sname)和所在系(Sdept)。selectsname,sdeptfromstudentswhereexists(select*fromscwheresno=students.snoandcno='c01')2）查询与“李伟”在同一个系学习的学生学号(Sno)、姓名(Sname)和系名(Sdept)。SELECTSno,Sname,SdeptFROMStudentsAWHEREEXISTS(SELECT*FROMStudentsBWHEREB.Sdept=A.SdeptANDB.Sname='李伟');3）查询选修了所有课程的学生姓名(Sname)和所在系。selectsname,sdeptfromstudentswherenotexists(select*fromcourseswherenotexists(select*fromscwheresno=students.snoandcno=o))实验四实验项目名称sql的数据控制语言及数据库的保护(2课时，验证)实验主要内容及方法数据控制语言，与用户、权限管理实验要求：(1)掌握用户的创建，删除，权限的授予、收回。(2)掌握数据库的备份和还原。实验目的：熟练掌握授权与撤权。实验设备：装有SQLSERVER2000的电脑实验步骤：一、用户管理1.用企业管理器管理SQLServer登录A.增加一个WindowsNT用户或用户组1.选择WindowsNT用户或用户组2.设置登录用户的服务器角色为系统管理员3.设置登录用户可访问的数据库为DBS。一个登录用户可以访问很多个数据库，可以在这里指定，指定后在相应的数据库中建立了相应的数据库用户。4.按下“确定”，即可建立一个WindowsNT用户，然后，可以在企业管理器中检验登录用户是否已经建立。B.增加一个SQLServer用户1.增加一个SQLServer用户，用户名为“lwh”，密码为“lwhpass”。打开WindowsNT/安全性，右击登陆选择新建登陆，输入登陆名，选择SQLSever身份验证，并输入密码2.设置登录用户的常规属性C.修改用户如：修改SQLServer用户lwh的属性。右键点击登陆中的lwh，选择属性，即可修改D.删除用户右键点击登陆中的lwh，选择删除2、用查询分析器管理SQLServer登录A.增加一个WindowsNT用户或用户组如：增加一个WindowsNT用户为“B029\sjxy”，授予登录访问权。execsp_grantlogin'B029\sjxy'B.增加一个SQLServer用户如：增加一个SQLServer用户，用户名为“lwh”，密码为“lwhpass”。execsp_addlogin'lwh','lwhpass'C.删除用户如：删除WindowsNT用户“B029\sjxy”。execsp_revokelogin'B029\sjxy'

如：删除SQLServer用户lwh。3.用企业管理器管理数据库用户A.增加一个数据库用户打开数据库，右键点击“用户”，选择数据库用户。B.删除一个数据库用户右键点击所选择的用户，再选择删除即可。4、用查询分析器管理数据库用户增加一个数据库用户在master中增加用户BUILTIN\Administratorsusemastergoexe