数据库原理实验指导书v.

数据库原理实验指导书于世东、马佳琳、崔丹 编2006-10-01目 录实验一 SQL SERVER2000数据库的使用1实验二 创建数据库和数据表5实验三 SQL技术14实验四 实现视图22实验五 创建和维护索引27实验六 触发器管理32实验七 数据的备份与恢复35*实验八 SQL Server2000安全管理40实验一 SQL SERVER2000数据库的使用实验目的 1. 了解Microsoft SQL Server安装方法与步骤2. 了解SQL Server 2000常用管理工具3. 初步熟悉系统提供的学习数据库4. 掌握在线手册的使用实验内容一、安装过程操作要求：本地安装SQL Server 2000，明确安装步骤以及注意事项。在WindowsXP或Windows2000 Professional操作系统平台下安装SQL Server 2000个人版。 系统安装完毕后，在开始菜单“程序”中新增了如图1.1所示的菜单。图1.1程序组菜单二、使用服务管理器操作要求：对已安装的本地SQL Server服务器，完成启动、暂停和停止操作。操作1：执行“开始程序Microsoft SQL Server服务管理器”菜单命令，或双击桌面任务栏上“服务管理器”图标按钮，打开“服务管理器”窗口，如图1.2所示。注意：SQL Server服务管理器用交通信号灯比喻SQL Server的启动（ ）、停止（ ）和暂停（ ）。图1.2 “服务管理器”窗口 操作2：在窗口中的“服务器”栏，选择要启动的数据库服务器（取决于所安装的实例），在“服务”中选择“SQL Server”，单击“开始/继续”按钮，启动SQL Server。注意：选择“当启动OS时自动启动服务”复选按钮，可在每次Windwos启动时自动启动SQL Server。操作3：单击“暂停”按钮，观察信号灯变化；单击“停止”按钮，观察信号灯变化；再次单击“开始/继续”按钮。操作4：关闭服务管理器窗口（但SQL Server服务仍在运行，任务栏上的“ ” 图标表示服务已启动）。三、使用企业管理器操作要求：启动SQL Server的“企业管理器”，查看已安装的数据库实例中系统数据库情况。（如果条件许可，并尝试在企业管理器中通过向导“新建SQL Server注册”，连接SQL Server教学远程数据库服务器。）图1.3 “企业管理器”窗口操作1：执行“开始程序Microsoft SQL Server企业管理器”菜单命令，打开“企业管理器”，如图1.1所示。 操作2：单击窗口中树状目录窗格中“Microsoft SQL Server”节点，并打开在其下级SQL Server组下自己安装的数据库实例的“数据库”树节点（如图1.3所示），浏览系统自动创建的数据库情况，并单击某一数据库（如Pubs或Northwind学习数据库）节点，浏览其中包含的数据库对象。注意：如果相应实例的数据库服务器未启动，用鼠标右键单击该数据库服务器，从弹出的快捷菜单中选择“连接”也可启动SQL Server服务。四、使用查询分析器操作要求：通过“企业管理器”进入“查询分析器”，按示例进行简单的数据查询操作。操作1：在“企业管理器”窗口中，选位pubs数据库（系统提供的学习用数据库），执行“工具SQL查询分析器”菜单命令（如图1.4所示），打开“查询分析器”窗口（如图1.5所示）。图1.4 “工具”菜单图1.5 “查询分析器”窗口操作2：通过窗口的工具栏确认当前库数据库为“ ”，在查询分析器的文本窗口中输入查询语句：SELECT * FROM publishers执行“查询 ”菜单命令或单击工具栏上相应按钮，执行查询，注意观察输出结果；关闭查询分析器窗口。注意：执行查询语句之前，可以先执行“查询分析”菜单命令，分析SQL代码的语法正确性。操作3：也可以执行“开始程序Microsoft SQL Server查询分析器”菜单命令，打开“查询分析器”，但首先应在如图1.6所示的连接SQL Server对话框中选择连接的服务器，并输入合法的用户名与密码，可进入SQL查询分析器。图1.6连接对话框 五、使用联机丛书操作要求：通过SQL Server 联机丛书了解权威的“SQL Server”的相关说明。操作1：通过“开始菜单”的Microsoft SQL Server程序组，打开“联机丛书”，如图1.7所示。图1.7“联机丛书”窗口操作2：在联机丛书窗口的“目录”标签中，选择感兴趣的标题，展开浏览。如：单击“安装SQL Server基本安装选项实例名称”，查看实例命名规则。操作3：在联机丛书窗口的“索引”标签中，输入关键字“服务器”，选择【服务器-SQL Server, sa 密码】项，查看系统关于“sa”用户身份的说明介绍。操作4：在联机丛书窗口的“搜索”标签中，输入查找的短语“pubs 示例数据库”，打开主题“pubs 示例数据库”，然后查看pubs数据库的有关信息。注意：在“企业管理器”或“查询分析器”中，执行“帮助”菜单命令，以及使用工具栏按钮“ ”也可打开联机帮助说明书。技能要点 Microsoft SQL Server 2000安装步骤 三个实用工具程序（服务管理器、企业管理器和查询分析器）的基本操作 使用在线手册，快速查找特定主题内容。实验二 创建数据库和数据表 实验目的1. 掌握数据库和数据表的创建方法与步骤2. 了解数据库及其对象的SQL脚本的自动生成3. 进一步熟练企业管理器和查询分析器的使用实验内容一、创建和修改数据库操作要求：分别使用SQL Server 2000企业管理器和Transcat-SQL语句，按下列要求创建和修改用户数据库。1创建一个名为“testdb”数据库： (1)包含一个数据文件，逻辑文件名“testdb_data”，磁盘文件名“testdb_data.mdf”,文件初始容量为5MB,最大容量为15MB，文件容量递增值为1MB；(2)包含一个事务日志文件，逻辑文件名为“testdb_log”, 磁盘文件名“testdb_log.ldf”,文件初始容量为5MB，最大容量为10MB，文件容量递增值为1MB；2对数据库作如下修改：(1)添加一个数据文件，逻辑文件“testdb2_data”，磁盘文件名“testdb2_data.ndf”，文件初始容量为1MB，最大容量为6MB，文件容量递增值为1MB；(2)将事务日志文件的最大容量增加为15MB，递增值改为2MB；3使用企业管理器创建和修改数据库操作1：启动“企业管理器”，在控制面板的“树形目录”中选择“数据库”节点，单击鼠标右键，在弹出菜单中选择“新建数据库”命令，出现如图2.12.3所示“数据库属性”对话框。 设置常规选项卡：在“名称”文本框中输入数据库名称“testdb”；图2.1数据库属性“常规” 选项卡图2.2“数据文件”选项卡图2.3“事物日志文件” 选项卡 设置数据文件选项卡：在文件名字段中输入数据文件逻辑文件名“testdb_data”；设置该文件初始大小为5MB；标记“文件属性”栏下的“文件自动增长” 复选框，并选中“按兆字节”设置文件容量递增值为1。在“最大文件大小”对话框中选择“将文件增长限制为(MB)”为15。 设置事务日志选项卡：在文件名字段中输入数据文件逻辑文件名“testdb_log”；设置该文件初始大小为5MB，标记“文件属性”栏下的“文件自动增长”复选框，并选中“按兆字节”设置文件容量递增值为1；在“最大文件大小”对话框中，选择“将文件增长限制为(MB)”为10。注意：在选项卡中，单击“位置”下文件名前的“”按钮，可以选择相应文件夹并输入文件名保存所新建的数据文件和事务日志文件；本例选择默认值。单击“确定”按钮，完成数据库的创建。操作2：修改数据库。1)在“树状目录窗格”中，选择新建的数据库“testdb”，单击鼠标右键，在弹出菜单中选择“属性”命令，打开数据库属性窗口；2)选择数据文件选项卡，在其中添加数据文件“testdb2_data”（文件名），确定初始分配容量1MB,文件按1兆字节增长，文件增长限制为6MB，如图2.4所示。3)选择事务日志选项卡，在其中将事务日志文件的最大容量改为15，递增量改为2。注意：修改数据库时，其属性对话框中将增加“文件组”、“选项”和“权限”选项卡，可参照相关要求设置，本例中可使用默认设置。图2.4 添加数据文件4使用Transact-SQL语句创建和修改数据库1）创建数据库程序代码：CREATE DATABASE testdb ON PRIMARY ( NAME = testdb_Data, FILENAME = C:Program FilesMicrosoft SQL ServerMSSQLdatatestdb_Data.MDF , SIZE = 5, MAXSIZE = 15, FILEGROWTH = 1) LOG ON ( NAME = testdb_Log, FILENAME = C:Program FilesMicrosoft SQL ServerMSSQL datatestdb_Log.LDF , SIZE = 5, MAXSIZE = 10, FILEGROWTH = 1)GONOTE：若按百分比增长则FILEGROWTH=n%，如FILEGROWTH=10%表示按10%递增。2）修改数据库文件增加辅助文件testdb2_data：USE testdbALTER DATABASE testdbADD FILE( NAME=testdb2_data, FILENAME = C:Program FilesMicrosoft SQL Serverdata testdb2_data.NDF , SIZE = 1, MAXSIZE = 6, FILEGROWTH = 1 )GO修改日志文件testdb_Log：USE testdbALTER DATABASE testdbMODIFY FILE( NAME = testdb_Log, MAXSIZE = 15, FILEGROWTH = 2 )GO3）自动生成Transcat-SQL程序代码操作1：使用企业管理器，自动生成已建“testdb”数据库的相关Transcat-SQL程序代码。(1)在企业管理器窗口的“树形目录窗格”，选择已建“testdb”数据库，单击鼠标右键，在快捷菜单中执行“所有任务生成SQL脚本”命令，如图2.5所示。图2.5 快捷菜单图2.6“生成SQL脚本”对话框(2)在弹出的如图2.6所示对话框中，单击“选项”选项卡，标记“编写数据库脚本”复选框；再选择“常规”选项卡，单击“预览”按钮，在生成的SQL脚本预览框中，浏览查看；也可选择“复制”，将该文本复制到记事本或其他文本编辑器中备用。注意：以后你可以用类似方法，生成你所需要的SQL脚本代码。操作2：使用查询分析器建立数据库。(1)在企业管理器窗口，选择原新建数据库“testdb”，单击鼠标右键，在快捷菜单中执行数据库“删除”命令；然后执行“工具SQL查询分析器”菜单命令，进入查询分析器窗口。(2)在查询分析器窗口，输入建立数据库程序代码，或将保存的数据库“自动生成的SQL脚本”复制到其文本窗口，“分析”无误后“执行”，则完成建库任务。注意：在查询分析器窗口的“对象浏览器”中，刷新服务器对象，可以观察到新建的testdb数据库。窗口中SQL代码可以保存为磁盘文件(.sql)作为备用。操作3：使用查询分析器修改数据库。(1)假如操作2中，在查询分析器的文本窗口输入并执行的是本次实验提供的建立数据库程序代码，则清除文本窗口内容，输入提供的数据库修改程序代码，分析并执行。(2)假如操作2中，在查询分析器的文本窗口输入并执行的是自动生成的SQL脚本代码，有关修改的具体内容，由学习者自己灵活设定。二、创建和修改数据表操作要求：使用企业管理器或Transcat-SQL语句，按下列要求testdb数据库中创建两个用户数据表（项目表和员工表），并按步骤完成对数据表的相关修改和约束设置。项目表员工表字段名数据类型注释字段名数据类型注释项目编号Int主键，标识列编号Int主键，标识列名称Varchar长度50姓名Char长度10负责人Int性别Char长度2客户Int所属部门Varchar长度50开始日期Datetime工资Money长度8结束日期Datetime1使用企业管理器创建数据表并添加约束操作1：创建数据表(1)启动企业管理器，在树状目录窗格中找到数据库节点“testdb”，选择其下级“表”节点 ，单击鼠标右键，从快捷菜单中选择“新建表”命令，打开表设计窗口，输入前面“员工表”表格中所列字段的“列名”、“数据类型”、“长度”以及是否允许空，如图2.7所示。(2)选定编号字段，在下面“列”属性标签上，单击“标识”下拉列表，选择“是”，设置该字段为“标识列”。(3)选定编号字段，单击工具栏“ ”按钮，设置该字段为“主键”。图2.7 表属性修改窗口(4)单击“保存”按钮，输入表名“员工表”，关闭表设计窗口，完成项目表创建。同样步骤，创建数据表“项目表”。操作2：参照“员工表”的编号字段，对“项目表”的负责人字段添加外键约束(FOREIGN KEY 约束)。(1)在企业管理器的控制面板“树形目录窗格”中，选定testdb数据库中“表”节点，在内容窗格中，鼠标右键单击“项目表”，执行快捷菜单中 “设计表”命令；(2)在打开的表属性修改窗口中，单击工具栏管理关系“ ”按钮，选择“关系”选项卡；(3)单击“新建”按钮，主键表为“员工表”，选择字段编号；外键表为“项目表”，选择字段负责人，如图2.8所示。(4)关系名可使用缺省或另取，单击关闭按钮，完成设置。图2.8 表设计窗口操作3：对“员工表”的工资字段，添加检查 (CHECK) 约束。(1)同样，在“员工表”的表属性设计窗口中，单击管理关系“ ”按钮，选择CHECK约束选项卡；(2)单击“新建”按钮，在“约束表达式”文本框中输入一个条件表达式：(工资 = 1000 and 工资 = 1000 and 工资=90的学生学号及成绩=90的课程数。写出查询语句： 二、多表查询练习20：查询每个学生及其选修课情况（使用自然连接）。写出查询语句： 练习21：查询每门课的间接先修课。写出查询语句： 练习22：以student表为主体列出每个学生的基本情况及其选课情况，如果学生没有选课，只输出其基本情况。写出查询语句（写出两种形式）： 练习23：查询选修了2号课程且成绩在80分以上的学生信息。写出查询语句： 练习24：查询每个学生的学号、姓名、选修的课程及成绩。写出查询语句： 练习25：在STD数据库中，查询所有计算机系学生的学号、选修课程号及分数。写出查询语句（写出两种形式）： 练习26：查询选修了课程名为“操作系统”的学生学号和姓名。写出查询语句（用IN嵌套实现）： 练习27：在STD数据库中，查询所有计算机系学生的学号、选修课程号以及分数。写出查询语句（用EXISTS嵌套实现）： 练习28：查询与“刘晨”在同一个系学习的学生。写出查询语句（写出三种形式）： 练习29：查询其它系中比信息系某一学生年龄大的学生姓名和年龄。写出查询语句（写出两种形式）： 练习30：查询选修了课程1或者选修了课程4的学生。写出查询语句（写出两种形式）： 练习30：查询信息系的学生与年龄不大于19岁的学生的差集。写出查询语句（写出两种形式）： 练习31：建立关系Employee，内容如下：iddepsalaryrank001A8002002A9003003B8502004B10003005C7002(id表示职员编号，dep表示职员所属部门，salary表示职员的薪水，rank表示职员的等级)（1）选出平均工资大于850的部门。 （2）选出级别为2的职员的平均工资。 （3）选出级别为3的职员的最高工资大于900的部门。 三、数据操纵练习32：使用SQL增加一条记录到course表中，增加的记录内容如下：（9,数据挖掘,1,5） 练习33：执行以下SQL语句，再重新检索course表中的数据，分析其结果的变化。USE STDINSERT course(Cno,Cname,Cpno,Ccredit)SELECT 001+Cno,新+Cname, Cpno,CcreditFROM course（本例中认为Cno是char型，若是int型改为100+Cno）course表产生了怎样的变化： 练习34：修改Cno为1的记录的课程名为数据库原理及应用。写出查询语句： 练习35：将信息系全体学生的成绩改为80。写出查询语句： 练习36：教工胡云辞职，删除胡云的记录。写出查询语句： 练习37：删除信息系所有学生的选课记录。写出查询语句： 练习38：执行以下语句：USE STDTRUNCATE course再次查看course表中的内容，分析执行的结果，并写出等价的SQL语句： 技能要点 基本SELECT查询 使用SELECT语句的主要子句，实现复杂查询 数据表的记录数据更新与修改（1）使用INSERT 语句的插入数据（2）使用UPDATE语句更新数据（3）使用DELETE语句删除一个表中数据行实验四 实现视图 实验目的 1 掌握使用企业管理器创建并管理视图的步骤与方法2 掌握Transact-SQL语句创建与管理视图3 熟悉通过视图更新数据表中记录数据的方法实验内容一、使用企业管理器创建和管理视图操作要求：在testdb数据库中，创建基于数据表“项目表”和“员工表”的视图。具体要求如下：(1)自定视图名（如“员工项目_VIEW”等）；(2)含“员工表”中字段编号、姓名，字段别名分别是员工编号、员工姓名；(3)含“项目表”中字段名称、开始日期，字段别名分别是项目名称和项目开始日期。1使用企业管理器中“向导”创建视图操作1：启动企业管理器，执行控制台根目录窗口中“工具向导”菜单，在如图4.1所示对话框中，选择“数据库创建视图向导”，单击“确定”按钮。图4.1 选择向导对话框操作2：在下一个向导对话框中，选择数据库名称“testdb”，单击“下一步”按钮；在如图4.2所示对话框中，标记“ ”选择视图所引用的数据库对象“员工表”、“项目表”，单击“下一步”按钮；操作3：在如图4.3所示向导对话框中，分别标记“ ”选择“员工表”中字段编号、姓名和“项目表”中字段名称、开始日期作为所建视图中的列，单击“下一步”按钮。图4.2 “选择对象”对话框图4.3 “选择列”对话框 操作4：在如图4.4所示向导对话框中，输入限制视图信息的条件“项目表.负责人=员工表.编号”，单击“下一步”按钮；图4.4 “定义限制”对话框操作5：在下一个的对话框中，给视图命名“员工项目_VIEW”，单击“下一步”按钮；系统自动给创建该视图的Transact-SQL代码，单击“完成”按钮。注意：在使用向导创建视图过程中，给视图指定字段别名需要在最后出现的向导对话框窗口中修改脚本代码（本例不推荐）。操作6：返回企业管理器窗口，展开树形目录窗格中“数据库testdb视图”节点 ，在内容显示窗格中，鼠标右键单击已建视图“项目员工_VIEW”，执行“打开视图返回所有行”菜单命令，浏览视图信息显示。操作7：关闭视图信息显示窗口；选定所建视图，使用右键快捷菜单或窗口“操作”菜单，重命名视图为“项目员工1_VIEW”，或删除视图。注意：视图创建后，同样可以使用企业管理器重新设计（修改）视图，但建议在完成下一个任务后，再进行操作。2使用企业管理器直接创建视图操作1：在企业管理器的控制台根目录窗口中，选择testdb数据库的视图节点，在鼠标右键快捷菜单或窗口“操作”菜单中，执行“新建视图”命令，出现如图4.5所示的视图设计窗口。图4.5视图设计窗口操作2：鼠标右键单击视图设计窗口中“(1)”区，执行快捷菜单中“添加表”命令，在添加表对话框中，选择数据表“员工表”和“项目表”添加，并关闭对话框。注意：所添加的两个表已建立表间关联（参考图4.6所示）；留意观察视图设计窗口中“(3)”区自动生成的SQL代码。图4.6视图设计效果操作3：标记选择所添加表的相应字段（员工表.编号、员工表.姓名、项目表.名称.项目表.开始日期）。操作4：在视图设计窗口“(2)”区中的“别名”列中，分别指定相应字段别名（按操作要求），如图4.6所示。操作5：单击工具栏执行“ ”按钮，预览“(4)”区中所设计视图的显示信息。注意：显示信息类似图4.4所示，但列名已显示为相应字段的别名。操作6：单击工具栏“保存”按钮，命名所建视图“员工项目2_VIEW”。注意：可以在视图设计窗口“(2)”区中，进一步设置其他列如“输出”、“排序类型”、“准则”等列，修改视图，满足实际需要。二、使用Transact-SQL语句创建视图操作要求：在查询分析器中，输入执行相应SQL代码，创建与前面任务同样要求的视图。操作1：启动SQL查询分析器，在文本窗口中输入如下Transact-SQL语句，并分析执行。USE testdbCREATE VIEW 员工项目3_VIEW （员工编号, 员工姓名,项目名称, 项目开始日期）AS select 员工表.编号, 员工表.姓名,项目表.名称, 项目表.开始日期from 项目表 inner join 员工表 on 项目表.负责人=员工表.编号操作2：使用企业管理器，选定已创建的视图，单击鼠标右键，执行快捷菜单中“所有任务自动生成SQL脚本”命令，单击对话框窗口的“预览”按钮，将列表框中的SQL代码复制到查询分析器文本窗口中，分析执行。注意：理解复制所得SQL代码（开头）中如下IF语句的含义及功能：三、通过视图操作记录数据操作要求：.建立基于“员工表”的视图；通过视图对记录数据的插入/更新/删除等操作。操作1：在查询分析器文本窗口中，输入下列SQL代码，视图命名为“员工信息视图”；或者使用企业管理器创建该视图。USE testdbCREATE VIEW 员工信息视图 AS select * from 员工表操作2：在查询分析器文本窗口输入相关文本，开始完成下列任务。(1) 添加新记录参考语法：INSERT INTO 员工信息视图（姓名，性别，所属部门，工资)VALUES （刘仪彬, 男，工程部，2800）注意：编号为“员工表”的标识列，无须给定。(2) 修改记录数据参考语法：UPDATE员工信息视图 SET 姓名=李四光 WHERE 姓名=刘仪彬(3) 删除记录数据参考语法：DELETE 员工信息视图 FROM 姓名=李四光(4) 执行系统存储过程sp_rename将视图更名为“员工表视图” 参考语法：EXEC sp_rename 员工信息视图，员工表视图请结合课堂教学实例，创建基于“项目表”和“员工表”的其他视图，进一步练习使用视图操作数据库中表的数据。四、自我提高操作要求：.(1)新建Testdb数据库中数据表“客户表”(如图4.7所示)，并添加若干记录数据。图4.7 “客户表”属性示意(2)设置“项目表”(客户字段为外键)与“客户表”(客户编号字段为主键)设置关联。(3)创建基于“员工表”、“项目表”、“客户表”的“项目信息”视图(如图4.8所示)。图4.8 “项目信息”视图输出效果操作提示：.(1) 输入“客户表”记录数据时需注意其数据完整性的相关约束；(2) 视图中列“项目工期（天）”是使用日期函数DATEDIFF()（该函数的使用请参看SQL Server的联机从书）。技能要点 使用企业管理器创建和管理视图 编写并执行一个脚本创建视图 执行查询验证结果来测试视图 通过视图修改数据库表的记录数据实验五 创建和维护索引 实验目的1. 掌握使用企业管理器创建并维护索引的步骤与方法2. 熟悉系统自动索引的创建3. 理解CREATE INDEX选项的使用4. 了解查询性能信息的获取方法实验内容一、使用企业管理器创建索引 方式一操作要求：使用企业管理器，对员工表中基于姓名创建索引，索引名“IDX_Name”，索引类型为非聚集索引。操作1：打开企业管理器，将控制台根目录展开至“数据库testdb表”节点，选择“员工表”，并单击鼠标右键，执行快捷菜单中“所有任务管理索引”命令，打开如图5.1所示对话框。注意：图示“现有索引”列表中“PK_员工表”是在表设计中设定编号字段为主键而自动建立的聚集索引。图5.1 管理索引对话框操作2：单击对话框中“新建”按钮，在如图5.2所示的“新建索引”对话框中，输入索引名称“IDX_Name”，标记选择列中姓名字段，单击 “确定