《数据库原理》实验指导书1-8

1数据库原理实验指导书2目 录实验一 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 的启动（ ）、停止（ ）和暂停（ ）。3图 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 学习数据库）节点，浏览其中包含的数据库对象。4注意：如果相应实例的数据库服务器未启动，用鼠标右键单击该数据库服务器，从弹出的快捷菜单中选择“连接 ”也可启动 SQL Server 服务。四、使用查询分析器操作要求：通过“企业管理器” 进入 “查询分析器”，按示例进行简单的数据查询操作。操作 1：在“企业管理器 ”窗口中，选位 pubs 数据库（系统提供的学习用数据库），执行“工具SQL 查询分析器” 菜单命令（如图 1.4 所示），打开“ 查询分析器”窗口（如图 1.5 所示）。图 1.4 “工具” 菜单图 1.5 “查询分析器” 窗口操作 2：通过窗口的工具栏确认当前库数据库为“ ”，在查询分析器的文本窗口中输入查询语句：SELECT * FROM publishers5执行“ 查询 ”菜单命令或单击工具栏上相应按钮，执行查询，注意观察输出结果；关闭查询分析器窗口。注意：执行查询语句之前，可以先执行“查询分析” 菜单命令，分析 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”用户身份的说明介绍。6操作 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”；7图 2.1 数据库属性“常规” 选项卡图 2.2“数据文件” 选项卡8图 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。9注意：修改数据库时，其属性对话框中将增加“文件组” 、“选项”和 “权限”选项卡，可参照相关要求设置，本例中可使用默认设置。图 2.4 添加数据文件4使用 Transact-SQL 语句创建和修改数据库1）创建数据库程序代码：CREATE DATABASE testdbON 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 )10GO3）自动生成 Transcat-SQL 程序代码操作 1：使用企业管理器，自动生成已建“testdb”数据库的相关 Transcat-SQL 程序代码。(1)在企业管理器窗口的“ 树形目录窗格”，选择已建“testdb”数据库，单击鼠标右键，在快捷菜单中执行“所有任务生成 SQL 脚本”命令，如图 2.5 所示。图 2.5 快捷菜单图 2.6“生成 SQL 脚本”对话框(2)在弹出的如图 2.6 所示对话框中，单击“选项”选项卡，标记“ 编写数据库脚本”复选框；再选择“常规”选项卡，单击“预览 ”按钮，在生成的 SQL 脚本预览框中，浏览查看；也可选择“复制”，将该文本复制到记事本或其他文本编辑器中备用。注意：以后你可以用类似方法，生成你所需要的 SQL 脚本代码。操作 2：使用查询分析器建立数据库。11(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结束日期 Datetime 1使用企业管理器创建数据表并添加约束操作 1：创建数据表(1)启动企业管理器，在树状目录窗格中找到数据库节点“testdb”，选择其下级 “表”节点 ，单击鼠标右键，从快捷菜单中选择“新建表” 命令，打开表设计窗口，输入前面 “员工表” 表格中所列字段的“列名”、“数据类型”、 “长度 ”以及是否允许空，如图 2.7 所示。(2)选定编号 字段，在下面“列” 属性标签上，单击“ 标识”下拉列表，选择 “是”，设置该字段为“标识列”。(3)选定编号 字段，单击工具栏“ ”按钮，设置该字段为“主键” 。12图 2.7 表属性修改窗口(4)单击“保存” 按钮，输入表名“ 员工表”，关闭表设计窗口，完成项目表创建。同样步骤，创建数据表“项目表” 。操作 2：参照“员工表 ”的编号字段，对 “项目表”的负责人 字段添加外键约束 (FOREIGN KEY 约束)。(1)在企业管理器的控制面板“ 树形目录窗格”中，选定 testdb 数据库中“表”节点，在内容窗格中，鼠标右键单击“ 项目表 ”，执行快捷菜单中 “设计表”命令；(2)在打开的表属性修改窗口中，单击工具栏管理关系“ ”按钮，选择“关系”选项卡；(3)单击“新建” 按钮，主键表为“ 员工表”，选择字段编号 ；外键表为 “项目表”，选择字段 负责人，如图 2.8所示。(4)关系名可使用缺省或另取，单击关闭按钮，完成设置。13图 2.8 表设计窗口操作 3：对“员工表 ”的工资字段，添加检查 (CHECK) 约束。(1)同样，在“员工表”的表属性设计窗口中，单击管理关系 “ ”按钮，选择 CHECK 约束选项卡；(2)单击“新建” 按钮，在“ 约束表达式”文本框中输入一个条件表达式：(工资 = 1000 and 工资 = 1000 and 工资=90 的学生学号及成绩=90 的课程数。写出查询语句：二、多表查询练习 20：查询每个学生及其选修课情况（使用自然连接）。写出查询语句：练习 21：查询每门课的间接先修课。写出查询语句：练习 22：以 student 表为主体列出每个学生的基本情况及其选课情况，如果学生没有选课，只输出其基本情况。写出查询语句（写出两种形式）：练习 23：查询选修了 2 号课程且成绩在 80 分以上的学生信息。写出查询语句：20练习 24：查询每个学生的学号、姓名、选修的课程及成绩。写出查询语句：练习 25：在 STD 数据库中，查询所有计算机系学生的学号、选修课程号及分数。写出查询语句（写出两种形式）：练习 26：查询选修了课程名为“操作系统”的学生学号和姓名。写出查询语句（用 IN 嵌套实现）：练习 27：在 STD 数据库中，查询所有计算机系学生的学号、选修课程号以及分数。写出查询语句（用 EXISTS 嵌套实现）：练习 28：查询与“刘晨”在同一个系学习的学生。写出查询语句（写出三种形式）：21练习 29：查询其它系中比信息系某一学生年龄大的学生姓名和年龄。写出查询语句（写出两种形式）：练习 30：查询选修了课程 1 或者选修了课程 4 的学生。写出查询语句（写出两种形式）：练习 30：查询信息系的学生与年龄不大于 19 岁的学生的差集。写出查询语句（写出两种形式）：练习 31：建立关系 Employee，内容如下：id dep salary rank22001 A 800 2002 A 900 3003 B 850 2004 B 1000 3005 C 700 2(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 表产生了怎样的变化：23练习 34：修改 Cno 为1的记录的课程名为 数据库原理及应用。写出查询语句：练习 35：将信息系全体学生的成绩改为 80。写出查询语句：练习 36：教工胡云辞职，删除胡云的记录。写出查询语句：练习 37：删除信息系所有学生的选课记录。写出查询语句：练习 38：执行以下语句：USE STDTRUNCATE course再次查看 course 表中的内容，分析执行的结果，并写出等价的 SQL 语句：24技能要点 基本 SELECT 查询 使用 SELECT 语句的主要子句，实现复杂查询 数据表的记录数据更新与修改（1）使用 INSERT 语句的插入数据（2）使用 UPDATE 语句更新数据（3）使用 DELETE 语句删除一个表中数据行实验四 实现视图 实验目的 1 掌握使用企业管理器创建并管理视图的步骤与方法2 掌握 Transact-SQL 语句创建与管理视图3 熟悉通过视图更新数据表中记录数据的方法实验内容一、使用企业管理器创建和管理视图操作要求：在 testdb 数据库中，创建基于数据表“项目表” 和“员工表”的视图。具体要求如下：(1)自定视图名（如“ 员工项目_VIEW”等）；(2)含“ 员工表” 中字段编号 、姓名，字段别名分别是员工编号 、员工姓名 ；(3)含“ 项目表” 中字段名称 、开始日期，字段别名分别是项目名称和项目开始日期 。1使用企业管理器中“向导” 创建视图操作 1：启动企业管理器，执行控制台根目录窗口中“工具向导”菜单，在如图 4.1 所示对话框中，选择“数据库 创建视图向导 ”，单击“确定”按钮。25图 4.1 选择向导对话框操作 2：在下一个向导对话框中，选择数据库名称“testdb”，单击“下一步”按钮；在如图 4.2 所示对话框中，标记“ ”选择视图所引用的数据库对象“员工表”、“ 项目表”，单击“下一步”按钮；操作 3：在如图 4.3 所示向导对话框中，分别标记“ ”选择“员工表” 中字段 编号、姓名 和“ 项目表”中字段名称、开始日期作为所建视图中的列，单击“下一步”按钮。图 4.2 “选择对象” 对话框26图 4.3 “选择列” 对话框 操作 4：在如图 4.4 所示向导对话框中，输入限制视图信息的条件“项目表.负责人= 员工表.编号”，单击“下一步” 按钮；图 4.4 “定义限制” 对话框操作 5：在下一个的对话框中，给视图命名“员工项目_VIEW”，单击“下一步”按钮；系统自动给创建该视图的 Transact-SQL 代码，单击“ 完成”按钮。注意：在使用向导创建视图过程中，给视图指定字段别名需要在最后出现的向导对话框窗口中修改脚本代码（本例不推荐）。操作 6：返回企业管理器窗口，展开树形目录窗格中“数据库testdb 视图”节点 ，在内容显示窗格中，鼠标右键单击已建视图“项目员工_VIEW”，执行“ 打开视图返回所有行 ”菜单命令，浏览视图信息显示。27操作 7：关闭视图信息显示窗口；选定所建视图，使用右键快捷菜单或窗口“操作”菜单，重命名视图为“项目员工 1_VIEW”，或删除视图。注意：视图创建后，同样可以使用企业管理器重新设计（修改）视图，但建议在完成下一个任务后，再进行操作。2使用企业管理器直接创建视图操作 1：在企业管理器的控制台根目录窗口中，选择 testdb 数据库的视图节点，在鼠标右键快捷菜单或窗口“操作 ”菜单中，执行 “新建视图”命令，出现如图 4.5 所示的视图设计窗口。图 4.5 视图设计窗口操作 2：鼠标右键单击视图设计窗口中“(1)”区，执行快捷菜单中“添加表”命令，在添加表对话框中，选择数据表“ 员工表 ”和 “项目表”添加，并关闭对话框。注意：所添加的两个表已建立表间关联（参考图 4.6 所示）；留意观察视图设计窗口中“(3)”区自动生成的SQL 代码。图 4.6 视图设计效果28操作 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 （ 刘仪彬,