数据库与信息系统基础实验指导书.doc

数据库与信息系统基础实验指导书指导老师：张华中国矿业大学测绘与地理信息工程系2012 年 9 月目 录简 介3实验14实验2 数据库的创建与管理14实验3 表的创建与管理23实验4 数据操纵27实验5 数据查询30实验6 数据库系统开发34简 介一、适用专业地理信息系统，测绘工程二、实验目的与任务通过实验教学，使学生进一步加深理解数据库系统的基本理论，学会数据库设计方法、DBMS的使用，数据库系统的管理和维护，熟悉数据库技术的应用。三、实验配套的主要仪器设备几台（套）数装有WIN2000及SQL SERVER 2000的电脑60台四、主要教材及参考书教材：数据库技术与应用 贺利坚 宇航出版社 参考书目：数据库系统概论（第三版） 萨师煊 高等教育出版社五、考核形式 1、平时实验完成情况 20% 2、分组开发一套数据库管理系统程序 80%六、实验开出率实验开出率100%七、实验项目与要求序号实 验 项 目 名 称实验课学时数仪器组数每组人数实验类型开出要求指导实验人员材料消耗费（元）计划实际1实验1 SQL语言初步22601验证必开教师2实验2 数据库的创建与管理22601验证必开教师3实验3 表的创建与管理22601设计必开教师4实验4 数据操纵22601设计必开教师5实验5 数据查询24601验证必开教师6实验6 数据库系统开发44601设计必开教师789实 验 1实验项目：建立数据库及基本表，数据查询。实验目的：使用SQL查询分析器用CREATE语句创建基本表，使用SQL查询分析器用SELECT语句进行数据查询。步骤：1、利用查询分析器创建学生课程数据库。 用鼠标单击“开始”，将鼠标分别指向程序、microsoft SQL server,在microsoft SQL server下单击“查询分析器”；在“查询分析器”窗口下键入CREATE DATABASE SC命令并保存，文件名为SC.sql 用鼠单击任务栏的绿色向右箭头运行该命令。这样，SC数据库就建成了。如下列图所示步骤 1) 步骤 2) 选择此选项步骤 2)2、利用查询分析器创建基本表student(sno,sname,ssex,sage,sdept)course(cno,cname,ccredit)sc(sno,cno,grade)在查询分析器任务栏的数据库选为SC。在窗体下键入如下命令： CREATE TABLE Student (sno CHAR(10) NOT NULL UNIQUE， sname CHAR(8)， ssex CHAR(2)，sage INT，sdept CHAR(20) 保存，文件名为student.sql.,用鼠单击任务栏的绿色向右箭头运行.在窗体下键入如下命令：CREATE TABLE Course (cno CHAR(10) NOT NULL ,cname CHAR(20)，Ccredit INT)用鼠单击任务栏的绿色向右箭头运行，观察结果。CREATE TABLE SC(sno CHAR(10) NOT NULL ,cno CHAR(10) NOT NULL,grade INT) 保存，文件名为sc.sql,用鼠单击任务栏的绿色向右箭头运行.这样在SC数据库中建成了三个表：Student,Course,SC.3、利用企业管理器向表中输入数据。Student表snosnamessexSageSdept200101张明男19计算机系 200102王强男20信息系 200103李华女18数学系 200104秦永男19计算机系 course表CnoCnameCcreditCno1 数据库 31 2 数学 42 3 信息系统 33 4 操作系统 34 5 数据结构 45 6 PASCAL语言 36 S_c表SnoCnoGrade2001011 922001012 852001013 882001022 902001023 804、使用SQL查询分析器用SELECT语句进行数据查询(1)简单查询例1查询全体学生的姓名、学号、所在系。Select sname,sno,sdeptFROM student本例查询结果为：Sname Sno 张明 200101 计算机系 王强 200102 信息系 李华 200103 数学系秦永 200104 计算机系例2 查询学生的详细记录Select * from student本例查询结果为Sno Sname Sage Ssex Sdept200101 张明 19 男 计算机系200102 王强 18 一男 信息系200103 李华 20 女 数学系200104 秦永 19 男 计算机系 例3 查询学校有哪些系。Select distinct sdeptFrom student；本例查询结果为：Sdept 计算机系数学系例4 查询计算机系全体学生的学号与姓名SELECT sno,snameFrom studentWhere sdept=计算机系本例查询结果为：Sno Sname200101 张明200104 秦永例5 查询选修一号课程且成绩90分以上的学生的学号Select sno From sc Where grade90 and cno=1本例查询结果为：Sno 200101 (2) 连接查询等值与非等值连接查询例6查询每个学生及其选修课程的情况Select student.*,sc.*From student,scWhere student.sno=sc.sno执行结果为：Sno Sname Sage Ssex Sdept Sno20010l 张明 19 男 计算机系 200lOl20010l 张明 19 男 计算机系 20010l200101 张明 19 男 计算机系 20010l200102 王强 18 男 信息系 200102200102 王强 18 男 信息系 200102复合条件连接例7查询每个学生的学号、姓名、选修的课程名及成绩。Select student.sno,sname,cname,gradeFrom student,sc.courseWhere student.sno=sc.snoAnd o=o执行结果为：Sno Sname Cname Grade 200101 张明 数据库 92 200101 张明 数学 85 200101 张明 信息系统 88200102 王强 数学 90200102 王强 信息系统 80例8查询计算机系同学的各科成绩，要求输出姓名、课名和成绩Select sname,cname,gradeFrom student,course,scWhere student.sno=sc.snoAnd o=oAnd student.sdept=计算机系执行结果为：Sname Cname Grade 张明 数据库 92 张明 数学 85 张明 信息系统 88例9查询选修了数学课且成绩及格的所有学生的学号、姓名和成绩Select student.sno,sname,gradeForm student,sc,courseWhere student.sno=sc.snoAnd =oAnd ame= 数学And sc.grade60执行结果为：Sno sname Grade 200101 张明 85 200102 王强 90自身连接例10查询和张明在同一个系的学号和姓名select s1.sno,s1.snamefrom student s1,student s2where s1.sdept=s2.sdeptand s2.sname=张明执行结果为：Sno sname200101张明 200104秦永 外连接如例10的结果中没有200103和200104两个学生的信息，原因在于他们没有选课，在SC表中没有相应的元组。但是有时我们想以Student表为主体列出每个学生的基本情况及其选课情况，当然要反映出学生选修了什么课或是否没有选课。若某个学生没有选课，只输出其基本情况信息，其选课信息为空值即可，这时就需要使用外连接(Outer Join)。可以将例6改写为Select student.sno,sname,ssex,sage,sdept,cno,gradeFrom student,scWhere student.sno*=sc.sno执行结果如下： Sno Sname Ssex Sage Sdept Cno Grade 20010l 张明 男 19 计算机系 1 92 200101 张明 男 19 计算机系 2 85 20010l 张明 男 19 计算机系 3 88 200102 王强 男 18 信息系 2 90 200102 王强 男 18 信息系 3 80 200103 李华 女 20 数学系 NULL NULL 200104 秦永 男 19 计算机系NULL NULL(3) 嵌套查询例11查询与“张明”在同一个系的学生学号与姓名SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 张明)执行结果如下：Sno Sname Sdept200101张明 计算机系 200104秦永 计算机系 例12查询选修了信息系统学生学号与姓名SELECT Sno,Sname FROM Student WHERE SNO IN (SELECT Sno FROM Sc WHERE cno in (select cno From course Where cname=信息系统)执行结果如下：Sno sname200101张明 200102王强 (4) 谓词查询between . and .例13查询选修了2号课程，成绩在80-90之间学生学号SELECT Sno FROM Sc WHERE cno=2and grade between 80 and 90执行结果如下：Sno 200101 200102In 、not in例14 查询数学系和计算机系学生的姓名和性别select sname,ssexfrom studentwhere sdept in(数学系,计算机系 )执行结果为：Sname Ssex 张明 男李华 女秦永 男Like ,not like例15查询学号为200l0l的学生的详细情况。Select *From student Where sno like 200101执行结果为：Sno Sname Sage Ssex Sdept200101 张明 19 男 计算机系例16 查询所有姓李的学生的姓名、学号和性别。Select sname,sno,ssexFrom studentWhere sname like 李%本例查询结果为：Sname Sno Ssex李华 200103 女例17查询名字中第2个字为“明”字的学生的姓名和学号。Select sname,snoFrom studentWhere sname like _明% 本例查询结果为： Sno Sname20010l 张明Is null ,Is not null例18 查询缺少成绩的学生的学号和相应的课程号select sno,cnofrom scwhere grade is null本例查询结果为空值例19 查询所有有成绩的学生学号和课程号select sno,cnofrom scwhere grade is not null本例查询结果为 Sno CnO 20010l l 200101 2 200101 3 200102 2200102 3谓词ANY和ALL及比较运算符例20 查询其他系中比计算机系某一学生年龄小的学生姓名和年龄。 select sname,sagefrom studentwhere sdept计算机系and sageany(select sage from student where sdept=计算机系)执行结果为： Sname Sage 王强 18例21查询其他系中比计算机系所有学生年龄都小的学生姓名及年龄。select sname,sagefrom studentwhere sdept计算机系and sageall(select sage from student where sdept=计算机系)本例执行结果。Sname Sage王强 18谓词EXISTS和NOT EXISTS例22查询所有选修了3号课程的学生姓名select snamefrom studentwhere EXISTS (select* from SC where SNO=STUDENT.SNO AND CNO=3)本例执行结果SNAME张明 王强 例22查询没有选修l号课程的学生姓名。select snamefrom studentwhere not EXISTS (select* from SC where SNO=STUDENT.SNO AND CNO=1) 执行结果为： Sname 王强 李华（5）查询结果排序例23查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列select sno,gradefrom scwhere cno=3order by grade desc执行结果为：Sn0 Grade 200lUl 88200102 80例24查询全体学生情况，查询结果按所在系的系名升序排列，同一系中的学生按年龄降序排列.select *from studentorder by sdept,sage desc执行结果为：sno sname ssex sage sdept200101张明 男19计算机系 200104秦永 男19计算机系 200103李华 女18数学系 200102王强 男20信息系 (6)统计查询例25 查询学生总人数select count(*)from student 执行结果为：4例26 查询选修了课程的学生人数select count(distinct sno)from sc执行结果为：2例27 计算数学课的学生平均成绩select avg(grade)from scwhere cno in(select cnofrom coursewhere cname=数学)执行结果为：87例28 计算数学课的学生最高分select max(grade)from scwhere cno in(select cnofrom coursewhere cname=数学).执行结果为：90例29 计算各门课的平均成绩select cno,avg(grade)from scgroup by cno执行结果为：CNO 无列名1 922 873 84实验2 数据库的创建与管理目的和要求1巩固数据库的基础知识；2掌握创建数据库的两种方法；3掌握查看、修改数据库属性的方法；4掌握缩小、更名与删除数据库的方法。内容和步骤一、建数据库在SQL Server2000 下创建数据库有两种方法：1使用企业管理器直接创建数据库1) 打开企业管理器。2) 左键双击“数据库”后，右键单击空白区并选择【新建数据库】。3) 会弹出窗体如图1-1所示，在常规标签中输入数据库名称。 图1-1 新建数据库4)然后单击【数据文件】标签，可以输入文件名、文件位置、文件大小以及文件组。在这个对话框中还提供了在数据文件需要更多空间时自动扩展的选项以及数据文件大小的最大值。5)单击【事务日志】标签，在此键入日志文件的名字、位置、大小。在这个标签中还提供了让日志文件在日志需要更多空间时自动扩展选项和日志文件大小的最大值。 2使用Transact-SQL语句创建数据库使用Transact-SQL语句创建数据库的语法如下：CREATE DATABASE database_name ONPRIMARY( NAME = logical_file_name, FILENAME = os_file_name ,SIZE = size ,MAXSIZE = max_size|UNLIMITED ,FILEGROWTH = growth_increment),n:=FILEGROUP filegroup_name ,nLOG ON(NAME = logical_file_name,FILENAME = os_file_nameSIZE = size,MAXSIZE = max_size | UNLIMITED,FILEGROWTH = growth_increment)例1 创建一个student数据库，其中主文件组包含主要数据文件student1_dat和次要数据文件student2_dat。有2个次要文件组：studentGroup1包含2个次要数据文件student1Fi1_dat 和 studentFi2_dat, studentGroup2包含student2Fi1_dat和student2Fi2_dat两个次要数据文件。日志的逻辑文件名为student_log，此日志文件存储在c:mssqldatastudentlog.ldf中。CREATE DATABASE studentON PRIMARY(NAME = student1_dat, FILENAME = c:student1_dat.mdf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15%), (NAME = student2_dat, FILENAME = c:student2_dat.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 15%), FILEGROUP studentGroup1 ( NAME = student1Fi1_dat, FILENAME = c:student1Fildt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5) ,( NAME = student1Fi2_dat, FILENAME = c:student2Fildt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5), FILEGROUP studentGroup2(NAME = student2Fil_dat, FILENAME = c:SG1Fi1dt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5),(NAME = student2Fi2_dat, FILENAME = c:SG2Fi1dt.ndf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5)LOG ON (NAME = student_log, FILENAME = c:studentlog.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB)二、查看、修改数据库属性 1利用企业管理器（1）SQL Server2000 有许多选项可以改变数据库的行为。因此，在使用数据库之前，可以考虑其中一些选项的设置。 进入企业管理器，用鼠标右键单击数据库student，再单击“属性”，然后选择“选项”标签即可进入如图1-2所示student数据库的选项窗体。 图1-2 student数据库的选项窗体属性说明如下：访问控制：用于控制哪些用户能访问该数据库。只读：这个选项使数据库只能读取不能写入。模型：指定数据库的恢复模型类型。ANSI NULL默认设置：指定将数据库列默认定义为NULL还是NOT NULL。递归触发器：启用触发器的递归触发。自动更新统计信息：指定在优化期间自动生成查询优化所需的过时统计信息。残缺页检测：指定可以检测不完整页。自动关闭：指定数据库资源被释放以及所有用户退出之后关闭数据库。自动收缩：指定数据库文件可以周期性自动收缩。自动创建统计信息：指定在优化期间自动生成优化查询所需的任何缺少的统计信息。使用引用的标识符：指定SQL Server2000将强制执行关于引号的ANSI规则。级别：指定数据库兼容性级别。2 调整数据库大小点击“数据文件”或“事物日志”标签，可以得到如图1-3所示student数据库的窗体 图1-3 student数据库的数据文件窗体属性说明：文件自动增长：选中表示数据库自动，这时可以选择按兆增长还是按百分比增长。文件增长不受限制：意味着数据库没有最大限制，可以一直充满磁盘将文件增长限制为，为数据库选择一个最大限制。分配空间：用户可以直接增加数据库文件大小扩大数据库的另外一个方法是增加辅助数据文件。3 增加文件组生成一辅助文件后，可将其逻辑组织到文件组中，帮助管理磁盘空间分配。增加文件组的方法是：在文件组列中输入一个新的文件组名称，如“next”，单击“确定”，然后右击数据库，选择“属性”，选择“文件组”标签会看到增加的文件组。如图1-4所示：图1-4 student数据库的文件组窗体 缩小数据文件右击数据库，单击“所有任务”，选择收缩数据库，会弹出图1-5所示窗体，利用该窗体可以输入数据库大小，并且可以定制任务，让SQL Server2000自动定期收缩数据库大小。 图1-5 收缩数据库窗体2使用Transact-SQL语句查看、修改数据库属性使用Transact-SQL语句查看、修改数据库属性的语法为： sp_dboptiondbname = database,optname = option_name,optname = option_name dbname = database 设置指定选项的数据库名称optname = option_name 设置的选项名称optname = option_name 可以示ture、false、on或off 修改日志文件的最大值将student1_log日志文件的最大值更改为150M，SQL脚本程序如下：USE studentGOALTER DATABASE student MODIFY FILE(NAME=student1_log,MAXSIZE=150MB)GO 修改日志文件的初始值将student1_log日志文件的初始值由5M更改为20M，SQL脚本程序如下：USE studentGOALTER DATABASE student MODIFY FILE(NAME=student1_log,SIZE=20MB)GO 查看数据库信息执行系统存储过程sp_helpdb,可以查看student数据库信息，并验证数据库是否已经修改成功。Sp_helpdb student 更改数据库更改数据库student时为student添加一个辅助文件，文件名为studentfz，存储在c: studentfz.ndf中，初始大小为3MB，最大值为50MB，增长量为3MB。USE studentALTER DATABASE studentADD FILE(NAME = studentfz,FILENAME=c:studentfz.ndf,SIZE=3MB,MAXSIZE=50,FILEGROWTH=3)向数据库student中添加一个含有2个文件的文件组USE studentGOALTER DATABASE student ADD FILEGROUP studentfg1GOALTER DATABASE studentADD FILE(NAME=studentfz1, FILENAME=C:studentfz1.ndf, SIZE =5MB, MAXSIZE=50MB, FILEGROWTH =5),(NAME=studentfz2, FILENAME=C:studentfz2.ndf, SIZE =5MB, MAXSIZE=50MB, FILEGROWTH =5) TO FILEGROUP studentfg1向student数据库中添加2个5MB的日志文件USE studentGOALTER DATABASE studentADD LOG FILE(NAME =studentlog2, FILENAME=c:studentlog2.ldf, SIZE =5MB, MAXSIZE = 10MB, FILEGROWTH =5),(NAME =studentlog3, FILENAME=c:studentlog3.ldf, SIZE =5MB, MAXSIZE = 10MB, FILEGROWTH =5)删除数据库student中的一个文件studentfz.ndfUSE studentGOALTER DATABASE student REMOVE FILE studentfz删除student数据库USE studentDROP DATABASE student练习题练习1：创建数据库 用企业管理器创建数据库1) 1) 创建数据库Student：2) 2) 右击数据库，单击【新建数据库】3) 3) 输入数据库名称Student4) 4) 选择【数据文件】标签，增加一个文件student1，设置初始大小为5MB5) 5) 选择【事务日志】标签，增加一个日志文件student1_log，初始大小为5MB6) 6) 单击【确定】按钮，开始创建数据库练习2：用Transact-SQL语句创建数据库打开查询分析器，创建数据库Student1。数据库的参数如下表所示。参数参数值数据库名称Student1数据库逻辑文件名Student1_dat操作系统数据文件名C:mssqldataStudent1_dat.mdf数据文件的初始大小10MB数据文件最大大小50MB数据文件增长量原来的20%日志逻辑文件名Student_log操作系统日志文件名C:mssqldataStudent1_log.ldf日志文件初始大小5MB日志文件的最大大小25MB日志文件增长增量5MB1） 设置数据库选项：使用企业管理器修改数据库选项。右击数据库，单击“属性”，选择“选项”标签，弹出如图所示窗体，选中“只读”复选框。这样数据库就变为只读数据库。然后打开查询分析器使用T-SQL语句更改数据库选项。EXEC SP_DBOPTION student, read only, TRUE2）压缩数据库：使用企业管理器压缩数据库。右击数据库，选择“所有任务”，单击“收缩数据库”，弹出图示窗体。键入收缩量。使用T-SQL语句压缩数据库。打开查询分析器键入DBCC SHRINKDATABASE（student，2）将student数据库压缩成为2M。3）更改数据库：打开查询分析器更改数据库student，参数如下：参数参数值数据库名Student增加的文件组名StudentFG1增加的文件1的逻辑名Studentdat3文件1在磁盘中的目录C:tdat3.ndf文件1的初始大小5MB文件1的最大值50MB文件1的增长量5MB增加的文件2逻辑名Studentdat4文件2磁盘中的目录C:tdat3.ndf文件2初始大小5MB文件2最大值50MB文件2增长量5MB新增日志逻辑文件名Studentlog2日志文件在磁盘中的目录D:studentlog.ldf日志文件初始值5MB日志文件最大值100MB日志文件增长量5MB实验3 表的创建与管理目的和要求1巩固数据库的基础知识；2掌握创建与删除表的两种方法；3掌握修改表结构的方法。内容和步骤一、表的创建1利用企业管理器创建表 1) 打开企业管理器。2) 选中创建好的数据库，单击数据库中的表对象，然后右击窗口右侧选择新建表，弹出如图所示窗体。3) 在这个窗体中，列名列就示表的字段名，可以在这个窗体中为字段选择数据类型和长度以及是否可以为空值。4) 在此处可以为表选择文件组，在表中单击右键，选择属性，如果已经为数据库创建了一个次要组，可以在属性窗体的表文件组中为此表选择文件组。应该把一些竞争使用的表放于不同的文件组中，并且让文件组属于不同磁盘，这样可以在表竞争读写时提高并发性能。5) 创建完成后，单击磁盘图标，并为表取一个名字，注意，应该为表取一个有意义的名字。2用Transact_SQL语句创建表 语法为： CREAT