32--48数据库实验指导书.doc

数据库原理及应用C实验指导书实验一 认识 SQL Server 2000管理工具实验二 数据库及表的创建实验三 单表数据查询实验四 多表数据查询、数据删除、修改实验五 视图和索引上海工程技术大学电子电气工程学院计算机系上海工程技术大学实验报告要求实验课程：年级：实验成绩：实验名称：姓名：实验编号：学号：实验日期：指导教师：组号：实验时间：一、实验目的本次实验所涉及并要求掌握的知识点。二、实验内容与实验步骤实验内容、原理分析及具体实验步骤。三、实验环境实验所使用的器件、仪器设备名称及规格。四、实验过程与分析详细记录实验过程中发生的故障和问题，进行故障分析，说明故障排除的过程及方法。根据具体实验，记录、整理相应数据表格、绘制曲线、波形图等。五、实验结果总结对实验结果进行分析，完成思考题目，总结实验的心得体会，并提出实验的改进意见。六、附录 实验一 认识 SQL Server 2000管理工具一、实验目的熟悉SQL Server 2000的主要工具，并了解其他辅助工具或向导，学会如何创建数据库及其他数据库对象，灵活进行操作。二、预备知识l SQL Server服务管理器SQL Server服务管理器界面如图所示：服务管理器（Service Manger）用于开启、暂停和停止SQL服务工作。同时，也可以通过它设置服务是否自动启动。l SQL Server企业管理器在Windows桌面上，依次选择（单击）开始程序Microsoft SQL Server企业管理器（Enterprise Manager），将打开SQL Server 企业管理器。如图所示：企业管理器（Enterprise Manager）是SQL Server 2000功能最强大的管理工具之一。启动企业管理器后，窗口的左边是以树形结构组织的SQL Server管理的对象，右边称为明细窗口，其内容是左边选中对象的详细内容。通过企业管理器，可以实现对管理对象的定义、改动、删除等各种操作。当对准选中对象单击鼠标右键时，就会弹出相应的功能菜单，选择后就可以转到系统提供的工具或向导。在菜单栏中包括“控制台”、“窗口”、“帮助”、“操作”、“查看”、“工具”等。单击其中任一项将弹出相应的菜单，使用者可从中指定所需的操作。单击快捷工具栏上的图标，使用者可方便地实施某些操作。单击工具栏上的“属性”图标，将会弹出一个“属性”窗口，使用者可以浏览或修改被选中数据库的属性。在目录树窗格。如果本机与SQL Server尚未连接上，可用右键单击目录树上本机的计算机名，在弹出的菜单中选择“连接”项。若已连接，应在目录上可以看到“数据库”文件夹。单击“数据库”文件夹前的“+”号，将展开“数据库”文件夹。在图中，每个柱状图标标记一个数据库。展开一个数据库，可以看到数据库下的数据库对象（如表、视图等）。1.pubs和Northwind示例数据库2.Master数据库系统数据库Master数据库的主文件名为Master.mdf，日志文件为Master.ldf。Master数据库中内含许多系统表，用来跟踪和记录SQL Server相关信息。这些信息用于控制用户数据库和数据操作。Master数据库是SQL Server 2000最重要的系统数据库，在进行数据备份时，一定要将Master数据库的内容备份和用户数据库一起作备份处理，否则，系统不能正常工作。3.Msdb数据库系统数据库Msdb数据库的主文件名为Msdb.mdf，日志文件名为Msdb.ldf。Msdf数据库主要由SQL Server企业管理器和代理服务器使用。Msdb数据库中记录着任务计划信息、事件处理信息、数据备份及恢复信息和警告及异常信息。4.Model数据库系统数据库Model数据库的主文件是model.mdf，日志文件为model.ldf。Medel数据库是SQL Server 2000为用户数据库提供的样板，新的用户数据库都以model数据库为基础。每次创建一个数据库时，SQL Server 20000先制作一个model数据库的拷贝，然后再将这个拷贝扩展成要求的规模。5.Tempdb数据库系统数据库Tempdb数据库的主文件名和日志文件名分别为tempdb.mdf和tempdb.ldb。Tempdb数据库是一个共享的工作空间，SQL Server 2000中的所有数据库都可以使用它，它为临时表和其他临时工作提供了一个存储区。当用户脱离tempdb数据库时，用户的所有临时表都从tempdb数据库中卸下。当关闭一个数据库服务时，该SQL服务器上的tempdb数据库中的内容将全部被清空。l SQL Server查询分析器查询分析器是在开发数据库应用系统时使用最多的工具，如图：有两种方法可以进入SQL Server查询分析器。第一种方法是在Windows桌面上，依次选择（单击）开始程序Microsoft SQL Server 查询分析器；第二种方法是进入“企业管理器”界面后，单击“工具”菜单选项，再从弹出的菜单选择“SQL 查询分析器”。无论用哪一种方法，都可以打开“SQL查询分析器”界面。在SQL Server 查询分析器界面中，左侧窗格是对象浏览器。可以展开目录树浏览对象。单击其中任一项将弹出相应的菜单，可从中指定所需的操作。查询分析器顶部第二行是快捷工具栏。单击工具栏上的图标，可方便地实施某些操作，例如，可以“保存查询结果”、“复制”、“粘贴”等。查询代码窗格用于输入和编辑程序代码，显示结果窗格用于输出系统反馈信息和显示查询结果。三、实验内容使用SQL Server 2000企业管理器，按下列要求创建和修改用户数据库。1. 创建一个数据库，要求如下：（1）数据库名为“Stu_C”。（2）数据库中包含一个基本数据文件，逻辑文件名为Stu_C_data，磁盘文件名为Stu_C_data.mdf，文件初始容量为10MB，最大容量为100MB，文件容量递增值为5。（3）事务日志文件，逻辑文件名为Stu_C_log，磁盘文件名为Stu_C_log.ldf，文件初始容量为5MB，最大容量为30MB，文件容量递增值为2MB。2. 对数据库做如下修改：（1）添加一个辅助数据文件，逻辑文件名为Stu_C2_data，磁盘文件名为Stu_C2_data.ndf，文件初始容量为5MB，最大容量为50MB，文件容量递增值为5MB。（2）将日志文件的最大容量增加为50MB，递增值改为3MB。3. 将Stu_C数据库更名为New_Stu_C。4. 删除New_Stu_C数据库。四、实验步骤启动SQL Server服务器。在企业管理器中创建和修改数据库Stu_C。1. 设置常规选项卡（1）在企业管理器的控制面板目路中选中节点“数据库”，单击鼠标右键，在弹出菜单中选择“新建数据库”命令。（2）设置新建数据库的常规选项卡，在“名称”文本框中键入数据库名称“Stu_C”。2. 设置数据文件选项卡（1）基本数据文件名“Stu_C_data”已自动生成在“文件名”项，“位置”指明数据文件存储的位置，可进行修改。（2）设置该文件初始大小为10MB。（3）选中“文件属性”对话框中的复选框“文件自动增长”，并选中“按百分比”设置文件容量递增值为5。（4）在“最大文件大小”对话框中选择“将文件增长限制为（MB）”为100。3. 设置事务日志选项卡（1）事务日志文件名“Stu_C_log”已自动生成。设置该文件初始大小为5MB。（2）选中“文件属性”对话框中“文件自动增长”，并选中“按兆字节” 设置文件容量递增值为。（3）在“最大文件大小”对话框中选择“将文件增长限制为（MB）”为30。（4）单击“确定”按钮，完成数据库的创建。4. 修改数据库属性（1）从树状目录窗口中找到刚刚创建的数据库Stu_C，单击右键，从弹出菜单中选择命令“属性”，打开数据库Stu_C的属性窗口。（2）选择数据文件选项卡。在该选项卡中添加数据文件Stu_C2_data，设置该文件初始大小为5MB，选中“文件自动增长”，并选中“按兆字节”设置文件内容递增值为5。最后选择“将文件增长限制为（MB）”为50。（3）选择事务日志选项卡，在该选项卡中将事务日志文件的最大容量改为50，递增值改为3。5. 将Stu_C 数据库更名为New_Stu_C可以调用系统存储过程sp_renamedb更改数据库的名称。在查询分析器中输入如下语句：sp_renamedb Stu_C, New_Stu_C6. 删除数据库New_Stu_C（1）从树状目录窗口中找到刚创建的数据库New_Stu_C，单击右键，从弹出菜单中选择命令“删除”。（2）在弹出对话框中选择“是”确认即可。还可以使用DROP DATABASE来实现数据库的删除。使用语句： DROP DATABASE New_Stu_C。实验二 数据库及表的创建一、实验目的熟悉数据库、表、约束与规则、索引的创建；了解主键的创建和使用；熟练掌握企业管理器和Transact-SQL对数据库及表的操作方法。二、实验内容1. 创建学生课程数据库Stu_C中学生表S，表结构如表所示：表2-1 学生表S的数据结构字段名称字段说明数据类型长度是否为空备注SNO学号CHAR10NOT NULL主键SNAME姓名CHAR10NOT NULLSEX性别CHAR2NULLAGE年龄INT4NULLSDEPT系别CHAR10NULL2. 创建学生课程数据库Stu_C中课程表C，表结构如表所示：表2-2 课程表C的数据结构字段名称字段说明数据类型长度是否为空备注CNO课程号CHAR6NOT NULL主键CNAME课程名CHAR20NOT NULLPCNO先修课号CHAR6NULLCREDIT学分INT4NULL3. 创建学生课程数据库Stu_C中课程表SC，表结构如表所示：表2-3 选修表SC的数据结构字段名称字段说明数据类型长度是否为空备注CNO课程号CHAR6NOT NULL主键为（SNO，CNO）SNO学号CHAR10NOT NULLGRADE成绩INT4NULL4. 修改C表，删除CREDIT字段。5. 在C表中添加一列，列名为TEACHER，数据类型为CHAR，长度为10，允许为空。6. 修改S表中SDEPT字段，将其长度改为30。7. 创建CHECK约束。在S表SEX列创建“SEX”只能是“男”或“女”的（检查）约束。8. 创建DEFAULT默认值约束。在S表AGE列创建缺省值为“18”的约束。9. 创建FOREIGN KEY约束。C表中的先修课PCNO参照C表中的CNO。SC表中的SNO参照S表中的SNO属性，CNO参照C表中CNO属性。10.根据外码约束，生成S，C，SC三个表的关系图。三、实验步骤1. 方法一：使用企业管理器创建数据表（1）打开企业管理器，在树状目录窗口中找到数据库节点Stu_C，若没有，则按照实验一方法创建数据库。（2）选中下级节点“表”。单击鼠标右键，从弹出菜单中选择“新建表”，打开表设计窗口，在窗口按照“实验内容”中所列出的数据表结构，定义列名、数据类型、长度等属性。（3）选择主键字段，单击右键，选择“设置主键”，或使用工具栏上的“主键”快捷工具设定。（4）单击工具栏“保存”按钮，在弹出的对话框中输入表名称，关闭表设计窗口，完成表的创建。方法二：使用Transact_SQL语句创建数据表。如图所示，在查询分析器代码窗格内输入：USE Stu_CGOCREATE TABLE S (SNO CHAR(10) PRIMARY KEY, SNAME CHAR(10) NOT NULL, SEX CHAR(2) NULL, AGE INT NULL, SDEPT CHAR(10) NULL); CREATE TABLE C (CNO CHAR(6) PRIMARY KEY, CNAME CHAR(20) NOT NULL, PCNO CHAR(6) NULL, CREDIT INT NULL);CREATE TABLE SC (SNO CHAR(10) NOT NULL, CNO CHAR(6) NOT NULL, GRADE INT NULL, PRIMARY KEY (Sno, Cno);然后单击工具栏上的三角形(执行查询)图标，系统将运行查询代码,并在显示结果窗格上显示Stu_C数据库中的各个表。2. 在企业管理器中删除列（1）在目录树中选择Stu_C数据库，选择要修改的表。（2）单击右键，选择“设计表”，打开表设计对话框。（3）选择需要进行修改的列，选择“删除列”。（4）保存，完成对数据表的修改。也可以使用Transact_SQL语句来实现。USE Stu_CGOALTER TABLE C DROP COLUMN CREDITGO3. 在企业管理器中添加列（1）在服务器目录树中选择Stu_C数据库，展开单击“表”选项，选择C表。（2）点击右键，选择“设计表”，打开表设计对话框。（3）在表设计对话框中添加列TEACHER，并进行数据类型及约束定义。也可以使用Transact_SQL语句来实现。USE Stu_CGOALTER TABLE C ADD TEACHER CHAR(10) NULLGO（4）保存，完成对数据表的修改。4. 在企业管理器中对表结构进行修改（1）在服务器目录树中选择Stu_C数据库，展开单击“表”选项，选择S表。（2）点击右键，选择“设计表”，打开表设计对话框。（3）在表设计对话框中对列DEPT进行修改。也可以使用Transact_SQL语句来实现。USE Stu_CGOALTER TABLE S ALTER COLUMN DEPT (30) GO（4）保存，完成对数据表的修改。5. 在企业管理器中对表进行约束和规则定义（1）在服务器目录树中选择Stu_C数据库，展开单击“表”选项，选择S表。（2）点击右键，选择“设计表”，打开表设计对话框。（3）在表设计对话框中，选中要进行CHECK约束定义的列SEX，点击右键，选择“CHECK约束”，在对话框中输入约束表达式“SEX IN (男,女)”。（4）选中要进行DEFAULT定义的列AGE，在下面的属性选项卡“默认值”中输入“18”。（5）保存，完成对数据表的约束定义。6. 在企业管理器中义创建三个表的关系（1）在服务器目录树中选择Stu_C数据库，展开单击“表”选项，选择S表。（2）点击右键，选择“设计表”，打开表设计对话框。（3）在表设计对话框中，点击右键，选择“关系”，或在工具栏中选择“关系”图标。（4）在关系对话框中，选择“新建”，在“主键表”“外键表”选项中选择表名，体现参照和被参照的关系。并确定相对应的列名。（5）保存，完成一个关系的定义。之后再用同样的方法“新建”新的关系，保存。7. 使用向导创建关系图（1）在服务器目录树中选择Stu_C数据库，展开单击“关系图”选项。（2）进入新建关系图向导，按照提示进行此数据库关系图的创建。（3）对关系图保存并命名。四、思考题1.为什么要进行约束定义？2.学生实体与课程实体之间的联系是什么类型？3.指出三个表之间是怎样的参照和被参照关系？4. 在创建关系时，选中“级联删除相关记录”与不选“级联删除相关记录”有什么不同？五、实验报告要求1. 回答思考题。2. 实验最终获得的关系图。3. 实验中遇到的特殊问题及解决办法。4. 实验体会。六、注意事项1. 创建的库及表默认状态下保存在系统盘C相应目录下，可修改其保存位置。2. 灵活运用右键功能。实验三 单表数据查询一、实验目的1. 熟练使用企业管理器向表中插入、删除、修改数据。2. 学会使用企业管理器查询数据。3. 掌握使用SQL语句查询数据。二、实验内容1. 基于实验二创建的学生课程数据库Stu_C，向表S、C、SC中分别插入至少10条记录。2. 完成如下数据查询：1) 检索liu老师所授课程的课程号和课程名。2) 检索年龄大于23岁的男同学的学号和姓名。3) 检索学号为s3的学生所学课程的课程名和任课教师名。4) 检索至少选修liu老师所授课程中一门课程的女同学姓名。5) 检索至少选修两门课程的学生学号。6) 统计有学生选修的课程门数。7) 求liu老师所授课程的每门课程的学生平均成绩。8) 求选修c4课程的学生平均年龄。9) 检索学号比wang同学大，而年龄比他小的学生姓名。10) 检索姓名以wang打头的所有学生的姓名和年龄。11) 在sc中检索成绩为空值的学生学号和课程号。三、实验步骤使用SQL Server 2000企业管理器和Transact-SQL语句进行数据查询。1. 在企业管理器中，打开查询窗口，以图形方式设计查询（1）启动企业管理器。展开自己的数据库Stu_C，单击表。（2）在需要查询的表上单击右键，在快捷菜单中选择“打开表”，其下级菜单即为：l 返回所有行：该命令以网格方式显示指定表的所有记录；l 返回首行：该命令以交互方式输入要返回的行数；l 查询：打开查询窗口，以图形方式设计查询；（3）选中要查询的列，输入相应的筛选条件，再单击执行按钮（！）执行查询，下方显示查询结果。2. 用SQL完成上述查询（1）启动查询分析器。（2）在查询分析器中输入SQL语句。（3）按F5执行查询结果，以网格方式或以消息方式显示。四、思考题1. 数据库中一般不允许更改主码数据。如果一定要更改主码数据时，怎么实现？2. 为什么系统不允许随意的删除被参照表中的主码？3. 在实验二中创建的关系在进行数据操纵时起到了哪些作用？五、实验报告要求1. 回答思考题。2. 写出以上11个SQL语句查询及运行结果。3. 实验中的问题和体会。六、注意事项1. 输入数据时要注意数据的一致性。2. 数据修改（特别是主属性）和删除时要注意遵守约束定义。实验四 多表数据查询、数据删除、修改一、实验目的1. 掌握使用查询分析器进行连接查询。2. 加深对SQL语句的理解。二、实验内容1. 基于实验三的表S、C、SC，用查询分析期进行连接查询。2. 完成如下数据查询：1) 检索wang同学不学的课程的课程号。2) 检索全部学生都修的课程的课程号与课程名。3) 检索选修课程包括liu老师所教所有课程的学生学号。4) 统计每门课程的学生选修人数（超过10人的课程才统计），要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。5) 求年龄大于女同学平均年龄的男学生姓名和年龄。6) 求年龄大于所有女同学年龄的男同学姓名和年龄。7) 选修了全部课程的学生姓名8) 检索只选修了c2课程的学生姓名。9) 检索至少选修了c1和c2课程的学生姓名。10) 检索至少选修了s2学生所修课程的学生姓名。11) 检索英语成绩最高的学生姓名。12) 往基本表s中插入一个学生元组（s9，wu，18）。13) 8.在基本表s中检索每一门课程成绩都大于等于80分的学生学号，姓名和性别，并把检索到的值送往另一个已存在的基本表student（sno，sname，sex）14) 在基本表sc中删除尚无成绩的选课元组。15) 把wang同学的学习选课和成绩全部删去。16) 把选修maths课不及格的成绩全改为空值。17) 把低于总平均成绩的女同学成绩提高15。18) 在基本表sc中修改c4课程的成绩，若成绩小于等于75分时提高5，若成绩大于75分时提高4（用两个update语句实现）。三、思考题1. 如何提高数据查询和连接速度。2. 怎样简化用户的查询？3. 查询结果如何保存？四、实验报告要求1. 回答思考题。2. 写出以上18个SQL语句查询及运行结果。3. 实验中的问题和体会。五、注意事项1. 查询结果有多种处理方式。2. 输入SQL语句时应注意，语句中均使用西文操作符号。实验五 视图和索引一、实验目的1. 掌握通过视图向导和图表创建向导进行视图创建。2. 加深对视图和SQL SERVER图表作用的理解。3. 掌握通过视图删除记录、添加记录、更新记录和删除视图的方法。二、实验内容1.创建“计算机系”学生的视图Stu_CS1。2.创建“计算机系”选修了“081024”号课程的学生视图Stu_CS2。3.创建“计算机系”选修了“081024”号课程且成绩在“90”分以上的学生视图Stu_CS3。4.基于视图进行添加、更新、删除记录。5.使用企业管理器和Transact-SQL语句删除视图。6为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建普通索引，SC表按学号升序和课程号降序建复合索引。7删除Student表的Stusno索引。8.将Student数据库中的三个表s,c,sc导出为EXCEL文件a.xls9新建立一个数据库ss，将a.xls中的三个表s,c,sc导入10将数据库ss备份，放在c:/ss1文件中11删除ss数据库，根据备份文件ss1还原数据库12建立一个用户Mary，设置其在s数据表的权限为只能读，不能更新。13为student数据库创建名称为Tom的用户，设置该用户对s表具有Select和插入权限，但禁止对sc表进行修改和删除操作。14为student数据库创建一个名称为StockRole的数据库角色（组），设置其对数据表S具有Select、Insert和Update权限，但禁止执行Delete操作，同时为student数据库创建一个名为TomUser的用户，并将其添加为数据库角色（组）StockRole的成员。三、实验步骤1.创建视图（1）打开企业管理器窗口，确认服务器，打开数据库文件夹，选中新视图所在的数据库；（2）选择菜单“工具”“向导”；图5.1 视图向导（3）在向导对话框中，单击数据库左边的“+”号，使之展开。选择“创建视图向导”项，单击“确定”按钮；（4）进入创建视图向导后，首先出